索引的选择与Change Buffer

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 显著高估；执行计划走了全表扫描
核心原因	索引基数估算不准确；二级索引导致回表开销被放大
解决办法	使用 ANALYZE TABLE 更新统计信息
实践建议	当发现慢查询/rows 异常时，第一步先做统计更新；必要时使用 force index 临时规避