02.3 - 索引优化策略
定位：索引不是建得越多越好，理解联合索引、最左前缀、索引下推才能设计真正高效的索引 面试高频度：⭐⭐⭐⭐⭐ 考查方式：联合索引设计、最左前缀原则题目、索引下推（ICP）原理、MRR 优化
一、这是什么？为什么需要它？
索引优化的本质：用尽量少的索引覆盖尽量多的查询模式。
索引不是免费的——每个索引在 INSERT/UPDATE/DELETE 时都需要维护：
- 表有 N 个索引，每次写入要维护 N+1 棵 B+树（1 个聚簇索引 + N 个二级索引）
- 索引占用磁盘空间和 Buffer Pool 内存
核心 insight：索引优化是"空间换时间＋维护成本"的权衡。少建索引，但让每个索引都能覆盖多个查询模式。
二、原理拆解
2.1 联合索引（复合索引）
sql
-- 创建联合索引: (a, b, c)
ALTER TABLE t ADD INDEX idx_a_b_c (a, b, c);联合索引的物理结构：一个 B+树，key 是 (a, b, c) 的拼接。
B+树叶子节点内容:
┌──────────┬──────────┬──────────┐
│ a │ b │ c │ ← 索引列
├──────────┼──────────┼──────────┤
│ 1 │ 10 │ 100 │
│ 1 │ 10 │ 200 │
│ 1 │ 20 │ 100 │
│ 2 │ 10 │ 300 │ ← 先按 a 排序
│ 2 │ 10 │ 400 │ 再按 b 排序
│ 2 │ 20 │ 100 │ 再按 c 排序
│ 3 │ 5 │ 500 │
└──────────┴──────────┴──────────┘关键：联合索引的排序规则是从左到右——先按第一个列排序，相同再按第二列，以此类推。这就是最左前缀原则的底层原因。
2.2 最左前缀原则（Leftmost Prefix）
联合索引 (a, b, c)
能用到索引的查询： 能否利用排序：
✅ WHERE a = 1 部分（只剩 a 确定）
✅ WHERE a = 1 AND b = 2 全部
✅ WHERE a = 1 AND b = 2 AND c = 3 全部
✅ WHERE a IN (1,2) AND b = 2 部分（a 范围查）
✅ WHERE a = 1 ORDER BY b 全部（a 确定，b 有序）
✅ WHERE a = 1 AND b > 10 部分（b 范围后停止）
❌ 不能用到索引的查询：
❌ WHERE b = 2 ← 跳过了第一列
❌ WHERE c = 3 ← 跳过了前两列
❌ WHERE b = 2 AND c = 3 ← 没有 a
⚠️ 部分用到索引的查询：
WHERE a > 1 AND b = 2 ← a 用到了索引（范围），b 不能利用（因为 a 是范围）
Extra: Using index condition为什么跳跃第一列就失效？
没有 a=1 的限制，b 是无法排序的：
(1,10) (2,5) (1,20) ← 按 b 排序时，需要知道 a 才能确定顺序
(2,10) (3,5) ...由于 B+树先按 a 排序，再按 b 排序——如果 a 不确定，b 的有序性没有意义。
2.3 索引下推（Index Condition Pushdown, ICP）
MySQL 5.6+ 的优化，减少回表次数。
没有 ICP 时：
sql
SELECT * FROM users
WHERE name LIKE '张%' AND age = 25
-- 联合索引: (name, age)1. 二级索引找到 name LIKE '张%' 的所有行
↓
2. 回表获取完整行 → 再过滤 age = 25 ← age 条件在这里才检查
↓
3. 返回最终结果问题：即使 age=25 可以快速判定，也要先回表再过滤。
有 ICP 时：
1. 二级索引找到 name LIKE '张%' 的所有行
↓
2. 在二级索引层直接检查 age = 25 ← ICP：在引擎层过滤
↓
3. 只有 age=25 的行才回表
↓
4. 返回最终结果ICP 的收益量化：
| 场景 | 无 ICP | 有 ICP |
|---|---|---|
| name LIKE '张%' 匹配 1000 行 | 回表 1000 次 | 回表 50 次 |
| 其中 age=25 只有 50 行 | 期间检查 age 条件 | 引擎层提前过滤 |
| 额外 IO | 950 次随机读 | 0 次 |
如何确认 ICP 生效：
sql
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- Extra: Using index condition ← ICP 生效的标志ICP 的限制：
- 只适用于二级索引（聚簇索引不需要回表）
- 对于覆盖索引查询，ICP 没有额外收益（因为已经不用回表了）
- push 下去的条件必须是索引中包含的列
2.4 MRR（Multi-Range Read）
MySQL 5.6+ 的优化，解决回表随机 IO 问题。
没有 MRR：
二级索引找到主键值: [42, 17, 89, 3, 55, ...] ← 无序
↓
回表: 查 id=42 → id=17 → id=89 → id=3 ... ← 随机 IO
↓
每次回表都是随机位置，Buffer Pool 无法预读有 MRR：
二级索引找到主键值: [42, 17, 89, 3, 55, ...]
↓
MRR: 在内存中排序 → [3, 17, 42, 55, 89, ...]
↓
按主键顺序回表: id=3 → id=17 → id=42 ... ← 顺序 IO
↓
聚簇索引按主键排序，顺序回表可以预读、磁盘 IO 更少MRR 适用场景：
- 二级索引范围查询（
rangetype） - 需要回表且回表行数较多
如何确认 MRR 生效：
sql
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- Extra: Using index condition; Using MRR ← MRR 生效2.5 索引设计策略
索引设计决策树
这个查询需要索引吗？
│
├── 查询频率低（月/季度报表）→ 不建，全表扫描就行
│
├── 查询频率高但数据量小（<1000行）→ 不建，全表扫描更快
│
├── 查询频率高 + 数据量大
│ │
│ ├── 核心查询（WHERE 条件固定）
│ │ └── 建联合索引覆盖所有过滤条件
│ │
│ ├── 排序多（ORDER BY）
│ │ └── 索引包含排序列（减少 filesort）
│ │
│ └── 查询列少（只需要几个字段）
│ └── 建覆盖索引（Using index）
│
└── 写入频繁的表 → 索引数量要精简索引设计十大原则
| # | 原则 | 说明 |
|---|---|---|
| 1 | 区分度高的列放联合索引左侧 | 选择性 = COUNT(DISTINCT col) / COUNT(*)，越高越好 |
| 2 | 联合索引把等值查询的列放前面 | WHERE a=1 AND b>10 → index(a,b) 比 index(b,a) 好 |
| 3 | 索引覆盖常用查询 | 避免回表的 SELECT 查询 |
| 4 | 避免在索引列上使用函数 | WHERE DATE(create_time) = '2024-01-01' → 无法用索引 |
| 5 | LIKE 前面的 % 会让索引失效 | LIKE '%abc' 无法使用索引 |
| 6 | 用 IN 代替 OR | WHERE a IN (1,2,3) 可以用索引，WHERE a=1 OR a=2 可能不走 |
| 7 | 不要建太多索引 | 一般单表不超过 5-6 个索引，写入多的表更少 |
| 8 | 小表不需要索引 | 全表扫描比索引查找更快（不需要额外 IO） |
| 9 | 定期维护索引 | OPTIMIZE TABLE 减少索引碎片 |
| 10 | 冗余索引定期清理 | pt-duplicate-key-checker 检测冗余索引 |
三、图解全景
索引优化策略全景
┌──────────────────────────────────────┐
│ 查询进入 │
└────────────────┬─────────────────────┘
│
┌────┴────┐
│ 能否用索引 │
└────┬────┘
│
┌──────────┴──────────┐
│ │ │
▼ ▼ ▼
┌────────┐ ┌────────┐ ┌────────┐
│全表扫描 │ │全索引扫描│ │索引范围 │
│(不用) │ │(type=I)│ │(range) │
└────────┘ └────────┘ └────────┘
│
▼
┌────────────────┐
│ 二级索引查找？ │
└───────┬────────┘
│
┌─────────────┴─────────────┐
│ │
▼ ▼
┌──────────────┐ ┌─────────────────┐
│ 覆盖索引 │ │ ICP（索引下推） │
│ Extra: │ │ Extra: Using │
│ Using index │ │ index condition │
└──────────────┘ └────────┬────────┘
│
▼
┌─────────────────┐
│ 需要回表？ │
└────────┬────────┘
│
┌──────────┴──────────┐
│ │
▼ ▼
┌──────────────┐ ┌─────────────────┐
│ MRR 排序回表 │ │ 直接回表 │
│ Extra: MRR │ │ (随机 IO) │
└──────────────┘ └─────────────────┘四、实战验证
验证最左前缀
sql
-- 创建测试表
CREATE TABLE test_prefix (
a INT, b INT, c INT, d VARCHAR(100),
INDEX idx_a_b_c (a, b, c)
);
-- 场景 1: 全部匹配
EXPLAIN SELECT * FROM test_prefix WHERE a=1 AND b=2 AND c=3;
-- key_len: 12 (3个INT × 4字节) → 全部命中
-- 场景 2: 只有前两列
EXPLAIN SELECT * FROM test_prefix WHERE a=1 AND b=2;
-- key_len: 8 (2个INT) → 使用了前两列
-- 场景 3: 跳过中间列
EXPLAIN SELECT * FROM test_prefix WHERE a=1 AND c=3;
-- key_len: 4 (只用了 a) → a 用索引，c 没用到索引
-- Extra: Using index condition
-- 场景 4: 范围查询
EXPLAIN SELECT * FROM test_prefix WHERE a=1 AND b>10 AND c=3;
-- key_len: 8 (a=精确使用 + b=范围使用) → c 没用到索引
-- Extra: Using index condition验证 ICP 效果
sql
-- 需要打开 optimizer_trace
SET optimizer_trace='enabled=on';
EXPLAIN SELECT * FROM test_prefix WHERE a=1 AND b>10 AND c=3;
-- Extra: Using index condition → ICP 生效
-- 查看优化器详细信息
SELECT * FROM information_schema.OPTIMIZER_TRACE\G检查冗余索引
sql
-- 查找可能的冗余索引
SELECT
a.TABLE_SCHEMA, a.TABLE_NAME,
a.INDEX_NAME AS redundant_index,
b.INDEX_NAME AS covered_by
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.INDEX_NAME != b.INDEX_NAME
AND (a.SEQ_IN_INDEX = (SELECT MAX(SEQ_IN_INDEX)
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = a.TABLE_SCHEMA
AND TABLE_NAME = a.TABLE_NAME
AND INDEX_NAME = a.INDEX_NAME))
GROUP BY a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, b.INDEX_NAME;五、面试视角
| 追问 | 答案要点 |
|---|---|
| 最左前缀原则是什么？ | 联合索引 (a,b,c)，只有从第一列开始的连续前缀才能用到索引。因为 B+树按从左到右顺序排序 |
| 什么是索引下推（ICP）？ | 在引擎层用索引过滤数据，减少回表次数。Extra: Using index condition |
| ICP 什么时候不生效？ | 覆盖索引查询（已经不需要回表）、聚簇索引查询 |
| MRR 解决了什么问题？ | 减少回表时的随机 IO——把主键值排序后顺序回表 |
| 一张表建多少索引合适？ | OLTP 一般不超过 5-6 个。每个索引都增加写入开销 |
| WHERE a=1 AND b>10 AND c=3，联合索引 (a,b,c) 能用到全部吗？ | a 和 b 能用到（精确+范围），c 用不到（b 范围后停止匹配） |
| 如何判断有没有用到索引？ | EXPLAIN 看 type 和 key 字段，Extra 看 Using index / Using index condition |
📚 相关链接
- 前置基础：**B+树索引结构**
- 索引物理：**聚簇索引与二级索引**
- 分析工具：**EXPLAIN与查询优化**
- ← 返回 **索引与查询优化索引**