唯一索引与普通索引

本文分析一下普通索引和唯一索引的区别和选择，还是以以下表为例。

查询

如果执行的查询语句是select id from T where k=5；那么

• 普通索引：查到满足条件的(5,500)后，查找下一条记录，一直到碰到第一个满足k不等于5的记录。
• 唯一索引：找到第一条记录后，停止检索。

这两个查询带来的差距实际上是很小的。InnoDB是按照数据页为单位读写的，那么一般你要查询下一个的话，开销不大。当然，如果你刚好查到的k=5是数据页的最后一条，就要复杂一点了，但是概率很低。总体来说，在查询上，二者差别不大。

更新

在此之前，需要介绍下change buffer的意义。当我们要更新一个记录时，如果这条记录所在的数据页在内存中，那么直接更新内存就好了。如果不在的话，就把更新操作的缓存放在change buffer中，这样就不用从磁盘中读入数据页了，注意仅仅只是更新的操作，不是更新后的具体值。如果下次需要读取的话，那么就必须要从磁盘中读入数据页了，然后执行change buffer的操作。

需要注意的是，change buffer也是可以持久化的数据，也可以存在磁盘中。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。访问这个数据页、后台定期更新、数据库正常关闭都会触发merge。

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；

2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页；

3. 写redo log。这个redo log包含了数据的变更和change buffer的变更。

到这里merge过程就结束了。

所以change buffer在更新时能减少读磁盘的次数，也能减少读取时占用的buffer pool。

 

唯一索引不能使用change buffer

因为唯一索引所有的更新操作，就要判断是否是唯一的，那么就要加载数据页，那肯定就用不上change buffer了。

下面我们来看看处理更新操作。如果要在表中插入一条(4,400)的记录，流程如下：

第一种情况，如果更新的目标页在内存中，那么：

1. 唯一索引，找到3和5之间的位置，判断没有冲突，插入这个值。
2. 普通索引，找到3和5之间的位置，插入这个值。

第二种情况，如果不在内存中，那么：

1. 唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值。
2. 普通索引，将更新记录在change buffer，语句执行就结束了。

change buffer确实可以在普通索引的场景下，起到加速的作用，但是有一种情况不能，就是如果你每次在更新后，都要读取的话，那么久会不断得merge，这样访问磁盘的次数不会减少，反而还多了维护change buffer的代价。

 

change buffer 和 redo log

change buffer 和 redo log的区别，用一个例子来介绍。现在要在表中插入insert into t(id,k) values(id1,k1),(id2,k2); 假设k1所在是数据页在内存中，k2的数据页不在内存中。

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

1. Page 1在内存中，直接更新内存；

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

3. 将上述两个动作记入redo log中（图中3和4）。

总的来说，这条语句，写了两次内存，一次磁盘（两次操作合并起来写了一次磁盘的redo log）。而且虚线的箭头，是后台操作的，都是异步定期落盘。以上就是一个写的过程。

接下来看一个读的过程。执行 select * from t where k in (k1, k2)

从图中可以看到：

1. 读Page 1的时候，直接从内存返回。

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

所以， redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

Ps. 如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据。虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。