MySQL 为什么偶尔执行速度变慢

“脏页”：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

“干净页”：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

MySQL 执行偶尔变慢一会可能就是在刷脏页（flush）。

5.1 什么情况会引发数据库的 flush 过程呢？

• InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，将日志对应的所有脏页都 flush 到磁盘上，redo log 留出空间就可以继续写。
• 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
• MySQL 判断出系统处于空闲的时候。
• MySQL 正常关闭的时候。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

以上场景对性能的影响：

• 第三种和第四种情况，一种是系统空闲，一种是 MySQL 准备关闭，这种时候都不太需要关注性能问题。
• 第一种情况，redo log 写满了，需要 flush 刷脏页。这种情况是 InnoDB 要尽量避免的，因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果从监控上看，这时候更新数会跌为0。
• 第二种情况，内存不够用了，需要先将脏页写到磁盘上。这种情况其实是常态，InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
  • 第一种是还没有使用的。
  • 第二种是使用了并且是干净页。
  • 第三种是使用了并且是脏页。
  • InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
  • 而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页，这时候只能把最久不使用的数据页从内存中淘汰掉。
    • 如果要淘汰的是一个干净页，就直接释放出来复用。
    • 如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。
  • 所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
    • 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
    • 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。
  • 因此，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

5.2 InnoDB 刷脏页的控制策略

1. 首先，要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。
   • innodb_io_capacity：它会告诉 InnoDB 所在主机的磁盘能力。这个值建议设置成磁盘的 IOPS（可视为每秒读写次数） 。磁盘的 IOPS 可以通过 fio 这个工具来测试：
   • // 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
   • fio 使用介绍文章：https://blog.csdn.net/weixin_42241611/article/details/124367568
2. 设计策略控制刷脏页的速度需要参考的因素：
   • 脏页比例。参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是75%。
   • redo log 的写盘速度。

1. 现在知道了，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到更新语句，都可能是造成从业务端感知到 MySQL “抖”了一下的原因。
   • 要尽量避免这种情况，就要合理地设置 innodb_io_capacity 的值，并且平时时要多关注脏页比例，不要让它经常接近 75%。
   • 脏页比例是通过 Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 得到的，SQL 语句如下：
   • 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;

1. MySQL 的“连坐”机制：当 MySQL 准备刷一个脏页时，如果这个数据页旁边的数据页也是脏页，就将旁边的数据也一起刷掉，还可以继续蔓延。
   • 在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
   • 找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO 。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
   • 而如果使用的是 SSD 这类 IOPS 比较高的设备的话，就建议把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。
   • 在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

六、为什么表数据删了表文件大小却不变

一个 InnoDB 表包含两部分：表结构定义和表数据。

在 MySQL 8.0 以前，表结构存在以 .frm 为后缀的文件里。

在 MySQL 8.0 版本及以后，已经允许把表结构定义放在系统数据表中了。

参数innodb_file_per_table：表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
3. 从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。
4. 建议不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

6.1 数据删除、插入带来的“空洞”

数据删除：

1. 数据在删除的时候，只是标记数据删除了，而使用空间并不会减少，而是变为一个可复用的位置。
2. 整个数据页的记录删除时，整个数据页就变为可复用的数据页了。
3. 数据页的复用跟记录的复用是不同的。记录的复用需要相同的值才可以复用同一个位置，而数据页的复用时整个页当成新页一样使用。
4. delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

数据插入：

1. 如果数据是随机插入的，就可能造成索引的数据页分裂。
2. 如果数据页满了，再进行插入，就不得不申请一个新的页面。页分裂完成后，原数据页分裂出去的数据原来的位置可能就留下了“空洞”。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

而重建表，就可以达到这样的目的。

6.2 重建表

• 可以使用 alter table A engine=InnoDB 命令。
• 在 MySQL 5.5 版本及之前，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。但如果有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。
• 在 MySQL 5.6 版本开始引入的 Online DDL ，对这重建表的操作流程做了优化。
• 重建表流程：
  1. 建立一个临时文件，扫描表 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
  5. 用临时文件替换表 A 的数据文件。

• 由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。
• DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？
• 在上图的流程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。
• 为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。
• 那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。
• 而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
• 上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用 GitHub 开源的 gh-ost 来做。

6.3 Online 和 inplace

• inplace：是指整个 DDL 过程都在 InnoDB 内部完成，对于 server 层来说，没有把数据挪动到临时表，就是一个“原地”操作。

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

• 跟inplace对应的就是拷贝表的方式了，用法是：
• alter table t engine=innodb,ALGORITHM=copy;

Online 和 inplace 的关系：

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

七、如何改善使用 Count 的性能问题

7.1 count(*)的实现方式

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
• 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
• 需要注意的是，这里讨论的是没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

7.2 为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢

• 因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。
• InnoDB 的默认的隔离级别是可重复读，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

MySQL 在执行 count(*) 操作的时候做了优化：

• InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

7.3 解决 count 太慢的办法

思路：找一个地方把操作记录表的行数存起来。

• 用缓存系统保存技术。在并发系统中，无法确保计数值在逻辑上的精确。把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。
• 用数据库保存计数，把这个计数直接放到数据库里单独的一张计数表C中，然后利用事务解决数值精确问题。

7.4 不同的 count 比较

count(*)、count(主键id)、count(字段)和count(1)等不同用法的性能，有哪些差别？

• count(*)、count(主键id) 和 count(1) 都表示返回满足条件的结果集的总行数。
• 而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

至于分析性能差别的时候，可以记住这么几个原则：

1. server 层要什么就给什么；
2. InnoDB 只给必要的值；
3. 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

• 对于 count(主键id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• count(1) 执行得要比 count(主键id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

• 对于count(字段)来说：
  1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
  2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。
• 但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。 count(*) 肯定不是 null，按行累加。

结论是：按照效率排序的话，count(字段) < count(主键id) < count(1) ≈ count(*) ，所以建议尽量使用 count(*)。