【读书笔记】MySQL实战45讲——普通索引和唯一索引

该课程来自极客时间《MySQL实战45讲》

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

如字段k上的值都不重复，普通索引和唯一索引有何差异？

1、查询过程

如使用

SELECT ID FROM T WHERE k=5

这个查询语句在索引树上查找的过程

先是通过B+树从树根开始，按层搜索到叶子节点，也就是一个数据页

然后在这个数据页中通过二分法来定位记录

• 普通索引，当查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到不满足k=5的记录
• 唯一索引，当查找到满足条件的第一个记录，由于它的唯一性就会停止检索

1.1、性能差异

微乎其微

InnoDB的数据是按数据页为单位来读写的，每次都是读取一个页到内存中，每个数据页默认大小为16KB

• 第一种情况，查找和判断下一条记录在该数据页中，那么只需要一次指针寻找和计算
• 第二种情况，查找和判断下一条记录正好在该数据页的最末尾，要取下一个记录，必须读取下一个数据页，这会复杂一点

但，计算平均性能差异时，可以认为操作成本对现在的CPU来说忽略不计

2、更新过程

2.1、buffer pool

硬盘在读写速度上相比内存有着数量级差距，如果每次读写都要从磁盘加载相应数据页，DB的效率就上不来

因而为了化解这个困局，几乎所有的DB都会把缓存池当做标配（在内存中开辟的一整块空间，由引擎利用一些命中算法和淘汰算法负责维护和管理）

change buffer则更进一步，把在内存中更新就能可以立即返回执行结果并且满足一致性约束（显式或隐式定义的约束条件）的记录也暂时放在缓存池中，这样大大减少了磁盘IO操作的几率

2.2、change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新

而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作

通过这种方式就能保证这个数据逻辑的正确性。

虽然是叫buffer，但实际上是可以持久化的数据，change buffer在内存中有拷贝，也会被写入到磁盘上

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge

2.3、何时会merge？

1. 访问这个数据页时会触发merge
2. 系统有后台线程会定期merge
3. 数据库正常关闭过程中，也会执行merge操作

2.4、change buffer的好处

1. 将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会明显提升
2. 数据读入内存需要占用buffer pool，这种方式还能够避免占用内存，提高内存利用率

2.5、何时使用change buffer？

对于唯一索引，所有更新操作都需要判断这个操作是否违反唯一性约束

这必须要将数据页读入内存才能判断，如果已经读入到内存，直接更新内存会更快，无需用到change buffer

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用

但在普通索引的所有场景，change buffer并不全部适用

merge是一个数据更新的时刻，如果在merge前，change buffer记录的变更越多，merge的收益越大

因此，对于写多读少的业务，change buffer效果最好

因为如果当读的次数多时，会不断触发merge，这样反而增加了change buffer的维护代价，也起不到减少随机访问IO次数的作用

2.6、插入记录流程

例如插入记录(4,400)

第一种情况是，这个记录要更新的目标页在内存中

• 唯一索引，找到3和5之间的位置，判断到没有冲突后插入这个值，结束
• 普通索引，找到3和5之间的位置，插入这个值，结束

这个情况，区别仅在于一个判断

第二种情况，这个记录要更新的目标页不在内存中

• 唯一索引，需要将数据读入内存，判断没有冲突后，插入这个值，结束
• 普通索引，将更新记录在change buffer，结束

change buffer因为减少随机磁盘访问，对更新性能的提升会很明显

3、索引选择的总结

普通索引和唯一索引在查询中近乎无差别

但，在更新性能上，因为change buffer，所以普通索引应该尽量被选择（除读多的业务场景）

二、change buffer和redo log

WAL提升性能的核心机制，是尽量减少随机读写，这两个概念容易混淆

举个例子

假设执行插入语句

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

当前k索引树的状态，查找到位置后，k1所在的数据页在内存(InnoDB buffer pool)中，k2所在的数据页不在内存中

四个部分：内存、redo log、数据表空间、系统表空间

执行了如下操作

1. page1在内存中，直接更新内存
2. page2不在内存中，记入change buffer
3. 将上述两个动作记入redo log

如果更新完成后进行读取

1. 读page 1时，直接从内存返回
2. 读page 2时，需要先把page 2从磁盘读入内存，使用change buffer里的操作日志进行更新并返回结果

redo主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗

change buffer节省的随机读磁盘消耗是在：如果没有change buffer, 执行更新的“当时那一刻”，就要求从磁盘把数据页读出来（这个操作是随机读）

三、问题

change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有了merge过程，就等于是数据丢失了。会不会出现这种情况呢？
答案：虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；

2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页；

3. 写redo log。这个redo log包含了数据的变更和change buffer的变更。

到这里merge过程就结束了。这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。