MySQL 索引原理与优化
从索引数据结构出发，讲清 InnoDB B+ 树索引的工作原理，覆盖"为什么要用覆盖索引"、"什么时候会选错索引"、"字符串字段如何建索引"等高频实战问题。
目录
| 章节 | 说明 |
|---|---|
| 索引模型对比 | 哈希表、有序数组、B 树的适用场景 |
| InnoDB B+ 树索引 | 聚簇索引与二级索引，回表的代价 |
| 覆盖索引 | 避免回表的核心技巧 |
| 最左前缀原则 | 联合索引的使用规律 |
| 索引下推（ICP） | MySQL 5.6 引入的引擎层过滤优化 |
| 普通索引 vs 唯一索引 | Change Buffer 的妙用 |
| MySQL 选错索引 | 为什么优化器会选错，如何干预 |
| 字符串字段索引策略 | 前缀索引的选择与代价 |
索引模型对比
| 数据结构 | 等值查询 | 范围查询 | 更新成本 | 适用场景 |
|---|---|---|---|---|
| 哈希表 | O(1) | ❌ 不支持（无序） | O(1) | 等值查询为主（如 Memcached） |
| 有序数组 | O(log N) | O(log N) | O(N)（插入需移位） | 静态数据（写后不再改） |
| B 树（多叉树） | O(log N) | O(log N) | O(log N) | ✅ 数据库索引的主流选择 |
为什么数据库选 B 树而非二叉搜索树？ 磁盘读取是按"页"（通常 16 KB）为单位，B 树每个节点存多个 key，一次磁盘 I/O 加载一个节点，树高更低（3-4 层即可支撑千万级数据），I/O 次数少。
InnoDB B+ 树索引
页大小：为什么是 16KB
InnoDB 默认页大小为 16KB（初始化时可设为 4/8/32/64KB，之后不可更改）。
B+ 树高度决定查询 I/O 次数
非叶子页每个索引项约 14 字节（6B 主键 + 8B 指针），16KB 页可存：
16384 ÷ 14 ≈ 1170 个索引项
三层 B+ 树容量：
1170 × 1170 × 16 行/页 ≈ 2000 万行
→ 2000 万行的表，根到叶只需 3 次 I/O
页越小树越深，I/O 次数越多；页越大写放大越严重、锁粒度越粗——16KB 是经验最优解。
与磁盘扇区对齐
现代 SSD/HDD 扇区大小 4KB，16KB = 4 × 4KB，一次页读写刚好对应整数个扇区，无撕裂写风险。
聚簇索引
二级索引
- 查询主键 → 直接在 B+ 树找到对应叶子节点，取完整行数据
- InnoDB 中每张表都有且只有一个聚簇索引（按主键组织）
回表的代价：多一次 B+ 树查找，且是随机 I/O（因为主键值可能分散在不同页）。回表次数多时，优化器可能放弃索引，直接全表扫描。
覆盖索引
定义
查询需要的字段全部在索引上，无需回表。
-- 假设有联合索引 (name, age)
-- 场景：查 18 岁的用户的姓名（name 和 age 都在索引上）
SELECT name FROM user WHERE age = 18;
-- 这个查询"覆盖"了索引，不需要回表
-- EXPLAIN 中 Extra 列显示 "Using index"
何时建覆盖索引
高频查询、数据量大的表，如果查询涉及的字段有限且固定，可以专门建覆盖索引：
-- 身份证号查姓名（高频查询，只需要 id_card + name）
ALTER TABLE user ADD INDEX idx_id_card_name(id_card, name);
-- 查询时无需回表
SELECT name FROM user WHERE id_card = '310...';
最左前缀原则
联合索引 (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（a 是范围查询，b 无法继续用） |
索引设计原则
原则：能复用就不新建，通过调整字段顺序让一个索引服务多个查询
示例：已有索引 (name, age)，要支持 "WHERE name = ?" → 不需要新建 (name) 索引，直接复用左前缀
索引下推（ICP）
MySQL 5.6+ 引入，将部分 WHERE 条件下推到存储引擎层执行，减少回表次数。
-- 联合索引 (name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 10;
-- 5.6 之前：
-- 索引扫描所有 name LIKE '张%' 的行 → 每行都回表 → 在 Server 层过滤 age=10
-- 5.6 之后（ICP）：
-- 索引扫描 name LIKE '张%' 时，同时在索引上判断 age=10 → 只有满足的行才回表
-- EXPLAIN 中 Extra 列显示 "Using index condition"
普通索引 vs 唯一索引
Change Buffer（写缓冲）
写入时，如果数据页不在内存中：
- 普通索引：将写操作记录到 Change Buffer，不需要读磁盘，等下次读该页时再合并
- 唯一索引：必须先将数据页读入内存（验证唯一性），无法使用 Change Buffer
选择建议
| 场景 | 建议 |
|---|---|
| 业务逻辑保证数据唯一 | 建普通索引，利用 Change Buffer 提升写性能 |
| 必须数据库层面约束唯一性 | 建唯一索引，但写性能有损失 |
| 写多读少（如日志类数据） | 尤其推荐普通索引，Change Buffer 效益最大 |
| 内存充足（所有数据页常驻内存） | 差异不大，Change Buffer 优势消失 |
注意：设置
innodb_change_buffer_max_size（默认 25，即 Buffer Pool 的 25%）可以调整 Change Buffer 大小。
MySQL 选错索引
为什么优化器会选错
优化器基于扫描行数的估算值（而非精确值）选择索引，估算方式是采样统计（ANALYZE TABLE 触发重新统计）。
常见原因：
- 基数（Cardinality）估算偏差——索引上的数据分布变化后统计信息未更新
- 回表代价估算不准——以为走索引但回表次数太多，不如全表扫描
- 排序操作——走索引需要额外排序时，优化器可能选择全表扫描
修正方法
-- 方法1：强制使用指定索引
SELECT * FROM t FORCE INDEX(idx_a) WHERE a > 1 ORDER BY b LIMIT 3;
-- 方法2：重新统计索引信息
ANALYZE TABLE t;
-- 方法3：修改 SQL，引导优化器
-- 如：将 ORDER BY b LIMIT 3 改为 ORDER BY b, a LIMIT 3
-- 让优化器认为不需要额外排序，从而选择正确索引
字符串字段索引策略
前缀索引
-- 完整索引：存储空间大，但查询无需回表
ALTER TABLE user ADD INDEX idx_email(email);
-- 前缀索引：节省空间，但可能需要回表（用来验证完整值）
ALTER TABLE user ADD INDEX idx_email_6(email(6));
选择前缀长度：前缀区分度 ≈ 完整字段区分度即可。
-- 统计不同前缀长度的区分度（越接近 1 越好）
SELECT
COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS ratio_4,
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS ratio_5,
COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS ratio_6
FROM user;
前缀索引的代价
- 无法使用覆盖索引：即使查询只需要 email，也需要回表确认完整值
- 无法在 ORDER BY 中避免排序
针对身份证号、邮箱等场景的优化方案
| 方案 | 思路 | 适用场景 |
|---|---|---|
| 倒序存储 + 前缀索引 | REVERSE(email) 存入倒序字段，查询时 WHERE email_rev LIKE REVERSE('...%') | 区分度集中在后半段（如身份证号末位） |
| Hash 字段 | 新增 email_hash 字段存 CRC32(email)，走等值查询 | 只有等值查询、不需要范围查询 |
-- Hash 字段方案
ALTER TABLE user ADD COLUMN email_hash INT UNSIGNED;
UPDATE user SET email_hash = CRC32(email);
CREATE INDEX idx_email_hash ON user(email_hash);
-- 查询（注意 CRC32 可能碰撞，需额外判断 email 完整值）
SELECT * FROM user WHERE email_hash = CRC32('xxx@example.com') AND email = 'xxx@example.com';
参考资料
- 《MySQL 实战 45 讲》— 第 04、05 讲：深入浅出索引（上/下）
- 《MySQL 实战 45 讲》— 第 09 讲：普通索引和唯一索引，应该怎么选择？
- 《MySQL 实战 45 讲》— 第 10 讲：MySQL 为什么有时候会选错索引？
- 《MySQL 实战 45 讲》— 第 11 讲：怎么给字符串字段加索引？
- InnoDB Index Types
评论 (0)