第一部分：索引基礎(chǔ)

一、索引的本質(zhì)

1.1 什么是索引？

索引（Index） 是一種數(shù)據(jù)結(jié)構(gòu)，用于幫助數(shù)據(jù)庫(kù)高效地查找數(shù)據(jù)。

類比理解：

圖書館找書的方式：

沒(méi)有索引 = 逐本翻找（全表掃描）
有索引   = 通過(guò)目錄快速定位（索引查找）

書籍目錄：
- 章節(jié)目錄：按順序組織（B+樹）
- 關(guān)鍵詞索引：按字母排序（索引）
- 頁(yè)碼：快速定位到具體位置（主鍵）

1.2 索引解決的核心問(wèn)題

問(wèn)題場(chǎng)景：

-- 100萬(wàn)條用戶數(shù)據(jù)
SELECT * FROM users WHERE name = '張三';

無(wú)索引：

• 需要掃描100萬(wàn)行數(shù)據(jù)（全表掃描）
• 時(shí)間復(fù)雜度：O(n)
• IO次數(shù)：取決于表的大小

有索引：

• 通過(guò)B+樹快速定位
• 時(shí)間復(fù)雜度：O(log n)
• IO次數(shù)：通常3-4次（樹的高度）

性能對(duì)比：

數(shù)據(jù)量      無(wú)索引耗時(shí)    有索引耗時(shí)    提升倍數(shù)
1萬(wàn)       10ms          1ms           10倍
10萬(wàn)      100ms         1ms           100倍
100萬(wàn)     1000ms        1ms           1000倍
1000萬(wàn)    10000ms       2ms           5000倍

1.3 索引的核心思想

1. 空間換時(shí)間：額外存儲(chǔ)索引數(shù)據(jù)，換取查詢速度
2. 有序結(jié)構(gòu)：數(shù)據(jù)按特定規(guī)則排列，支持快速查找
3. 減少IO：減少磁盤訪問(wèn)次數(shù)（數(shù)據(jù)庫(kù)性能瓶頸）

二、索引的底層數(shù)據(jù)結(jié)構(gòu)

2.1 為什么選擇B+樹？

常見數(shù)據(jù)結(jié)構(gòu)對(duì)比

為什么不用其他結(jié)構(gòu)？

1. 哈希表

優(yōu)點(diǎn)：O(1) 查找
缺點(diǎn)：
  ? 不支持范圍查詢（WHERE age > 20）
  ? 不支持排序（ORDER BY）
  ? 不支持模糊查詢（LIKE 'zhang%'）
  ? 哈希沖突問(wèn)題
  
使用場(chǎng)景：Memory引擎的HASH索引

2. 二叉搜索樹/紅黑樹

缺點(diǎn)：
  ? 樹高太高：100萬(wàn)數(shù)據(jù)，紅黑樹高度約20層 = 20次IO
  ? 每個(gè)節(jié)點(diǎn)只存一個(gè)值，IO利用率低
  ? 不適合磁盤存儲(chǔ)

B+樹高度：100萬(wàn)數(shù)據(jù)，高度約3-4層 = 3-4次IO

3. B樹

缺點(diǎn)：
  ? 非葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，導(dǎo)致每個(gè)節(jié)點(diǎn)存儲(chǔ)的索引項(xiàng)更少
  ? 范圍查詢需要中序遍歷，效率低
  
B+樹優(yōu)勢(shì)：
  ? 非葉子節(jié)點(diǎn)只存索引，每個(gè)節(jié)點(diǎn)可存更多索引項(xiàng)
  ? 葉子節(jié)點(diǎn)用鏈表連接，范圍查詢效率高

2.2 B+樹詳解

B+樹的特點(diǎn)

B+樹結(jié)構(gòu)示例（3階B+樹）：

                [20, 50]               ← 根節(jié)點(diǎn)（只存索引）
              /    |    \
        [10,15]  [30,40]  [60,70]     ← 中間節(jié)點(diǎn)（只存索引）
         /  |  \   /  |  \   /  |  \
       [...] [...] [...] [...] [...] ← 葉子節(jié)點(diǎn)（存數(shù)據(jù)+指針）
         ↔     ↔     ↔     ↔     ↔   ← 雙向鏈表

