普通索引和唯一索引，应该怎么选择？

最新推荐文章于 2022-05-01 23:07:29 发布

✦昨夜星辰✦

最新推荐文章于 2022-05-01 23:07:29 发布

阅读量358

点赞数 5

分类专栏： ★ MySQL 实战文章标签： change buffer 普通索引唯一索引

huadong

本文链接：https://blog.csdn.net/qq_36221788/article/details/103280823

版权

24 篇文章 1 订阅

订阅专栏

我们就从普通索引和唯一索引对查询语句和更新语句的性能影响来进行分析。

查询过程

执行查询的语句是 select id from T where k=5，其中索引在 k 上。
- 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
- 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。
我们知道，InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。
因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，需要先介绍一下 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge，除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge，在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议你尽量选择普通索引。
当然，对于是否使用唯一索引：
- 首先，业务正确性优先。咱们这篇文章的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本篇文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。
- 然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

change buffer 主要节省了随机读磁盘的 IO 消耗，而且在事务提交的时候，change buffer 中的操作记录也提交到了redo log 中。
redo log 主要提供的是一个 crash-safe 能力，也间接做到了随机写磁盘的 IO 消耗。
我的理解是这样的：一条普通索引的更新语句进来时，首先将语句保存到 change buffer，同时写 redo log，这时候就算完成了；假如有个查询进来，就会读取存储引擎的数据页到内存触发 merge 操作，根据 change buffer 的操作更新内存中的数据页；等到 redo log 满了或者内存满了或者空闲时候，就会触发刷脏页的操作，即将内存中的数据页同步到存储引擎中，同时清空 redo log 日志。

笔记来源于《极客时间：MySQL实战45讲：普通索引和唯一索引，应该怎么选择？》