┌──────────┐
│ 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. 所有数据在叶子节点 → 查询路径长度相同 → 性能稳定
假设:
- 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!
| 数据结构 | 磁盘I/O | 范围查询 | 树高(千万级) | 问题 |
|---|---|---|---|---|
| B+树 | 3-4次 | 极快(链表遍历) | 3-4层 | 最优选择 |
| B树 | 更多 | 慢(中序遍历) | 5-6层 | 非叶节点存数据,页少树高 |
| 红黑树 | 极多 | 慢 | 20+层 | 二叉树,千万数据太高 |
| Hash | 1次 | 不支持 | - | 不支持范围/排序 |
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条件顺序,不影响命中
联合索引 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标志
| # | 场景 | 错误示例 | 正确做法 |
|---|---|---|---|
| 1 | 隐式类型转换 | WHERE varchar_col = 123 | WHERE varchar_col = '123' |
| 2 | 函数计算 | WHERE YEAR(create_time) = 2026 | WHERE create_time >= '2026-01-01' |
| 3 | LIKE前缀通配 | WHERE name LIKE '%张' | WHERE name LIKE '张%' 或用ES |
| 4 | OR含非索引列 | WHERE a=1 OR b=2(b无索引) | 给b加索引 |
| 5 | 违背最左前缀 | 联合索引(a,b)查WHERE b=2 | 包含a列 |
| 6 | 负向查询 | WHERE status != 1 | WHERE status IN (2,3,4) |
| 7 | IS NOT NULL | WHERE col IS NOT NULL | 用默认值替代NULL |
| 8 | 计算表达式 | WHERE id + 1 = 10 | WHERE id = 9 |
| 9 | 优化器放弃 | 表小/数据分布不均 | FORCE INDEX(idx_name) |
| 10 | ORDER BY非索引列 | 无索引列排序→filesort | 加索引或改写SQL |
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_ref | JOIN时主键/唯一索引 | 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的索引下推能在存储引擎层用索引列提前过滤,减少回表次数。索引失效最常见的是隐式类型转换和函数计算。"
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内部是怎么处理的?