核心特性：

1. 所有數(shù)據(jù)都在葉子節(jié)點(diǎn)

   • 非葉子節(jié)點(diǎn)只存索引鍵
   • 葉子節(jié)點(diǎn)存完整數(shù)據(jù)（或指針）

2. 葉子節(jié)點(diǎn)形成有序鏈表

   • 支持高效的范圍查詢
   • 支持順序遍歷

3. 高度平衡

   • 所有葉子節(jié)點(diǎn)在同一層
   • 查詢?nèi)我鈹?shù)據(jù)的IO次數(shù)一致

4. 多路查找

   • 每個(gè)節(jié)點(diǎn)可以有多個(gè)子節(jié)點(diǎn)（不是二叉）
   • 減少樹的高度，減少IO次數(shù)

B+樹容量計(jì)算

假設(shè)：

• InnoDB頁(yè)大?。?6KB
• 索引鍵（bigint）：8字節(jié)
• 指針大?。?字節(jié)
• 數(shù)據(jù)行大?。?KB

非葉子節(jié)點(diǎn)：

每個(gè)節(jié)點(diǎn)能存儲(chǔ)的索引項(xiàng)數(shù) = 16KB / (8B + 6B) ≈ 1170個(gè)

3層B+樹能存儲(chǔ)的數(shù)據(jù)量：
  第1層（根）：1個(gè)節(jié)點(diǎn)
  第2層：1170個(gè)節(jié)點(diǎn)
  第3層（葉子）：1170 × 1170 = 1,368,900個(gè)節(jié)點(diǎn)
  
每個(gè)葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)：16KB / 1KB = 16條
總數(shù)據(jù)量：1,368,900 × 16 ≈ 2000萬(wàn)條

結(jié)論：3次IO可以查詢2000萬(wàn)數(shù)據(jù)！

第二部分：索引類型

三、MySQL索引分類

3.1 按數(shù)據(jù)結(jié)構(gòu)分類

• B+樹索引（默認(rèn)）
• Hash索引（Memory引擎）
• 全文索引（FULLTEXT）
• 空間索引（SPATIAL）

3.2 按物理存儲(chǔ)分類

• 聚簇索引（Clustered Index）
• 非聚簇索引（Secondary Index）

3.3 按邏輯功能分類

• 主鍵索引（PRIMARY KEY）
• 唯一索引（UNIQUE）
• 普通索引（INDEX）
• 全文索引（FULLTEXT）

3.4 按字段數(shù)量分類

• 單列索引
• 聯(lián)合索引（復(fù)合索引）

3.5 按功能分類

• 覆蓋索引（Covering Index）
• 前綴索引（Prefix Index）
• 索引下推（ICP）

四、主鍵索引與唯一索引對(duì)比

4.1 核心區(qū)別

關(guān)系：

主鍵 = 唯一索引 + NOT NULL + 聚簇索引(InnoDB) + 只能一個(gè)

4.2 NULL值處理差異

-- 主鍵：不允許NULL
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO t1 VALUES (NULL, '張三');  -- ? 報(bào)錯(cuò)

-- 唯一索引：允許多個(gè)NULL
CREATE TABLE t2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE
);
INSERT INTO t2 (email) VALUES (NULL);  -- ? 成功
INSERT INTO t2 (email) VALUES (NULL);  -- ? 成功（SQL標(biāo)準(zhǔn)：NULL != NULL）
INSERT INTO t2 (email) VALUES ('a@example.com');  -- ? 成功
INSERT INTO t2 (email) VALUES ('a@example.com');  -- ? 報(bào)錯(cuò)（重復(fù)）

4.3 使用場(chǎng)景對(duì)比

-- 主鍵索引：系統(tǒng)標(biāo)識(shí)
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 主鍵：系統(tǒng)唯一標(biāo)識(shí)
    username VARCHAR(50) UNIQUE,           -- 唯一索引：業(yè)務(wù)約束
    email VARCHAR(100) UNIQUE,             -- 唯一索引：業(yè)務(wù)約束
    phone VARCHAR(20) UNIQUE               -- 唯一索引：業(yè)務(wù)約束
);

