简历敢写精通mysql - Mysql BufferPool详解

Mysql BufferPool详解：

InnoDB插入过程：

insert into t1 1,1,1,1,‘c’

insert into t1 2,2,2,2,‘b’

insert into t1 8,8,8,8,‘h’

插入到”页“的用户区域格式为，根据主键排序进行插入，方便用户查询时根据页目录进行数据查找，减少查询时间。

多页数据，where条件较大，通过二分查找法提升速度，以空间换时间。

页里存储（主键最小值，页的指针） B+树结构。

InnoDB的内存和磁盘架构

Buffer Pool内存结构详解：

​ Mysql优化分为索引优化、内存层面、缓冲区层面、磁盘层面等进行优化

​ 进行Mysql查询时，是有“页”的概念。在InnoDB中，1页大小为16kb

开始Mysql就会开辟一块内存空间，默认为128M的Buffer Pool，因为我们数据都是存在磁盘内，磁盘里存了好多数据，是以页为单位，一页为16kb

此时进行select语句，数据本身在磁盘里面，现在在磁盘里找出来那一页，复制这一页数据到Buffer Pool里面去，然后mysql从Buffer Pool里面去取具体那一行的数据。（优点：此时当同张表数据再次进行查询，where条件不一样时，可直接通过上次取出来的那一页进行查找数据，减少了一次与磁盘交互的过程。附SQL：select * from t1 where a=2）

此时进行update语句，首先会修改我们Buffer Pool里面这一页，此时这一页就变成了脏页，再进来查询的时候查询时Buffer Pool里面的数据，没有去磁盘中拿，update也没有直接修改磁盘中的数据。Mysql会有后台线程根据flush链表，定时把脏页持久化到磁盘中。

此时Pool里页存储满了，会通过lru链表进行淘汰。每次从磁盘中取“页”，都会把控制块放到lru链表中，后进来的页会放到lru链表的头部。如果在lru链表中的控制块又有查询语句用到了该控制块，则会移到lru链表头部，淘汰末端控制块。

1、Buffer Pool调优

SHOW GLOBAL VARIABLES like '%innodb_old_blocks_pct%'; - -LRU链表冷热数据区域配置 默认为37
SHOW GLOBAL VARIABLES like '%innodb_old_blocks_time%'; - -LRU链表冷数据区的数据隔多久可以放入到热数据区
SHOW GLOBAL VARIABLES like '%innodb_log_file_size%'; - -单个logfile的大小 默认为48M
SHOW GLOBAL VARIABLES like '%innodb_log_file_in_group%'; - -配置有几个logfile
SHOW GLOBAL VARIABLES like '%innodb_buffer_pool_size%'; - -配置buffer pool大小 默认为128M
SHOW GLOBAL VARIABLES like '%innodb_change_buffer_max_size%'; - -配置change pool大小 默认为25个字节
SHOW GLOBAL VARIABLES like '%innodb_log_buffer_size%'; - -redo log buffer的大小，默认为16M
SHOW GLOBAL VARIABLES like '%innodb_flush_log_at_trx_commit%'; - -redo log buffer中的内容间隔多久刷新到磁盘，默认为1s
SHOW GLOBAL VARIABLES like '%undo%';

set global innodb_buffer_pool_size=15032385536; - -修改buffer pool大小

优化Bugger Pool内存，默认为128兆

2、Buffer Pool概念

Buffer Pool页的数量计算公式，内存/页大小：128M/16kb=N页

3、Buffer Pool链表管理淘汰页

链表的共同特点：基节点存储三个参数。

​ 1.size：多少个控制块，等于链表长度

​ 2.头节点：第1个控制块

​ 3.尾结点：最后一个控制块

free链表：管理空闲区域

此时新进来的这一页，取free链表头节点的控制块，同时删除取出去的控制块。

此时新增加一个free的控制块，新增在链表的末端。

flush链表：脏页控制

此时有update操作，存储脏页到链表末端，后台线程从链表头持久化页到磁盘。

lru链表：淘汰控制

新从磁盘中取出来的页，会放到lru链表的头部开始存储，当pool满了会淘汰链表尾部控制块

如果在lru链表中的控制块 又有查询语句 用到了该控制块，会移到lru链表头部

头部节点存储为最近使用的、最近频繁使用的、最新的控制块。

