1.引例

假设现在维护着一个市民系统，每个人都有唯一的一个身份证号，而且业务代码已经保证了不会写入两个重复的身份证号，如果市民系统需要按照身份证号查姓名，就会执行下面的SQL语句：

mysql> select name from CUser where id_card = 'xxxxyyyzzzzaaaaaaa'

因为身份证号码比较长，所以不建议作为主键。那么肯定会需要在上面建索引，那是选择唯一索引还是普通索引呢？

从业务上来说，两种索引都可以。但是从性能角度看，是该选择唯一索引还是普通索引呢？

为了简单起见，还是用前面的例子来说明。

2.查询过程

假设执行如下的查询语句：

mysql> select id from T where k=5;

这个查询语句会在索引树上采用二分法进行查找。

• 对于普通索引，查找到满足条件的第一个记录(5,500)之后，会继续查找，直到碰到第一个k不等于5的记录
• 对于唯一索引，由于索引定义了字段的唯一性，查到第一个满足的之后，就会停止查询。

因为InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是只读这条记录，而是将所在的数据页整体读入内存。

因此，对于普通索引来说，要做的只是在内存的当前的数据页中，多读几行数据。但是如果该行数据刚好是数据页中的最后一行，需要读取下一个数据页，那就会麻烦一点，但是这种概率是很低的。

因此，从查询过程来看，普通索引和唯一索引的性能差距是微乎其微的。

3.更新过程

为了介绍更新过程，先引入change buffer。

当需要更新一个数据页的时候，如果数据页在内存中就直接更新。而如果这个数据页不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要将数据页读入内存进行更新，在下次需要访问这个数据页的时候，再将数据页读入内存，更新数据页。

需要注意的是，虽然名字叫change buffer，实际上它是可以持久化数据的，也会被写入到磁盘上的。

将change buffer中的操作应用到原数据页，得到最新的结果的过程称为merge。除了访问数据页会进行merge，系统后台也会定期进行merge，关闭数据库也会进行merge。

因此，通过change buffer，减少读磁盘操作，语句的执行效率会得到明显提升。

change buffer用的是buffer pool里的内存，不能无限增大，通过innodb_change_buffer_max_size可以进行动态设置。

那什么条件下可以使用change buffer呢？
对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一性约束。但是这必须要读入内存才能判断。如果都已经读到内存了，那直接更新内存会更快，就没必要使用change buffer了。
实际上也只有普通索引才能用得上change buffer。

假设我们现在要在这张表中插入一个新纪录(4,400)的话，InnoDB的处理流程是这样的：

第一种情况，这个记录要更新的目标页在内存中：

1. 对于唯一索引，找到3和5的中间位置，判断有没有冲突，执行插入
2. 对于普通索引，找到3和5的中间位置，直接插入

对于这种情况，两者几乎没有性能差距。

第二种情况，这个记录要更新的目标页不在内存中

1. 对于唯一索引，需要将数据页读入到内存中，判断到没有冲突，才插入
2. 对于普通索引，将更新记录在change buffer中即可

这种情况下，对于普通索引不需要读取磁盘，就显著提高了性能。

3.change buffer的使用场景

下面思考一个问题，对于普通索引的所有场景，使用change buffer都能起到加速作用吗？

change buffer的主要目的就是将更新操作缓存下来，所以 在进行merge之前，缓存的操作阅读，收益越大。

对于写多读少的业务，change buffer的使用效果就比较好。
对于更新之后，马上就会进行查询的业务，更新操作记录在change buffer中，但是马上查询又要将数据页读入内存，立即触发merge。这样反而增加了change buffer的维护成本。

4.索引的选择和实践

普通索引和唯一索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以建议尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭change buffer。 而在其他情况下，change buffer都能提升更新性能。

5.change buffer和redo log

我们可能会发现redo log和change buffer很类似。因为对于redo log，Write-Ahead logging（WAL）技术也是尽量减少对于磁盘的读写。

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

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

这里我们假设k1所在的数据页在内存中，k2所在的数据页不在内存中。下图是更新流程图：

这条语句会涉及四个部分：内存、redo log（ib_log_file）、数据表空间(t.ibd)、系统表空间(ibdata1)。下面是具体的执行流程：

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

上述操作都是只在内存中进行的，所以速度很快。图中的虚线是在后台运行的， 不影响执行时间。

如果我们后面继续执行读请求，如下面的语句

select * from t where k in (k1,k2)

下面是请求执行的流程图：

具体流程包括以下两步：

1. 读Page1的时候，直接从内存返回
2. 读Page2的时候，需要将Page2读入内存，然后执行change buffer中的操作，生成一个正确的结果。

因此可以看到直到需要读的时候，Page2才会被调入内存。

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