原理詳解與SQL優(yōu)化實戰(zhàn))
上周朋友去中國郵政參加Java開發(fā)崗面試回來后跟我吐槽面試官前半小時還在聊項目、聊分布式最后十分鐘突然甩來一個問題——MySQL的索引條件下推ICP你知道嗎他當(dāng)時愣了一下第一反應(yīng)是ICP不是互聯(lián)網(wǎng)內(nèi)容提供商嗎反應(yīng)過來之后磕磕絆絆講了半天也沒講利索。老實說這個知識點我在日常工作中也經(jīng)常忽略但它幾乎囊括了MySQL二級索引回表、優(yōu)化器成本、條件過濾的全部關(guān)鍵點。這篇文章就從這個面試題出發(fā)把ICP的原理、觸發(fā)條件、實操驗證方法和面試應(yīng)答思路一次講透適合正在準(zhǔn)備Java后端面試的同學(xué)也適合平時寫SQL想優(yōu)化慢查詢的開發(fā)者。1. 面試現(xiàn)場復(fù)盤面試官到底在問什么1.1 一句“ICP”背后是MySQL執(zhí)行原理的完整鏈路Java后端面試問MySQL并不稀奇但很多候選人只背了“索引失效十大場景”這種口訣碰到ICP就露餡了。實際上面試官問ICP并不是要你背誦一個概念而是要確認(rèn)你有沒有真正理解一條SQL查詢從客戶端到存儲引擎要經(jīng)歷哪些環(huán)節(jié)。MySQL整體是兩層架構(gòu)上面是Server層負(fù)責(zé)連接管理、語法解析、優(yōu)化、執(zhí)行下面是存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和讀取。在MySQL 5.6之前Server層通過存儲引擎接口拿到二級索引定位到的記錄后會在Server層把where條件里的其他過濾條件逐一判斷而在5.6之后MySQL引入了Index Condition Pushdown允許把一部分索引條件“下推”到存儲引擎層讓引擎在讀取二級索引記錄時就先做一次過濾。面試官問這個其實是想看你是否知道這條鏈路上哪一層在干活哪里能省I/O哪里不能省。這才是考察的要點。1.2 沒搞懂“回表”ICP一定講不透要理解ICP必須先理解什么是回表。InnoDB有兩種索引聚簇索引和二級索引。聚簇索引的葉子節(jié)點直接存的是整行數(shù)據(jù)主鍵的B樹就是數(shù)據(jù)本身而二級索引的葉子節(jié)點存的是“索引列的值 主鍵值”。通過二級索引查詢時第一步是掃描二級索引B樹找到匹配的索引記錄第二步再用這條索引記錄里的主鍵值回到聚簇索引去取完整行。這第二步就是“回表”。回表是一次隨機(jī)I/O尤其在二級索引匹配到很多條記錄、卻只有少數(shù)幾條真正滿足全部where條件時一次一次回表消耗就非常可觀。我平時喜歡用圖書館查書的例子來解釋圖書館有一套目錄卡片每張卡片記錄著書名、作者還有一個唯一的圖書編號。假設(shè)你想找“作者是某某、書名里有某個詞”的書目錄卡片只能定位到作者但你不知道書里具體內(nèi)容符不符合沒有ICP時你得把所有該作者的書從書庫里搬出來翻一遍再挑出符合書名的有ICP時圖書管理員直接在目錄卡片上先比對作者和書名關(guān)鍵詞明顯不符合的卡片直接淘汰剩下的才去書庫搬書。這里的“在卡片上先比對”就是索引條件下推的雛形。2. ICP的核心原理和觸發(fā)條件2.1 條件“下推”到了哪一層ICP的全稱是Index Condition Pushdown索引條件下推。所謂“下推”是指把原來在Server層執(zhí)行的where條件判斷推到存儲引擎層在引擎讀取二級索引記錄時同步判斷。但這里有個非常關(guān)鍵的前提能被下推的條件必須是“能利用二級索引記錄中的字段進(jìn)行判斷”的條件。通俗點說二級索引的葉子節(jié)點只包含索引列和主鍵如果你的過濾條件用到某個不在索引里的列存儲引擎手里根本沒有這個字段的值自然沒法提前判斷這個條件就只能老老實實留在Server層過濾。這也是ICP最容易被人誤解的地方不是所有where條件都能下推。只有索引鍵內(nèi)包含的列才能參與下推。知道了這個前提就能很好理解為什么ICP能減少回表次數(shù)引擎在二級索引上掃描時對一條索引記錄先判斷下推下來的條件滿足才拿主鍵去回表不滿足直接跳過。這樣一來回表的對象從“所有被二級索引定位到的記錄”縮小成了“先經(jīng)過索引記錄條件過濾后的記錄”。2.2 一個經(jīng)典例子看懂下推過程我們建一張員工表用聯(lián)合索引(last_name, first_name)作為例子CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, salary DECIMAL(10,2), KEY idx_name (last_name, first_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;執(zhí)行這條查詢SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000;聯(lián)合索引idx_name里有l(wèi)ast_name和first_name。last_name Smith可以直接用來做索引范圍定位first_name LIKE %John%由于是前導(dǎo)通配符沒法用來縮小索引掃描范圍但它仍然是索引中的字段salary完全不在索引里。沒有ICP時MySQL的做法是先在二級索引里找到所有l(wèi)ast_name Smith的記錄然后一條一條回表把完整行返回給Server層再由Server層過濾first_name LIKE %John%和salary 50000。假設(shè)Smith有1000條記錄可能最后只有10條符合那么900多次回表都是白做的。啟用ICP后存儲引擎在讀取二級索引記錄時手里已經(jīng)有一條索引記錄里面包含last_name和first_name。引擎可以先用自己的first_name字段判斷LIKE條件滿足才回表不滿足的索引記錄直接扔掉。所以salary 50000沒法下推回表后還要在Server層過濾但回表次數(shù)已經(jīng)從1000次降到了比如100次。這就是ICP的核心價值在“索引定位”和“回表取數(shù)”之間多了一道攔截。2.3 哪些SQL才配觸發(fā)ICPICP不是所有SQL都適用我根據(jù)實際碰到的情況整理了觸發(fā)條件查詢必須真正使用了二級索引如果優(yōu)化器選擇全表掃描則談不上ICP。WHERE條件中的過濾列必須是當(dāng)前使用二級索引的組成部分不在索引里的列不能下推。該列在索引記錄上的判斷方式可以是等值、范圍、LIKE等但不能對索引列使用函數(shù)或表達(dá)式計算。不能用于主鍵索引因為主鍵索引是聚簇索引葉子節(jié)點已經(jīng)包含整行數(shù)據(jù)不存在“回表再過濾”的過程。系統(tǒng)參數(shù)optimizer_switch中的index_condition_pushdown必須為on這個參數(shù)從MySQL 5.6開始默認(rèn)開啟。最終是否使用ICP還要看優(yōu)化器的成本估算如果優(yōu)化器認(rèn)為全表掃描或其它執(zhí)行方式代價更低也不會用ICP。很多人會問為什么first_name LIKE %John%不能用索引定位卻能用ICP過濾這兩個不是一回事。索引定位要利用B樹的有序性前導(dǎo)通配符破壞了有序匹配所以沒法作為索引訪問條件但I(xiàn)CP只是“在二級索引記錄上做一次條件判斷”相當(dāng)于把引擎本來沒參與過濾的字段加入判斷。存儲引擎掃描到一條索引記錄它完全有能力讀取這個字段并判斷LIKE所以就能下推。這也是ICP最優(yōu)雅的地方它把索引中“不能用于定位但能用于判斷”的價值榨干了。2.4 ICP和覆蓋索引別混淆ICP的Extra顯示是Using index condition覆蓋索引的Extra顯示是Using index兩者經(jīng)常被搞混。覆蓋索引指查詢所需的所有列都能從索引中直接取得不需要回表ICP指查詢?nèi)匀恍枰乇碇皇腔乇砬跋扔盟饕涗涀隽艘坏肋^濾。一個是“完全不需要回表”一個是“減少回表次數(shù)”收益不一樣。實際優(yōu)化時如果能用覆蓋索引就不該只滿足于ICP。比如查詢字段只有l(wèi)ast_name, first_name那直接走覆蓋索引比ICP更徹底但如果查詢字段里有salary這種不在索引里的列又無法把所有字段都塞進(jìn)索引時利用ICP在回表前攔截一下往往是性價比最高的方案。3. 從建表到EXPLAIN手把手驗證ICP3.1 準(zhǔn)備測試環(huán)境和數(shù)據(jù)紙上談兵不踏實我在本機(jī)MySQL 8.0里重新驗證了一遍。先建表然后插一部分測試數(shù)據(jù)DROP TABLE IF EXISTS employees; CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, salary DECIMAL(10,2), KEY idx_name (last_name, first_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO employees(last_name, first_name, salary) VALUES (Smith,John,8000), (Smith,Johnny,12000), (Smith,John,50000), (Smith,Jane,88000), (Smith,John,120000), (Smith,Johnny,30000), (Smith,John,20000), (Smith,Mike,70000), (Smith,John,60000), (Brown,John,90000);數(shù)據(jù)量不大但足夠看出執(zhí)行計劃的差異。如果想驗證得更明顯可以用存儲過程循環(huán)插入幾十萬行把last_name隨機(jī)成20個常見姓氏first_name隨機(jī)成20個名字salary隨機(jī)。ICP在數(shù)據(jù)量大、回表成本高的時候才能體現(xiàn)性能差距。3.2 用EXPLAIN看Using index condition執(zhí)行下面這條SQL注意要在前面加EXPLAINEXPLAIN SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000\G我在本機(jī)得到的關(guān)鍵列是這些id: 1 select_type: SIMPLE table: employees type: ref possible_keys: idx_name key: idx_name key_len: 202 ref: const rows: 9 filtered: 11.11 Extra: Using index condition看幾個點。key是idx_name說明這條SQL真的走了二級索引ref是const說明last_name Smith用于等值定位rows是9優(yōu)化器估算通過last_name定位到9條索引記錄。最關(guān)鍵的Extra顯示Using index condition這就是ICP生效的標(biāo)志。filtered是11.11%代表回表之后在Server層繼續(xù)過濾剩余條件后預(yù)計還有 9 * 11.11% 約等于1條返回記錄。注意不要以為Using index condition出現(xiàn)就表示沒有salary條件了。由于salary不在索引里它仍然是在Server層過濾的所以我的測試環(huán)境里這條SQL在部分版本下Extra會同時出現(xiàn)Using index condition; Using where代表“引擎層下推了一部分條件 Server層還要繼續(xù)過濾剩余條件”。3.3 開關(guān)ICP對比執(zhí)行計劃差異為了對比我把ICP在會話級別關(guān)掉SET SESSION optimizer_switch index_condition_pushdownoff; EXPLAIN SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000\G這時Extra變成Using whererows可能不變但執(zhí)行流程變成了“拿到全部Smith的索引記錄回表再在Server層過濾first_name LIKE和salary”。測試完記得恢復(fù)SET SESSION optimizer_switch index_condition_pushdownon;如果你用的是MySQL 8.0.18以上版本還可以用EXPLAIN ANALYZE看實際執(zhí)行過程。它會把執(zhí)行計劃中每個節(jié)點消耗多少毫秒、返回多少行都打印出來其中能看到Index lookup on employees using idx_name (last_nameSmith), with index condition: (first_name like %John%)這樣的描述非常直觀。3.4 驗證時務(wù)必別踩的坑我在驗證時踩過一個最典型的坑把optimizer_switch改了重新執(zhí)行EXPLAIN發(fā)現(xiàn)Extra里始終沒有Using index condition。后來排查才發(fā)現(xiàn)那條SQL壓根沒走二級索引優(yōu)化器直接選了全表掃描。EXPLAIN里的key是NULL自然不會有ICP。所以驗證ICP之前第一件事是確認(rèn)key列非空必要時可以用FORCE INDEX(idx_name)強(qiáng)制走索引來觀察。另外生產(chǎn)環(huán)境千萬不要為了方便測試在全局把index_condition_pushdown關(guān)掉。它默認(rèn)就是優(yōu)化的關(guān)閉后只會讓本可以提前過濾的查詢回更多次表除非你是在做對比實驗否則沒必要動它。4. 常見問題與實戰(zhàn)排查4.1 為什么加了索引卻沒走ICP我在給同事排查慢SQL時最常見的原因就是“加了索引但SQL還是全表掃描”。比如對last_name單列建了索引但查詢里用了WHERE UPPER(last_name) SMITH索引列套了函數(shù)MySQL不會使用這個索引又比如統(tǒng)計信息不準(zhǔn)確優(yōu)化器判斷全表掃描成本更低。處理辦法是先看EXPLAIN的possible_keys和key確認(rèn)索引有沒有機(jī)會被用上如果統(tǒng)計信息明顯陳舊執(zhí)行ANALYZE TABLE employees;更新一下。如果實在想驗證可以用FORCE INDEX強(qiáng)制索引但要注意FORCE INDEX本身也可能選錯對象測試完就撤銷。另外有時候不是索引沒加是索引設(shè)計不合理。舉例來說如果查詢條件經(jīng)常是last_name salary但索引建成了(first_name, last_name)salary不在索引中那么where里的salary條件就無法下推只能回表后過濾。合理做法是根據(jù)業(yè)務(wù)高頻過濾條件調(diào)整聯(lián)合索引順序把常用等值條件放前面把需要過濾的字段想辦法納入索引。4.2 ICP不生效是索引設(shè)計的問題ICP并不是萬能藥我在真實項目里見過不少“以為用了ICP其實收益很小”的情況。ICP只能減少回表次數(shù)不能消除回表。如果SQL里頻繁使用SELECT *即使ICP把滿足條件的記錄從1000條篩到100條這100條仍然要回表取全部字段。此時更好的方案可能是把高頻查詢字段整理成一個覆蓋索引讓Extra變成Using index徹底避免回表。還有一類問題是優(yōu)化器沒有選對索引。表上有多個聯(lián)合索引時MySQL會估算哪個索引代價更低。ICP的過濾能力會影響估算但也可能因為另一個索引能直接覆蓋查詢就放棄了ICP。我在優(yōu)化時不會只看單條SQL而是會同時輸出整個表的索引分布結(jié)合業(yè)務(wù)查詢頻率決定刪除冗余索引減少優(yōu)化器選錯索引的概率。4.3 Java項目里怎么用好ICP這個知識ICP是個MySQL自動執(zhí)行的優(yōu)化Java代碼層面不需要做任何特殊配置也不需要改SQL。但這不代表我們沒事可做。日常用Spring Boot MyBatis開發(fā)時可以把a(bǔ)pplication.yml里的數(shù)據(jù)源連接串多配一個sessionVariablesoptimizer_switchindex_condition_pushdownon當(dāng)然這個參數(shù)默認(rèn)就是開我只是習(xí)慣顯式確認(rèn)一下。更重要的是排查慢SQL的意識。MyBatis打印SQL后我通常會復(fù)制到開發(fā)庫執(zhí)行并加EXPLAINMySQL 8.0還可以用EXPLAIN ANALYZE看真實執(zhí)行時間。如果你發(fā)現(xiàn)某個查詢走了二級索引但回表很多就要思考回表之前能不能讓引擎多用索引列做下推索引列是不是被函數(shù)包住了能不能把某些高頻查詢字段塞進(jìn)索引做成覆蓋索引ICP只是整個索引優(yōu)化鏈路里的一環(huán)它幫我們打開了“看執(zhí)行計劃”這扇門。5. 面試應(yīng)答思路與后續(xù)追問拆招5.1 一分鐘講透ICP如果面試官讓你解釋ICP可以先給一個干凈利落的版本ICP是MySQL 5.6引入的優(yōu)化全稱Index Condition Pushdown。在查詢使用二級索引時Server層會把一部分可以用索引列判斷的where條件下推到InnoDB存儲引擎存儲引擎在掃描二級索引記錄時直接判斷滿足條件的記錄才回表減少回表次數(shù)。判斷是否生效看EXPLAIN里的Extra是否顯示Using index condition。這個回答包含了版本、全稱、層級、作用、驗證手段已經(jīng)能證明你確實了解它。但要高分還得補(bǔ)一個例子。把idx_name那個例子用口述講出來last_name Smith AND first_name LIKE %John% AND salary 50000前兩個條件在索引里salary不在索引里。LIKE不能用于定位卻能在二級索引記錄上直接判斷并下推salary留在Server層過濾。這樣面試官會相信你不只是背了定義而是能在具體SQL里分析。5.2 三分鐘版本區(qū)分覆蓋索引和ICP如果面試官繼續(xù)追問“那你是不是用了ICP就不需要覆蓋索引了”這里要警惕。覆蓋索引是查詢字段全部在索引里直接返回數(shù)據(jù)不回表ICP是回表前先過濾仍然要回表。覆蓋索引的效果更徹底但對索引大小有代價索引列越多寫入成本越高。兩者各有適用場景。平時優(yōu)化時優(yōu)先看能否用覆蓋索引如果字段太多沒法全覆蓋再用ICP把回表量壓下來。還可以補(bǔ)充一個細(xì)節(jié)Extra的三個狀態(tài)別搞混。Using index是覆蓋索引Using index condition是索引條件下推Using where是Server層過濾。有時候一條SQL的Extra會同時出現(xiàn)多個說明引擎層和下推都參與了一層Server層還做了一層這反而是正?，F(xiàn)象。5.3 高頻追問拆招面試官可能會接著問為什么ICP只能用于二級索引。答案很明確主鍵索引是聚簇索引葉子節(jié)點就是整行數(shù)據(jù)讀取索引記錄時已經(jīng)拿到了所有字段不存在“先通過索引定位到主鍵再回表取數(shù)”的額外I/O所以沒有優(yōu)化空間。ICP的收益完全來自二級索引場景下“回表”這個動作。還有可能問ICP一定能提升性能嗎這個問題我會回答“不一定”。使用ICP是優(yōu)化器基于代價估算的選擇。如果last_nameSmith本身能匹配到的記錄已經(jīng)很少比如只有一兩條回表成本本來就很低ICP的收益可以忽略如果統(tǒng)計信息不準(zhǔn)確優(yōu)化器甚至可能作出相反選擇。另外ICP過濾掉大量記錄回表次數(shù)減少但二級索引掃描本身仍要讀取那些被淘汰的索引記錄所以收益大小取決于索引記錄過濾能力不能神話它。最后再分享一個小技巧驗證ICP時一定要先看EXPLAIN里的key列有沒有值。我見過很多人改了半天optimizer_switch結(jié)果SQL全表掃描Extra里壓根不會出現(xiàn)Using index condition。遇到這種情況用FORCE INDEX強(qiáng)制走索引先確認(rèn)ICP能生效再回頭審視為什么優(yōu)化器不選這個索引是統(tǒng)計信息問題還是索引設(shè)計問題。這個思路在面試后的實際項目里比單純記住ICP的流程有用得多。 MySQL索引條件下推(ICP)原理詳解與SQL優(yōu)化實戰(zhàn)
上周朋友去中國郵政參加Java開發(fā)崗面試回來后跟我吐槽面試官前半小時還在聊項目、聊分布式最后十分鐘突然甩來一個問題——MySQL的索引條件下推ICP你知道嗎他當(dāng)時愣了一下第一反應(yīng)是ICP不是互聯(lián)網(wǎng)內(nèi)容提供商嗎反應(yīng)過來之后磕磕絆絆講了半天也沒講利索。老實說這個知識點我在日常工作中也經(jīng)常忽略但它幾乎囊括了MySQL二級索引回表、優(yōu)化器成本、條件過濾的全部關(guān)鍵點。這篇文章就從這個面試題出發(fā)把ICP的原理、觸發(fā)條件、實操驗證方法和面試應(yīng)答思路一次講透適合正在準(zhǔn)備Java后端面試的同學(xué)也適合平時寫SQL想優(yōu)化慢查詢的開發(fā)者。1. 面試現(xiàn)場復(fù)盤面試官到底在問什么1.1 一句“ICP”背后是MySQL執(zhí)行原理的完整鏈路Java后端面試問MySQL并不稀奇但很多候選人只背了“索引失效十大場景”這種口訣碰到ICP就露餡了。實際上面試官問ICP并不是要你背誦一個概念而是要確認(rèn)你有沒有真正理解一條SQL查詢從客戶端到存儲引擎要經(jīng)歷哪些環(huán)節(jié)。MySQL整體是兩層架構(gòu)上面是Server層負(fù)責(zé)連接管理、語法解析、優(yōu)化、執(zhí)行下面是存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和讀取。在MySQL 5.6之前Server層通過存儲引擎接口拿到二級索引定位到的記錄后會在Server層把where條件里的其他過濾條件逐一判斷而在5.6之后MySQL引入了Index Condition Pushdown允許把一部分索引條件“下推”到存儲引擎層讓引擎在讀取二級索引記錄時就先做一次過濾。面試官問這個其實是想看你是否知道這條鏈路上哪一層在干活哪里能省I/O哪里不能省。這才是考察的要點。1.2 沒搞懂“回表”ICP一定講不透要理解ICP必須先理解什么是回表。InnoDB有兩種索引聚簇索引和二級索引。聚簇索引的葉子節(jié)點直接存的是整行數(shù)據(jù)主鍵的B樹就是數(shù)據(jù)本身而二級索引的葉子節(jié)點存的是“索引列的值 主鍵值”。通過二級索引查詢時第一步是掃描二級索引B樹找到匹配的索引記錄第二步再用這條索引記錄里的主鍵值回到聚簇索引去取完整行。這第二步就是“回表”。回表是一次隨機(jī)I/O尤其在二級索引匹配到很多條記錄、卻只有少數(shù)幾條真正滿足全部where條件時一次一次回表消耗就非常可觀。我平時喜歡用圖書館查書的例子來解釋圖書館有一套目錄卡片每張卡片記錄著書名、作者還有一個唯一的圖書編號。假設(shè)你想找“作者是某某、書名里有某個詞”的書目錄卡片只能定位到作者但你不知道書里具體內(nèi)容符不符合沒有ICP時你得把所有該作者的書從書庫里搬出來翻一遍再挑出符合書名的有ICP時圖書管理員直接在目錄卡片上先比對作者和書名關(guān)鍵詞明顯不符合的卡片直接淘汰剩下的才去書庫搬書。這里的“在卡片上先比對”就是索引條件下推的雛形。2. ICP的核心原理和觸發(fā)條件2.1 條件“下推”到了哪一層ICP的全稱是Index Condition Pushdown索引條件下推。所謂“下推”是指把原來在Server層執(zhí)行的where條件判斷推到存儲引擎層在引擎讀取二級索引記錄時同步判斷。但這里有個非常關(guān)鍵的前提能被下推的條件必須是“能利用二級索引記錄中的字段進(jìn)行判斷”的條件。通俗點說二級索引的葉子節(jié)點只包含索引列和主鍵如果你的過濾條件用到某個不在索引里的列存儲引擎手里根本沒有這個字段的值自然沒法提前判斷這個條件就只能老老實實留在Server層過濾。這也是ICP最容易被人誤解的地方不是所有where條件都能下推。只有索引鍵內(nèi)包含的列才能參與下推。知道了這個前提就能很好理解為什么ICP能減少回表次數(shù)引擎在二級索引上掃描時對一條索引記錄先判斷下推下來的條件滿足才拿主鍵去回表不滿足直接跳過。這樣一來回表的對象從“所有被二級索引定位到的記錄”縮小成了“先經(jīng)過索引記錄條件過濾后的記錄”。2.2 一個經(jīng)典例子看懂下推過程我們建一張員工表用聯(lián)合索引(last_name, first_name)作為例子CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, salary DECIMAL(10,2), KEY idx_name (last_name, first_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;執(zhí)行這條查詢SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000;聯(lián)合索引idx_name里有l(wèi)ast_name和first_name。last_name Smith可以直接用來做索引范圍定位first_name LIKE %John%由于是前導(dǎo)通配符沒法用來縮小索引掃描范圍但它仍然是索引中的字段salary完全不在索引里。沒有ICP時MySQL的做法是先在二級索引里找到所有l(wèi)ast_name Smith的記錄然后一條一條回表把完整行返回給Server層再由Server層過濾first_name LIKE %John%和salary 50000。假設(shè)Smith有1000條記錄可能最后只有10條符合那么900多次回表都是白做的。啟用ICP后存儲引擎在讀取二級索引記錄時手里已經(jīng)有一條索引記錄里面包含last_name和first_name。引擎可以先用自己的first_name字段判斷LIKE條件滿足才回表不滿足的索引記錄直接扔掉。所以salary 50000沒法下推回表后還要在Server層過濾但回表次數(shù)已經(jīng)從1000次降到了比如100次。這就是ICP的核心價值在“索引定位”和“回表取數(shù)”之間多了一道攔截。2.3 哪些SQL才配觸發(fā)ICPICP不是所有SQL都適用我根據(jù)實際碰到的情況整理了觸發(fā)條件查詢必須真正使用了二級索引如果優(yōu)化器選擇全表掃描則談不上ICP。WHERE條件中的過濾列必須是當(dāng)前使用二級索引的組成部分不在索引里的列不能下推。該列在索引記錄上的判斷方式可以是等值、范圍、LIKE等但不能對索引列使用函數(shù)或表達(dá)式計算。不能用于主鍵索引因為主鍵索引是聚簇索引葉子節(jié)點已經(jīng)包含整行數(shù)據(jù)不存在“回表再過濾”的過程。系統(tǒng)參數(shù)optimizer_switch中的index_condition_pushdown必須為on這個參數(shù)從MySQL 5.6開始默認(rèn)開啟。最終是否使用ICP還要看優(yōu)化器的成本估算如果優(yōu)化器認(rèn)為全表掃描或其它執(zhí)行方式代價更低也不會用ICP。很多人會問為什么first_name LIKE %John%不能用索引定位卻能用ICP過濾這兩個不是一回事。索引定位要利用B樹的有序性前導(dǎo)通配符破壞了有序匹配所以沒法作為索引訪問條件但I(xiàn)CP只是“在二級索引記錄上做一次條件判斷”相當(dāng)于把引擎本來沒參與過濾的字段加入判斷。存儲引擎掃描到一條索引記錄它完全有能力讀取這個字段并判斷LIKE所以就能下推。這也是ICP最優(yōu)雅的地方它把索引中“不能用于定位但能用于判斷”的價值榨干了。2.4 ICP和覆蓋索引別混淆ICP的Extra顯示是Using index condition覆蓋索引的Extra顯示是Using index兩者經(jīng)常被搞混。覆蓋索引指查詢所需的所有列都能從索引中直接取得不需要回表ICP指查詢?nèi)匀恍枰乇碇皇腔乇砬跋扔盟饕涗涀隽艘坏肋^濾。一個是“完全不需要回表”一個是“減少回表次數(shù)”收益不一樣。實際優(yōu)化時如果能用覆蓋索引就不該只滿足于ICP。比如查詢字段只有l(wèi)ast_name, first_name那直接走覆蓋索引比ICP更徹底但如果查詢字段里有salary這種不在索引里的列又無法把所有字段都塞進(jìn)索引時利用ICP在回表前攔截一下往往是性價比最高的方案。3. 從建表到EXPLAIN手把手驗證ICP3.1 準(zhǔn)備測試環(huán)境和數(shù)據(jù)紙上談兵不踏實我在本機(jī)MySQL 8.0里重新驗證了一遍。先建表然后插一部分測試數(shù)據(jù)DROP TABLE IF EXISTS employees; CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, salary DECIMAL(10,2), KEY idx_name (last_name, first_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO employees(last_name, first_name, salary) VALUES (Smith,John,8000), (Smith,Johnny,12000), (Smith,John,50000), (Smith,Jane,88000), (Smith,John,120000), (Smith,Johnny,30000), (Smith,John,20000), (Smith,Mike,70000), (Smith,John,60000), (Brown,John,90000);數(shù)據(jù)量不大但足夠看出執(zhí)行計劃的差異。如果想驗證得更明顯可以用存儲過程循環(huán)插入幾十萬行把last_name隨機(jī)成20個常見姓氏first_name隨機(jī)成20個名字salary隨機(jī)。ICP在數(shù)據(jù)量大、回表成本高的時候才能體現(xiàn)性能差距。3.2 用EXPLAIN看Using index condition執(zhí)行下面這條SQL注意要在前面加EXPLAINEXPLAIN SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000\G我在本機(jī)得到的關(guān)鍵列是這些id: 1 select_type: SIMPLE table: employees type: ref possible_keys: idx_name key: idx_name key_len: 202 ref: const rows: 9 filtered: 11.11 Extra: Using index condition看幾個點。key是idx_name說明這條SQL真的走了二級索引ref是const說明last_name Smith用于等值定位rows是9優(yōu)化器估算通過last_name定位到9條索引記錄。最關(guān)鍵的Extra顯示Using index condition這就是ICP生效的標(biāo)志。filtered是11.11%代表回表之后在Server層繼續(xù)過濾剩余條件后預(yù)計還有 9 * 11.11% 約等于1條返回記錄。注意不要以為Using index condition出現(xiàn)就表示沒有salary條件了。由于salary不在索引里它仍然是在Server層過濾的所以我的測試環(huán)境里這條SQL在部分版本下Extra會同時出現(xiàn)Using index condition; Using where代表“引擎層下推了一部分條件 Server層還要繼續(xù)過濾剩余條件”。3.3 開關(guān)ICP對比執(zhí)行計劃差異為了對比我把ICP在會話級別關(guān)掉SET SESSION optimizer_switch index_condition_pushdownoff; EXPLAIN SELECT * FROM employees WHERE last_name Smith AND first_name LIKE %John% AND salary 50000\G這時Extra變成Using whererows可能不變但執(zhí)行流程變成了“拿到全部Smith的索引記錄回表再在Server層過濾first_name LIKE和salary”。測試完記得恢復(fù)SET SESSION optimizer_switch index_condition_pushdownon;如果你用的是MySQL 8.0.18以上版本還可以用EXPLAIN ANALYZE看實際執(zhí)行過程。它會把執(zhí)行計劃中每個節(jié)點消耗多少毫秒、返回多少行都打印出來其中能看到Index lookup on employees using idx_name (last_nameSmith), with index condition: (first_name like %John%)這樣的描述非常直觀。3.4 驗證時務(wù)必別踩的坑我在驗證時踩過一個最典型的坑把optimizer_switch改了重新執(zhí)行EXPLAIN發(fā)現(xiàn)Extra里始終沒有Using index condition。后來排查才發(fā)現(xiàn)那條SQL壓根沒走二級索引優(yōu)化器直接選了全表掃描。EXPLAIN里的key是NULL自然不會有ICP。所以驗證ICP之前第一件事是確認(rèn)key列非空必要時可以用FORCE INDEX(idx_name)強(qiáng)制走索引來觀察。另外生產(chǎn)環(huán)境千萬不要為了方便測試在全局把index_condition_pushdown關(guān)掉。它默認(rèn)就是優(yōu)化的關(guān)閉后只會讓本可以提前過濾的查詢回更多次表除非你是在做對比實驗否則沒必要動它。4. 常見問題與實戰(zhàn)排查4.1 為什么加了索引卻沒走ICP我在給同事排查慢SQL時最常見的原因就是“加了索引但SQL還是全表掃描”。比如對last_name單列建了索引但查詢里用了WHERE UPPER(last_name) SMITH索引列套了函數(shù)MySQL不會使用這個索引又比如統(tǒng)計信息不準(zhǔn)確優(yōu)化器判斷全表掃描成本更低。處理辦法是先看EXPLAIN的possible_keys和key確認(rèn)索引有沒有機(jī)會被用上如果統(tǒng)計信息明顯陳舊執(zhí)行ANALYZE TABLE employees;更新一下。如果實在想驗證可以用FORCE INDEX強(qiáng)制索引但要注意FORCE INDEX本身也可能選錯對象測試完就撤銷。另外有時候不是索引沒加是索引設(shè)計不合理。舉例來說如果查詢條件經(jīng)常是last_name salary但索引建成了(first_name, last_name)salary不在索引中那么where里的salary條件就無法下推只能回表后過濾。合理做法是根據(jù)業(yè)務(wù)高頻過濾條件調(diào)整聯(lián)合索引順序把常用等值條件放前面把需要過濾的字段想辦法納入索引。4.2 ICP不生效是索引設(shè)計的問題ICP并不是萬能藥我在真實項目里見過不少“以為用了ICP其實收益很小”的情況。ICP只能減少回表次數(shù)不能消除回表。如果SQL里頻繁使用SELECT *即使ICP把滿足條件的記錄從1000條篩到100條這100條仍然要回表取全部字段。此時更好的方案可能是把高頻查詢字段整理成一個覆蓋索引讓Extra變成Using index徹底避免回表。還有一類問題是優(yōu)化器沒有選對索引。表上有多個聯(lián)合索引時MySQL會估算哪個索引代價更低。ICP的過濾能力會影響估算但也可能因為另一個索引能直接覆蓋查詢就放棄了ICP。我在優(yōu)化時不會只看單條SQL而是會同時輸出整個表的索引分布結(jié)合業(yè)務(wù)查詢頻率決定刪除冗余索引減少優(yōu)化器選錯索引的概率。4.3 Java項目里怎么用好ICP這個知識ICP是個MySQL自動執(zhí)行的優(yōu)化Java代碼層面不需要做任何特殊配置也不需要改SQL。但這不代表我們沒事可做。日常用Spring Boot MyBatis開發(fā)時可以把a(bǔ)pplication.yml里的數(shù)據(jù)源連接串多配一個sessionVariablesoptimizer_switchindex_condition_pushdownon當(dāng)然這個參數(shù)默認(rèn)就是開我只是習(xí)慣顯式確認(rèn)一下。更重要的是排查慢SQL的意識。MyBatis打印SQL后我通常會復(fù)制到開發(fā)庫執(zhí)行并加EXPLAINMySQL 8.0還可以用EXPLAIN ANALYZE看真實執(zhí)行時間。如果你發(fā)現(xiàn)某個查詢走了二級索引但回表很多就要思考回表之前能不能讓引擎多用索引列做下推索引列是不是被函數(shù)包住了能不能把某些高頻查詢字段塞進(jìn)索引做成覆蓋索引ICP只是整個索引優(yōu)化鏈路里的一環(huán)它幫我們打開了“看執(zhí)行計劃”這扇門。5. 面試應(yīng)答思路與后續(xù)追問拆招5.1 一分鐘講透ICP如果面試官讓你解釋ICP可以先給一個干凈利落的版本ICP是MySQL 5.6引入的優(yōu)化全稱Index Condition Pushdown。在查詢使用二級索引時Server層會把一部分可以用索引列判斷的where條件下推到InnoDB存儲引擎存儲引擎在掃描二級索引記錄時直接判斷滿足條件的記錄才回表減少回表次數(shù)。判斷是否生效看EXPLAIN里的Extra是否顯示Using index condition。這個回答包含了版本、全稱、層級、作用、驗證手段已經(jīng)能證明你確實了解它。但要高分還得補(bǔ)一個例子。把idx_name那個例子用口述講出來last_name Smith AND first_name LIKE %John% AND salary 50000前兩個條件在索引里salary不在索引里。LIKE不能用于定位卻能在二級索引記錄上直接判斷并下推salary留在Server層過濾。這樣面試官會相信你不只是背了定義而是能在具體SQL里分析。5.2 三分鐘版本區(qū)分覆蓋索引和ICP如果面試官繼續(xù)追問“那你是不是用了ICP就不需要覆蓋索引了”這里要警惕。覆蓋索引是查詢字段全部在索引里直接返回數(shù)據(jù)不回表ICP是回表前先過濾仍然要回表。覆蓋索引的效果更徹底但對索引大小有代價索引列越多寫入成本越高。兩者各有適用場景。平時優(yōu)化時優(yōu)先看能否用覆蓋索引如果字段太多沒法全覆蓋再用ICP把回表量壓下來。還可以補(bǔ)充一個細(xì)節(jié)Extra的三個狀態(tài)別搞混。Using index是覆蓋索引Using index condition是索引條件下推Using where是Server層過濾。有時候一條SQL的Extra會同時出現(xiàn)多個說明引擎層和下推都參與了一層Server層還做了一層這反而是正?，F(xiàn)象。5.3 高頻追問拆招面試官可能會接著問為什么ICP只能用于二級索引。答案很明確主鍵索引是聚簇索引葉子節(jié)點就是整行數(shù)據(jù)讀取索引記錄時已經(jīng)拿到了所有字段不存在“先通過索引定位到主鍵再回表取數(shù)”的額外I/O所以沒有優(yōu)化空間。ICP的收益完全來自二級索引場景下“回表”這個動作。還有可能問ICP一定能提升性能嗎這個問題我會回答“不一定”。使用ICP是優(yōu)化器基于代價估算的選擇。如果last_nameSmith本身能匹配到的記錄已經(jīng)很少比如只有一兩條回表成本本來就很低ICP的收益可以忽略如果統(tǒng)計信息不準(zhǔn)確優(yōu)化器甚至可能作出相反選擇。另外ICP過濾掉大量記錄回表次數(shù)減少但二級索引掃描本身仍要讀取那些被淘汰的索引記錄所以收益大小取決于索引記錄過濾能力不能神話它。最后再分享一個小技巧驗證ICP時一定要先看EXPLAIN里的key列有沒有值。我見過很多人改了半天optimizer_switch結(jié)果SQL全表掃描Extra里壓根不會出現(xiàn)Using index condition。遇到這種情況用FORCE INDEX強(qiáng)制走索引先確認(rèn)ICP能生效再回頭審視為什么優(yōu)化器不選這個索引是統(tǒng)計信息問題還是索引設(shè)計問題。這個思路在面試后的實際項目里比單純記住ICP的流程有用得多。