MySQL中的遊標和綁定變數是什麼

一、MySQL遊標簡介

MySQL在伺服器端提供唯讀的、單向的遊標，而且只能在預存程序或更底層的客戶端API中使用。

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個寫入。在開啟遊標動作的第五行發生了1000次讀寫操作，原因在於表sakila.film中有1000筆記錄。

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

使用遊標可能會導致MySQL執行一些額外的低效率I/O操作。因為臨時記憶體表不支援BLOB和TEXT類型，如果遊標傳回的結果包含這樣的列的話，MySQL就必須建立臨時磁碟表來存放，這樣效能可能會很糟。即便沒有該列，當臨時表超過tmp_table_size大小時，MySQL仍會在磁碟上建立臨時表。

雖然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语句非常非常长，并且需要多次执行的时候。

2.3 绑定变量的限制

关于绑定变量的一些限制和注意事项如下：

1.绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄。同样地，一旦连接断开，则原来的句柄也不能再使用了。（连接池和持久化连接可以在一定程度上缓解这个问题。）

2.在MySQL 5.1版本之前，绑定变量的SQL是不能使用查询缓存的。

3.并不是所有的时候使用绑定变量都能获得更好的性能。如果只是执行一次SQL，那么使用绑定变量方式无疑比直接执行多了一次额外的准备阶段消耗，而且还需要一次额外的网络开销。（要正确地使用绑定变量，还需要在使用完成后，释放相关的资源。）

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

如果沒有釋放綁定變數的資源，伺服器端很容易出現資源外洩的情況。由於綁定變數 SQL 總數的限制是全域限制，因此在一個地方出現的錯誤可能會影響到所有其他執行緒。

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

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

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

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

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

2.伺服器端的綁定變數

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

3.SQL介面的綁定變數

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

以上是MySQL中的遊標和綁定變數是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！