合索引與回表優(yōu)化實(shí)戰(zhàn))
上周被拉去排查一個(gè)慢查詢某項(xiàng)目的訂單表已經(jīng) 800 萬行一條很普通的查詢SELECT * FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;跑一次要 2 秒多。表上明明有聯(lián)合索引 idx_user_time(user_id, create_time)EXPLAIN 也顯示用上了索引但實(shí)際大部分時(shí)間耗在回表上。這類問題我不是第一次遇到了。很多人對 MySQL 主鍵索引和聯(lián)合索引的理解停留在知道有 B 樹、知道最左前綴的層面真到設(shè)計(jì)索引、排查慢 SQL 的時(shí)候全靠試。這篇文章想把這兩類索引的底層原理講透聯(lián)合索引為什么講究列順序主鍵索引為什么影響整張表所有索引索引下推和覆蓋索引又是怎么幫我們省回表成本的適合正在被慢查詢折磨的開發(fā)者也適合準(zhǔn)備數(shù)據(jù)庫面試的人我會把原理和實(shí)操放在一起講但核心始終是那一棵 B 樹。1. 從兩個(gè)真實(shí)查詢說起聯(lián)合索引列順序如何影響性能1.1 準(zhǔn)備一張演示表兩個(gè)順序相反的聯(lián)合索引為了方便說明先造一張訂單演示表。注意這張表上我故意掛了兩個(gè)列順序相反的聯(lián)合索引真實(shí)生產(chǎn)環(huán)境不要這么干后面會專門解釋為什么這是冗余設(shè)計(jì)。CREATE TABLE user_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_time (user_id, create_time), KEY idx_time_user (create_time, user_id) ) ENGINEInnoDB;假設(shè)業(yè)務(wù)特征是這樣的10 萬個(gè)活躍用戶平均每個(gè)用戶 80 條訂單訂單的時(shí)間分布集中在最近半年。數(shù)據(jù)量、數(shù)據(jù)分布都清楚了下面看兩個(gè)典型查詢。1.2 同一條查詢?yōu)槭裁此饕樞蜃兞舜鷥r(jià)就變查詢一查某個(gè)用戶最近的訂單。SELECT id, order_no, amount, create_time FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;走 idx_user_time 時(shí)索引樹先定位到 user_id123 這個(gè)位置然后沿著葉子節(jié)點(diǎn)鏈表向后掃描 create_time 大于指定值的記錄掃出來的數(shù)據(jù)天然就是按 (user_id, create_time) 排序的連 ORDER BY 的額外排序都省了。這個(gè)路徑的掃描范圍很小——主鍵等值定位一次剩下只需要讀取該用戶自己的那批索引條目。走 idx_time_user 時(shí)情況就完全不同了。create_time 是聯(lián)合索引的第一列user_id 不是索引的前綴列所以 MySQL 只能先定位到 create_time 2024-06-01 這個(gè)索引區(qū)間這個(gè)區(qū)間可能橫跨幾十萬甚至上百萬條索引條目掃描過程中再把 user_id123 的記錄過濾出來。雖然 MySQL 8.0 的索引下推ICP能在存儲引擎層幫我們過濾 user_id減少一部分回表但區(qū)間起點(diǎn)只能按 create_time 定位中間要掃的索引條目數(shù)量級完全不一樣。查詢二統(tǒng)計(jì)某個(gè)時(shí)間段內(nèi)的訂單量。SELECT COUNT(*) FROM user_orders WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30;這次反轉(zhuǎn)了。idx_time_user 可以精準(zhǔn)定位到 6 月 1 日這個(gè)起點(diǎn)只掃一個(gè)月的索引區(qū)間計(jì)數(shù)器加上去就完事而 idx_user_time 因?yàn)?create_time 不是第一列完全無法直接從時(shí)間上定位只能把整棵索引樹掃一遍把所有 create_time 落在范圍內(nèi)的條目數(shù)出來。1.3 兩種查詢模式對照查詢模式索引 (user_id, create_time)索引 (create_time, user_id)某用戶 時(shí)間范圍等值定位后掃小區(qū)間無需排序先掃大時(shí)間范圍再過濾掃描量大純時(shí)間范圍統(tǒng)計(jì)無法直接定位整樹掃描時(shí)間定位精準(zhǔn)掃描量小同樣是兩個(gè)字段的聯(lián)合索引只是順序換了一下同一批查詢的代價(jià)可能差兩個(gè)數(shù)量級。這里的差別不是 MySQL 優(yōu)化器偷懶而是底層存儲結(jié)構(gòu)決定的聯(lián)合索引在 B 樹里的排序方式、聚簇索引和二級索引之間的協(xié)作方式共同決定了什么查詢能走捷徑。要真正理解得先看清主鍵索引到底長什么樣。2. 主鍵索引的底層形態(tài)聚簇索引與二級索引的協(xié)作關(guān)系2.1 B 樹在 InnoDB 里的存儲單元頁與層高先說基礎(chǔ)。InnoDB 的數(shù)據(jù)是按頁組織的默認(rèn)一頁 16KB。一顆 B 樹里非葉子節(jié)點(diǎn)頁只存索引鍵和指向子頁的指針?biāo)鼈冐?fù)責(zé)路由真正的數(shù)據(jù)全部落在葉子節(jié)點(diǎn)頁。查找時(shí)從根節(jié)點(diǎn)開始每層做一次二分定位逐層向下最后在葉子頁里找到目標(biāo)記錄。一顆 B 樹通常只有 2 到 4 層。做個(gè)粗略估算主鍵如果是 BIGINT8 字節(jié)加上子頁指針約 6 字節(jié)一個(gè) 16KB 的內(nèi)部頁大概能放 1000 個(gè)目錄項(xiàng)葉子頁按每行 200 字節(jié)算一個(gè)頁能放 80 行左右。三層 B 樹大致可以支撐 1000 × 1000 × 80也就是 8000 萬量級的數(shù)據(jù)。這里有個(gè)重要推論樹越高一次查找要訪問的頁越多。而前兩層因?yàn)閿?shù)據(jù)量小大概率常駐在 buffer pool 里真正可能產(chǎn)生磁盤 IO 的往往是最底層那一次——這也是為什么三層和四層的差距在熱數(shù)據(jù)場景下會非常明顯。2.2 聚簇索引整張表就是一棵主鍵樹InnoDB 里每張表必然有一棵聚簇索引樹這棵樹就是表本身。葉子頁里存的是完整的一行記錄行數(shù)據(jù)按主鍵順序物理排列。主鍵等值查詢和主鍵范圍查詢之所以快是因?yàn)橹苯釉诰鄞厮饕隙ㄎ灰淮尾檎揖湍苣玫秸?。一個(gè)常見的誤解是沒建主鍵就沒有聚簇索引。實(shí)際規(guī)則是建了主鍵用主鍵做聚簇索引沒有主鍵InnoDB 會選第一個(gè)非空唯一索引再沒有就生成一個(gè)隱藏的自增 rowid 列。也就是說聚簇索引永遠(yuǎn)存在主鍵只是最理想的那種選擇。這個(gè)設(shè)計(jì)也解釋了為什么主鍵一旦被更新整個(gè)行可能都要移動位置——因?yàn)榫鄞厮饕娜~子頁是按主鍵順序組織的行遷移的成本很高。2.3 二級索引只存索引字段和主鍵值普通索引、聯(lián)合索引、唯一索引在 InnoDB 里都屬于二級索引。二級索引也是一棵 B 樹但葉子節(jié)點(diǎn)不存整行只存兩部分索引列的值加上主鍵值。查詢時(shí)先在二級索引里找到目標(biāo)主鍵再拿著主鍵去聚簇索引里定位完整行這個(gè)額外的查找動作就叫回表。這里有個(gè)關(guān)鍵問題為什么二級索引的葉子不直接存行的物理地址因?yàn)槲锢淼刂凡环€(wěn)定。頁分裂、行遷移、碎片整理都會改變記錄的存放位置如果二級索引存了地址每次行移動都要把所有相關(guān)索引一起改。InnoDB 的選擇是讓二級索引存主鍵這個(gè)邏輯標(biāo)識行怎么移動都由聚簇索引自己維護(hù)二級索引完全不用感知。所以回表本質(zhì)上是一次額外的 B 樹查找掃描大量二級索引條目時(shí)回表次數(shù)會成倍放大查詢成本。2.4 主鍵大小會傳染到每一個(gè)二級索引既然每個(gè)二級索引的葉子節(jié)點(diǎn)都要帶一份主鍵值那么主鍵越長每一張二級索引的代價(jià)就越大。二級索引葉子頁能放的條目數(shù)變少樹就可能變高、IO 變多buffer pool 里能緩存的索引頁也變少。這是個(gè)連鎖反應(yīng)。我見過一張表主鍵用 CHAR(36) 的 UUID表上掛了 6 個(gè)二級索引數(shù)據(jù)量 2000 萬。后來把主鍵改成 BIGINT 自增并清洗數(shù)據(jù)單表空間直接降了將近 40%幾個(gè)高頻查詢的掃描行數(shù)也明顯下降。所以主鍵設(shè)計(jì)不只是選個(gè)唯一值的問題它直接影響整張表所有索引的體積和效率。后面第五節(jié)我會專門展開主鍵選擇的幾種方案。3. 聯(lián)合索引的構(gòu)建規(guī)則最左前綴背后的排序邏輯3.1 聯(lián)合索引的排序方式先排第一列再排第二列如果說清楚了一棵 B 樹怎么存主鍵、怎么存二級索引聯(lián)合索引的原理就順理成章了。聯(lián)合索引本質(zhì)上也是一棵二級索引 B 樹只是葉子和內(nèi)部節(jié)點(diǎn)里同時(shí)包含多列的值。所有條目先按第一列排序第一列相同的再按第二列排序以此類推。用一個(gè)生活類比理解聯(lián)合索引 (last_name, first_name) 就像按姓名排列的電話簿。你想找張偉只需要先在電話簿里翻到姓張的區(qū)域再在這個(gè)區(qū)域內(nèi)按名找非?？斓绻阒恢缹Ψ浇袀ゲ恢佬帐裁淳蜎]辦法翻到任何特定位置因?yàn)檎麄€(gè)電話簿不是按名排序的。3.2 五類典型查詢哪些命中哪些不命中以索引 (a, b, c) 為例MySQL 解析 WHERE 條件時(shí)的實(shí)際行為如下查詢條件索引中的定位方式實(shí)際效果a 1 AND b 2 AND c 3a、b、c 依次等值定位全部命中最優(yōu)a 1 AND b 2a 等值定位b 范圍定位完整命中b 之后的列無法定位a 1 ORDER BY ba 等值定位b 天然有序連排序都省了b 2 AND c 3無法從 a 開始定位聯(lián)合索引整體用不上特殊情況見 3.3a 1 AND b 2a 范圍定位b 無法定位b 不能用于定位但可交給 ICP 過濾這里要糾正一個(gè)流傳很廣的說法范圍條件后面的索引列會失效。更準(zhǔn)確的說法是范圍條件后面的列無法繼續(xù)在索引上做定位但索引下推機(jī)制仍然可以在存儲引擎掃描時(shí)過濾這些列減少回表。換句話說不是完全沒用而是定位能力降級成了過濾能力。這正好呼應(yīng)第 4 節(jié)要講的 ICP。再解釋一個(gè)容易繞暈的點(diǎn)為什么 b 2 AND c 3 這種查詢對 (a, b, c) 索引這么不友好因?yàn)樗饕w按 a 排序a 沒有約束時(shí)所有 a 值交織在一起b 2 的記錄在整個(gè)索引里并沒有連續(xù)區(qū)間。就像電話簿里偉散落在很多姓氏下面你沒法從第一個(gè)偉一路順下去讀。3.3 8.0 的松散索引掃描最左前綴的一個(gè)例外MySQL 8.0.13 之后引入了 Skip Scan松散索引掃描專門處理查詢條件沒有覆蓋聯(lián)合索引第一列的情況典型場景就是 WHERE b 2 而索引是 (a, b)。它的思路很直接既然 a 的不同取值個(gè)數(shù)不多那就把每個(gè) a 值都枚舉出來對每個(gè) a 值在索引內(nèi)部做一個(gè) (a具體值, b2) 的等值定位。相當(dāng)于把一個(gè)大問題拆成若干個(gè)小區(qū)間來查。代價(jià)也很明顯掃描成本跟 a 的 distinct 數(shù)量線性相關(guān)。如果 a 有 10 萬個(gè)不同值這個(gè)方案還不如直接掃全索引如果 a 只有 3 個(gè)值效果就很好。所以優(yōu)化器只有在判斷第一列區(qū)分度很低、枚舉成本可控時(shí)才會選擇它。這并不意味著最左前綴規(guī)則被廢棄了而是多了一種在特定數(shù)據(jù)分布下的兜底手段。4. 索引下推與覆蓋索引省回表的兩條隱蔽通道4.1 索引下推把過濾動作下沉到存儲引擎回表很貴所以一切能減少回表次數(shù)的機(jī)制都值得理解。索引下推ICP是 MySQL 5.6 引入的優(yōu)化默認(rèn)開啟。它的含義是原本需要回到 Server 層才能做的 WHERE 過濾現(xiàn)在可以提前到存儲引擎掃描索引的時(shí)候做。舉個(gè)例子索引 (a, b, c)查詢 WHERE a 1 AND c 5。a 用于索引定位c 因?yàn)樵?a 之后又缺少 b 的等值條件無法參與定位。沒有 ICP 時(shí)存儲引擎會把所有 a1 的索引條目連同主鍵值回傳給 Server 層Server 層再逐條判斷 c5命中的才回表拿整行這意味著大量無效回表。有了 ICP存儲引擎在掃描葉子節(jié)點(diǎn)時(shí)就把 c5 過濾掉只把滿足條件的條目交給 Server 層。回表次數(shù)從a1 的全部行降為a1 AND c5 的真實(shí)結(jié)果集。EXPLAIN 里如果 Extra 列出現(xiàn) Using index condition就說明走的是索引下推。注意它和索引失效完全不是一回事——恰恰相反它是在索引無法完整定位時(shí)盡量把索引的剩余價(jià)值榨干。4.2 覆蓋索引整個(gè)查詢在一棵索引樹里完成覆蓋索引是另一個(gè)減少回表的手段邏輯更簡單如果查詢要的列全部包含在某個(gè)二級索引里MySQL 就直接讀索引葉子頁根本不需要回表。EXPLAIN 的 Extra 列會顯示 Using index。舉一個(gè)典型場景訂單表上有 (user_id, create_time, amount) 聯(lián)合索引查詢是 SELECT amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 10:30:00。索引葉子節(jié)點(diǎn)里有 user_id、create_time也有 amount三列都齊了查詢?nèi)滩恍枰鼍鄞厮饕τ诟哳l的點(diǎn)查接口這種設(shè)計(jì)收益非?？捎^。但覆蓋索引不是免費(fèi)的。索引列越多索引體積越大寫入時(shí)維護(hù)成本越高。更關(guān)鍵的是別指望 SELECT * 也能覆蓋因?yàn)?SELECT * 通常意味著把整行都查出來二級索引里根本放不下。所以覆蓋索引要挑高頻且字段固定的查詢來做。這也是為什么我一直建議大家寫 SQL 時(shí)按需查列而不是順手 SELECT *。4.3 從 EXPLAIN 的 Extra 列判斷執(zhí)行路徑排查慢查詢時(shí)比起看 MySQL 到底用沒用索引更重要的是看查詢實(shí)際走了什么路徑。EXPLAIN 輸出里我重點(diǎn)看這幾項(xiàng)字段含義重點(diǎn)關(guān)注type訪問類型const/ref 優(yōu)于 range再優(yōu)于 index 和 ALLkey實(shí)際選擇的索引注意是不是被另一個(gè)看起來更對的索引搶走了key_len實(shí)際用于定位的索引字節(jié)數(shù)判斷聯(lián)合索引用了哪幾列rows預(yù)估掃描行數(shù)和實(shí)際結(jié)果集對比差距越大越可疑Extra附加信息Using index / Using index condition / Using filesort 三種都要認(rèn)看一個(gè)具體例子mysql EXPLAIN SELECT id, amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01\G *************************** 1. row *************************** type: range key: idx_user_time key_len: 9 rows: 120 Extra: Using index conditionkey_len 是 9說明 user_idINT4 字節(jié)和 create_timeDATETIME5 字節(jié)都參與了定位。如果這里 create_time 沒有參與定位key_len 只會顯示 4rows 會明顯變大。我對 key_len 的判斷方法是把 key_len 和聯(lián)合索引的列寬度對應(yīng)上就能知道優(yōu)化器實(shí)際吃到索引的哪一列這是排查索引設(shè)計(jì)問題最直接的證據(jù)。5. 實(shí)戰(zhàn)設(shè)計(jì)經(jīng)驗(yàn)字段順序、主鍵選擇與索引取舍5.1 字段排列的四條經(jīng)驗(yàn)法則講完原理落到設(shè)計(jì)上。聯(lián)合索引字段順序我一般按下面四條原則排等值條件的列放前面范圍條件的列放后面。等值列可以精準(zhǔn)定位范圍列只能圈定區(qū)間讓區(qū)間盡量小才是關(guān)鍵。多個(gè)等值列之間區(qū)分度高的通常放前面。如果 user_id 能篩掉 99% 的數(shù)據(jù)而 type 只能篩掉一半優(yōu)先把 user_id 放前面。把 ORDER BY、GROUP BY 用到的列考慮進(jìn)去。索引本身有序能契合排序需求就不用 filesort這是很多排序查詢的關(guān)鍵優(yōu)化。前綴列盡量短。索引整體寬度決定葉子頁密度字符串字段做前綴索引或放后面都是減少體積的手段。舉個(gè)組合例子業(yè)務(wù)查詢是 WHERE user_id ? AND status ? ORDER BY create_time DESC那優(yōu)先考慮 (user_id, create_time) 或者 (user_id, status, create_time)。user_id 等值定位后create_time 天然有序排序省了status 如果是一個(gè)低基數(shù)字段可以放 create_time 后面用 ICP 過濾也可以根據(jù) status 的過濾能力決定要不要把它提前。沒有放之四海而皆準(zhǔn)的公式核心是拿真實(shí)數(shù)據(jù)分布去驗(yàn)證。5.2 主鍵三選一自增、UUID、趨勢遞增ID主鍵選擇是另一個(gè)影響全局的決定。方案寫入方式索引體積適用場景BIGINT 自增順序追加頁分裂最少最小絕大多數(shù)內(nèi)部業(yè)務(wù)表UUID 字符串隨機(jī)寫入頁分裂頻繁碎片多大拖累所有二級索引多端生成、需要防枚舉的業(yè)務(wù)趨勢遞增ID雪花類近似順序頁分裂較少中分布式系統(tǒng)需要全局唯一自增主鍵為什么寫入友好因?yàn)樾略鲇涗浀闹麈I越來越大聚簇索引總是往最新頁的尾部追加新頁順序?qū)憹M即可。UUID 主鍵則不同新記錄可能落在樹的任何位置隨機(jī)觸發(fā)頁分裂造成空間碎片和大量額外寫 IO。而且 UUID 哪怕?lián)Q成 BINARY(16) 存儲隨機(jī)性依然存在頁分裂問題并不會消失。另外有個(gè)容易被忽視的點(diǎn)主鍵必須永遠(yuǎn)不變。因?yàn)榫鄞厮饕~子頁按主鍵排序改主鍵幾乎等于移動整行牽連所有二級索引更新。所以業(yè)務(wù)上看起來唯一的字段例如手機(jī)號、郵箱、身份證號都不適合直接做主鍵因?yàn)樗鼈兌加斜恍薷牡目赡苄浴＿@也是代理主鍵這種設(shè)計(jì)能成為主流的原因之一。5.3 用系統(tǒng)視圖找出冗余索引冗余索引是線上數(shù)據(jù)庫最常見的隱形殺手。識別邏輯其實(shí)很樸素如果索引 A 的前綴列和索引 B 完全一致而 B 的列覆蓋更全那么 A 大概率是冗余的。舉例表上同時(shí)有 (a) 和 (a, b) 兩個(gè)索引。所有只用 a 的查詢走 (a, b) 也能滿足最左前綴但 (a) 的存在讓每次寫入都要多維護(hù)一棵 B 樹。這就是典型的冗余應(yīng)該刪掉 (a)。同理有 (a, b, c) 又有 (a, b) 時(shí)后者通常冗余。注意(a) 和 (b) 不是冗余關(guān)系(b, a) 和 (a, b) 也不是因?yàn)樽钭笄熬Y方向不同。MySQL 5.7.23 之后的 sys schema 里有一張 schema_redundant_indexes 視圖可以直接查出冗余索引對不用自己一條條比對。沒有 sys schema 的版本也可以查 information_schema.STATISTICS按 TABLE_NAME 分組對比前綴列。清理冗余索引對寫入性能的提升往往比加新索引還明顯。5.4 三個(gè)常見設(shè)計(jì)誤區(qū)最后匯總?cè)齻€(gè)我在實(shí)際項(xiàng)目里反復(fù)見過的誤區(qū)。第一個(gè)誤區(qū)是索引越多越好。每個(gè)索引都是一棵 B 樹每次 INSERT、UPDATE、DELETE 都要全量維護(hù)一遍。索引過多會直接放大寫入延遲索引頁還會占用 buffer pool 空間擠壓真正需要緩存的數(shù)據(jù)頁。索引不是保險(xiǎn)是成本。第二個(gè)誤區(qū)是對覆蓋索引的期望過高。有人為了覆蓋把十幾個(gè)列全塞進(jìn)一個(gè)聯(lián)合索引索引寬度爆炸葉子頁密度驟降反而拖慢掃描。覆蓋索引只適合高頻、字段固定的查詢一般撐死三到五列再多就要重新評估。第三個(gè)誤區(qū)是忽略數(shù)據(jù)分布的變化。三個(gè)月前區(qū)分度高的字段可能因?yàn)樾聵I(yè)務(wù)模式變成低基數(shù)字段之前的索引設(shè)計(jì)隨之失效。慢查詢排查時(shí)如果發(fā)現(xiàn) rows 和預(yù)期差很多別著急加索引先看看數(shù)據(jù)分布是不是變了統(tǒng)計(jì)信息是不是過期了再決定是優(yōu)化索引還是優(yōu)化查詢。最后說一個(gè)我這些年養(yǎng)成的習(xí)慣。每次寫完一條新查詢我不會急著上線而是先跑一遍 EXPLAIN重點(diǎn)看兩個(gè)數(shù)字key_len 是否用到了我預(yù)期的那幾列rows 是否比實(shí)際結(jié)果集大不了多少。如果看到 Using filesort或者 key 突然變成了別的索引我都會停下來想想是統(tǒng)計(jì)信息過期了還是索引設(shè)計(jì)本身有沖突。索引設(shè)計(jì)沒有銀彈數(shù)據(jù)分布變了、查詢模式變了之前的規(guī)則都可能要推翻。但只要把聚簇索引、二級索引、最左前綴這幾層原理放在腦子里排查慢查詢時(shí)你就有了一條清晰的推理鏈而不是靠猜。 MySQL索引底層原理：聚簇索引、聯(lián)合索引與回表優(yōu)化實(shí)戰(zhàn)
上周被拉去排查一個(gè)慢查詢某項(xiàng)目的訂單表已經(jīng) 800 萬行一條很普通的查詢SELECT * FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;跑一次要 2 秒多。表上明明有聯(lián)合索引 idx_user_time(user_id, create_time)EXPLAIN 也顯示用上了索引但實(shí)際大部分時(shí)間耗在回表上。這類問題我不是第一次遇到了。很多人對 MySQL 主鍵索引和聯(lián)合索引的理解停留在知道有 B 樹、知道最左前綴的層面真到設(shè)計(jì)索引、排查慢 SQL 的時(shí)候全靠試。這篇文章想把這兩類索引的底層原理講透聯(lián)合索引為什么講究列順序主鍵索引為什么影響整張表所有索引索引下推和覆蓋索引又是怎么幫我們省回表成本的適合正在被慢查詢折磨的開發(fā)者也適合準(zhǔn)備數(shù)據(jù)庫面試的人我會把原理和實(shí)操放在一起講但核心始終是那一棵 B 樹。1. 從兩個(gè)真實(shí)查詢說起聯(lián)合索引列順序如何影響性能1.1 準(zhǔn)備一張演示表兩個(gè)順序相反的聯(lián)合索引為了方便說明先造一張訂單演示表。注意這張表上我故意掛了兩個(gè)列順序相反的聯(lián)合索引真實(shí)生產(chǎn)環(huán)境不要這么干后面會專門解釋為什么這是冗余設(shè)計(jì)。CREATE TABLE user_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_time (user_id, create_time), KEY idx_time_user (create_time, user_id) ) ENGINEInnoDB;假設(shè)業(yè)務(wù)特征是這樣的10 萬個(gè)活躍用戶平均每個(gè)用戶 80 條訂單訂單的時(shí)間分布集中在最近半年。數(shù)據(jù)量、數(shù)據(jù)分布都清楚了下面看兩個(gè)典型查詢。1.2 同一條查詢?yōu)槭裁此饕樞蜃兞舜鷥r(jià)就變查詢一查某個(gè)用戶最近的訂單。SELECT id, order_no, amount, create_time FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 ORDER BY create_time DESC;走 idx_user_time 時(shí)索引樹先定位到 user_id123 這個(gè)位置然后沿著葉子節(jié)點(diǎn)鏈表向后掃描 create_time 大于指定值的記錄掃出來的數(shù)據(jù)天然就是按 (user_id, create_time) 排序的連 ORDER BY 的額外排序都省了。這個(gè)路徑的掃描范圍很小——主鍵等值定位一次剩下只需要讀取該用戶自己的那批索引條目。走 idx_time_user 時(shí)情況就完全不同了。create_time 是聯(lián)合索引的第一列user_id 不是索引的前綴列所以 MySQL 只能先定位到 create_time 2024-06-01 這個(gè)索引區(qū)間這個(gè)區(qū)間可能橫跨幾十萬甚至上百萬條索引條目掃描過程中再把 user_id123 的記錄過濾出來。雖然 MySQL 8.0 的索引下推ICP能在存儲引擎層幫我們過濾 user_id減少一部分回表但區(qū)間起點(diǎn)只能按 create_time 定位中間要掃的索引條目數(shù)量級完全不一樣。查詢二統(tǒng)計(jì)某個(gè)時(shí)間段內(nèi)的訂單量。SELECT COUNT(*) FROM user_orders WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30;這次反轉(zhuǎn)了。idx_time_user 可以精準(zhǔn)定位到 6 月 1 日這個(gè)起點(diǎn)只掃一個(gè)月的索引區(qū)間計(jì)數(shù)器加上去就完事而 idx_user_time 因?yàn)?create_time 不是第一列完全無法直接從時(shí)間上定位只能把整棵索引樹掃一遍把所有 create_time 落在范圍內(nèi)的條目數(shù)出來。1.3 兩種查詢模式對照查詢模式索引 (user_id, create_time)索引 (create_time, user_id)某用戶 時(shí)間范圍等值定位后掃小區(qū)間無需排序先掃大時(shí)間范圍再過濾掃描量大純時(shí)間范圍統(tǒng)計(jì)無法直接定位整樹掃描時(shí)間定位精準(zhǔn)掃描量小同樣是兩個(gè)字段的聯(lián)合索引只是順序換了一下同一批查詢的代價(jià)可能差兩個(gè)數(shù)量級。這里的差別不是 MySQL 優(yōu)化器偷懶而是底層存儲結(jié)構(gòu)決定的聯(lián)合索引在 B 樹里的排序方式、聚簇索引和二級索引之間的協(xié)作方式共同決定了什么查詢能走捷徑。要真正理解得先看清主鍵索引到底長什么樣。2. 主鍵索引的底層形態(tài)聚簇索引與二級索引的協(xié)作關(guān)系2.1 B 樹在 InnoDB 里的存儲單元頁與層高先說基礎(chǔ)。InnoDB 的數(shù)據(jù)是按頁組織的默認(rèn)一頁 16KB。一顆 B 樹里非葉子節(jié)點(diǎn)頁只存索引鍵和指向子頁的指針?biāo)鼈冐?fù)責(zé)路由真正的數(shù)據(jù)全部落在葉子節(jié)點(diǎn)頁。查找時(shí)從根節(jié)點(diǎn)開始每層做一次二分定位逐層向下最后在葉子頁里找到目標(biāo)記錄。一顆 B 樹通常只有 2 到 4 層。做個(gè)粗略估算主鍵如果是 BIGINT8 字節(jié)加上子頁指針約 6 字節(jié)一個(gè) 16KB 的內(nèi)部頁大概能放 1000 個(gè)目錄項(xiàng)葉子頁按每行 200 字節(jié)算一個(gè)頁能放 80 行左右。三層 B 樹大致可以支撐 1000 × 1000 × 80也就是 8000 萬量級的數(shù)據(jù)。這里有個(gè)重要推論樹越高一次查找要訪問的頁越多。而前兩層因?yàn)閿?shù)據(jù)量小大概率常駐在 buffer pool 里真正可能產(chǎn)生磁盤 IO 的往往是最底層那一次——這也是為什么三層和四層的差距在熱數(shù)據(jù)場景下會非常明顯。2.2 聚簇索引整張表就是一棵主鍵樹InnoDB 里每張表必然有一棵聚簇索引樹這棵樹就是表本身。葉子頁里存的是完整的一行記錄行數(shù)據(jù)按主鍵順序物理排列。主鍵等值查詢和主鍵范圍查詢之所以快是因?yàn)橹苯釉诰鄞厮饕隙ㄎ灰淮尾檎揖湍苣玫秸?。一個(gè)常見的誤解是沒建主鍵就沒有聚簇索引。實(shí)際規(guī)則是建了主鍵用主鍵做聚簇索引沒有主鍵InnoDB 會選第一個(gè)非空唯一索引再沒有就生成一個(gè)隱藏的自增 rowid 列。也就是說聚簇索引永遠(yuǎn)存在主鍵只是最理想的那種選擇。這個(gè)設(shè)計(jì)也解釋了為什么主鍵一旦被更新整個(gè)行可能都要移動位置——因?yàn)榫鄞厮饕娜~子頁是按主鍵順序組織的行遷移的成本很高。2.3 二級索引只存索引字段和主鍵值普通索引、聯(lián)合索引、唯一索引在 InnoDB 里都屬于二級索引。二級索引也是一棵 B 樹但葉子節(jié)點(diǎn)不存整行只存兩部分索引列的值加上主鍵值。查詢時(shí)先在二級索引里找到目標(biāo)主鍵再拿著主鍵去聚簇索引里定位完整行這個(gè)額外的查找動作就叫回表。這里有個(gè)關(guān)鍵問題為什么二級索引的葉子不直接存行的物理地址因?yàn)槲锢淼刂凡环€(wěn)定。頁分裂、行遷移、碎片整理都會改變記錄的存放位置如果二級索引存了地址每次行移動都要把所有相關(guān)索引一起改。InnoDB 的選擇是讓二級索引存主鍵這個(gè)邏輯標(biāo)識行怎么移動都由聚簇索引自己維護(hù)二級索引完全不用感知。所以回表本質(zhì)上是一次額外的 B 樹查找掃描大量二級索引條目時(shí)回表次數(shù)會成倍放大查詢成本。2.4 主鍵大小會傳染到每一個(gè)二級索引既然每個(gè)二級索引的葉子節(jié)點(diǎn)都要帶一份主鍵值那么主鍵越長每一張二級索引的代價(jià)就越大。二級索引葉子頁能放的條目數(shù)變少樹就可能變高、IO 變多buffer pool 里能緩存的索引頁也變少。這是個(gè)連鎖反應(yīng)。我見過一張表主鍵用 CHAR(36) 的 UUID表上掛了 6 個(gè)二級索引數(shù)據(jù)量 2000 萬。后來把主鍵改成 BIGINT 自增并清洗數(shù)據(jù)單表空間直接降了將近 40%幾個(gè)高頻查詢的掃描行數(shù)也明顯下降。所以主鍵設(shè)計(jì)不只是選個(gè)唯一值的問題它直接影響整張表所有索引的體積和效率。后面第五節(jié)我會專門展開主鍵選擇的幾種方案。3. 聯(lián)合索引的構(gòu)建規(guī)則最左前綴背后的排序邏輯3.1 聯(lián)合索引的排序方式先排第一列再排第二列如果說清楚了一棵 B 樹怎么存主鍵、怎么存二級索引聯(lián)合索引的原理就順理成章了。聯(lián)合索引本質(zhì)上也是一棵二級索引 B 樹只是葉子和內(nèi)部節(jié)點(diǎn)里同時(shí)包含多列的值。所有條目先按第一列排序第一列相同的再按第二列排序以此類推。用一個(gè)生活類比理解聯(lián)合索引 (last_name, first_name) 就像按姓名排列的電話簿。你想找張偉只需要先在電話簿里翻到姓張的區(qū)域再在這個(gè)區(qū)域內(nèi)按名找非?？斓绻阒恢缹Ψ浇袀ゲ恢佬帐裁淳蜎]辦法翻到任何特定位置因?yàn)檎麄€(gè)電話簿不是按名排序的。3.2 五類典型查詢哪些命中哪些不命中以索引 (a, b, c) 為例MySQL 解析 WHERE 條件時(shí)的實(shí)際行為如下查詢條件索引中的定位方式實(shí)際效果a 1 AND b 2 AND c 3a、b、c 依次等值定位全部命中最優(yōu)a 1 AND b 2a 等值定位b 范圍定位完整命中b 之后的列無法定位a 1 ORDER BY ba 等值定位b 天然有序連排序都省了b 2 AND c 3無法從 a 開始定位聯(lián)合索引整體用不上特殊情況見 3.3a 1 AND b 2a 范圍定位b 無法定位b 不能用于定位但可交給 ICP 過濾這里要糾正一個(gè)流傳很廣的說法范圍條件后面的索引列會失效。更準(zhǔn)確的說法是范圍條件后面的列無法繼續(xù)在索引上做定位但索引下推機(jī)制仍然可以在存儲引擎掃描時(shí)過濾這些列減少回表。換句話說不是完全沒用而是定位能力降級成了過濾能力。這正好呼應(yīng)第 4 節(jié)要講的 ICP。再解釋一個(gè)容易繞暈的點(diǎn)為什么 b 2 AND c 3 這種查詢對 (a, b, c) 索引這么不友好因?yàn)樗饕w按 a 排序a 沒有約束時(shí)所有 a 值交織在一起b 2 的記錄在整個(gè)索引里并沒有連續(xù)區(qū)間。就像電話簿里偉散落在很多姓氏下面你沒法從第一個(gè)偉一路順下去讀。3.3 8.0 的松散索引掃描最左前綴的一個(gè)例外MySQL 8.0.13 之后引入了 Skip Scan松散索引掃描專門處理查詢條件沒有覆蓋聯(lián)合索引第一列的情況典型場景就是 WHERE b 2 而索引是 (a, b)。它的思路很直接既然 a 的不同取值個(gè)數(shù)不多那就把每個(gè) a 值都枚舉出來對每個(gè) a 值在索引內(nèi)部做一個(gè) (a具體值, b2) 的等值定位。相當(dāng)于把一個(gè)大問題拆成若干個(gè)小區(qū)間來查。代價(jià)也很明顯掃描成本跟 a 的 distinct 數(shù)量線性相關(guān)。如果 a 有 10 萬個(gè)不同值這個(gè)方案還不如直接掃全索引如果 a 只有 3 個(gè)值效果就很好。所以優(yōu)化器只有在判斷第一列區(qū)分度很低、枚舉成本可控時(shí)才會選擇它。這并不意味著最左前綴規(guī)則被廢棄了而是多了一種在特定數(shù)據(jù)分布下的兜底手段。4. 索引下推與覆蓋索引省回表的兩條隱蔽通道4.1 索引下推把過濾動作下沉到存儲引擎回表很貴所以一切能減少回表次數(shù)的機(jī)制都值得理解。索引下推ICP是 MySQL 5.6 引入的優(yōu)化默認(rèn)開啟。它的含義是原本需要回到 Server 層才能做的 WHERE 過濾現(xiàn)在可以提前到存儲引擎掃描索引的時(shí)候做。舉個(gè)例子索引 (a, b, c)查詢 WHERE a 1 AND c 5。a 用于索引定位c 因?yàn)樵?a 之后又缺少 b 的等值條件無法參與定位。沒有 ICP 時(shí)存儲引擎會把所有 a1 的索引條目連同主鍵值回傳給 Server 層Server 層再逐條判斷 c5命中的才回表拿整行這意味著大量無效回表。有了 ICP存儲引擎在掃描葉子節(jié)點(diǎn)時(shí)就把 c5 過濾掉只把滿足條件的條目交給 Server 層。回表次數(shù)從a1 的全部行降為a1 AND c5 的真實(shí)結(jié)果集。EXPLAIN 里如果 Extra 列出現(xiàn) Using index condition就說明走的是索引下推。注意它和索引失效完全不是一回事——恰恰相反它是在索引無法完整定位時(shí)盡量把索引的剩余價(jià)值榨干。4.2 覆蓋索引整個(gè)查詢在一棵索引樹里完成覆蓋索引是另一個(gè)減少回表的手段邏輯更簡單如果查詢要的列全部包含在某個(gè)二級索引里MySQL 就直接讀索引葉子頁根本不需要回表。EXPLAIN 的 Extra 列會顯示 Using index。舉一個(gè)典型場景訂單表上有 (user_id, create_time, amount) 聯(lián)合索引查詢是 SELECT amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01 10:30:00。索引葉子節(jié)點(diǎn)里有 user_id、create_time也有 amount三列都齊了查詢?nèi)滩恍枰鼍鄞厮饕τ诟哳l的點(diǎn)查接口這種設(shè)計(jì)收益非?？捎^。但覆蓋索引不是免費(fèi)的。索引列越多索引體積越大寫入時(shí)維護(hù)成本越高。更關(guān)鍵的是別指望 SELECT * 也能覆蓋因?yàn)?SELECT * 通常意味著把整行都查出來二級索引里根本放不下。所以覆蓋索引要挑高頻且字段固定的查詢來做。這也是為什么我一直建議大家寫 SQL 時(shí)按需查列而不是順手 SELECT *。4.3 從 EXPLAIN 的 Extra 列判斷執(zhí)行路徑排查慢查詢時(shí)比起看 MySQL 到底用沒用索引更重要的是看查詢實(shí)際走了什么路徑。EXPLAIN 輸出里我重點(diǎn)看這幾項(xiàng)字段含義重點(diǎn)關(guān)注type訪問類型const/ref 優(yōu)于 range再優(yōu)于 index 和 ALLkey實(shí)際選擇的索引注意是不是被另一個(gè)看起來更對的索引搶走了key_len實(shí)際用于定位的索引字節(jié)數(shù)判斷聯(lián)合索引用了哪幾列rows預(yù)估掃描行數(shù)和實(shí)際結(jié)果集對比差距越大越可疑Extra附加信息Using index / Using index condition / Using filesort 三種都要認(rèn)看一個(gè)具體例子mysql EXPLAIN SELECT id, amount FROM user_orders WHERE user_id 123 AND create_time 2024-06-01\G *************************** 1. row *************************** type: range key: idx_user_time key_len: 9 rows: 120 Extra: Using index conditionkey_len 是 9說明 user_idINT4 字節(jié)和 create_timeDATETIME5 字節(jié)都參與了定位。如果這里 create_time 沒有參與定位key_len 只會顯示 4rows 會明顯變大。我對 key_len 的判斷方法是把 key_len 和聯(lián)合索引的列寬度對應(yīng)上就能知道優(yōu)化器實(shí)際吃到索引的哪一列這是排查索引設(shè)計(jì)問題最直接的證據(jù)。5. 實(shí)戰(zhàn)設(shè)計(jì)經(jīng)驗(yàn)字段順序、主鍵選擇與索引取舍5.1 字段排列的四條經(jīng)驗(yàn)法則講完原理落到設(shè)計(jì)上。聯(lián)合索引字段順序我一般按下面四條原則排等值條件的列放前面范圍條件的列放后面。等值列可以精準(zhǔn)定位范圍列只能圈定區(qū)間讓區(qū)間盡量小才是關(guān)鍵。多個(gè)等值列之間區(qū)分度高的通常放前面。如果 user_id 能篩掉 99% 的數(shù)據(jù)而 type 只能篩掉一半優(yōu)先把 user_id 放前面。把 ORDER BY、GROUP BY 用到的列考慮進(jìn)去。索引本身有序能契合排序需求就不用 filesort這是很多排序查詢的關(guān)鍵優(yōu)化。前綴列盡量短。索引整體寬度決定葉子頁密度字符串字段做前綴索引或放后面都是減少體積的手段。舉個(gè)組合例子業(yè)務(wù)查詢是 WHERE user_id ? AND status ? ORDER BY create_time DESC那優(yōu)先考慮 (user_id, create_time) 或者 (user_id, status, create_time)。user_id 等值定位后create_time 天然有序排序省了status 如果是一個(gè)低基數(shù)字段可以放 create_time 后面用 ICP 過濾也可以根據(jù) status 的過濾能力決定要不要把它提前。沒有放之四海而皆準(zhǔn)的公式核心是拿真實(shí)數(shù)據(jù)分布去驗(yàn)證。5.2 主鍵三選一自增、UUID、趨勢遞增ID主鍵選擇是另一個(gè)影響全局的決定。方案寫入方式索引體積適用場景BIGINT 自增順序追加頁分裂最少最小絕大多數(shù)內(nèi)部業(yè)務(wù)表UUID 字符串隨機(jī)寫入頁分裂頻繁碎片多大拖累所有二級索引多端生成、需要防枚舉的業(yè)務(wù)趨勢遞增ID雪花類近似順序頁分裂較少中分布式系統(tǒng)需要全局唯一自增主鍵為什么寫入友好因?yàn)樾略鲇涗浀闹麈I越來越大聚簇索引總是往最新頁的尾部追加新頁順序?qū)憹M即可。UUID 主鍵則不同新記錄可能落在樹的任何位置隨機(jī)觸發(fā)頁分裂造成空間碎片和大量額外寫 IO。而且 UUID 哪怕?lián)Q成 BINARY(16) 存儲隨機(jī)性依然存在頁分裂問題并不會消失。另外有個(gè)容易被忽視的點(diǎn)主鍵必須永遠(yuǎn)不變。因?yàn)榫鄞厮饕~子頁按主鍵排序改主鍵幾乎等于移動整行牽連所有二級索引更新。所以業(yè)務(wù)上看起來唯一的字段例如手機(jī)號、郵箱、身份證號都不適合直接做主鍵因?yàn)樗鼈兌加斜恍薷牡目赡苄浴＿@也是代理主鍵這種設(shè)計(jì)能成為主流的原因之一。5.3 用系統(tǒng)視圖找出冗余索引冗余索引是線上數(shù)據(jù)庫最常見的隱形殺手。識別邏輯其實(shí)很樸素如果索引 A 的前綴列和索引 B 完全一致而 B 的列覆蓋更全那么 A 大概率是冗余的。舉例表上同時(shí)有 (a) 和 (a, b) 兩個(gè)索引。所有只用 a 的查詢走 (a, b) 也能滿足最左前綴但 (a) 的存在讓每次寫入都要多維護(hù)一棵 B 樹。這就是典型的冗余應(yīng)該刪掉 (a)。同理有 (a, b, c) 又有 (a, b) 時(shí)后者通常冗余。注意(a) 和 (b) 不是冗余關(guān)系(b, a) 和 (a, b) 也不是因?yàn)樽钭笄熬Y方向不同。MySQL 5.7.23 之后的 sys schema 里有一張 schema_redundant_indexes 視圖可以直接查出冗余索引對不用自己一條條比對。沒有 sys schema 的版本也可以查 information_schema.STATISTICS按 TABLE_NAME 分組對比前綴列。清理冗余索引對寫入性能的提升往往比加新索引還明顯。5.4 三個(gè)常見設(shè)計(jì)誤區(qū)最后匯總?cè)齻€(gè)我在實(shí)際項(xiàng)目里反復(fù)見過的誤區(qū)。第一個(gè)誤區(qū)是索引越多越好。每個(gè)索引都是一棵 B 樹每次 INSERT、UPDATE、DELETE 都要全量維護(hù)一遍。索引過多會直接放大寫入延遲索引頁還會占用 buffer pool 空間擠壓真正需要緩存的數(shù)據(jù)頁。索引不是保險(xiǎn)是成本。第二個(gè)誤區(qū)是對覆蓋索引的期望過高。有人為了覆蓋把十幾個(gè)列全塞進(jìn)一個(gè)聯(lián)合索引索引寬度爆炸葉子頁密度驟降反而拖慢掃描。覆蓋索引只適合高頻、字段固定的查詢一般撐死三到五列再多就要重新評估。第三個(gè)誤區(qū)是忽略數(shù)據(jù)分布的變化。三個(gè)月前區(qū)分度高的字段可能因?yàn)樾聵I(yè)務(wù)模式變成低基數(shù)字段之前的索引設(shè)計(jì)隨之失效。慢查詢排查時(shí)如果發(fā)現(xiàn) rows 和預(yù)期差很多別著急加索引先看看數(shù)據(jù)分布是不是變了統(tǒng)計(jì)信息是不是過期了再決定是優(yōu)化索引還是優(yōu)化查詢。最后說一個(gè)我這些年養(yǎng)成的習(xí)慣。每次寫完一條新查詢我不會急著上線而是先跑一遍 EXPLAIN重點(diǎn)看兩個(gè)數(shù)字key_len 是否用到了我預(yù)期的那幾列rows 是否比實(shí)際結(jié)果集大不了多少。如果看到 Using filesort或者 key 突然變成了別的索引我都會停下來想想是統(tǒng)計(jì)信息過期了還是索引設(shè)計(jì)本身有沖突。索引設(shè)計(jì)沒有銀彈數(shù)據(jù)分布變了、查詢模式變了之前的規(guī)則都可能要推翻。但只要把聚簇索引、二級索引、最左前綴這幾層原理放在腦子里排查慢查詢時(shí)你就有了一條清晰的推理鏈而不是靠猜。