索引原理与优化
问题
MySQL 索引的底层数据结构是什么？聚簇索引和非聚簇索引有什么区别？什么情况下索引会失效？
答案
一、InnoDB 索引结构 — B+ 树
InnoDB 所有索引都基于 B+ 树 实现。一棵典型的 B+ 树：
核心特点：
- 非叶子节点 只存 key，不存数据 → 每个页能存更多 key → 树更矮
- 叶子节点存 完整数据，且通过 双向链表 连接 → 范围查询高效
- 树高通常 3~4 层，即千万级数据只需 3~4 次磁盘 IO
为什么 3 层就够？
InnoDB 的数据页大小为 16KB。假设主键为 bigint（8B），指针 6B，则非叶子节点每页存 16KB / 14B ≈ 1170 个 key。
- 2 层：1170 × 16 个叶子 ≈ 18,000 行
- 3 层：1170 × 1170 × 16 ≈ 2 千万行
- 4 层：≈ 200 亿行
绝大多数表 3 层 B+ 树就够了，也就是 3 次 IO 即可定位任意一行。
二、聚簇索引 vs 非聚簇索引
这是 InnoDB 索引体系中 最核心的概念：
聚簇索引（主键索引）
- 叶子节点存储 完整数据行
- 每张表 有且只有一棵 聚簇索引
- 数据按主键顺序物理存储
主键选择规则：
- 有显式 PRIMARY KEY → 用它
- 没有 → 找第一个所有列都 NOT NULL 的 UNIQUE 索引
- 都没有 → InnoDB 自动生成一个 6 字节的隐藏 row_id
非聚簇索引（二级索引 / 辅助索引）
- 叶子节点存储 索引列的值 + 主键值
- 不存储完整数据行
回表查询
通过二级索引查询时，如果需要的列不在索引中：
-- 走 idx_name 索引
SELECT * FROM users WHERE name = '张三';
1. 在 idx_name 索引中找到 name='张三' → 得到 id=5
2. 用 id=5 去聚簇索引中查找完整数据行 ← 这就是"回表"
回表的代价：多一次 B+ 树查找，增加 IO 次数。
覆盖索引 — 避免回表
如果查询的列 都包含在索引中，则不需要回表：
-- 联合索引 idx_name_age(name, age)
SELECT name, age FROM users WHERE name = '张三';
-- 无需回表，索引中已包含 name 和 age（+ 隐含的 id）
-- EXPLAIN 的 Extra 列会显示 "Using index"
覆盖索引是重要优化手段
在设计索引时，尽量让高频查询可以走覆盖索引，避免回表。这是 SQL 优化中投入产出比最高的手段之一。
三、联合索引与最左前缀原则
联合索引 (a, b, c) 的 B+ 树按 先 a 排序、a 相同按 b 排序、b 相同按 c 排序 的方式组织：
(1, 1, 1) → (1, 1, 2) → (1, 2, 1) → (2, 1, 1) → (2, 1, 3) → (2, 2, 2)
最左前缀原则
| 查询条件 | 是否走索引 (a, 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 b = 2 | ❌ 不走索引 | 缺少最左列 a |
WHERE a = 1 AND c = 3 | ✅ 用到 a | c 无法使用（b 中断了） |
WHERE a > 1 AND b = 2 | ✅ 用到 a | 范围查询后面的列无法使用 |
WHERE a = 1 ORDER BY b | ✅ 用到 a, b | b 可用于排序，避免 filesort |
范围查询会中断联合索引
>, <, BETWEEN, LIKE 'abc%' 等范围条件 之后的列不能使用索引。
设计联合索引时：等值条件的列放前面，范围条件的列放后面。
四、索引失效的常见场景
| 场景 | 示例 | 原因 |
|---|---|---|
| 对索引列使用函数 | WHERE YEAR(create_time) = 2024 | 函数破坏了 B+ 树的有序性 |
| 隐式类型转换 | WHERE phone = 13800138000（phone 是 varchar） | MySQL 将 varchar 转为数字比较，等同于对列加了函数 |
| LIKE 左模糊 | WHERE name LIKE '%三' | 无法利用 B+ 树的前缀有序性 |
| OR 条件未全覆盖 | WHERE a = 1 OR b = 2（b 无索引） | OR 的任一条件无索引则全表扫描 |
| NOT IN / NOT EXISTS | 部分情况 | 优化器可能选择全表扫描 |
| 数据量小 | 表只有几十行 | 优化器认为全表扫描更快 |
| 索引选择性低 | 性别列（只有男/女） | 区分度太低，不如全表扫描 |
-- ❌ 索引失效：对列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
-- ✅ 改写：用范围查询
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
五、索引设计原则
| 原则 | 说明 |
|---|---|
| 选择性高的列优先 | COUNT(DISTINCT col) / COUNT(*) 越接近 1 越好 |
| 频繁查询的列建索引 | WHERE、JOIN、ORDER BY 中的列 |
| 尽量用联合索引 | 一个联合索引 > 多个单列索引 |
| 避免冗余索引 | (a, b) 已包含 (a) 的功能，不需要额外建 (a) |
| 控制索引数量 | 索引占空间，且 INSERT/UPDATE/DELETE 需要维护索引 |
| 短索引优先 | 前缀索引：INDEX idx_email(email(10)) |
常见面试问题
Q1: 为什么推荐用自增主键？
答案：
- 插入效率高：自增主键保证新数据总是追加到 B+ 树的最后，不会引起页分裂
- 存储紧凑：int/bigint 占 4/8 字节，比 UUID（36 字节字符串）小得多
- 二级索引更小：每个二级索引叶子节点都存主键值，主键越短二级索引越小
UUID 做主键的问题：
- 无序插入导致频繁页分裂
- 占空间大（36 字节 vs 8 字节）
- 索引效率低
Q2: 什么是索引下推（Index Condition Pushdown）？
答案：
MySQL 5.6 引入的优化。以联合索引 (name, age) 为例：
SELECT * FROM users WHERE name LIKE '张%' AND age = 20;
- 无 ICP：用
name LIKE '张%'在索引中找到所有姓张的（比如 100 条），逐条回表，回表后再用 age=20 过滤 - 有 ICP：在索引中找到姓张的后，直接在索引层 用 age=20 过滤（因为联合索引中有 age），过滤后只回表匹配的行（比如 5 条）
ICP 减少了回表次数，EXPLAIN 中 Extra 显示 Using index condition。
Q3: 前缀索引的优缺点？
答案：
-- 对 email 的前 10 个字符建索引
ALTER TABLE users ADD INDEX idx_email(email(10));
优点：索引更小，存储更省 缺点：
- 无法用于 ORDER BY
- 无法作为覆盖索引（因为索引中存的不是完整值）
- 需要根据选择性选择合适的前缀长度
Q4: 一千万数据，如何添加索引不影响线上服务？
答案：
MySQL 5.6+ 支持 Online DDL，添加索引期间不阻塞 DML：
ALTER TABLE big_table ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM=INPLACE：原地修改，不需要复制整张表LOCK=NONE：不加锁，DML 可以正常执行
对于更老版本或更安全的方案，可用 pt-online-schema-change 或 gh-ost 工具。