-- 訂單表：主鍵 + 業(yè)務(wù)唯一號(hào)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 主鍵：內(nèi)部ID
    order_no VARCHAR(32) UNIQUE,           -- 唯一索引：業(yè)務(wù)訂單號(hào)
    idempotent_key VARCHAR(64) UNIQUE      -- 唯一索引：冪等性控制
);

五、聚簇索引與非聚簇索引

5.1 聚簇索引（InnoDB主鍵）

定義：數(shù)據(jù)行和索引存儲(chǔ)在一起，葉子節(jié)點(diǎn)存儲(chǔ)完整數(shù)據(jù)行。

特點(diǎn)：

• ? 一個(gè)表只能有一個(gè)
• ? 主鍵就是聚簇索引
• ? 查詢速度快（無(wú)需回表）
• ? 插入可能導(dǎo)致頁(yè)分裂

結(jié)構(gòu)：

聚簇索引（主鍵索引）：
              [20, 50]
             /    |    \
        [10,15] [30,40] [60,70]
         /  |      |       |
    [完整數(shù)據(jù)行] [完整數(shù)據(jù)行] [完整數(shù)據(jù)行]
    id:10      id:20       id:30
    name:張三   name:李四    name:王五
    age:25     age:30      age:35
    ...        ...         ...

5.2 非聚簇索引（二級(jí)索引）

定義：索引和數(shù)據(jù)分離，葉子節(jié)點(diǎn)存儲(chǔ)主鍵值。

結(jié)構(gòu)：

二級(jí)索引（name索引）：
              [李四, 王五]
             /    |    \
       [張三] [李四,劉備] [王五,趙六]
         |      |           |
    [name:張三  [name:李四   [name:王五
     → id:10]   → id:20]     → id:30]
                    ↓
            回表查詢主鍵索引

回表查詢：

SELECT * FROM users WHERE name = '張三';

執(zhí)行過(guò)程：
1. 在name索引中找到 '張三' → 得到主鍵id=10
2. 回到主鍵索引，通過(guò)id=10找到完整數(shù)據(jù)行
3. 返回結(jié)果

共2次索引查找（2次B+樹遍歷）

5.3 InnoDB vs MyISAM

第三部分：索引失效

六、索引失效的13種場(chǎng)景

6.1 索引列使用函數(shù)或表達(dá)式

原因：破壞索引的有序性。

-- ? 失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;
SELECT * FROM users WHERE age + 1 = 25;

-- ? 正確
SELECT * FROM users 
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM users WHERE age = 24;

6.2 隱式類型轉(zhuǎn)換

-- 假設(shè)phone是VARCHAR
-- ? 失效
SELECT * FROM users WHERE phone = 13800138000;

-- ? 正確
SELECT * FROM users WHERE phone = '13800138000';

規(guī)則：
- 字符串索引 + 數(shù)值查詢 = 失效
- 數(shù)值索引 + 字符串查詢 = 生效

6.3 LIKE前綴模糊

-- ? 失效
SELECT * FROM users WHERE name LIKE '%張三';
SELECT * FROM users WHERE name LIKE '%張三%';

-- ? 生效
SELECT * FROM users WHERE name LIKE '張三%';

6.4 OR條件有非索引列

-- ? 失效
SELECT * FROM users WHERE name = '張三' OR age = 20;  -- age無(wú)索引

-- ? 解決方案
-- 方案1：為age創(chuàng)建索引
-- 方案2：改寫為UNION
SELECT * FROM users WHERE name = '張三'
UNION
SELECT * FROM users WHERE age = 20;

6.5 負(fù)向查詢

-- ? 通常失效
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- ? 改寫為正向
SELECT * FROM users WHERE status IN (0, 2, 3, 4);

6.6 違反最左前綴原則

-- 假設(shè)索引：INDEX(name, age, city)

