← 返回首页 📚 实战课程
📑 本篇章节导航 (共5章,点击直达)
本节目标(学完能回答的面试题)核心知识(1小时精讲内容)面试速记卡(考前30秒速看)练习题(自测)学习资料

L16 - MySQL索引深度解析

本节目标(学完能回答的面试题)

核心知识(1小时精讲内容)

一、B+树原理

1.1 B+树结构


                        ┌──────────┐
                        │  Root    │
                        │ [10|20|30]│  ← 非叶子节点只存索引键
                        └────┬─────┘
              ┌──────────┬───┴───┬──────────┐
              ▼          ▼       ▼          ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
        │ [1|3|5]  │ │[11|13|15]│ │[21|23|25]│ │[31|33|35]│
        └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘
             ▼            ▼            ▼            ▼
   ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐
   │[1,张三,..]  │ │[11,李四,..] │ │[21,王五,..] │ │[31,赵六,..] │
   │[3,钱七,..]  │ │[13,孙八,..] │ │[23,周九,..] │ │[33,吴十,..] │
   │[5,郑一,..]  │ │[15,冯二,..] │ │[25,陈三,..] │ │[35,褚四,..] │
   └───┬───↔───┘ └───┬───↔───┘ └───┬───↔───┘ └───┬───↔───┘
       │             │             │             │
       ←────────── 双向链表连接 ──────────────→

特点:
1. 非叶子节点只存键值,不存数据 → 单页存更多键 → 树更矮
2. 叶子节点存完整数据,用双向链表连接 → 范围查询极快
3. 所有数据在叶子节点 → 查询路径长度相同 → 性能稳定

1.2 三层B+树存2000万行的计算


假设:
- InnoDB页大小: 16KB
- 主键BIGINT: 8字节 + 指针6字节 = 14字节/索引项
- 一行数据约1KB

非叶子节点:
  每页可存: 16KB / 14B ≈ 1170个索引项

叶子节点:
  每页可存: 16KB / 1KB ≈ 16行

三层B+树:
  第1层(根): 1个页 = 1170个索引项
  第2层:     1170个页 × 1170 = 1,368,900个索引项
  第3层(叶): 1,368,900 × 16 = 21,902,400行 ≈ 2000万行

结论: 三层B+树存2000万行数据,每次查询只需3次磁盘I/O!

1.3 为什么不用其他数据结构?

数据结构磁盘I/O范围查询树高(千万级)问题
B+树3-4次极快(链表遍历)3-4层最优选择
B树更多慢(中序遍历)5-6层非叶节点存数据,页少树高
红黑树极多慢20+层二叉树,千万数据太高
Hash1次不支持-不支持范围/排序

二、聚簇索引 vs 二级索引


InnoDB索引结构:

聚簇索引(主键索引):
  B+树叶子节点存 → 完整行数据
  一张表只有一个聚簇索引(默认主键)
  无主键 → 选唯一非空索引 → 隐式生成ROW_ID

二级索引(非主键索引):
  B+树叶子节点存 → 主键值(不是完整数据)
  查非索引列 → 需要回表

示例: 表 user(id PK, name, age, INDEX idx_age(age))

聚簇索引(id):
  叶子: [1,'张三',25] [2,'李四',30] [3,'王五',25]

二级索引(age):
  叶子: [25,→1] [25,→3] [30,→2]  ← 存的是(age, 主键id)

三、回表


SELECT * FROM user WHERE age = 25;

执行流程:
┌───────────┐     ┌───────────┐     ┌───────────┐
│ 1.查二级  │────→│ 2.回聚簇   │────→│ 3.返回    │
│ 索引idx_age│     │ 索引查完整 │     │ 完整行数据 │
│ 找到age=25│     │ 数据       │     │           │
│ → [id=1]  │     │ id=1 →    │     │ [1,'张三'] │
│ → [id=3]  │     │ id=3 →    │     │ [3,'王五'] │
└───────────┘     └───────────┘     └───────────┘

回表代价: 二级索引查到N条 → 回聚簇索引N次
每次回表都是一次B+树查找(3-4次I/O)

四、覆盖索引(避免回表)


-- 需要回表(查name,不在idx_age索引中)
SELECT * FROM user WHERE age = 25;
-- Extra: NULL

-- 覆盖索引(id和age都在idx_age索引中)
SELECT id, age FROM user WHERE age = 25;
-- Extra: Using index ← 不需要回表!

-- 联合索引做覆盖索引
CREATE INDEX idx_name_age ON user(name, age);
SELECT id, name, age FROM user WHERE name = '张三';
-- Extra: Using index

-- 企业规范: 避免 SELECT *,只查需要的字段

五、最左前缀原则


联合索引: idx_abc(a, b, c)
B+树排序: 先按a排 → a相同按b排 → b相同按c排

可以命中索引:
WHERE a = 1                          ✅ 命中a
WHERE a = 1 AND b = 2               ✅ 命中a,b
WHERE a = 1 AND b = 2 AND c = 3     ✅ 命中a,b,c
WHERE a = 1 AND c = 3               ✅ 命中a (c无法利用)
WHERE b = 2 AND c = 3               ❌ 缺少最左列a
WHERE a = 1 AND b > 2 AND c = 3     ✅ 命中a,b (c被范围查询阻断)

关键规则:
1. 联合索引从最左列开始匹配
2. 跳过某列 → 后续列无法使用索引
3. 范围查询(>/</BETWEEN/LIKE)后的列无法利用索引
4. MySQL优化器可能调整WHERE条件顺序,不影响命中

