在講ICP和MRR之前挖函，我們先了解一下MySQL的架構(gòu)鳖链。于本文的重點(diǎn)并不在架構(gòu)細(xì)節(jié)上，所以讓我們直接看關(guān)鍵部分暴氏，存儲(chǔ)引擎作為單獨(dú)的一層，是連接底層存儲(chǔ)系統(tǒng)和上層server其他部分的橋梁绣张，而MySQL對(duì)存儲(chǔ)引擎的抽象也極大地豐富了其可擴(kuò)展性答渔。
ICP
我們平時(shí)需要查詢和寫入的數(shù)據(jù)最終的存儲(chǔ)介質(zhì)都是底層的文件系統(tǒng)，而數(shù)據(jù)的傳輸必然要經(jīng)過(guò)上面說(shuō)到的兩個(gè)部分侥涵。就好像我們將單體服務(wù)切分成微服務(wù)一樣沼撕，如果我們需要從另一個(gè)服務(wù)取數(shù)據(jù)宋雏，需要由對(duì)方服務(wù)查詢DB，然后通過(guò)網(wǎng)絡(luò)再傳輸給自己务豺。查詢DB的過(guò)程也是一種網(wǎng)絡(luò)傳輸磨总，所以這里的數(shù)據(jù)其實(shí)經(jīng)過(guò)了兩次網(wǎng)絡(luò)傳輸，相比直接查詢查詢DB增加了一倍的網(wǎng)絡(luò)消耗笼沥。這是微服務(wù)切分后帶來(lái)的代價(jià)蚪燕，那有沒有其他的途徑可以減少這種消耗呢？
如果我實(shí)際需要10條數(shù)據(jù)奔浅，但每次都獲取100條數(shù)據(jù)后自己執(zhí)行過(guò)濾馆纳，那兩次網(wǎng)絡(luò)傳輸?shù)亩际?00條；如果由對(duì)方服務(wù)進(jìn)行過(guò)濾汹桦，則其中一次網(wǎng)絡(luò)只需傳輸10條厕诡；更甚者，如果對(duì)方查DB直接只查10條营勤，那兩次網(wǎng)絡(luò)傳輸都是10條灵嫌。
ICP(Index Condition Pushdown)就是利用了類似的原理來(lái)達(dá)到性能優(yōu)化的。舉個(gè)??
有這么一張表employees葛作，其中first_name和last_name建立了一個(gè)聯(lián)合索引寿羞。
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `birth` (`birth_date`),
KEY `first_last` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
執(zhí)行以下語(yǔ)句，MySQL會(huì)怎么處理呢赂蠢？
EXPLAIN SELECT * FROM employees WHERE first_name = "Georgi" AND last_name LIKE "Maliniak%"
在5.6版本以前绪穆，MySQL會(huì)使用first_last這個(gè)聯(lián)合索引，然后存儲(chǔ)引擎根據(jù)first_name = "Georgi"這個(gè)過(guò)濾條件查詢相應(yīng)的記錄虱岂，返回上層的server后再根據(jù)last_name LIKE "Maliniak%"這個(gè)條件做過(guò)濾玖院，最后將符合條件的數(shù)據(jù)返回給用戶。也就是說(shuō)第岖，last_name LIKE "Maliniak%"這個(gè)條件是經(jīng)歷了兩次數(shù)據(jù)傳輸后才起的作用难菌，查詢的掃描行數(shù)就是first_name = "Georgi"的所有記錄，跟我們一開始舉的例子很像蔑滓，是存在優(yōu)化空間的郊酒。
5.6版本及以后呢，MySQL不這么干了键袱。走索引的時(shí)候雖然拿不到完整的數(shù)據(jù)文件燎窘，但是last_name就是索引中的字段，這個(gè)信息其實(shí)在first_last這個(gè)聯(lián)合索引文件中是存在的蹄咖，所以提前過(guò)濾完全可以做到褐健。MySQL將where條件下推到存儲(chǔ)引擎，存儲(chǔ)引擎盡可能的根據(jù)條件中的信息查詢記錄澜汤，最終存儲(chǔ)引擎獲取到的就是符合first_name = "Georgi" AND last_name LIKE "Maliniak%"的記錄蚜迅，這樣在最底層就避免了額外的數(shù)據(jù)傳輸舵匾。
ICP是可以通過(guò)開關(guān)控制的，而且它有特別的適用場(chǎng)景慢叨，并不是無(wú)腦能用的：
- ICP適用于連接類型為range，ref务蝠，eq_ref和ref_or_null的全表掃描拍谐，連接類型就explain輸出中的type字段；
- InnoDB和MyISAM都支持ICP馏段；
- ICP適用于二級(jí)索引轩拨，不適用于聚簇索引(一般就是主鍵)，因?yàn)榫鄞厮饕娜~子節(jié)點(diǎn)就是數(shù)據(jù)記錄院喜，在存在緩沖區(qū)的情況下亡蓉，ICP起不到啥作用；
- 二級(jí)索引如果含有虛擬列的話不支持ICP喷舀；
- 條件中如果用到了子查詢砍濒，那不會(huì)被下推；
默認(rèn)情況下ICP是打開的硫麻，如果你愿意(除了測(cè)試應(yīng)該沒人會(huì)這么做的吧)爸邢，可以通過(guò)SELECT @@optimizer_switch查看，通過(guò)SET optimizer_switch = 'index_condition_pushdown=off'關(guān)閉這個(gè)功能拿愧。
MRR
MRR(Multi-Range Read)則是從硬件層面切入杠河，但目標(biāo)和ICP是一致的，就是優(yōu)化查詢效率浇辜。我們都知道對(duì)于機(jī)械硬盤來(lái)說(shuō)券敌，由于其物理結(jié)構(gòu)的限制，隨機(jī)讀的效率遠(yuǎn)低于順序讀柳洋。大致結(jié)構(gòu)長(zhǎng)下面這樣待诅，湊合看吧。
因?yàn)檗D(zhuǎn)速有上限熊镣，所以單位時(shí)間內(nèi)磁頭的移動(dòng)距離也有限制咱士，最壞的情況下，7200 RPM的機(jī)械硬盤每秒尋道120次轧钓，1次尋道讀取一條記錄序厉，那1秒內(nèi)只能返回120條記錄，完全是涼涼的節(jié)奏毕箍。而如果單次尋道能夠匹配多條記錄弛房，那效率就能成倍上升，這就是順序讀的優(yōu)勢(shì)而柑，更何況還有page cache預(yù)讀等黑科技加持文捶，順序讀和隨機(jī)讀簡(jiǎn)直就像跑車和自行車荷逞。
說(shuō)了這么多好像跟MRR沒太大關(guān)系，我們想象一種場(chǎng)景：通過(guò)二級(jí)索引查詢數(shù)據(jù)庫(kù)記錄粹排，此時(shí)不管是MyISAM還是InnoDB的存儲(chǔ)引擎(假設(shè)無(wú)法用到覆蓋索引)种远，都需要一個(gè)回表操作，而二級(jí)索引的順序和數(shù)據(jù)記錄的順序可能完全不一致顽耳，這次查詢的流程就像下面這張圖一樣(來(lái)源于MariaDB官網(wǎng)坠敷，MariaDB是MySQL的一個(gè)分支，好多功能類似)
圖中的紅線是查詢過(guò)程射富，而藍(lán)色則是磁盤的運(yùn)動(dòng)軌跡膝迎，可以看到上上下下，很明顯磁盤在做隨機(jī)讀胰耗。MRR就是一種將這樣的隨機(jī)讀轉(zhuǎn)變?yōu)轫樞蜃x的騷操作限次。我們看一下加入了MRR后整個(gè)流程會(huì)有哪些變化：
中間多了一步排序，拿InnoDB舉例柴灯，聚簇索引(一般就是主鍵索引卖漫，除非你沒設(shè)置主鍵)的葉子節(jié)點(diǎn)存放的就是數(shù)據(jù)記錄本身，也就是說(shuō)赠群，聚簇索引和數(shù)據(jù)在磁盤的存儲(chǔ)順序其實(shí)是一致的懊亡。當(dāng)我們通過(guò)二級(jí)索引拿到聚簇索引的值后，先對(duì)其進(jìn)行排序乎串，然后再去磁盤查詢店枣，這樣原來(lái)的隨機(jī)讀就被轉(zhuǎn)化為了順序讀。除了將隨機(jī)讀轉(zhuǎn)換為順序讀以外叹誉，MRR還有索引批量訪問(wèn)的優(yōu)點(diǎn)鸯两，比如對(duì)索引進(jìn)行范圍訪問(wèn)或者將索引列作為連接屬性進(jìn)行連接查詢，MRR能夠累積一定數(shù)量的查詢key然后進(jìn)行批量查詢长豁。
當(dāng)我們用EXPLAIN輸出某條SQL的執(zhí)行計(jì)劃的時(shí)候钧唐，如果MySQL判斷會(huì)用到MRR，則會(huì)在Extra這一列說(shuō)明Using MRR匠襟。
雖然MRR看上去是一個(gè)很牛逼的優(yōu)化钝侠，但也并不能做到一招鮮吃遍天。最簡(jiǎn)單的酸舍，如果我只查詢一條數(shù)據(jù)帅韧，哪需要什么排序呢？而中間的排序過(guò)程其實(shí)需要將第一步的結(jié)果放入一個(gè)臨時(shí)的緩沖區(qū)啃勉，可以將MRR看成一種空間換時(shí)間的方法忽舟。既然MRR有時(shí)候沒有優(yōu)勢(shì)，所以機(jī)智的MySQL增加了好幾個(gè)配置來(lái)處理這些情況：
- mrr——如果值為off，mrr永遠(yuǎn)不會(huì)被使用叮阅，on則有可能會(huì)被使用
- mrr_cost_based——智能決策刁品，如果發(fā)現(xiàn)mrr劃算則使用，否則不使用
- read_rnd_buffer_size——控制排序步驟緩沖區(qū)的大小浩姥，如果滿了就會(huì)先執(zhí)行一次查詢
這三個(gè)屬性值前兩個(gè)可以通過(guò)SELECT @@optimizer_switch查看挑随，第3個(gè)則可以通過(guò)SHOW VARIABLES查看。我的測(cè)試版本是5.7.29勒叠，這三個(gè)屬性的默認(rèn)值分別是on兜挨，on，262144(也就是256KB)缴饭。
