各位準(zhǔn)備 Java 后端面試的朋友們今天咱們來啃下一塊硬骨頭MySQL 索引。我見過太多候選人在簡(jiǎn)歷上寫“熟悉 MySQL 索引優(yōu)化”結(jié)果一被追問“B樹和 B 樹的區(qū)別”“聯(lián)合索引最左前綴到底怎么走”“為什么明明建了索引卻還是慢查詢”就開始支支吾吾。這些問題不是背幾道八股文就能糊弄過去的面試官隨便改一個(gè)條件答案就變了。這篇文章會(huì)從索引的最底層數(shù)據(jù)結(jié)構(gòu)講起逐步延伸到 BufferPool、主鍵索引、二級(jí)索引、聯(lián)合索引、索引失效場(chǎng)景最后給出一套可以直接落地的索引優(yōu)化實(shí)戰(zhàn)方案。不管你是在準(zhǔn)備 2026 年的校招、社招還是在處理線上真實(shí)的慢 SQL這篇文章都值得你收藏起來反復(fù)看。文章內(nèi)容較長(zhǎng)建議先點(diǎn)贊收藏再慢慢閱讀。1. 索引到底是什么先解決“為什么慢”的問題在聊 B樹和 BufferPool 之前我們先回到一個(gè)最基礎(chǔ)的問題為什么數(shù)據(jù)庫需要索引1.1 沒有索引時(shí)MySQL 是怎么查數(shù)據(jù)的假設(shè)我們有一張用戶表user里面有 1000 萬條記錄現(xiàn)在要執(zhí)行這條 SQLSELECT * FROM user WHERE username zhangsan;如果沒有索引MySQL 只能從表的第一條記錄開始一條一條往下掃描直到找到所有滿足username zhangsan的記錄。這個(gè)過程的專業(yè)叫法是全表掃描Full Table Scan。全表掃描的時(shí)間復(fù)雜度是 O(N)也就是 1000 萬條記錄最壞情況下要把 1000 萬條記錄全部讀一遍才能拿到結(jié)果。就算每次 IO 只讀一頁數(shù)據(jù)MySQL 默認(rèn)頁大小是 16KB1000 萬條記錄也需要讀取大量數(shù)據(jù)頁磁盤 IO 的耗時(shí)是毫秒級(jí)甚至幾十毫秒級(jí)一次查詢幾十毫秒放在高并發(fā)的業(yè)務(wù)場(chǎng)景里數(shù)據(jù)庫很快就會(huì)被拖垮。這就像一本 1000 頁的書沒有目錄你想找某個(gè)關(guān)鍵詞只能從第 1 頁翻到第 1000 頁。1.2 索引的本質(zhì)用空間換時(shí)間索引的本質(zhì)就是額外維護(hù)一套查找結(jié)構(gòu)讓 MySQL 能夠用更少的比較次數(shù)、更少的磁盤 IO 定位到目標(biāo)數(shù)據(jù)。還是以書打比方索引就是書末尾的“索引表”它告訴你某個(gè)關(guān)鍵詞出現(xiàn)在哪一頁你直接翻到那一頁就行了。在 MySQL 的 InnoDB 存儲(chǔ)引擎中索引底層使用的是B樹一種專門為磁盤 IO 設(shè)計(jì)的多路平衡查找樹。1.3 面試官視角你至少要能說清楚這幾點(diǎn)索引是存儲(chǔ)引擎層面的概念不同的存儲(chǔ)引擎索引實(shí)現(xiàn)不同MyISAM 和 InnoDB 就有明顯區(qū)別。InnoDB 的索引是聚簇索引結(jié)構(gòu)數(shù)據(jù)和索引存儲(chǔ)在一起。索引不是越多越好每次寫操作都需要維護(hù)索引索引過多會(huì)拖慢寫入速度。這部分內(nèi)容是地基地基不牢后面講 B樹、BufferPool 你都會(huì)覺得在聽天書。2. 為什么偏偏是 B 樹從二叉樹到 B 樹的演化邏輯這一節(jié)是對(duì)標(biāo)面試高頻題“為什么 MySQL 的索引結(jié)構(gòu)要選 B樹”的完整回答思路。2.1 二叉搜索樹的缺陷很多人第一反應(yīng)是查找最快的數(shù)據(jù)結(jié)構(gòu)不是二叉樹嗎二分查找那么快為什么 MySQL 不用二叉樹做索引我們來看一個(gè)極端的例子。如果把索引列的值按遞增順序插入一棵普通二叉搜索樹它會(huì)退化成一條鏈表1 \ 2 \ 3 \ 4 \ 5這時(shí)候查找5需要比較 5 次時(shí)間復(fù)雜度從 O(logN) 退化為 O(N)。普通二叉樹在數(shù)據(jù)分布不均勻時(shí)樹的高度不可控。2.2 為什么不是 AVL 樹 / 紅黑樹AVL 樹和紅黑樹通過旋轉(zhuǎn)操作解決了二叉樹退化成鏈表的問題它們能保證樹的高度在 O(logN) 級(jí)別。那為什么 MySQL 不用它們關(guān)鍵問題在磁盤 IO。我們算一筆賬假設(shè)一張表有 1000 萬條記錄使用紅黑樹存儲(chǔ)索引樹的高度大概在 20 左右。查找一次數(shù)據(jù)最壞情況下需要訪問從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)路徑上的 20 個(gè)節(jié)點(diǎn)也就是最多觸發(fā) 20 次磁盤 IO。磁盤隨機(jī)讀一次 IO 的耗時(shí)大約 10ms20 次就是 200ms。一次查詢 200ms這個(gè)性能是無法接受的。問題的核心是二叉樹每個(gè)節(jié)點(diǎn)只能存儲(chǔ)一個(gè)鍵值導(dǎo)致樹太高訪問路徑太長(zhǎng)。2.3 B 樹和 B 樹的區(qū)別B 樹Balance Tree是多路平衡查找樹一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)鍵值每個(gè)節(jié)點(diǎn)也存儲(chǔ)數(shù)據(jù)。這相比二叉樹樹的高度大幅降低。但 InnoDB 沒有直接使用 B 樹而是使用了 B樹原因在于對(duì)比項(xiàng)B 樹B 樹數(shù)據(jù)存儲(chǔ)位置每個(gè)節(jié)點(diǎn)都存數(shù)據(jù)只有葉子節(jié)點(diǎn)存數(shù)據(jù)葉子節(jié)點(diǎn)結(jié)構(gòu)葉子節(jié)點(diǎn)無鏈表連接葉子節(jié)點(diǎn)通過鏈表有序連接查詢穩(wěn)定性非葉子節(jié)點(diǎn)查到即返回不穩(wěn)定必須走到葉子節(jié)點(diǎn)才能取數(shù)據(jù)查詢路徑穩(wěn)定范圍查詢需要中序遍歷效率低借助葉子節(jié)點(diǎn)鏈表順序掃描即可磁盤 IO 次數(shù)較少中間層也可能返回固定等于樹高但樹高更低這里重點(diǎn)說兩個(gè)關(guān)鍵點(diǎn)第一非葉子節(jié)點(diǎn)不存數(shù)據(jù)可以存更多索引鍵值。InnoDB 一頁大小默認(rèn)為 16KB。如果非葉子節(jié)點(diǎn)只存索引鍵值不存數(shù)據(jù)一個(gè) 16KB 的頁可以存放幾百甚至上千個(gè)鍵值。假設(shè)一個(gè)節(jié)點(diǎn)放 1000 個(gè)鍵值樹高為 3 的情況下就能存儲(chǔ) 10 億級(jí)別的數(shù)據(jù)量1000 × 1000 × 1000。也就是說查詢一張億級(jí)數(shù)據(jù)表只需要 3 次磁盤 IO 就能定位到葉子節(jié)點(diǎn)這個(gè)效率遠(yuǎn)遠(yuǎn)超過紅黑樹。第二葉子節(jié)點(diǎn)用鏈表串聯(lián)范圍查詢非?？臁?duì)于 SQL 中的BETWEEN、、、ORDER BY這類范圍操作B樹在找到第一個(gè)滿足條件的記錄后只需要順著葉子節(jié)點(diǎn)的鏈表指針向后掃描即可不需要回溯父節(jié)點(diǎn)。B 樹要實(shí)現(xiàn)范圍查詢需要在節(jié)點(diǎn)之間來回跳躍效率低得多。2.4 小結(jié)B 樹三大核心優(yōu)勢(shì)樹高低一般 2~4 層磁盤 IO 次數(shù)穩(wěn)定且少。非葉子節(jié)點(diǎn)只存索引鍵一頁能容納更多節(jié)點(diǎn)天然適合磁盤分頁存儲(chǔ)。葉子節(jié)點(diǎn)有序鏈表讓排序和范圍查詢變成順序 IO性能極優(yōu)。理解了這幾個(gè)點(diǎn)面試題“為什么索引結(jié)構(gòu)選 B樹”你就能從磁盤 IO 的角度講出深度了。3. BufferPool 與索引查詢?yōu)槭裁醋x數(shù)據(jù)不是直接查磁盤很多人在講 MySQL 索引的時(shí)候只講樹結(jié)構(gòu)不提 BufferPool這其實(shí)是不夠的。因?yàn)樗饕樵兊男阅軆?yōu)勢(shì)在很大程度上依賴 BufferPool 對(duì)數(shù)據(jù)頁和索引頁的緩存。3.1 BufferPool 是什么BufferPool緩沖池是 InnoDB 存儲(chǔ)引擎在內(nèi)存中維護(hù)的一片區(qū)域用于緩存數(shù)據(jù)頁、索引頁、undo 日志頁等。InnoDB 的所有讀寫操作第一步都是先操作 BufferPool 中的頁而不是直接操作磁盤。----------------------- | MySQL | | ----------------- | | | BufferPool | | | | (內(nèi)存緩存) | | | ----------------- | | | | | v | | ----------------- | | | 磁盤數(shù)據(jù)文件 | | | ----------------- | -----------------------可以簡(jiǎn)單理解為磁盤是倉庫BufferPool 是倉庫門口的臨時(shí)貨架。查詢數(shù)據(jù)時(shí)優(yōu)先看貨架上有沒有沒有再去倉庫搬。3.2 BufferPool 對(duì)索引查詢的影響回到上面 B樹的例子。我們說的“查詢一張億級(jí)表只需要 3 次磁盤 IO”這是最理想情況。實(shí)際上B樹的根節(jié)點(diǎn)、中間層節(jié)點(diǎn)如果已經(jīng)被加載到 BufferPool 中那么查詢時(shí)根本不需要產(chǎn)生磁盤 IO直接從內(nèi)存讀取即可。所以索引查詢性能的關(guān)鍵不只是 B樹本身還包括BufferPool 是否足夠大能否容納熱數(shù)據(jù)頁和索引頁。索引是否足夠“瘦”也就是索引鍵值占用空間是否合理。查詢是否觸發(fā)了全表掃描導(dǎo)致大量冷數(shù)據(jù)頁頻繁換入換出。3.3 面試題擴(kuò)展為什么不直接全部放內(nèi)存既然內(nèi)存這么快為什么 MySQL 不把數(shù)據(jù)全部放進(jìn)內(nèi)存原因很現(xiàn)實(shí)內(nèi)存成本遠(yuǎn)高于磁盤數(shù)據(jù)量超過內(nèi)存容量時(shí)內(nèi)存裝不下。內(nèi)存是易失性存儲(chǔ)斷電后數(shù)據(jù)會(huì)丟失數(shù)據(jù)庫必須保證數(shù)據(jù)持久化到磁盤。MySQL 設(shè)計(jì)目標(biāo)之一是支持遠(yuǎn)超內(nèi)存容量的海量數(shù)據(jù)存儲(chǔ)。所以 MySQL 的架構(gòu)是“內(nèi)存 磁盤”的層級(jí)組合BufferPool 解決的是熱數(shù)據(jù)的訪問速度磁盤上的 B樹解決的是海量數(shù)據(jù)的有序存儲(chǔ)。3.4 索引命中和 BufferPool 的協(xié)同效果做一個(gè)簡(jiǎn)單的計(jì)算假設(shè)一張 1000 萬行的表主鍵索引是BIGINT類型一個(gè)索引鍵占 8 字節(jié)。B樹根節(jié)點(diǎn)所在的頁是常駐內(nèi)存的第二層節(jié)點(diǎn)大約幾百個(gè)頁如果 BufferPool 足夠大這層也能被緩存。那么在緩存命中的情況下一次主鍵查詢的成本近似等于1 次內(nèi)存 B樹路徑查找微秒級(jí)。1 次內(nèi)存中讀取目標(biāo)數(shù)據(jù)頁微秒級(jí)。整個(gè)過程沒有磁盤 IO所以單次主鍵查詢可以在 1ms 以內(nèi)完成。這就是為什么我們說“InnoDB 按主鍵查詢非常快”。4. 主鍵索引、二級(jí)索引、回表索引到底怎么組織數(shù)據(jù)面試中有一個(gè)連環(huán)追問非常常見主鍵索引和普通索引有什么區(qū)別什么是回表回表一定需要嗎什么是索引覆蓋這些問題全部圍繞 InnoDB 聚簇索引的特性展開。4.1 聚簇索引主鍵索引InnoDB 的表本質(zhì)上就是一棵 B樹這棵 B樹的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)這個(gè) B樹就是聚簇索引通常也說主鍵索引。在 InnoDB 中聚簇索引的葉子節(jié)點(diǎn) 主鍵值 完整行記錄。-- 建一張測(cè)試表 CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL, age INT DEFAULT NULL, email VARCHAR(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;對(duì)于這張表id是主鍵InnoDB 會(huì)基于id構(gòu)建聚簇索引。聚簇索引的葉子節(jié)點(diǎn)直接存放id、username、age、email的完整數(shù)據(jù)。查詢SELECT * FROM user WHERE id 100只需要走聚簇索引找到葉子節(jié)點(diǎn)直接返回整行數(shù)據(jù)。這里有一個(gè)重要設(shè)計(jì)點(diǎn)聚簇索引決定了數(shù)據(jù)在磁盤上的物理存儲(chǔ)順序。因?yàn)槿~子節(jié)點(diǎn)本身就是數(shù)據(jù)數(shù)據(jù)行按照主鍵值在磁盤上有序排列。所以 InnoDB 表也叫索引組織表Index Organized Table。4.2 二級(jí)索引輔助索引除了主鍵索引之外我們手動(dòng)創(chuàng)建的普通索引都叫二級(jí)索引。CREATE INDEX idx_username ON user(username);二級(jí)索引的葉子節(jié)點(diǎn)結(jié)構(gòu)是索引列的值 主鍵值。也就是說走idx_username這條索引查數(shù)據(jù)最多只能拿到兩樣?xùn)|西usernameid如果查詢要返回的字段不只是這兩列MySQL 就需要拿著拿到的id再到聚簇索引里查一次完整記錄。這個(gè)拿著二級(jí)索引的主鍵值去聚簇索引里查完整行的過程就叫回表Table Lookup。-- 這條 SQL 需要回表 -- 因?yàn)?username 索引里沒有 email EXPLAIN SELECT * FROM user WHERE username zhangsan;執(zhí)行計(jì)劃大概是這樣-------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | user | NULL | ref | idx_username | idx_username | 258 | const | 1 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------4.3 覆蓋索引避免回表的優(yōu)化手段如果查詢所需的字段都能從二級(jí)索引中拿到就不需要回表了這種場(chǎng)景叫覆蓋索引Covering Index。-- 這條 SQL 只需要 username 和 id -- 而這兩個(gè)字段在 idx_username 索引中都有 EXPLAIN SELECT id, username FROM user WHERE username zhangsan;執(zhí)行計(jì)劃的 Extra 列會(huì)顯示Using index表示不需要回表。覆蓋索引是優(yōu)化高頻查詢的重要手段尤其是在統(tǒng)計(jì)類、列表類查詢中效果明顯。4.4 面試回答要點(diǎn)InnoDB 表只有一個(gè)聚簇索引通常就是主鍵索引。聚簇索引葉子節(jié)點(diǎn)存完整行數(shù)據(jù)二級(jí)索引葉子節(jié)點(diǎn)存索引列值 主鍵值。回表是指二級(jí)索引查到主鍵后再回聚簇索引取完整行的過程。覆蓋索引可以讓查詢免于回表是 SQL 優(yōu)化的重要方向。如果表沒有定義主鍵InnoDB 會(huì)選一個(gè)非空唯一索引作為聚簇索引如果也沒有InnoDB 會(huì)隱式生成一個(gè) rowid 作為聚簇索引。5. 聯(lián)合索引最左前綴、索引下推與設(shè)計(jì)要點(diǎn)在真實(shí)業(yè)務(wù)系統(tǒng)中單列索引的使用場(chǎng)景其實(shí)有限。更多的查詢條件會(huì)同時(shí)包含多個(gè)列這就涉及到聯(lián)合索引。5.1 什么是聯(lián)合索引聯(lián)合索引Composite Index是在多個(gè)列上同時(shí)建立的索引。CREATE INDEX idx_user_age_name ON user(age, username);這個(gè)索引的特點(diǎn)是先按 age 排序age 相同的情況下再按 username 排序。所以聯(lián)合索引的 B樹里鍵值是一個(gè)元組(age, username)。舉個(gè)直觀的例子age18, usernameaaa age18, usernamebbb age19, usernameccc age20, usernameaaa可以看到age是主導(dǎo)順序的第一列username只在age相等的時(shí)候才體現(xiàn)排序價(jià)值。5.2 最左前綴原則聯(lián)合索引最重要的規(guī)則就是最左前綴原則Leftmost Prefix。意思是聯(lián)合索引(a, b, c)可以被以下查詢條件使用a等值查詢a b等值查詢a b c等值查詢a的范圍查詢a b的范圍查詢但不一定能被以下條件高效使用直接使用b作為查詢條件直接使用c作為查詢條件查詢條件跳過中間列用(age, username)索引來舉例-- 可以使用索引滿足最左前綴 SELECT * FROM user WHERE age 18; SELECT * FROM user WHERE age 18 AND username aaa; SELECT * FROM user WHERE age 18 AND username aaa; -- 無法高效使用索引跳過了 age SELECT * FROM user WHERE username aaa;為什么直接查username用不了索引因?yàn)?B樹的葉子節(jié)點(diǎn)里先按age排好序username的有序性是建立在age相同的前提下的。如果只給出usernameMySQL 沒有辦法在 B樹里直接定位只能老老實(shí)實(shí)全表掃描或者走另外的索引。5.3 面試高頻追問WHERE a 1 AND b 2與WHERE b 2 AND a 1有區(qū)別嗎在 MySQL 優(yōu)化器足夠智能的情況下WHERE條件的書寫順序不影響索引的使用。優(yōu)化器會(huì)做條件重排把符合最左前綴條件的列拎出來。但是注意如果查詢條件中的某個(gè)列使用了函數(shù)、隱式類型轉(zhuǎn)換或者參與運(yùn)算這個(gè)列就可能無法走索引。這一點(diǎn)在后面的“索引失效”部分會(huì)展開講。5.4 索引下推Index Condition PushdownICP這是近幾年 Java 后端面試特別喜歡問的一個(gè)點(diǎn)。我們先看一個(gè)場(chǎng)景-- 表結(jié)構(gòu)聯(lián)合索引 idx(age, username) -- 查詢條件age 范圍 username 等值 SELECT * FROM user WHERE age 18 AND username LIKE 張%;按照最左前綴規(guī)則age走了索引但age是范圍條件username無法繼續(xù)在索引樹中精確定位。在沒有索引下推的舊版本 MySQL 中查詢流程是通過age 18從索引中找到一批主鍵 id。拿這批 id 回表把完整行讀出來。在服務(wù)器層過濾username LIKE 張%。問題在于在毫秒級(jí)性能敏感的業(yè)務(wù)里回表次數(shù)越多性能越差。很多不滿足username條件的行白白回表了一次。開啟索引下推后MySQL 5.6 默認(rèn)開啟流程變成通過age 18從索引中找到索引記錄。直接在索引內(nèi)部判斷username LIKE 張%是否滿足。只有滿足條件的記錄才回表。這樣就減少了大量無謂的回表操作。我們可以從執(zhí)行計(jì)劃中看到Using index condition字樣------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | user | NULL | range | idx_user_age_name | 262 | NULL | 100 | 11.11 | Using index condition | -------------------------------------------------------------------------------------------------------------------------------------5.5 聯(lián)合索引的經(jīng)典設(shè)計(jì)建議識(shí)別高頻查詢把選擇性最好的列放在最前面。盡量把等值條件列放前面范圍條件列放后面這樣能最大程度利用索引的有序性。一次范圍查詢會(huì)使后續(xù)列無法繼續(xù)用于索引定位但可以被索引下推部分優(yōu)化。聯(lián)合索引要控制列的數(shù)量一般不建議超過 3~4 列避免索引占用空間過大、寫入成本過高。如果查詢經(jīng)常出現(xiàn)(a, b)條件設(shè)計(jì)一個(gè)(a, b)聯(lián)合索引通常比兩個(gè)單列索引更高效。6. 索引失效的六大典型場(chǎng)景面試中另一類高頻題是“哪些情況會(huì)導(dǎo)致索引失效”。如果回答不完整面試官會(huì)覺得你對(duì)索引的理解只停留在表面。下面結(jié)合 SQL 示例列出最常見的索引失效場(chǎng)景。6.1 對(duì)索引列使用函數(shù)-- username 上有普通索引 SELECT * FROM user WHERE LOWER(username) zhangsan;在索引列上使用函數(shù)MySQL 無法直接使用 B樹的有序性進(jìn)行查找索引失效。實(shí)際項(xiàng)目中常見的是對(duì)日期列使用DATE_FORMAT、YEAR等函數(shù)。解決方案把函數(shù)操作遷移到查詢值上-- 改寫為等價(jià)的等值條件 SELECT * FROM user WHERE username zhangsan; -- 日期范圍查詢 -- 不要寫成 DATE_FORMAT(create_time, %Y-%m-%d) 2026-01-01 -- 應(yīng)寫成 SELECT * FROM user WHERE create_time 2026-01-01 00:00:00 AND create_time 2026-01-02 00:00:00;6.2 隱式類型轉(zhuǎn)換-- user_phone 是 VARCHAR 類型查詢時(shí)用了數(shù)值 SELECT * FROM user WHERE user_phone 13800138000;MySQL 會(huì)把字符串類型和數(shù)值類型比較時(shí)隱式地把字符串列轉(zhuǎn)換為數(shù)值導(dǎo)致索引失效。解決方案保持字段類型一致SELECT * FROM user WHERE user_phone 13800138000;6.3 不符合最左前綴原則前面已經(jīng)講過-- 聯(lián)合索引 idx(age, username) SELECT * FROM user WHERE username zhangsan;6.4 LIKE 以通配符開頭-- username 上有普通索引 SELECT * FROM user WHERE username LIKE %zhang%;當(dāng)%出現(xiàn)在字符串最前面時(shí)MySQL 無法利用 B樹的有序結(jié)構(gòu)因?yàn)闊o法確定匹配的起點(diǎn)位置。如果是右模糊SELECT * FROM user WHERE username LIKE zhang%;這種情況是可以走索引的。6.5 OR 連接的條件包含非索引列-- 假設(shè) id 有主鍵索引email 沒有索引 SELECT * FROM user WHERE id 100 OR email zhangsanexample.com;OR 兩邊的條件只要有一個(gè)字段沒有索引整個(gè)查詢就可能會(huì)退化為全表掃描。更優(yōu)的寫法是用 UNION 拆開SELECT * FROM user WHERE id 100 UNION SELECT * FROM user WHERE email zhangsanexample.com;6.6 索引列參與運(yùn)算-- age 有索引 SELECT * FROM user WHERE age 1 18;對(duì)索引列做算術(shù)運(yùn)算會(huì)破壞索引列的值本身MySQL 無法直接比較索引失效。改寫為SELECT * FROM user WHERE age 17;6.7 索引失效排查模板場(chǎng)景示例正確寫法函數(shù)操作WHERE DATE(col) 2026-01-01WHERE col ... AND col ...隱式轉(zhuǎn)換WHERE varchar_col 123WHERE varchar_col 123最左前綴失效WHERE b 1索引是(a,b)調(diào)整索引列順序或補(bǔ)上 a 條件前置通配符WHERE name LIKE %abcWHERE name LIKE abc%OR 截?cái)郬HERE a 1 OR no_index_col 2拆分為 UNION 查詢列運(yùn)算WHERE age 1 18WHERE age 17需要特別提醒的是如果表數(shù)據(jù)量很小MySQL 優(yōu)化器可能放棄索引直接全表掃描這不算索引失效而是優(yōu)化器認(rèn)為全表掃描成本更低。在分析索引問題時(shí)要用EXPLAIN看執(zhí)行計(jì)劃而不是只看“有沒有走索引”這個(gè)直覺。7. 千萬級(jí)索引優(yōu)化實(shí)戰(zhàn)從慢 SQL 到執(zhí)行計(jì)劃分析前面講了很多概念這一節(jié)用一個(gè)接近真實(shí)業(yè)務(wù)的案例把慢 SQL 優(yōu)化流程完整走一遍。7.1 模擬表結(jié)構(gòu)與數(shù)據(jù)背景假設(shè)我們有一張訂單表orders體量在千萬級(jí)CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 訂單號(hào), user_id BIGINT NOT NULL COMMENT 用戶ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 訂單狀態(tài) 0-待支付 1-已支付 2-已取消, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 訂單金額, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下單時(shí)間, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;業(yè)務(wù)側(cè)有一個(gè)高頻查詢查詢某個(gè)用戶某個(gè)時(shí)間段的訂單列表并按下單時(shí)間倒序。SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 123456 AND create_time 2026-01-01 00:00:00 AND create_time 2026-02-01 00:00:00 ORDER BY create_time DESC LIMIT 20;7.2 優(yōu)化前全表掃描定位如果第一個(gè)版本直接執(zhí)行這條 SQL在沒有合適索引的情況下執(zhí)行計(jì)劃會(huì)顯示type ALL也就是全表掃描。----------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 1048576 | 10.00 | Using where; Using filesort | -----------------------------------------------------------------------------------------------------------------------注意上面的Using filesort這意味著 MySQL 需要把結(jié)果集先排序再取前 20 條。千萬級(jí)數(shù)據(jù)量的全表掃描 文件排序這條 SQL 基本可以認(rèn)定為慢 SQL。7.3 第一步優(yōu)化為高頻查詢建立聯(lián)合索引根據(jù)查詢條件user_id是等值條件create_time是范圍條件建議聯(lián)合索引設(shè)計(jì)為ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time);建立索引后再次執(zhí)行EXPLAIN-------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | range | idx_user_create_time| idx_user_create_time | 12 | NULL | 560 | 100.00 | Using index condition | --------------------------------------------------------------------------------------------------------------------------------------------此時(shí)type從ALL變成了range說明聯(lián)合索引生效了。rows從 100 萬級(jí)別下降到了幾百行。因?yàn)樗饕~子節(jié)點(diǎn)本身按(user_id, create_time)排序ORDER BY create_time DESC已經(jīng)可以直接從索引的有序性中拿到結(jié)果Using filesort消失了。7.4 第二步優(yōu)化使用覆蓋索引消除回表再看上面的 SQL查詢列是id, order_no, amount, status, create_time。其中只有id,create_time,user_id在索引中order_no,amount,status不在索引中所以拿到符合條件的索引記錄后還需要回表 560 次才能取到完整數(shù)據(jù)。如果這個(gè)查詢是超高頻查詢我們可以考慮建立更寬的覆蓋索引ALTER TABLE orders ADD INDEX idx_user_create_time_cover (user_id, create_time, order_no, amount, status);執(zhí)行計(jì)劃會(huì)變化為Using index也就是不需要回表------------------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | NULL | range | idx_user_create_time_cover | idx_user_create_time_cover | 12 | NULL | 560 | 100.00 | Using index | ------------------------------------------------------------------------------------------------------------------------------------------------不過覆蓋索引要權(quán)衡它會(huì)把更多列放進(jìn)索引索引體積更大插入、更新成本更高。它適合讀多寫少、查詢結(jié)果列相對(duì)固定的場(chǎng)景。一般來說線上表不應(yīng)該盲目造寬索引。先看慢 SQL 的rows是否已經(jīng)很低如果回表量在幾百行以內(nèi)大多數(shù)情況下性能都是可以接受的。是否要覆蓋索引取決于壓測(cè)結(jié)果和業(yè)務(wù)瓶頸。7.5 深分頁問題LIMIT 100000, 20的性能陷阱還有一個(gè)非常常見的性能問題——深分頁。SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 123456 ORDER BY create_time DESC LIMIT 100000, 20;即使走了聯(lián)合索引這種LIMIT 100000, 20的寫法在千萬級(jí)數(shù)據(jù)下依然非常慢。原因是 MySQL 需要先掃描前 100020 條符合條件的索引記錄然后丟棄前 100000 條只返回最后 20 條。優(yōu)化思路是先獲取主鍵再用主鍵關(guān)聯(lián)回表SELECT t.id, t.order_no, t.amount, t.status, t.create_time FROM orders t INNER JOIN ( SELECT id FROM orders WHERE user_id 123456 AND create_time 2026-01-01 00:00:00 AND create_time 2026-02-01 00:00:00 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC;或者使用“上一頁最大 id”的方式也就是基于游標(biāo)的分頁SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 123456 AND create_time 2026-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20;在業(yè)務(wù)允許的情況下基于游標(biāo)的分頁是性能最優(yōu)的方案因?yàn)樗苊饬恕跋葤呙璐罅繜o用記錄再丟棄”的問題。7.6 慢 SQL 優(yōu)化標(biāo)準(zhǔn)流程開啟慢查詢?nèi)罩径ㄎ痪唧w慢 SQL。使用EXPLAIN分析執(zhí)行計(jì)劃關(guān)注type、key、rows、Extra四列。確認(rèn)當(dāng)前查詢是全表掃描、文件排序還是回表過多。根據(jù)高頻查詢條件設(shè)計(jì)合理的聯(lián)合索引。用EXPLAIN驗(yàn)證索引是否生效注意key_len和rows是否合理。壓測(cè)驗(yàn)證性能而不是只憑執(zhí)行計(jì)劃做判斷。8. 索引在 JVM 與 MySQL 協(xié)同場(chǎng)景中的常見誤區(qū)標(biāo)題里出現(xiàn)了“Java后端面試”所以這里要補(bǔ)充一個(gè)容易被忽略的交叉知識(shí)點(diǎn)為什么索引不能解決所有慢 SQL 問題它和 JVM 層面的問題有什么關(guān)聯(lián)很多后端同學(xué)遇到“接口變慢”第一反應(yīng)就是“建索引”。但有時(shí)候慢的根本原因不在 MySQL而在于應(yīng)用層。8.1 慢 SQL 之外的性能瓶頸瓶頸層現(xiàn)象排查方向JVM GC 頻繁接口響應(yīng)變慢CPU 飆高查看 GC 日志、堆內(nèi)存占用連接池打滿數(shù)據(jù)庫連接獲取超時(shí)查看連接池配置、慢 SQL 堆積網(wǎng)絡(luò)抖動(dòng)查詢本身很快但整體耗時(shí)高查看鏈路追蹤、網(wǎng)絡(luò)延遲緩存失效大量請(qǐng)求穿透到數(shù)據(jù)庫檢查 Redis 緩存擊穿、雪崩策略索引解決的是“減少 MySQL 側(cè)掃描數(shù)據(jù)量”的問題。如果數(shù)據(jù)庫查詢只要 5ms但 JVM 在 Full GC 上卡了 500ms那建再多索引也無濟(jì)于事。8.2 面試官喜歡的完整回答框架當(dāng)一個(gè)候選人被問到“SQL 慢怎么排查”時(shí)高分回答大概率是這樣的先確認(rèn)到底慢在數(shù)據(jù)庫還是慢在應(yīng)用層?？唇涌谡w耗時(shí)、數(shù)據(jù)庫耗時(shí)占比、GC 情況。如果慢在數(shù)據(jù)庫開啟慢查詢?nèi)罩咀コ雎?SQL。用EXPLAIN看執(zhí)行計(jì)劃依次分析是否全表掃描、是否文件排序、是否回表過多、是否索引失效。結(jié)合業(yè)務(wù)查詢場(chǎng)景設(shè)計(jì)聯(lián)合索引或覆蓋索引。如果索引優(yōu)化后依然不夠再考慮 SQL 改寫、分庫分表、緩存、讀寫分離等手段。每次優(yōu)化都要做壓測(cè)不能只憑感覺上線。這個(gè)框架既體現(xiàn)了技術(shù)深度又展示了工程思維比較容易被面試官認(rèn)可。9. 常見面試題速查表這一節(jié)把高頻面試題和參考回答濃縮為一個(gè)清單方便你在面試前最后 10 分鐘快速翻閱。面試題核心回答要點(diǎn)為什么 InnoDB 用 B樹不用 B 樹B樹非葉子節(jié)點(diǎn)不存數(shù)據(jù)樹高低磁盤 IO 少葉子節(jié)點(diǎn)有序鏈表范圍查詢強(qiáng)什么是聚簇索引InnoDB 表的主鍵索引葉子節(jié)點(diǎn)存完整行記錄什么是回表二級(jí)索引查到主鍵后再到聚簇索引取完整行的過程什么是覆蓋索引查詢所需字段全部在二級(jí)索引中不需要回表聯(lián)合索引最左前綴原則是什么聯(lián)合索引按列順序排序查詢條件從最左列開始連續(xù)匹配才能高效走索引索引下推是什么MySQL 在索引遍歷過程中對(duì)索引列做條件過濾減少回表次數(shù)哪些情況會(huì)導(dǎo)致索引失效函數(shù)操作、隱式類型轉(zhuǎn)換、前置通配符、聯(lián)合索引跳列、OR 含非索引列、列參與運(yùn)算主鍵能用 UUID 嗎不建議。UUID 無序聚簇索引會(huì)頻繁頁分裂寫入性能差建議自增 id 或有序雪花 id為什么不建議給每個(gè)列建索引每個(gè)索引都是額外 B樹占用空間寫入時(shí)要同時(shí)維護(hù)多個(gè)索引寫放大明顯強(qiáng)制走索引一定更好嗎不一定。小表全表掃描成本更低優(yōu)化器會(huì)自行選擇可用 FORCE INDEX 做驗(yàn)證但不宜生產(chǎn)強(qiáng)制使用10. 最佳實(shí)踐與索引設(shè)計(jì)規(guī)范最后這一部分是作者在平時(shí)和團(tuán)隊(duì)做代碼 review 時(shí)最常強(qiáng)調(diào)的一些點(diǎn)希望對(duì)你也有啟發(fā)。10.1 索引命名規(guī)范主鍵約束PK_表名(縮寫)例如PK_orders。唯一索引uk_字段名例如uk_order_no。普通索引idx_字段名多個(gè)字段用下劃線連接例如idx_user_id_create_time。統(tǒng)一命名方便排查問題也能避免索引名重復(fù)導(dǎo)致項(xiàng)目里的腳本沖突。10.2 區(qū)分業(yè)務(wù)索引與輔助索引核心業(yè)務(wù)查詢字段要安排聯(lián)合索引。低頻查詢字段不要隨意建索引。一張表索引數(shù)量一般控制在 5~6 個(gè)以內(nèi)超過這個(gè)數(shù)量要仔細(xì)審視寫入成本。10.3 控制索引鍵長(zhǎng)度索引列越短B樹每個(gè)頁能裝下的鍵值越多樹就越矮查詢磁盤 IO 次數(shù)越少。如果某個(gè)字段是超長(zhǎng)字符串可以使用前綴索引-- 對(duì) username 的前 20 個(gè)字符建索引 CREATE INDEX idx_username_prefix ON user(username(20));但需要注意前綴索引很可能無法用于ORDER BY和覆蓋索引場(chǎng)景。10.4 在測(cè)試環(huán)境驗(yàn)證執(zhí)行計(jì)劃線上變更索引時(shí)務(wù)必遵循以下步驟在測(cè)試環(huán)境或預(yù)發(fā)環(huán)境執(zhí)行EXPLAIN確認(rèn)執(zhí)行計(jì)劃符合預(yù)期。確認(rèn)新索引不會(huì)導(dǎo)致重復(fù)索引例如已有(a)索引又建了(a, b)可能產(chǎn)生冗余索引。在低峰期ALTER TABLE添加索引避免長(zhǎng)時(shí)間鎖表。重要表的 DDL 變更要有回滾方案建議記錄變更時(shí)間、變更人、影響范圍。-- 查看表的現(xiàn)有索引 SHOW INDEX FROM orders; -- 刪除冗余索引如果確認(rèn)無用 ALTER TABLE orders DROP INDEX idx_create_time;10.5 索引與業(yè)務(wù)代碼的配合在 Java 后端代碼層面有幾個(gè)容易被忽視的坑MyBatis 中動(dòng)態(tài) SQL 的條件拼接可能導(dǎo)致查詢條件不固定使得索引設(shè)計(jì)難度增加。建議高頻查詢固定的幾個(gè)查詢模板而不是讓用戶所有字段都能隨意組合。批量插入時(shí)大量二級(jí)索引維護(hù)會(huì)拖慢寫入速度。在導(dǎo)入歷史數(shù)據(jù)時(shí)可以考慮先刪索引、導(dǎo)入數(shù)據(jù)、再重建索引。分頁查詢盡量使用游標(biāo)分頁而不是深分頁LIMIT offset, size。對(duì)統(tǒng)計(jì)報(bào)表類查詢不要指望單條 SQL 加個(gè)索引就能支撐億級(jí)數(shù)據(jù)實(shí)時(shí)計(jì)算該上匯總表或離線數(shù)倉就要上。10.6 從 SQL 角度保護(hù)線上安全結(jié)合近年的數(shù)據(jù)安全問題所有開發(fā)同學(xué)都應(yīng)該養(yǎng)成一個(gè)習(xí)慣更新和刪除 SQL 必須先EXPLAIN確認(rèn)影響行數(shù)或者先在事務(wù)里用SELECT COUNT(*)確認(rèn)范圍。千萬級(jí)表上的DELETE FROM orders WHERE status 0如果沒有走索引不僅會(huì)產(chǎn)生慢 SQL還可能導(dǎo)致鎖范圍擴(kuò)大影響線上可用性。生產(chǎn)環(huán)境不建議直接用DELETE清理超大表數(shù)據(jù)可以考慮分批刪除或歸檔表。任何UPDATE和DELETE都要帶 WHERE且 WHERE 條件必須能走索引。數(shù)據(jù)庫賬號(hào)權(quán)限要遵循最小權(quán)限原則應(yīng)用賬號(hào)不應(yīng)該有DROP、TRUNCATE權(quán)限。寫在最后MySQL 索引是一個(gè)典型的“看起來簡(jiǎn)單、挖下去很深”的知識(shí)點(diǎn)。從 B樹的磁盤 IO 特性到聚簇索引與二級(jí)索引的內(nèi)部結(jié)構(gòu)再到聯(lián)合索引、索引下推、覆蓋索引和 BufferPool 的協(xié)同機(jī)制每一層都直接決定你在面試中能展示出多少深度。這篇文章里的內(nèi)容大家可以對(duì)照實(shí)際項(xiàng)目里的慢 SQL 去驗(yàn)證也可以拿一張千萬級(jí)測(cè)試表自己建索引、看執(zhí)行計(jì)劃、對(duì)比優(yōu)化前后耗時(shí)。只有自己親手操作過一次面試提問時(shí)才能真正講出底氣。祝每一位讀者都能在 2026 年的面試中拿到心儀的 offer。如果這篇文章對(duì)你有幫助歡迎點(diǎn)贊、收藏下一篇會(huì)繼續(xù)深入聊聊 MySQL 的鎖機(jī)制與事務(wù)隔離級(jí)別在 Java 后端面試中的高頻考點(diǎn)。