一、MySQL游標(biāo)簡(jiǎn)介

MySQL在服務(wù)器端提供只讀的、單向的游標(biāo)，而且只能在存儲(chǔ)過(guò)程或者更底層的客戶(hù)端API中使用。

因?yàn)镸ySQL游標(biāo)中指向的對(duì)象都是存儲(chǔ)在臨時(shí)表中而不是實(shí)際查詢(xún)到的數(shù)據(jù)，所以MySQL游標(biāo)總是只讀的。它可以逐行指向查詢(xún)結(jié)果，然后讓程序做進(jìn)一步的處理。在一個(gè)存儲(chǔ)過(guò)程中，可以有多個(gè)游標(biāo)，也可以在循環(huán)中“嵌套”地使用游標(biāo)。

MySQL的游標(biāo)設(shè)計(jì)也為粗心的人“準(zhǔn)備”了陷阱。因?yàn)槭鞘褂门R時(shí)表實(shí)現(xiàn)的，所以它在效率上給開(kāi)發(fā)人員一個(gè)錯(cuò)覺(jué)。需要記住的最重要的一點(diǎn)是：當(dāng)你打開(kāi)一個(gè)游標(biāo)的時(shí)候需要執(zhí)行整個(gè)查詢(xún)。

考慮下面的存儲(chǔ)過(guò)程：

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

從這個(gè)例子中可以看到，不用處理完所有的數(shù)據(jù)就可以立刻關(guān)閉游標(biāo)。使用Oracle或者SQL Server的用戶(hù)不會(huì)認(rèn)為這個(gè)存儲(chǔ)過(guò)程有什么問(wèn)題，但是在MySQL中，這會(huì)帶來(lái)很多的不必要的額外操作。使用SHOW STATUS來(lái)診斷這個(gè)存儲(chǔ)過(guò)程，可以看到它需要做1000個(gè)索引頁(yè)的讀取，做1000個(gè)寫(xiě)入。這是因?yàn)樵诒韘akila.film中有1000條記錄，而所有這些讀和寫(xiě)都發(fā)生在第五行的打開(kāi)游標(biāo)動(dòng)作。

這個(gè)案例告訴我們，如果在關(guān)閉游標(biāo)的時(shí)候你只是掃描一個(gè)大結(jié)果集的一小部分，那么存儲(chǔ)過(guò)程可能不僅沒(méi)有減少開(kāi)銷(xiāo)，相反帶來(lái)了大量的額外開(kāi)銷(xiāo)。這時(shí)，你需要考慮使用LIMIT來(lái)限制返回的結(jié)果集。

游標(biāo)也會(huì)讓MySQL執(zhí)行一些額外的I/O操作，而這些操作的效率可能非常低。因?yàn)榕R時(shí)內(nèi)存表不支持BLOB和TEXT類(lèi)型，如果游標(biāo)返回的結(jié)果包含這樣的列的話(huà)，MySQL就必須創(chuàng)建臨時(shí)磁盤(pán)表來(lái)存放，這樣性能可能會(huì)很糟。即使沒(méi)有這樣的列，當(dāng)臨時(shí)表大于tmp_table_size的時(shí)候，MyQL也還是會(huì)在磁盤(pán)上創(chuàng)建臨時(shí)表。

MySQL不支持客戶(hù)端的游標(biāo)，不過(guò)客戶(hù)端API可以通過(guò)緩存全部查詢(xún)結(jié)果的方式模擬客戶(hù)端的游標(biāo)。這和直接將結(jié)果放在一個(gè)內(nèi)存數(shù)組中來(lái)維護(hù)并沒(méi)有什么不同。

二、綁定變量

從MySQL 4.1版本開(kāi)始，就支持服務(wù)器端的綁定變量（prepared statement），這大大提高了客戶(hù)端和服務(wù)器端數(shù)據(jù)傳輸?shù)男省Ｄ闳羰褂靡粋€(gè)支持新協(xié)議的客戶(hù)端，如MySQL CAPI，就可以使用綁定變量功能了。另外，Java和.NET的也都可以使用各自的客戶(hù)端Connector/J和Connector/NET來(lái)使用綁定變量。

