化實(shí)戰(zhàn)：從B+樹原理到EXPLAIN慢查詢排查)
很多人在面試的時(shí)候能把索引背得滾瓜爛熟B樹、最左前綴、覆蓋索引、回表……但真正到了線上一條慢查詢打過來(lái)能快速定位到是索引問題、并且能給出合理優(yōu)化方案的人其實(shí)并不多。我見過不少同事建索引全憑感覺查詢條件里出現(xiàn)什么字段就給什么字段加索引結(jié)果一張表上掛了十幾個(gè)單列索引寫入越來(lái)越慢查詢卻沒快多少。這篇文章不打算重復(fù)教科書上那些概念而是結(jié)合我這些年踩過的坑把MySQL索引從設(shè)計(jì)到驗(yàn)證、再到日常維護(hù)的完整思路捋一遍。無(wú)論你是后端開發(fā)、DBA還是剛接觸數(shù)據(jù)庫(kù)的新人只要按照這個(gè)思路去分析慢查詢基本都能找到方向。1. 索引到底是什么先搞懂B樹這棵大樹的底層邏輯1.1 沒有索引的時(shí)候MySQL是怎么找數(shù)據(jù)的在沒有可用索引的情況下InnoDB 對(duì)表的查詢幾乎是純體力活從表的第一行開始一行一行往下掃直到把所有行都檢查完這就是我們常說的全表掃描。在線上環(huán)境里我經(jīng)常在慢查詢?nèi)罩纠锟吹綆装偃f(wàn)行的表一條查詢直接拖到幾百毫秒甚至幾秒十有八九就是缺索引或者索引沒被正確使用。為什么全表掃描這么慢對(duì)于 InnoDB 來(lái)說數(shù)據(jù)是按主鍵聚簇存放的也就是說 B樹的葉子節(jié)點(diǎn)上完整地保存了每一行數(shù)據(jù)。如果沒有索引可用優(yōu)化器只能把整棵樹的葉子節(jié)點(diǎn)全部讀一遍I/O 量幾乎等于讀完整張表。對(duì)一張千萬(wàn)級(jí)別的表來(lái)講即使按照順序讀也會(huì)產(chǎn)生大量的磁盤 I/O 和內(nèi)存拷貝。拿生活里的場(chǎng)景類比一下全表掃描就像一本沒有目錄、沒有章節(jié)號(hào)的字典你要找一個(gè)字只能一頁(yè)一頁(yè)翻。這個(gè)行為在數(shù)據(jù)量小的時(shí)候還能忍受幾千行全表掃也就幾毫秒但數(shù)據(jù)量一旦上去了響應(yīng)時(shí)間就會(huì)斷崖式變差。很多新人容易忽略的是全表掃描并非一無(wú)是處當(dāng)表很小或者查詢需要讀取超過一定比例的數(shù)據(jù)時(shí)全表掃描反而比走索引更高效。這也是為什么有時(shí)候你明明建了索引優(yōu)化器卻選擇了全表掃描后面我會(huì)專門講到這個(gè)點(diǎn)。1.2 B樹索引結(jié)構(gòu)為什么適合數(shù)據(jù)庫(kù)MySQL 索引最常見的底層數(shù)據(jù)結(jié)構(gòu)是 B樹。為什么不是哈希表、二叉搜索樹或者普通 B 樹這個(gè)問題的答案幾乎決定了我們對(duì)索引使用的理解深度。哈希表雖然能在等值查詢時(shí)做到 O(1) 的查找速度但它天生不支持范圍查詢也無(wú)法做排序。SQL 里最常見的WHERE id 100、ORDER BY create_time這類操作哈希結(jié)構(gòu)基本幫不上忙。二叉搜索樹會(huì)隨著數(shù)據(jù)不斷插入而失去平衡最壞情況退化成鏈表查詢性能不可控。普通 B 樹雖然已經(jīng)比較矮胖但它的非葉子節(jié)點(diǎn)也存儲(chǔ)了實(shí)際數(shù)據(jù)這樣在范圍掃描時(shí)需要在不同層級(jí)之間反復(fù)回溯效率不夠流暢。B樹的設(shè)計(jì)恰恰解決了這些痛點(diǎn)只有葉子節(jié)點(diǎn)才存實(shí)際數(shù)據(jù)內(nèi)部節(jié)點(diǎn)只存索引鍵這樣每個(gè)節(jié)點(diǎn)能容納更多鍵值整棵樹可以做得很矮很寬。以 InnoDB 默認(rèn) 16KB 的頁(yè)大小為例如果一行索引鍵加上指針大約占幾十字節(jié)三層高度的 B樹往往就能容納幾千萬(wàn)條記錄。這意味著查詢一兩千萬(wàn)行的表通常只需要兩到三次磁盤 I/O就能定位到目標(biāo)數(shù)據(jù)所在的葉子頁(yè)。另一個(gè)關(guān)鍵設(shè)計(jì)是葉子節(jié)點(diǎn)之間通過指針連成有序鏈表。這讓范圍查詢變得非常流暢找到一個(gè)起始位置后沿著鏈表往后順序讀就行避免了跨層回溯。BETWEEN、ORDER BY、以及分頁(yè)查詢這類操作B樹都能給優(yōu)化器提供清晰的掃描路徑。理解了這層設(shè)計(jì)你就能明白為什么索引在排序和范圍查詢場(chǎng)景下如此重要。1.3 二級(jí)索引、回表與索引覆蓋除了主鍵對(duì)應(yīng)的聚簇索引普通字段上的索引都叫二級(jí)索引。二級(jí)索引的葉子節(jié)點(diǎn)里存的不再是完整數(shù)據(jù)行而是索引鍵和主鍵值。當(dāng)查詢條件命中二級(jí)索引但需要讀取的列不在索引里時(shí)MySQL 還要拿著主鍵值回到聚簇索引里再查一次這個(gè)過程叫回表。這里就到了一個(gè)非常關(guān)鍵的優(yōu)化點(diǎn)如果 SELECT 需要讀取的所有列都恰好包含在某個(gè)索引中InnoDB 可以直接從索引葉子節(jié)點(diǎn)上拿到數(shù)據(jù)不需要回表這就是覆蓋索引。我曾經(jīng)處理過一個(gè)列表接口原來(lái)查詢無(wú)論怎么調(diào)都慢最后把查詢列全部納入聯(lián)合索引執(zhí)行計(jì)劃的 Extra 字段里出現(xiàn) Using index 之后性能立刻上了一個(gè)臺(tái)階。理解回表和覆蓋索引對(duì)后續(xù)設(shè)計(jì)聯(lián)合索引至關(guān)重要。你設(shè)計(jì)索引時(shí)不僅要看 WHERE 條件用到了哪些列還要想清楚 SELECT 了哪些列。兩者同時(shí)納入視野才算一個(gè)完整的設(shè)計(jì)思路。尤其是那種高頻查詢的列表頁(yè)覆蓋索引往往是性價(jià)比最高的優(yōu)化手段它能同時(shí)減少 I/O 次數(shù)和回表帶來(lái)的隨機(jī)讀開銷。2. 索引設(shè)計(jì)實(shí)戰(zhàn)字段怎么選、聯(lián)合索引怎么排2.1 區(qū)分度是索引設(shè)計(jì)的先決條件很多人建索引的規(guī)則是“查詢條件里有什么字段就加什么”這個(gè)習(xí)慣其實(shí)非常危險(xiǎn)。一個(gè)字段是否適合建立索引最先要看的指標(biāo)是區(qū)分度也就是這一列有多少種不同的值。區(qū)分度的計(jì)算可以簡(jiǎn)單寫成COUNT(DISTINCT column) / COUNT(*)。這個(gè)比例越接近 1說明列的取值越分散索引篩選能力越強(qiáng)。性別、狀態(tài)、是否刪除這類只有幾個(gè)固定值的字段區(qū)分度非常低。如果單獨(dú)給它們建索引一次匹配可能還會(huì)命中幾百萬(wàn)行索引的意義就大打折扣了。我見過一個(gè)實(shí)際案例某張業(yè)務(wù)表有 800 萬(wàn)數(shù)據(jù)status 字段只有 5 種取值團(tuán)隊(duì)給 status 單獨(dú)建了個(gè)索引。結(jié)果查詢WHERE status 1時(shí)優(yōu)化器算了一下發(fā)現(xiàn)走索引要匹配 160 萬(wàn)行再回表還不如全表掃描快于是執(zhí)行計(jì)劃直接放棄了索引。這就是低區(qū)分度字段單獨(dú)建索引的典型困境。低區(qū)分度字段并非完全不能用但通常要放在聯(lián)合索引靠后的位置或者與其他高區(qū)分度字段配合使用。比如一條查詢里既能用 user_id 過濾到幾千行又能用 status 進(jìn)一步過濾到幾百行那么聯(lián)合索引 (user_id, status) 就很有價(jià)值單獨(dú)拿 status 建索引則基本屬于浪費(fèi)空間。判斷一個(gè)索引有沒有價(jià)值不是看語(yǔ)法上能不能建而是看它到底能把掃描范圍縮小到什么程度。2.2 聯(lián)合索引的最左前綴原則聯(lián)合索引的存儲(chǔ)規(guī)則決定了它和多個(gè)單列索引有本質(zhì)區(qū)別。以 (a, b, c) 聯(lián)合索引為例B樹葉子節(jié)點(diǎn)上的數(shù)據(jù)會(huì)先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。理解這個(gè)順序就能理解為什么 MySQL 只認(rèn)最左前綴。所謂最左前綴就是查詢條件里必須從聯(lián)合索引的最左邊一列開始匹配索引才可能被用到。(a, b, c) 這個(gè)索引能服務(wù)于 (a)、(a, b)、(a, b, c) 三種查詢條件組合。如果查詢只針對(duì) b 或者 c 作為過濾條件這個(gè)聯(lián)合索引通常幫不上忙。有些開發(fā)者以為三個(gè)字段建了一個(gè)索引所有組合都能優(yōu)化這是個(gè)很常見的誤區(qū)。聯(lián)合索引的真正優(yōu)勢(shì)在于它讓等值條件下的多列過濾變成了一次索引范圍的精確掃描。舉個(gè)例子一個(gè)訂單表有聯(lián)合索引 (user_id, status)查詢WHERE user_id U10001 AND status 1時(shí)InnoDB 可以非常精確地在索引樹中定位到 user_id 為 U10001、status 為 1 的那一段葉子節(jié)點(diǎn)不需要一個(gè)一個(gè)回表看數(shù)據(jù)。還有一點(diǎn)容易被忽略范圍條件之后的列會(huì)停止匹配。如果查詢是WHERE a 1 AND b 10 AND c 2那么索引最多用到 a 和 bc 列沒法繼續(xù)參與索引定位只能等回表后再過濾。設(shè)計(jì)聯(lián)合索引時(shí)通常建議把等值條件的字段放在左邊范圍條件的字段放在右邊排序字段再進(jìn)一步考慮。這樣能讓索引發(fā)揮最大作用。2.3 單列索引和聯(lián)合索引該怎么取舍面試?yán)锍枴岸鄠€(gè)條件查詢?yōu)槭裁匆?lián)合索引而不是多個(gè)單列索引”實(shí)際工作里這個(gè)問題的答案非常真實(shí)多個(gè)單列索引對(duì)優(yōu)化器的選擇非常不友好。舊版本的 MySQL 雖然有 index merge 優(yōu)化但大多數(shù)情況下優(yōu)化器只會(huì)挑選其中一個(gè)區(qū)分度較高的索引去掃回表后再過濾其他條件。這意味著你建了三個(gè)單列索引但一次查詢大概率只能“吃掉”其中一個(gè)另外兩個(gè)除了浪費(fèi)磁盤空間和維護(hù)開銷幾乎沒有實(shí)際收益。而聯(lián)合索引因?yàn)榘讯鄠€(gè)條件按順序保存在一起可以一次掃描完成多條件篩選回表次數(shù)也會(huì)更少。當(dāng)然聯(lián)合索引也不是越多越好。聯(lián)合索引本身占空間且每次寫入、更新都要同步維護(hù)索引樹索引過多會(huì)顯著拖慢寫入性能。更麻煩的是聯(lián)合索引之間存在互相覆蓋的可能有了 (a, b) 索引再建 (a) 單列索引就比較冗余。我自己在做索引維護(hù)時(shí)會(huì)把那些“可以被其他索引替代的多余索引”清理掉這一步對(duì)高寫入場(chǎng)景的表來(lái)說收益非常明顯。3. 用 EXPLAIN 驗(yàn)證索引一次慢查詢優(yōu)化全過程3.1 讀懂 EXPLAIN 里的幾個(gè)關(guān)鍵字段索引到底有沒有生效不能靠猜要以 EXPLAIN 的執(zhí)行計(jì)劃為準(zhǔn)?？磮?zhí)行計(jì)劃看多了之后我基本只盯幾個(gè)關(guān)鍵字段type、key、key_len、rows、Extra。type 字段表示訪問類型從好到差大致是 const、eq_ref、ref、range、index、ALL?？吹?ALL 說明是全表掃描這是最危險(xiǎn)的狀態(tài)index 表示掃描整個(gè)索引樹雖然比全表掃描好一點(diǎn)但也不代表高效range 是索引范圍掃描常見于 BETWEEN、IN、大于小于這類條件ref 是等值匹配二級(jí)索引是比較理想的狀態(tài)const 是主鍵或唯一索引等值查詢性能最好。key_len 用來(lái)判斷聯(lián)合索引真正用到了幾列。拿聯(lián)合索引 (user_id, status, create_time) 來(lái)說如果執(zhí)行計(jì)劃里的 key_len 只覆蓋了前兩列的長(zhǎng)度說明第三條索引列沒有參與本次查詢的定位。計(jì)算 key_len 時(shí)要考慮字符集比如 utf8mb4 下一個(gè) varchar(32) 字段本身占 128 字節(jié)再加 2 字節(jié)長(zhǎng)度和可空標(biāo)識(shí)最終可能到 132 左右。不需要背精確公式但要知道這個(gè)值越大通常意味著索引利用得越充分。Extra 里的信息更需要重視。Using index 表示用到了覆蓋索引全程沒回表Using index condition 表示用上了索引下推ICP能在索引層過濾一部分?jǐn)?shù)據(jù)Using filesort 表示排序沒有走索引需要額外做文件排序數(shù)據(jù)量大時(shí)是性能黑洞Using temporary 則往往和 group by、去重等操作相關(guān)。執(zhí)行計(jì)劃里如果同時(shí)出現(xiàn) Using filesort 和 Using temporary基本可以斷定這條 SQL 還有很大的優(yōu)化空間。3.2 從 1.6 秒到 30 毫秒一個(gè)訂單分頁(yè)查詢的真實(shí)優(yōu)化過程我之前接手過一個(gè)后臺(tái)訂單查詢接口功能很簡(jiǎn)單根據(jù)用戶 ID 查訂單列表按創(chuàng)建時(shí)間倒序分頁(yè)返回。表數(shù)據(jù)量大約 700 萬(wàn)接口在高峰期經(jīng)常超時(shí)用戶端反饋“列表頁(yè)轉(zhuǎn)圈圈轉(zhuǎn)很久”。最初 SQL 長(zhǎng)這樣SELECT id, order_no, status, create_time FROM orders WHERE user_id U20240001 ORDER BY create_time DESC LIMIT 20;EXPLAIN 的結(jié)果讓我印象深刻type: ALL possible_keys: NULL key: NULL rows: 682340 Extra: Using where; Using filesorttype 是 ALL直接全表掃還帶 Using filesort。問題很明確表結(jié)構(gòu)里只有主鍵索引沒有任何二級(jí)索引能輔助 user_id 過濾和 create_time 排序。優(yōu)化器只能先把 68 萬(wàn)行全部掃出來(lái)再對(duì)結(jié)果做一次完整排序最后才取 20 條返回。優(yōu)化方案是加一個(gè)貼合查詢的聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);再跑一次 EXPLAIN結(jié)果變成type: ref possible_keys: idx_user_time key: idx_user_time key_len: 132 rows: 38 Extra: NULLrows 從 68 萬(wàn)直接降到幾十行文件排序也消失了。因?yàn)槁?lián)合索引的葉子節(jié)點(diǎn)已經(jīng)按照 user_id 和 create_time 排序?qū)τ谕粋€(gè)用戶的訂單InnoDB 可以直接從匹配范圍的尾部往前取 20 條不需要把匹配行全部撈出來(lái)再排一次。上線之后我又復(fù)測(cè)了幾輪單次查詢從原來(lái)的 1.6 秒左右穩(wěn)定在 30 毫秒內(nèi)接口 99 分位耗時(shí)也明顯下降。這里給一個(gè)實(shí)操上的建議加完索引之后不要只看 SELECT 是否變快還要觀察 INSERT、UPDATE 的耗時(shí)是否在可接受范圍內(nèi)。像這種訂單表寫入量本身就大加索引之前最好先在測(cè)試環(huán)境評(píng)估一下寫入鏈路的壓力變化。3.3 覆蓋索引帶來(lái)的下一步優(yōu)化同樣是上面這個(gè)查詢?nèi)绻霃氐妆苊饣乇砜梢园?SELECT 涉及的列也放進(jìn)索引。比如 order_no、status 是高頻 SELECT 字段把索引擴(kuò)展成 (user_id, create_time, order_no, status)Extra 里就會(huì)出現(xiàn) Using index整個(gè)過程不再訪問聚簇索引。不過這種方案需要權(quán)衡索引列變多存儲(chǔ)空間和維護(hù)成本都會(huì)增加單個(gè)索引頁(yè)能容納的鍵數(shù)量減少索引體積變大會(huì)反過來(lái)影響緩存命中率。實(shí)際操作中我通常先看接口的核心瓶頸在哪里。如果 30 毫秒已經(jīng)能滿足業(yè)務(wù)要求就沒必要為了追求 Using index 盲目加長(zhǎng)索引。性能優(yōu)化不是把執(zhí)行計(jì)劃做到最完美而是把耗時(shí)控制在業(yè)務(wù)能接受的范圍同時(shí)保證復(fù)雜度和維護(hù)成本可控。這里也想提醒一下不是所有 SELECT 的列都適合塞進(jìn)索引。像長(zhǎng)文本、大 JSON 這種字段放進(jìn)索引會(huì)直接把索引體積撐爆通常的做法是只在 SQL 里查詢必要字段或者把大字段拆到單獨(dú)的表里避免拖垮主表的索引效率。4. 索引失效的幾大場(chǎng)景與排查清單4.1 常見失效場(chǎng)景索引設(shè)計(jì)得再好只要 SQL 寫法不配合索引照樣不生效。下面這幾種情況是我在線上排查慢查詢時(shí)遇到最多的。第一類是函數(shù)包裹索引列。WHERE DATE(create_time) 2024-01-01這種寫法的意思是先對(duì) create_time 做函數(shù)運(yùn)算再比較結(jié)果索引樹上的值沒法直接匹配。類似問題還有WHERE YEAR(create_time) 2024。正確做法是改成范圍條件create_time 2024-01-01 AND create_time 2024-01-02這樣索引才能正常走 range 掃描。第二類是隱式類型轉(zhuǎn)換。比如 mobile 字段在表里是 varchar 類型查詢寫成WHERE mobile 13800000000MySQL 會(huì)把字符串列轉(zhuǎn)成數(shù)字去比較轉(zhuǎn)換過程導(dǎo)致索引列上發(fā)生函數(shù)操作索引隨之失效。這類問題排查起來(lái)特別隱蔽因?yàn)轭愋娃D(zhuǎn)換是數(shù)據(jù)庫(kù)內(nèi)部做的EXPLAIN 里不會(huì)給出很顯眼的提示只看到 type 變成 ALLrows 飆得很高。第三類是前導(dǎo)模糊查詢。WHERE name LIKE %abc因?yàn)闊o(wú)法確定前綴索引的排序和定位優(yōu)勢(shì)完全發(fā)揮不出來(lái)。反過來(lái)WHERE name LIKE abc%就可以走 range 范圍掃描。如果業(yè)務(wù)確實(shí)需要后綴模糊搜索可以考慮全文索引或者額外冗余一個(gè)反向字符串字段不要讓這種查詢硬打主表索引。第四類是對(duì)索引列做運(yùn)算。WHERE id 1 100這類寫法同樣會(huì)導(dǎo)致索引失效應(yīng)該改寫成WHERE id 99。MySQL 在優(yōu)化階段不會(huì)替你把等式兩邊的運(yùn)算消除掉索引列一旦參與運(yùn)算就無(wú)法按原始值去定位。還有一種比較容易被忽視的場(chǎng)景是 OR。如果 OR 條件中的某個(gè)列沒有可用索引優(yōu)化器往往直接放棄索引走全表掃描因?yàn)闊o(wú)法保證每一分支都能高效命中。遇到這種情況解決辦法是給所有 OR 列都加上索引或者把 SQL 改寫成 UNION把有索引和無(wú)索引的查詢拆開執(zhí)行讓優(yōu)化器可以選擇更優(yōu)的訪問路徑。4.2 大表在線加索引的實(shí)操經(jīng)驗(yàn)我們總說“直接執(zhí)行一條 ALTER TABLE 加索引就行”但真正要在幾百萬(wàn)行甚至更大的生產(chǎn)表上加索引事情沒那么簡(jiǎn)單。InnoDB 從 5.6 開始支持在線 DDL添加二級(jí)索引時(shí)允許并發(fā) DML但這并不代表沒有代價(jià)。大表加索引有幾個(gè)典型影響第一個(gè)是主庫(kù) DDL 期間仍然可能產(chǎn)生較大的磁盤 I/O 壓力因?yàn)橐亟ǘ?jí)索引樹第二個(gè)是如果通過主從復(fù)制讓從庫(kù)也執(zhí)行 DDL從庫(kù)延遲很可能會(huì)被拉高。我踩過這類坑曾經(jīng)在寫流量較大的表上直接跑 ALTER TABLE結(jié)果主庫(kù)會(huì)話堆積、從庫(kù)復(fù)制延遲飆升。后來(lái)總結(jié)了一條經(jīng)驗(yàn)生產(chǎn)環(huán)境大表加索引優(yōu)先放在業(yè)務(wù)低峰期執(zhí)行并提前確認(rèn)磁盤空間足夠同時(shí)觀察主從復(fù)制延遲必要的時(shí)候用并行復(fù)制參數(shù)來(lái)緩解。需要回退時(shí)也要小心。索引一旦創(chuàng)建刪除它同樣不是瞬間完成的事也會(huì)觸發(fā)索引頁(yè)的回收。所以給大表加索引之前最好是先在測(cè)試環(huán)境用完整數(shù)據(jù)量跑一遍 DDL記錄耗時(shí)再?zèng)Q定正式執(zhí)行的窗口。臨時(shí)表、備份表這些手段雖然老套但在關(guān)鍵時(shí)刻能兜底。4.3 索引統(tǒng)計(jì)信息與優(yōu)化器選錯(cuò)索引有時(shí)候明明有合適的索引執(zhí)行計(jì)劃卻選了別的索引甚至全表掃描這通常是優(yōu)化器基于統(tǒng)計(jì)信息做了“錯(cuò)誤”判斷。InnoDB 的統(tǒng)計(jì)信息是從采樣頁(yè)里估算出來(lái)的如果表的數(shù)據(jù)分布發(fā)生較大變化統(tǒng)計(jì)信息可能沒有及時(shí)更新。解決辦法不是直接上手 FORCE INDEX而是先刷新統(tǒng)計(jì)信息ANALYZE TABLE orders;。刷新之后優(yōu)化器通常會(huì)給出更合理的執(zhí)行計(jì)劃。如果刷新之后依然選錯(cuò)再考慮使用 FORCE INDEX 指定索引。不過 FORCE INDEX 屬于強(qiáng)制手段一旦數(shù)據(jù)分布再次變化手里這個(gè)索引可能不再是最優(yōu)解所以線上使用要非常克制并且要寫清楚注釋說明為什么強(qiáng)制指定。另外一個(gè)細(xì)節(jié)是優(yōu)化器對(duì)“預(yù)計(jì)掃描行數(shù)”和實(shí)際回表成本之間的權(quán)衡不一定準(zhǔn)確。在二級(jí)索引上MySQL 要逐行回表才能判斷是否滿足 WHERE 條件如果匹配的行數(shù)太多優(yōu)化器寧愿選擇全表掃描而不是走二級(jí)索引。這也能解釋為什么低區(qū)分度字段單獨(dú)建索引經(jīng)常不生效優(yōu)化器快速估算一下覺得走索引還不如全表掃。遇到這種情況要從根本上改變查詢條件或者重新設(shè)計(jì)聯(lián)合索引而不是跟優(yōu)化器較勁。5. 索引日常巡檢與維護(hù)工具速查5.1 如何查看現(xiàn)有索引與潛在冗余索引不是建完就一勞永逸的業(yè)務(wù)變化后原來(lái)的索引可能變成累贅。我比較習(xí)慣用SHOW INDEX FROM orders;查看表上的索引情況但這個(gè)輸出太原始實(shí)際維護(hù)時(shí)更多會(huì)對(duì)比列順序和查詢場(chǎng)景。目標(biāo)是找出冗余索引。判斷標(biāo)準(zhǔn)很簡(jiǎn)單如果一個(gè)索引的列是另一個(gè)聯(lián)合索引的最左前綴那它大概率可以被覆蓋。例如有一個(gè)聯(lián)合索引 (user_id, status)同時(shí)又存在 user_id 單列索引后者就是冗余的。這種冗余索引浪費(fèi)的是每次寫入時(shí)的維護(hù)開銷在高寫入場(chǎng)景下影響非常明顯。也可以直接查詢系統(tǒng)元數(shù)據(jù)比如 information_schema.STATISTICS 和 sys.schema_unused_indexes。后者能直接列出未被使用的索引對(duì)日常巡檢非常有幫助。遇到長(zhǎng)期不用的索引建議先在業(yè)務(wù)代碼里完全確認(rèn)沒有隱性使用再下線避免直接刪除后突然被打回。有趣的是一些團(tuán)隊(duì)喜歡把所有可能用到的索引都“一次性建好”美其名曰以防萬(wàn)一。這種做法恰恰是索引膨脹的根源。索引數(shù)量越多寫放大越嚴(yán)重查詢優(yōu)化器的選擇成本也越高。理想的策略是根據(jù)實(shí)際慢查詢和業(yè)務(wù) SQL 的分布來(lái)動(dòng)態(tài)維護(hù)而不是靠拍腦袋預(yù)防式建索引。設(shè)計(jì)索引永遠(yuǎn)服務(wù)核心查詢鏈路不為偶爾跑一次的后臺(tái)報(bào)表任務(wù)犧牲主鏈路性能。5.2 慢查詢?nèi)罩九c執(zhí)行計(jì)劃常態(tài)化檢查我把慢查詢?nèi)罩镜臋z查當(dāng)成了日常習(xí)慣。long_query_time設(shè)置 1 秒或更小慢日志里出現(xiàn)的每一條 SQL我都會(huì)用 EXPLAIN 重新看一眼。倒不是為了“批量性能優(yōu)化”而是通過這些執(zhí)行計(jì)劃來(lái)判斷未來(lái)數(shù)據(jù)量增長(zhǎng)后的風(fēng)險(xiǎn)。如果一個(gè) SQL 當(dāng)前雖然只查了 5 毫秒但 EXPLAIN 顯示 type 是 ALLrows 接近整表大小一旦數(shù)據(jù)翻倍它很可能就變成慢查詢。這種隱患是慢查詢優(yōu)化里最值得提前處理的類型。反過來(lái)如果 type 已經(jīng)是 refrows 也只有幾十行那基本可以放心很多短時(shí)間內(nèi)不太會(huì)出問題。在維護(hù)索引這件事上我還傾向于定期觀察索引頁(yè)的碎片情況。InnoDB 表經(jīng)過大量隨機(jī)刪除、更新后索引頁(yè)可能變得稀疏占用空間變大掃描效率下降。必要時(shí)可以通過 OPTIMIZE TABLE 重建表和索引但要注意這個(gè)操作在超大表上的耗時(shí)和資源消耗依舊要安排在低峰期執(zhí)行。一條 SQL 經(jīng)常無(wú)法用上索引的時(shí)候我更愿意退一步想想 SQL 邏輯本身是不是有優(yōu)化空間。比如把函數(shù)處理挪到應(yīng)用層完成、把 OR 改成 UNION、把分頁(yè)條件從 LIMIT 大偏移改成基于上一頁(yè)最大 ID 的游標(biāo)模式。這些做法往往比糾結(jié)單個(gè)索引更有效而且能從根源上降低數(shù)據(jù)庫(kù)的壓力。5.3 幾條實(shí)用經(jīng)驗(yàn)總結(jié)文章寫到這里把我這些年跟 MySQL 索引打交道的核心體會(huì)整理一下。不要憑感覺建索引先用區(qū)分度和核心查詢場(chǎng)景做判斷。區(qū)分度太低、查詢本身命中數(shù)據(jù)量過大的字段建了也是白建。寫完任何一條涉及多條件篩選的 SQL都養(yǎng)成跑一次 EXPLAIN 的習(xí)慣。執(zhí)行計(jì)劃里的 type、key_len、rows、Extra 四個(gè)字段就能回答 90% 的“為什么慢”問題。聯(lián)合索引的列順序要從“等值優(yōu)先、范圍其次、排序最后”這個(gè)思路去設(shè)計(jì)。這個(gè)順序排錯(cuò)了索引實(shí)際價(jià)值會(huì)大打折扣。線上改索引必須有回退方案且要在業(yè)務(wù)低峰期執(zhí)行。大表 DDL 不是一句 ALTER TABLE 就能草率搞定的。保持索引數(shù)量的克制。一個(gè)表的索引數(shù)量超過 5~6 個(gè)之后寫入性能的劣化往往會(huì)超過新增索引的查詢收益需要非常謹(jǐn)慎地評(píng)估。索引是手段讓查詢快速返回結(jié)果才是目的。這一點(diǎn)我一直寫在團(tuán)隊(duì)的開發(fā)規(guī)范里也是每次評(píng)審表結(jié)構(gòu)時(shí)最常強(qiáng)調(diào)的一句話。慢查詢排查的次數(shù)多了之后你會(huì)發(fā)現(xiàn)真正需要你記在腦子里的不是各種復(fù)雜的索引語(yǔ)法而是一個(gè)樸素的思考習(xí)慣這條 SQL 想要定位的數(shù)據(jù)到底被存在哪里、怎么才能最快找到它。最后再多說一句每次優(yōu)化的答案都藏在執(zhí)行計(jì)劃里。不需要背下所有語(yǔ)法細(xì)節(jié)但一定要能看懂 EXPLAIN 正在做什么。慢通常不是 MySQL 的問題而是我們對(duì)數(shù)據(jù)的訪問路徑還不夠理解。把這條路徑理清了索引設(shè)計(jì)自然會(huì)跟著清晰起來(lái)。 MySQL索引優(yōu)化實(shí)戰(zhàn)：從B+樹原理到EXPLAIN慢查詢排查
很多人在面試的時(shí)候能把索引背得滾瓜爛熟B樹、最左前綴、覆蓋索引、回表……但真正到了線上一條慢查詢打過來(lái)能快速定位到是索引問題、并且能給出合理優(yōu)化方案的人其實(shí)并不多。我見過不少同事建索引全憑感覺查詢條件里出現(xiàn)什么字段就給什么字段加索引結(jié)果一張表上掛了十幾個(gè)單列索引寫入越來(lái)越慢查詢卻沒快多少。這篇文章不打算重復(fù)教科書上那些概念而是結(jié)合我這些年踩過的坑把MySQL索引從設(shè)計(jì)到驗(yàn)證、再到日常維護(hù)的完整思路捋一遍。無(wú)論你是后端開發(fā)、DBA還是剛接觸數(shù)據(jù)庫(kù)的新人只要按照這個(gè)思路去分析慢查詢基本都能找到方向。1. 索引到底是什么先搞懂B樹這棵大樹的底層邏輯1.1 沒有索引的時(shí)候MySQL是怎么找數(shù)據(jù)的在沒有可用索引的情況下InnoDB 對(duì)表的查詢幾乎是純體力活從表的第一行開始一行一行往下掃直到把所有行都檢查完這就是我們常說的全表掃描。在線上環(huán)境里我經(jīng)常在慢查詢?nèi)罩纠锟吹綆装偃f(wàn)行的表一條查詢直接拖到幾百毫秒甚至幾秒十有八九就是缺索引或者索引沒被正確使用。為什么全表掃描這么慢對(duì)于 InnoDB 來(lái)說數(shù)據(jù)是按主鍵聚簇存放的也就是說 B樹的葉子節(jié)點(diǎn)上完整地保存了每一行數(shù)據(jù)。如果沒有索引可用優(yōu)化器只能把整棵樹的葉子節(jié)點(diǎn)全部讀一遍I/O 量幾乎等于讀完整張表。對(duì)一張千萬(wàn)級(jí)別的表來(lái)講即使按照順序讀也會(huì)產(chǎn)生大量的磁盤 I/O 和內(nèi)存拷貝。拿生活里的場(chǎng)景類比一下全表掃描就像一本沒有目錄、沒有章節(jié)號(hào)的字典你要找一個(gè)字只能一頁(yè)一頁(yè)翻。這個(gè)行為在數(shù)據(jù)量小的時(shí)候還能忍受幾千行全表掃也就幾毫秒但數(shù)據(jù)量一旦上去了響應(yīng)時(shí)間就會(huì)斷崖式變差。很多新人容易忽略的是全表掃描并非一無(wú)是處當(dāng)表很小或者查詢需要讀取超過一定比例的數(shù)據(jù)時(shí)全表掃描反而比走索引更高效。這也是為什么有時(shí)候你明明建了索引優(yōu)化器卻選擇了全表掃描后面我會(huì)專門講到這個(gè)點(diǎn)。1.2 B樹索引結(jié)構(gòu)為什么適合數(shù)據(jù)庫(kù)MySQL 索引最常見的底層數(shù)據(jù)結(jié)構(gòu)是 B樹。為什么不是哈希表、二叉搜索樹或者普通 B 樹這個(gè)問題的答案幾乎決定了我們對(duì)索引使用的理解深度。哈希表雖然能在等值查詢時(shí)做到 O(1) 的查找速度但它天生不支持范圍查詢也無(wú)法做排序。SQL 里最常見的WHERE id 100、ORDER BY create_time這類操作哈希結(jié)構(gòu)基本幫不上忙。二叉搜索樹會(huì)隨著數(shù)據(jù)不斷插入而失去平衡最壞情況退化成鏈表查詢性能不可控。普通 B 樹雖然已經(jīng)比較矮胖但它的非葉子節(jié)點(diǎn)也存儲(chǔ)了實(shí)際數(shù)據(jù)這樣在范圍掃描時(shí)需要在不同層級(jí)之間反復(fù)回溯效率不夠流暢。B樹的設(shè)計(jì)恰恰解決了這些痛點(diǎn)只有葉子節(jié)點(diǎn)才存實(shí)際數(shù)據(jù)內(nèi)部節(jié)點(diǎn)只存索引鍵這樣每個(gè)節(jié)點(diǎn)能容納更多鍵值整棵樹可以做得很矮很寬。以 InnoDB 默認(rèn) 16KB 的頁(yè)大小為例如果一行索引鍵加上指針大約占幾十字節(jié)三層高度的 B樹往往就能容納幾千萬(wàn)條記錄。這意味著查詢一兩千萬(wàn)行的表通常只需要兩到三次磁盤 I/O就能定位到目標(biāo)數(shù)據(jù)所在的葉子頁(yè)。另一個(gè)關(guān)鍵設(shè)計(jì)是葉子節(jié)點(diǎn)之間通過指針連成有序鏈表。這讓范圍查詢變得非常流暢找到一個(gè)起始位置后沿著鏈表往后順序讀就行避免了跨層回溯。BETWEEN、ORDER BY、以及分頁(yè)查詢這類操作B樹都能給優(yōu)化器提供清晰的掃描路徑。理解了這層設(shè)計(jì)你就能明白為什么索引在排序和范圍查詢場(chǎng)景下如此重要。1.3 二級(jí)索引、回表與索引覆蓋除了主鍵對(duì)應(yīng)的聚簇索引普通字段上的索引都叫二級(jí)索引。二級(jí)索引的葉子節(jié)點(diǎn)里存的不再是完整數(shù)據(jù)行而是索引鍵和主鍵值。當(dāng)查詢條件命中二級(jí)索引但需要讀取的列不在索引里時(shí)MySQL 還要拿著主鍵值回到聚簇索引里再查一次這個(gè)過程叫回表。這里就到了一個(gè)非常關(guān)鍵的優(yōu)化點(diǎn)如果 SELECT 需要讀取的所有列都恰好包含在某個(gè)索引中InnoDB 可以直接從索引葉子節(jié)點(diǎn)上拿到數(shù)據(jù)不需要回表這就是覆蓋索引。我曾經(jīng)處理過一個(gè)列表接口原來(lái)查詢無(wú)論怎么調(diào)都慢最后把查詢列全部納入聯(lián)合索引執(zhí)行計(jì)劃的 Extra 字段里出現(xiàn) Using index 之后性能立刻上了一個(gè)臺(tái)階。理解回表和覆蓋索引對(duì)后續(xù)設(shè)計(jì)聯(lián)合索引至關(guān)重要。你設(shè)計(jì)索引時(shí)不僅要看 WHERE 條件用到了哪些列還要想清楚 SELECT 了哪些列。兩者同時(shí)納入視野才算一個(gè)完整的設(shè)計(jì)思路。尤其是那種高頻查詢的列表頁(yè)覆蓋索引往往是性價(jià)比最高的優(yōu)化手段它能同時(shí)減少 I/O 次數(shù)和回表帶來(lái)的隨機(jī)讀開銷。2. 索引設(shè)計(jì)實(shí)戰(zhàn)字段怎么選、聯(lián)合索引怎么排2.1 區(qū)分度是索引設(shè)計(jì)的先決條件很多人建索引的規(guī)則是“查詢條件里有什么字段就加什么”這個(gè)習(xí)慣其實(shí)非常危險(xiǎn)。一個(gè)字段是否適合建立索引最先要看的指標(biāo)是區(qū)分度也就是這一列有多少種不同的值。區(qū)分度的計(jì)算可以簡(jiǎn)單寫成COUNT(DISTINCT column) / COUNT(*)。這個(gè)比例越接近 1說明列的取值越分散索引篩選能力越強(qiáng)。性別、狀態(tài)、是否刪除這類只有幾個(gè)固定值的字段區(qū)分度非常低。如果單獨(dú)給它們建索引一次匹配可能還會(huì)命中幾百萬(wàn)行索引的意義就大打折扣了。我見過一個(gè)實(shí)際案例某張業(yè)務(wù)表有 800 萬(wàn)數(shù)據(jù)status 字段只有 5 種取值團(tuán)隊(duì)給 status 單獨(dú)建了個(gè)索引。結(jié)果查詢WHERE status 1時(shí)優(yōu)化器算了一下發(fā)現(xiàn)走索引要匹配 160 萬(wàn)行再回表還不如全表掃描快于是執(zhí)行計(jì)劃直接放棄了索引。這就是低區(qū)分度字段單獨(dú)建索引的典型困境。低區(qū)分度字段并非完全不能用但通常要放在聯(lián)合索引靠后的位置或者與其他高區(qū)分度字段配合使用。比如一條查詢里既能用 user_id 過濾到幾千行又能用 status 進(jìn)一步過濾到幾百行那么聯(lián)合索引 (user_id, status) 就很有價(jià)值單獨(dú)拿 status 建索引則基本屬于浪費(fèi)空間。判斷一個(gè)索引有沒有價(jià)值不是看語(yǔ)法上能不能建而是看它到底能把掃描范圍縮小到什么程度。2.2 聯(lián)合索引的最左前綴原則聯(lián)合索引的存儲(chǔ)規(guī)則決定了它和多個(gè)單列索引有本質(zhì)區(qū)別。以 (a, b, c) 聯(lián)合索引為例B樹葉子節(jié)點(diǎn)上的數(shù)據(jù)會(huì)先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。理解這個(gè)順序就能理解為什么 MySQL 只認(rèn)最左前綴。所謂最左前綴就是查詢條件里必須從聯(lián)合索引的最左邊一列開始匹配索引才可能被用到。(a, b, c) 這個(gè)索引能服務(wù)于 (a)、(a, b)、(a, b, c) 三種查詢條件組合。如果查詢只針對(duì) b 或者 c 作為過濾條件這個(gè)聯(lián)合索引通常幫不上忙。有些開發(fā)者以為三個(gè)字段建了一個(gè)索引所有組合都能優(yōu)化這是個(gè)很常見的誤區(qū)。聯(lián)合索引的真正優(yōu)勢(shì)在于它讓等值條件下的多列過濾變成了一次索引范圍的精確掃描。舉個(gè)例子一個(gè)訂單表有聯(lián)合索引 (user_id, status)查詢WHERE user_id U10001 AND status 1時(shí)InnoDB 可以非常精確地在索引樹中定位到 user_id 為 U10001、status 為 1 的那一段葉子節(jié)點(diǎn)不需要一個(gè)一個(gè)回表看數(shù)據(jù)。還有一點(diǎn)容易被忽略范圍條件之后的列會(huì)停止匹配。如果查詢是WHERE a 1 AND b 10 AND c 2那么索引最多用到 a 和 bc 列沒法繼續(xù)參與索引定位只能等回表后再過濾。設(shè)計(jì)聯(lián)合索引時(shí)通常建議把等值條件的字段放在左邊范圍條件的字段放在右邊排序字段再進(jìn)一步考慮。這樣能讓索引發(fā)揮最大作用。2.3 單列索引和聯(lián)合索引該怎么取舍面試?yán)锍枴岸鄠€(gè)條件查詢?yōu)槭裁匆?lián)合索引而不是多個(gè)單列索引”實(shí)際工作里這個(gè)問題的答案非常真實(shí)多個(gè)單列索引對(duì)優(yōu)化器的選擇非常不友好。舊版本的 MySQL 雖然有 index merge 優(yōu)化但大多數(shù)情況下優(yōu)化器只會(huì)挑選其中一個(gè)區(qū)分度較高的索引去掃回表后再過濾其他條件。這意味著你建了三個(gè)單列索引但一次查詢大概率只能“吃掉”其中一個(gè)另外兩個(gè)除了浪費(fèi)磁盤空間和維護(hù)開銷幾乎沒有實(shí)際收益。而聯(lián)合索引因?yàn)榘讯鄠€(gè)條件按順序保存在一起可以一次掃描完成多條件篩選回表次數(shù)也會(huì)更少。當(dāng)然聯(lián)合索引也不是越多越好。聯(lián)合索引本身占空間且每次寫入、更新都要同步維護(hù)索引樹索引過多會(huì)顯著拖慢寫入性能。更麻煩的是聯(lián)合索引之間存在互相覆蓋的可能有了 (a, b) 索引再建 (a) 單列索引就比較冗余。我自己在做索引維護(hù)時(shí)會(huì)把那些“可以被其他索引替代的多余索引”清理掉這一步對(duì)高寫入場(chǎng)景的表來(lái)說收益非常明顯。3. 用 EXPLAIN 驗(yàn)證索引一次慢查詢優(yōu)化全過程3.1 讀懂 EXPLAIN 里的幾個(gè)關(guān)鍵字段索引到底有沒有生效不能靠猜要以 EXPLAIN 的執(zhí)行計(jì)劃為準(zhǔn)?？磮?zhí)行計(jì)劃看多了之后我基本只盯幾個(gè)關(guān)鍵字段type、key、key_len、rows、Extra。type 字段表示訪問類型從好到差大致是 const、eq_ref、ref、range、index、ALL?？吹?ALL 說明是全表掃描這是最危險(xiǎn)的狀態(tài)index 表示掃描整個(gè)索引樹雖然比全表掃描好一點(diǎn)但也不代表高效range 是索引范圍掃描常見于 BETWEEN、IN、大于小于這類條件ref 是等值匹配二級(jí)索引是比較理想的狀態(tài)const 是主鍵或唯一索引等值查詢性能最好。key_len 用來(lái)判斷聯(lián)合索引真正用到了幾列。拿聯(lián)合索引 (user_id, status, create_time) 來(lái)說如果執(zhí)行計(jì)劃里的 key_len 只覆蓋了前兩列的長(zhǎng)度說明第三條索引列沒有參與本次查詢的定位。計(jì)算 key_len 時(shí)要考慮字符集比如 utf8mb4 下一個(gè) varchar(32) 字段本身占 128 字節(jié)再加 2 字節(jié)長(zhǎng)度和可空標(biāo)識(shí)最終可能到 132 左右。不需要背精確公式但要知道這個(gè)值越大通常意味著索引利用得越充分。Extra 里的信息更需要重視。Using index 表示用到了覆蓋索引全程沒回表Using index condition 表示用上了索引下推ICP能在索引層過濾一部分?jǐn)?shù)據(jù)Using filesort 表示排序沒有走索引需要額外做文件排序數(shù)據(jù)量大時(shí)是性能黑洞Using temporary 則往往和 group by、去重等操作相關(guān)。執(zhí)行計(jì)劃里如果同時(shí)出現(xiàn) Using filesort 和 Using temporary基本可以斷定這條 SQL 還有很大的優(yōu)化空間。3.2 從 1.6 秒到 30 毫秒一個(gè)訂單分頁(yè)查詢的真實(shí)優(yōu)化過程我之前接手過一個(gè)后臺(tái)訂單查詢接口功能很簡(jiǎn)單根據(jù)用戶 ID 查訂單列表按創(chuàng)建時(shí)間倒序分頁(yè)返回。表數(shù)據(jù)量大約 700 萬(wàn)接口在高峰期經(jīng)常超時(shí)用戶端反饋“列表頁(yè)轉(zhuǎn)圈圈轉(zhuǎn)很久”。最初 SQL 長(zhǎng)這樣SELECT id, order_no, status, create_time FROM orders WHERE user_id U20240001 ORDER BY create_time DESC LIMIT 20;EXPLAIN 的結(jié)果讓我印象深刻type: ALL possible_keys: NULL key: NULL rows: 682340 Extra: Using where; Using filesorttype 是 ALL直接全表掃還帶 Using filesort。問題很明確表結(jié)構(gòu)里只有主鍵索引沒有任何二級(jí)索引能輔助 user_id 過濾和 create_time 排序。優(yōu)化器只能先把 68 萬(wàn)行全部掃出來(lái)再對(duì)結(jié)果做一次完整排序最后才取 20 條返回。優(yōu)化方案是加一個(gè)貼合查詢的聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);再跑一次 EXPLAIN結(jié)果變成type: ref possible_keys: idx_user_time key: idx_user_time key_len: 132 rows: 38 Extra: NULLrows 從 68 萬(wàn)直接降到幾十行文件排序也消失了。因?yàn)槁?lián)合索引的葉子節(jié)點(diǎn)已經(jīng)按照 user_id 和 create_time 排序?qū)τ谕粋€(gè)用戶的訂單InnoDB 可以直接從匹配范圍的尾部往前取 20 條不需要把匹配行全部撈出來(lái)再排一次。上線之后我又復(fù)測(cè)了幾輪單次查詢從原來(lái)的 1.6 秒左右穩(wěn)定在 30 毫秒內(nèi)接口 99 分位耗時(shí)也明顯下降。這里給一個(gè)實(shí)操上的建議加完索引之后不要只看 SELECT 是否變快還要觀察 INSERT、UPDATE 的耗時(shí)是否在可接受范圍內(nèi)。像這種訂單表寫入量本身就大加索引之前最好先在測(cè)試環(huán)境評(píng)估一下寫入鏈路的壓力變化。3.3 覆蓋索引帶來(lái)的下一步優(yōu)化同樣是上面這個(gè)查詢?nèi)绻霃氐妆苊饣乇砜梢园?SELECT 涉及的列也放進(jìn)索引。比如 order_no、status 是高頻 SELECT 字段把索引擴(kuò)展成 (user_id, create_time, order_no, status)Extra 里就會(huì)出現(xiàn) Using index整個(gè)過程不再訪問聚簇索引。不過這種方案需要權(quán)衡索引列變多存儲(chǔ)空間和維護(hù)成本都會(huì)增加單個(gè)索引頁(yè)能容納的鍵數(shù)量減少索引體積變大會(huì)反過來(lái)影響緩存命中率。實(shí)際操作中我通常先看接口的核心瓶頸在哪里。如果 30 毫秒已經(jīng)能滿足業(yè)務(wù)要求就沒必要為了追求 Using index 盲目加長(zhǎng)索引。性能優(yōu)化不是把執(zhí)行計(jì)劃做到最完美而是把耗時(shí)控制在業(yè)務(wù)能接受的范圍同時(shí)保證復(fù)雜度和維護(hù)成本可控。這里也想提醒一下不是所有 SELECT 的列都適合塞進(jìn)索引。像長(zhǎng)文本、大 JSON 這種字段放進(jìn)索引會(huì)直接把索引體積撐爆通常的做法是只在 SQL 里查詢必要字段或者把大字段拆到單獨(dú)的表里避免拖垮主表的索引效率。4. 索引失效的幾大場(chǎng)景與排查清單4.1 常見失效場(chǎng)景索引設(shè)計(jì)得再好只要 SQL 寫法不配合索引照樣不生效。下面這幾種情況是我在線上排查慢查詢時(shí)遇到最多的。第一類是函數(shù)包裹索引列。WHERE DATE(create_time) 2024-01-01這種寫法的意思是先對(duì) create_time 做函數(shù)運(yùn)算再比較結(jié)果索引樹上的值沒法直接匹配。類似問題還有WHERE YEAR(create_time) 2024。正確做法是改成范圍條件create_time 2024-01-01 AND create_time 2024-01-02這樣索引才能正常走 range 掃描。第二類是隱式類型轉(zhuǎn)換。比如 mobile 字段在表里是 varchar 類型查詢寫成WHERE mobile 13800000000MySQL 會(huì)把字符串列轉(zhuǎn)成數(shù)字去比較轉(zhuǎn)換過程導(dǎo)致索引列上發(fā)生函數(shù)操作索引隨之失效。這類問題排查起來(lái)特別隱蔽因?yàn)轭愋娃D(zhuǎn)換是數(shù)據(jù)庫(kù)內(nèi)部做的EXPLAIN 里不會(huì)給出很顯眼的提示只看到 type 變成 ALLrows 飆得很高。第三類是前導(dǎo)模糊查詢。WHERE name LIKE %abc因?yàn)闊o(wú)法確定前綴索引的排序和定位優(yōu)勢(shì)完全發(fā)揮不出來(lái)。反過來(lái)WHERE name LIKE abc%就可以走 range 范圍掃描。如果業(yè)務(wù)確實(shí)需要后綴模糊搜索可以考慮全文索引或者額外冗余一個(gè)反向字符串字段不要讓這種查詢硬打主表索引。第四類是對(duì)索引列做運(yùn)算。WHERE id 1 100這類寫法同樣會(huì)導(dǎo)致索引失效應(yīng)該改寫成WHERE id 99。MySQL 在優(yōu)化階段不會(huì)替你把等式兩邊的運(yùn)算消除掉索引列一旦參與運(yùn)算就無(wú)法按原始值去定位。還有一種比較容易被忽視的場(chǎng)景是 OR。如果 OR 條件中的某個(gè)列沒有可用索引優(yōu)化器往往直接放棄索引走全表掃描因?yàn)闊o(wú)法保證每一分支都能高效命中。遇到這種情況解決辦法是給所有 OR 列都加上索引或者把 SQL 改寫成 UNION把有索引和無(wú)索引的查詢拆開執(zhí)行讓優(yōu)化器可以選擇更優(yōu)的訪問路徑。4.2 大表在線加索引的實(shí)操經(jīng)驗(yàn)我們總說“直接執(zhí)行一條 ALTER TABLE 加索引就行”但真正要在幾百萬(wàn)行甚至更大的生產(chǎn)表上加索引事情沒那么簡(jiǎn)單。InnoDB 從 5.6 開始支持在線 DDL添加二級(jí)索引時(shí)允許并發(fā) DML但這并不代表沒有代價(jià)。大表加索引有幾個(gè)典型影響第一個(gè)是主庫(kù) DDL 期間仍然可能產(chǎn)生較大的磁盤 I/O 壓力因?yàn)橐亟ǘ?jí)索引樹第二個(gè)是如果通過主從復(fù)制讓從庫(kù)也執(zhí)行 DDL從庫(kù)延遲很可能會(huì)被拉高。我踩過這類坑曾經(jīng)在寫流量較大的表上直接跑 ALTER TABLE結(jié)果主庫(kù)會(huì)話堆積、從庫(kù)復(fù)制延遲飆升。后來(lái)總結(jié)了一條經(jīng)驗(yàn)生產(chǎn)環(huán)境大表加索引優(yōu)先放在業(yè)務(wù)低峰期執(zhí)行并提前確認(rèn)磁盤空間足夠同時(shí)觀察主從復(fù)制延遲必要的時(shí)候用并行復(fù)制參數(shù)來(lái)緩解。需要回退時(shí)也要小心。索引一旦創(chuàng)建刪除它同樣不是瞬間完成的事也會(huì)觸發(fā)索引頁(yè)的回收。所以給大表加索引之前最好是先在測(cè)試環(huán)境用完整數(shù)據(jù)量跑一遍 DDL記錄耗時(shí)再?zèng)Q定正式執(zhí)行的窗口。臨時(shí)表、備份表這些手段雖然老套但在關(guān)鍵時(shí)刻能兜底。4.3 索引統(tǒng)計(jì)信息與優(yōu)化器選錯(cuò)索引有時(shí)候明明有合適的索引執(zhí)行計(jì)劃卻選了別的索引甚至全表掃描這通常是優(yōu)化器基于統(tǒng)計(jì)信息做了“錯(cuò)誤”判斷。InnoDB 的統(tǒng)計(jì)信息是從采樣頁(yè)里估算出來(lái)的如果表的數(shù)據(jù)分布發(fā)生較大變化統(tǒng)計(jì)信息可能沒有及時(shí)更新。解決辦法不是直接上手 FORCE INDEX而是先刷新統(tǒng)計(jì)信息ANALYZE TABLE orders;。刷新之后優(yōu)化器通常會(huì)給出更合理的執(zhí)行計(jì)劃。如果刷新之后依然選錯(cuò)再考慮使用 FORCE INDEX 指定索引。不過 FORCE INDEX 屬于強(qiáng)制手段一旦數(shù)據(jù)分布再次變化手里這個(gè)索引可能不再是最優(yōu)解所以線上使用要非常克制并且要寫清楚注釋說明為什么強(qiáng)制指定。另外一個(gè)細(xì)節(jié)是優(yōu)化器對(duì)“預(yù)計(jì)掃描行數(shù)”和實(shí)際回表成本之間的權(quán)衡不一定準(zhǔn)確。在二級(jí)索引上MySQL 要逐行回表才能判斷是否滿足 WHERE 條件如果匹配的行數(shù)太多優(yōu)化器寧愿選擇全表掃描而不是走二級(jí)索引。這也能解釋為什么低區(qū)分度字段單獨(dú)建索引經(jīng)常不生效優(yōu)化器快速估算一下覺得走索引還不如全表掃。遇到這種情況要從根本上改變查詢條件或者重新設(shè)計(jì)聯(lián)合索引而不是跟優(yōu)化器較勁。5. 索引日常巡檢與維護(hù)工具速查5.1 如何查看現(xiàn)有索引與潛在冗余索引不是建完就一勞永逸的業(yè)務(wù)變化后原來(lái)的索引可能變成累贅。我比較習(xí)慣用SHOW INDEX FROM orders;查看表上的索引情況但這個(gè)輸出太原始實(shí)際維護(hù)時(shí)更多會(huì)對(duì)比列順序和查詢場(chǎng)景。目標(biāo)是找出冗余索引。判斷標(biāo)準(zhǔn)很簡(jiǎn)單如果一個(gè)索引的列是另一個(gè)聯(lián)合索引的最左前綴那它大概率可以被覆蓋。例如有一個(gè)聯(lián)合索引 (user_id, status)同時(shí)又存在 user_id 單列索引后者就是冗余的。這種冗余索引浪費(fèi)的是每次寫入時(shí)的維護(hù)開銷在高寫入場(chǎng)景下影響非常明顯。也可以直接查詢系統(tǒng)元數(shù)據(jù)比如 information_schema.STATISTICS 和 sys.schema_unused_indexes。后者能直接列出未被使用的索引對(duì)日常巡檢非常有幫助。遇到長(zhǎng)期不用的索引建議先在業(yè)務(wù)代碼里完全確認(rèn)沒有隱性使用再下線避免直接刪除后突然被打回。有趣的是一些團(tuán)隊(duì)喜歡把所有可能用到的索引都“一次性建好”美其名曰以防萬(wàn)一。這種做法恰恰是索引膨脹的根源。索引數(shù)量越多寫放大越嚴(yán)重查詢優(yōu)化器的選擇成本也越高。理想的策略是根據(jù)實(shí)際慢查詢和業(yè)務(wù) SQL 的分布來(lái)動(dòng)態(tài)維護(hù)而不是靠拍腦袋預(yù)防式建索引。設(shè)計(jì)索引永遠(yuǎn)服務(wù)核心查詢鏈路不為偶爾跑一次的后臺(tái)報(bào)表任務(wù)犧牲主鏈路性能。5.2 慢查詢?nèi)罩九c執(zhí)行計(jì)劃常態(tài)化檢查我把慢查詢?nèi)罩镜臋z查當(dāng)成了日常習(xí)慣。long_query_time設(shè)置 1 秒或更小慢日志里出現(xiàn)的每一條 SQL我都會(huì)用 EXPLAIN 重新看一眼。倒不是為了“批量性能優(yōu)化”而是通過這些執(zhí)行計(jì)劃來(lái)判斷未來(lái)數(shù)據(jù)量增長(zhǎng)后的風(fēng)險(xiǎn)。如果一個(gè) SQL 當(dāng)前雖然只查了 5 毫秒但 EXPLAIN 顯示 type 是 ALLrows 接近整表大小一旦數(shù)據(jù)翻倍它很可能就變成慢查詢。這種隱患是慢查詢優(yōu)化里最值得提前處理的類型。反過來(lái)如果 type 已經(jīng)是 refrows 也只有幾十行那基本可以放心很多短時(shí)間內(nèi)不太會(huì)出問題。在維護(hù)索引這件事上我還傾向于定期觀察索引頁(yè)的碎片情況。InnoDB 表經(jīng)過大量隨機(jī)刪除、更新后索引頁(yè)可能變得稀疏占用空間變大掃描效率下降。必要時(shí)可以通過 OPTIMIZE TABLE 重建表和索引但要注意這個(gè)操作在超大表上的耗時(shí)和資源消耗依舊要安排在低峰期執(zhí)行。一條 SQL 經(jīng)常無(wú)法用上索引的時(shí)候我更愿意退一步想想 SQL 邏輯本身是不是有優(yōu)化空間。比如把函數(shù)處理挪到應(yīng)用層完成、把 OR 改成 UNION、把分頁(yè)條件從 LIMIT 大偏移改成基于上一頁(yè)最大 ID 的游標(biāo)模式。這些做法往往比糾結(jié)單個(gè)索引更有效而且能從根源上降低數(shù)據(jù)庫(kù)的壓力。5.3 幾條實(shí)用經(jīng)驗(yàn)總結(jié)文章寫到這里把我這些年跟 MySQL 索引打交道的核心體會(huì)整理一下。不要憑感覺建索引先用區(qū)分度和核心查詢場(chǎng)景做判斷。區(qū)分度太低、查詢本身命中數(shù)據(jù)量過大的字段建了也是白建。寫完任何一條涉及多條件篩選的 SQL都養(yǎng)成跑一次 EXPLAIN 的習(xí)慣。執(zhí)行計(jì)劃里的 type、key_len、rows、Extra 四個(gè)字段就能回答 90% 的“為什么慢”問題。聯(lián)合索引的列順序要從“等值優(yōu)先、范圍其次、排序最后”這個(gè)思路去設(shè)計(jì)。這個(gè)順序排錯(cuò)了索引實(shí)際價(jià)值會(huì)大打折扣。線上改索引必須有回退方案且要在業(yè)務(wù)低峰期執(zhí)行。大表 DDL 不是一句 ALTER TABLE 就能草率搞定的。保持索引數(shù)量的克制。一個(gè)表的索引數(shù)量超過 5~6 個(gè)之后寫入性能的劣化往往會(huì)超過新增索引的查詢收益需要非常謹(jǐn)慎地評(píng)估。索引是手段讓查詢快速返回結(jié)果才是目的。這一點(diǎn)我一直寫在團(tuán)隊(duì)的開發(fā)規(guī)范里也是每次評(píng)審表結(jié)構(gòu)時(shí)最常強(qiáng)調(diào)的一句話。慢查詢排查的次數(shù)多了之后你會(huì)發(fā)現(xiàn)真正需要你記在腦子里的不是各種復(fù)雜的索引語(yǔ)法而是一個(gè)樸素的思考習(xí)慣這條 SQL 想要定位的數(shù)據(jù)到底被存在哪里、怎么才能最快找到它。最后再多說一句每次優(yōu)化的答案都藏在執(zhí)行計(jì)劃里。不需要背下所有語(yǔ)法細(xì)節(jié)但一定要能看懂 EXPLAIN 正在做什么。慢通常不是 MySQL 的問題而是我們對(duì)數(shù)據(jù)的訪問路徑還不夠理解。把這條路徑理清了索引設(shè)計(jì)自然會(huì)跟著清晰起來(lái)。