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

最新推荐文章于 2024-09-14 14:42:32 发布

小关同学喜欢吃汉堡

最新推荐文章于 2024-09-14 14:42:32 发布

阅读量305

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_45784666/article/details/121129340

版权

数据库专栏收录该内容

10 篇文章

订阅专栏

前言：最近在研究阿里的开发手册中关于 MySQL 的一些规定，所以来记录一下学习中的心得

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

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

唯一索引和普通索引有什么区别？

普通索引：查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

上面的查询区别对于性能而言是微乎其微的，InnoDB 读写数据是按页来的，也是说当找到 k=5 的记录的时候，它所在的数据页就都在内存里了，那么我们只需要判断下一条数据是不是等于5即可，直到出现不为5的值为止，这些都是在内存中操作的，所以性能微乎其微。

再来看看更新插入操作。想了解更新操作对性能的影响先了解了解 change buffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。下面是几种会发生 merge 的情景

有用户查询访问这个数据页
系统后台线程定期执行 merge 操作
数据库正常关闭的过程中也会执行 merge 操作

除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中，也会执行 merge 操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

什么情况下会使用 change buffer ？

**对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。**比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为50的时候，表示 change buffer 的大小最多只能占用 buffer pool 的50%。

再了解了change buffer 之后我们再看看一条插入流程是怎么样的。

这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下:

唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
普通索引：找到3和5之间的位置，插入这个值，语句执行结束。

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。可以认为性能相同。

这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下:

唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束;
普通索引：则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。普通索引的所有场景，使用 change buffer 都可以起到加速作用吗?

因为 merge 的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多(也就是这个⻚面上要更新的次数越多)，收益就越大。

写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer ，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说， change buffer 反而起到了副作用。

文章借鉴了：https://www.jianshu.com/p/226be962ceca