最后，還有一個(gè)SQL接口用于支持綁定變量，后面我們將討論這個(gè)（這里容易引起困擾）。

當(dāng)創(chuàng)建一個(gè)綁定變量SQL時(shí)，客戶(hù)端向服務(wù)器發(fā)送了一個(gè)SQL語(yǔ)句的原型。服務(wù)器端收到這個(gè)SQL語(yǔ)句框架后，解析并存儲(chǔ)這個(gè)SQL語(yǔ)句的部分執(zhí)行計(jì)劃，返回給客戶(hù)端一個(gè)SQL語(yǔ)句處理句柄。以后每次執(zhí)行這類(lèi)查詢(xún)，客戶(hù)端都指定使用這個(gè)句柄。

綁定變量的SQL，使用問(wèn)號(hào)標(biāo)記可以接收參數(shù)的位置，當(dāng)真正需要執(zhí)行具體查詢(xún)的時(shí)候，則使用具體值代替這些問(wèn)號(hào)。例如，下面是一個(gè)綁定變量的SQL語(yǔ)句：

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

可以通過(guò)向服務(wù)器端發(fā)送各個(gè)問(wèn)號(hào)的取值和這個(gè)SQL的句柄來(lái)執(zhí)行一個(gè)具體的查詢(xún)。反復(fù)使用這樣的方式執(zhí)行具體的查詢(xún)，這正是綁定變量的優(yōu)勢(shì)所在。具體如何發(fā)送取值參數(shù)和SQL句柄，則和各個(gè)客戶(hù)端的編程語(yǔ)言有關(guān)。使用Java和.NET的MySQL連接器就是一種辦法。很多使用MySQL C語(yǔ)言鏈接庫(kù)的客戶(hù)端可以提供類(lèi)似的接口，需要根據(jù)使用的編程語(yǔ)言的文檔來(lái)了解如何使用綁定變量。

因?yàn)槿缦碌脑?，MySQL在使用綁定變量的時(shí)候可以更高效地執(zhí)行大量的重復(fù)語(yǔ)句：

1.在服務(wù)器端只需要解析一次SQL語(yǔ)句。

2.在服務(wù)器端某些優(yōu)化器的工作只需要執(zhí)行一次，因?yàn)樗鼤?huì)緩存一部分的執(zhí)行計(jì)劃。

• 以二進(jìn)制的方式只發(fā)送參數(shù)和句柄，比起每次都發(fā)送ASCII碼文本效率更高，一個(gè)二進(jìn)制的日期字段只需要三個(gè)字節(jié)，但如果是ASCII碼則需要十個(gè)字節(jié)。不過(guò)最大的節(jié)省還是來(lái)自于BLOB和TEXT字段，綁定變量的形式可以分塊傳輸，而無(wú)須一次性傳輸。二進(jìn)制協(xié)議在客戶(hù)端也可能節(jié)省很多內(nèi)存，減少了網(wǎng)絡(luò)開(kāi)銷(xiāo)，另外，還節(jié)省了將數(shù)據(jù)從存儲(chǔ)原始格式轉(zhuǎn)換成文本格式的開(kāi)銷(xiāo)。

4.僅僅是參數(shù)——而不是整個(gè)查詢(xún)語(yǔ)句——需要發(fā)送到服務(wù)器端，所以網(wǎng)絡(luò)開(kāi)銷(xiāo)會(huì)更小。

5.MySQL在存儲(chǔ)參數(shù)的時(shí)候，直接將其存放到緩存中，不再需要在內(nèi)存中多次復(fù)制。

綁定變量相對(duì)也更安全。無(wú)須在應(yīng)用程序中處理轉(zhuǎn)義，一則更簡(jiǎn)單了，二則也大大減少了SQL注入和攻擊的風(fēng)險(xiǎn)。（任何時(shí)候都不要信任用戶(hù)輸入，即使是使用綁定變量的時(shí)候。）

