该课程来自极客时间《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?
- 访问这个数据页时会触发merge
- 系统有后台线程会定期merge
- 数据库正常关闭过程中,也会执行merge操作
2.4、change buffer的好处
- 将更新操作先记录在change buffer,减少读磁盘,语句的执行速度会明显提升
- 数据读入内存需要占用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、数据表空间、系统表空间
执行了如下操作
- page1在内存中,直接更新内存
- page2不在内存中,记入change buffer
- 将上述两个动作记入redo log
如果更新完成后进行读取
- 读page 1时,直接从内存返回
- 读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的执行流程是这样的:
-
从磁盘读入数据页到内存(老版本的数据页);
-
从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页;
-
写redo log。这个redo log包含了数据的变更和change buffer的变更。
到这里merge过程就结束了。这时候,数据页和内存中change buffer对应的磁盘位置都还没有修改,属于脏页,之后各自刷回自己的物理数据,就是另外一个过程了。