9 - 普通索引和唯一索引的选择

关键字

普通索引，唯一索引，change buffer，查询，更新

0.引子

在 MySQL 中的索引可以大致分为来两类：普通索引和唯一索引。

普通索引：为索引项建立 B+ 树，加速数据查询的效率。
唯一索引：在普通索引的基础上，对索引项添加了约束，即索引项目不可重复。如果在添加数据时，索引项重复，数据库会拒绝这个请求。

你可以看出，普通索引和唯一索引存在功能上的差距。除此之外，它们的性能还有很大的不同，今天，就从这两种索引的查询语句和更新语句的性能影响来帮助你决策如何选择索引类型。

1.查询过程

首先，回顾一下之前的内容，在 InnoDB 中，它的索引结构如下：

左边为主键索引，右边为用户添加的索引

假设，我们执行的查询语句是：

 select id from T where k=5

这个语句会从 B+树的树根开始，按层搜索到叶子节点，最终到达数据页，然后通过二分查找定位记录。在最后的过程中，两种索引会出现差异：

普通索引：找到第一个（5，500）记录后，依然会向后查找，直到找到不满足 k=5 的记录。
唯一索引：由于唯一性的约束，在找到（5，500）之后，查询就结束了。

这两者的性能差距会有多少呢？

答案是：微乎其微，因为数据会将磁盘中的数据页（这涉及到操作系统的文件系统的处理方式，一般操作系统都是使用段页式存储文件，这其中的数据页，可能就是操作系统中的页的概念。）整块读入内存，在 InnoDB 中，每个数据页的大小默认是 16KB 。

也就是说，两种索引的查询方式的差异，也就是在内存中多读取一次数据的差异而已，这个操作成本对于 CPU 来说可以忽略不记。当然，如果你查询的数据恰好在页的末尾，可能会多进行一次磁盘操作，但是这种情况出现的概率非常小。

2.更新过程

要理解两种索引在更新中的差异，首先要理解一个概念：change buffer。

2.1change buffer

当我们对一个数据进行更新时，会出现两种情况：

该数据的数据页已经在内存中，我们可以直接在内存中进行更新。
该数据不在内存中，InnoDB 会将这个操作缓存在 change buffer 中。注意，此时数据库中的实际数据还没有发生改变，在之后系统会将 change buffer 中的数据存入到数据库中。

将 change buffer 的操作应用到原始数据页的操作，称为 merge。一般情况下，访问数据页会触发 merge 操作；同时，后台线程也会定期进行 merge，在数据关闭的过程中，也会进行 merge。

显然，使用 change buffer 可以减少磁盘操作，语句的执行速度也会得到提升。

2.2两种索引和 change buffer

唯一索引
对于唯一索引来说，它无法使用 change buffer ，因为唯一索引会在数据插入时检查该数据是否违反了唯一性约束，这就要求它必须从磁盘中将数据读出，判断数据是否重复。

既然唯一索引的插入必须读取磁盘，change buffer 的缓存作用也就没有了，反而徒增了操纵 change buffer 的消耗。

普通索引
对普通索引来说，如果要更新的内存页不在内存中，只需要将更新记录放入 change buffer，语句执行就结束了，这大大提升了性能。

所以，对于普通索引来说，可以使用 change buffer 提升性能，但是 change buffer 也有自己的适用场景。

3.change buffer 的使用场景

merge 是真正数据更新的时刻，而 change buffer 的主要目的是将变更记录缓存下来。所以，在 merge之前，change buffer 内的记录越多，收益就越大。
因此，对于写多读少的业务来说，页面写完之后马上访问的概率较小，此时使用 change buffer 的效果也最好。常见的业务模型有账单类、日志类系统。
反过来，如果在变更之后立即会对数据进行访问，change buffer 不仅没有提升性能，反而起到了副作用。
对于上面的情况，你应该主动关闭 change buffer：change buffer 使用了 buffer pool 中的内存，可以通过 innodb_change_buffer_max_size 设置它在 buffer pool 中的使用占比。
实际中，普通索引 + change buffer 对更新量大的数据表的优化还是很明显的。

4.change buffer 和 redo log

其实在学习日志系统的时候，我一直认为 redo log 的功能是提供数据缓存，而实际上实现这个功能的是 change buffer 。那么，这两个容易混淆的概念该怎么区分呢？

假设我们要在一个表上执行下面的插入语句：

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

在这里，假设 k1 的数据页在内存（InnoDB buffer pool）中，k2 的数据页不再内存中，下图就是带 change buffer 的更新状态：

9-带changebuffer的更新过程.png

对于这个过程的描述，我将专栏作者的描述放在下面：

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
将上述两个动作记入 redo log 中（图中 3 和 4）。

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

如果我们要执行 select * from t where k in (k1, k2) ，假设内存中的数据依然还在，那么它的执行如下：

9-带change buffer的读过程.png

描述如下：

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

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

综合我在上面两个加粗的地方，可以看出 redo log 和 change buffer 的区别：redo log 将更新记录缓存下来，并在事务执行完成之后（一般建议这么做，你可以通过innodb_flush_log_at_trx_commit = 1 设置每个事务执行后就将redo log 写入磁盘。）一次性顺序将日志写入磁盘。change buffer 节省从磁盘中读取数据的 IO 消耗。

总结

唯一索引和普通索引在数据搜索上的很小。
change buffer 可以缓存数据变更操作。
普通索引 + change buffer 可以大大提升频繁数据写入的情况。
redo log 可以缓存日志，并节省写磁盘的 IO 操作。
如果业务能够保证数据不重复，建议使用普通索引。

课后思考

通过图9-带changebuffer的更新过程.png 你可以看到，change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢？

答案会在下一篇文章公布。

上期问题

在下面两种情况下，会出现上一节的问题：

9-课后问答答案1.png

9-课后问答答案2.png

以上就是本节内容，希望对你有所帮助。

注：本文章的主要内容来自我对极客时间app的《MySQL实战45讲》专栏的总结，我使用了大量的原文、代码和截图，如果想要了解具体内容，可以前往极客时间