1. 索引选择与Change Buffer
问题引出：普通索引 vs 唯一索引 ——如何选择？
在实际业务中，如果一个字段的值天然具有唯一性（如身份证号），并且业务代码已确保无重复写入，那就存在两种选择：
- 创建唯一索引
- 创建普通索引
虽然逻辑上两者都可以正确工作，但从 性能角度看，应该如何选择呢？
1.1. 查询场景下的性能差异
查询语句示例：
SELECT name FROM CUser WHERE id_card = 'xxxxxxxyyyyyyzzzzz';查询过程分析：
- InnoDB 使用 B+ 树索引，查找过程是按层遍历到叶子节点。
- 普通索引：
找到首个满足条件的记录后，还会继续查找，直到条件不再满足。
- 唯一索引：
找到首个满足条件的记录后立即停止。
性能差距分析：
- InnoDB 是按数据页（默认16KB）为单位读取的。
- 也就是说，命中一条记录时，整个数据页已在内存中。
- 普通索引多做一次判断和指针移动，性能开销极小，可以忽略不计。
结论：查询性能差异微乎其微
1.2. 更新场景下的性能差异 （关注 Change Buffer ）
Change Buffer 的概念：
- 又称 变更缓冲区，用于缓存针对尚未加载入内存的数据页的 DML 操作。
- 目的是延迟磁盘读写，提升写性能。
- 持久化存储，内存+磁盘双存储。
Merge 操作：
- 当数据页被访问或系统后台线程定期触发时，change buffer 会被合并（merge）到实际数据页中。
两种索引对比：
| 特性 | 唯一索引 | 普通索引 |
| 查询性能差距 | 几乎无 | 几乎无 |
| 是否能使用 Change Buffer | ❌ 不能使用 | ✅ 可以使用 |
| 写入磁盘前是否需加载数据页 | ✅ 是 | ❌ 否 |
| 写多读少场景优化空间 | ⛔️ 受限 | ✅ 提升明显 |
| 建议使用场景 | 严格校验唯一性 | 默认首选 |
- 唯一索引需验证是否存在重复值，必须读入数据页判断唯一性，无法延迟IO。
- 而普通索引可以直接缓存写操作，延迟数据页加载。
1.3. Change Buffer 的影响和适用场景
Change Buffer 的实际影响分析
1. 情况一：目标页在内存中
- 唯一索引：读内存判断唯一性后插入，结束。
- 普通索引：直接插入，结束。
- ✅ 性能差异极小
2. 情况二：目标页不在内存中
- 唯一索引：
需要将目标页从磁盘加载入内存进行唯一性判断 → 高成本的随机 IO
- 普通索引：
操作直接写入 Change Buffer，延迟磁盘读写 → 性能提升明显
这是唯一索引与普通索引的性能关键差异点！
Change Buffer 的适用场景
适用场景 ：
- 写多读少 的系统
例如：日志系统、账单系统等
页面写完之后很少会被立即查询，Change Buffer 能发挥显著优势。
不适用场景 ：
- 写后立刻读 的业务模型
写操作刚缓存就被查询命中，触发 merge，反而增加了维护成本。
实际应用建议
- 查询性能差异不大，但更新性能差异明显。
- 尽量优先选择普通索引，除非业务逻辑依赖数据库强一致性校验。
- 写多读少场景下，配合开启 Change Buffer（默认开启），显著优化性能。
- 使用机械硬盘时，Change Buffer 的效果更明显，应适当调大
innodb_change_buffer_max_size参数（如 50%）。 - 若写后即读，可以考虑 关闭 Change Buffer。
2. MySQL选错索引问题分析
2.1. 索引错选问题
问题背景与现象：
- 有时 MySQL 执行 SQL 时并没有选择最佳索引，导致性能下降。
- 通过一个具体例子说明了优化器因估算错误而选错索引的情况。
实验设计：
1. 表结构与索引
CREATE TABLE t (a INT,b INT,c INT,INDEX(a),INDEX(b)
);2. 数据插入
- 插入数据：
(1,1,1)到(100000,100000,100000)共 10 万行。
预期查询语句
SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;3. 实验步骤（关键触发逻辑）
- Session A：开启事务，未提交；
- Session B：
删除所有数据；
重新插入 10 万行；
执行上面的查询。
4. 异常现象
- 查询变慢，发现 优化器选择了全表扫描 而不是走
a的索引。
执行计划对比与影响分析：
Q1：默认语句
SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;- 使用了全表扫描，rows = 104620
- 扫描耗时约 40ms
Q2：强制使用索引
SELECT * FROM t FORCE INDEX(a) WHERE a BETWEEN 10000 AND 20000;- 使用索引
a，rows = 10001 - 扫描耗时约 21ms
- 结论：Q2 明显更优
2.2. MySQL 优化器选错索引原因
优化器目标
- 找出 执行代价最小 的执行计划；
- 代价估算核心：行数（row estimate） + 回表成本。
行数估算依赖“统计信息”
- MySQL 使用索引的基数（cardinality） 估算结果行数；
- 采样得出，不一定准确；
- 命令查看基数：
SHOW INDEX FROM t;统计信息采样机制
- 参数
innodb_stats_persistent：
ON：采样页数 20，触发更新阈值 10
OFF：采样页数 8，触发更新阈值 16
- 采样带来的估算误差：
优化器以为 a between 10000 and 20000 会返回约 37000 行；
实际只有 10001 行，高估了结果量。
回表代价高估
- 索引
a是二级索引，取出数据后需要回主键索引查全行（回表）； - 优化器认为：
37000 次回表 ≈ 37000 次随机 IO；
而全表扫描只需约 100 页顺序读；
所以选择全表扫描。
2.3. 验证与解决方案
观察 EXPLAIN 输出
EXPLAIN SELECT * FROM t WHERE a BETWEEN 10000 AND 20000;- rows ≈ 37116（高估）→ 优化器认为成本更高。
修复手段：更新统计信息
ANALYZE TABLE t;- 执行后重新
EXPLAIN，rows 变为 10001； - 优化器重新选择正确索引。
总结与实践建议
| 类别 | 内容 |
| 问题核心 | 优化器因统计信息误差、高估回表代价，选错了索引 |
| 典型表现 | EXPLAIN 中 显著高估；执行计划走了全表扫描 |
| 核心原因 | 索引基数估算不准确；二级索引导致回表开销被放大 |
| 解决办法 | 使用 更新统计信息 |
| 实践建议 | 当发现慢查询/rows 异常时，第一步先做统计更新；必要时使用 临时规避 |
)
