mysql 增加唯一索引_MySQL 普通索引和唯一索引究竟如何选择？

最新推荐文章于 2024-07-22 03:11:08 发布

Mustangmelo

最新推荐文章于 2024-07-22 03:11:08 发布

阅读量210

点赞数

文章标签： mysql 增加唯一索引

本文链接：https://blog.csdn.net/weixin_42345731/article/details/113489433

版权

点击上方蓝色“一点代码”，选择“设为星标”

更方便获取学习资源！

实际上，我们在面临选择这两种索引时，更多的会考虑业务数据的适用场景，如业务上是否可以确保唯一性？但当我们使用唯一索引后，又总会担心是否会造成性能的下降... 今天这篇文章，将深入解析普通索引和唯一索引在面临更新和查询两种场景时，底层的实现机制。

假设我们在维护一张用户系统，每个人都有一个唯一的身份证号码，并且业务代码已经确保了不会写入两个重复的身份证号。如果需要执行按照身份证号查询姓名，就会执行类似以下的SQL语句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

由于用户表极高的数据量，我们应该考虑在id_card字段上建立索引。但由于身份证号字段比较大，并不建议作为主键。因此我们需要考虑选择使用唯一索引还是普通索引。我们假设字段k上的值都不重复，来看看查询过程中不同的索引实现对性能的影响。

查询过程

一. 查询场景 假定执行的语句:

select id from T where k = 5

这个查询语句在索引树上的查找过程，是首先通过B+树从树根开始，按层搜索到叶子节点(这部分我们可以认为数据页内部使用二分查找来定位记录)

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到的第一个不满足k=5条件的记录。
对于唯一索引，由于索引本身确定了数据的唯一性，因此查找到第一个满足条件的记录后，就会停止继续检索。

这二种索引在查询上的区别，看上去唯一索引减少了数据遍历的次数，但实际上，性能上的差异却微乎其微。因为InnoDB中，实际上是按照数据页来进行读写的。因此，当需要读一条记录时，并不是将这单条记录从磁盘中读出来，而是以页为单位，将含有该行记录的整页都读入内存(在InnoDB中，每个数据页大小默认16KB) 而因为引擎按页读写，当找到k=5的记录时，它所在的数据页就都在内存里了。所以对于普通索引来说，要多做的那几次“查找和判断下一条记录”的操作，都只是在内存中多做几次指针寻找。但也有特殊情况，如果k=5位于数据页的最后一条记录，那么读取下一条记录就涉及到读取下一个数据页，这个操作就会较为复杂了。而对于一个整型字段，一个数据页可以存放近千个key，因此出现这种情况的概率会很低。所以我们计算平均性能差异时，对于现在的CPU来说可以忽略不计。 更新过程

二. 更新场景 说明普通索引和唯一索引更新操作上的性能差异，此处我们需要引入一个机制：change buffer 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘读入这个数据页了。在下次查询需要访问这个数据页时，将数据页读入内存，然后执行change buffer中与这个页有关的更新操作。通过这种方式来保证这个数据逻辑的正确性。值得一提的是，虽然名字叫做change buffer，但实际上它是可以持久化的数据。也就是说，change buffer在内存中有副本，也会被写入到磁盘上。关于change buffer，MySQL官方介绍为：

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中，也会执行 merge 操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。在什么条件下可以使用 change buffer？对于唯一索引来说，所有更新操作都要先判断这个操作是否违反唯一性约束。比如要插入(4,400)这个记录，就要先判断表中是否已经存在了k=4的记录，而这必须要将数据读入内存才能判断。如果都已经读入内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新操作就不能使用到change buffer了，实际上，也只有普通索引可以使用到。 change buffer使用的是buffer pool里的一部分内存空间，因此不能无限增大。

关于buffer pool可参见官方文档: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

change buffer的大小可以通过参数：

innodb_change_buffer_max_size

来控制，当设置为50时，表示change buffer的大小最多能占用buffer pool的50%。回到更新操作，我们在这张表中插入新纪录(4,400)时，InnoDB的处理流程如下：

对于唯一索引而言，找到k=3和5之间的位置，判断是否有冲突，然后插入这个值。
对于普通索引而言，找到k=3和5之间的位置，然后插入这个值。

这样来看，普通索引和唯一索引对更新语句性能的差别，只是一个判断，只会耗费极小的CPU时间但重点是，当我们要更新的记录数所处的数据页不在内存中，此时InnoDB的处理流程如下：

对于唯一索引而言，需要将数据页读入内存，判断没有冲突，更新操作。
对于普通索引而言，则是将更新记录在change buffer中，操作结束。

将磁盘数据读入内存，涉及磁盘的随机IO，这是数据库成本最高的操作之一。change buffer因为减少了随机磁盘的访问，所以对更新性能提升是非常明显的。 change buffer的merge动作是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的更新动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多，收益也就越大。因此，对于写多读少的业务来说，页面在写完以后被马上访问的概率比较小，此时change buffer的使用效果最好。比如账单类、日志类的系统。反之，假设一个业务的更新模式是写入之后很快就会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge操作，这样随机访问IO的次数不会减小，反而增加了change buffer的维护代价。所以对于这种业务场景系统而言，change buffer反而会适得其反。因此，对于这种场景，我推荐你关闭change buffer，而其他场景下，change buffer都能提升更新性能。在实际使用过程中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化，收益还是比较明显的。特别是在机械硬盘时，change buffer的收益将非常显著。

————————————————