一文詳解MySQL索引(六張圖徹底搞懂)
一、什么是索引？為什么需要索引？
查字典時，你會逐頁翻找某個漢字嗎？顯然不會。我們通常會先查目錄，通過拼音或部首定位到漢字所在的頁碼——這個目錄就是一種索引。它通過額外的空間存儲（目錄頁），換取了查詢速度的大幅提升，這正是"空間換時間"的經(jīng)典思想。
數(shù)據(jù)庫中的索引本質(zhì)相同：它是一種能快速定位數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，核心作用是加速SQL查詢。沒有索引的查詢就像逐頁翻字典，需要掃描整張表（全表掃描），在數(shù)據(jù)量龐大時效率極低；而有了索引，數(shù)據(jù)庫能直接定位到目標(biāo)數(shù)據(jù)所在的位置，避免無效掃描。
二、索引該用哪種數(shù)據(jù)結(jié)構(gòu)？
MySQL索引設(shè)計的出發(fā)點(diǎn)在于，要能按區(qū)間高效地范圍查找，還要盡少在磁盤 I/O 操作中做查詢。
1. 哈希表
哈希表通過鍵值對存儲，查詢單個數(shù)據(jù)的時間復(fù)雜度是O(1)，看似高效，但有致命缺陷：
- 無法支持范圍查詢（如
age > 30） - 不能按順序返回結(jié)果（無法滿足
ORDER BY需求） - 哈希沖突會導(dǎo)致性能波動
因此，哈希表僅適合精準(zhǔn)匹配的場景（如字典查詢），無法作為數(shù)據(jù)庫的主力索引結(jié)構(gòu)。
2. 跳表
跳表是一種基于鏈表的 “分層索引結(jié)構(gòu)”，核心思路是給基礎(chǔ)鏈表增加多級索引，實(shí)現(xiàn)類似 “二分查找” 的高效查詢：
- 結(jié)構(gòu)特點(diǎn)：底層是有序鏈表，上層索引層按固定間隔（如每 2 個節(jié)點(diǎn)）抽取節(jié)點(diǎn)形成，最高層索引指向鏈表首尾，通過索引層快速定位范圍，再下沉到底層鏈表精確查找。
- 優(yōu)勢：支持范圍查詢（天然有序），插入 / 刪除無需旋轉(zhuǎn)（只需調(diào)整索引層指針），實(shí)現(xiàn)復(fù)雜度低于平衡樹。
- 缺陷：隨著數(shù)據(jù)量遞增，索引層數(shù)會同步增加（百萬級數(shù)據(jù)可能需要 10 + 層索引）。數(shù)據(jù)庫索引需持久化到磁盤，每一層索引的訪問都對應(yīng)一次磁盤 IO—— 多層索引會導(dǎo)致 IO 次數(shù)激增，反而比二叉樹更低效。此外，跳表的索引層占用大量額外空間，數(shù)據(jù)量越大空間開銷越突出。
跳表更適合內(nèi)存數(shù)據(jù)庫（如 Redis 的 Sorted Set），磁盤數(shù)據(jù)庫中因 IO 開銷問題極少采用。
3. 二叉排序樹
二叉排序樹（左子樹 < 根節(jié)點(diǎn) < 右子樹）支持范圍查詢和排序，但存在嚴(yán)重缺陷：
- 順序插入時會退化為鏈表（如插入1、2、3、4），查詢時間復(fù)雜度從O(logn)暴跌至O(n)
- 樹高過高導(dǎo)致磁盤IO頻繁（數(shù)據(jù)庫索引需持久化到磁盤，樹高直接影響IO次數(shù)）
4. 平衡二叉樹
平衡二叉樹（如AVL樹）通過旋轉(zhuǎn)操作維持平衡（左右子樹高度差≤1），解決了退化問題，但新問題出現(xiàn)：
- 為保持"絕對平衡"，插入/刪除時需頻繁旋轉(zhuǎn)，導(dǎo)致大量磁盤IO
- 仍為二叉結(jié)構(gòu)（每個節(jié)點(diǎn)最多2個子樹），數(shù)據(jù)量過大時樹高依然很高（百萬級數(shù)據(jù)樹高約20）
5. 紅黑樹
紅黑樹是一種"近似平衡"的二叉樹，通過變色和有限旋轉(zhuǎn)維持平衡，不追求絕對高度差：
- 減少了旋轉(zhuǎn)次數(shù)，降低了插入/刪除的IO開銷
- 但本質(zhì)仍是二叉樹，數(shù)據(jù)量龐大時樹高問題依然存在（千萬級數(shù)據(jù)樹高約30）
紅黑樹更適合內(nèi)存中的小規(guī)模數(shù)據(jù)（如Java HashMap中鏈表轉(zhuǎn)紅黑樹的閾值為8），而非磁盤存儲的數(shù)據(jù)庫索引。
6. B樹
B樹是多路平衡排序樹（一個節(jié)點(diǎn)可包含多個子樹），顯著降低了樹高：
- 每個節(jié)點(diǎn)存儲多個鍵值對（索引+數(shù)據(jù)），減少IO次數(shù)
- 查詢效率不穩(wěn)定，如有的數(shù)據(jù)在二層有的數(shù)據(jù)在最后一層
- 不方便范圍查詢：B 樹能高效的通過等值查詢 90 這個值，但不方便查詢出一個期間內(nèi) 3 ~ 10 區(qū)間內(nèi)所有數(shù)的結(jié)果。因?yàn)楫?dāng) B 樹做范圍查詢時需要使用中序遍歷，那么父節(jié)點(diǎn)和子節(jié)點(diǎn)也就需要不斷的來回切換涉及了多個節(jié)點(diǎn)會給磁盤 I/O 帶來很多負(fù)擔(dān)。
7. B+樹
B+樹就完美解決了上述問題：
- 非葉子節(jié)點(diǎn)只存索引：單個節(jié)點(diǎn)可容納更多索引，樹高顯著降低（百萬級數(shù)據(jù)樹高通常≤3）
- 數(shù)據(jù)只在葉子節(jié)點(diǎn)存儲：所有葉子節(jié)點(diǎn)通過雙向鏈表連接，范圍查詢只需遍歷鏈表，無需回溯
- 磁盤IO友好：樹高低+順序IO，大幅減少磁盤訪問次數(shù)
因此，MySQL等主流數(shù)據(jù)庫均采用B+樹作為索引的底層數(shù)據(jù)結(jié)構(gòu)。
三、B+樹是如何存索引的
MySQL的索引按存儲方式可分為兩類，核心區(qū)別在于"索引是否與數(shù)據(jù)存放在一起"：
1. 聚簇索引
- 特點(diǎn)：索引與數(shù)據(jù)存儲在一起，葉子節(jié)點(diǎn)包含完整的行數(shù)據(jù)
- 典型案例：主鍵索引（InnoDB表必有的索引）
- 優(yōu)勢：查詢主鍵時無需額外操作，直接獲取數(shù)據(jù)
- 注意點(diǎn)：主鍵應(yīng)設(shè)計為自增字段。若主鍵無序（如UUID），插入時會導(dǎo)致B+樹頻繁"頁分裂"，嚴(yán)重影響性能
2. 非聚簇索引（二級索引）
特點(diǎn)：索引與數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲索引值+主鍵ID
典型案例：唯一索引、普通索引、前綴索引等
查詢流程：需經(jīng)過"回表"操作——先通過二級索引找到主鍵ID，再通過主鍵索引查詢完整數(shù)據(jù)
查二級索引一定會回表嗎？
不一定，如果只查id，那二級索引的葉子結(jié)點(diǎn)就有id；如果查詢字段均可在二級索引中找到，也無需回表，這就是覆蓋索引。
四、聯(lián)合索引與最左前綴匹配法則
聯(lián)合索引是針對多個字段創(chuàng)建的索引（如(name, age, gender)），其B+樹按字段順序排序（先按name，再按age，最后按gender）。使用時需遵循最左前綴匹配法則：
必須從左到右匹配：
- 有效：
WHERE name='張三'、WHERE name='張三' AND age=20 - 無效：
WHERE age=20（跳過了最左的name）、WHERE name='張三' AND gender='男'（跳過了中間的age）
- 有效：
字段順序不影響有效性：
WHERE age=20 AND name='張三'會被MySQL優(yōu)化器調(diào)整為name='張三' AND age=20，仍能使用索引
范圍查詢會中斷匹配：
WHERE name='張三' AND age>20 AND gender='男'中，age>20是范圍查詢，后續(xù)的gender無法使用索引
為什么不從最左開始查，就無法匹配呢
比如有一個 user 表，我們給 name 和 age 建立了一個聯(lián)合索引 (name, age)。
ALTER TABLE user add INDEX comidx_name_phone (name,age);
聯(lián)合索引在 B+ 樹中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，按照從左到右的順序依次建立搜索樹 (name 在左邊，age 在右邊)。
注意，name 是有序的，age 是無序的。當(dāng) name 相等的時候，age 才有序。
五、索引失效的場景
理解索引失效的原因，本質(zhì)是理解B+樹的查詢邏輯：
模糊查詢前綴含通配符：
- 有效：
name LIKE '張%'（前綴明確，可匹配索引） - 無效：
name LIKE '%張'（前綴模糊，無法定位索引位置）
- 有效：
索引列參與運(yùn)算或函數(shù)：
- 無效：
WHERE YEAR(birthday) = 1990、WHERE age + 1 = 30（索引存儲原始值，運(yùn)算后無法匹配）
- 無效：
隱式類型轉(zhuǎn)換：
- 無效：
WHERE phone = 13800138000（若phone是字符串類型，會觸發(fā)CAST(phone AS UNSIGNED)，等價于函數(shù)操作）
- 無效：
OR條件包含非索引列：
- 無效：
WHERE name='張三' OR address='北京'（address無索引時，無法同時走索引和全表掃描，直接退化為全表掃描）
- 無效：
六、索引設(shè)計的最佳實(shí)踐
索引并非越多越好，需在查詢性能與寫入性能間平衡：
適合建索引的場景：
- 數(shù)據(jù)量大且查詢頻繁的表
WHERE、GROUP BY、ORDER BY涉及的字段- 區(qū)分度高的字段（如身份證號，而非性別）
不適合建索引的場景：
- 增刪改頻繁的列（索引會增加寫入開銷）
- 數(shù)據(jù)量極小的表（全表掃描可能更快）
- 區(qū)分度低的字段（如性別、狀態(tài)），整個b+樹一邊男一邊女，加索引也提高不了效率
實(shí)用技巧：
- 優(yōu)先使用聯(lián)合索引，提高覆蓋索引概率，避免回表
- 長字符串用前綴索引（如
name(10)），減少空間占用 - 控制單表索引數(shù)量（建議≤5個）
- 避免
SELECT *，減少回表操作
七、如何分析索引使用情況？
- 慢查詢?nèi)罩?/strong>：開啟
slow_query_log，記錄執(zhí)行時間超過閾值的SQL，定位需要優(yōu)化的查詢。 - 執(zhí)行計劃（EXPLAIN）：在SQL前加
EXPLAIN，通過type（訪問類型）、key（使用的索引）等字段判斷是否走索引。
到此這篇關(guān)于MySQL索引的文章就介紹到這了,更多相關(guān)MySQL索引詳解內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL重定位數(shù)據(jù)目錄的實(shí)現(xiàn)方法,分析了重定位MySQL數(shù)據(jù)目錄的實(shí)現(xiàn)原理與技巧,具有一定的參考借鑒價值,需要的朋友可以參考下2014-12-12
MYSQL隨機(jī)抽取查詢 MySQL Order By Rand()效率問題
MYSQL隨機(jī)抽取查詢：MySQL Order By Rand()效率問題一直是開發(fā)人員的常見問題，俺們不是DBA，沒有那么牛B，所只能慢慢研究咯，最近由于項(xiàng)目問題，需要大概研究了一下MYSQL的隨機(jī)抽取實(shí)現(xiàn)方法2011-11-11
慢?SQL?是指執(zhí)行時間超過預(yù)設(shè)閾值的?SQL?語句,本文主要來今天聊一聊sql優(yōu)化的問題,怎么找到那些慢SQL,文中提供了一些常用的方法,希望對大家有所幫助2026-04-04
這篇文章主要介紹了mysql登錄時報socket找不到的問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07
MySQL中大數(shù)據(jù)表增加字段的實(shí)現(xiàn)思路
最近遇到的一個問題，需要在一張將近1000萬數(shù)據(jù)量的表中添加加一個字段，但是直接添加會導(dǎo)致mysql 奔潰，所以需要利用其他的方法進(jìn)行添加，這篇文章主要給大家介紹了MySQL中大數(shù)據(jù)表增加字段的實(shí)現(xiàn)思路，需要的朋友可以參考借鑒。2017-01-01
當(dāng)你收到“臨時表滿了”的警報時,通常意味著 MySQL 在處理查詢時創(chuàng)建的臨時表空間已經(jīng)耗盡,本文主要介紹了MySQL臨時表滿了/臨時表空間耗盡的解決方法,感興趣的可以了解一下2024-08-08
MySQL中數(shù)據(jù)類型相關(guān)的優(yōu)化辦法
這篇文章主要介紹了MySQL中數(shù)據(jù)類型相關(guān)的優(yōu)化辦法,包括使用多列索引等相關(guān)的優(yōu)化方法,需要的朋友可以參考下2015-07-07
mysql派生表（Derived Table）簡單用法實(shí)例解析
這篇文章主要介紹了mysql派生表（Derived Table）簡單用法,結(jié)合實(shí)例形式分析了mysql派生表的原理、簡單使用方法及操作注意事項(xiàng),需要的朋友可以參考下2019-12-12
本文主要介紹了mysql binlog日志自動清理及手動刪除，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
在生產(chǎn)環(huán)境中，由于業(yè)務(wù)的增長或者業(yè)務(wù)的拆分，DBA經(jīng)常需要拆庫操作。那么我們常見的拆庫手段有哪些呢2016-07-07