唯一索引，普通索引如何抉择

1. 什么是唯一索引

UNIQUE 唯一索引，不允许重复。

2. 什么是普通索引

INDEX 普通索引，允许重复。

3. 数据库查询过程中普通索引和唯一索引的区别

比如 select id from T where k=5

3.1 在k上建立普通索引

在查找的过程中，找到第一个满足 k=5的记录后，需要查询下一个记录，知道遇到第一个不满足k=5的记录。

3.2 在k上建立唯一索引

唯一索引表示索引是唯一的，因此找到第一个满足k=5的记录后，就停止查找。

3.3 对比

普通索引和唯一索引查找过程中的性能差距微乎其微，可以忽略不计。

4. 数据库更新过程中普通索引和唯一索引的区别

4.1 change buffer

  • 更新一个数据页x的时候，如果数据页x在内存里的话，就直接更新。
  • 如果数据页x不在内存里，InnoDB会将更新操作换存在change buffer中，不需要把数据页x读到内存里
  • 下次查询访问数据页x的时候，把数据页x读入内存后，先把change buffer 中与数据页x有关操作执行，保证数据正确性。
  • change buffer 操作执行的过程为merge，除了查询操作会触发merge，后台也会定期merge。

4.2 只有普通索引的情况下才会使用change buffer

唯一索引更新的时候，需要检查唯一性约束，需要把数据页读到内存里，因此不需要change buffer。

5. 如何抉择

结论是 尽量选择普通索引。

5.1 查询的过程中

唯一索引和普通索引性能差别微乎其微。

5.2 更新的过程中

1. 要更新的数据页 在内存里
  • 普通索引，找到目标值进行更新。
  • 唯一索引，在普通索引的基础上多了一步判断冲突。
2. 要更新的数据页不在内存里
  • 唯一索引需要把数据页读到内存里，然后更新。
  • 普通索引把更新操作记录在change buffer中，稍后再执行，少了一步读取数据页到内存的操作。

6. 扩展 redo log 和 change buffer 的区别

  • redo log 节省了随机写磁盘的IO消耗，将写操作缓存下来，之后再执行。
  • change buffer 节省了 随机读磁盘的IO消耗，更新数据的时候，减少了一次读操作。