mysql学习笔记（四）普通索引还是唯一索引

这一篇主要来分析下如何选择普通索引和唯一索引，以及他们在查询时候的原理。

一、change buffer

mysql引擎在查询的时候在存储引擎中提供了内存来帮助我们提高查询的效率。那么在语句的更新操作下有没有什么中间区域来帮助我们提高效率呢？

mysql在内存与数据页之间使用了change buffer的概念来帮助我们提示更新的效率。

change buffer的工作原理：

（1）当需要进行一个更新操作时，如果数据页在内存中则直接更新内存，由后续空闲的时候将内存中的脏页刷到数据页中。

（2）如果数据页在内存中没有数据，如果如果没有change buffer，那么就会开启一次IO操作，读取数据页到内存中进行修改，如果大批量的更新操作则需要开启大批量的IO操作。

如果有了change buffer，mysql引擎会将这些更新操作缓存在change buffer中，并且这个时候表示更新操作完成，可以提交事务。减少了更新操作时的IO操作，提升了性能。

（3）如果下次查询需要访问这个数据页时候，如果内存中没有数据页，会先读到内存中，然后查看change buffer中是否有和该内存页有关的操作，执行后返回结果。

（4）redo log日志中也会记录change buffer中的操作记录，所以不用担心断电数据库重启后在change buffer中的操作丢失。可以做到持久化数据。

change buffer什么时候写入到磁盘中：

（1）上述提到的，访问该数据页时会将更新操作应用到数据页。

（2）mysql引擎后台有线程会定期进行同步。

（3）数据库正常关闭之前，也会进行同步

以上的change buffer更新操作应用到数据页的过程我们可以叫做merge。

二、普通索引和唯一索引的查询过程

查询语句：select id from user where phone = '1234';假设在内存中没有该数据页的情况。

1、普通索引

对于普通索引，根据二分查找算法找到满足条件的记录后，还需要继续查找下一个记录，直到不满足条件为止。

2、唯一索引

对于唯一索引，根据二分查找找到满足条件的记录后，根据唯一索引的唯一性，找到第一个就可以停止查找。

上述的查询过程，对于满足条件的判断都是将数据页读取到内存中进行判断的，因此如果使用了唯一索引的数据，比如要插入一个手机号码是110的用户，那么会先将110对于的数据页读取到内存中，来判断是否存在110的数据，由于唯一索引已经将数据读取到内存中了，所以后续的更新操作直接在内存中更新即可。所以在唯一索引的查询和更新情况下change buffer的意义就不大了。

而普通索引在插入数据的时候直接插入即可，因此需要change buffer来提升更新的性能。

因此如果在更新频率很高的情况下，有了change buffer加持的普通索引会比唯一索引的性能更好。

· 使用change buffer在任何场景下都可以起到加速作用吗？

不一定，因为change buffer只有在写操作很频繁的时候确实可以起到加速作用，因为change buffer记录的变更越多相当于数据页上的更新次数也越大，收益也就越大。

因此对于写多读少的业务来说，change buffer会更好，读少的业务意味着会较少的触发merge操作。对于读多的业务来说，会较高的触发merge操作，反正增加了change buffer的维护代价，让change buffer变得没有意义。

另外上面的例子中，change buffer并不适合在唯一索引中使用。

三、唯一索引和普通索引的选择

首先在查询方面，他们的性能其实没有一点差别。如果考虑到更新的性能，并且可以保证业务上做出更新的幂等那么我建议还是优先使用普通索引。

但是如果使用普通索引的情况下，更新操作后面马上跟着这条数据页的查询操作，反正会增加change buffer的维护代价那么建议可以关闭change buffer。

在实际的使用中，在特定的业务场景下普通索引配合change buffer的使用，对于数据量大的表的更新优化我觉得还是很明显的。