MYSQL45讲学习笔记

09 普通索引和唯一索引，怎么选择？

以select id from T where k=5为例，假设id为主键，k上有普通索引或者唯一索引。

1.普通索引和唯一索引的查询过程

首先查询索引，找到叶子结点中k=5的数据页，将其加载进内存。

普通索引：  查询到k=5的行之后，继续查找下一行，直到碰到第一个k不为5的行

唯一索引：  查询到k=5的行，直接返回。

两者的不同之处在于，普通索引多了一个“查询和判断下一行的记录”，这个操作对于性能影响微乎其微，因为mysql加载数据是将页加载进内存，页的大小默认为16k，当找到k=5的行时，它的下一行一般在同一行上，所以并不影响性能。

当然也有极端情况，比如k=5的行刚好是本页的最后一行，这种稍微麻烦一点，需要读取下一页。

2.普通索引和唯一索引的更新过程

如果数据页不在内存中

唯一索引：将数据页加载进内存中，判断更新后的值又没有冲突，若没有冲突，更新内存。

普通索引：更新changebuffer,流程结束。

可以看到，普通索引比唯一索引，少了一个“将数据页加载到内存的操作”，减少了一次磁盘的随机io读，提升了性能。

3.changebuffer

changebuffer的前身是insertbuffer，当需要更新一个数据页时，如果数据页在内存中就直接更新，如果不在内存中，将更新操作记录到changebuffer中，之后会有后台线程将其更新到数据页中，或者当被更新的数据页被查询时，这时会将数据页加载到内存中，然后根据changbuffer中的操作记录，对数据进行更新，此操作称为merge。

changebuffer是内存中的一部分（数据表空间，.ibt文件），也是磁盘中的一部分（系统表空间，存储了数据字典等等）。

changebuffer适用于写多读少的场景，比如日志系统。

在merge前changebuffer记录的操作越多，则越能提升性能。

changebuffer属于buffer pool中的一部分，大小是固定的，可以循环写，可以通过设置innodb_change_buffer_max_size参数来改变changebuffer的大小，比如：将其值设置为50，表示最大可以使用buffer pool的百分之50内存。

只有非唯一的辅助索引可以使用changebuffer，因为唯一索引需要判断是否有冲突，所以需要将数据页加载到内存中，既然数据已经在内存中了，那么直接更新内存会更快。

4.changebuffer和redo log的区别

changebuffer的主要作用是减少了 更新数据时磁盘的随机读，

而redo log是减少了磁盘的随机写。

题外话：redo log 的主要功能为实现crash-safe。binlog没有crash-sage功能是因为没有checkpoint,无法知道哪些操作已刷盘。