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