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