谈谈 MySQL的刷盘过程 和 表数据

先来看第一个问题，SQL语句变慢

原因分析

一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短，像抖了一下。

我们通常的更新语句，只做了更新内存数据页并写redo log的写磁盘动作，但是内存中的脏页 总得跟新到磁盘中，也就是flush的动作。这个flush，就会影响到SQL语句的操作。

总结一下引发 flush 的场景：

1.  InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。checkpoint 位置往前 推进，就需要将两个点之间的日志，对应的所有脏页都 flush 到磁盘上。之后，从 write pos 到 checkpoint 之间就是可以再写入的 redo log 的区域。出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
2. 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：还没有使用的、使用了并且是干净页、以及使用了并且是脏页。InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
3.  MySQL 认为系统“空闲”的时候，就刷一点“脏页”。
4. MySQL 正常关闭的情况。MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
• 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略

 innodb_io_capacity 这个参数，它会告诉 InnoDB 磁盘的能力，这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试:

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

如果没能正确地设置 innodb_io_capacity 参数，如果设置太小，InnoDB 认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。表现出来的就是MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大。

然而，这个参数只是说明全力刷脏页的能力，但是平时还需要服务用户请求。InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。InnoDB 会根据这两个因素先单独算出两个数字。

1. 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字，计算这个数字的伪代码类似这样

   F1(M)
   {
     if M>=innodb_max_dirty_pages_pct then
         return 100;
     return 100*M/innodb_max_dirty_pages_pct;
   }

2. InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)。F2(N) 算法比较复杂，你只要知道 N 越大，算出来的值越大就好了。
3. 最后，根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

现在你知道，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑，都会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。也就是说，平时要多关注脏页比例，不要让它经常接近 75%。脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

然而， MySQL 中的另一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

除了上面说的刷脏页速度控制外，redo log 也不能设置太小。每次事务提交都要写 redo log，如果设置太小，很快就会被写满，write pos 一直追着 CP。这时候系统不得不停止所有更新，去推进 checkpoint。然后你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

接下来看看第二个问题，表数据删掉一半，表文件大小竟然不变

问题分析

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

参数 innodb_file_per_table，可以控制表数据是存在共享表空间里，还是存在单独的文件中：

• OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
•  ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。这样一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

在删除整个表的时候，可以使用 drop table 命令回收表空间。但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

数据删除流程

我们知道，InnoDB 里的数据都是用 B+ 树的结构组织的。我们要删掉 某个记录，InnoDB 引擎只会把  这个记录标记为删除，如果之后要再插入一个 记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

那么如果我们删掉了一个数据页上的所有记录，整个数据页就可以被复用了。但是，数据页的复用跟记录的复用是不同的，因为记录的复用，只限于符合范围条件的数据，而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。进一步地，如果我们用 delete 命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

假设某个索引的某个 数据页 已经满了，这时我要再插入范围属其中的一行数据，就不得不再申请一个新的页面 来保存数据了。页分裂完成后，旧页 的末尾就留下了空洞，而且可能不止 1 个记录的位置是空洞。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值，这也是会造成空洞的。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

重建表

基于上面的问题分析，解决空洞可以达到收缩空间的目的，重建表就行。

重建表的过程：

新建一个与原表结构相同的表 ，然后按照主键 ID 递增的顺序，把数据一行一行地从源表 里读出来再插入到新表 中。这样，在新表中就都不存在旧表 主键索引上的空洞了。显然地，新表的主键索引更紧凑，数据页的利用率也更高。如果我们把新表 作为临时表，数据导入新表 的操作完成后，新表替旧表，从效果上看，就起到了收缩旧表 空间的作用。

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

这个过程中，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到旧表 的话，就会造成数据丢失。因此，在整个 DDL 过程中，旧表 中不能有更新，也就是说，这个 DDL 不是 Online 的。

而在 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。

引入了 Online DDL 之后，重建表的流程：

1. 建立一个临时文件，扫描原表 主键的所有数据页；
2. 用数据页中原表的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对原表 的操作记录在一个日志文件（row log）中；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与原表 相同的数据文件；
5. 用临时文件替换表 A 的数据文件。

正常情况下，DDL 之前是要拿 MDL 写锁的，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。但是还不能直接解锁呢，要防止其他线程对这个表同时做 DDL。

对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

Online 和 inplace

说到 Online，还要再澄清一下它和另一个跟 DDL 有关的、容易混淆的概念 inplace 的区别。

上面说过，在版本5.5之前，重建表是往临时表插入数据，而版本5.6之后，是往临时文件放数据，前者是在server层干的事儿，后者是在InnoDB引擎层干的事儿。

那么，对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源，但是临时文件，也是要占用临时空间。

我们重建表的这个语句 alter table t engine=InnoDB，其实隐含的意思是，alter table t engine=innodb,ALGORITHM=inplace；

跟 inplace 对应的就是拷贝表的方式了，alter table t engine=innodb,ALGORITHM=copy；

使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是临时表的操作过程。

到这里，咋一看inplace也是online，但是其实，这里只是因为重建表的逻辑刚好是 inplace的同时还可以 DML操作。

比如，我要给 InnoDB 表的一个字段加全文索引，写法是：alter table t add FULLTEXT(field_name)；这个过程是 inplace 的，但会阻塞增删改操作，是非 Online 的。

这两个逻辑之间的关系可以概括为：

• DDL 过程如果是 Online 的，就一定是 inplace 的；
• 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

延伸一下，optimize table、analyze table 和 alter table 这三种方式重建表的区别：

• 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面临时文件存数据的流程了；
• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
• optimize table t 等于 recreate+analyze。

关于重建表，有个极端情况的问题：

有时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大。

原因：在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。如果再次重建之前，又有新的DML操作，会占用该页剩余空间。这时，再重新收缩的话，还会继续按1/16 预留空间，这样收缩之后，文件就反而变大了。

内容来源： 林晓斌 《MySQL实战45讲》