灰气球

灰气球

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

326
2019-10-15

原文《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+树丛树根开始,按层搜索都叶子节点,然后可以认为数据页内部通过二分法来定位记录。

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

但是这个不同会带来多少的性能差距呢?答案是,微乎其微。
因为,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时间。

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

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

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

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

小结

  1. 唯一索引用不上change buffer的优化机制,因此如果业务可以接受,从性能角度出发,应该考虑非唯一索引。
  2. 对于”是否使用唯一索引“与”业务可能无法确保唯一性“的情况。
  3. 首先,我们必须保证业务正确性。假如”业务代码已经保证不会写入重复数据“,那么我们可以选择非唯一索引,使得程序有更高的处理性能。如果”业务无法保证不会写入重复数据“,那么没得选,必须创建唯一性索引,用数据库来保证唯一性约束。
  4. 在一些”归档库“的场景,即线上数据只保留半年,历史数据保存归档库。这个时候,归档数据已经是确保没有唯一键冲突了,这个情况下,为了提高归档效率,可以考虑把表里面的唯一索引改成普通索引。