MySQL 笔记整理（9） --普通索引和唯一索引，应该怎么选择？

程序员文章站 2022-04-04 12:31:58

笔记记录自林晓斌（丁奇）老师的《MySQL实战45讲》（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除） 9） --普通索引和唯一索引，应该怎么选择？假如你在维护一个市民系统，每个人都有唯一的身份证号，而且业务代码也已经保证了不会写入两个相同的身份证号。如果需要按身份证号来查找，你可能会 ......

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）

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

　　假如你在维护一个市民系统，每个人都有唯一的身份证号，而且业务代码也已经保证了不会写入两个相同的身份证号。如果需要按身份证号来查找，你可能会执行类似这样的sql语句：

select name from cuser where id_card = 'xxxxxxxxyyyyyzzz';

　　由于身份证号id_card字段较长，不建议将身份证号当做主键，那么现在你有两个选择。要么给id_card建立一个普通索引，要么给它建立一个唯一索引。由于业务代码已经保证了身份证号的唯一性，这两个选择在逻辑上都是正确的。那么，从性能角度来考虑，你会选择普通索引还是唯一索引呢？为什么呢？

　　前面我们提到过，在innodb中，索引是以b+树的形式存在的。查询语句在索引树的查找过程，先是通过b+树从树根开始，按层搜索到叶子节点，然后再进行判断.(当然如果查询条件比较多，可能需要回表，具体请参考之前的文章。)

　　对于普通索引来说，查找到满足条件的id_card之后，需要再去查找下一个记录，直到碰到id_card不符合要求的记录。
　　对于唯一索引，由于索引保证了唯一性，查找到第一个符合条件的id_card之后就会立即返回，不再进行检索了。

　　那么这两个索引之间的不同带来的性能差异会有多少呢？答案是，微乎其微。innodb的数据是按数据页为单位来读写的。也就是说，当需要一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在innodb中，每个数据页的大小默认是16kb。因为引擎是按页读写的，所以，当找到符合id_card的记录的时候，它所在的数据页就都在内存中了。那么，对于普通索引来说，只不过是多做一次“查找和判断下一条记录”的操作，只需要一次指针寻找和一次计算。当然，如果这条记录恰好是这个数据页的最后一条，那么就必须读取下一个数据页才能进行判断。但，一个数据页可以放下很多个key，这种情况出现的概率会很低。所以，对于查询来说，这两种索引的性能差异是不大的。

更新过程：

　　这里，我们需要先介绍一下change buffer。当需要更新一个数据页的时候，如果这个数据页在内存中就直接更新，如果这个数据页不在内存中，在不影响数据一致性的前提下，innodb会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存中，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。需要说明的是，虽然名叫change buffer,实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

　　将change buffer中的操作应该用到原数据页，得到最新结果的过程叫做merge.除了访问这个数据页会触发merge外，系统后台线程会定期merge。在数据库正常关闭(shutdown)的过程中，也会执行merge操作。显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能避免占用内存，提高内存利用率。那么，什么条件下可以使用change buffer呢？

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

　　change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change_buffer的大小最多只能占用buffer pool的50%。那么，如果要在一张表里插入一条新记录的时候innodb的处理流程是怎样的呢?

　　第一种情况，这个记录要更新的目标在内存中。这时，innodb的处理流程如下：

　　对于唯一索引来说，找到要插入的位置，判断有没有冲突，插入这个值，语句执行结束
　　对于普通索引来说，找到要插入的位置，插入这个值，语句执行结束。

　　这样看来，两种方式的差别知识一个判断，影响很小。我们再来看看第二种情况，这个记录要更新的目标不再内存中，这时，处理的流程如下：

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

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

索引的选择和实践：

　　这两类索引再查询能力上是没差别的，主要考虑的是对更新性能的影响。所以建议尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer.而在其他情况下，change buffer都能提升更新性能。实际上，普通索引和change buffer的配合使用，对于数据量大的表的更新优化是很明显的。