普通索引与唯一索引的选择

在选择使用普通索引还是唯一索引时，主要需要考虑它们在查询性能和更新性能上的差异。

1. 查询性能

• 等值查询:
  • 唯一索引: 找到满足条件的第一个记录后，直接停止搜索。
  • 普通索引: 找到满足条件的第一个记录后，还需要继续向后查找，直到碰到第一个不满足条件的记录。
• 性能差异: 对于 InnoDB 引擎来说，数据是按数据页为单位读写的。当找到满足条件的记录时，它所在的数据页就已经在内存中了。对于普通索引，多做的那次“查找和判断下一条记录”的操作，只是简单的指针移动和 CPU 计算，速度非常快。因此，两者在查询性能上的差距微乎其微。

2. 更新性能 (Change Buffer)

这是两者性能差异的主要体现。

• Change Buffer:

  • 定义: 当需要更新一个数据页时，如果该数据页不在内存 (Buffer Pool) 中，InnoDB 会将这个更新操作缓存在 Change Buffer 中（这是内存 Buffer Pool 的一部分），而不是立即从磁盘加载数据页进行更新。这样就避免了大量的随机 I/O。
  • 适用对象: 非唯一二级索引 (普通索引) 的更新操作（INSERT, DELETE, UPDATE）。
  • 不适用唯一索引: 因为唯一索引需要保证其唯一性约束，所以在插入或更新时，必须将数据页读入内存进行判断，看是否存在重复记录。因此，唯一索引的更新无法利用 Change Buffer。
  • Merge 操作: Change Buffer 中的操作并不会一直缓存。当下次有查询需要访问该数据页时，会将数据页读入内存，然后执行 (merge) Change Buffer 中与这个页相关的操作，以保证数据的逻辑正确性。系统后台线程也会定期进行 merge 操作，数据库正常关闭时也会执行 merge。
  • 持久化: Change Buffer 本身也是可以持久化的。它的变更也会记录到 redo log 中，所以即使数据库异常重启，Change Buffer 中的数据也不会丢失。

• 性能影响:

  • 普通索引: 可以利用 Change Buffer，将对磁盘的随机写（更新数据页）转换为对 Change Buffer 的顺序写（可能还有 redo log 的顺序写），并在后续 merge 时批量处理，显著减少了随机 I/O，提升更新性能，尤其是在写多读少的场景。
  • 唯一索引: 无法利用 Change Buffer，每次更新都需要将数据页读入内存，进行随机 I/O，更新性能相对较低。

3. Change Buffer 的使用场景与效果

• 适用场景:
  • 写多读少的业务：例如账单流水、日志记录等。更新操作可以先缓存，后续读取概率低，merge 操作可以延迟进行，优化效果明显。
  • 写入后不会立即读取的场景。
• 不适用场景 (或效果差):
  • 写入后立即读取: 写入后马上查询该数据，会立即触发 merge 操作，不仅没有减少随机 I/O，反而增加了 Change Buffer 的维护代价。
  • 业务强制要求唯一性: 必须使用唯一索引。

4. 索引选择建议

• 查询性能: 两者差别不大。
• 更新性能: 普通索引由于可以利用 Change Buffer，通常更新性能更好。
• 核心考量: 业务是否强制要求数据库层面保证唯一性。
  • 如果业务不要求或可以通过应用层逻辑保证唯一性，强烈推荐使用普通索引，以获得更好的更新性能。
  • 如果业务必须依赖数据库保证唯一性，则只能选择唯一索引。

5. Change Buffer 与 Redo Log 的对比

两者都是 InnoDB 用来提升性能的机制，但侧重点不同：

• Redo Log: 主要目的是保证事务的持久性 (Crash-Safe)。它通过将随机的磁盘写操作（更新数据文件）转换为顺序的日志写操作，来提升性能和保证数据不丢失。节省的是随机写磁盘的 I/O 消耗。
• Change Buffer: 主要目的是提升非唯一二级索引的更新性能。它通过缓存更新操作，避免了在数据页不在内存时立即进行的随机读操作。节省的是随机读磁盘的 I/O 消耗。

---