MySQL中的遊標和繫結變數

2022-08-17 18:03:24

一、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介面的繫結變數：

當伺服器收到這些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語句非常非常長，並且需要多次執行的時候。