Skip to content

普通索引与唯一索引

一、 问题的提出：性能视角下的选择

  • 场景：为一个保证业务层面唯一的字段（如身份证号 id_card）创建索引，用于查询。逻辑上，普通索引和唯一索引都可行。
  • 核心问题：在业务已保证唯一性的前提下，从 性能 角度出发，应该选择普通索引还是唯一索引？

二、 查询性能对比

  1. 查找过程：通过 B+ 树定位到叶子节点数据页，页内可近似看作二分查找。
  2. 唯一索引：找到满足条件的记录后，立即停止 检索。
  3. 普通索引：找到第一个满足条件的记录后，需要继续查找下一条记录，直到不满足条件为止。
  4. 性能差异：微乎其微。
    • 原因：InnoDB 按 数据页 (默认 16KB) 读写。当找到目标记录时，其所在的数据页已在内存中。普通索引多做的“查找下一条”操作通常在同一页内完成，仅涉及内存中的指针操作和计算，CPU 耗时极少。即使需要读下一页（概率低），平均性能差异也可忽略。

三、 更新性能对比：Change Buffer 是关键

  1. Change Buffer 机制：

    • 目的：优化 非唯一二级索引 的更新（INSERT, DELETE, UPDATE）性能，减少随机磁盘 I/O 读取。
    • 工作原理：当需要更新一个数据页，但该页 不在内存 (Buffer Pool) 中 时，InnoDB 不会立刻从磁盘加载该页，而是将更新操作 缓存 在内存中的 Change Buffer 区域。
    • 持久化：Change Buffer 的内容会被记录到 redo log 并最终写入系统表空间，保证掉电不丢失。
    • Merge 操作：当该数据页 后续被访问（查询等）需要读入内存时，或者由 后台线程定期 执行，或者在 数据库关闭 时，会将 Change Buffer 中缓存的相关更新操作 应用 (merge) 到数据页上，得到最新数据。

  2. Change Buffer 的使用条件：

    • 仅适用于普通索引 (非唯一二级索引)。
    • 不适用于唯一索引：因为唯一索引在更新时 必须 检查唯一性约束，这需要将目标数据页 读入内存 进行判断。既然页已在内存，直接更新内存即可，无需 Change Buffer。

  3. 更新流程对比：

    • Case 1: 目标页在内存中
      • 唯一索引：查找位置，检查唯一性，插入/更新。
      • 普通索引：查找位置，插入/更新。
      • 性能差异： negligible (仅多一个 CPU 判断)。
    • Case 2: 目标页不在内存中
      • 唯一索引：必须从磁盘读取数据页 (随机 I/O)，检查唯一性，插入/更新。成本高。
      • 普通索引：将更新记录在 Change Buffer 中，语句执行结束。成本低，速度快。

  4. Change Buffer 带来的收益：

    • 显著提升更新性能，因为它 避免了大量的随机磁盘读 操作。
    • 减少 Buffer Pool 的占用（不需要立刻加载冷数据页）。

四、 Change Buffer 的适用场景

  • 效果最好：写多读少 的业务。页面写入后不常被立即访问，Change Buffer 可以累积较多更新，Merge 时摊薄成本效益最高（如日志、账单系统）。
  • 效果较差/副作用：写入后立即查询 的业务。更新刚写入 Change Buffer，马上触发查询导致页面加载和 Merge，不仅没减少随机读，反而增加了 Change Buffer 的维护开销。这种场景下可考虑关闭 Change Buffer (innodb_change_buffer = none)。

五、 索引选择与实践建议

  1. 核心结论：在 业务已确保唯一性 的前提下，从 更新性能 角度考虑，优先推荐使用普通索引。
  2. 理由：普通索引能充分利用 Change Buffer 机制来优化更新操作，减少随机磁盘读，尤其在数据量大或使用机械硬盘时效果显著。
  3. 例外情况：
    • 如果业务 必须依赖数据库强制执行唯一性约束，则 必须使用唯一索引，性能是为保证业务正确性付出的代价。
    • 对于写入后立即查询的模式，普通索引+Change Buffer 可能无优势甚至有劣势。
  4. 归档库场景：对于历史数据归档库（数据已确定无唯一冲突），可以将唯一索引改为普通索引，并适当调大 innodb_change_buffer_max_size (如 50%，表示最多占用 Buffer Pool 的 50%)，以提高归档写入效率。

六、 Change Buffer 与 Redo Log 的区别

  • Redo Log (WAL)：保证 事务的持久性 (Durability)。主要目的是将 随机的磁盘写 操作转换为 顺序写 (写 redo log 文件)，节省随机写 I/O。记录的是对数据页的 物理修改。
  • Change Buffer：主要目的是 优化更新性能。通过缓存对不在内存中的二级索引页的更新操作，节省随机读 I/O。记录的是 逻辑操作（如“向 Page X 插入记录 Y”）。
  • 协同工作：写入 Change Buffer 的操作本身也会被记录到 Redo Log 中，以保证其持久性。

七、 总结

  • 查询性能上，唯一索引和普通索引差别不大。
  • 更新性能上，普通索引可借助 Change Buffer 显著减少随机磁盘读，优势明显，尤其在写多读少或使用机械硬盘时。
  • 决策关键：业务是否需要数据库强制唯一约束 vs 是否追求极致的更新性能。在前者满足或业务已保证唯一性的情况下，后者倾向于选择普通索引。
  • Change Buffer 是 InnoDB 的一个重要性能优化特性，理解其机制有助于更好地设计索引和诊断性能问题。