MySQL中的遊標和綁定變量

Posted on by

一、MySQL遊標簡介

MySQL在服務器端提供隻讀的、單向的遊標，而且隻能在存儲過程或者更底層的客戶端API中使用。

因為MySQL遊標中指向的對象都是存儲在臨時表中而不是實際查詢到的數據，所以MySQL遊標總是隻讀的。它可以逐行指向查詢結果，然後讓程序做進一步的處理。在一個存儲過程中，可以有多個遊標，也可以在循環中“嵌套”地使用遊標。

MySQL的遊標設計也為粗心的人“準備”瞭陷阱。因為是使用臨時表實現的，所以它在效率上給開發人員一個錯覺。需要記住的最重要的一點是：當你打開一個遊標的時候需要執行整個查詢。

考慮下面的存儲過程：

CREATE PROCEDURE bad_cursor()
BEGIN
DECLARE film_id INT;
DECLARE f CURSOR FOR SELECT film_id FROM sakila.film;
OPEN f;
FETCH f INTO film_id;
CLOSE f;
END

從這個例子中可以看到，不用處理完所有的數據就可以立刻關閉遊標。使用Oracle或者SQL Server的用戶不會認為這個存儲過程有什麼問題，但是在MySQL中，這會帶來很多的不必要的額外操作。使用SHOW STATUS來診斷這個存儲過程，可以看到它需要做1000個索引頁的讀取，做1000個寫入。這是因為在表sakila.film中有1000條記錄，而所有這些讀和寫都發生在第五行的打開遊標動作。

這個案例告訴我們，如果在關閉遊標的時候你隻是掃描一個大結果集的一小部分，那麼存儲過程可能不僅沒有減少開銷，相反帶來瞭大量的額外開銷。這時，你需要考慮使用LIMIT來限制返回的結果集。

遊標也會讓MySQL執行一些額外的I/O操作，而這些操作的效率可能非常低。因為臨時內存表不支持BLOB和TEXT類型，如果遊標返回的結果包含這樣的列的話，MySQL就必須創建臨時磁盤表來存放，這樣性能可能會很糟。即使沒有這樣的列，當臨時表大於tmp_table_size的時候，MyQL也還是會在磁盤上創建臨時表。

MySQL不支持客戶端的遊標，不過客戶端API可以通過緩存全部查詢結果的方式模擬客戶端的遊標。這和直接將結果放在一個內存數組中來維護並沒有什麼不同。

二、綁定變量

從MySQL 4.1版本開始，就支持服務器端的綁定變量（prepared statement），這大大提高瞭客戶端和服務器端數據傳輸的效率。你若使用一個支持新協議的客戶端，如MySQL CAPI，就可以使用綁定變量功能瞭。另外，Java和.NET的也都可以使用各自的客戶端Connector/J和Connector/NET來使用綁定變量。

最後，還有一個SQL接口用於支持綁定變量，後面我們將討論這個（這裡容易引起困擾）。

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

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

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

可以通過向服務器端發送各個問號的取值和這個SQL的句柄來執行一個具體的查詢。反復使用這樣的方式執行具體的查詢，這正是綁定變量的優勢所在。具體如何發送取值參數和SQL句柄，則和各個客戶端的編程語言有關。使用Java和.NET的MySQL連接器就是一種辦法。很多使用MySQL C語言鏈接庫的客戶端可以提供類似的接口，需要根據使用的編程語言的文檔來瞭解如何使用綁定變量。

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

1.在服務器端隻需要解析一次SQL語句。

2.在服務器端某些優化器的工作隻需要執行一次，因為它會緩存一部分的執行計劃。

  • 以二進制的方式隻發送參數和句柄，比起每次都發送ASCII碼文本效率更高，一個二進制的日期字段隻需要三個字節，但如果是ASCII碼則需要十個字節。不過最大的節省還是來自於BLOB和TEXT字段，綁定變量的形式可以分塊傳輸，而無須一次性傳輸。二進制協議在客戶端也可能節省很多內存，減少瞭網絡開銷，另外，還節省瞭將數據從存儲原始格式轉換成文本格式的開銷。

4.僅僅是參數——而不是整個查詢語句——需要發送到服務器端，所以網絡開銷會更小。

5.MySQL在存儲參數的時候，直接將其存放到緩存中，不再需要在內存中多次復制。

綁定變量相對也更安全。無須在應用程序中處理轉義，一則更簡單瞭，二則也大大減少瞭SQL註入和攻擊的風險。（任何時候都不要信任用戶輸入，即使是使用綁定變量的時候。）

可以隻在使用綁定變量的時候才使用二進制傳輸協議。如果使用普通的mysql_query()接口則不會使用二進制傳輸協議。還有一些客戶端讓你使用綁定變量，先發送帶參數的綁定SQL，然後發送變量值，但是實際上，這些客戶端隻是模擬瞭綁定變量的接口，最後還是會直接用具體值代替參數後，再使用mysql_query()發送整個查詢語句。

