阿里一面,唯一索引和普通索引如何选择?

共 3085字,需浏览 7分钟

 ·

2021-11-23 14:23

因为明年也要准备秋招了,最近准备以面试为导向开始写文章了,考虑到目前网络上提供的面试题大部分都是短短的几行字,也即背诵版,我觉得这无法满足大部分同学的诉求,知其然而不知所以然,我希望的是以面试题为导向,建立完整的知识体系,而不是东一锤西一棒,所以后续准备以牛客上的面经帖为导向,对每个面试题提供背诵版 + 详解版,已经会的同学呢可以直接看背诵版,还不太了解的同学呢可以结合详解版一起看,目前已经做了一部分汇总在 Notion 上,后续会放出来给大家的 😀

本题来源:

  • https://www.nowcoder.com/discuss/613909?type=post&order=recall&pos=&page=1&ncTraceId=&channel=-1&source_id=search_post_nctrack

很多时候,我们在业务开发的时候,都是直接无脑上手唯一索引,Tell me,多少人考虑过这个问题,唯一索引和普通索引该怎么选择?唯一索引是否会对系统性能造成影响?

事实上,确实是有影响,并且还不小!

今天这篇文章就来带大家分析下什么时候该用普通索引,什么时候该用唯一索引(进入本文之前建议各位先了解下 Buffer Pool、Insert Pool 和 Change Buffer 是个什么东西,可以参考上篇文章)。

学会了拿去装杯(滑稽)

🔈 背诵版可直接拉到文末查看

直接上个例子:

有一张用户表 user,包含主键自增长的 id,身份证号 cid,年龄,性别等

我们的系统有大量的请求,需要根据身份证号来查询用户信息:

select * from user where cid = '1111111111'

显然,对于这条语句,对身份证号 cid 加上索引以此来提高查询效率是在所难免的了。

由于身份证号每个人都是唯一的,所以我们有以下两种方案:

  1. 在业务逻辑中保证不会写入重复的身份证,给身份证号加上普通索引
  2. 给身份证号加上唯一索引

可能有小伙伴会问为啥不直接用身份证号做主键 id?

首先前文讲过的,顺序的聚集索引可以避免磁盘的随机读取,提高效率;另外,一般也不建议把业务上的字段作为主键

从逻辑上来说,上述两种方案都是可行的。

但是,从性能上来说,对于这个情景,其实方案二会消耗更多的资源!

首先,不管建立在身份号上的是唯一索引还是普通索引,他们都属于辅助索引,对吧。通过遍历辅助索引 B+ 树我们能够知道要查找的数据的主键是多少,然后回表查询。

唯一索引和普通索引的不同点就在于,普通索引查找到满足条件 cid = 1111111111 的第一个记录后,还会继续去查找下一个记录,直到碰到第一个不满足 cid = 1111111111 条件的记录;而对于唯一索引来说,一旦找到一个满足条件的记录后,就会立即停止继续检索。

看到这里有些小伙伴估计就笑了,啥玩意儿,就这么简单?于是不屑地关闭了文章。

事实上,这点不同带来的性能上的差距微乎其微

因为 InnoDB 存储引擎是按页进行读写的,所以说,当它找到 cid = 1111111111 的记录的时候,这条记录所在的数据页就已经都在内存里了。对于普通索引来说,无非就是再移动一次指针罢了。

所以,显然,这点性能差距不足以让 “唯一索引会影响性能” 这个结论站住脚。

接下来才是重点!

在某些情况下,普通索引取代唯一索引的直接理由,就来自于 InnoDB 的 Inser Buffer / Change Buffer 这个振奋人心的新特性,这里还是简单回顾下。

索引的本质是什么?是 B+ 树,是一个存在磁盘上的物理文件。

以插入操作为例

我们在构建辅助索引身份证号 cid 的这棵 B+ 树的时候,由于身份证号不是连续自增的,也就是说辅助索引的节点插入是没有顺序的,当要插入的索引页不在 Buffer Pool 里的时候,就要去离散地访问磁盘上的索引页了。

正是由于随机读取的存在导致了插入操作性能下降。

那么 Insert Buffer 呢,简单来说,当要插入的索引页不在 Buffer Pool 的时候,存储引擎并不会每插入一个新数据就去离散地访问一次磁盘页,而是先将这个操作存储到 Insert Buffer 中,在下次查询需要访问这个数据的时候,存储引擎才会将其合并(Merge)到真正的辅助索引中。这时,就相当于将多个叶子节点插入操作合并到一个操作中,这就大大提高了对于辅助索引的插入性能。

除了访问这个数据会触发 Merge 外,系统有后台线程会定期 Merge。在数据库正常关闭的过程中,也会执行 Merge 操作

不过,遗憾的是,Insert/Change Buffer 只适用于非唯一的辅助索引

对于这样一个用户系统,每天都有大量的新用户被插入进来:

insert into user values(id, cid, )

如果这个记录要操作的索引页不在 Buffer Pool 中:

  • 对于唯一索引来说,需要将数据读入 Buffer Pool,然后访问磁盘上的索引页来判断到没有冲突,随后插入这个值,语句执行结束;
  • 对于普通索引来说,由于可以使用 Inser Buffer,所以只需要将操作记录在 Insert Buffer 中,然后语句就可以结束了

很显然了,随机 IO 磁盘是数据库里面成本最高的操作之一,在这种场景下的唯一索引确实给性能带来了很大的影响。

不过,对于非唯一的辅助索引来说,Insert/Change Buffer 总是能起到作用吗

其实不然,它主要适用于写多读少的业务,因为页面在写完以后马上被访问到的概率比较小,那么 Merge 操作就不会被频繁的执行。

所以,以后各位小伙伴要是遇到读多写少的业务,记得留个心眼,少用唯一索引~


最后放上这道题的背诵版:

🥸 面试官:唯一索引和普通索引有什么区别,该如何选择?

😎 小牛肉:唯一索引和普通索引的不同点就在于,普通索引查找到满足条件的第一个记录后,还会继续去查找下一个记录,直到碰到第一个不满足该条件的记录;而对于唯一索引来说,一旦找到一个满足条件的记录后,就会立即停止继续检索。

不过这一点性能差距几乎是微乎其微,因为 InnoDB 存储引擎是按页进行读写的,所以说,当它找到符合某个条件的记录的时候,这条记录所在的数据页就已经都在内存里了。对于普通索引来说,无非就是再移动一次指针罢了。

真正能够区分唯一索引和普通索引差距的,在于 Insert Buffer / Change Buffer 的存在,因为它们只适用于非唯一的辅助索引。

以 Insert Buffer 为例,当要插入的索引页不在缓冲池的时候,存储引擎并不会每插入一个新数据就去离散地访问一次磁盘页,而是先将这个操作存储到 Insert Buffer 中,在下次查询需要访问这个数据的时候,存储引擎才会将其合并(Merge)到真正的辅助索引中。这时,就相当于将多个叶子节点插入操作合并到一个操作中,这就大大提高了对于辅助索引的插入性能。

所以,在平常使用中,对于写多读少的业务,因为页面在写完以后马上被访问到的概率比较小,那么 Merge 操作就不会被频繁的执行,所以这个时候 使用非唯一索引的性能就优于唯一索引。


流水不争先,争的是滔滔不绝,我是小牛肉,小伙伴们下篇文章再见 👋

浏览 30
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报