持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第12天，点击查看活动详情
考虑维护一个市民系统，每个人有一个唯一的身份证，现在需要通过身份证查姓名，就需要执行
Select name from Cuser where id_card = 'xaaxadascjacahc'
我们就要考虑在 id_card 字段上建索引，但是身份证号字段较大，不适合作为主键，所以要么给 id_card 字段建立唯一索引，要么建立一个普通索引。如果业务代码能保证不会写入重复的身份证号，那么两种选择逻辑上都是对的
这两种索引在查询和更新语句的性能比较
1 查询语句
在 B+树中逐层搜索到叶子节点，然后在数据页通过二分法来定位记录
- 对于普通索引后，查找到第一个满足条件的记录后需要查找下一条记录，直到碰到第一个不满足条件的记录
- 对于唯一索引来说，查找到第一个满足条件的记录后就会停止检索
但是她们带来的性能差距微乎其微。因为 InnoDB 的数据是按数据页为单位来写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘中读取出来，而是以页为单位，将其整体读入内存中。在 InnoDB 中，每个数据页的大小默认是 16 KB。
所以在读取记录的时候，它所在的数据页都在内存中了，普通索引页只需要多做一次查找和判断下一条记录的操作即可，也就是一次指针寻找和一次计算。如果这条数据刚好是这个数据页的最后一个记录，那么取下一个记录，就需要读取下一个数据页，操作复杂些，但是对于整型字段，一个数据页可以放近千个 key,所以出现这种情况概率很低，总体来说普通索引和唯一索引性能相差不大
2 更新语句
2.1 Change Buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
所以如果能将更新操作记录在 change buffer 中，就能减少读磁盘，语句的执行速度就会明显提升。
- 唯一索引来说，所有的更新操作都要判断这个操作是否违反唯一性约束，比如要插入 （4，400）这个记录，要先判断现在表中是否已经存在 k=4 的记录，而这必须将数据读入内存中才能判断，如果已经读到内存中，就没必要用 change buffer 了（所有的更新操作之前需要进行判断，就需要读取数据页到内存中，不需要使用 change buffer）
- 普通索引可以使用 change buffer
2.2 性能分析
- 更新的目标在内存中
差异不大
- 更新的目标不在内存中
- 唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
- 普通索引：将更新记录在 change buffer，语句执行就结束了
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。所以普通索引更好一些
3 Change Buffer 与普通索引
普通索引的所有使用场景都能使用 change buffer 吗？
我们回到 change buffer，将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge，除了访问数据页的时候会触发 merge 以外，系统有后台线程会定期 merge，在数据库正常关闭的过程中也会触发 merge。change buffer 的主要目的是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更（这个页面上要变更的次数越多）越多越好
因此对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好，例如账单类，日志类的系统
如果某个业务写入后立马做查询，即使记录在 change buffer 中，但由于立刻需要访问这个数据也，立即出发 merge，随机访问 I/O 并没有减少，反而徒增 change buffer 维护代价，所以不适合使用 change buffer
4 redo log 和 change buffer
redo log 主要节省的是随机写磁盘的 IO 消耗（转为顺序写），而 change buffer 主要节省的是随机读磁盘的 IO 消耗
5 小结
在查询能力上两个索引没差别，更新的时候有差别，尽量选择普通索引而不是唯一索引。如果所有的更新后面都会伴随着记录的查询，那么关闭 change buffer，否则 change buffer 能提升更新性能
