概述

今天主要分享下MySQL的SQL預處理方面內容，也是屬于優化必須會的一方面，建議大家都能掌握。

一、SQL 語句的執行處理

1、即時 SQL

一條 SQL 在 DB 接收到最終執行完畢返回，大致的過程如下：

• 1. 詞法和語義解析；
• 2. 優化 SQL 語句，制定執行計劃；
• 3. 執行并返回結果；

如上，一條 SQL 直接是走流程處理，一次編譯，單次運行，此類普通語句被稱作 Immediate Statements （即時 SQL）。

2、預處理 SQL

但是，絕大多數情況下，某需求某一條 SQL 語句可能會被反復調用執行，或者每次執行的時候只有個別的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要經過上面的詞法語義解析、語句優化、制定執行計劃等，則效率就明顯不行了。

所謂預編譯語句就是將此類 SQL 語句中的值用占位符替代，可以視為將 SQL 語句模板化或者說參數化，一般稱這類語句叫Prepared Statements。

預編譯語句的優勢在于歸納為：一次編譯、多次運行，省去了解析優化等過程；此外預編譯語句能防止 SQL 注入。

注意：

雖然可能是通過預處理 SQL 的方式一定程度的提高了效率，但是對于優化而言，最優的執行計劃不是光靠 SQL 語句的模板化來實現的，往往還是需要通過具體值來預估出成本代價。

二、Prepared SQL Statement Syntax

MySQL 官方將 prepare、execute、deallocate 統稱為 PREPARE STATEMENT。翻譯也就習慣的稱其為預處理語句。

MySQL 預處理語句的支持版本較早，所以我們目前普遍使用的 MySQL 版本都是支持這一語法的。

語法：

# 定義預處理語句
PREPARE stmt_name FROM preparable_stmt;
# 執行預處理語句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 刪除(釋放)定義
{DEALLOCATE | DROP} PREPARE stmt_name;

1、利用字符串定義預處理 SQL (直角三角形計算)

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hwb';
mysql> SET @a = 3;
mysql> SET @b = 4; 
mysql> EXECUTE stmt1 USING @a, @b;
mysql> DEALLOCATE PREPARE stmt1; 

2、利用變量定義預處理 SQL (直角三角形計算)

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hwb';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @c = 6;
mysql> SET @d = 8;
mysql> EXECUTE stmt2 USING @c, @d;
mysql> DEALLOCATE PREPARE stmt2;

3、解決無法傳參問題

我們知道，對于 LIMIT 子句中的值，必須是常量，不得使用變量，也就是說不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，這里就可以用 PREPARE 語句解決此問題。

mysql> SET @skip = 1; 
mysql> SET @numrows = 3;
mysql> SELECT host,user FROM mysql.user LIMIT @skip, @numrows;
mysql> PREPARE stmt3 FROM "SELECT host,user FROM mysql.user LIMIT ?, ?";
mysql> EXECUTE stmt3 USING @skip, @numrows;
mysql> DEALLOCATE PREPARE stmt3;

如此一來，結合2中介紹的利用變量定義預處理 SQL 也就基本解決了傳參時語法報錯問題了，類似的：用變量傳參做表名時，MySQL 會把變量名當做表名，這樣既不是本意，也會是語法錯誤，在 SQL Server 的解決辦法是利用字符串拼接穿插變量進行傳參，再將整條 SQL 語句作為變量，最后是用 sp_executesql 調用該拼接 SQL 執行，而 Prepared SQL Statement 可謂異曲同工之妙。

mysql> SET @table = 'mysql.user';
mysql> SET @s = CONCAT('SELECT HOST,USER FROM ', @table);
mysql> PREPARE stmt4 FROM @s;
mysql> EXECUTE stmt4;
mysql> DROP PREPARE stmt4;

三、預處理 SQL 使用注意點

1、stmt_name 作為 preparable_stmt 的接收者，唯一標識，不區分大小寫。

2、preparable_stmt 語句中的 ? 是個占位符，所代表的是一個字符串，不需要將 ? 用引號包含起來。

3、定義一個已存在的 stmt_name ，原有的將被立即釋放，類似于變量的重新賦值。

4、PREPARE stmt_name 的作用域是session級

可以通過 max_prepared_stmt_count 變量來控制全局最大的存儲的預處理語句。

mysql> show variables like 'max_prepared%';

最后，預處理編譯 SQL 是占用資源的，所以在使用后注意及時使用 DEALLOCATE PREPARE 釋放資源，這也是一個好習慣。