Mysql prepare預處理的具體使用

MySQL PREPARE預處理技術意義在於,是為瞭減輕服務器壓力的一種技術。

就是說絕大多數情況下,某需求某一條SQL語句可能會被反復調用執行,或者每次執行的時候隻有個別的值不同。
比如:

  • SELECT的 WHERE子句值不同;
  • UPDATE的 SET子句值不同;
  • INSERT的 VALUES值不同;
    如果每次都需要經過上面的詞法語義解析、語句優化、制定執行計劃等,則效率就明顯下降。

1.預處理

MySQL提供瞭對服務器端準備語句的支持,就叫預處理。

這種支持利用瞭高效的客戶機/服務器二進制協議,使用帶有參數值占位符的預編譯語句有以下好處:

  • 減少每次執行語句時解析語句的開銷。通常,數據庫應用程序處理大量幾乎相同的語句,隻對子句中的字面值或變量值進行更改,例如用於查詢和刪除的WHERE、用於更新的SET和用於插入的values。
  • 防止SQL註入攻擊。參數值可以包含未轉義的SQL引號和分隔符。

預處理接口

1.應用程序中的預處理語句
可以通過客戶端編程接口使用服務器端準備好的語句,包括用於C程序的MySQL C API客戶端庫,用於Java程序的MySQL Connector/J,以及用於使用。NET技術的程序的MySQL Connector/NET。例如,C API提供瞭一組函數調用,這些函數調用構成瞭它的預編譯語句API

2.SQL腳本中的準備語句
還有一個用於預處理語句的替代SQL接口。但不需要編程,在SQL級別直接可用,可以在任何可以將SQL語句發送到要執行的服務器的程序中使用它,例如mysql客戶端程序。

2.預處理應用方式

預處理語句的SQL語法基於三個SQL語句:

  • PREPARE語句準備執行。
  • EXECUTE執行一條預處理語句。
  • DEALLOCATE PREPARE釋放一個預處理語句。

A.例子:

預處理語句無法跨SESSION操作:

mysql>CREATE TABLE `t1` (
  `id` int NOT NULL,
   NAME varchar(20),
KEY `idx_id` (`id`)
) ENGINE=InnoDB ;
 
mysql>INSERT INTO t1(id,name) values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F');
 
#設定預處理語句
mysql>PREPARE stmt1 FROM  'SELECT * FROM t1 WHERE a=? ';
 
#設置傳遞變量
mysql>SET @a = 8;
 
#執行語句
mysql>EXECUTE stmt1 USING @a;
 
#釋放預處理語句
mysql>DEALLOCATE PREPAR stmt1;

B.預處理對執行計劃變化跟蹤

通過觀察status指標Select_scan(執行全表搜索查詢的數量)變化判斷是否會受到數據量變更的影響。

image.jpg

預處理sql語句隨著數據量的變化執行計劃也在變更。

C.存儲過程包含預處理

預處理語句在存儲的例程中創建預處理語句,則在存儲的例程結束時不會釋放該語句。

DELIMITER //
 
DROP PROCEDURE IF EXISTS proc_prepared;
CREATE PROCEDURE proc_prepared()
BEGIN
DECLARE a INT;
DECLARE i INT;
PREPARE stmt1 FROM  'SELECT * FROM t1 WHERE id>? ';
SET @a = 5;
EXECUTE stmt1 USING @a;
END //
 
DELIMITER ;
 
call proc_prepared();
存儲過程之後單獨調用預處理語句,返回結果集:說明預處理沒有銷毀
 
SET @a = 5;
EXECUTE stmt1 USING @a;
+----+------+
| id | NAME |
+----+------+
|  6 | F    |
。。。

存儲過程之後單獨調用預處理語句,返回結果集:說明預處理沒有銷毀

SET @a = 5; EXECUTE stmt1 USING @a; +----+------+ | id | NAME | +----+------+ | 6 | F | 。。。

D.通過profile 查看解析語句的開銷

通過profile各種語句執行時間,解析語句花費的時間都在0.01秒以內。可以忽略不計。
所以目前在預處理方面上沒有發現明顯的優勢。

image.jpg

3.總結

預編譯初始的作用:

  • 提高效率:事先解析、檢查、編譯等工作。
  • 提高安全性:預防SQL註入

局限性和實際效果:

  • 預處理因為局限在session級別,現在無法體現真正的價值。因為mysql GA版本沒有線程池概念,每個鏈接就是每個session
  • 解析編譯語句的開銷 基本對於mysql環境來說忽略不計
  • 執行計劃也是隨著數據量而變化的。

從局限性和實際效果來看,目前沒有發揮應有的功能。不適合聲場環境中使用。

到此這篇關於Mysql prepare預處理的具體使用的文章就介紹到這瞭,更多相關Mysql prepare預處理內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: