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