介紹MySQL資料庫的預處理（prepared statement）效能測試

#免費學習推薦：mysql影片教學

#1、預處理做了什麼

        當我們提交一個資料庫語句時，語句到達資料庫服務那邊，資料庫服務需要解析這本sql語句，比如說語法檢查，查詢條件先後最佳化，然後再執行。對於預處理，簡單來說就是把客戶端與資料庫服務原本一次互動的分成兩次。首先，提交資料庫語句，讓資料庫服務先解析這句語句。其次，提交參數，呼叫語句並執行。這樣對於多次重複執行的語句來說，可以提交並解析一次資料庫語句就可以了，然後不斷的呼叫剛剛解析過得語句並執行。這樣就省去了多次解析同一語句的時間。從而達到提高效率的目的。

        預處理語句支援佔位符（place holder）,透過綁定佔位符的方式提交參數。一個很重要的一點是，能與佔位符綁定的只能是值，而不能是sql語句的一些關鍵字。例如語句：「select * from student where student.id = ?」。如果放入佔位符（?）中的是“1 or 1=1”，那麼“1 or 1=1”就會被當成一個值，即用``符號包括起來，最終這條非法的語句就出錯了。從而達到放sql注入的漏洞（sql injestion）。

       預處理機制主要的三步驟：

       1、將語句進行預處理

##       2、執行語句# 

  #       2、執行語句# 

  語句。

2、關於`performance_schema`.`prepared_statements_instances` 表的介紹

         執行sql腳本：show global variable like ‘%prepare%’。 可以看到一個叫做‘

performance_schema_max_prepared_statement_instances’的系統變數。其值為0表示不啟用預處理語句效能資料記錄表`performance_schema`.`prepared_statements_instances`；-1表示記錄的數量動態處理；其他正整數值則表示performance_schema_max_prepared_statement_instances

1記錄的最大的最大條數。

        表`performance_schema`.`prepared_statements_instances`又是什麼呢？它是用來記錄預處理語句的一些基本資訊和效能資料。例如預處理語句的ID，預處理語句的名字，預處理語句的具體語句內容，預處理語句被執行的次數，每次執行耗時，每個預處理語句所屬的線程id等。當我們建立一條預處理語句時，就會插入一條資料到這張表裡。預處理語句是基於連接的，連接斷開，則預處理語句會自動刪除。但`performance_schema`.`prepared_statements_instances`表是全域的，它與資料庫連線沒關係。有了這些數據，我們就可以知道，1、程式碼中執行的語句是否真的做了預處理，2、透過了解預處理語句的執行情況來決定業務中是否需要把一個語句進行預處理。

3、qt prepare函數說明

        根據我自己本身的專案需求，這次測試的客戶程式碼使用的是Qt。這裡記錄一個關鍵的函數：QSqlQuery類別的prepare函數。呼叫prepare函數即是向資料庫提交一個建立預處理語句的命令。意味著在呼叫期間，是會與資料庫服務進行一次互動的。要注意的是，當同一個QSqlQuery類別物件呼叫第二次prepare時，會將第一次呼叫prepare建立的預處理語句刪除掉，然後再建立一條預處理語句，即便是這兩個預處理語句是一模一樣的。在呼叫QSqlQuery的exec函數時，也會將QSqlQuery先前建立的預處理語句刪除掉。所以在查詢結束，關閉掉連接，或者查詢又執行了其他語句，從而導致`performance_schema`.`prepared_statements_instances`表沒有相關預處理語句的記錄，就會誤認為預處理語句創建失敗。其實Qt的這種做法，也省了要我們人為的刪除預處理語句。

4、實驗猜想

        常規執行的語句和預處理後執行的語句不同點在於，在多次執行的情況下，預處理語句只需解析一次sql語句，而之後多花時間在傳輸參數和綁定參數上。預處理語句在傳回結果時，使用的是二進位傳輸協議，而普通語句使用的是文字格式的傳輸協定。因此我們做出以下猜想並驗證。

        1.如果執行的是簡單語句，那麼一般執行和預處理執行效能上差異不大。預處理語句在重複執行複雜的語句情況下才展現優勢。 ###

        2、查詢結果集是大資料量的情況下，預處理語句會展現出效能優勢。

5、實驗資料記錄  

##語句是否遠端資料庫傳回資料量每次實驗語句執行總次數三次實驗平均總耗時/單位毫秒#1是select * from task where task.taskId in (?)是10001000698222否select * from task where task.taskId in (arr)是10001000#667783是select * from task where task.taskId = ?是#11000#1260#4否select * from task where task.taskId = id是110009515是select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a .taskName like '%s%' and b.file_id > 100000 and b.file_id 是21000#21306否select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 是2#100014807是select * from task where task.taskId in (?)#否#10001000#570518否select * from task where task.taskId in (arr)#否 10001000562359是select * from task where task.taskId = ?否11000#21710##select * from task where task.taskId = id否11000204#11是select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 否21000##36612否select * from task a LEFT JOIN task_file b ON a.taskId = b.task_id where a.taskName like '%s%' and b.file_id > 100000 and b.file_id 否2#1000#380

6、结论

         实验的数据结果和我预期的相差有点儿大，但经过反复检查测试代码和测试过程，确认测试本身应该没有问题。尊重实验数据，我们得出以下结论：

        1、通过实验5和实验6对比，实验11和实验12对比，可得猜想1是错误的。结论应该是：MySQL预处理和常规查询在简单语句和复杂语句下，都没有显著性的性能差别。

        2、通过实验1和实验2对比，实验7和实验8对比，可得猜想2是错误的。结论应该是：MySQL预处理和常规查询的结果在数据传输上没有显著性的性能差距。

        3、此外，对比远程数据库和本地数据库实验数据。可得结论：MySQL数据库在本地会给数据操作带来显著性的性能提高。

相关免费学习推荐：mysql数据库(视频)

#序號	是否預處理

以上是介紹MySQL資料庫的預處理（prepared statement）效能測試的詳細內容。更多資訊請關注PHP中文網其他相關文章！