尾部节点存储为不常用的控制块。

lru升级版：

避免进行全表扫描lru对此进行了升级版：

在mysql里lru链表是有默认长度

八分之五(5/8)为热数据区域，八分之三(3/8)为冷数据区域。

参数控制 innodb_old_blocks_pct 默认是37 冷数据为37%

逻辑上改变：

1.如果是从磁盘中新读取的页，放在冷数据区域的第一个位置

冷数据区域满了，则淘汰冷数据区域链表末端的控制块

2.如果是该页的控制块在lru链表中，重新用到了该控制块，会把他放到链表区域头部

预读机制：

由于mysql有预读的机制，会导致不是热数据的预读页会随着缓存页的读取会被放到lru链表的最头部

还有mysql有全量扫描表的可能，也会导致大量本来属于热数据的缓存页会被挤到lru链表的最尾部

– innodb_read_ahead_threshold 默认值是56 ，如果顺序的访问一个区内的数据页超过这个数量，就会触发预读机制，将下一个相邻区中的所有数据页都会加载到缓存页中

– innodb_random_read_ahead

默认值是off 如果buffer pool里缓存了一个区中的13个连续的数据页 那么这个区中的其他数据页也会被加载到缓存页中的

所以lru进行了优化，引入了冷热数据分离处理的机制

冷热数据分离处理机制：

第一次加载进来的数据会被放在冷数据的头部

参数 innodb_old_blocks_time 默认值是1000

也就是说在1000毫秒之后再次访问这个值，那么这个值就会从冷数据区移到热数据区头部，避免全表扫描的控制块进入热数据区域

t1:将控制块放到链表中的时间

t2:再次用到该控制块的时间

如果t2-t1>1s(1000毫秒) 则会从冷数据区域放到热数据区域的头部

如果是全表扫描，会连续的访问这个控制块，时间肯定是小于1s，需要过滤掉这部分

解决了全表扫描Buffer Pool大换血的问题

热数据区优化：

由于热数据经常被访问会导致热数据前部分数据的节点会经常移动，所以热数据区的前1/4被访问之后是不会发生节点移动的。只有在访问热数据区的后3/4才会将数据移到链表头部

避免进行全表扫描的原因

例：select * from t1 数据量很大的情况下，会进行Buffer Pool大换血，全量数据的页会淘汰掉热数据页，导致下次热数据查询还需重新从磁盘中获取，导致查询效率低。

Log Buffer之RedoLog

InnoDB存储引擎下才会有redolog！！！

1.事务过程：

begin; update (rollback;) commit;

update语句会产生redolog到Log Buffer中

如果中间产生rollback回滚，会删除对应的redolog

只有进行commit操作，才会通过操作系统缓存持久化到磁盘文件中

配置事务参数

操作系统通过参数配置进行持久化到磁盘文件中，默认值为1

配置为0：

优点：操作系统什么都没做，只是记录一下，后台线程自己操作，效率高

缺点：如果mysql挂了，数据发生丢失，没有进行持久化

配置为1：

会与磁盘进行持久化，消耗一点点时间，多了IO操作

配置为2：

将redoLog写到操作系统缓冲区，mysql挂了操作系统没挂，数据可以继续持久化

2.update语句执行过程

1.更新buffer pool里面的页的数据

2.生成redoLog (redoLog = update语句)

3.commit完成、持久化redoLog对象

3.为什么事务提交不直接持久化？

第一方面：

在第一步完成时，更新了页的数据，生成了redoLog，这时候mysql挂了，重启后跟根据redoLog持久化到磁盘还原数据，否则会出现数据不一致数据丢失问题。

第二方面：

没有redoLog的话是随机IO效率低。

有redoLog是顺序IO效率高

mysql安装时分配了ib_logfile0，ib_logfile1文件，可以进行顺序IO

4.ib_logfile文件

ib_logfile写满了如何处理

当ib_logfile0，ib_logfile1都写满了，进来一条redoLog，会写到ib_logfile0文件，会产生log覆盖。

此时会触发mysql的检查点checkpoint，一旦触发mysql性能就会有影响，在进行redoLog持久化到磁盘。

检查点查看：

1.查看版本

mysql> selectversion();±----------+

| version() |

±----------+

| 8.0.11 |

±----------+

