Mysql 5.7 InnoDB 存储引擎整体逻辑架构图
一、索引概述
CREATE TABLE `t_user`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 id',
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
`age` smallint(4) NOT NULL DEFAULT '0' COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='用户表'
该表有三个字段,id 是自增主键,为 id 列创建主键索引,为 name 列创建非唯一二级索引
其实主键索引就是聚集索引的一种,而二级索引是非聚集索引的一种,关于聚集和非聚集索引可以看一下这篇文章
https://www.cnblogs.com/xiaomaomao/p/16196006.html
二、Insert Buffer 概述
通过上面的表结构,我们可以知道该表有两颗 B+ 树,一颗是基于 id 列构建的主键索引树,一颗是基于 name 列构建的二级索引树
我们往 t_user 表中插入几条数据
那么两颗索引树的结构如下(假设 B+ 树是三阶的)
接着我们继续向 t_user 表中插入如下数据
insert into t_user (id, name, age) VALUES (null,'b',21);
insert into t_user (id, name, age) VALUES (null,'f',18);
insert into t_user (id, name, age) VALUES (null,'a',17);
insert into t_user (id, name, age) VALUES (null,'e',16);
上面两颗 B+ 树就会变成如下所示
对于使用 name 列构建的二级索引树来说,由于 name 列是无序的,每次插入的时候位置都不固定,通常将这种认为是随机 IO
对于使用 id 列构建的主键索引树来说,由于每次插入 id 都是自增的,每次插入的数据都会被放在叶子结点的最右侧,通常将这种认为是顺序 IO
当然在整个插入的过程中,无论是二级索引还是主键索引都会触发页分裂与页合并,在构建两颗索引树的过程中就能很明显的看到,这是因为我们选定的 B+ 树只有 3 阶,所以触发页分裂与页合并的次数会比较频繁,实际生产环境下,一个 page 页存放的数据远不止几条,触发页合并与裂变的情况不会像 3 阶 B+ 树这么频繁,这里就忽略了页分裂与合并带来的性能损耗
这里有一个比较好的构建数据结构的网站,有兴趣的可以去试一下生成整颗 B+ 树的过程
https://algorithm.lerogo.com/
基于此我们得出一个结论,基于自增方式构建的主键索引在做插入操作时通常认为是顺序 IO,而二级索引由于其数据的无序性,在做插入操作时通常认为是随机 IO,随机 IO 的访问性能是很差的,InnoDB 存储引擎针对这种情况,设计了 Insert Buffer
对于非唯一的二级索引的插入操作,不是每一次都实时的插入到二级索引页中,而是先判断插入的非唯一二级索引页是否缓存在 Insert Buffer 中
如果在,直接操作 Buffer Pool 中的缓存页即可,不需要额外去磁盘读取相关的数据页
如果不在,则先将要插入的数据放入 Insert Buffer 中,告诉客户端这条数据中相关的二级索引字段已经插入到二级索引的叶子节点中了
实际上并没有插入,只是存放在了 Insert Buffer 中,然后再根据数据库的实际情况,以一定的频率进行 Insert Buffer 和二级索引叶子节点的合并操作,这个过程称为 merge,这种时候,经常能将多条记录的插入合并到一个操作中,这样就大大提高了二级索引离散插入的性能
例如下面的 sql 语句
insert into t_user (id, name, age) VALUES (null,'x',17);
这条 sql 语句不仅仅会在主键索引树上做插入操作,还会在 name 二级索引树上做插入操作,也就是说会同时维护主键索引和二级索引两颗 B+ 树
由于主键索引是顺序 IO,性能较高,并且还要校验唯一性,所以主键索引的插入可以认为是实时的
二级索引是随机 IO,性能较差,所以设计了 Insert Buffer 进行缓存,在特定的时机进行 merge 操作,降低随机 IO 带来的性能问题,所以二级索引的插入可以认为是延时的
二、Change Buffer 概述
Insert Buffer 是 InnoDB 早期版本引进的,当时只支持插入操作,后来 InnoDB 对所有的 DML(insert、update、delete) 都支持缓冲操作,Insert Buffer 就演变成了现在的 Change Buffer
Change Buffer 与 Insert Buffer 一样适用的对象依旧是非唯一的二级索引
下面是官网对 Change Buffer 的一些介绍
https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html
Change Buffer 是一种特殊的数据结构,它用于缓冲对于那些不在 Buffer Pool 中的二级索引页,造成这些二级索引页的变更可能来自 Insert、Update、Delete(DML)操作,稍后当其它读操作将相关的二级索引页加载到缓冲池中时,这些变更将会被合并
与聚集索引不同,二级索引通常是非唯一的,并且二级索引的插入以相对随机的顺序进行,类似地,删除和更新操作可能会影响索引树中不相邻的二级索引页,稍后,当其它操作将受影响的页面读入缓冲池中时,合并缓存的更改,可以避免将辅助索引页面从磁盘读入
Buffer Pool 中造成的大量随机访问 IO
在系统大部分时间处于空闲状态或 Mysql 关闭期间运行的 purge 操作会定期将更新的索引页写入磁盘,与立即将每个值写入磁盘相比,purge 操作可以更有效地将一些列值写入磁盘块
当有许多受影响的行和许多二级索引需要更新时,更改缓冲区合并可能需要几个小时.在此期间,磁盘I/O会增加,这可能导致磁盘绑定查询的速度明显减慢.Change Buffer 的合并也可能在事务提交后继续发生,甚至在服务器关闭和重启后
三、Change Buffer大小配置
Change Buffer 是 Buffer Pool 的一部分,Change buffer 使用的是 Buffer Pool 的内存,由于 Buffer Pool 的内存大小是有限制的,所以 Change Buffer 大小也是有限制的,可通过参数 innodb_change_buffer_max_size 进行设置
show variables like '%innodb_change_buffer_max_size%';
innodb_change_buffer_max_size: 表示允许 Change 占 Buffer Pool总大小的百分比,默认值为 25%,最大可设置为 50%
当在系统中有大量插入、更新、删除操作时,可以增大 innodb_change_buffer_max_size,以提高系统的写入性能(Change Buffer 越大,可以缓存的二级索引页的 DML 操作更多,从而降低随机 IO)
当在系统中有大量查询操作时,可以减小 innodb_change_buffer_max_size,以减少 Buffer Pool 中数据页的淘汰的概率,提高系统的读取性能(Buffer Pool 越大,可以缓存更多的数据页和索引页,缓存变多了,缓存的命中率就变高了)
需要注意的是: innodb_change_buffer_max_size 设置是动态的,它允许修改设置而无需重新启动服务器
四、Change Buffer 的 Merge 时机
1、辅助索引页被读到缓冲池时
当辅助索引页被读取到缓冲池中时,例如正在执行正常的 select 查询操作,这时需要检查 Change Buffer Bitmap 页,然后确认该辅助索引页是否有记录存放于 Change Buffer B+ 树中,若有,则将 Change Buffer B+ 树该页的记录插入到该辅助索引缓存页中.可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中,因此性能会有大幅提高
2、Change Buffer 内存空间不足时
Change Buffer Bitmap 页用来追踪每个辅助索引页的可用空间,当空间不足时,后台 master 线程则会强制进行一个合并操作,即强制读取辅助索引页,将 Change Buffer B+ 树中该页的记录及待更新的记录 merge 到到辅助索引页中
3、系统空闲或者关闭 Mysql 正常关闭时,后台 master 线程发起 merge
五、Change Buffer 为什么只为非唯一二级索引服务
唯一索引的 DML 操作都需要校验唯一性,做法就是将磁盘上的数据页加载到 Buffer Pool 中,在内存中判断 DML 操作的数据是否唯一,既然数据都加载到 Buffer Pool 中了,那就没有必要再缓存 Change Buffer 了,直接更新 Buffer Pool 中的缓存页就好了
六、Change Buffer 适用场景
1、写多读少(大量的写操作可以缓存在 Change Buffer 中,并且多个操作可以合并为一个操作,减少磁盘随机 IO)
2、数据写完之后立马被访问的概率低(如果数据刚写入 Change Buffer 就立马被访问,这个时候就会触发 merge 操作,merge 完成之后就要写入磁盘,就会有大量随机 IO 操作)