MySQL如何选择普通索引和唯一索引

vlambda
2022-05-15

MySQL如何选择普通索引和唯一索引

原文《MySQL实战45讲》

前言

假如你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似的SQL语句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以，你一定会考虑在id_card字段上建索引。

由于身份证号字段比较大，我不建议你把身份证号当作主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择，逻辑上都是正确的。

现在我要问你的是，从性能的角度考虑，你选择唯一索引呢？还是普通索引？选择的依据是什么？

开门见山

举个栗子

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在k上有索引。

这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的(ID,k) 值分别为 (100, 1) 、 (200, 2) 、 (300, 3) 、 (400, 4) 、 (500, 5) 和 (600, 6) , 两棵树的示意图如下：

接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

查询过程

假设，执行查询的语句是 select id from T where k = 5。这个查询语句在索引书上查找的过程，是先通过B+树丛树根开始，按层搜索都叶子节点，然后可以认为数据页内部通过二分法来定位记录。

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

但是这个不同会带来多少的性能差距呢？答案是，微乎其微。

因为，InnoDB的数据是按数据页为单位来读写的。也就是说，当需要都一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认为16KB。所以，“查找和判断下一条记录”的操作，就只需要一个指针寻找和一次计算。当然，如果k=5这个记录刚好是这个数据也的最后一条记录，那么要取下一个记录，就必须读取下一页，这个操作会稍微复杂一些。但是，对于整形字段，一个数据页可以放近千个key，因此出现这种情况的概率很低。

一个数据页可以放多少个整形key呢？

答案是 16*1024/(8+6)≈1200。其中，数据也默认大小为16k，MySQL的bigint为8个字节，指针大小在InnoDB元宝吗中设置为6字节。

更新过程

当需要更新一个数据页时，如果数据页在内存中，就直接更新，如果这个数据页没有在内存中，在不影响数据一致性的前提下，InnoDB会将更新操作缓存在change buffer 中，这样就不需要从磁盘中读入这个数据页了。若下次查询需要访问该数据页，将数据页读入内存，然后执行change buffer中与这个页有关的操作，就能得到正确的数据。

现在，我们一起来看看，如果要在这张表中插入一个新纪录(4, 400)的话，InnoDB的处理流程是怎么样的。

第一种情况，要更新的记录所在的数据页在内存中，这种情况下普通索引和唯一索引对更新语句性能的差别，只是一个判断，只会耗费微笑的CPU时间。

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

第二中情况，要更新的记录所在的数据页不在内存中。

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

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

小结

唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发，应该考虑非唯一索引。
对于”是否使用唯一索引“与”业务可能无法确保唯一性“的情况。

首先，我们必须保证业务正确性。假如”业务代码已经保证不会写入重复数据“，那么我们可以选择非唯一索引，使得程序有更高的处理性能。如果”业务无法保证不会写入重复数据“，那么没得选，必须创建唯一性索引，用数据库来保证唯一性约束。
在一些”归档库“的场景，即线上数据只保留半年，历史数据保存归档库。这个时候，归档数据已经是确保没有唯一键冲突了，这个情况下，为了提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

vlambda博客
学习文章列表