MySQL实战 | 09 普通索引还是唯一索引？

普通索引和唯一索引如何选择？

脑图地址：https://mubu.com/doc/aRFaYEd5EG

• 几种索引
  • 普通索引
    • 作用：加快对数据的访问速度
    • 一般加在经常查询或者排序的字段上
    • 允许字段值重复
  • 唯一索引
    • 保证数据记录的唯一性
    • 目的是为了避免数据重复
    • 维护数据完整性
• 如何选择
  • 查询效率
    • 普通索引会查询多个，需要多出查找和判断下一条记录的动作
    • 唯一索引查询一次
    • 性能差不多
      • InnoDB 的数据是按数据页为单位来读写的
      • 一个数据页可以放近千个 key
      • 很少出现跨数据页查询的情况
  • 更新效率
    • change buffer
      • 更新数据页时，若数据页在内存中，则直接更新
      • 若数据页不在内存中，则先将更新操作缓存在 change buffer 中
      • redo log 里记录了数据页的修改以及 change buffer 新写入的信息
      • 下次查询时，先将数据读入内存，然后再执行缓存在 buffer 中的操作
      • 可以持久化，在内存中有拷贝，也会写入磁盘
      • 何时 merge 数据？
        • 访问该数据页
        • 后台定期 merge
        • 关闭数据库时
      • 优点
        • 减少磁盘读 IO
    • 唯一索引的更新过程
      • 唯一索引需要确保数据的唯一性
      • 若目标数据页不在内存中，需要将数据读取到内存
      • 不会使用到 change buffer，直接在内存中更新
    • 普通索引
      • 若目标数据页不在内存中，直接将更新缓存到 change buffer 中
      • 适合写多读少的业务
      • 若写完立马就读，就需要数据的 merge
      • 不但不能减少磁盘 IO，会增加了 change buffer 的维护代价
    • 结论
      • 数据从磁盘读入内存涉及随机 IO 的访问
      • 唯一索引的更新代价更大
• 索引最终选择
  • 查询能力上差别不大
  • 更新性能差别较大
  • 若业务没有字段的唯一性要求，推荐使用普通索引
  • 另外，就算有唯一性需求，唯一性也可以用业务层来保证，例如 uuid
  • 普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的