【MySQL 读书笔记】普通索引和唯一索引应该怎么选择

通常我们在做这个选择的时候，考虑得最多的应该是如果我们需要让 Database MySQL 来帮助我们从数据库层面过滤掉对应字段的重复数据我们会选择唯一索引，如果没有前者的需求，一般都会使用普通索引。这篇文章将会站在性能的角度来分析一下两者的区别对性能的影响。

这里还是用一张之前分析索引用到的图。

 

查询过程

在我们查询的时候我们使用 select id from T where k=5。这个查询语句通过查询逐渐搜索到 B+Tree 的叶子节点，然后取到对应的数据页，然后在数据页内部找到对应记录。我记得没错的话数据页内部似乎是链表形式存储的。

对于普通索引来说，查找到对应满足的条件 500 之后，还需要找下一个记录，直到碰到第一个不满足 k=5 的条件记录。

对于唯一索引来说，查找到第一条满足条件的记录之后，就会立即停止继续检索。

这两者带来的性能差距是微乎其微的。

首先我们将数据页从磁盘里面读出来是读出整个16kb 的数据页，那么当我们在找到第一个满足条件的记录的时候其实绝大多数情况我们要读取的下一个记录也在内存中。即使不在，我们也只需要再读取一个数据页，并且我记得数据页之间是有指针直接可以取到下一个数据页位置的。这个优化进一步优化了读取连续数据页的性能，可以认为这样的操作成本很低。

 

更新过程

为了说明普通索引和唯一索引对更新语句性能的影响，我们先来普及一下 change buffer 这个概念。

当我们需要更新一个数据页的时候，如果数据也在内存中久直接更新，而如果这个数据页没有在内存中，在不影响数据一致性的情况下， InnoDB 会将这些更新操作缓存在 change buffer 中。这样就不需要立即取磁盘中读取这个数据页进行engine了。在下次需要访问这个数据页的时候，再将数据页读入内存，然后执行 change buffer 中相关数据页的操作。这种方式就能保证数据逻辑的正确性，并且节省随机读取 IO 消耗，而不是进行频繁随机读取。这里要特别注意，随机读写可能是数据库里面消耗最高的操作了。

需要说明的是，虽然名字叫作 change buffer 实际上它是可以持久化的数据。也就是说， change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新的结果的过程称为 merge。除了访问这个数据页会触发 merge 外系统有后台线程会定期 merge。在数据库正常关闭的过程中，也会触发 merge 操作。

数据读入内存是需要占用 buffer pool 的，如果我们需要更新的操作记录在 change buffer ，可以减少读磁盘，而且这种方式可以用避免短时间内占用内存，提高内存利用率。

 

那么哪些情况下可以使用 change buffer 呢？

对于唯一索引的情况所有的更新情况都要判断是否会违反唯一性约束，比如我们在插入一条记录的时候，我们需要先判断是否已经存在这条记录，只要有我们去扫表才能判断，我们就需要把对应的数据页读入内存，如果已经读入内存，如果已经读入内存就直接更新插入就行。没有必要去使用 change buffer ，反正都必须先读入内存。

因此，唯一索引的更新就不能使用 change buffer 这个东西。实际上就只有普通索引可以使用 change buffer.

change buffer  用的是 InnoDB buffer pool 里面的内存，change buffer 的大小可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数默认是 25。表示最多占用 buffer pool 百分之 25 应用于作 change buffer。

 

Change Buffer 的使用场景

通过上面的分析，你已经清楚了使用 change buffer 对更新过程的加速作用，也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。那么，现在有一个问题就是：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

 

所以实际情况中，如果是并发量不高的业务，还是在底层对数据做保护不要犹豫，直接选用唯一索引是比较好的选择。如果是对更新性能要求极高的场景，可以考虑建普通索引，然后在代码里面对唯一性的情况进行保护。

 

change buffer 和 redo log

理解了 change buffer 的原理，你可能会联想到我在前面文章中和你介绍过的 redo log 和 WAL。

在前面文章的评论中，我发现有同学混淆了 redo log 和 change buffer。WAL 提升性能的核心机制，也的确是尽量减少随机读写，这两个概念确实容易混淆。所以，这里我把它们放到了同一个流程里来说明，便于你区分这两个概念。

 

现在，我们要在表上执行插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 2 所示是带 change buffer 的更新状态图。

这条插入操作做了如下操作：

1. page 1 在内存中，直接更新内存。

2. page 2 没有在内存中，就在内存的 change buffer 区域，记录下「我要往 page 2 插入行」这个信息。

3. 将这两个动作记入 redo log 中。

做完这些事情事务就可以完成了。成本是 更新两处内存，一个 直接更新内存 一个 更新 change buffer。然后一次写 redo log 写磁盘操作。

同时图中虚线部分不是后台操作，不影响更新时间。

 

前面我说了触发 merge 除了定时 merge 正常 shutdown MySQL 以外 如果立即查询对应页上的数据也会立即触发和 change buffer 的 merge。

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。所以，我在图中就没画出这两部分。 

从图中可以看到：

1. 读 Page 1 的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL 之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用的。你可以看一下图 3 的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。

2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

可以看到直到要读 page 2 的时候，这个数据页才会被读入内存。

所以，如果要简单对比这两个机制在提升更新性能上的收益的话， redo log 主要节省的是随机写的磁盘的 io 消耗。

 

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受。从性能的角度出发应该先考虑非唯一索引。但是我前面也说了，还是看业务来，如果对性能本就没有什么要求，并且代码质量才是头等大问题，那应该毫不犹豫的使用唯一索引。可以让你避免很多麻烦。

 

 

Reference:

本读书笔记皆来自发布在极客时间的 林晓斌（丁奇）的 MySQL 实战45讲：

极客时间版权所有: https://time.geekbang.org/ 版权所有: 

https://time.geekbang.org/column/article/70848