可以只在使用綁定變量的時(shí)候才使用二進(jìn)制傳輸協(xié)議。如果使用普通的mysql_query()接口則不會(huì)使用二進(jìn)制傳輸協(xié)議。還有一些客戶(hù)端讓你使用綁定變量，先發(fā)送帶參數(shù)的綁定SQL，然后發(fā)送變量值，但是實(shí)際上，這些客戶(hù)端只是模擬了綁定變量的接口，最后還是會(huì)直接用具體值代替參數(shù)后，再使用mysql_query()發(fā)送整個(gè)查詢(xún)語(yǔ)句。

2.1 綁定變量的優(yōu)化

對(duì)使用綁定變量的SQL，MySQL能夠緩存其部分執(zhí)行計(jì)劃，如果某些執(zhí)行計(jì)劃需要根據(jù)傳入的參數(shù)來(lái)計(jì)算時(shí)，MySQL就無(wú)法緩存這部分的執(zhí)行計(jì)劃。根據(jù)優(yōu)化器什么時(shí)候工作，可以將優(yōu)化分為三類(lèi)。

在本書(shū)編寫(xiě)的時(shí)候，下面的三點(diǎn)是適用的。

1.在準(zhǔn)備階段

• 服務(wù)器解析SQL語(yǔ)句，移除不可能的條件，并且重寫(xiě)子查詢(xún)。

2.在第一次執(zhí)行的時(shí)候

• 如果可能的話(huà)，服務(wù)器先簡(jiǎn)化嵌套循環(huán)的關(guān)聯(lián)，并將外關(guān)聯(lián)轉(zhuǎn)化成內(nèi)關(guān)聯(lián)。

3.在每次SQL語(yǔ)句執(zhí)行時(shí)

• 服務(wù)器做如下事情：

1)過(guò)濾分區(qū)。

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

3)移除常數(shù)表達(dá)式。

4)檢測(cè)常量表。

5)做必要的等值傳播。

6)分析和優(yōu)化ref、range和索引優(yōu)化等訪問(wèn)數(shù)據(jù)的方法。

7)優(yōu)化關(guān)聯(lián)順序。

2.2 SQL接口的綁定變量

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

當(dāng)服務(wù)器收到這些SQL語(yǔ)句后，先會(huì)像一般客戶(hù)端的鏈接庫(kù)一樣將其翻譯成對(duì)應(yīng)的操作。

這意味著你無(wú)須使用二進(jìn)制協(xié)議也可以使用綁定變量。

正如你看到的，比起直接編寫(xiě)的SQL語(yǔ)句，這里的語(yǔ)法看起來(lái)有一些怪怪的。

那么，這種寫(xiě)法實(shí)現(xiàn)的綁定變量到底有什么優(yōu)勢(shì)呢？

最主要的用途就是在存儲(chǔ)過(guò)程中使用。在MySQL 5.0版本中，就可以在存儲(chǔ)過(guò)程中使用綁定變量，其語(yǔ)法和前面介紹的SQL接口的綁定變量類(lèi)似。這意味，可以在存儲(chǔ)過(guò)程中構(gòu)建并執(zhí)行“動(dòng)態(tài)”的SQL語(yǔ)句，這里的

“動(dòng)態(tài)”是指可以通過(guò)靈活地拼接字符串等參數(shù)構(gòu)建SQL語(yǔ)句。例如，下面的示例存儲(chǔ)過(guò)程中可以針對(duì)某個(gè)數(shù)據(jù)庫(kù)執(zhí)行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 ;

可以這樣調(diào)用這個(gè)存儲(chǔ)過(guò)程：

mysql> CALL optimize_tables('sakila')

另一種實(shí)現(xiàn)存儲(chǔ)過(guò)程中循環(huán)的辦法是：

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;

這兩種循環(huán)結(jié)構(gòu)最重要的區(qū)別在于：REPEAT會(huì)為每個(gè)循環(huán)檢查兩次循環(huán)條件。在這個(gè)例子中，因?yàn)檠h(huán)條件檢查的是一個(gè)整數(shù)判斷，并不會(huì)有什么性能問(wèn)題，如果循環(huán)的判斷條件非常復(fù)雜的話(huà)，則需要注意這兩者的區(qū)別。

