Mysql脏页flush及收缩表空间原理解析

mysql脏页

由于WAL机制，InnoDB在更新语句的时候，制作了写日志这一个磁盘操作，就是redo log，在内存写完redo log后，就返回给客户端， 即更新成功。

把内存里的数据写入磁盘的过程，术语就是flush，在flush之前，实际数据和数据库中的数据是不一致的，因为在redo log基础上更新了还未写入，数据库是老的，当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为脏页，内存写入后就一致了，称为干净页，

如果mysql偶尔运行速度很慢，很可能是在刷脏页。引发数据库flush的过程

• redo log满了，系统停止所有更新操作，将checkpoint向前推进，腾出空间继续写。
• 系统内存不足，需要新的内存页不够用，就会淘汰一些数据页，留给别的数据页使用，如果淘汰的是脏页，就会先写到磁盘。
• mysql空闲的时候。
• 正常关闭mysql的时候
• 在第一种情况下，redo log满了，这种情况是innodb要避免的，因为整个系统都不能再更新了，这是不能接受的
• 第二种情况，内存满了，要先写到磁盘，innodb用缓冲池管理内存，有三种状态
• 还没有用的内存页
• 用了并且是干净页
• 用了并且是脏页（淘汰的时候需要写入到磁盘）

所以我们有时使用数据库会发现数据库性能突然下降，可能就是在处理脏页。

刷脏页控制策略

• Innodb_io_capacity参数，这个参数会告诉innodb你的磁盘io能力。（有公式计算）
• innodb刷盘主要两个因素：脏页比例和redo log的写盘速度
• innodb_max_derty_pages_pct是脏页比例上限，默认是75%，调整好Innodb_io_capacity参数值，使脏页比例不要超过75%收缩表空间

场景例子：数据库占用空间太大，把最大的表删掉了一半数据，表的大小还是没有变化。

数据删除流程

加入要删掉R4，InnoDB引擎只会把R4这个记录标记为删除，如果之后再掺入一个ID在300-600之间的记录时，会复用这个位置，但是磁盘文件的大小并不会缩小。

如果删掉了一个数据页上的所有记录，这个数据页就可以被复用。

注意：数据页的复用跟记录的复用是不同的。

• 比如R4这条记录被删除，如果插入一个ID是400的行，直接复用这个空间，但是如果插入ID是800的行，就不能复用这个位置了。
• 但是整个数据页Page A上的所有记录删除之后，pageA标记为可复用，如果插入一条ID=50的记录需要用新的数据页的时候，PageA是可以背负用的。
• 如果我们用delete命令把整个表数据删除，结果是所有的数据页都会标记为可复用，但是在磁盘上，文件不会变小。

插入数据流程

如果数据按照索引顺序插入的，索引是紧凑的，但是如果是随机插入的，就会造成索引的数据页分页。

如果pageA已经满了，在插入一行数据会怎样？由于A满了，在插入一个id是550的数据时，就会申请一个新的页面pageB来保存数据，分裂完成后pageA的末尾就留下了空洞。

更新索引上的值也是删除一个旧的值，再插入一个新值，也会造成空洞。

收缩空间

新建一个与表A相同结构的表B，按照主键ID递增的顺序，把数据一行一行的从A里读出来再插入到表B中，表B中无空洞，数据页的利用率也更高，如果我们把表B作为临时表，数据从表A导入到B中的操作完成后，用B替换A，从效果上也起到了收缩A的作用。

在整个DDL过程中，表A不能有更新，所以这个DDL不是online的，在5.6之后的版本中，流程做了更改：

建立一个临时文件，扫描A中的所有数据页，

用数据页中A的记录生成B+树，存储在临时文件中

将所有对A的操作记录在一个日志文件中

临时文件生成后，将日志文件的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件

用临时文件替换表A的数据文件

图示

可以看到，与图3过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A做增删改操作。

使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。