MySQL 优化器如何选择索引

优化器与索引选择

1. 普通索引 vs 唯一索引

change buffer机制：

当需要更新一个数据页时：

• 如果数据页在内存中就直接更新
• 如果数据页不在内存中，InnoDB会将这些更新操作缓存在change buffer中
• 下次查询需要访问该数据页时，将数据页读入内存并执行change buffer中的操作

唯一索引必须将数据页读入内存判断唯一性，因此无法使用change buffer；普通索引可以利用change buffer提升更新性能。

使用场景：

• 适合：写多读少的业务（如账单、日志）
• 不适合：写入后立即读取的场景

建议： 业务不强制要求唯一性时，优先选择普通索引。

2. 索引下推优化(ICP)

原理：

• 在索引遍历过程中，对索引中包含的字段先做判断
• 直接过滤掉不满足条件的记录，减少回表次数

效果：

• 减少回表操作
• 提高查询效率

查看： EXPLAIN的Extra列显示Using index condition表示使用了ICP

3. 优化器选择索引的逻辑

优化器基于成本选择索引，主要考虑：

• I/O成本：从磁盘读取数据页的成本
• CPU成本：数据解析和判断的成本

常见索引选择异常情况：

• 统计信息不准确
• 成本估算偏差
• 索引选择受查询条件顺序影响

4. 索引选择异常处理

解决方法：

1. 使用force index强制指定索引
2. 修改SQL语句引导优化器选择
3. 重建索引或analyze table更新统计信息
4. 增加或优化索引

案例：