六、索引下推 ICP (Index Condition Pushdown, MySQL 5.6+)


联合索引 idx_abc(a, b, c)
查询: WHERE a = 1 AND c = 3

没有ICP(MySQL 5.6之前):
┌──────────────┐         ┌──────────────┐
│  存储引擎     │         │  Server层    │
│ 用索引查a=1  │──N条──→ │ 逐行回表     │
│ 返回所有a=1  │         │ 再判断c=3    │
│ 的主键       │         │ 回表N次      │
└──────────────┘         └──────────────┘

有ICP(MySQL 5.6+):
┌──────────────┐         ┌──────────────┐
│  存储引擎     │         │  Server层    │
│ 用索引查a=1  │         │              │
│ 在索引中判断  │──M条──→ │ 只回表M次    │
│ c=3(索引有c) │  M<<N   │ M<<N         │
│ 只返回满足的  │         │              │
└──────────────┘         └──────────────┘

EXPLAIN Extra: Using index condition ← ICP标志

七、索引失效10大场景

#场景错误示例正确做法
1隐式类型转换WHERE varchar_col = 123WHERE varchar_col = '123'
2函数计算WHERE YEAR(create_time) = 2026WHERE create_time >= '2026-01-01'
3LIKE前缀通配WHERE name LIKE '%张'WHERE name LIKE '张%' 或用ES
4OR含非索引列WHERE a=1 OR b=2(b无索引)给b加索引
5违背最左前缀联合索引(a,b)查WHERE b=2包含a列
6负向查询WHERE status != 1WHERE status IN (2,3,4)
7IS NOT NULLWHERE col IS NOT NULL用默认值替代NULL
8计算表达式WHERE id + 1 = 10WHERE id = 9
9优化器放弃表小/数据分布不均FORCE INDEX(idx_name)
10ORDER BY非索引列无索引列排序→filesort加索引或改写SQL

八、EXPLAIN执行计划解读


EXPLAIN SELECT * FROM user WHERE age = 25\G

关键字段:
┌──────────┬────────────────────────────────────────────────┐
│ 字段     │ 含义与关注点                                    │
├──────────┼────────────────────────────────────────────────┤
│ type     │ 访问类型(从好到差):                              │
│          │ system > const > eq_ref > ref > range          │
│          │ > index > ALL(全表扫描,必须优化)                │
├──────────┼────────────────────────────────────────────────┤
│ key      │ 实际使用的索引(NULL=未走索引)                    │
├──────────┼────────────────────────────────────────────────┤
│ rows     │ 预估扫描行数(越少越好)                          │
├──────────┼────────────────────────────────────────────────┤
│ Extra    │ 额外信息:                                       │
│          │ Using index(覆盖索引,好)                       │
│          │ Using index condition(ICP,好)                  │
│          │ Using where(Server层过滤,一般)                  │
│          │ Using filesort(额外排序,差)                     │
│          │ Using temporary(临时表,差)                      │
└──────────┴────────────────────────────────────────────────┘

type字段详解:

type含义SQL示例级别
system表中只有一行系统表最优
const主键/唯一索引等值查询WHERE id = 1极优
eq_refJOIN时主键/唯一索引JOIN ON a.id = b.id优
ref非唯一索引等值查询WHERE name = '张三'良
range索引范围扫描WHERE age > 20中
index全索引扫描覆盖索引无条件一般
ALL全表扫描无索引差,必须优化

九、企业级优化案例


慢SQL: 3秒
SELECT * FROM order WHERE user_id = ? AND status = ?
ORDER BY create_time DESC LIMIT 20

EXPLAIN:
type=ALL, rows=13000000, Extra=Using where; Using filesort

问题分析:
1. type=ALL → 全表扫描
2. Using filesort → 额外排序
3. rows=1300万 → 扫描太多

优化方案:
CREATE INDEX idx_user_status_time ON `order`(user_id, status, create_time);

优化后: 50ms
EXPLAIN:
type=ref, rows=500, Extra=Using index condition

分析:
- user_id等值 → ref类型
- status等值 → 继续走索引
- create_time排序 → 索引已有序,无需filesort
- 完美遵循最左前缀: 等值→等值→排序

面试话术: "MySQL用B+树做索引是因为非叶子节点只存键值,一个16KB页能存约1170个索引项,三层B+树就能存2000万行数据,只需3次I/O。二级索引查非索引列需要回表,优化方案是覆盖索引,EXPLAIN显示Using index。联合索引遵循最左前缀原则,范围查询会阻断后续列。MySQL 5.6的索引下推能在存储引擎层用索引列提前过滤,减少回表次数。索引失效最常见的是隐式类型转换和函数计算。"

面试速记卡(考前30秒速看)

练习题(自测)

1. 请手算: 如果一行数据是500字节,非叶节点索引项是20字节,一个三层B+树最多能存多少行?

2. SELECT id, name FROM user WHERE name = '张三',如果有联合索引idx_name_age(name, age),会不会回表?为什么?

3. 联合索引(a, b, c),查询WHERE a = 1 AND b > 2 AND c = 3,哪些列能用到索引?为什么c用不到?

4. 索引下推(ICP)和覆盖索引(Using index)有什么区别?分别在什么场景下生效?

5. WHERE varchar_col = 123为什么会导致索引失效?MySQL内部是怎么处理的?

学习资料