如在我们的用户表中，每个用户都有一个身份证号，用户在注册或者是身份信息认证时，业务代码已校验了这个身份证号的唯一性。

当经常会有根据用户身份证号来查用户信息时，一般会在 身份证号(id_card)上建立索引。

select username from tb_user where id_card=''

给id_card 字段创建索引可以考虑唯一索引或者是普通索引。

1 从查询角度来分析

如执行查询语句如下：

select username from tb_user where id_card='740'

如果是普通索引，查询过程如下所示

从 id_card 索引树上查询到 id_card为740这一行然后回表获取这一行数据，然后再向下查询一次，不满足条件，终结查询。

如果是唯一索引，查询过程如下图所示

对于唯一索引来讲，索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

在这个查询过程中，这两种选择的区别只是“查找和判断下一条记录”的操作，只是一次指针寻找和一次计算，性能消耗差异对于现在的 CPU 来说可以忽略不计。

2 从修改数据角度来分析

InnoDB引擎是按数据页为单位来读写数据的，InnoDB引擎是以页为单位，从磁盘读出来，然后以页为单位，将其整体读入内存，在 InnoDB 中，每个数据页的大小默认是 16KB。

所以在更新数据时，如果数据页在内存中就直接更新，否则会将这些更新操作缓存在 change buffer 中，先不作从磁盘中读入数据页，减少读磁盘，语句的执行速度会得到 明显的提升。

然后在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge，同时系统有后台线程会定期 merge，当然在数据库正常关闭 (shutdown)的过程中，也会执行 merge 操作。

2.1 唯一索引的更新

唯一索引的更新就不能使用 change buffer。

因为 唯一索引来，所有的更新操作都要先判断这个操作是否违反唯一性约束，如要插入 id_card 为 740的用户数据，而数据库中已有740这个唯一身份证，就不能进行操作，而这个过程必须要将数据页读入内存才能判断。

2.2 插入新值时

如插入id_card 为 740的用户数据，需要插入的目标页在内存中，

对于唯一索引来说，找到 739 和 741之间的位置，判断到没有冲突，插入这个值，语句执行

结束;

对于普通索引来说，找到 739 和 741之间的位置，插入这个值，语句执行结束。

两者的差别就是一次的冲突判断，性能方面可以忽略。

当需要插入的目标页不在内存中

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结

束;

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

从磁盘读数据内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一，change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

3 索引选择

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。

在业务开发中，如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer，反之则应该开启。

完毕

不局限于思维，不局限语言限制，才是编程的最高境界。