備注:測試數(shù)據(jù)庫版本為MySQL 8.0
一. MySQL游標(biāo)簡介
MySQL在服務(wù)器端提供只讀的、單向的游標(biāo)，而且只能在存儲過程或者更底層的客戶端API中使用。因為MySQL游標(biāo)中指向的對象都是存儲在臨時表中而不是實際查詢到的數(shù)據(jù)，所以MySQL游標(biāo)總是只讀的。它可以逐行指向查詢結(jié)果溃睹，然后讓程序做進一步的處理。在一個存儲過程中胰坟，可以有多個游標(biāo)因篇，也可以在循環(huán)中“嵌套”地使用游標(biāo)。MySQL的游標(biāo)設(shè)計也為粗心的人“準(zhǔn)備”了陷阱笔横。因為是使用臨時表實現(xiàn)的竞滓，所以它在效率上給開發(fā)人員一個錯覺。需要記住的最重要的一點是：當(dāng)你打開一個游標(biāo)的時候需要執(zhí)行整個查詢吹缔∩逃樱考慮下面的存儲過程：
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
從這個例子中可以看到，不用處理完所有的數(shù)據(jù)就可以立刻關(guān)閉游標(biāo)厢塘。使用Oracle或者SQL Server的用戶不會認(rèn)為這個存儲過程有什么問題茶没，但是在MySQL中，這會帶來很多的不必要的額外操作晚碾。使用SHOW STATUS來診斷這個存儲過程抓半，可以看到它需要做1000個索引頁的讀取，做1000個寫入格嘁。這是因為在表sakila.film中有1000條記錄琅关，而所有這些讀和寫都發(fā)生在第五行的打開游標(biāo)動作。
這個案例告訴我們讥蔽，如果在關(guān)閉游標(biāo)的時候你只是掃描一個大結(jié)果集的一小部分涣易，那么存儲過程可能不僅沒有減少開銷，相反帶來了大量的額外開銷冶伞。這時新症，你需要考慮使用LIMIT來限制返回的結(jié)果集。
游標(biāo)也會讓MySQL執(zhí)行一些額外的I/O操作响禽，而這些操作的效率可能非常低徒爹。因為臨時內(nèi)存表不支持BLOB和TEXT類型，如果游標(biāo)返回的結(jié)果包含這樣的列的話芋类，MySQL就必須創(chuàng)建臨時磁盤表來存放隆嗅，這樣性能可能會很糟。即使沒有這樣的列侯繁，當(dāng)臨時表大于tmp_table_size的時候胖喳，MyQL也還是會在磁盤上創(chuàng)建臨時表。
MySQL不支持客戶端的游標(biāo)贮竟，不過客戶端API可以通過緩存全部查詢結(jié)果的方式模擬客戶端的游標(biāo)丽焊。這和直接將結(jié)果放在一個內(nèi)存數(shù)組中來維護并沒有什么不同。
二.綁定變量
從MySQL 4.1版本開始咕别，就支持服務(wù)器端的綁定變量（prepared statement）技健，這大大提高了客戶端和服務(wù)器端數(shù)據(jù)傳輸?shù)男省Ｄ闳羰褂靡粋€支持新協(xié)議的客戶端惰拱，如MySQL CAPI雌贱，就可以使用綁定變量功能了。另外偿短，Java和.NET的也都可以使用各自的客戶端Connector/J和Connector/NET來使用綁定變量欣孤。最后，還有一個SQL接口用于支持綁定變量翔冀，后面我們將討論這個（這里容易引起困擾）导街。
當(dāng)創(chuàng)建一個綁定變量SQL時，客戶端向服務(wù)器發(fā)送了一個SQL語句的原型纤子。服務(wù)器端收到這個SQL語句框架后搬瑰，解析并存儲這個SQL語句的部分執(zhí)行計劃，返回給客戶端一個SQL語句處理句柄控硼。以后每次執(zhí)行這類查詢泽论，客戶端都指定使用這個句柄。
綁定變量的SQL卡乾，使用問號標(biāo)記可以接收參數(shù)的位置翼悴，當(dāng)真正需要執(zhí)行具體查詢的時候，則使用具體值代替這些問號。例如鹦赎，下面是一個綁定變量的SQL語句：
INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);
可以通過向服務(wù)器端發(fā)送各個問號的取值和這個SQL的句柄來執(zhí)行一個具體的查詢谍椅。反復(fù)使用這樣的方式執(zhí)行具體的查詢，這正是綁定變量的優(yōu)勢所在古话。具體如何發(fā)送取值參數(shù)和SQL句柄雏吭，則和各個客戶端的編程語言有關(guān)。使用Java和.NET的MySQL連接器就是一種辦法陪踩。很多使用MySQL C語言鏈接庫的客戶端可以提供類似的接口杖们，需要根據(jù)使用的編程語言的文檔來了解如何使用綁定變量。
因為如下的原因肩狂，MySQL在使用綁定變量的時候可以更高效地執(zhí)行大量的重復(fù)語句：
- 在服務(wù)器端只需要解析一次SQL語句摘完。
- 在服務(wù)器端某些優(yōu)化器的工作只需要執(zhí)行一次，因為它會緩存一部分的執(zhí)行計劃傻谁。
- 以二進制的方式只發(fā)送參數(shù)和句柄孝治，比起每次都發(fā)送ASCII碼文本效率更高，一個二進制的日期字段只需要三個字節(jié)栅螟，但如果是ASCII碼則需要十個字節(jié)荆秦。不過最大的節(jié)省還是來自于BLOB和TEXT字段，綁定變量的形式可以分塊傳輸力图，而無須一次性傳輸步绸。二進制協(xié)議在客戶端也可能節(jié)省很多內(nèi)存，減少了網(wǎng)絡(luò)開銷吃媒，另外瓤介，還節(jié)省了將數(shù)據(jù)從存儲原始格式轉(zhuǎn)換成文本格式的開銷。
- 僅僅是參數(shù)——而不是整個查詢語句——需要發(fā)送到服務(wù)器端赘那，所以網(wǎng)絡(luò)開銷會更小刑桑。
- MySQL在存儲參數(shù)的時候，直接將其存放到緩存中募舟，不再需要在內(nèi)存中多次復(fù)制祠斧。
綁定變量相對也更安全。無須在應(yīng)用程序中處理轉(zhuǎn)義拱礁，一則更簡單了琢锋，二則也大大減少了SQL注入和攻擊的風(fēng)險。（任何時候都不要信任用戶輸入呢灶，即使是使用綁定變量的時候吴超。）
可以只在使用綁定變量的時候才使用二進制傳輸協(xié)議。如果使用普通的mysql_query()接口則不會使用二進制傳輸協(xié)議鸯乃。還有一些客戶端讓你使用綁定變量鲸阻，先發(fā)送帶參數(shù)的綁定SQL，然后發(fā)送變量值，但是實際上鸟悴，這些客戶端只是模擬了綁定變量的接口陈辱，最后還是會直接用具體值代替參數(shù)后，再使用mysql_query()發(fā)送整個查詢語句遣臼。
2.1 綁定變量的優(yōu)化
對使用綁定變量的SQL性置，MySQL能夠緩存其部分執(zhí)行計劃，如果某些執(zhí)行計劃需要根據(jù)傳入的參數(shù)來計算時揍堰，MySQL就無法緩存這部分的執(zhí)行計劃。根據(jù)優(yōu)化器什么時候工作嗅义，可以將優(yōu)化分為三類屏歹。在本書編寫的時候，下面的三點是適用的之碗。
- 在準(zhǔn)備階段
服務(wù)器解析SQL語句蝙眶，移除不可能的條件，并且重寫子查詢褪那。 - 在第一次執(zhí)行的時候
如果可能的話幽纷，服務(wù)器先簡化嵌套循環(huán)的關(guān)聯(lián)，并將外關(guān)聯(lián)轉(zhuǎn)化成內(nèi)關(guān)聯(lián)博敬。 - 在每次SQL語句執(zhí)行時
服務(wù)器做如下事情：
1)過濾分區(qū)友浸。
2)如果可能的話，盡量移除COUNT()偏窝、MIN()和MAX()收恢。
3)移除常數(shù)表達式。
4)檢測常量表祭往。
5)做必要的等值傳播伦意。
6)分析和優(yōu)化ref、range和索引優(yōu)化等訪問數(shù)據(jù)的方法硼补。
7)優(yōu)化關(guān)聯(lián)順序驮肉。
2.2 SQL接口的綁定變量
MySQL支持了SQL接口的綁定變量。不使用二進制傳輸協(xié)議也可以直接以SQL的方式使用綁定變量已骇。下面案例展示了如何使用SQL接口的綁定變量：
當(dāng)服務(wù)器收到這些SQL語句后离钝，先會像一般客戶端的鏈接庫一樣將其翻譯成對應(yīng)的操作。
這意味著你無須使用二進制協(xié)議也可以使用綁定變量疾捍。
正如你看到的奈辰，比起直接編寫的SQL語句，這里的語法看起來有一些怪怪的乱豆。那么奖恰，這種寫法實現(xiàn)的綁定變量到底有什么優(yōu)勢呢？
最主要的用途就是在存儲過程中使用。在MySQL 5.0版本中瑟啃，就可以在存儲過程中使用綁定變量论泛，其語法和前面介紹的SQL接口的綁定變量類似。這意味蛹屿，可以在存儲過程中構(gòu)建并執(zhí)行“動態(tài)”的SQL語句屁奏，這里的“動態(tài)”是指可以通過靈活地拼接字符串等參數(shù)構(gòu)建SQL語句。例如错负，下面的示例存儲過程中可以針對某個數(shù)據(jù)庫執(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)用這個存儲過程：
mysql> CALL optimize_tables('sakila')
另一種實現(xiàn)存儲過程中循環(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án)檢查兩次循環(huán)條件坟瓢。在這個例子中，因為循環(huán)條件檢查的是一個整數(shù)判斷犹撒，并不會有什么性能問題折联，如果循環(huán)的判斷條件非常復(fù)雜的話，則需要注意這兩者的區(qū)別识颊。
像這樣使用SQL接口的綁定變量拼接表名和庫名是很常見的诚镰，這樣的好處是無須使用任何參數(shù)就能完成SQL語句。而庫名和表名都是關(guān)鍵字祥款，在二進制協(xié)議的綁定變量中是不能將這兩部分參數(shù)化的清笨。另一個經(jīng)常需要動態(tài)設(shè)置的就是LIMIT子句，因為二進制協(xié)議中也無法將這個值參數(shù)化刃跛。
另外抠艾，編寫存儲過程時，SQL接口的綁定變量通车煳保可以很大程度地幫助我們調(diào)試綁定變量跌帐，如果不是在存儲過程中，SQL接口的綁定變量就不是那么有用了绊率。因為SQL接口的綁定變量谨敛，它既沒有使用二進制傳輸協(xié)議，也沒有能夠節(jié)省帶寬滤否，相反還總是需要增加至少一次額外網(wǎng)絡(luò)傳輸才能完成一次查詢脸狸。所有只有在某些特殊的場景下SQL接口的綁定變量才有用，比如當(dāng)SQL語句非常非常長藐俺，并且需要多次執(zhí)行的時候炊甲。
2.3 綁定變量的限制
關(guān)于綁定變量的一些限制和注意事項如下：
- 綁定變量是會話級別的，所以連接之間不能共用綁定變量句柄欲芹。同樣地卿啡，一旦連接斷開，則原來的句柄也不能再使用了菱父。（連接池和持久化連接可以在一定程度上緩解這個問題颈娜。）
- 在MySQL 5.1版本之前剑逃，綁定變量的SQL是不能使用查詢緩存的。
- 并不是所有的時候使用綁定變量都能獲得更好的性能官辽。如果只是執(zhí)行一次SQL蛹磺，那么使用綁定變量方式無疑比直接執(zhí)行多了一次額外的準(zhǔn)備階段消耗，而且還需要一次額外的網(wǎng)絡(luò)開銷同仆。（要正確地使用綁定變量萤捆，還需要在使用完成后，釋放相關(guān)的資源俗批。）
- 當(dāng)前版本下俗或，還不能在存儲函數(shù)中使用綁定變量（但是存儲過程中可以使用）。
- 如果總是忘記釋放綁定變量資源扶镀，則在服務(wù)器端很容易發(fā)生資源“泄漏”蕴侣。綁定變量 SQL總數(shù)的限制是一個全局限制，所以某一個地方的錯誤可能會對所有其他的線程都產(chǎn)生影響臭觉。
- 有些操作，如BEGIN辱志，無法在綁定變量中完成蝠筑。
不過使用綁定變量最大的障礙可能是：它是如何實現(xiàn)以及原理是怎樣的，這兩點很容易讓人困惑揩懒。有時什乙，很難解釋如下三種綁定變量類型之間的區(qū)別是什么：
- 客戶端模擬的綁定變量
客戶端的驅(qū)動程序接收一個帶參數(shù)的SQL，再將指定的值帶入其中已球，最后將完整的查詢發(fā)送到服務(wù)器端臣镣。 - 服務(wù)器端的綁定變量
客戶端使用特殊的二進制協(xié)議將帶參數(shù)的字符串發(fā)送到服務(wù)器端，然后使用二進制協(xié)議將具體的參數(shù)值發(fā)送給服務(wù)器端并執(zhí)行智亮。 - SQL接口的綁定變量
客戶端先發(fā)送一個帶參數(shù)的字符串到服務(wù)器端忆某，這類似于使用PREPARE的SQL語句，然后發(fā)送設(shè)置參數(shù)的SQL阔蛉，最后使用EXECUTE來執(zhí)行SQL弃舒。所有這些都使用普通的文本傳輸協(xié)議。
參考:
- 《高性能MySQL》
