1. 覆蓋索引先從一次慢查詢講起1.1 一次讓人頭疼的回表經(jīng)歷先說個我自己的真實(shí)案例。之前幫一個電商團(tuán)隊(duì)優(yōu)化后臺訂單查詢表里數(shù)據(jù)量大概幾百萬行SQL長這樣SELECT order_id, user_id, status FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;業(yè)務(wù)方反饋說這個頁面轉(zhuǎn)圈要好半天我一看執(zhí)行計劃Extra那一欄明晃晃寫著Using filesort再看key字段雖然用上了索引但rows預(yù)估掃描了幾萬行。幾萬行本身不算多問題在于每一行都得靠主鍵去聚簇索引里再撈一次完整記錄這個動作就叫回表。回表一次兩次無所謂回表幾萬次哪怕每次都是主鍵查找累加起來也足夠讓接口響應(yīng)時間沖到幾百毫秒甚至一秒以上。很多人對回表的理解停留在“需要回表”這個結(jié)論上但對于回表到底為什么慢、慢在哪個環(huán)節(jié)其實(shí)沒太想清楚。InnoDB 的索引結(jié)構(gòu)是 B 樹二級索引葉子節(jié)點(diǎn)存的是索引列 主鍵聚簇索引葉子節(jié)點(diǎn)存的是完整行記錄。你用二級索引去過濾的時候查出來的其實(shí)是“主鍵值列表”再用這些主鍵值去聚簇索引里面撈整行。這中間隔著一次隨機(jī) I/O。機(jī)械硬盤時代這是致命傷SSD 時代雖然好一些但一次回表至少也是一次 B 樹搜索幾萬行就是幾萬次搜索累積起來非常可觀。1.2 覆蓋索引的原理就是“免回表”覆蓋索引解決的就是這個問題。所謂覆蓋索引指的是一個二級索引包含了查詢所需要的全部列。這個“全部列”包括三部分WHERE 子句用到的過濾列、SELECT 子句要返回的列、ORDER BY 或 GROUP BY 涉及的排序列。當(dāng)這所有列都落在同一個索引里的時候MySQL 直接從二級索引的葉子節(jié)點(diǎn)取數(shù)返回就行了完事不需要再回聚簇索引。為什么能這樣因?yàn)槎壦饕娜~子節(jié)點(diǎn)上本身就有完整的索引列值和主鍵值。你要的數(shù)據(jù)全在這些值里面那引擎層直接返回即可。我之前遇到的那個慢查詢優(yōu)化方式就是建一個聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time, order_id);這個索引把 WHERE 過濾條件涉及的user_id、status排序涉及的create_timeSELECT 返回的order_id全包進(jìn)去了。建完之后再看執(zhí)行計劃Extra 欄出現(xiàn)了Using index這個詞在 MySQL 里就是覆蓋索引的標(biāo)志后面還跟著一個Using filesort消失了因?yàn)閏reate_time已經(jīng)在索引里有序排列直接順序讀就行。效果很明顯原來 800ms 的查詢優(yōu)化完差不多 10ms 以內(nèi)。一個索引同時解決了回表和 filesort 兩個問題。1.3 覆蓋索引的驗(yàn)證方法和使用邊界怎么確認(rèn)你的 SQL 真的走了覆蓋索引核心就看EXPLAIN的 Extra 列Using index代表查詢命中覆蓋索引不需要回表如果同時出現(xiàn)Using where說明雖然索引覆蓋了但還有部分條件是在索引掃描結(jié)果上再做過濾這種仍然算覆蓋索引如果出現(xiàn)Using index condition那是索引下推跟覆蓋索引是兩個不同的機(jī)制后面細(xì)說什么都沒寫那大概率回表了這里要強(qiáng)調(diào)一個很容易踩的坑覆蓋索引不是萬能的不能為了覆蓋所有查詢就拼命往索引里塞列。索引列越多B 樹越寬每個葉子節(jié)點(diǎn)能存放的條目越少樹的高度就會變高同時寫入時要更新的索引頁也更多插入性能會明顯下降。我見過有團(tuán)隊(duì)為了覆蓋一個多條件查詢建了一個包含 8 列的索引結(jié)果是寫接口慢了 30%這就是典型的得不償失。合理做法是優(yōu)先覆蓋高頻查詢控制索引列數(shù)在 3 到 4 列以內(nèi)如果個別列比如大的TEXT字段不能進(jìn)索引就退而求其次先保證過濾和排序列在索引里SELECT 列實(shí)在覆蓋不了的就讓它回表但把回表行數(shù)壓到最低。2. 索引下推索引未覆蓋時的一層額外保護(hù)2.1 從一次 LIKE 查詢的困惑說起再來分享一個優(yōu)化經(jīng)歷。有一次排查一個用戶搜索接口表結(jié)構(gòu)是CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), KEY idx_name_age (name, age) ) ENGINEInnoDB;搜索 SQL 長這樣SELECT * FROM users WHERE name LIKE 張% AND age BETWEEN 18 AND 25;第一反應(yīng)是聯(lián)合索引idx_name_age(name, age)肯定能用上但問題在于 LIKE 的%通配符放在后面意味著索引只能用到name這一列的前綴部分age這個條件怎么處理如果是在 MySQL 5.5 及更早版本流程是這樣的存儲引擎根據(jù)name LIKE 張%這個條件在索引里找到所有姓張的用戶的 ID然后回表把完整記錄撈出來再到 Server 層去判斷age BETWEEN 18 AND 25。問題來了如果姓張的用戶有 10 萬行但年齡在范圍內(nèi)只有 1 萬行意味著你白白回表了 9 萬次做了 9 萬次毫無意義的主鍵查找。2.2 索引下推到底“推”了什么MySQL 5.6 引入了索引下推Index Condition PushdownICP就是為了解決上面這個場景。它的核心邏輯是把 WHERE 條件中能用索引列判斷的部分從 Server 層“推”到存儲引擎層去執(zhí)行。還是剛才那個查詢。有了 ICP 之后存儲引擎在掃描二級索引時先用name LIKE 張%定位到候選范圍然后在索引內(nèi)部繼續(xù)用age BETWEEN 18 AND 25這個條件去過濾過濾完了再回表。回表次數(shù)從 10 萬次直接降到 1 萬次。簡單總結(jié)一下沒有 ICP存儲引擎掃描二級索引 → 得到主鍵列表 → 回表 → Server 層過濾剩余條件有 ICP存儲引擎掃描二級索引 → 在索引內(nèi)過濾掉不滿足條件的數(shù)據(jù) → 得到主鍵列表 → 回表 → Server 層拿到結(jié)果回表依然存在但回表次數(shù)被大幅壓縮了。這也是 ICP 和覆蓋索引最大的區(qū)別覆蓋索引是從結(jié)果側(cè)消滅回表ICP 是從過程側(cè)減少回表。2.3 EXPLAIN 怎么看索引下推EXPLAIN出來Extra 列如果顯示Using index condition就代表這條 SQL 命中了索引下推優(yōu)化。注意這里的關(guān)鍵詞是condition而不是Using index兩個千萬不要搞混。實(shí)操中還有一種常見組合Extra 同時出現(xiàn)Using index condition; Using where這個組合的含義是有一部分條件在存儲引擎層通過索引過濾掉了Using index condition還有一部分條件必須回到 Server 層才能過濾Using where。比如WHERE name LIKE 張% AND age BETWEEN 18 AND 25 AND city 杭州聯(lián)合索引是idx_name_agecity不在索引里那city條件就只能等回表之后在 Server 層過濾。在索引里能把name和age先篩掉已經(jīng)幫了大忙。我在 MySQL 8.0 里執(zhí)行時還發(fā)現(xiàn)一個細(xì)節(jié)WHERE條件里如果包含無法用索引判斷的函數(shù)運(yùn)算比如WHERE age 1 BETWEEN 18 AND 25這種優(yōu)化器就沒法把a(bǔ)ge條件推下去因?yàn)榇鎯σ嬖谒饕镏荒茏龅戎岛头秶袛嗖荒軐λ饕凶霰磉_(dá)式計算。所以想讓 ICP 發(fā)揮效果SQL 書寫時盡量保持索引列獨(dú)立別包在函數(shù)或表達(dá)式里。2.4 索引下推的適用邊界和生效條件ICP 不是對所有查詢都生效的我在實(shí)際使用中總結(jié)了幾個關(guān)鍵限制第一版本限制。ICP 是 MySQL 5.6 引入的特性5.6 之前的版本連功能開關(guān)都沒有。雖然現(xiàn)在生產(chǎn)環(huán)境已經(jīng)很少見 5.5 了但如果你還在維護(hù)老系統(tǒng)要注意這個問題。第二只能在二級索引上生效聚簇索引沒有意義。聚簇索引本身葉子節(jié)點(diǎn)就是完整行記錄根本沒有回表動作自然不存在“減少回表”的優(yōu)化空間。第三存儲引擎要支持。InnoDB 和 MyISAM 都支持 ICP如果你用的是其他存儲引擎需要自己確認(rèn)一下文檔說明。第四條件類型有限制。下推判斷只適用于REF、EQ_REF、RANGE等訪問類型能處理的場景。如果是LIKE %關(guān)鍵字這種前導(dǎo)通配符索引本身都沒法走自然談不上把條件下推。第五系統(tǒng)開關(guān)也可能被關(guān)掉。可以通過optimizer_switch來檢查SELECT optimizer_switch;輸出內(nèi)容里找到index_condition_pushdownon如果是off可以手動開啟SET optimizer_switch index_condition_pushdownon;不過絕大多數(shù)默認(rèn)安裝是開啟狀態(tài)這個操作更多用在對查詢行為做驗(yàn)證時的開關(guān)對比。3. 聯(lián)合索引計劃覆蓋索引和索引下推的協(xié)同優(yōu)化3.1 一個實(shí)際業(yè)務(wù)場景的完整拆解前面分別講了兩個技術(shù)點(diǎn)實(shí)際生產(chǎn)中更常見的情況是一條 SQL 同時需要覆蓋索引和索引下推來配合。我來還原一個完整的優(yōu)化過程。業(yè)務(wù)背景是一個內(nèi)容管理系統(tǒng)的文章列表頁支持按作者、狀態(tài)、發(fā)布時間篩選SQL 大致如下SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;當(dāng)前的索引情況是author_id上有單列索引status和publish_time都沒有獨(dú)立索引。執(zhí)行計劃顯示 key 是idx_author_idExtra 是Using filesortrows預(yù)估 5000 行左右。現(xiàn)在的核心問題有兩個author_id單列索引過濾完之后要回表拿title、status、publish_time等所有 SELECT 列回表完還需要在內(nèi)存里做一次排序因?yàn)閕dx_author_id里只有author_id沒有publish_time的排序信息3.2 索引設(shè)計的具體推演過程這里就要倒推一下怎么設(shè)計索引才能同時滿足 WHERE、ORDER BY、SELECT 三方面需求。首先是 WHERE。author_id 2088是等值條件優(yōu)先級最高放組合索引最左邊。status 1是第二個等值條件跟在后面。publish_time 2024-01-01是范圍條件只能放在等值條件之后否則會導(dǎo)致后面列無法走索引。其次是 ORDER BY。ORDER BY publish_time DESC需要publish_time在索引里而且它前面必須全是等值條件才能保證索引順序和排序順序一致。目前author_id和status都是等值條件把publish_time放在第三個位置排序就能自動走索引。最后是 SELECT。article_id是主鍵二級索引自動帶上title必須顯式加進(jìn)索引才能實(shí)現(xiàn)覆蓋。綜合下來最終索引設(shè)計是ALTER TABLE articles ADD INDEX idx_author_status_time (author_id, status, publish_time, title);這個索引同時干了三件事完成了 WHERE 條件的快速過濾、消除了Using filesort、實(shí)現(xiàn)了覆蓋索引避免回表。執(zhí)行計劃里 Extra 變成了干凈的Using indexrows降到了幾十行響應(yīng)時間從 200ms 降到了 5ms 以內(nèi)效果好得肉眼可見。3.3 當(dāng)覆蓋索引做不到時ICP 如何兜底但現(xiàn)實(shí)往往不會這么理想。有時候 SELECT 列包含大字段比如content這種 TEXT 類型沒法加進(jìn)索引。這時候覆蓋索引計劃就得放棄但也不意味著眼睜睜看著上萬次回表發(fā)生。拿一個具體場景來說文章表里有content大字段查詢要求按category_id和publish_time過濾返回文章標(biāo)題和摘要同時要排除掉刪除狀態(tài)的文章。SELECT article_id, title, summary FROM articles WHERE category_id 10 AND publish_time BETWEEN 2024-01-01 AND 2024-06-01 AND status 1;組合索引可以設(shè)計成idx_category_time(category_id, publish_time, status)。三個字段都在索引里WHERE 條件的所有過濾動作都可以在索引內(nèi)部完成雖然最終依然要回表撈title、summary但關(guān)鍵是status 1這個條件在存儲引擎層就被過濾掉了回表的行數(shù)只剩下那些真正符合所有條件的記錄這比先把所有category_id 10的用戶回表撈出來再判斷status要高效得多。這兩種思路在實(shí)際業(yè)務(wù)里經(jīng)常交替出現(xiàn)。我個人的判斷標(biāo)準(zhǔn)很簡單SELECT 列能不能全部放進(jìn)索引能就奔著覆蓋索引去不能就退而求其次把 WHERE 過濾條件盡量設(shè)計進(jìn)索引利用 ICP 把回表規(guī)模壓到最小。兩者一組合慢查詢基本能消滅一大半。4. 常見問題與排查技巧實(shí)錄4.1 一張排查速查表實(shí)際操作中不管是覆蓋索引還是索引下推出問題的場景其實(shí)高度集中在下面幾個情況里。我把這些年積累的排查結(jié)論整理成了一個速查表方便遇到問題時直接對照疑問可能原因排查方法建立了覆蓋索引但 EXPLAIN 顯示回表SELECT 列超出索引范圍LIKE 左模糊導(dǎo)致條件失效OR 條件破壞索引核對索引列和 SELECT 列是否完全匹配檢查 WHERE 條件寫法沒有顯示 Using index condition版本低于 5.6optimizer_switch 被關(guān)閉條件無法在引擎層判斷檢查 version查詢optimizer_switch確認(rèn)條件是索引列上的等值或范圍索引下推沒起效果索引列順序不對例如范圍條件放在等值條件前面條件涉及函數(shù)或類型轉(zhuǎn)換調(diào)整索引列順序改寫 SQL 讓索引列獨(dú)立索引建了不少但查詢還是慢優(yōu)化器沒選中預(yù)期索引索引統(tǒng)計信息過期ANALYZE TABLE更新統(tǒng)計信息用FORCE INDEX臨時驗(yàn)證索引覆蓋了但是寫入變慢索引列太多B 樹變大評估查詢頻率刪除不常用的冗余索引4.2 兩個深坑深分頁和類型隱式轉(zhuǎn)換第一個深坑是深分頁。很多開發(fā)會用LIMIT 10000, 20這種寫法做分頁從 MySQL 角度來說它會先掃到前 10020 條符合條件的記錄再把前 10000 條丟棄只返回最后 20 條。如果配合覆蓋索引前 10020 條都不用回表性能還勉強(qiáng)撐得住但一旦覆蓋不了就得回表 10020 次再丟棄 10000 條簡直就是災(zāi)難。這里我推薦用延遲關(guān)聯(lián)或者游標(biāo)分頁來改寫。簡單說先用覆蓋索引查出符合條件的id再用id去關(guān)聯(lián)原表撈完整數(shù)據(jù)類似下面這樣SELECT a.* FROM articles a INNER JOIN ( SELECT id FROM articles WHERE author_id 2088 AND status 1 ORDER BY publish_time DESC LIMIT 10000, 20 ) t ON a.id t.id;內(nèi)層子查詢因?yàn)橹恍枰猧d、author_id、status、publish_time完全可以通過覆蓋索引掃描回表次數(shù)被壓縮到只有最后 20 次。第二個深坑是隱式類型轉(zhuǎn)換。比如user_id是字符串類型傳參時后端代碼傳了數(shù)字MySQL 在比較時就會做類型轉(zhuǎn)換這會導(dǎo)致索引列被函數(shù)包裹覆蓋索引和 ICP 直接失效動作全變成全表掃描。這種問題最隱蔽EXPLAIN 看不出來端倪只有用SHOW WARNINGS才能看到底層 SQL 被改寫成了CAST(user_id AS INT)。排查時遇到索引突然失效優(yōu)先檢查字段類型和傳入?yún)?shù)類型是否一致。4.3 利用 MySQL 8.0 的 EXPLAIN ANALYZE 做驗(yàn)證MySQL 8.0.18 開始引入了EXPLAIN ANALYZE這是一個我非常推薦的實(shí)際調(diào)試工具。跟傳統(tǒng)EXPLAIN不同它會真實(shí)執(zhí)行這條 SQL然后輸出每一步的實(shí)際耗時、實(shí)際行數(shù)、掃描行數(shù)、循環(huán)次數(shù)。EXPLAIN ANALYZE SELECT article_id, title, status, publish_time FROM articles WHERE author_id 2088 AND status 1 AND publish_time 2024-01-01 ORDER BY publish_time DESC LIMIT 20;輸出的結(jié)果里可以清晰看到actual time和actual rows比如- Limit: 20 row(s) (actual time2.345..2.348 rows20 loops1) - Sort: articles.publish_time DESC, limit input to 20 row(s) (actual time2.344..2.347 rows20 loops1) - Index range scan on articles using idx_author_status_time (actual time0.108..2.178 rows37 loops1)對比傳統(tǒng)EXPLAIN只能看到預(yù)估行數(shù)EXPLAIN ANALYZE能直接看到每個環(huán)節(jié)的實(shí)際耗時分布。哪個環(huán)節(jié)掃的行數(shù)多、哪個環(huán)節(jié)排序耗時高一目了然。做覆蓋索引優(yōu)化前和優(yōu)化后用這個工具各跑一次效果對比非常直觀。最后分享一個小技巧如果EXPLAIN顯示行數(shù)跟實(shí)際差異很大很多時候不一定是 SQL 問題而是表統(tǒng)計信息太久沒更新了。運(yùn)行一下ANALYZE TABLE table_name;再重新看執(zhí)行計劃可能結(jié)果完全不同。這一點(diǎn)在索引設(shè)計完成后做驗(yàn)證時特別重要別讓過期的統(tǒng)計信息誤導(dǎo)你做出錯誤的索引判斷。