这是我参与11月更文挑战的第13天,活动详情查看:2021最后一次更文挑战
假设我们现在正在维护市民系统,每个人都有一个唯一的身份证号,若能保证数据库中,一个市民只对应一个身份证号,并且身份证号是唯一的,那如果要求根据身份证号来查名字,如下sql所示:
select * from User where id_card = 'xxxxxxxxxxxxxxxxxxxxxxxxx'
按平常来看,我们一定会在id_card上加索引,但是身份证号的字段很大,我们不会将它作为主键,那我们该选择把它作为唯一索引还是普通索引呢?
我们对普通索引和唯一索引对查询和更新两种操作的性能影响进行分析。
我们先来了解下普通索引和唯一索引的差异是什么?
一.创建索引语句差异
1.1 普通索引
LTER TABLE `test` ;
ADD INDEX `k_title` (`title`) USING BTREE ;
1.2 唯一索引
LTER TABLE `test` ;
ADD UNIQUE INDEX `un_index_title` (`title`) USING BTREE ;
这里我们来介绍下 using btree
- B-TREE以B+树结构存储数据,大大加快了数据的查询速度
- B-TREE索引在范围查找的SQL语句中更加适合(顺序存储)
B-TREE索引使用场景
-
全值匹配的查询SQL,如 where act_id= '1111_act'
-
联合索引汇中匹配到最左前缀查询,如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE,只要条件中使用到了联合索引的第一列,就会用到该索引,但如果查询使用到的是联合索引的第二列act_name,该SQL则便无法使用到该联合索引(注:覆盖索引除外)
-
匹配模糊查询的前匹配,如where act_name like '11_act%'
-
匹配范围值的SQL查询,如where act_date > '9865123547215'(not in和<>无法使用索引)
二.查询过程
假设,执行查询的语句是 select id from T where k=5。 k是T表的索引字段,id是T表的主键。这个查询语句在索引树上查找的过程,先是通过B+树从树根开始,按层搜索到叶子节点,然后可以认为数据页内部通过二分法来定位记录。如下图所示:
对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
但是这两个所消耗的性能差别近乎相同。
InnoDB引擎读写是以页为单位的,当需要读一条记录时,并不是把这个记录从磁盘读取出来,而是以页为单位读入内存中,每页大小为16KB。所以当普通索引查找k为5的数据时,就已经把它所在的数据页都读入内存中,所以判断下条记录只需要寻找一次。但如果k=5这个数据在页的最后一位,那下一条数据在另一个页上,但操作成本依旧很低。
三.更新过程
当需要更新一个数据页的时候,如果此数据页就在内存中那就直接更新,如果不在的话,InnoDB会将更新操作缓存在change buffer 里,下次需要访问这个数据页时,将数据页读入内存然后执行change buffer里与该页有关的操作。
虽然change buffer有缓存二字,但是它也是会持久化到磁盘里去,将change buffer操作应用到数据页的操作,叫做merge,系统在后台也会定期执行merge,并且在数据库关闭的过程中,也会执行merge。 我个人觉得这个操作很像是消息队列。
显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率。
change buffer占用 buffer pool的大小可以通过参数 innodb_change_buffer_max_size
来动态设置,例如设置50,就代表change buffer 的大小最多只能占用buffer pool 的 50%。
那如果我们插入一条数据呢?比如我们插入一条(4,400)的记录。
- 第一种情况是:这个记录要更新的目标页在内存中。
唯一索引::找到 3 和 5 之间的位置,判断到没有冲突,插入这个值,语句执行结束;
普通索引::找到 3 和 5 之间的位置,插入这个值,语句执行结束。
这种情况并不会消耗多少时间,因为仅仅只是多了一个判断。
- 第二种情况是: 这个记录要更新的目标页不在内存中。
唯一索引::需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
普通索引::将更新记录在 change buffer,语句执行就结束了。
唯一索引需要将数据读入内存,这是数据库里成本最高的操作,所以此时,普通索引性能的提升是非常明显的。
change buffer只限于用在普通索引场景下,而不适用于唯一索引。但是,普通索引下的change buffer能一直给我们带来性能上的提升吗?答案是否定的,需要结合业务场景。
change buffer将变更的动作缓存下来,所以当change buffer中的记录越多时执行merge,收益将最大化。所以对于那些写多读少的业务时,效果最好。当业务中,更新完数据就要读数据时,会立即触发merge,IO次数增多,所以性能肯定是下降的。
小结
由于唯一索引用不上 change buffer 的优化机制,因此如果业务可以接受,从性能角度出发,建议优先考虑非唯一索引。