面试官问：普通索引和唯一索引该怎么选择？

如果面试官问你：“在mysql中，普通索引和唯一索引你是如何做选择的？”，你会不会觉得很容易：两者都可以利用索引的特性，来加速数的查询的效率。不同之处在于，唯一索引能够保证索引字段或者字段集合的唯一性，如果插入的数据或者更新后的数据与已有数据存在重复，则会产生唯一键冲突，导致插入或者更新失败，而普通索引则不具备这种特性。看似很完备的回答，其实是没有达到面试官的要求的，如果面试官再问:“如果我在业务层面保证了插入数据或者更新后数据的唯一性，那唯一索引和普通索引是不是就没有区别了呢？”，面试官一下就把你回答的"两者差异"，给打平了，那接下来，你又该怎么回答呢？

回答这个问题，如果仅仅从语义层面表现出来的差异做选择和区分的话，会显得你对知识的掌握只停留在会用的层面。回答问题，可以从工作中经常用的查询和更新操作入手，分析查询过程和更新过程中，mysql内部对两者实现的差异来分析，这样更能打动面试官。

为了便于后面的描述和分析，建立如下表结构：

CREATE TABLE `test1` (
  `id` int(11) NOT NULL not null auto_increment,
  `a` int(11) ,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB;

建立名称为test1的表，id为主键和字段a。我们在业务层面可以保证字段a的唯一性，比如字段a为手机号码，身份证号等。
初始化一些数据：

insert into test1 (a) values (1),(2),(4),(8);

我们在字段a上添加索引，此时无论在a上添加唯一索引还是普通索引，a的索引结构图大致都会如下图所示：

查询过程

执行如下查询语句

select * from test1 where a= 2

对于普通索引：当查到第一个满足条件的记录后，会继续查找下一个记录，直到找到第一个不满足条件的记录。

对于唯一索引：当查到账到第一个满足条件的记录后，就会停止检索，因为数据库引擎认为，数据表中最多只可能有一个满足条件的数据。

在上述的查询过程中，普通索引相比唯一索引，多增加的开销是：检索到满足条件的第一个记录后到检索到第一个不满足条件的记录之间的检索开销。因为我们已经在业务层面保证数据的唯一性，所以检索到第一个不满足条件的记录的开销就是判断一下，下一条记录是否满足条件。

而在innoDB中，数据的是以页为单位来进行管理，默认情况下，一个页的大小是16kb(遗憾的是，mysql没有对外暴露参数，对页大小进行配置)，当需要读取一条记录时，会把这条记录所在的整个页都加载到内存中。

所以，当找到a=2的记录时，会把其所在数据页都会加载到内存中，那么对于普通索引来说，多做的那次查找和判断下一条记录,仅仅就是一次指针寻找和一次计算的操作，这种操作的开销微乎其微。

如果很不幸，a=2的那条记录是所在页的最后一条记录的话，那么查找下一条记录，就必须读取下一个数据页，这个操作相对会复杂一些。不过一个数据页可以存放的索引key有近千个，因此出现这种不幸的情况的概率还是很小的。因此，唯一索引和普通索引的查询性能上几乎没有差异。

更新过程

在比较普通索引和唯一索引在更新过程中的差异之前，我们先学习下，InnoDB对更新操作的一个性能优化：当需要更新一个数据记录时，如果这条记录所在的数据页已经加载到内存中的话，那么就直接在数据页中更新。如果记录所在数据页不在内存中的话，在不影响一致性的前提下，InnoDB会将这些更新操作缓存到change buffer中，而不是将更新的操作直接应用到目标记录上。这样可以减少在对数据记录更新时，数据页加载到内存的次数，也即是访问磁盘的次数。当这个被更新记录所在的数据页，再次被访问的时候，为了保证数据的一致性，此时会将该数据页加载到内存，然后将change buffer中的变更应用到数据页上，最终完成了数据的更新。这个将change buffer中的变更应用到数据页的操作，被称为merge。

以上过程，对于执行更新操作的客户端来说，当将更新写到change buffer后，更新操作就算完成了，也就是一次更新内存的操作耗时，响应时间很短，对客户端比较友好。

这里说明一下，change buffer所占用的空间，其实是buffer pool的一部分，为了避免change buffer占用过多的空间，可以使用参数 innodb_change_buffer_max_size来设置change_buffer最多可以占用buffer_pool总空间大小的百分比，如果该参数的值为30，则表示change buffer的大小，最大为buffer pool总大小的30%。这里使用change buffer对更新操作性能优化过程，主要就是利用内存空间记录更新操作，减少数据页加载到内存的次数，本质上是空间换时间的一种做法。

了解了change buffer的工作机制后，那么向表中插入一条新纪录 a=3 的话，InnoDB的处理流程是怎样的呢？

对于普通索引：找到a=2和a=4之间的位置，插入这条记录，语句执行结束。

对于唯一索引：找到a=3和a=4之间的位置，然后判断是否存在a=3的记录，如果不存在话，插入这条记录，语句执行结束。

如果从上面更新操作的执行流程来看的话，普通索引和唯一索引的差异，仅仅就是一个判断操作，性能上也几乎没有什么差异。但是有了change buffer机制后，两者的差异就不仅仅是一个判断操作了。

首先我们深入思考一下，在上面的更新流程中，无论是普通索引还是唯一索引，都需要找到新纪录需要插入的位置，然后将新纪录插入进去，在没有change buffer时，这个过程需要将内存页加载到内存中，而当有了change buffer后：

对于普通索引来说，就不需要再将数据页，加载到内存中了，而是直接将插入操作记录到change buffer中，然后完成整个插入操作。此时插入操作就变成了一次内存操作了。

而对于唯一索引来说，如果也按照普通索引的插入操作流程执行的话，可能会出现：客户端认为数据插入成功了(成功写入了change buffer)，但是change buffer执行merge时，出现唯一性冲突。为了防止这种情况的出现，在进行插入操作时，就需要执行判断是否存在唯一性冲突的操作，而这个操作的执行，就需要将对应的数据页加载的内存中，既然数据页都加载到了内存中了，change buffer也就没有意义了。

到这里，我们大概也就清楚了，在使用change buffer优化后，对插入操作来说，使用普通索引要比唯一索引性能要好很多，尤其是在大量数据插入的情况下。

总结

了解了change buffer的工作原理后，我们知道，对于更新操作，只有当执行merge操作时，才会将数据页加载到内存中，完成数据最终的更新操作。所以merge操作的执行频率直接决定了，change buffer对性能优化的收益情况。如果在执行merge前，change buffer中的记录越多，那么使用change buffer的收益就越好，所以对于写多读少的业务来说，使用change buffer的效果比较好。而对于更新操作执行后，马上执行查询操作的业务来说，使用change buffer并不会减少访问磁盘的次数，而且还增加了change buffer的维护代价，对于这种情况，使用change buffer反而起到了副作用。

工作中，你有用到过change buffer吗？change buffer设置多大呢？欢迎留言讨论。