mysql8 缓冲区状态 mysql写缓冲

转载

编程梦想家 2023-09-03 09:29:14

文章标签 mysql8 缓冲区状态 mysql 服务器数据库链表 文章分类 MySQL 数据库

缓冲池和写缓冲

在MySQL中数据分为内存和磁盘两个部分，可以通过在buffer pool缓存数据页和索引页，来减少磁盘读
通过change buffer缓解磁盘写。

如何理解 MySQL 的边读边发

服务端并不须要保存一个完整的结果集。取数据和发数据的流程是：

获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
重复获取行，直到 net_buffer 写满，调用网络接口发出去。
若是发送成功，就清空 net_buffer，而后继续取下一行，并写入 net_buffer。
若是发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socketsend buffer）写满了，进入等待。直到网络栈从新可写，再继续发送
所以一个查询在发送过程当中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大

也意味着，若是客户端接收得慢，会致使 MySQL 服务端因为结果发不出去，这个事务的执行时间变长

MySQL 的大表查询为什么不会爆内存

因为MySQL是边读变发，对于数据量很大的查询结果来说，不会再 server 端保存完整的结果集，
所以如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

mysql_use_result的使用

对于每个可以产生一个结果集的命令（比如select、show、describe, explain, check_table等等），都需要调用mysql_store_result或者mysql_use_result语句，处理完结果集后需要使用mysql_free_result释放。
Mysql_use_result初始化一个取回结果集但是它并不像mysql_store_result那样实际的读取结果放到客户端。相反，每一个列结果都是通过调用mysql_fecth_row独立取回的。它直接从服务器读取一个查询的结果而不是存储它到一个临时表或者一个客户端的缓存里面。因此对于mysql_use_result而言它比mysql_store_result快一些并且使用更少的内存。客户端只有在当前的列或者通信的缓存即将超过max_allowed_packet才申请内存。
有些情况不能使用mysql_use_result接口，如果每一列在客户端要做很多的的处理，或者输出发生到屏幕用户可能通过ctrl-s退出，这样会挂起服务器，而阻止其他线程去更新客户端正在获取数据的这些表。
当使用mysql_use_result时，必须执行mysql_fetch_row直到NULL值返回。否则那些没有被获取的列将作为你下个请求的一部分返回。
一旦你处理完所有的结果集，必须调用mysql_free_result去释放。

Buffer_Pool【缓冲池，简称BP】

为了提高性能，减少磁盘IO，MySQL同样具有缓冲池机制
就性能而言，innodb缓冲池大小通常是最重要的变量，因为它不仅缓存索引，还缓存行数据，自适应哈希索引，锁和其他内部结构
缓冲池还实现了延时写，把多个写操作合并在一起，大型的缓冲池需要更长的关闭时间和预设时间
MySQL缓冲池是一块默认大小为128M的内存区域，用于缓存表数据与索引数据，避免每次访问都进行磁盘IO，起到加速访问的作用
innodb所有数据页的读写操作都需要通过缓冲池进行

读操作的时候，要先从缓冲池中查看数据的数据页是否存在，如果不存在，则将页从磁盘读取到缓冲池中。
写操作的时候，先更新Buffer Pool中的数据页，然后将更新操作顺序写Redo log，再由后台线程以一定频率将缓冲池中的内容刷到磁盘，写操作的事务持久性由redo log 落盘保证，buffer pool只是为了提高读写效率。

buffer pool的工作流程，大致可以分为三步：

先查询buffer pool是否存在对应的数据页，有的话则直接返回
不存在对应的数据页，就去磁盘中查找，并把结果复制一份到buffer pool中，然后返回给客户端
下次有同样的查询，就可以直接查找buffer pool返回数据

控制块的概念：

为了管理的缓存页，InnoDB 为每一个缓存的数据页都创建了一个单独的区域用于描述数据也就是控制块，大概占缓存页大小的5%
包括了数据页所属表空间、数据页编号、缓存页在Buffer Pool中的地址，链表节点信息、一些锁信息等
控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中

缓冲池内部结构：

Buffer Pool里有三个链表，LRU链表，free链表，flush链表，InnoDB通过这三个链表的使用来控制数据页的更新与淘汰的
Free链表（空闲链表）

当启动 Mysql 服务器的时候，需要完成对 Buffer Pool 的初始化过程，把它划分为若干对控制块和缓存页
划分空间后Buffer Pool的缓存页是都是空的，当要对数据执行增删改查的操作的时候，才会把数据对应的页从磁盘文件里读取出来，放入Buffer Pool中的缓存页中，当Buffer Pool中间有的页数据持久化到硬盘后，这些数据页又会被空闲出来。
所以为了知道哪些数据页是空的，那些是有数据的，innoDB维护了一个空闲链表，记录了空闲的数据页对应的控制块信息

LRU链表

缓冲池的大小是有限的，所以应该把频繁访问的数据留在缓冲池中，把访问比较少的数据淘汰掉，空出数据页缓存其他数据
所以，InnoBD采用了LRU（Least recently used）算法，将频繁访问的数据放在链表头部，而不怎么访问的数据链表末尾，空间不够的时候就从尾部开始淘汰
当数据库从磁盘加载一个数据页到缓冲池中的时候，会将一些变动信息也写到控制块中，并且将控制块从Free链表中脱离加入到LRU链表中

