MySQL數(shù)據(jù)庫中預(yù)處理prepared statement性能測試的示例

小編給大家分享一下MySQL數(shù)據(jù)庫中預(yù)處理prepared statement性能測試的示例，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

1、預(yù)處理干了什么

當(dāng)我們提交一條數(shù)據(jù)庫語句時，語句到達(dá)數(shù)據(jù)庫服務(wù)那邊，數(shù)據(jù)庫服務(wù)需要解析這條sql語句，比如說語法檢查，查詢條件先后優(yōu)化，然后才執(zhí)行。對于預(yù)處理，簡單來說就是把客戶端與數(shù)據(jù)庫服務(wù)原本一次交互的分成兩次。首先，提交數(shù)據(jù)庫語句，讓數(shù)據(jù)庫服務(wù)先解析這條語句。其次，提交參數(shù)，調(diào)用語句并執(zhí)行。這樣對于多次重復(fù)執(zhí)行的語句來說，可以提交并解析一次數(shù)據(jù)庫語句就可以了，然后不斷的調(diào)用剛剛解析過得語句并執(zhí)行。這樣就省去了多次解析同一條語句的時間。從而達(dá)到提高效率的目的。

預(yù)處理語句支持占位符（place holder）,通過綁定占位符的方式提交參數(shù)。一個非常重要的一點是，能與占位符綁定的只能是值，而不能是sql語句的一些關(guān)鍵詞。例如語句：“select * from student where student.id = ?”。如果放入占位符（?）中的是“1 or 1=1”，那么“1 or 1=1”就會被當(dāng)成一個值，即用``符號包括起來，最終這條非法的語句就出錯了。從而達(dá)到放sql注入的漏洞（sql injestion）。

預(yù)處理機制主要的三步驟：

1、將語句進(jìn)行預(yù)處理

2、執(zhí)行語句

3、析構(gòu)掉預(yù)處理語句。

2、關(guān)于`performance_schema`.`prepared_statements_instances` 表的介紹

運行sql腳本：show global variable like ‘%prepare%’。 可以看到一個叫‘performance_schema_max_prepared_statement_instances’的系統(tǒng)變量。其值為0表示不啟用預(yù)處理語句性能數(shù)據(jù)記錄表`performance_schema`.`prepared_statements_instances`；-1表示記錄的數(shù)量動態(tài)處理；其他正整數(shù)值則表示performance_schema_max_prepared_statement_instances記錄的最大條數(shù)。

表`performance_schema`.`prepared_statements_instances`又是什么呢？它是用來記錄預(yù)處理語句的一些基本信息和性能數(shù)據(jù)。比如預(yù)處理語句的ID，預(yù)處理語句的名字，預(yù)處理語句的具體語句內(nèi)容，預(yù)處理語句被執(zhí)行的次數(shù)，每次執(zhí)行耗時，每條預(yù)處理語句所屬的線程id等。當(dāng)我們創(chuàng)建一條預(yù)處理語句時，就會插入一條數(shù)據(jù)到這張表里。預(yù)處理語句是基于連接的，連接斷開，則預(yù)處理語句自動刪除。但`performance_schema`.`prepared_statements_instances`表是全局的，它與數(shù)據(jù)庫連接沒關(guān)系。有了這些數(shù)據(jù)，我們就可以知道，1、代碼中執(zhí)行的語句是否真的做了預(yù)處理，2、通過了解預(yù)處理語句的執(zhí)行情況來決定業(yè)務(wù)中是否需要把一個語句進(jìn)行預(yù)處理。

3、qt prepare函數(shù)說明

根據(jù)我自己本身的項目需求，這次測試的客戶端代碼使用的是Qt。這里記錄一個關(guān)鍵的函數(shù)：QSqlQuery類的prepare函數(shù)。調(diào)用prepare函數(shù)即是向數(shù)據(jù)庫提交一個創(chuàng)建預(yù)處理語句的命令。意味著調(diào)用期間，是會與數(shù)據(jù)庫服務(wù)進(jìn)行一次交互的。需要注意的是，當(dāng)同一個QSqlQuery類對象調(diào)用第二次prepare時，會將第一次調(diào)用prepare創(chuàng)建的預(yù)處理語句刪除掉，然后再創(chuàng)建一條預(yù)處理語句，即便是這兩條預(yù)處理語句是一模一樣的。在調(diào)用QSqlQuery的exec函數(shù)時，也會將QSqlQuery先前創(chuàng)建的預(yù)處理語句刪除掉。所以在查詢結(jié)束，關(guān)閉掉連接，或者查詢又執(zhí)行了其他語句，從而導(dǎo)致`performance_schema`.`prepared_statements_instances`表沒有相關(guān)預(yù)處理語句的記錄，就會誤認(rèn)為預(yù)處理語句創(chuàng)建失敗。其實Qt的這種做法，也省去了要我們?nèi)藶榈膭h除預(yù)處理語句。

4、實驗猜想

常規(guī)執(zhí)行的語句和預(yù)處理后執(zhí)行的語句不同點在于，在多次執(zhí)行的情況下，預(yù)處理語句只需解析一次sql語句，而之后多花時間在傳輸參數(shù)和綁定參數(shù)上。預(yù)處理語句在返回結(jié)果時，使用的是二進(jìn)制傳輸協(xié)議，而普通語句使用的是文本格式的傳輸協(xié)議。因此我們做出以下猜想并驗證。

1、如果執(zhí)行的是簡單語句，那么普通執(zhí)行和預(yù)處理執(zhí)行性能上差別不大。預(yù)處理語句在重復(fù)執(zhí)行復(fù)雜的語句情況下才展現(xiàn)出優(yōu)勢。

2、在查詢結(jié)果集是大數(shù)據(jù)量的情況下，預(yù)處理語句會展現(xiàn)出性能優(yōu)勢。

5、實驗數(shù)據(jù)記錄  

6、結(jié)論

實驗的數(shù)據(jù)結(jié)果和我預(yù)期的相差有點兒大，但經(jīng)過反復(fù)檢查測試代碼和測試過程，確認(rèn)測試本身應(yīng)該沒有問題。尊重實驗數(shù)據(jù)，我們得出以下結(jié)論：

1、通過實驗5和實驗6對比，實驗11和實驗12對比，可得猜想1是錯誤的。結(jié)論應(yīng)該是：MySQL預(yù)處理和常規(guī)查詢在簡單語句和復(fù)雜語句下，都沒有顯著性的性能差別。

2、通過實驗1和實驗2對比，實驗7和實驗8對比，可得猜想2是錯誤的。結(jié)論應(yīng)該是：MySQL預(yù)處理和常規(guī)查詢的結(jié)果在數(shù)據(jù)傳輸上沒有顯著性的性能差距。

       3、此外，對比遠(yuǎn)程數(shù)據(jù)庫和本地數(shù)據(jù)庫實驗數(shù)據(jù)?？傻媒Y(jié)論：MySQL數(shù)據(jù)庫在本地會給數(shù)據(jù)操作帶來顯著性的性能提高。

以上是“MySQL數(shù)據(jù)庫中預(yù)處理prepared statement性能測試的示例”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！