-- ? 走索引
WHERE name = '張三'
WHERE name = '張三' AND age = 20
WHERE name = '張三' AND age = 20 AND city = '北京'

-- ? 不走索引
WHERE age = 20
WHERE city = '北京'
WHERE age = 20 AND city = '北京'

6.7 范圍查詢后的列失效

-- 假設(shè)索引：INDEX(name, age, city)
SELECT * FROM users 
WHERE name = '張三' AND age > 20 AND city = '北京';
-- name和age走索引，city不走（age是范圍查詢）

6.8-6.13 其他失效場(chǎng)景

詳見完整表格（篇幅原因省略，包括IS NULL、區(qū)分度低、結(jié)果集過(guò)大、IN過(guò)多、字符集不一致等）

七、如何診斷索引問(wèn)題

7.1 使用EXPLAIN

EXPLAIN SELECT * FROM users WHERE name = '張三';

關(guān)鍵字段：
- type: ALL（最差）< index < range < ref < const（最優(yōu)）
- key: 實(shí)際使用的索引（NULL表示未使用）
- rows: 掃描行數(shù)（越少越好）
- Extra: 
  - Using index（覆蓋索引，最優(yōu)）
  - Using filesort（需要排序優(yōu)化）
  - Using temporary（需要臨時(shí)表優(yōu)化）

7.2 查看索引統(tǒng)計(jì)

-- 查看索引信息
SHOW INDEX FROM users;

-- 更新統(tǒng)計(jì)信息
ANALYZE TABLE users;

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

第四部分：索引優(yōu)化

八、索引設(shè)計(jì)原則

8.1 什么時(shí)候建索引

? 應(yīng)該建索引：

• WHERE條件列
• ORDER BY排序列
• GROUP BY分組列
• JOIN連接列
• 高頻查詢列
• 區(qū)分度高的列（>0.1）

? 不應(yīng)該建索引：

• 區(qū)分度低的列（如性別）
• 頻繁更新的列
• 大字段（TEXT、BLOB）
• 很少使用的列
• 小表（<1000行）

8.2 索引設(shè)計(jì)6大原則

1. 選擇性原則：高區(qū)分度列優(yōu)先
2. 最左前綴原則：聯(lián)合索引按順序使用
3. 覆蓋索引原則：包含查詢所需列
4. 索引順序原則：等值>范圍、高頻>低頻
5. 避免冗余原則：刪除重復(fù)索引
6. 主鍵設(shè)計(jì)原則：自增整型優(yōu)先

8.3 聯(lián)合索引設(shè)計(jì)

-- 查詢需求：WHERE status = ? AND user_id = ? ORDER BY create_time

-- ? 推薦
CREATE INDEX idx_status_user_create 
ON orders(status, user_id, create_time);

-- 理由：
-- 1. status和user_id是等值查詢，放前面
-- 2. create_time是排序列，放后面（避免filesort）
-- 3. 可以覆蓋索引（如果只查這些列）

九、索引優(yōu)化實(shí)戰(zhàn)

9.1 案例1：函數(shù)導(dǎo)致索引失效

-- ? 問(wèn)題SQL（1.5秒）
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';

-- EXPLAIN: type=ALL, rows=1000000

-- ? 優(yōu)化后（0.01秒）
SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- EXPLAIN: type=range, rows=1000, 提升150倍

9.2 案例2：覆蓋索引優(yōu)化

-- ? 問(wèn)題SQL（10ms，需要回表）
SELECT user_id, order_no, amount, status 
FROM orders 
WHERE user_id = 12345;

-- ? 建立覆蓋索引
CREATE INDEX idx_user_order_amount_status 
ON orders(user_id, order_no, amount, status);

-- 優(yōu)化后（2ms，無(wú)需回表），提升5倍

9.3 案例3：深度分頁(yè)優(yōu)化

-- ? 慢（5秒）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ? 快（0.5秒）- 子查詢
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;

-- ? 最快（0.01秒）- 游標(biāo)分頁(yè)
SELECT * FROM orders 
WHERE id > 1000000  -- 上次最后一條ID
ORDER BY id LIMIT 10;

第五部分：面試必備

