阿里一面，唯一索引和普通索引如何选择？

因为明年也要准备秋招了，最近准备以面试为导向开始写文章了，考虑到目前网络上提供的面试题大部分都是短短的几行字，也即背诵版，我觉得这无法满足大部分同学的诉求，知其然而不知所以然，我希望的是以面试题为导向，建立完整的知识体系，而不是东一锤西一棒，所以后续准备以牛客上的面经帖为导向，对每个面试题提供背诵版 + 详解版，已经会的同学呢可以直接看背诵版，还不太了解的同学呢可以结合详解版一起看，目前已经做了一部分汇总在 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 操作就不会被频繁的执行，所以这个时候 使用非唯一索引的性能就优于唯一索引。

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