像這樣使用SQL接口的綁定變量拼接表名和庫(kù)名是很常見(jiàn)的，這樣的好處是無(wú)須使用任何參數(shù)就能完成SQL語(yǔ)句。而庫(kù)名和表名都是關(guān)鍵字，在二進(jìn)制協(xié)議的綁定變量中是不能將這兩部分參數(shù)化的。另一個(gè)經(jīng)常需要?jiǎng)討B(tài)設(shè)置的就是LIMIT子句，因?yàn)槎M(jìn)制協(xié)議中也無(wú)法將這個(gè)值參數(shù)化。

另外，編寫(xiě)存儲(chǔ)過(guò)程時(shí)，SQL接口的綁定變量通?？梢院艽蟪潭鹊貛椭覀冋{(diào)試綁定變量，如果不是在存儲(chǔ)過(guò)程中，SQL接口的綁定變量就不是那么有用了。因?yàn)镾QL接口的綁定變量，它既沒(méi)有使用二進(jìn)制傳輸協(xié)議，也沒(méi)有能夠節(jié)省帶寬，相反還總是需要增加至少一次額外網(wǎng)絡(luò)傳輸才能完成一次查詢(xún)。所有只有在某些特殊的場(chǎng)景下SQL接口的綁定變量才有用，比如當(dāng)SQL語(yǔ)句非常非常長(zhǎng)，并且需要多次執(zhí)行的時(shí)候。

2.3 綁定變量的限制

關(guān)于綁定變量的一些限制和注意事項(xiàng)如下：

1.綁定變量是會(huì)話(huà)級(jí)別的，所以連接之間不能共用綁定變量句柄。同樣地，一旦連接斷開(kāi)，則原來(lái)的句柄也不能再使用了。（連接池和持久化連接可以在一定程度上緩解這個(gè)問(wèn)題。）

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

3.并不是所有的時(shí)候使用綁定變量都能獲得更好的性能。如果只是執(zhí)行一次SQL，那么使用綁定變量方式無(wú)疑比直接執(zhí)行多了一次額外的準(zhǔn)備階段消耗，而且還需要一次額外的網(wǎng)絡(luò)開(kāi)銷(xiāo)。（要正確地使用綁定變量，還需要在使用完成后，釋放相關(guān)的資源。）

4.當(dāng)前版本下，還不能在存儲(chǔ)函數(shù)中使用綁定變量（但是存儲(chǔ)過(guò)程中可以使用）。

5.如果總是忘記釋放綁定變量資源，則在服務(wù)器端很容易發(fā)生資源“泄漏”。綁定變量 SQL總數(shù)的限制是一個(gè)全局限制，所以某一個(gè)地方的錯(cuò)誤可能會(huì)對(duì)所有其他的線程都產(chǎn)生影響。

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

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

它是如何實(shí)現(xiàn)以及原理是怎樣的，這兩點(diǎn)很容易讓人困惑。有時(shí)，很難解釋如下三種綁定變量類(lèi)型之間的區(qū)別是什么：

1.客戶(hù)端模擬的綁定變量

• 客戶(hù)端的驅(qū)動(dòng)程序接收一個(gè)帶參數(shù)的SQL，再將指定的值帶入其中，最后將完整的查詢(xún)發(fā)送到服務(wù)器端。

2.服務(wù)器端的綁定變量

• 客戶(hù)端使用特殊的二進(jìn)制協(xié)議將帶參數(shù)的字符串發(fā)送到服務(wù)器端，然后使用二進(jìn)制協(xié)議將具體的參數(shù)值發(fā)送給服務(wù)器端并執(zhí)行。

3.SQL接口的綁定變量

• 客戶(hù)端先發(fā)送一個(gè)帶參數(shù)的字符串到服務(wù)器端，這類(lèi)似于使用PREPARE的SQL語(yǔ)句，然后發(fā)送設(shè)置參數(shù)的SQL，最后使用EXECUTE來(lái)執(zhí)行SQL。所有這些都使用普通的文本傳輸協(xié)議。 

參考:《高性能MySQL》

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。