MySQL唯一索引和普通索引运行原理和使用选择

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。

唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

这两种索引的运行原理

查询过程

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

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

所以在这里你感觉用唯一性索引会快一些，毕竟少了一个步骤。但是这个不同带来的性能差距微乎其微。

你知道的，InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

因为引擎是按页读写的，所以说，当找到符合条件的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。 

当然也会有特殊情况，就是符合条件的记录正好处于数据页的最后一个，那往下查找的操作就会拿下一个数据页放进内存，这个时候就会慢了，但是一个整型字段，一个数据页可以放进千的key，所以这个概率很低。

更新过程

Change buffer。

两种索引更新过程主要差别就是因为Change buffer。

Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。

当InooDB更新一个数据页的时候有2中情况：

1. 数据页在内存中，此时直接更新。
2. 数据页不在内存中，这时候 InooDB 会将这些更新操作缓存在 change buffer 中，然后在下次需要访问这个数据页的时候，将数据页放入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。这种情况在更新操作时省去了把数据页从磁盘读入内存这一步，而是在以后访问这个数据页的时候再做更新操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。 

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

buffer pool： Innodb维护了一个缓存区域叫做Buffer Pool，用来缓存数据和索引在内存中。Buffer Pool可以用来加速数据的读写，如果Buffer Pool越大，那么Mysql就越像一个内存数据库，所以了解Buffer Pool的配置可以提高Buffer Pool的性能。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

这个 change buffer普通索引会用到，唯一索引用不到，因为唯一索引的更新操作之前都要判断唯一性，所以在判断这步已经把数据页放在了内存里，所以之后的更新操作就直接在内存里操作了，内存更新更快，没必要用change buffer了。

所以，普通索引会用到 change buffer。

索引具体的处理流程

清楚了change buffer然后模拟一个场景来看一下两种索引具体的处理流程是怎样的。

如果要在这张表中插入一个 id=5的新纪录，InnoDB 的处理流程是怎样的。

第一种情况：目标数据页在内存中。

• 唯一索引：找到 4 和 6 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 普通索引：找到 4 和 6 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是：目标数据页不在内存中

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

主要区别就是唯一索引需要把磁盘中的数据页放入内存。就是这步影响了性能。

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

但是普通索引用change buffer起到加速作用也是有应用场景的。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

由此看来就是对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

所以反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引使用选择

从上面的内容来说，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

唯一索引使用的问题，主要是纠结在“业务可能无法确保”的情况。
首先，业务正确性优先。如果业务不能保证数据的唯一性，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。本篇文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。