2.1 綁定變量的優化

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

在本書編寫的時候，下面的三點是適用的。

1.在準備階段

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

2.在第一次執行的時候

  • 如果可能的話，服務器先簡化嵌套循環的關聯，並將外關聯轉化成內關聯。

3.在每次SQL語句執行時

  • 服務器做如下事情：

1)過濾分區。

2)如果可能的話，盡量移除COUNT()、MIN()和MAX()。

3)移除常數表達式。

4)檢測常量表。

5)做必要的等值傳播。

6)分析和優化ref、range和索引優化等訪問數據的方法。

7)優化關聯順序。

2.2 SQL接口的綁定變量

MySQL支持瞭SQL接口的綁定變量。不使用二進制傳輸協議也可以直接以SQL的方式使用綁定變量。下面案例展示瞭如何使用SQL接口的綁定變量： 

image.png

當服務器收到這些SQL語句後，先會像一般客戶端的鏈接庫一樣將其翻譯成對應的操作。

這意味著你無須使用二進制協議也可以使用綁定變量。

正如你看到的，比起直接編寫的SQL語句，這裡的語法看起來有一些怪怪的。

那麼，這種寫法實現的綁定變量到底有什麼優勢呢？

最主要的用途就是在存儲過程中使用。在MySQL 5.0版本中，就可以在存儲過程中使用綁定變量，其語法和前面介紹的SQL接口的綁定變量類似。這意味，可以在存儲過程中構建並執行“動態”的SQL語句，這裡的

“動態”是指可以通過靈活地拼接字符串等參數構建SQL語句。例如，下面的示例存儲過程中可以針對某個數據庫執行OPTIMIZE TABLE的操作：

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH c INTO t;
IF done THEN
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END//
DELIMITER ;

可以這樣調用這個存儲過程：

mysql> CALL optimize_tables('sakila')

另一種實現存儲過程中循環的辦法是：

REPEAT
FETCH c INTO t;
IF NOT done THEN
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

這兩種循環結構最重要的區別在於：REPEAT會為每個循環檢查兩次循環條件。在這個例子中，因為循環條件檢查的是一個整數判斷，並不會有什麼性能問題，如果循環的判斷條件非常復雜的話，則需要註意這兩者的區別。

像這樣使用SQL接口的綁定變量拼接表名和庫名是很常見的，這樣的好處是無須使用任何參數就能完成SQL語句。而庫名和表名都是關鍵字，在二進制協議的綁定變量中是不能將這兩部分參數化的。另一個經常需要動態設置的就是LIMIT子句，因為二進制協議中也無法將這個值參數化。

另外，編寫存儲過程時，SQL接口的綁定變量通常可以很大程度地幫助我們調試綁定變量，如果不是在存儲過程中，SQL接口的綁定變量就不是那麼有用瞭。因為SQL接口的綁定變量，它既沒有使用二進制傳輸協議，也沒有能夠節省帶寬，相反還總是需要增加至少一次額外網絡傳輸才能完成一次查詢。所有隻有在某些特殊的場景下SQL接口的綁定變量才有用，比如當SQL語句非常非常長，並且需要多次執行的時候。

2.3 綁定變量的限制

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

1.綁定變量是會話級別的，所以連接之間不能共用綁定變量句柄。同樣地，一旦連接斷開，則原來的句柄也不能再使用瞭。（連接池和持久化連接可以在一定程度上緩解這個問題。）

2.在MySQL 5.1版本之前，綁定變量的SQL是不能使用查詢緩存的。

3.並不是所有的時候使用綁定變量都能獲得更好的性能。如果隻是執行一次SQL，那麼使用綁定變量方式無疑比直接執行多瞭一次額外的準備階段消耗，而且還需要一次額外的網絡開銷。（要正確地使用綁定變量，還需要在使用完成後，釋放相關的資源。）

4.當前版本下，還不能在存儲函數中使用綁定變量（但是存儲過程中可以使用）。

5.如果總是忘記釋放綁定變量資源，則在服務器端很容易發生資源“泄漏”。綁定變量 SQL總數的限制是一個全局限制，所以某一個地方的錯誤可能會對所有其他的線程都產生影響。

6.有些操作，如BEGIN，無法在綁定變量中完成。

不過使用綁定變量最大的障礙可能是：

它是如何實現以及原理是怎樣的，這兩點很容易讓人困惑。有時，很難解釋如下三種綁定變量類型之間的區別是什麼：

1.客戶端模擬的綁定變量

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

2.服務器端的綁定變量

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

3.SQL接口的綁定變量

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

參考:《高性能MySQL》

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。 

推薦閱讀：