MySQL普通索引和唯一索引的区别

对于普通索引和唯一索引的使用，对于一个开发来说，我相信更多的区别场景就是：如果需要在库里面做一个唯一限制，就使用唯一索引；否则就使用普通索引了。下面主要介绍一下这 2 种索引的区别（使用MySQL时，大多数场景下使用的是B+ 索引，下面的案例或者描述默认都是 B+ 索引）。

查询流程区别

当我们执行 select * from T where k = 3 时，首先会从 B+ 索引树的树根开始遍历。

1、普通索引：当遍历到 k = 3 的叶子节点后，会继续查询下一个叶子节点是否满足条件，不满足则返回结果集，否则继续查找。

2、唯一索引：当找到 k = 3 的叶子节点后，因为唯一性和等值的查询，此时就会直接返回结果集。

InnoDB 是按照数据页的维度去进行读写的（默认 16KB），所以大多数场景下，普通索引和唯一索引没有什么区别，也就是多了一次CPU计算。当然对于普通索引来讲，有可能读取下一条的数据在第二个数据页上，此时就会多一次 随机IO 访问。这样看上去可能会相对耗时一点，但是这种场景出现的频率相对较少，可以忽略不计。

更新过程区别

change buffer

当需要更新一个数据页时，如果数据在内存中就直接更新。如果数据页还没有在内存中的话，在不影响数据已执行的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次需要访问到这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个数据页相关的逻辑，就能保证查询的正确性。

将 change buffer 中的操作应用到原数据页，得到最近的结果叫做 merge 。除了访问会触发 merge 之外，系统还会有线程定期的 merge。在数据库正常关闭的过程中，也会触发 merge。从上面可以看出，如果能够将更新操作记录在 change buffer ，可以减少 随机磁盘访问，从而提高性能。并且数据页读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，为了检查唯一性约束，此时必须要将数据页装载到内存，此时直接更新内存更快，就不会使用change buffer 了。因此，只有普通索引可以使用 change buffer，唯一索引不会使用。

change buffer 使用的是 buffer pool 中的内存，因此是有限制的。我们可以通过 innodb_change_buffer_max_size 来动态设置，设置为 50 时表示：change buffer 最多可占用 buffer pool 中的50% 。

从上面可以看出，当我们插入一条数据时。对于普通索引和 唯一索引有如下区别：

1、唯一索引：会首先将数据页载入内存，然后判断是否违反唯一性约束，若不违反则插入数据。

2、普通索引：会将数据直接更新到 change buffer 中，然后就结束了。此时就减少了一次随机IO 的访问，提高了性能。

索引选择

对于读多写少的业务，数据页在写完之后被访问到的可能性比较小，此时使用 change buffer 的效果比较好。常见的有日志、账单类等。反过来，如果数据写入之后立马就会读取，此时开启了 change buffer 就会增加内存开销。

普通索引 + 开启 change buffer 在机械硬盘的机器上，在数据量比较大的时候，可以有性能的明显提升。

change buffer 与 redo log

介绍完 change buffer 的原理之后，下面介绍一下其与 redo log 之间的关系。如在数据库中执行如下语句：

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

假设 k1 所在的数据页在 change buffer 中，k2 所在的数据页不在 change buffer 中，则更新过程如下所示：

从上图可以看出，主要涉及了 4 部分内容：内存（buffer pool）、redo log (ib_log_fileX)、数据表空间、系统表空间。其执行流程如下：

1. k1 对应的数据页 Page1 在内存中，直接更新。

2. k2 对应的数据页 Page2 不在内存中，就往内存的 change buffer 中记录 add 操作。

3. 分别记录上述 2 个步骤的 redo log。

执行上述流程之后，插入操作就做完了。上图中虚线的部分是异步的后台操作，不影响客户端的响应时间。

如果在插入请求之后，再次查询时，就会有下图所示的执行流程：

从上图的执行流程可以看出，读取 k1 时，直接从 Page1 的内存中返回；读取 k2 时，先从 磁盘中读取 Page2 到内存，然后合并 change buffer 中的变更，然后返回结果。

从上面 redo log 和 change buffer 对性能的提升：redo log 可以减少随机磁盘IO 的写入，change buffer 可以减少随机磁盘IO 的读取。

---

参考：《极客时间：MySQL实战》、《高性能MySQL》
链接：http://moguhu.com/article/detail?articleId=120