?準(zhǔn)確地說(shuō)，這是一篇打臉文章，打的還是自己的臉，真疼。
舊文為什么你的SQL執(zhí)行很慢曾經(jīng)簡(jiǎn)單分析了一下SQL慢查的原因，然而，文中關(guān)于ICP的論述卻是錯(cuò)誤的。但是因?yàn)楣娞?hào)文章無(wú)法修改，所以今天，我們就再來(lái)詳細(xì)了解一下ICP。
為了方便說(shuō)明，這里先給出建表語(yǔ)句和初始化語(yǔ)句：
CREATE TABLE `t` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`a` int(10) DEFAULT NULL,
`b` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`c` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
KEY `idx_b` (`b`),
KEY `idx_a_c` (`a`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
delimiter ;;
create procedure init()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t values(i, i, cast(i as char), '12345678');
set i=i+1;
end while;
end;;
delimiter ;
call init();
ICP
ICP，全稱Index Condition Pushdown，是MySQL5.6版本推出的一項(xiàng)新特性。旨在將部分where條件的過(guò)濾從server層下放至engine層，以減少回表次數(shù)和server層對(duì)數(shù)據(jù)記錄的讀取行數(shù)，從而提高SQL的執(zhí)行效率。
為了弄清楚為什么ICP可以提高優(yōu)化SQL的執(zhí)行效率，我們先簡(jiǎn)單了解一下MySQL的整體架構(gòu)。
從大的層面來(lái)說(shuō)，MySQL可以分為兩層。上層是Server層，主要包含了MySQL一些核心功能的實(shí)現(xiàn)，比如優(yōu)化器等等；下層是Engine層，主要就是各種存儲(chǔ)引擎，負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)。如下圖：
其中Server層又可以進(jìn)一步細(xì)分如下：
連接器：主要是負(fù)責(zé)建立連接、權(quán)限校驗(yàn)等等工作，解決who的問(wèn)題
分析器：主要是負(fù)責(zé)語(yǔ)法分析和詞法分析，解決what的問(wèn)題
優(yōu)化器：主要是負(fù)責(zé)執(zhí)行計(jì)劃的確定，解決how的問(wèn)題
執(zhí)行器：根據(jù)優(yōu)化器確定的執(zhí)行計(jì)劃向Engine層發(fā)起數(shù)據(jù)請(qǐng)求，返回結(jié)果
下圖給出了在開啟ICP前后SQL的執(zhí)行示意圖。
舉個(gè)例子，對(duì)于這樣一條SQL語(yǔ)句：
select * from t2 where a > 9000 and c like '%1'; 關(guān)閉ICP時(shí)，其執(zhí)行計(jì)劃如下：
開啟ICP時(shí)，其執(zhí)行計(jì)劃則變?yōu)?，Extra字段出現(xiàn)了Using index condition的提示，表示使用的ICP優(yōu)化：
對(duì)應(yīng)的慢查日志分別為：
可以看到，關(guān)閉ICP時(shí)，Engine層最終返回了1000條記錄，但最終結(jié)果集為空，而開啟ICP時(shí)，Engine層返回的結(jié)果集就已經(jīng)為空了，減少了1000次不必要的回表操作和Server層對(duì)這1000條記錄的讀取操作。而這其中的原因就在于前者在Engine層只應(yīng)用了a > 9000的條件， c like '%1'這個(gè)條件是放到Server層比較的，開啟ICP之后，Engine層則是同時(shí)應(yīng)用了 a > 9000 and c like '%1'的條件。
MRR
既然談到了ICP，那就順便再聊一聊同樣是MySQL5.6版本推出的另外一項(xiàng)新特性：MRR(Multi-Range Read)。MRR的主要目的是減少對(duì)主鍵索引的隨機(jī)訪問(wèn)，將隨機(jī)訪問(wèn)轉(zhuǎn)化為較為順序的數(shù)據(jù)訪問(wèn)。
下圖給出了開啟MRR前后的SQL執(zhí)行示意圖：
簡(jiǎn)單解釋一下，通常情況下，根據(jù)二級(jí)索引過(guò)濾出來(lái)的數(shù)據(jù)是按照二級(jí)索引有序，因此回表的時(shí)候也是按照二級(jí)索引的順序回表的。然而，由于主鍵索引的順序和二級(jí)索引的順序通常不一樣，因此這就會(huì)造成對(duì)主鍵索引的隨機(jī)訪問(wèn)。為了避免這一問(wèn)題，開啟MRR后，MySQL會(huì)對(duì)二級(jí)索引過(guò)濾之后的結(jié)果先按照主鍵進(jìn)行排序，然后再按照這個(gè)按主鍵有序的列表進(jìn)行回表操作。
舉個(gè)例子，假設(shè)我們的二級(jí)索引樹如下，現(xiàn)在我們要查詢二級(jí)索引字段大于1的數(shù)據(jù)：
在未開啟MRR的條件下，由于是按照二級(jí)索引順序回表，所以回表時(shí)的主鍵依次為：40、20、10、60、30、50，這就造成了對(duì)主鍵索引的隨機(jī)訪問(wèn)。
開啟MRR之后，按照主鍵排序，這時(shí)回表的主鍵順序就變成了：10、20、30、40、50、60，從隨機(jī)訪問(wèn)變成了順序訪問(wèn)。
在MySQL5.6之后的版本中，MRR都是默認(rèn)開啟的，但是與ICP只有一個(gè)參數(shù)控制不同，MRR有兩個(gè)參數(shù)共同控制:
其中mrr用于控制是否開啟mrr優(yōu)化，但開啟mrr優(yōu)化并不意味著所有符合條件的SQL語(yǔ)句都會(huì)采用此優(yōu)化，因?yàn)榕判虿僮饕彩怯写鷥r(jià)的，如果優(yōu)化器評(píng)估認(rèn)為排序代價(jià)太大，也可能會(huì)放棄mrr優(yōu)化。因此如果你想一直啟用mrr功能，則需要這么設(shè)置：mrr=on,mrrcostbased=off.
全文完(?′?‵?)