1 row in set (0.00 sec)

2.查看位置

mysql>show engine innodb status\G;—

LOG

---

Log sequence number 25048841

Log buffer assigned up to 25048841

Log buffer completed up to 25048841

Log written up to 25048841

Log flushed up to 25048841Added dirty pages upto 25048841Pages flushed upto 25048841Lastcheckpoint at 25048841

35 log i/o‘s done, 0.00 log i/o‘s/second----------------------

Last checkpoint就是检查点的位置，在数据库并不繁忙的时候，可以看到Log sequence number，Log flushed,Pages flushed的位置接近或相等。当数据库DML操作较多时，它们的差值会变大。

3.相关参数

mysql数据库将内存中脏页刷新到磁盘时，记录一次检查点，也这是检查点的功能，刷新内存脏页到磁盘。innodb每秒或几秒都会自动刷新脏页，除此之外，它还受以下两个参数影响

mysql> show variables like ‘%max_dirty_pages%‘;±-------------------------------±----------+

| Variable_name | Value |

±-------------------------------±----------+

| innodb_max_dirty_pages_pct | 50.000000 |

| innodb_max_dirty_pages_pct_lwm | 10.000000 |

±-------------------------------±----------+

2 rows in set (0.00 sec)

当innodb脏页使用率超过innodb_max_dirty_pages_pct_lwm的值时，除innodb本身的自动刷新脏页机制外，会开始加速刷脏页，当脏页使用率超过innodb_max_dirty_pages_pct的值时，会再次提高刷新脏页的速度。

innodb_max_dirty_pages_pct_lwm为0表示该参数不生效。

4.检查点分类

Sharp Checkpoint(完全检查点)

将内存中所有脏页全部写到磁盘就是完全检查点，比如数据库实例关闭时。

Fuzzy Checkpoint(模糊检查点)

将部分脏页刷新到磁盘，就是模糊检查点，数据库实例运行过程产生的检查基本上就是这种类型的检查点。

5.检查点作用

主要作用就是减少数据恢复时间 。因为检查点位置之前的数据都写到磁盘上了，数据恢复便不需要再关注这部分数据，所以它减少了恢复的时间 。

Mysql崩溃恢复，恢复的是哪些数据？

恢复的是Last checkpoint到Log flushedup之间的数据。Log flushedup是内存写日志的位置，也是日志文件最新的位置。再新的话就到内存中去了，还没有写入日志文件的数据是没法恢复的。

应急措施：

1.调大ib_logfile文件

优点：触发checkpoint频率降低，触发时间节点靠后

缺点：记录redolog变多，mysql重启时间较长会进行持久化到磁盘做数据恢复

2.暂停sql执行，优先进行log持久化

查看mysql数据目录

show global variables like ‘%datadir%’;

服务器进到mysql目录(value)

cd value

查看文件详情

ls -lh

找到ib_logfile0文件 存储的redoLog，相同为48M

Log Buffer之UndoLog

1.事务

1.更新buffer pool里面的页的数据

2.生成redoLog (redoLog = update语句)

3.生成binLog (binLog = ddl语句) 主从同步用的较多

4.生成undoLog (undoLog = 还原语句)

3.commit完成、持久化redoLog对象

Doublewrite Buffer

InnoDB 脏页为16kb，但是经过操作系统往磁盘持久化的过程中，操作系统里页为4kb，4次才能写进磁盘

如果InnoDB写入两次，写了8kb，操作系统挂了，还有8k数据就丢了

先将page页16kb写入到doubleWriteBuffer中去，这时候mysql挂了，重启后从磁盘中拿数据，会将t1表空间+redoLog结合为最新的数据返回页。

如果写入doubleWriteBuffer全部成功，会删除对应的redoLog，也是完整数据。

优化点

如果磁盘提供了一个接口，能保证16kb的数据原子性的情况下，可以不用双写缓冲区

Change Buffer

进行update操作时，涉及到到的字段不光要更改数据页，还要更改索引页。

字段多了，索引多了，导致磁盘IO也多了。

这时候mysql对此做优化，把update语句先存到change buff中，没有真正修改索引页。

此时如果再次从磁盘中拿“页”数据，会与change buff做整合，生成新的页到buffpool里面去。

然后根据buff pool里面的索引页做持久化，减少n次IO过程。