前兩天一個(gè)準(zhǔn)備去中國(guó)郵政面試Java崗的朋友回來(lái)跟我復(fù)盤(pán)說(shuō)面試官盯著MySQL追著問(wèn)聚簇索引和二級(jí)索引的區(qū)別、回表是什么、聯(lián)合索引最左前綴最后落到一句——“你知道ICP嗎索引條件下推講講原理和應(yīng)用場(chǎng)景。”他當(dāng)時(shí)有點(diǎn)懵名字聽(tīng)過(guò)EXPLAIN里見(jiàn)過(guò)Using index condition但真要講清楚“條件下推到底推給了誰(shuí)、推下去之后發(fā)生了什么”就講不利索了。這其實(shí)是很多人的通病會(huì)用EXPLAIN但沒(méi)把Server層和存儲(chǔ)引擎層的分工想透遇到“索引條件下推”這種偏底層的優(yōu)化就露餡。這篇就把ICP徹底拆開(kāi)。先說(shuō)清楚它解決什么問(wèn)題再一步步還原一次查詢?cè)凇皼](méi)有ICP”和“有ICP”兩種狀態(tài)下分別怎么干活然后給一組可以自己復(fù)現(xiàn)的實(shí)驗(yàn)最后把面試追問(wèn)方向、實(shí)戰(zhàn)里的坑和排查套路一起講完。不管你是準(zhǔn)備面試的Java開(kāi)發(fā)還是平時(shí)被慢查詢折騰得夠嗆的后端這篇都能直接拿來(lái)用。1. 面試官到底在考什么把背景先對(duì)齊1.1 MySQL執(zhí)行一次查詢誰(shuí)在干活要理解ICP第一步得先在腦子里建一張MySQL的“執(zhí)行地圖”。一條SELECT語(yǔ)句進(jìn)來(lái)要經(jīng)過(guò)連接器建立連接、權(quán)限校驗(yàn)、分析器詞法語(yǔ)法解析、優(yōu)化器決定訪問(wèn)路徑、選擇索引、執(zhí)行器調(diào)用存儲(chǔ)引擎接口最后才輪到存儲(chǔ)引擎——也就是InnoDB——去真正讀數(shù)據(jù)。這里最關(guān)鍵的分工是Server層負(fù)責(zé)“怎么查、查完再過(guò)濾”存儲(chǔ)引擎層負(fù)責(zé)“按什么方式把數(shù)據(jù)找出來(lái)”。在沒(méi)有ICP的年代取數(shù)和過(guò)濾這兩件事的邊界非常機(jī)械存儲(chǔ)引擎負(fù)責(zé)把索引定位到的記錄對(duì)應(yīng)的完整行撈出來(lái)交給Server層Server層再拿著每一行逐條去套WHERE條件。問(wèn)題就出在這個(gè)“先撈上來(lái)、再判斷”的流程上。如果一條二級(jí)索引能定位出1萬(wàn)條記錄但真正滿足完整WHERE條件的只有800條那9200次回表和后續(xù)的逐行判斷都是純浪費(fèi)。ICP要干的就是把這種浪費(fèi)壓縮到最低。1.2 從“回表”說(shuō)起回表這個(gè)詞面試幾乎必考。InnoDB的表是聚簇索引結(jié)構(gòu)主鍵索引的葉子節(jié)點(diǎn)直接存整行數(shù)據(jù)而二級(jí)索引的葉子節(jié)點(diǎn)只存“索引列 主鍵值”。你用二級(jí)索引查數(shù)據(jù)時(shí)得先在二級(jí)索引里找到主鍵值再拿著主鍵回聚簇索引取整行這個(gè)過(guò)程就叫回表官方也叫書(shū)簽查找?；乇硎怯姓鎸?shí)代價(jià)的它是隨機(jī)IO為主的操作命中的行越多回表次數(shù)越多慢查詢的概率越大。很多業(yè)務(wù)系統(tǒng)的慢SQL根子不在“沒(méi)建索引”而在“建了索引但回表次數(shù)太多”。ICP正是針對(duì)“二級(jí)索引 回表”這個(gè)組合做的優(yōu)化。它能在回表發(fā)生之前就把一部分WHERE條件先消化掉。換句話說(shuō)ICP讓“過(guò)濾”這件事提前到了存儲(chǔ)引擎遍歷二級(jí)索引的時(shí)候。1.3 面試官問(wèn)ICP實(shí)際在問(wèn)三層?xùn)|西中國(guó)郵政這類業(yè)務(wù)系統(tǒng)大量訂單、物流、賬單查詢單表幾千萬(wàn)行非常正常查詢性能直接決定線上穩(wěn)不穩(wěn)。面試官問(wèn)ICP表面是考一個(gè)優(yōu)化名詞實(shí)際在考察三層能力第一層知不知道回表原理能不能畫(huà)出二級(jí)索引和聚簇索引的結(jié)構(gòu)差異。第二層知不知道Server層和存儲(chǔ)引擎層的邊界懂不懂“下推”這個(gè)動(dòng)作意味著職責(zé)轉(zhuǎn)移。第三層能不能結(jié)合實(shí)際場(chǎng)景說(shuō)清楚ICP的收益、限制以及和覆蓋索引、MRR這些優(yōu)化的取舍。所以別把ICP當(dāng)孤立名詞背。你如果能從“回表次數(shù)”這個(gè)指標(biāo)切入把收益量化出來(lái)再把邊界條件講明白這道題基本就穩(wěn)了。2. ICP原理拆解一次查詢的前后對(duì)比2.1 沒(méi)有ICP時(shí)一次查詢的完整流程假設(shè)有張員工表二級(jí)索引建在(last_name, age)上查詢是SELECT * FROM employees WHERE last_name 王 AND age 20;聯(lián)合索引是last_name在前、age在后所以last_name王能用到索引的等值定位age 20是索引內(nèi)第二列的范圍條件同樣能參與索引掃描。MySQL 5.6之前這條SQL的執(zhí)行流程是這樣的Server層通過(guò)優(yōu)化器確定訪問(wèn)路徑走idx_last_age索引定位到所有l(wèi)ast_name王的索引記錄。InnoDB存儲(chǔ)引擎按這個(gè)范圍逐條掃描二級(jí)索引拿到每條索引記錄里的主鍵值。對(duì)每一條索引記錄存儲(chǔ)引擎都要拿著主鍵回聚簇索引把完整行讀出來(lái)。完整行返回給Server層Server層再判斷age 20是否成立成立則進(jìn)結(jié)果集不成立就丟棄。這個(gè)流程里age 20雖然涉及的是索引列但存儲(chǔ)引擎完全“看不見(jiàn)”它只會(huì)機(jī)械地把所有l(wèi)ast_name王的行都撈一遍。假設(shè)表里有8萬(wàn)條姓王的員工其中8千條年齡小于等于20那就意味著要回表8萬(wàn)次、向Server層傳8萬(wàn)行最后只留下8千行。7萬(wàn)多次回表和接近8萬(wàn)行的傳輸全部白費(fèi)。2.2 有ICP時(shí)流程發(fā)生了哪些變化MySQL 5.6引入ICP之后同樣的查詢變成這樣Server層在生成執(zhí)行計(jì)劃時(shí)發(fā)現(xiàn)age 20這個(gè)條件只涉及索引列age在idx_last_age里于是把這個(gè)條件下推給存儲(chǔ)引擎。InnoDB掃描二級(jí)索引記錄時(shí)每掃到一條先做兩個(gè)判斷l(xiāng)ast_name是否等于 王并且age是否小于等于20。只有兩個(gè)條件都滿足的索引記錄才被允許回表取完整行。最后返回給Server層的是已經(jīng)過(guò)了一輪預(yù)篩選的數(shù)據(jù)數(shù)量大幅減少。前后的數(shù)據(jù)流對(duì)比非常直觀過(guò)濾動(dòng)作從“Server層拿到完整行之后”提前到了“存儲(chǔ)引擎遍歷索引記錄時(shí)”?；乇泶螖?shù)從8萬(wàn)次降到8千次Server層需要處理的行數(shù)也跟著降了一個(gè)量級(jí)。在數(shù)據(jù)量大、篩選率高的場(chǎng)景下這就是數(shù)量級(jí)的差別。對(duì)比項(xiàng)無(wú)ICP有ICP索引掃描范圍所有l(wèi)ast_name王的索引記錄同樣范圍回表次數(shù)約8萬(wàn)次約8千次傳給Server層的行數(shù)約8萬(wàn)行約8千行過(guò)濾發(fā)生位置Server層回表之后存儲(chǔ)引擎層回表之前2.3 為什么能在二級(jí)索引上直接判斷條件這里有個(gè)關(guān)鍵點(diǎn)二級(jí)索引的葉子節(jié)點(diǎn)里不光有索引列還帶著主鍵值。也就是說(shuō)存儲(chǔ)引擎在掃描二級(jí)索引時(shí)手上已經(jīng)握有這條索引記錄的全部索引列值last_name、age以及主鍵id。正因?yàn)樗饕涗洷旧頂y帶了這些信息age 20這種只依賴索引列的條件就不需要回表看完整行才能判斷。存儲(chǔ)引擎在索引掃描過(guò)程中直接看一眼age字段的值就行了。所以ICP能成立底層靠的就是二級(jí)索引的存儲(chǔ)結(jié)構(gòu)本身。如果條件里混入了非索引列比如再加一個(gè)city 上海而city不在idx_last_age里那這個(gè)條件就下推不了。引擎只能先回表拿到完整行再判斷city。這也解釋了為什么ICP不是萬(wàn)能的——它能推下去的條件必須是在索引上就能算出答案的條件。2.4 ICP生效的硬性條件根據(jù)官方文檔和實(shí)際驗(yàn)證ICP要生效得同時(shí)滿足這些條件訪問(wèn)方法為range、ref、eq_ref或index中的一種也就是查詢確實(shí)走了索引掃描而不是全表掃描。表引擎必須是InnoDB或MyISAM實(shí)際生產(chǎn)里基本就是InnoDB。被下推的條件必須只涉及當(dāng)前表的索引列不能摻雜其他表的列。MySQL 5.6及以上版本且優(yōu)化器開(kāi)關(guān)index_condition_pushdown為on默認(rèn)就是on。條件匹配引擎支持的操作類型。等值、范圍、BETWEEN、LIKE前綴匹配這些通常都可以。2.5 哪些場(chǎng)景ICP幫不上忙聚簇索引回表場(chǎng)景如果查詢走的是主鍵索引索引記錄本身就是完整的行根本不存在回表這個(gè)動(dòng)作ICP自然沒(méi)有用武之地。條件含非索引列比如索引是(name, age)條件里還帶address xxxaddress不在索引里這個(gè)條件只能在回表后判斷。條件引用其他表的列多表關(guān)聯(lián)時(shí)涉及另一張表字段的條件不能下推給當(dāng)前表的存儲(chǔ)引擎。條件難以在索引層判斷對(duì)索引列使用函數(shù)如SUBSTR(name,1,1)王、類型不匹配導(dǎo)致隱式轉(zhuǎn)換、某些NOT條件和OR組合都可能破壞下推甚至直接讓整個(gè)索引失效。理解這些限制比背定義重要得多。面試時(shí)能主動(dòng)說(shuō)出“哪個(gè)條件下推不了”反而更能體現(xiàn)深度。3. 動(dòng)手驗(yàn)證ICP用EXPLAIN看真相3.1 準(zhǔn)備實(shí)驗(yàn)環(huán)境與造數(shù)據(jù)理論講完做一個(gè)能自己復(fù)現(xiàn)的實(shí)驗(yàn)。我這里用的是MySQL 8.05.6之后都支持先建一張表CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT, emp_no VARCHAR(20), last_name VARCHAR(50), age INT, city VARCHAR(50), PRIMARY KEY (id), KEY idx_last_age (last_name, age) ) ENGINEInnoDB;造點(diǎn)數(shù)據(jù)用存儲(chǔ)過(guò)程插10萬(wàn)行重點(diǎn)是讓last_name王的數(shù)據(jù)足夠多對(duì)比效果才明顯DROP PROCEDURE IF EXISTS init_data; DELIMITER $$ CREATE PROCEDURE init_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i 100000 DO INSERT INTO employees (emp_no, last_name, age, city) VALUES ( CONCAT(EMP, LPAD(i, 6, 0)), IF(i % 100 80, 王, 李), 18 (i % 30), IF(i % 2 0, 上海, 北京) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL init_data();這個(gè)造數(shù)方式故意讓姓王的比例占到80%也就是大約8萬(wàn)行年齡分布在18到47歲。這樣便于看到ICP的篩選收益。實(shí)際業(yè)務(wù)里篩選率可能沒(méi)這么夸張但實(shí)驗(yàn)效果一目了然。3.2 對(duì)比實(shí)驗(yàn)開(kāi)關(guān)ICP前后先保持默認(rèn)開(kāi)關(guān)執(zhí)行查詢并看執(zhí)行計(jì)劃EXPLAIN SELECT * FROM employees WHERE last_name 王 AND age 20;在MySQL 8.0上Extra列會(huì)顯示Using index condition代表ICP生效。然后再把優(yōu)化器開(kāi)關(guān)關(guān)掉模擬5.6之前的行為SET optimizer_switch index_condition_pushdownoff; EXPLAIN SELECT * FROM employees WHERE last_name 王 AND age 20; SET optimizer_switch index_condition_pushdownon;注意SET是會(huì)話級(jí)的不會(huì)影響其他連接但測(cè)完記得恢復(fù)。關(guān)掉ICP后執(zhí)行計(jì)劃里key仍然是idx_last_age但Extra列從Using index condition變成了Using where。這個(gè)變化就是核心證據(jù)同一個(gè)索引、同一個(gè)條件ICP開(kāi)與關(guān)只影響過(guò)濾發(fā)生的層次不影響訪問(wèn)路徑的選擇。很多人在面試?yán)镏v不清的“下推”用這兩條EXPLAIN一對(duì)比就非常直觀。3.3 結(jié)果解讀與rows列除了Extra列還可以看rows列。ICP開(kāi)啟時(shí)優(yōu)化器估算的rows通常會(huì)小一些關(guān)閉ICP后rows估算會(huì)變大。rows雖然是估算值但趨勢(shì)能說(shuō)明問(wèn)題ICP讓優(yōu)化器認(rèn)為“需要回表的行數(shù)”大大減少。再看實(shí)際效果。我保持SELECT *讓回表必然發(fā)生在10萬(wàn)行、姓王8萬(wàn)行的數(shù)據(jù)上分別跑ICP開(kāi)啟age 20在索引層過(guò)濾實(shí)際回表的行大約8千行。ICP關(guān)閉先回表取回所有8萬(wàn)行姓王的記錄再在Server層過(guò)濾年齡回表次數(shù)直接多出約10倍。服務(wù)端狀態(tài)變量也能看出差異。運(yùn)行查詢后對(duì)比Handler_read_rnd、Handler_read_secondary等值ICP開(kāi)啟時(shí)回表相關(guān)的讀取量明顯下降。如果你手頭環(huán)境方便可以用FLUSH STATUS配合SHOW STATUS LIKE Handler_read%實(shí)測(cè)。正是因?yàn)榛乇泶螖?shù)和Server層接收行數(shù)同時(shí)下降ICP的效果才這么明顯。如果你的SQL必須回表比如SELECT *ICP的價(jià)值最大如果你查詢的字段全在索引里那連回表都不需要直接走覆蓋索引那是另一個(gè)故事了。3.4 別把Using index condition和Using index搞混這里必須說(shuō)一個(gè)絕大多數(shù)初級(jí)開(kāi)發(fā)都會(huì)踩的誤區(qū)Extra列里出現(xiàn)Using index和Using index condition是兩種完全不同的優(yōu)化。Using index表示當(dāng)前查詢用到的所有字段都從索引里取得不需要回表這叫覆蓋索引。名字里的“index”側(cè)重“索引覆蓋”。Using index condition表示查詢需要回表取完整行但部分過(guò)濾條件被下推到了存儲(chǔ)引擎在索引掃描階段提前篩掉了不滿足條件的記錄?！癱ondition”是重點(diǎn)代表“條件下推”。Using where表示條件都在Server層完成過(guò)濾ICP沒(méi)參與或沒(méi)法參與。面試時(shí)能把這個(gè)區(qū)分講清楚會(huì)比單純背“Using index condition代表ICP”高一個(gè)檔次。不少文章把Using index condition說(shuō)成“索引覆蓋”這是完全錯(cuò)誤的要小心辨別。4. 實(shí)戰(zhàn)中的經(jīng)驗(yàn)與坑位4.1 典型受益場(chǎng)景聯(lián)合索引的第二列范圍過(guò)濾ICP最典型的受益場(chǎng)景就是聯(lián)合索引里第一列等值、第二列范圍過(guò)濾。比如索引(last_name, age)查詢WHERE last_name王 AND age BETWEEN 25 AND 35。沒(méi)有ICP時(shí)age的過(guò)濾發(fā)生在Server層存儲(chǔ)引擎要把所有姓王的記錄都回表有ICP時(shí)age在索引掃描時(shí)就過(guò)濾掉了。所以建索引時(shí)第二列、第三列不是擺設(shè)。只要查詢條件能落在索引列上哪怕不是最左前綴的等值部分ICP也能幫你省回表。這個(gè)認(rèn)知直接影響索引設(shè)計(jì)選擇度高的列放前面篩選率高的范圍條件放后面配合ICP可以大幅降低回表壓力。4.2 另一個(gè)受益場(chǎng)景LIKE前綴匹配后的再過(guò)濾第二個(gè)常見(jiàn)場(chǎng)景是模糊查詢。比如索引建在(name, age)上查詢WHERE name LIKE 張% AND age 20。age不在索引里但這不影響name LIKE 張%走索引的前綴掃描同時(shí)如果LIKE后面還有可下推的索引列條件比如name LIKE %三只要name還在索引上MySQL也可能把這個(gè)后綴條件下推在索引記錄層就過(guò)濾掉一批。實(shí)戰(zhàn)建議遇到前綴模糊查詢盡量讓能被索引判斷的條件和索引列對(duì)齊。比如“姓名以張開(kāi)頭年齡小于某值”這種組合只要age在索引里ICP通常能幫你省掉一大片回表。這個(gè)場(chǎng)景在會(huì)員檢索、商品篩選里很常見(jiàn)。4.3 坑點(diǎn)函數(shù)和隱式轉(zhuǎn)換讓ICP失效這里展開(kāi)說(shuō)一個(gè)最容易踩的坑。索引列上套了函數(shù)比如SELECT * FROM employees WHERE LEFT(last_name, 1) 王 AND age 20;LEFT(last_name, 1)對(duì)索引列做了函數(shù)運(yùn)算MySQL沒(méi)法用正常的B樹(shù)結(jié)構(gòu)定位這個(gè)條件基本就跟索引告別了自然也沒(méi)有ICP可言。另一個(gè)高發(fā)場(chǎng)景是隱式類型轉(zhuǎn)換索引列是varchar傳入數(shù)字或者索引列是int傳入字符串都可能讓優(yōu)化器放棄用這個(gè)條件和索引做匹配。要避免這類問(wèn)題第一原則是讓索引列“裸奔”——不要在索引列上套函數(shù)、不要做類型轉(zhuǎn)換、不要做加減乘除運(yùn)算。字段設(shè)計(jì)時(shí)也要注意類型統(tǒng)一應(yīng)用層傳參保持類型一致。4.4 與覆蓋索引的取舍什么時(shí)候別指望ICPICP雖然好但它只是減少了回表次數(shù)并沒(méi)有消滅回表。如果你的查詢里回表是最大瓶頸比起依賴ICP更徹底的做法是建立覆蓋索引——讓查詢的所有字段都在索引里把回表整個(gè)取消。比如固定查詢SELECT last_name, age FROM employees WHERE last_name王 AND age 20如果建了覆蓋(last_name, age)的索引Extra會(huì)顯示Using index回表次數(shù)直接歸零比ICP更極致。但覆蓋索引是有代價(jià)的索引要存儲(chǔ)更多字段寫(xiě)放大更大索引體積更大插入更新更慢。所以取舍原則是查詢字段固定且量少、性能要求高優(yōu)先覆蓋索引查詢字段多而雜比如SELECT *只能靠ICP盡量減少回表。面試中能把“ICP是減量覆蓋索引是清零”這個(gè)對(duì)比說(shuō)出來(lái)絕對(duì)加分。5. 面試延伸ICP與MRR、覆蓋索引的分工5.1 MRR是ICP的鄰居別混為一談MRRMulti-Range Read多范圍讀取也是MySQL 5.6加入的優(yōu)化但它解決的是另一個(gè)問(wèn)題。二級(jí)索引回表時(shí)命中的主鍵順序通常是雜亂的回表就變成了大量隨機(jī)IO。MRR的做法是先把要回表的主鍵收集起來(lái)并排序再統(tǒng)一批量回表盡量把隨機(jī)IO變成順序IO。ICP和MRR經(jīng)常被放在一起問(wèn)但切入點(diǎn)完全不同ICP是“減少回表次數(shù)”MRR是“優(yōu)化回表方式”。而且MRR開(kāi)啟后需要暫存主鍵再排序會(huì)有額外的內(nèi)存或磁盤(pán)開(kāi)銷?；卮饡r(shí)用一句話總結(jié)“ICP讓引擎少回表MRR讓引擎回表更順”面試官聽(tīng)到這種精準(zhǔn)對(duì)比通常會(huì)認(rèn)可。5.2 從一條SQL看三種優(yōu)化的分工拿實(shí)驗(yàn)里的SQL來(lái)總結(jié)SELECT * FROM employees WHERE last_name 王 AND age 20;如果沒(méi)有索引全表掃描一切優(yōu)化無(wú)從談起。有了聯(lián)合索引idx_last_ageMySQL按最左前綴定位last_name王。ICP介入把a(bǔ)ge 20下推到存儲(chǔ)引擎減少回表次數(shù)。如果需求字段少且固定可以改造成覆蓋索引徹底免回表。如果回表不可避免、命中的主鍵又分散MRR可以在回表階段幫你排序聚攏。這幾層優(yōu)化不是互斥的可以同時(shí)作用于一條SQL的不同階段。面試官問(wèn)“這幾個(gè)優(yōu)化你分得清嗎”其實(shí)就是在考察你是否理解它們各自作用在哪一層。優(yōu)化手段解決什么問(wèn)題作用位置關(guān)鍵標(biāo)識(shí)ICP減少回表次數(shù)二級(jí)索引掃描階段Extra: Using index condition覆蓋索引徹底取消回表索引設(shè)計(jì)階段Extra: Using indexMRR優(yōu)化回表IO順序回表階段Using MRR可能關(guān)聯(lián)5.3 一條可直接參考的完整回答話術(shù)如果面試官當(dāng)場(chǎng)讓你講ICP可以參考這個(gè)框架控制在兩分鐘左右先給定義“索引條件下推是MySQL 5.6引入的優(yōu)化能把WHERE中涉及索引列的部分條件下推到存儲(chǔ)引擎層在掃描二級(jí)索引記錄時(shí)提前過(guò)濾。”再講場(chǎng)景和收益“比如聯(lián)合索引(last_name, age)查詢last_name王 AND age20。沒(méi)有ICP引擎得把所有姓王的記錄都回表取完整行再交給Server層過(guò)濾有了ICP引擎在二級(jí)索引上直接判斷age20只對(duì)滿足條件的記錄回表回表次數(shù)可能從幾萬(wàn)降到幾千。”再講前提“ICP主要作用于二級(jí)索引回表場(chǎng)景條件得只涉及索引列涉及非索引列、其他表列的條件沒(méi)法下推。用EXPLAIN驗(yàn)證時(shí)Extra列顯示Using index condition。”最后補(bǔ)一句深度“它和覆蓋索引不一樣覆蓋索引是徹底免回表ICP是減少回表和MRR也不一樣一個(gè)減次數(shù)一個(gè)優(yōu)化回表順序?！边@個(gè)遞進(jìn)式的回答有原理、有量化、有驗(yàn)證、有對(duì)比基本可以拿滿分。6. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄6.1 問(wèn)題一EXPLAIN里看不到Using index condition怎么辦先檢查查詢是否真的走了索引。如果type是ALL那是全表掃描ICP無(wú)從談起。再檢查條件里是否混入了非索引列是否對(duì)索引列做了函數(shù)或類型轉(zhuǎn)換。還要確認(rèn)優(yōu)化器開(kāi)關(guān)沒(méi)被全局改過(guò)SHOW VARIABLES LIKE optimizer_switch;通常index_condition_pushdownon是默認(rèn)值。如果確實(shí)被關(guān)掉了可以在會(huì)話級(jí)臨時(shí)打開(kāi)再驗(yàn)證效果。還有一個(gè)經(jīng)常被忽略的點(diǎn)如果查詢條件本身命中的行極少、回表次數(shù)本來(lái)就很小優(yōu)化器可能覺(jué)得“下推不下推收益不大”但索引生效時(shí)通常還是會(huì)顯示。6.2 問(wèn)題二MySQL版本不同ICP行為有差異嗎ICP從5.6引入5.7和8.0延續(xù)基本原理一致。但每個(gè)版本對(duì)“什么條件下推”的支持細(xì)節(jié)有細(xì)微差異個(gè)別函數(shù)和操作符在版本間的行為可能變化。實(shí)踐時(shí)最穩(wěn)妥的辦法是以當(dāng)前版本的EXPLAIN輸出為準(zhǔn)不要拿老版本的結(jié)論硬套。8.0還可以用EXPLAIN FORMATtree結(jié)合傳統(tǒng)格式看filter條件的展示更直觀。6.3 問(wèn)題三分區(qū)表能用ICP嗎InnoDB分區(qū)表在MySQL 5.6以后同樣可以用ICP。分區(qū)裁剪和ICP是兩個(gè)不同維度的優(yōu)化一個(gè)決定哪些分區(qū)可以不讀一個(gè)決定分區(qū)內(nèi)回表前怎么過(guò)濾兩者可以疊加。但分區(qū)表本身會(huì)帶來(lái)不少維護(hù)成本業(yè)務(wù)上要謹(jǐn)慎使用不要為了優(yōu)化而強(qiáng)行分區(qū)。6.4 慢查詢排查時(shí)怎么判斷是不是該依賴ICP我自己排查慢查詢的套路是這樣的分享給你第一步先看EXPLAIN的type和key確認(rèn)訪問(wèn)路徑合理。 第二步看Extra出現(xiàn)Using index condition說(shuō)明ICP已經(jīng)在幫你省回表如果大量回表并且是Using where說(shuō)明條件沒(méi)被下推可能是有非索引列參與過(guò)濾。 第三步用狀態(tài)變量或者在會(huì)話里對(duì)比關(guān)掉ICP前后的執(zhí)行耗時(shí)把收益量化出來(lái)。 第四步如果回表確實(shí)是瓶頸再考慮兩個(gè)方向要么調(diào)整索引結(jié)構(gòu)讓更多條件下推要么改造成覆蓋索引徹底免回表。這套流程我平時(shí)排查慢查詢就是這么用的十次里有九次能定位到問(wèn)題。核心思想是別只看一個(gè)點(diǎn)要把訪問(wèn)路徑、過(guò)濾層次、回表量串起來(lái)看。我個(gè)人這些年排查慢查詢最大的體會(huì)是像ICP這種優(yōu)化背概念是不值錢(qián)的真正值錢(qián)的是你能不能在EXPLAIN里認(rèn)出它、在業(yè)務(wù)SQL里預(yù)判它、在索引設(shè)計(jì)里利用它。面試被問(wèn)到時(shí)與其背得滾瓜爛熟不如拿一條真實(shí)SQL一步步講清楚“哪個(gè)條件下推了、哪次回表被省掉了、驗(yàn)證的Extra列長(zhǎng)什么樣”。最后再分享一個(gè)小技巧平時(shí)給自己留一個(gè)造數(shù)環(huán)境把今天的實(shí)驗(yàn)自己跑一遍。開(kāi)關(guān)一次index_condition_pushdown看執(zhí)行計(jì)劃的變化比看十篇原理文章都管用。下次不管是面試還是實(shí)際排查心里都有底。