十、高頻面試問(wèn)題

Q1：什么是索引？作用是什么？

索引是一種數(shù)據(jù)結(jié)構(gòu)（通常是B+樹），用于幫助數(shù)據(jù)庫(kù)高效查找數(shù)據(jù)。

作用：

1. 大幅提升查詢速度（O(n) → O(log n)）
2. 加速排序和分組
3. 避免全表掃描
4. 保證數(shù)據(jù)唯一性

Q2：為什么使用B+樹？

B+樹 vs 紅黑樹：

• 紅黑樹：20層 = 20次IO
• B+樹：3-4層 = 3-4次IO

B+樹 vs B樹：

• B+樹葉子節(jié)點(diǎn)有序鏈表，范圍查詢更快

Q3：聚簇索引和非聚簇索引的區(qū)別？

Q4：主鍵索引和唯一索引的區(qū)別？

1. NULL值：主鍵不允許，唯一索引允許
2. 數(shù)量：主鍵1個(gè)，唯一索引多個(gè)
3. 類型：主鍵是聚簇索引，唯一索引是二級(jí)索引
4. 性能：主鍵查詢更快（無(wú)需回表）

Q5：什么是覆蓋索引？

查詢的所有列都在索引中，無(wú)需回表查詢。

優(yōu)勢(shì)：減少IO、提升速度、減少鎖競(jìng)爭(zhēng)

-- 假設(shè)索引：INDEX(name, age)

-- ? 覆蓋索引
SELECT name, age FROM users WHERE name = '張三';
-- Extra: Using index

-- ? 非覆蓋索引
SELECT * FROM users WHERE name = '張三';
-- 需要回表

Q6：聯(lián)合索引的最左前綴原則？

聯(lián)合索引INDEX(a, b, c)相當(dāng)于創(chuàng)建了(a)、(a,b)、(a,b,c)三個(gè)索引。

查詢必須包含最左列才能使用索引。

Q7：什么情況下索引會(huì)失效？

1. 索引列使用函數(shù)
2. 隱式類型轉(zhuǎn)換
3. LIKE前綴模糊
4. OR條件有非索引列
5. 負(fù)向查詢
6. 違反最左前綴
7. 查詢結(jié)果集過(guò)大

Q8：如何設(shè)計(jì)高效索引？

設(shè)計(jì)原則：

1. 選擇性原則：高區(qū)分度列（>0.1）
2. 最左前綴原則：合理安排順序
3. 覆蓋索引原則：包含查詢列
4. 索引順序：等值>范圍、高頻>低頻

Q9：索引越多越好嗎？

不是！索引有代價(jià)：

• ? 占用存儲(chǔ)空間
• ? 降低寫入性能
• ? 增加維護(hù)成本

建議：

• 單表索引不超過(guò)5個(gè)
• 定期刪除未使用的索引

Q10：主鍵為什么推薦自增整型？

折中方案：雪花算法（BIGINT，有序）

總結(jié)

索引核心知識(shí)圖譜

索引本質(zhì)：空間換時(shí)間的數(shù)據(jù)結(jié)構(gòu)
    ↓
底層結(jié)構(gòu)：B+樹（多路平衡查找樹）
    ↓
索引分類：
├─ 主鍵索引（聚簇索引，最快）
├─ 唯一索引（二級(jí)索引，較快）
├─ 普通索引（二級(jí)索引，常用）
└─ 特殊索引（全文、空間）
    ↓
優(yōu)化要點(diǎn)：
├─ 避免失效（13種場(chǎng)景）
├─ 合理設(shè)計(jì)（6大原則）
├─ 覆蓋索引（減少回表）
└─ 定期維護(hù)（ANALYZE、OPTIMIZE）
    ↓
性能提升：O(n) → O(log n)

索引使用口訣

索引設(shè)計(jì)三原則：選擇性、有序性、覆蓋性
索引失效三大忌：函數(shù)、類型、通配符
索引優(yōu)化三步走：EXPLAIN、分析、重構(gòu)

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)索引的文章就介紹到這了,更多相關(guān)MySQL索引指南內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論