MySQL高級特性——綁定變數

從MySQL 4.1 版本開始，就支持伺服器端的綁定變數，這大大提高了客戶端和伺服器端數據傳輸的效率

介紹

當創建一個綁定變數 SQL 時，客戶端會向伺服器發送一個SQL語句的原型。伺服器端收到這個SQL語句框架後，解析並存儲這個SQL語句的部分執行計劃，返回個客戶端一個 SQL 語句處理句柄。以後每次執行這類查詢，客戶端都指定使用這個句柄。

綁定變數的SQL，使用問號標記可以接受參數的位置，當真正需要執行具體查詢的時候，則使用具體值來替代這些問號。例如，下麵是一個綁定變數的SQL語句：

INSERT INTO tb1(col1, col2, col3) VALUES(?,?,?);

可以通過向伺服器端發送各個問號的取值和這個SQL的句柄來執行一個具體的查詢。反覆使用這樣的方式執行具體的查詢，正是使用綁定變數的優勢所在。具體如何發送取值參數和SQL句柄，這各個客戶端的編程語言有關。

因為如下的原因，MySQL可以在使用綁定變數的時候高效執行大量的重覆語句：

1. 在伺服器端只需要執行一次SQL語句
2. 在伺服器端某些優化器的工作只需要執行一次，因為它會緩存一部分的執行計劃
3. 以二進位的方式只發送參數和句柄，比每次都發送 ASCII 碼文本效率更高
4. 僅僅是參數而不是整個查詢語句，因此網路開銷會更小
5. MySQL在存儲參數的時候，直接將其存放到緩存中，不再需要在記憶體中多次複製

綁定變數相對也更加安全。因為無需在應用程式中處理轉義，一則更加簡單了，二則也大大減少了SQL諸如和攻擊的風險。

綁定變數的優化

對於綁定變數的SQL，MySQL 能夠緩存其部分執行計劃，如果某些執行計劃需要根據傳入的參數來計算時，MySQL就無法緩存這部分的執行計劃。根據優化器什麼時候工作，可以將優化分為三類：

準備階段

伺服器解析SQL語句，移除不可能的條件並且重寫子查詢

在第一次執行的時候

如果可能，伺服器先簡化嵌套迴圈的關聯，並將外關聯轉化成內關聯

在每次SQL語句執行時

伺服器會做如下事情：

• 過濾分區
• 如果可能的話，儘可能移除COUNT()、 MIN() 和 MAX()
• 移除常數表達式
• 檢測常量表
• 做必要的等值傳播
• 分析和優化 ref、 range和索引優化等訪問數據的方法
• 優化關聯順序

SQL 介面的綁定變數

在4.1 和更新版本中，MySQL 支持了SQL介面的綁定變數。不使用二進位傳輸協議也可以直接以SQL 的方式使用綁定變數：

SQL介面的綁定變數的最主要用途就是在存儲過程中使用。在MySQL5.0 版本中，這就可以在存儲過程中使用綁定變數，其語法和前面介紹的SQL介面的綁定變數類似。這意味著可以在存儲過程中構建並且執行動態的SQL語句，即可以通過靈活的拼接字元串等參數構建SQL語句。

綁定變數的限制

關於綁定變數的一些限制和註意事項如下：

• 綁定變數是會話級別的，所以鏈接之間不能公用綁定變數句柄。同樣的，一旦連接斷開，則原本的句柄也不能再使用了。（連接池和持久化連接可以在一定程度上緩解這個問題）
• 並不是所有的時候使用綁定變數都能獲得更好的性能。如果只是執行一次的SQL，那麼使用綁定變數方式無疑比直接執行多了一次額外的準備階段消耗，而且還需要一次額外的網路開銷。
• 綁定變數SQL總數的限制是一個全局限制，如果總是忘記釋放綁定變數資源，則在伺服器端很容易發生資源“泄漏”。

不過使用綁定變數最大的障礙可能是：它是如何實現以及原理是什麼樣的，這兩點很容易讓人困惑。有時候，很難解釋如下三種綁定變數類型之間的區別是什麼：

客戶端模擬的綁定變數

客戶端的驅動程式接收一個帶參數的SQL，再將指定的值帶入其中，最後將完整的查詢發送到伺服器端。

伺服器端的綁定變數

客戶端使用特殊的二進位協議將帶參數的字元串發送到伺服器端，然後使用二進位協議將具體的參數值發送給伺服器端並執行。

SQL介面的綁定變數

客戶端先發送一個帶參數的字元串到伺服器端，這類似於使用 PREPARE 的 SQL 語句，然後發送設置參數的SQL，最後使用 EXECUTE 來執行SQL。所有這些都是使用普通的文本傳輸協議。