Flush链表

对数据的读写都是先对Buffer Pool中的缓存页进行操作，此时缓存页跟磁盘页的数据就会不一致，也就是产生了脏页，然后在通过后台线程将脏页写入到磁盘，持久化到磁盘中
Flush链表的作用就是帮助定位脏页，结构与Free链表的结构很类似，也由基节点与子节点组成，是一个双向链表，链表结点是被修改过的缓存页对应的控制块

mysql的预读机制

如果顺序的访问一个区里的多个数据页，访问的数据页数量超过阈值（innodb_read_ahead_threshold，默认值为56），就会触发预读机制，把下一个相邻区的所有数据页都加载到缓存中
如果buffer pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁被访问的，此时会触发预读机制，把这个区里的其他数据页都加载到缓存中（innodb_random_read_ahead，默认值为off关闭）
因为数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，innoDB把数据从磁盘读取到内存中，并不是按需读取，而是按页读取，一次至少读一页数据（16K），如果未来要读取的数据就在页中，直接读取内存即可，不需要磁盘IO，提高效率

MySQL对LRU算法的改进

LRU 算法存在的问题：预读失效和Buffer Pool 污染
预读失效

当 Buffer Pool空间不够的时候，需要把末尾的页淘汰掉，预读失效指的就是，那些被提前加载进来的数据页一直没有被访问，就会被挤到链表的尾部，进而被淘汰，相当于预读是白费功夫，缓存的命中率就会大大降低
但是并不能因为预读失效，而将预读机制去掉，所以需要提高缓存的命中率。

MySQL将 LRU 划分为：old和young两个区域
young 区域，在 LRU 链表的前半部分；old 区域，在LRU 链表的后半部分
old 区域占整个 LRU 链表长度的比例，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37，（可以通过 innodb_old_blocks_pc 参数来设置）
预读的页会被加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部
如果预读的页一直没有被访问，会一直存在old 区域，直到被移除，不会影响 young 区域中的热点数据

Buffer Pool 污染

当Sql执行的时候，会数据加载到Buffer Pool ，然而Buffer Pool的大小是有限的
所以如果加载大量数据，例如对大表进行全表扫描，把Buffer Pool 里的所有页都替换出去，把原本只会访问一次的数据加载到Buffer Pool，导致原本的热点数据被淘汰下次访问的时候，又要重新去磁盘读取，导致数据库性能下降
所以可以将数据放young 区的门槛提高，从而把这种访问一次就不会用的数据过滤掉，把它挡在Old区，这样就不会污染young 区的热点数据了
MySQL 解决方式就是提高了数据从Old区域进入到 young 区域门槛

设定一个间隔时间，默认为1秒，也就是数据页必须在 old 区域停留的时间，然后将Old区域数据页的第一次访问时间在其对应的控制块中记录下来
如果后续的访问时间与第一次访问的时间大于间隔时间，才会将该缓存页移动到 young 区域的头部。
当同时满足数据页被访问与数据页在 old 区域停留时间超过 1 秒两个条件，才会被插入到 young 区域头部

young 区域优化

MySQL为了防止 young 区域节点频繁移动到头部，对 young 区域也做了一个优化：young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

脏页的刷盘时机

当我们对数据进行修改时，其实修改的是Buffer Pool 中数据所在缓存页，修改后将其设置为脏页，并将脏页的控制块同时存在于 LRU 链表和 Flush 链表，然后通过刷脏将修改同步至磁盘
刷脏的目的是将修改的数据同步到磁盘，并释放Buffer Pool内存空间，因此只需要根据LRU链表，将其Old区域尾部访问的最少的数据页刷回磁盘，并释放缓冲池中内存即可
刷脏并不是每次修改都进行的，那样性能会很差，脏页的刷盘时机总的来说就分为：

redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；
后台线程会定期脏页刷盘，为了避免缓冲池内存不够，MySQL在后台有一个定时任务，通过单独的后台线程，不断从LRU链表Old区尾部的缓存页刷回至磁盘中并同时释放缓存页。
Buffer Pool 空间不足时，会淘汰一部分数据页，分为两种情况：

如果缓存页同时在flush链表和LRU链表中，说明数据被修改过，则需要刷脏，释放掉缓存页的内存，将控制块重新添加到free链表中
如果缓存页只是存在于LRU链表中，说明数据没有被修改过，则不需要刷脏，直接释放掉缓存页的内存，将控制块重新添加到free链表中

change buffer（写缓冲）：

change buffer就是在非唯一普通索引页不在buffer pool中时，对页进行了写操作的情况下，先将记录变更缓冲，等未来数据被读取时，再将 change buffer 中的操作merge到原数据页的技术。
在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。
原理

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果数据页不在内存中。在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
虽然名字叫作写缓冲，实际上它是可以持久化的数据。也就是说，写缓冲在内存中有拷贝，也会被写入到磁盘上
将写缓冲中的操作合并到原数据页，得到最新结果的过程称为 merge。以下情况会触发merge：