化：從B+樹到慢查詢排查實戰(zhàn))
很多同學學 MySQL 都是靠視頻入門索引、B樹、SQL優(yōu)化這些詞聽起來都很熟但一到線上問題就露餡明明建了索引查詢還是慢明明 SQL 看著沒問題explain 一執(zhí)行卻走了全表掃描面試背了一堆“最左前綴”“索引失效”被追問底層原因時直接卡住。這套知識之所以難不是因為它有多高深而是因為大多數教程把“原理”和“實踐”拆開了。講 B樹的時候不講 SQL 怎么寫才走索引講 SQL 優(yōu)化的時候又不解釋為什么 MySQL 要這樣執(zhí)行。結果是背了一堆結論遇到新問題完全不會遷移。這篇文章不打算復述某個視頻的目錄而是把索引、B樹、聯(lián)合索引、SQL 優(yōu)化、慢查詢定位、高頻面試題串成一條完整的知識鏈路。讀完后你至少能做到三件事第一給一張表設計合理的索引不再“憑感覺加索引”第二遇到慢 SQL 能按固定思路排查知道先看哪里、再看哪里第三面試被問到底層原理時能用手畫 B樹的方式把過程講清楚。1. 這篇文章真正要解決的問題先說說我觀察到的普遍現象。很多開發(fā)者的 MySQL 知識是碎片化的知道索引能加速查詢但不知道索引為什么能加速知道聯(lián)合索引有最左前綴原則但不知道這個原則是怎么推導出來的知道 explain 能看執(zhí)行計劃但看到 type 為 ref 還是 range 時判斷不了要不要優(yōu)化。這些碎片化知識最直接的后果是線上 SQL 變慢時只能靠猜。網上說“不要在索引列上做函數操作”你就把所有函數調用都去掉網上說“select 不要用 *”你就把所有 select * 改成顯式字段。這些建議本身沒大問題但沒有底層邏輯支撐你永遠不知道邊界在哪里。這篇文章要幫你解決的核心問題是建立一條“數據結構 → 索引設計 → SQL 寫法 → 執(zhí)行計劃驗證 → 慢查詢治理 → 面試表達”的完整鏈路。你會先理解 B樹為什么是 MySQL 索引的默認數據結構再理解聚簇索引和二級索引的區(qū)別然后掌握聯(lián)合索引的字段順序設計方法最后能熟練用 explain 和慢查詢日志定位線上問題。一句話總結這不是一篇“記住 20 條優(yōu)化技巧”的文章而是一篇幫你把 MySQL 索引和 SQL 優(yōu)化體系化梳理的文章。適合正在準備 MySQL 面試的開發(fā)者也適合被線上慢查詢折磨得焦頭爛額的日常開發(fā)。2. 核心概念B樹、索引與聚簇索引2.1 B樹為什么是 MySQL 索引的默認選擇要理解索引先理解存儲。MySQL 的數據最終是存在磁盤上的而磁盤隨機讀寫的速度比內存慢好幾個數量級。所以存儲引擎設計索引的第一目標是盡量減少磁盤隨機 IO 的次數。B樹就是一種專門為磁盤存儲設計的多路平衡查找樹。它有兩個典型特征第一所有數據都存放在葉子節(jié)點非葉子節(jié)點只存放索引鍵值。這意味著每次查找都必須走到葉子節(jié)點才能拿到數據所以任何一個查詢的 IO 次數是相對穩(wěn)定的基本等于樹的層數。第二葉子節(jié)點之間用指針串聯(lián)成了有序鏈表。這個設計讓范圍查詢變得非常高效。比如查 id 大于 100 且小于 200 的數據只要找到 100然后順著鏈表往后讀就行了不需要回根節(jié)點重新遍歷。從數據量角度算一筆賬。假設一個非葉子節(jié)點能存 1000 個鍵值三層 B樹可以存儲大約 10 億條記錄。也就是說從 10 億條數據里查一條記錄大概只需要 3 到 4 次磁盤 IO。這正是 B樹能支撐海量數據在線查詢的根本原因。2.2 聚簇索引、非聚簇索引與回表InnoDB 存儲引擎的索引結構需要區(qū)分兩種聚簇索引和二級索引。聚簇索引的特點是索引鍵值決定數據的物理存儲順序。InnoDB 的聚簇索引就是主鍵索引。當你建表時沒有顯式指定主鍵InnoDB 會選擇一個沒有 NULL 的唯一鍵作為主鍵如果也沒有它會生成一個隱藏的 ROWID 作為聚簇索引。所以從實踐角度看每張 InnoDB 表都應該有主鍵主鍵最好是自增整型這樣新數據的物理插入位置永遠在末尾不會頻繁觸發(fā)頁分裂。二級索引也叫非聚簇索引它的葉子節(jié)點存放的是索引鍵值和主鍵值。這里有一個非常關鍵的設計通過二級索引查數據時先用索引找到主鍵再用主鍵回聚簇索引查完整行。這個過程叫回表。用一個例子說明。表 user 有主鍵 id還有二級索引 idx_name(name)。執(zhí)行 SQLSELECT * FROM user WHERE name 張三;MySQL 會先走 idx_name 索引找到 name 為“張三”的葉子節(jié)點得到主鍵 id再回到聚簇索引中根據 id 找到完整行。如果查詢命中的數據量很大回表成本就很高。這也引出后面要講的覆蓋索引它是一種避免回表的優(yōu)化手段。2.3 為什么 Hash 索引沒有成為默認選擇有同學會問Hash 索引做等值查詢不是更快嗎確實對等值查詢來說Hash 索引的定位速度通常比 B樹更快。但 Hash 索引有兩個致命短板第一它不支持范圍查詢因為 Hash 值是無序的區(qū)間查找只能全表掃描第二Hash 沖突時需要逐個比較在極端情況下性能反而下降。MySQL 的 Memory 引擎和 InnoDB 的 adaptive hash index 都提供 Hash 索引能力但 InnoDB 默認還是使用 B樹核心原因正是為了同時兼顧等值查詢和范圍查詢。理解了這個對比你在面試里說“B樹適合磁盤存儲、支持范圍查詢、樹高穩(wěn)定”時就有了依據。3. 環(huán)境準備搭建一個可持續(xù)實驗的 MySQL 環(huán)境3.1 安裝與版本選擇網上關于 MySQL 安裝的教程非常多還有安裝配置教程、Windows 安裝教程、Docker 安裝 MySQL 教程方法五花八門。這里不重復鋪開只強調幾個關鍵選擇。學習索引和 SQL 優(yōu)化建議優(yōu)先使用 MySQL 8.0 及以上版本。8.0 的優(yōu)化器比 5.7 更強支持窗口函數、CTE對索引失效場景的判定也更精細。生產環(huán)境選版本要看具體業(yè)務和云廠商支持情況。如果公司還在用 5.7學習時至少要清楚 5.7 和 8.0 在索引行為上的差異。本地實驗建議用 Docker 快速啟動一個實例避免在系統(tǒng)里安裝一堆依賴也方便隨時重置。Docker 啟動 MySQL 的參考命令如下docker run -d \ --name mysql-study \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot \ mysql:8.0啟動后連接mysql -h127.0.0.1 -P3306 -uroot -proot3.2 準備一張有足夠數據量的測試表索引優(yōu)化實驗最怕數據量太少。幾千條數據時全表掃描和走索引的差別幾乎看不出來很容易得出錯誤結論。建議準備一張百萬級數據量的測試表。創(chuàng)建一個簡單的訂單表CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用存儲過程灌入測試數據。這里用遞歸 CTE 或存儲過程都行但存儲過程的寫法更容易控制循環(huán)次數DELIMITER $$ CREATE PROCEDURE init_order_data(IN total INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i total DO INSERT INTO t_order(order_no, user_id, amount, status, create_time) VALUES ( CONCAT(NO_, LPAD(i, 8, 0)), FLOOR(RAND() * 100000), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); SET i i 1; END WHILE; END$$ DELIMITER ;然后執(zhí)行CALL init_order_data(1000000);數據量到 100 萬條后后續(xù) explain 的結果會更有參考價值。這步做完了才算有一個能支撐實驗的環(huán)境。3.3 開啟慢查詢日志慢查詢日志是定位線上 SQL 問題的第一工具。在 MySQL 8.0 中可以直接用 SET GLOBAL 命令動態(tài)開啟避免改配置文件后重啟SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output TABLE;log_output 設為 TABLE慢查詢記錄會寫入 mysql.slow_log 表查詢起來非常方便SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;如果要查看當前慢查詢日志相關的所有配置SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;4. explain 執(zhí)行計劃判斷 SQL 是否走索引的標尺4.1 explain 的核心字段給任意一條查詢前面加上 explainMySQL 就會告訴你這條 SQL 的執(zhí)行計劃。重點看四個字段type、key、rows、Extra。type 表示訪問類型性能從好到差大致是type含義常見場景system系統(tǒng)表通常不會出現系統(tǒng)表const主鍵或唯一索引等值查詢WHERE id 1eq_ref聯(lián)表查詢時被驅動表用主鍵或唯一索引JOIN 查詢ref非唯一索引等值查詢WHERE user_id 100range索引范圍掃描WHERE id 100index遍歷索引樹覆蓋索引掃描ALL全表掃描無索引或索引失效key 表示實際使用的索引名如果為 NULL說明沒走索引。rows 表示預估掃描行數行數越大通常性能越差。Extra 是優(yōu)化器給出的補充信息常見的有 Using index、Using where、Using filesort、Using temporary這幾項在后面優(yōu)化時都會遇到。4.2 用 explain 驗證一個基本查詢回到剛才的 t_order 表先看一個簡單查詢EXPLAIN SELECT * FROM t_order WHERE user_id 12345;預期結果中type 應該是 refkey 應該是 idx_user_idExtra 一般會顯示空或 Using index condition。這說明這條 SQL 使用了二級索引查找。再執(zhí)行EXPLAIN SELECT * FROM t_order WHERE amount 500;amount 列沒有索引結果大概率是 type 為 ALLkey 為 NULL。這就是一個典型的全表掃描。這里要養(yǎng)成的習慣是任何一條你擔心性能的 SQL先 explain 再下結論。不要只看 SQL 表面因為 MySQL 優(yōu)化器有自己的規(guī)則是否走索引并不是由 SQL 寫法單方面決定的。5. 聯(lián)合索引設計、最左前綴與索引下推5.1 聯(lián)合索引的物理結構聯(lián)合索引是指由多個字段組成的索引。很多人以為聯(lián)合索引是給多個字段分別建了索引這是錯誤的。聯(lián)合索引本質上是一棵單獨的 B樹它先按第一個字段排序第一個字段相同時再按第二個字段排序依次類推。這也是最左前綴原則的由來。因為索引樹的排序是從左到右依賴的如果你沒有使用聯(lián)合索引的最左邊字段作為查詢條件優(yōu)化器就無法利用這個有序性。比如聯(lián)合索引 (a, b, c)查詢條件只有 b 和 c 時用不上這個索引的最左前綴很難有效利用。5.2 字段順序怎么選聯(lián)合索引的字段順序設計有兩個核心優(yōu)先級第一優(yōu)先級區(qū)分度高的字段放前面。區(qū)分度越高索引樹在第一個層級就能篩掉越多的數據。比如性別字段區(qū)分度很低只有兩三個值放在聯(lián)合索引最前面通常不劃算。第二優(yōu)先級考慮查詢頻率和排序需求。如果有 order by 字段盡量讓排序字段包含在聯(lián)合索引中這樣可以利用索引有序性避免 filesort。舉個例子訂單表常見查詢是SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;比較合理的聯(lián)合索引是 (user_id, status, create_time)。user_id 區(qū)分度高放最前面status 用來進一步過濾create_time 用來排序。這樣查詢既能利用索引過濾又能避免額外的排序操作。5.3 索引下推是什么索引下推是 MySQL 5.6 引入的優(yōu)化英文叫 Index Condition Pushdown簡稱 ICP。它的作用是減少回表次數。在沒有索引下推之前二級索引查到數據后要先回表再把整行數據交給 Server 層去判斷其他條件。有了索引下推后部分條件可以在存儲引擎層直接過濾掉只有滿足條件的記錄才回表。還是用 (user_id, status, create_time) 這個聯(lián)合索引舉例。執(zhí)行SELECT * FROM t_order WHERE user_id BETWEEN 100 AND 200 AND status 1;MySQL 可以在索引樹的掃描過程中直接用 status 1 過濾掉不符合條件的記錄減少回表數量。理解了這一點你就能明白為什么說聯(lián)合索引設計得好不只是“能走索引”的問題還能降低回表壓力。5.4 一個典型的聯(lián)合索引示例建立聯(lián)合索引ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);然后執(zhí)行EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;預期的結果是type 為 refkey 為 idx_user_status_timeExtra 里不會出現 Using filesort。這說明索引設計覆蓋了查詢的過濾和排序需求。如果查詢條件少了 user_idEXPLAIN SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC;這時候聯(lián)合索引大概率無法被有效利用因為最左前綴缺失。這就是最左前綴原則的實際表現。6. 索引失效的常見場景與規(guī)避方法6.1 隱式類型轉換導致索引失效這是最容易被忽視的場景。比如 order_no 是 varchar 類型但查詢時用數字匹配SELECT * FROM t_order WHERE order_no 12345678;MySQL 會把 varchar 列隱式轉換為數字再比較導致索引失效走全表掃描。正確寫法是使用字符串SELECT * FROM t_order WHERE order_no 12345678;判斷一個查詢是否發(fā)生隱式類型轉換可以用 explain 看 type 是不是從 ref 變成了 ALL。這類的坑非常隱蔽尤其是長字符串類型的編號字段。6.2 在索引列上做計算或函數操作如果在索引列上應用函數或表達式優(yōu)化器通常無法使用索引。常見寫法包括SELECT * FROM t_order WHERE DATE(create_time) 2026-01-01;原因是 create_time 這個列的值被 DATE() 函數包裝后索引的有序性被打亂了。更推薦改成范圍查詢SELECT * FROM t_order WHERE create_time 2026-01-01 AND create_time 2026-01-02;這里要說明一點MySQL 8.0 對部分函數有優(yōu)化但作為通用規(guī)則仍然建議不要在索引列上做函數操作。6.3 前導模糊查詢以通配符開頭的 LIKE 查詢無法有效利用索引SELECT * FROM t_order WHERE order_no LIKE %abc%;因為索引樹是按前綴排序的%abc%無法從有序樹中定位起點。如果業(yè)務確實需要這種模糊匹配可以考慮全文索引或搜索引擎。僅以固定前綴查詢時LIKE abc%是可以走索引的。6.4 OR 條件與 IN 的注意事項OR 條件中只要有一個字段沒有索引整個查詢就可能走全表掃描。比如SELECT * FROM t_order WHERE user_id 100 OR status 1;如果 status 上沒有索引這條 SQL 很可能退化為全表掃描。更穩(wěn)妥的做法是拆分為兩個查詢或者用 UNION ALL 合并結果。關于 INMySQL 優(yōu)化器對 IN 列表的處理相對智能在索引列上使用 IN 通?？梢宰?range 訪問。但 IN 列表如果過長優(yōu)化器會重新評估成本甚至選擇全表掃描。這里不能一概而論應該以 explain 結果為準。6.5 對索引字段做 IS NULL 判斷在 MySQL 中IS NULL 不一定會讓索引失效。實際上如果索引列允許 NULLIS NULL 走索引的情況是存在的。但有一個容易忽略的問題不要在可空列上建立過多索引因為 NULL 值會占用額外空間也可能影響查詢計劃的穩(wěn)定性。設計表時能用 NOT NULL 就用 NOT NULL并為字段設置 DEFAULT 值。7. SQL 優(yōu)化實戰(zhàn)從慢 SQL 到高效 SQL7.1 找到慢 SQL前面已經配置了慢查詢日志。線上排查時第一步先看慢日志把耗時超過閾值的 SQL 撈出來。MySQL 8.0 里還可以查 performance_schema 的 events_statements_summary_by_digest 表按累計耗時排序快速找到 Top N 慢 SQLSELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms, MAX_TIMER_WAIT/1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;7.2 經典分頁深翻頁優(yōu)化LIMIT 深翻頁是很多業(yè)務都會遇到的問題。比如SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;這條 SQL 會掃描前 1000020 行再丟棄前 1000000 行成本非常高。優(yōu)化思路是先用子查詢拿到目標主鍵范圍再回表查完整數據SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 ) tmp ON t.id tmp.id;子查詢里只掃描主鍵速度會明顯更快。如果有序字段不是主鍵也可以用覆蓋索引先定位主鍵來做。7.3 order by 排序優(yōu)化出現 Using filesort 時通常說明排序字段沒有利用索引有序性。優(yōu)化方式有兩個層面。第一如果排序字段和過濾條件能組成聯(lián)合索引建立合適的索引消除 filesort。比如前面提到的 (user_id, status, create_time) 聯(lián)合索引。第二如果數據量確實很大讓排序在內存中完成?？梢酝ㄟ^調整 sort_buffer_size 來緩解但這是治標不治本。真正的思路是讓排序走索引而不是依賴緩沖區(qū)。7.4 group by 優(yōu)化group by 的優(yōu)化核心是消滅臨時表。常見思路是盡量讓分組字段走索引減少 MySQL 創(chuàng)建內部臨時表的概率。如果無法避免可以通過 SQL 改寫減少分組行數比如先縮小 WHERE 范圍再分組統(tǒng)計。7.5 避免 select * 的深層原因很多教程都會說“不要 select *”但沒說清楚原因。從索引優(yōu)化角度看select * 會強制回表讀取所有字段導致覆蓋索引失效。如果你只需要 user_id 和 status而索引 (user_id, status) 已經覆蓋了這兩個字段查詢就變成覆蓋索引查詢Extra 會顯示 Using index完全不需要回表。SELECT user_id, status FROM t_order WHERE user_id 100;只要這條查詢只查索引列它就在索引樹上完成不回表。這是覆蓋索引的價值也是 select * 最直接的性能損失點。7.6 用一條實際慢查詢走完排查流程假設有一條線上慢 SQLSELECT * FROM t_order WHERE user_id 100 AND DATE(create_time) 2026-01-01 ORDER BY amount DESC;排查步驟第一步explain 看執(zhí)行計劃大概率會發(fā)現 type 不是理想狀態(tài)或者 Extra 里有 Using filesort。第二步檢查 WHERE 條件。DATE(create_time) 在索引列上用了函數直接導致該條件無法使用索引。改成 create_time 2026-01-01 AND create_time 2026-01-02。第三步檢查排序字段。amount 不在聯(lián)合索引中導致 filesort。如果查詢頻率很高可以考慮調整索引設計讓它覆蓋排序需求。經過改寫后的 SQLSELECT * FROM t_order WHERE user_id 100 AND create_time 2026-01-01 AND create_time 2026-01-02 ORDER BY amount DESC;再 explain 一次對比 type、key、rows、Extra 的變化。這個流程不依賴任何經驗玄學完全靠執(zhí)行計劃驅動。8. 高頻面試題與底層原理串講8.1 為什么 InnoDB 表必須有主鍵InnoDB 的數據存儲在聚簇索引中聚簇索引的葉子節(jié)點就是整行數據。沒有主鍵InnoDB 就要找唯一鍵找不到就生成隱藏 ROWID。這個隱藏 ROWID 沒有業(yè)務意義且會導致數據存儲位置不受控。更重要的是二級索引葉子節(jié)點存的是主鍵值如果沒有主鍵二級索引就無法高效回表。所以從設計和原理兩個層面都建議為每張 InnoDB 表顯式設計主鍵。8.2 主鍵為什么推薦自增整型自增整型主鍵有兩個優(yōu)點一是整型占用空間小二級索引葉子節(jié)點存的都是主鍵值主鍵小索引自然小二是自增順序插入數據大概率追加到 B樹末尾減少頁分裂。換成 UUID 作為主鍵隨機字符串不僅占用空間大插入時還會導致頁分裂和索引碎片化。8.3 什么情況下適合用前綴索引對于 text 或超長 varchar 列為了減小索引空間可以只對字段前 N 個字符建索引。比如用戶手機號或郵箱前綴區(qū)分度足夠高時用前綴索引是合理的。但要注意前綴索引無法用覆蓋索引優(yōu)化也不能用于 order by 和 group by。實際項目中可以用如下 SQL 驗證不同前綴長度的區(qū)分度SELECT COUNT(DISTINCT LEFT(order_no, 6)) AS prefix6, COUNT(DISTINCT LEFT(order_no, 8)) AS prefix8, COUNT(DISTINCT LEFT(order_no, 10)) AS prefix10, COUNT(DISTINCT order_no) AS full_col FROM t_order;然后選擇一個與全列區(qū)分度接近的前綴長度。8.4 聯(lián)合索引為什么要考慮區(qū)分度區(qū)分度等于某個字段不同值的數量除以總行數。區(qū)分度越高該字段在索引樹中的過濾效果越強。比如共同構造聯(lián)合索引 (gender, user_id)gender 只有 0 和 1區(qū)分度極低幾乎不會縮小數據范圍。把區(qū)分度高的 user_id 放前面過濾效果會更好。8.5 什么是回表覆蓋索引怎么避免回表二級索引葉子節(jié)點只存主鍵值和索引列值查詢完整行時必須回聚簇索引這個過程叫回表。如果查詢需要的所有字段都在二級索引中那就不必回表直接遍歷索引樹即可這就是覆蓋索引。覆蓋索引是優(yōu)化高頻查詢最有效的手段之一。8.6 為什么 explain 里 rows 不準explain 的 rows 是優(yōu)化器基于統(tǒng)計信息和索引基數估算出來的不是精確值。它用于判斷執(zhí)行計劃的相對好壞但不要迷信這個數字。如果統(tǒng)計信息過期可以用 ANALYZE TABLE 更新ANALYZE TABLE t_order;9. 常見問題與排查方法問題現象可能原因排查方式解決方案查詢變慢explain 顯示全表掃描索引列上使用函數或隱式轉換檢查 WHERE 條件是否對索引列做計算改寫 SQL避免函數操作聯(lián)合索引沒有生效查詢條件不滿足最左前綴explain 查看 key 字段調整查詢條件順序或重新設計聯(lián)合索引排序字段導致 Using filesort排序字段不在索引中查看 Extra 字段優(yōu)化聯(lián)合索引讓排序字段入索引select * 導致回表過多查詢字段超出索引覆蓋范圍查看 Extra 是否顯示 Using index改成只查詢必要的字段分頁越深越慢LIMIT 深偏移掃描大量行觀察耗時隨頁數變化用覆蓋索引先取主鍵再回表慢查詢日志沒有記錄long_query_time 設置過大或日志未開啟查詢慢日志相關變量調整 long_query_time打開日志數據量小但查詢不穩(wěn)定統(tǒng)計信息過期對比多次執(zhí)行計劃執(zhí)行 ANALYZE TABLE 更新統(tǒng)計信息唯一鍵沖突導致性能抖動不合理的唯一索引設計查看錯誤日志重新評估業(yè)務約束字段10. 最佳實踐與工程建議10.1 索引設計的分階段策略新系統(tǒng)上線時建議不要一次性加滿所有索引。先建主鍵索引和明顯高頻查詢需要的聯(lián)合索引其他索引等壓測或上線后根據慢 SQL 和應用日志逐步補充。每個索引都會帶來寫入開銷索引數量越多insert、update、delete 的代價就越大。10.2 不要盲目刪除重復或冗余索引線上經常出現 idx_user_id 和 idx_user_status 同時存在的情況而 idx_user_status 其實可以覆蓋 user_id 的查詢場景。刪除索引前先用 sys.schema_unused_indexes 查看未使用索引SELECT * FROM sys.schema_unused_indexes;但要注意這張視圖只能反映統(tǒng)計信息收集期內的使用情況生產變更前一定要備份和驗證。10.3 SQL 審查應該進入 Code Review 流程很多團隊只 review Java 代碼SQL 寫完直接上線。實際上SQL 質量問題最好在開發(fā)階段就通過 explain 發(fā)現。團隊可以在 MR 模板里加入「本次涉及的 SQL 是否附上了 explain 結果」這一項從流程上倒逼執(zhí)行計劃檢查。10.4 用索引監(jiān)控視圖幫助決策MySQL 8.0 的 sys 庫提供了不少索引使用統(tǒng)計視圖。除了 schema_unused_indexes還可以查 schema_index_statistics查看索引的掃描次數、使用次數等信息。這是做一個索引增減決策的重要依據。10.5 運維變更安全提醒任何線上索引變更都要在低峰期進行。MySQL 8.0 支持在線 DDL但大表加索引依然會產生額外負載需要監(jiān)控主從延遲和磁盤 IO。執(zhí)行前確認備份執(zhí)行后立即用 explain 驗證查詢計劃。11. 總結與后續(xù)學習方向這篇文章把 MySQL 索引相關的核心知識做了一個體系化梳理從 B樹的結構原理到聚簇索引、二級索引、回表、覆蓋索引這些基本概念再到聯(lián)合索引設計、最左前綴、索引下推、索引失效場景最后落到慢查詢定位和 SQL 改寫實戰(zhàn)。比較重要的是這些知識點不是孤立的。遇到任何線上慢 SQL都應該走同一條排查路徑先撈慢日志再 explain 看執(zhí)行計劃然后根據 type、key、rows、Extra 判斷問題層面最后通過改寫 SQL 或調整索引解決。如果你正在準備面試建議不要停留在背誦概念?？梢哉乙粋€自己項目里的表把聯(lián)合索引設計、最左前綴失效、覆蓋索引優(yōu)化各寫一個 explain 示例親眼看一遍 type 和 Extra 的變化。這個過程比背十套面試題都有效。后續(xù)值得繼續(xù)深入的方向還有MySQL 優(yōu)化器成本模型、事務隔離級別對索引讀取的影響、redo log 與 undo log 的底層機制、主從復制延遲排查。這些內容都建立在索引和存儲結構的基礎上把今天這篇吃透再往下學就不會覺得吃力了。 MySQL索引與SQL優(yōu)化：從B+樹到慢查詢排查實戰(zhàn)
很多同學學 MySQL 都是靠視頻入門索引、B樹、SQL優(yōu)化這些詞聽起來都很熟但一到線上問題就露餡明明建了索引查詢還是慢明明 SQL 看著沒問題explain 一執(zhí)行卻走了全表掃描面試背了一堆“最左前綴”“索引失效”被追問底層原因時直接卡住。這套知識之所以難不是因為它有多高深而是因為大多數教程把“原理”和“實踐”拆開了。講 B樹的時候不講 SQL 怎么寫才走索引講 SQL 優(yōu)化的時候又不解釋為什么 MySQL 要這樣執(zhí)行。結果是背了一堆結論遇到新問題完全不會遷移。這篇文章不打算復述某個視頻的目錄而是把索引、B樹、聯(lián)合索引、SQL 優(yōu)化、慢查詢定位、高頻面試題串成一條完整的知識鏈路。讀完后你至少能做到三件事第一給一張表設計合理的索引不再“憑感覺加索引”第二遇到慢 SQL 能按固定思路排查知道先看哪里、再看哪里第三面試被問到底層原理時能用手畫 B樹的方式把過程講清楚。1. 這篇文章真正要解決的問題先說說我觀察到的普遍現象。很多開發(fā)者的 MySQL 知識是碎片化的知道索引能加速查詢但不知道索引為什么能加速知道聯(lián)合索引有最左前綴原則但不知道這個原則是怎么推導出來的知道 explain 能看執(zhí)行計劃但看到 type 為 ref 還是 range 時判斷不了要不要優(yōu)化。這些碎片化知識最直接的后果是線上 SQL 變慢時只能靠猜。網上說“不要在索引列上做函數操作”你就把所有函數調用都去掉網上說“select 不要用 *”你就把所有 select * 改成顯式字段。這些建議本身沒大問題但沒有底層邏輯支撐你永遠不知道邊界在哪里。這篇文章要幫你解決的核心問題是建立一條“數據結構 → 索引設計 → SQL 寫法 → 執(zhí)行計劃驗證 → 慢查詢治理 → 面試表達”的完整鏈路。你會先理解 B樹為什么是 MySQL 索引的默認數據結構再理解聚簇索引和二級索引的區(qū)別然后掌握聯(lián)合索引的字段順序設計方法最后能熟練用 explain 和慢查詢日志定位線上問題。一句話總結這不是一篇“記住 20 條優(yōu)化技巧”的文章而是一篇幫你把 MySQL 索引和 SQL 優(yōu)化體系化梳理的文章。適合正在準備 MySQL 面試的開發(fā)者也適合被線上慢查詢折磨得焦頭爛額的日常開發(fā)。2. 核心概念B樹、索引與聚簇索引2.1 B樹為什么是 MySQL 索引的默認選擇要理解索引先理解存儲。MySQL 的數據最終是存在磁盤上的而磁盤隨機讀寫的速度比內存慢好幾個數量級。所以存儲引擎設計索引的第一目標是盡量減少磁盤隨機 IO 的次數。B樹就是一種專門為磁盤存儲設計的多路平衡查找樹。它有兩個典型特征第一所有數據都存放在葉子節(jié)點非葉子節(jié)點只存放索引鍵值。這意味著每次查找都必須走到葉子節(jié)點才能拿到數據所以任何一個查詢的 IO 次數是相對穩(wěn)定的基本等于樹的層數。第二葉子節(jié)點之間用指針串聯(lián)成了有序鏈表。這個設計讓范圍查詢變得非常高效。比如查 id 大于 100 且小于 200 的數據只要找到 100然后順著鏈表往后讀就行了不需要回根節(jié)點重新遍歷。從數據量角度算一筆賬。假設一個非葉子節(jié)點能存 1000 個鍵值三層 B樹可以存儲大約 10 億條記錄。也就是說從 10 億條數據里查一條記錄大概只需要 3 到 4 次磁盤 IO。這正是 B樹能支撐海量數據在線查詢的根本原因。2.2 聚簇索引、非聚簇索引與回表InnoDB 存儲引擎的索引結構需要區(qū)分兩種聚簇索引和二級索引。聚簇索引的特點是索引鍵值決定數據的物理存儲順序。InnoDB 的聚簇索引就是主鍵索引。當你建表時沒有顯式指定主鍵InnoDB 會選擇一個沒有 NULL 的唯一鍵作為主鍵如果也沒有它會生成一個隱藏的 ROWID 作為聚簇索引。所以從實踐角度看每張 InnoDB 表都應該有主鍵主鍵最好是自增整型這樣新數據的物理插入位置永遠在末尾不會頻繁觸發(fā)頁分裂。二級索引也叫非聚簇索引它的葉子節(jié)點存放的是索引鍵值和主鍵值。這里有一個非常關鍵的設計通過二級索引查數據時先用索引找到主鍵再用主鍵回聚簇索引查完整行。這個過程叫回表。用一個例子說明。表 user 有主鍵 id還有二級索引 idx_name(name)。執(zhí)行 SQLSELECT * FROM user WHERE name 張三;MySQL 會先走 idx_name 索引找到 name 為“張三”的葉子節(jié)點得到主鍵 id再回到聚簇索引中根據 id 找到完整行。如果查詢命中的數據量很大回表成本就很高。這也引出后面要講的覆蓋索引它是一種避免回表的優(yōu)化手段。2.3 為什么 Hash 索引沒有成為默認選擇有同學會問Hash 索引做等值查詢不是更快嗎確實對等值查詢來說Hash 索引的定位速度通常比 B樹更快。但 Hash 索引有兩個致命短板第一它不支持范圍查詢因為 Hash 值是無序的區(qū)間查找只能全表掃描第二Hash 沖突時需要逐個比較在極端情況下性能反而下降。MySQL 的 Memory 引擎和 InnoDB 的 adaptive hash index 都提供 Hash 索引能力但 InnoDB 默認還是使用 B樹核心原因正是為了同時兼顧等值查詢和范圍查詢。理解了這個對比你在面試里說“B樹適合磁盤存儲、支持范圍查詢、樹高穩(wěn)定”時就有了依據。3. 環(huán)境準備搭建一個可持續(xù)實驗的 MySQL 環(huán)境3.1 安裝與版本選擇網上關于 MySQL 安裝的教程非常多還有安裝配置教程、Windows 安裝教程、Docker 安裝 MySQL 教程方法五花八門。這里不重復鋪開只強調幾個關鍵選擇。學習索引和 SQL 優(yōu)化建議優(yōu)先使用 MySQL 8.0 及以上版本。8.0 的優(yōu)化器比 5.7 更強支持窗口函數、CTE對索引失效場景的判定也更精細。生產環(huán)境選版本要看具體業(yè)務和云廠商支持情況。如果公司還在用 5.7學習時至少要清楚 5.7 和 8.0 在索引行為上的差異。本地實驗建議用 Docker 快速啟動一個實例避免在系統(tǒng)里安裝一堆依賴也方便隨時重置。Docker 啟動 MySQL 的參考命令如下docker run -d \ --name mysql-study \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot \ mysql:8.0啟動后連接mysql -h127.0.0.1 -P3306 -uroot -proot3.2 準備一張有足夠數據量的測試表索引優(yōu)化實驗最怕數據量太少。幾千條數據時全表掃描和走索引的差別幾乎看不出來很容易得出錯誤結論。建議準備一張百萬級數據量的測試表。創(chuàng)建一個簡單的訂單表CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;用存儲過程灌入測試數據。這里用遞歸 CTE 或存儲過程都行但存儲過程的寫法更容易控制循環(huán)次數DELIMITER $$ CREATE PROCEDURE init_order_data(IN total INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i total DO INSERT INTO t_order(order_no, user_id, amount, status, create_time) VALUES ( CONCAT(NO_, LPAD(i, 8, 0)), FLOOR(RAND() * 100000), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), NOW() - INTERVAL FLOOR(RAND() * 365) DAY ); SET i i 1; END WHILE; END$$ DELIMITER ;然后執(zhí)行CALL init_order_data(1000000);數據量到 100 萬條后后續(xù) explain 的結果會更有參考價值。這步做完了才算有一個能支撐實驗的環(huán)境。3.3 開啟慢查詢日志慢查詢日志是定位線上 SQL 問題的第一工具。在 MySQL 8.0 中可以直接用 SET GLOBAL 命令動態(tài)開啟避免改配置文件后重啟SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output TABLE;log_output 設為 TABLE慢查詢記錄會寫入 mysql.slow_log 表查詢起來非常方便SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;如果要查看當前慢查詢日志相關的所有配置SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;4. explain 執(zhí)行計劃判斷 SQL 是否走索引的標尺4.1 explain 的核心字段給任意一條查詢前面加上 explainMySQL 就會告訴你這條 SQL 的執(zhí)行計劃。重點看四個字段type、key、rows、Extra。type 表示訪問類型性能從好到差大致是type含義常見場景system系統(tǒng)表通常不會出現系統(tǒng)表const主鍵或唯一索引等值查詢WHERE id 1eq_ref聯(lián)表查詢時被驅動表用主鍵或唯一索引JOIN 查詢ref非唯一索引等值查詢WHERE user_id 100range索引范圍掃描WHERE id 100index遍歷索引樹覆蓋索引掃描ALL全表掃描無索引或索引失效key 表示實際使用的索引名如果為 NULL說明沒走索引。rows 表示預估掃描行數行數越大通常性能越差。Extra 是優(yōu)化器給出的補充信息常見的有 Using index、Using where、Using filesort、Using temporary這幾項在后面優(yōu)化時都會遇到。4.2 用 explain 驗證一個基本查詢回到剛才的 t_order 表先看一個簡單查詢EXPLAIN SELECT * FROM t_order WHERE user_id 12345;預期結果中type 應該是 refkey 應該是 idx_user_idExtra 一般會顯示空或 Using index condition。這說明這條 SQL 使用了二級索引查找。再執(zhí)行EXPLAIN SELECT * FROM t_order WHERE amount 500;amount 列沒有索引結果大概率是 type 為 ALLkey 為 NULL。這就是一個典型的全表掃描。這里要養(yǎng)成的習慣是任何一條你擔心性能的 SQL先 explain 再下結論。不要只看 SQL 表面因為 MySQL 優(yōu)化器有自己的規(guī)則是否走索引并不是由 SQL 寫法單方面決定的。5. 聯(lián)合索引設計、最左前綴與索引下推5.1 聯(lián)合索引的物理結構聯(lián)合索引是指由多個字段組成的索引。很多人以為聯(lián)合索引是給多個字段分別建了索引這是錯誤的。聯(lián)合索引本質上是一棵單獨的 B樹它先按第一個字段排序第一個字段相同時再按第二個字段排序依次類推。這也是最左前綴原則的由來。因為索引樹的排序是從左到右依賴的如果你沒有使用聯(lián)合索引的最左邊字段作為查詢條件優(yōu)化器就無法利用這個有序性。比如聯(lián)合索引 (a, b, c)查詢條件只有 b 和 c 時用不上這個索引的最左前綴很難有效利用。5.2 字段順序怎么選聯(lián)合索引的字段順序設計有兩個核心優(yōu)先級第一優(yōu)先級區(qū)分度高的字段放前面。區(qū)分度越高索引樹在第一個層級就能篩掉越多的數據。比如性別字段區(qū)分度很低只有兩三個值放在聯(lián)合索引最前面通常不劃算。第二優(yōu)先級考慮查詢頻率和排序需求。如果有 order by 字段盡量讓排序字段包含在聯(lián)合索引中這樣可以利用索引有序性避免 filesort。舉個例子訂單表常見查詢是SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;比較合理的聯(lián)合索引是 (user_id, status, create_time)。user_id 區(qū)分度高放最前面status 用來進一步過濾create_time 用來排序。這樣查詢既能利用索引過濾又能避免額外的排序操作。5.3 索引下推是什么索引下推是 MySQL 5.6 引入的優(yōu)化英文叫 Index Condition Pushdown簡稱 ICP。它的作用是減少回表次數。在沒有索引下推之前二級索引查到數據后要先回表再把整行數據交給 Server 層去判斷其他條件。有了索引下推后部分條件可以在存儲引擎層直接過濾掉只有滿足條件的記錄才回表。還是用 (user_id, status, create_time) 這個聯(lián)合索引舉例。執(zhí)行SELECT * FROM t_order WHERE user_id BETWEEN 100 AND 200 AND status 1;MySQL 可以在索引樹的掃描過程中直接用 status 1 過濾掉不符合條件的記錄減少回表數量。理解了這一點你就能明白為什么說聯(lián)合索引設計得好不只是“能走索引”的問題還能降低回表壓力。5.4 一個典型的聯(lián)合索引示例建立聯(lián)合索引ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);然后執(zhí)行EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1 ORDER BY create_time DESC;預期的結果是type 為 refkey 為 idx_user_status_timeExtra 里不會出現 Using filesort。這說明索引設計覆蓋了查詢的過濾和排序需求。如果查詢條件少了 user_idEXPLAIN SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC;這時候聯(lián)合索引大概率無法被有效利用因為最左前綴缺失。這就是最左前綴原則的實際表現。6. 索引失效的常見場景與規(guī)避方法6.1 隱式類型轉換導致索引失效這是最容易被忽視的場景。比如 order_no 是 varchar 類型但查詢時用數字匹配SELECT * FROM t_order WHERE order_no 12345678;MySQL 會把 varchar 列隱式轉換為數字再比較導致索引失效走全表掃描。正確寫法是使用字符串SELECT * FROM t_order WHERE order_no 12345678;判斷一個查詢是否發(fā)生隱式類型轉換可以用 explain 看 type 是不是從 ref 變成了 ALL。這類的坑非常隱蔽尤其是長字符串類型的編號字段。6.2 在索引列上做計算或函數操作如果在索引列上應用函數或表達式優(yōu)化器通常無法使用索引。常見寫法包括SELECT * FROM t_order WHERE DATE(create_time) 2026-01-01;原因是 create_time 這個列的值被 DATE() 函數包裝后索引的有序性被打亂了。更推薦改成范圍查詢SELECT * FROM t_order WHERE create_time 2026-01-01 AND create_time 2026-01-02;這里要說明一點MySQL 8.0 對部分函數有優(yōu)化但作為通用規(guī)則仍然建議不要在索引列上做函數操作。6.3 前導模糊查詢以通配符開頭的 LIKE 查詢無法有效利用索引SELECT * FROM t_order WHERE order_no LIKE %abc%;因為索引樹是按前綴排序的%abc%無法從有序樹中定位起點。如果業(yè)務確實需要這種模糊匹配可以考慮全文索引或搜索引擎。僅以固定前綴查詢時LIKE abc%是可以走索引的。6.4 OR 條件與 IN 的注意事項OR 條件中只要有一個字段沒有索引整個查詢就可能走全表掃描。比如SELECT * FROM t_order WHERE user_id 100 OR status 1;如果 status 上沒有索引這條 SQL 很可能退化為全表掃描。更穩(wěn)妥的做法是拆分為兩個查詢或者用 UNION ALL 合并結果。關于 INMySQL 優(yōu)化器對 IN 列表的處理相對智能在索引列上使用 IN 通?？梢宰?range 訪問。但 IN 列表如果過長優(yōu)化器會重新評估成本甚至選擇全表掃描。這里不能一概而論應該以 explain 結果為準。6.5 對索引字段做 IS NULL 判斷在 MySQL 中IS NULL 不一定會讓索引失效。實際上如果索引列允許 NULLIS NULL 走索引的情況是存在的。但有一個容易忽略的問題不要在可空列上建立過多索引因為 NULL 值會占用額外空間也可能影響查詢計劃的穩(wěn)定性。設計表時能用 NOT NULL 就用 NOT NULL并為字段設置 DEFAULT 值。7. SQL 優(yōu)化實戰(zhàn)從慢 SQL 到高效 SQL7.1 找到慢 SQL前面已經配置了慢查詢日志。線上排查時第一步先看慢日志把耗時超過閾值的 SQL 撈出來。MySQL 8.0 里還可以查 performance_schema 的 events_statements_summary_by_digest 表按累計耗時排序快速找到 Top N 慢 SQLSELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms, MAX_TIMER_WAIT/1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;7.2 經典分頁深翻頁優(yōu)化LIMIT 深翻頁是很多業(yè)務都會遇到的問題。比如SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;這條 SQL 會掃描前 1000020 行再丟棄前 1000000 行成本非常高。優(yōu)化思路是先用子查詢拿到目標主鍵范圍再回表查完整數據SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 ) tmp ON t.id tmp.id;子查詢里只掃描主鍵速度會明顯更快。如果有序字段不是主鍵也可以用覆蓋索引先定位主鍵來做。7.3 order by 排序優(yōu)化出現 Using filesort 時通常說明排序字段沒有利用索引有序性。優(yōu)化方式有兩個層面。第一如果排序字段和過濾條件能組成聯(lián)合索引建立合適的索引消除 filesort。比如前面提到的 (user_id, status, create_time) 聯(lián)合索引。第二如果數據量確實很大讓排序在內存中完成?？梢酝ㄟ^調整 sort_buffer_size 來緩解但這是治標不治本。真正的思路是讓排序走索引而不是依賴緩沖區(qū)。7.4 group by 優(yōu)化group by 的優(yōu)化核心是消滅臨時表。常見思路是盡量讓分組字段走索引減少 MySQL 創(chuàng)建內部臨時表的概率。如果無法避免可以通過 SQL 改寫減少分組行數比如先縮小 WHERE 范圍再分組統(tǒng)計。7.5 避免 select * 的深層原因很多教程都會說“不要 select *”但沒說清楚原因。從索引優(yōu)化角度看select * 會強制回表讀取所有字段導致覆蓋索引失效。如果你只需要 user_id 和 status而索引 (user_id, status) 已經覆蓋了這兩個字段查詢就變成覆蓋索引查詢Extra 會顯示 Using index完全不需要回表。SELECT user_id, status FROM t_order WHERE user_id 100;只要這條查詢只查索引列它就在索引樹上完成不回表。這是覆蓋索引的價值也是 select * 最直接的性能損失點。7.6 用一條實際慢查詢走完排查流程假設有一條線上慢 SQLSELECT * FROM t_order WHERE user_id 100 AND DATE(create_time) 2026-01-01 ORDER BY amount DESC;排查步驟第一步explain 看執(zhí)行計劃大概率會發(fā)現 type 不是理想狀態(tài)或者 Extra 里有 Using filesort。第二步檢查 WHERE 條件。DATE(create_time) 在索引列上用了函數直接導致該條件無法使用索引。改成 create_time 2026-01-01 AND create_time 2026-01-02。第三步檢查排序字段。amount 不在聯(lián)合索引中導致 filesort。如果查詢頻率很高可以考慮調整索引設計讓它覆蓋排序需求。經過改寫后的 SQLSELECT * FROM t_order WHERE user_id 100 AND create_time 2026-01-01 AND create_time 2026-01-02 ORDER BY amount DESC;再 explain 一次對比 type、key、rows、Extra 的變化。這個流程不依賴任何經驗玄學完全靠執(zhí)行計劃驅動。8. 高頻面試題與底層原理串講8.1 為什么 InnoDB 表必須有主鍵InnoDB 的數據存儲在聚簇索引中聚簇索引的葉子節(jié)點就是整行數據。沒有主鍵InnoDB 就要找唯一鍵找不到就生成隱藏 ROWID。這個隱藏 ROWID 沒有業(yè)務意義且會導致數據存儲位置不受控。更重要的是二級索引葉子節(jié)點存的是主鍵值如果沒有主鍵二級索引就無法高效回表。所以從設計和原理兩個層面都建議為每張 InnoDB 表顯式設計主鍵。8.2 主鍵為什么推薦自增整型自增整型主鍵有兩個優(yōu)點一是整型占用空間小二級索引葉子節(jié)點存的都是主鍵值主鍵小索引自然小二是自增順序插入數據大概率追加到 B樹末尾減少頁分裂。換成 UUID 作為主鍵隨機字符串不僅占用空間大插入時還會導致頁分裂和索引碎片化。8.3 什么情況下適合用前綴索引對于 text 或超長 varchar 列為了減小索引空間可以只對字段前 N 個字符建索引。比如用戶手機號或郵箱前綴區(qū)分度足夠高時用前綴索引是合理的。但要注意前綴索引無法用覆蓋索引優(yōu)化也不能用于 order by 和 group by。實際項目中可以用如下 SQL 驗證不同前綴長度的區(qū)分度SELECT COUNT(DISTINCT LEFT(order_no, 6)) AS prefix6, COUNT(DISTINCT LEFT(order_no, 8)) AS prefix8, COUNT(DISTINCT LEFT(order_no, 10)) AS prefix10, COUNT(DISTINCT order_no) AS full_col FROM t_order;然后選擇一個與全列區(qū)分度接近的前綴長度。8.4 聯(lián)合索引為什么要考慮區(qū)分度區(qū)分度等于某個字段不同值的數量除以總行數。區(qū)分度越高該字段在索引樹中的過濾效果越強。比如共同構造聯(lián)合索引 (gender, user_id)gender 只有 0 和 1區(qū)分度極低幾乎不會縮小數據范圍。把區(qū)分度高的 user_id 放前面過濾效果會更好。8.5 什么是回表覆蓋索引怎么避免回表二級索引葉子節(jié)點只存主鍵值和索引列值查詢完整行時必須回聚簇索引這個過程叫回表。如果查詢需要的所有字段都在二級索引中那就不必回表直接遍歷索引樹即可這就是覆蓋索引。覆蓋索引是優(yōu)化高頻查詢最有效的手段之一。8.6 為什么 explain 里 rows 不準explain 的 rows 是優(yōu)化器基于統(tǒng)計信息和索引基數估算出來的不是精確值。它用于判斷執(zhí)行計劃的相對好壞但不要迷信這個數字。如果統(tǒng)計信息過期可以用 ANALYZE TABLE 更新ANALYZE TABLE t_order;9. 常見問題與排查方法問題現象可能原因排查方式解決方案查詢變慢explain 顯示全表掃描索引列上使用函數或隱式轉換檢查 WHERE 條件是否對索引列做計算改寫 SQL避免函數操作聯(lián)合索引沒有生效查詢條件不滿足最左前綴explain 查看 key 字段調整查詢條件順序或重新設計聯(lián)合索引排序字段導致 Using filesort排序字段不在索引中查看 Extra 字段優(yōu)化聯(lián)合索引讓排序字段入索引select * 導致回表過多查詢字段超出索引覆蓋范圍查看 Extra 是否顯示 Using index改成只查詢必要的字段分頁越深越慢LIMIT 深偏移掃描大量行觀察耗時隨頁數變化用覆蓋索引先取主鍵再回表慢查詢日志沒有記錄long_query_time 設置過大或日志未開啟查詢慢日志相關變量調整 long_query_time打開日志數據量小但查詢不穩(wěn)定統(tǒng)計信息過期對比多次執(zhí)行計劃執(zhí)行 ANALYZE TABLE 更新統(tǒng)計信息唯一鍵沖突導致性能抖動不合理的唯一索引設計查看錯誤日志重新評估業(yè)務約束字段10. 最佳實踐與工程建議10.1 索引設計的分階段策略新系統(tǒng)上線時建議不要一次性加滿所有索引。先建主鍵索引和明顯高頻查詢需要的聯(lián)合索引其他索引等壓測或上線后根據慢 SQL 和應用日志逐步補充。每個索引都會帶來寫入開銷索引數量越多insert、update、delete 的代價就越大。10.2 不要盲目刪除重復或冗余索引線上經常出現 idx_user_id 和 idx_user_status 同時存在的情況而 idx_user_status 其實可以覆蓋 user_id 的查詢場景。刪除索引前先用 sys.schema_unused_indexes 查看未使用索引SELECT * FROM sys.schema_unused_indexes;但要注意這張視圖只能反映統(tǒng)計信息收集期內的使用情況生產變更前一定要備份和驗證。10.3 SQL 審查應該進入 Code Review 流程很多團隊只 review Java 代碼SQL 寫完直接上線。實際上SQL 質量問題最好在開發(fā)階段就通過 explain 發(fā)現。團隊可以在 MR 模板里加入「本次涉及的 SQL 是否附上了 explain 結果」這一項從流程上倒逼執(zhí)行計劃檢查。10.4 用索引監(jiān)控視圖幫助決策MySQL 8.0 的 sys 庫提供了不少索引使用統(tǒng)計視圖。除了 schema_unused_indexes還可以查 schema_index_statistics查看索引的掃描次數、使用次數等信息。這是做一個索引增減決策的重要依據。10.5 運維變更安全提醒任何線上索引變更都要在低峰期進行。MySQL 8.0 支持在線 DDL但大表加索引依然會產生額外負載需要監(jiān)控主從延遲和磁盤 IO。執(zhí)行前確認備份執(zhí)行后立即用 explain 驗證查詢計劃。11. 總結與后續(xù)學習方向這篇文章把 MySQL 索引相關的核心知識做了一個體系化梳理從 B樹的結構原理到聚簇索引、二級索引、回表、覆蓋索引這些基本概念再到聯(lián)合索引設計、最左前綴、索引下推、索引失效場景最后落到慢查詢定位和 SQL 改寫實戰(zhàn)。比較重要的是這些知識點不是孤立的。遇到任何線上慢 SQL都應該走同一條排查路徑先撈慢日志再 explain 看執(zhí)行計劃然后根據 type、key、rows、Extra 判斷問題層面最后通過改寫 SQL 或調整索引解決。如果你正在準備面試建議不要停留在背誦概念?？梢哉乙粋€自己項目里的表把聯(lián)合索引設計、最左前綴失效、覆蓋索引優(yōu)化各寫一個 explain 示例親眼看一遍 type 和 Extra 的變化。這個過程比背十套面試題都有效。后續(xù)值得繼續(xù)深入的方向還有MySQL 優(yōu)化器成本模型、事務隔離級別對索引讀取的影響、redo log 與 undo log 的底層機制、主從復制延遲排查。這些內容都建立在索引和存儲結構的基礎上把今天這篇吃透再往下學就不會覺得吃力了。