MySQL中普通索引与唯一索引的抉择思路
唯一索引的特性是字段里的取值不允许重复。而普通索引则没有这个限制，字段值能够出现重复情况。
就像下面展示的数据那样，并且字段k上的取值都是各不相同的：
接下来，从这两种索引对查询语句和更新语句在性能方面的影响来进行分析。
查询过程
假设查询语句是select id from T where k=5。那么首先会在字段k对应的索引树上找到k=5所在的数据页，之后在数据页内部进行二分查找来定位记录。对于普通索引和唯一索引，它们的区别在于：
- 对于普通索引来说，找到符合条件的第一个记录之后，还会继续查找，直到碰到第一个不满足
k=5条件的记录才会停止。 - 而唯一索引，找到第一个满足条件的记录后，就会停止查找。
其实这个区别带来的性能差异并不是很大。因为InnoDB是按照数据页为单位来进行读写操作的，当找到k=5对应的那条记录时，它所在的数据页就已经在内存里了，所以普通索引多进行的那一次查找并没有太大的开销。就算k=5恰好是数据页的最后一条记录，查找下一条记录需要读取新的一页，但是这种情况出现的概率很低，均摊到所有情况后成本可以忽略不计。
更新过程
在分析更新过程之前，先介绍一下change buffer。
当需要对一个数据页进行更新操作时，如果这个数据页在内存当中，那就直接进行更新；要是数据页在磁盘上，在不影响数据一致性的前提下，InnoDB会把这些更新操作缓存到change buffer里。当后续查询需要访问该数据页时，会把这个数据页读入内存，然后执行change buffer中与该页相关的操作，以此来保证数据逻辑的正确性。
change buffer是在内存里的，同时也会被持久化到磁盘上。
把change buffer中的操作应用到原数据页从而得到最新结果的这个过程叫做merge。除了访问这个数据页会触发merge之外，系统还有后台线程会定期进行merge操作。在数据库正常关闭的过程中，也会执行merge操作。
可以看出，如果能够把更新操作先记录在change buffer里，就能够减少读磁盘的次数，这样语句执行的速度就会加快。而且，数据读入内存需要占用buffer pool，使用change buffer还能够避免占用内存，提高内存的利用率。
不过，并不是在所有情况下都可以使用change buffer。对于唯一索引来说，所有的更新操作都必须判断该操作是否会违反唯一性约束，比如说要插入(4,400)，就需要先判断表中是否已经存在k=4的记录，所以每次更新都得把数据页读入内存。要是数据页本身已经在内存里了，那就直接更新内存，不需要再使用change buffer了。而对于普通索引，是可以使用change buffer的。
change buffer使用的是buffer pool里的内存，不能无限增大。它的大小可以通过innodb_change_buffer_max_size这个参数来动态设置，这个参数的值表示占用buffer pool的百分比。
那咱们来看一下，如果要插入一条k=4的新记录，InnoDB的处理流程是怎样的：
- 对于唯一索引，找到3和5之间的位置，判断没有冲突，然后插入值；
- 对于普通索引，找到3和5之间的位置，直接插入值。
表面上看两者就相差一个判断，但是如果该记录要更新的目标页不在内存中，再对两者进行比较：
- 对于唯一索引，需要把数据页读入内存，判断没有冲突后，插入值；
- 对于普通索引，只需要把更新记录记录在change buffer里就可以了。
可以看到，这种情况下就相差了一次随机磁盘IO，性能差距就比较大了。
change buffer的使用场景
虽然change buffer能够用于普通索引的场景，但是并不是在所有情况下它都能起到加速的作用。
这是因为merge才是真正进行数据更新的时刻，而change buffer的主要作用是把记录的变更动作缓存起来，所以在一个数据页merge之前，change buffer记录的变更越多，收益就越大。
对于写多读少的业务场景，页面写完之后马上被访问的概率比较小，这就意味着change buffer马上merge的概率比较小，此时使用change buffer的效果比较好，像账单类、日志类系统就属于这种场景业务模型。
而对于那种写入之后马上就进行查询的场景，merge的频率比较高，这样随机访问IO的次数并不会减少，反而还增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。
索引选择和实践
再回到之前的问题：如何选择普通索引和唯一索引。
通过上面的分析可以看到，两者在查询过程中的能力几乎没有差别，主要的区别在更新过程。所以，建议尽量选择普通索引。要是遇到写入后马上查询的场景，建议关闭change buffer。
普通索引配合change buffer，对于数据量大的表的更新优化是比较明显的。特别是在使用机械硬盘的时候，change buffer机制带来的收益非常显著，所以使用机械硬盘时可以考虑加大change buffer。
change buffer和redo log
理解了change buffer的原理之后，可能会发现它和redo log有一些相似之处。
咱们再回顾一下redo log的WAL机制：MySQL的更新采用了WAL（Write-Ahead Logging）技术，关键就是先写日志，再写磁盘。具体来说，当有一条记录需要更新时，InnoDB引擎先把记录写到redo log里，并且更新内存，这时候更新就可以算完成了。之后，InnoDB会在合适的时候把这个操作记录更新到磁盘中。
接下来，用一个例子来区分这两个概念。假设要在表上执行下面这个插入语句：
insert into t(id,k) values(id1,k1),(id2,k2);
假设在k索引树找到位置之后，k1所在的数据页在内存中，而k2所在的数据页不在内存中。下面是带有change buffer的更新状态图：
分析这条更新语句：
- 发现Page 1在内存中，直接对内存进行更新；
- 发现Page 2不在内存中，就在内存的change buffer区域，记录“我要往Page 2插入一行”这个信息；
- 把上述两个动作记录到redo log中。
做完这些动作，事务就完成了。可以发现，对于这次更新，只是进行了两次内存写入和一次磁盘写入。图中的虚线箭头是后台操作，不会影响更新的响应时间。
那么这次更新之后的查询请求，会怎么处理呢？
比如要执行select * from t where k in (k1,k2)，它的读请求流程图如下：
分析这条查询语句：
- 读Page 1的时候，直接从内存返回结果。
- 读Page 2的时候，需要先把Page 2从磁盘读入内存，然后应用change buffer，生成正确的版本并返回结果。
所以，如果简单对比change buffer和redo log在提升更新性能上的收益的话：
- redo log主要节省了随机写磁盘的IO消耗，把随机写转换成了顺序写；
- change buffer节省了随机读磁盘的IO消耗。
最后，思考一下：如果某次写入使用了change buffer，之后主机异常重启，会不会丢失change buffer和数据呢？
答案是不会的，因为在写完change buffer之后，会把这个动作也记录到redo log里，之后崩溃恢复的时候也可以通过redo log找回相关数据。
相关文章
