引言

本文分析一个主库与从库磁盘使用率差异大的案例，发现差异大的原因是从库系统表空间大小 296G，使用 innochecksum 工具分析显示 undo log 大小 288G，因此判断是长事务导致 ibdata 文件膨胀后空间未释放。分析过程中介绍了 ibdata、undo log、ibtmp 的相关知识点。

现象

时间：20230708 12:00

版本：5.7.24

现象：从库磁盘打满，且日志量很少，而主库正常

配置：16C /64G /4000G

分析

监控

从库磁盘打满，主库没有打满。监控显示主从差异在于系统数据。

ibdata

查看系统数据文件，显示从库的 ibdata2 文件大小等于 296G，而主库等于 3G。

通过 innochecksum 工具查看系统表空间中数据页的分配情况。

[root@MSS-yf58l7j9nm data]# /export/servers/mysql/bin/innochecksum -S ibdata2

File::ibdata2
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
   88115  Index page
18918133  Undo log page
  220518  Inode page
   46164  Insert buffer free list page
  117482  Freshly allocated page
    1178  Insert buffer bitmap
       0  System page
       0  Transaction system page
       0  File Space Header
    1178  Extent descriptor page
       0  BLOB page
       0  Compressed BLOB page
       0  Other type of page
===============================================
Additional information:
Undo page type: 4989 insert, 18913144 update, 0 other
Undo page state: 0 active, 399 cached, 227 to_free, 18913049 to_purge, 0 prepared, 4458 other

其中：

• Undo log page 18918133，大小 288G。

mysql> select 18918133*16/1024/1024;
+-----------------------+
| 18918133*16/1024/1024 |
+-----------------------+
|          288.66780090 |
+-----------------------+
1 row in set (0.00 sec)

查看 undo log 配置。

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.00 sec)

原理

tablespace

MySQL 中表空间是一个逻辑概念，对应文件系统中的物理文件。

表空间由段（segment）、区 （extent）、页 (page / block）组成 ，表明表空间用于管理页。

MySQL 中共有以下 5 种类型的表空间。

ibdata

ibdata 文件是 MySQL 系统表空间（ system tablespace）的数据文件，其中存放的数据包括：

• InnoDB data dictionary，InnoDB 表的数据字典
• doublewrite buffer
• change buffer
• undo log，前提是未使用 undo tablespaces
• table and index data，前提是未开启 innodb_file_per_table

系统表空间相关参数主要包括：

• innodb_data_file_path
• innodb_autoextend_increment
• innodb_data_home_dir

其中innodb_data_file_path参数控制系统表空间数据文件的大小与数量。

innodb_data_file_path defines the name, size, and attributes of InnoDB system tablespace data files.

innodb_data_file_path参数配置的完整语法如下所示。

file_name:file_size[:autoextend[:max:max_file_size]]

默认ibdata1:12M:autoextend，表示：

• 默认一个文件，文件名为 ibdata1；
• 默认大小 12M，由于指定 autoextend，因此在文件写满后自动扩展，并且没有上限；
• innodb_autoextend_increment参数控制文件每次自动扩展的增量大小，默认 64M；
• innodb_data_home_dir参数控制系统表空间数据文件保存的路径，默认数据目录 data directory。

如下所示是测试数据库的相关配置。

mysql> show variables like '%innodb_data%';
+-----------------------+-------------------------------------+
| Variable_name         | Value                               |
+-----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:1024M:autoextend            |
| innodb_data_home_dir  | /export/zhangkai321/mysql/3341/data |
+-----------------------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%innodb_autoextend_increment%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.00 sec)

系统表空间的主要问题是文件膨胀，常见原因包括：

• table and index data，未使用独立表空间
• undo log，如长事务

增加系统表空间大小的方式有两种：

• 将 innodb_data_file_path 配置为 autoextend，并支持设置上限，比如从 ibdata1:10M 修改为 ibdata1:10M:autoextend:1024M；
• 将 innodb_data_file_path 配置为多个文件，比如从 ibdata1:10M:autoextend 修改为 ibdata1:1024M;ibdata2:10M:autoextend。

注意：

• innodb_data_file_path 参数是静态参数，因此重启生效；
• innodb_data_file_path 参数不支持修改已有的文件大小，比如不允许从 ibdata1:10M 修改为 ibdata1:20M。

缩小系统表空间只有一种方式，就是逻辑导出后导入进行重建，8.0 中依然如此。

未开启 innodb_file_per_table 时，表数据写入系统表空间，即使删除表，也不会释放空间。

Unlike the system tablespace, file-per-table tablespaces return disk space to the operating system when they are truncated or dropped.

因此建议开启该参数，将每个表的数据存储在独立表空间，而不是使用系统表空间。

undo log

undo log 是 InnoDB 引擎用来保证 ACID 事务特性中的原子性（Atomicity）和持久性（Durability）的关键部分，具体指的是 MVCC 与 crash recovery。

InnoDB is a multi-version storage engine. It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in the system tablespace or undo tablespaces in a data structure called a rollback segment.

undo log 记录逻辑日志，如 insert 操作对应 undo log 中 delete 日志，update 操作对应 undo log 中相反的 update 记录。

具体分为以下两种类型：

• insert undo log，对应 insert 操作，回滚指针（DATA_ROLL_PTR）为 NULL，仅用于事务回滚，因此事务提交后可以马上删除；
• update undo log，对应 update 与 delete 操作，回滚指针指向数据行的上一个版本数据，用于事务回滚与一致性读（MVCC），因此事务提交后不可以马上删除。

如下图所示，索引中的同一个 record 被不同事务修改，会产生不同的历史版本，这些历史版本又通过 Rollptr 穿成一个链表，供 MVCC 使用。

其中：

• insert 类型 undo record 中只记录了对应的主键值：id=1；
• update 类型的 undo record 中还记录了对应的历史版本的生成事务 Trx_id，以及被修改的 field a 的历史值。

update undo log 在事务提交后按照事务提交的顺序挂载到 History List 上，由后台 purge 线程判断当没有其他事务需要访问旧版本的数据时删除。

具体是通过事务提交序号来判断其他事务是否需要访问旧版本的数据，包括以下两个序号：

• InnoDB 中每个写事务结束时都会拿一个递增的编号 trx_no 作为事务的提交序号；
• 每个读事务会在自己的 ReadView 中记录自己开始的时候看到的最大的 trx_no 为 m_low_limit_no。

如果一个事务提交的 trx_no 序号小于所有活跃的读事务 ReadView 中的 m_low_limit_no，表明该写事务在所有的读之前已提交，其修改的新版本是可见的，不需要通过 undo log 构建之前的版本，因此可以清理 undo log。

如下图所示，如果事务 K 在事务  J 提交后开始获取 ReadView，所有的读事务都可以通过 Index 中的版本或第一个 undo log（即事务 K）构建老版本，不需要更老的 undo log，因此事务 J 的 undo log 可以清理。

因此 undo log 膨胀的常见原因如：

• 长事务，运行时间很长的 select
• 大事务，大量 DML 操作
• purge 线程延迟

其中：

• 建议定期提交事务，即使事务中只有一致性读（快照读），否则 InnoDB 无法删除 update undo log，从而导致 rollback segment 越来越大；
• purge 线程延迟可以通过 History list length 参数进行评估，该参数表示已提交事务未清理的 undo log 数量。

The InnoDB history list is a global list of the undo logs for committed transactions. MySQL uses the history list to purge records and log pages when transactions no longer require the history. The history list length is the total number of undo logs that contain modifications in the history list. Each log contains one or more modifications. If the InnoDB history list length grows too large, indicating a large number of old row versions, queries and database shutdowns become slower.

History list length 非常大可能导致慢 SQL、主从延迟、关机慢，甚至导致数据库 hang，详见文档 MySQL undo log History list length夯住。

以下两种场景下可能导致 History list length 非常大：

• 长事务
• 写入负载高

MySQL 5.5 及以前的版本，undo log 跟数据字典一起放在 ibdata 文件里。

MySQL 5.6 增加了以下 5 个参数，可以把 undo log 从 ibdata 移出来单独存放。

• innodb_undo_directory，用于控制 undo log 的存储路径，默认 ./，表示保存在数据文件路径；
• innodb_undo_tablespaces，用于控制 undo tablespaces 的数量，默认 0，表示所有的 undo log 将会存储在系统表空间中，即ibdata1文件中；
• innodb_undo_logs，用于控制回滚段的数量，早期版本该参数名字是 innodb_rollback_segments，默认 128，建议大于等于 3；
• innodb_max_undo_log_size，用于控制 undo tablespaces 标记为可收缩的阈值，默认 1G；
• innodb_undo_log_truncate，用于控制是否开启在线收缩超过 innodb_max_undo_log_size 阈值的 undo tablespaces。默认关闭。注意系统表空间的 undo log 不支持在线收缩。

MySQL undo膨胀了怎么办？文章中详细模拟了 undo tablespaces 的在线收缩。

如下所示，查询当前实例的相关配置。

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.01 sec)

其中：

• 显示 undo log 保存在系统表空间中，并未独立出来，因此无法在线收缩。

不同于 undo tablespaces，undo log 最大的问题是即使长事务最终提交，回滚段被清理，文件也不会变小。

因此如果未开启 innodb_undo_tablespaces 和 innodb_undo_log_truncate，只能通过将数据逻辑导出导入的方式来释放空间，通常的处理方式是新建实例。

undo log 保存在 rollback segments 中的 undo log segments 中，每个 undo segment 会持有至少一个 undo page。

每一时刻一个 undo segment 都是被一个事务独占的。每个写事务都会持有至少一个 undo segment，当有大量写事务并发运行时，就需要存在多个 undo segment。InnoDB 中的 undo 文件中准备了大量的 undo segment slots，按照 1024 一组划分为 rollback segment。每个 undo tablespace 最多会包含 128 个 rollback segment，undo tablespace 文件中的第三个 page 会固定作为这 128 个 rollback segment 的目录，也就是 Rollback Segment Arrary Header。

因此 rollback segment 的数量会影响到 InnoDB 支持的最大事务并发数。

如果事务中可能是增删改操作，支持的最大事务并发数计算公式如下。

(innodb_page_size / 16) * (innodb_rollback_segments - 32)

其中：

• 每个 rollback segment 中 undo segment slots 的个数与 InnoDB page size 有关；
• 每个 undo tablespace 最多包含 128 个 rollback segment，其中 32 个用于 temporary tablespace。

回到本文的案例，查看事务信息。

------------
TRANSACTIONS
------------
Trx id counter 49700654240
Purge done for trx's n:o < 49700654240 undo n:o < 0 state: running but idle
History list length 42

其中：

• History list length 42 表明不存在 purge 线程延迟。

活跃线程也很少，而且执行用时很短。

---TRANSACTION 49700654238, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 14844147, OS thread handle 140121945372416, query id 48393023611 System lock
---TRANSACTION 49700654237, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 14844146, OS thread handle 140100718880512, query id 48393023609 System lock
---TRANSACTION 49700654235, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 14844144, OS thread handle 140113123063552, query id 48393023605 System lock

因此判断是长事务导致 undo 膨胀，而该事务已结束，但空间未释放。

除了系统表空间膨胀，常见的还有临时表空间膨胀，下面进行简单介绍。

ibtmp

MySQL 3.23 中引入临时表，临时表分为外部临时表与内部临时表。

外部临时表通过 create temporary table 语法创建。

内部临时表用于进行性能优化，会被 MySQL 自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段，执行计划的 Extra 中包含 Using temporary。

如果 SQL 在执行过程中读取到的数据无法直接得到结果，就需要额外的内存来保存中间结果，这个额外的内存就是内部临时表。比如 group by 执行时，就需要构建一个临时表，需要额外的字段保存聚合函数的结果。

使用到内部临时表的语法如 group by、order by、distinct、union。

内部临时表分为 in-memory 临时表与 on-disk 临时表。

参数 tmp_table_size（默认 16M）与 max_heap_table_size（默认 16M）用于限制内部临时表在内存中的大小。

两参数的区别是 max_heap_table_size 也用于限制外部临时表的大小；

当 in-memory 临时表的大小超过两参数中的最小值时，in-memory 临时表将自动转换成 on-disk 临时表，称为临时表溢出。

即：min(tmp_table_size，max_heap_table_size) => disk

If an internal temporary table is created as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table.

具体 on-disk 临时表保存的路径与数据库版本有关。

• MySQL 5.6 中：
• 如果开启 innodb_file_per_table，路径是临时目录（tmpdir）；
• 如果关闭 innodb_file_per_table，路径是系统表空间（ibdata）；
• MySQL 5.7 中，路径是临时表空间（ibtmp），文件具体的大小和名称由innodb_temp_data_file_path参数控制。

innodb_temp_data_file_path参数配置的完整语法如下所示。

file_name:file_size[:autoextend[:max:max_file_size]]

默认ibtmp1:12M:autoextend，表示：

• 默认一个文件，文件名为 ibtmp1；
• 默认大小 12M，由于指定 autoextend，因此在文件写满后自动扩展，并且没有上限；
• innodb_autoextend_increment参数控制文件每次自动扩展的增量大小，默认 64M；
• 显示与innodb_data_file_path参数的默认值除文件名外相同。

前文提到，当 undo log 保存在系统表空间 ibdata 中时，即使事务提交，空间也不会释放，释放空间需要通过逻辑备份完成。

临时表空间 ibtmp 的释放方式与版本有关系：

• MySQL 5.6 中：
• 如果开启 innodb_file_per_table（tmpdir），空间会自动释放，原因是使用独立表空间，但创建和删除临时表降低性能；
• 如果关闭 innodb_file_per_table（ibdata），空间不会自动释放，逻辑导出数据后释放空间；
• MySQL 5.7（ibtmp），空间不会自动释放，重启可以释放空间，原因是 MySQL 服务停止时将删除共享临时表空间，然后启动时新建。如果临时表看空间创建失败，MySQL 服务启动失败。

如下所示是测试数据库的相关配置。

mysql> select @@innodb_temp_data_file_path;
+--------------------------------+
| @@innodb_temp_data_file_path   |
+--------------------------------+
| ibtmp1:12M:autoextend:max:200M |
+--------------------------------+
1 row in set (0.00 sec)

停止后启动数据库，查看错误日志。

2023-12-08T21:05:01.099748+08:00 0 [Note] InnoDB: Starting shutdown...
2023-12-08T21:05:02.931603+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2023-12-08T21:05:02.932449+08:00 0 [Note] /export/servers/mysql/bin/mysqld: Shutdown complete

2023-12-08T21:05:13.860847+08:00 0 [Note] /export/servers/mysql/bin/mysqld (mysqld 5.7.24-log) starting as process 31805 ...
2023-12-08T21:05:13.946930+08:00 0 [Note] InnoDB: Opened 4 undo tablespaces
2023-12-08T21:05:13.946947+08:00 0 [Note] InnoDB: 4 undo tablespaces made active
2023-12-08T21:05:14.046512+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-12-08T21:05:14.046591+08:00 0 [Note] InnoDB: Setting file '/export/zhangkai321/mysql/3341/data/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-12-08T21:05:14.080904+08:00 0 [Note] InnoDB: File '/export/zhangkai321/mysql/3341/data/ibtmp1' size is now 12 MB.
2023-12-08T21:05:14.081954+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-12-08T21:05:14.081969+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-12-08T21:05:14.120822+08:00 0 [Note] /export/servers/mysql/bin/mysqld: ready for connections.

其中：

• 停止时删除临时表空间；
• 启动时创建临时表空间，默认 12M；
• 启动时创建 undo tablespaces，其中 32 个 rollback segment 分配给临时表空间，96 个分配给系统表空间。

innochecksum

innochecksum 是 MySQL 自带的离线表空间文件分析工具。

该工具用于校验表空间的 checksum，具体是计算每个页面的 checksum，然后与数据页 File Header 中存储的 checksum（FIL_PAGE_SPACE_OR_CHKSUM）进行比较，如果两者不等，表明数据页损坏。

常见参数及功能包括：

• –page-type-summary, -S，展示表空间中每种类型 page 的数量；
• –page-type-dump, -D，展示表空间中每个 page 的信息；
• –strict-check, -C，指定校验方式并检验，默认 crc32。如果没有输出，表明数据页完整。

-S，查看系统表空间中每种类型 page 的数量，报错，原因是要求数据库停止。

[root@test data]# /export/servers/mysql/bin/innochecksum -S ibdata1
Error: Unable to lock file:: ibdata1
fcntl: Resource temporarily unavailable

停止后重新执行。

[root@test data]# /export/servers/mysql/bin/innochecksum -S ibdata1 

File::ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
      96        Index page
      20        Undo log page
       1        Inode page
       0        Insert buffer free list page
   65384        Freshly allocated page
       1        Insert buffer bitmap
      29        System page
       2        Transaction system page
       3        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 20 update, 0 other
Undo page state: 0 active, 18 cached, 0 to_free, 2 to_purge, 0 prepared, 0 other

其中：

• 20 Undo log pag，20 update，18 cached，2 to_purge
• 65384 Freshly allocated page，表示已分配未使用的页。

查看 undo 配置

mysql> show variables like '%undo%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| innodb_max_undo_log_size | 1073741824                          |
| innodb_undo_directory    | /export/zhangkai321/mysql/3341/undo |
| innodb_undo_log_truncate | OFF                                 |
| innodb_undo_logs         | 128                                 |
| innodb_undo_tablespaces  | 4                                   |
+--------------------------+-------------------------------------+
5 rows in set (0.00 sec)

查看 undo 文件，显示使用 undo tablespaces。

[root@test data]# ll -h /export/zhangkai321/mysql/3341/undo
total 40M
-rw-r----- 1 mysql mysql 10M Jul  8 14:41 undo001
-rw-r----- 1 mysql mysql 10M Jul  8 14:41 undo002
-rw-r----- 1 mysql mysql 10M Jul  8 14:41 undo003
-rw-r----- 1 mysql mysql 10M Jul  8 14:41 undo004

分析 undo tablespaces

[root@test data]# /export/servers/mysql/bin/innochecksum -S ../undo/undo001 

File::../undo/undo001
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
       0        Index page
    9747        Undo log page
       1        Inode page
       0        Insert buffer free list page
     466        Freshly allocated page
       1        Insert buffer bitmap
      24        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 9726 insert, 21 update, 0 other
Undo page state: 0 active, 34 cached, 1 to_free, 1 to_purge, 0 prepared, 9711 other

其中：

• 9747 Undo log page，9726 insert, 21 update，34 cached, 1 to_free, 1 to_purge

-D，查看独立表空间中每个 page 的信息。

[root@test data]# /export/servers/mysql/bin/innochecksum --page-type-dump=t1.ibd.txt test_zk/t1.ibd 
[root@test data]# 
[root@test data]# ll -h t1.ibd.txt 
-rw-r--r-- 1 root root 741 Dec  9 11:40 t1.ibd.txt
[root@test data]# 
[root@test data]# cat t1.ibd.txt 

Filename::test_zk/t1.ibd
==============================================================================
        PAGE_NO         |               PAGE_TYPE                       |       EXTRA INFO
==============================================================================
#::       0             |               File Space Header               |       -
#::       1             |               Insert Buffer Bitmap            |       -
#::       2             |               Inode page                      |       -
#::       3             |               Index page                      |       index id=629, page level=0, No. of records=8, garbage=0, -
#::       4             |               Index page                      |       index id=630, page level=0, No. of records=8, garbage=0, -
#::       5             |               Index page                      |       index id=631, page level=0, No. of records=8, garbage=0, -
#::       6             |               Index page                      |       index id=631, page level=0, No. of records=8, garbage=0, -
#::       7             |               Freshly allocated page          |       -

其中：

• File Space Header 是表空间的第一个页，其中记录表空间的元数据信息，如表空间 ID Space_id、管理每个段中区的链表的基节点。一个区是在物理位置上连续的 64 个页，并根据区的使用情况将其分为三种状态，包括空闲区 FREE、有剩余空间的碎片区 FREE_FRAG、没有剩余空间的碎片区 FULL_FRAG。插入数据时优先从 FREE_FRAG 中找空闲页，否则申请一个状态为 FREE 的区；
• Insert Buffer Bitmap 是表空间的第二个页，其中记录 chage buffer 相关信息。通过维护位图跟踪每个非聚簇索引页的使用情况，从而判断是否有足够空间存放 insert buffer 中的记录，否则就会发生页拆分；
• Inode page 是表空间的第三个页，其中记录 INODE Entry 结构相关信息。一个索引会产生两个段，分别是叶子节点段和非叶子节点段，而每个段都会对应一个 INODE Entry 结构，B+ 树的根页中定义每个段的头部信息。

指定校验方式为 innodb 并校验。

[root@test data]# /export/servers/mysql/bin/innochecksum --strict-check=innodb test_zk/t1.ibd 
[root@test data]#

其中没有输出，表明数据页完整。

最后介绍两个相关案例，都是长事务导致系统表空间膨胀，其中一个事务已提交，另一个事务执行中。

相关案例

案例 1

现象

时间：2023-08-18

现象：主从磁盘空间差异大，其中系统数据差异大

监控

从库系统数据使用量远远高于主库

系统数据大小变化，6月24日开始增长直到7月27日，共增长了 250G 左右

长事务，6月24日开始增长直到7月27日

事务信息

{
        "trx_id":"281475292762032",
        "trx_started":"2023-06-24 22:54:33",
        "scan_time":"2023-07-25 21:43:11",
        "trx_exec_time_sec":"2674118",
        "trx_state":"RUNNING",
        "trx_lock_structs":"0",
        "trx_lock_memory_bytes":"1136",
        "trx_rows_locked":"0",
        "trx_rows_modified":"0",
        "trx_isolation_level":"REPEATABLE READ",
        "processlist_id":"113435803",
        "USER":"las_rw",
        "HOST":"x.x.x.x:39886",
        "DB":"las_waybill_report",
        "Command":"Query",
        "executing_sql":"select t.dcNo as dcNo, SUM(t.totalOrderCount) as deliveryFinishCount, SUM(t.selfOrderCount) as selfDeliveryFinishCount, SUM(t.outSideOrderCount) as outDeliveryFinishCount, SUM(t.furnitureOrderCount) as furnitureDeliveryFinishCount, SUM(t.electricOrderCount) as electricDeliveryFinishCount from ( select wr.to_delivery_id as dcNo, SUM( case when wr.waybill_type in(0, 1, 8) then 1 else 0 end ) as totalOrderCount, SUM( case when wr.waybill_type = 8 then 1 else 0 end ) as selfOrderCount, SUM( case when wr.waybill_type in(0, 1) then 1 else 0 end ) as outSideOrderCount, SUM( case when wr.network_type = 1 then 1 else 0 end ) as furnitureOrderCount, SUM( case when wr.network_type = 0 then 1 else 0 end ) as electricOrderCount from waybill_report wr force index (idx_delivery_task) inner join waybill_oper_log_temp wol on wol.waybill_no = wr.waybill_no where wr.is_delete = 0 and wol.is_delete = 0 and wr.create_time >= STR_TO_DATE('2023-06-18 00:55:00','%Y-%m-%d %H:%i:%s') and wr.create_time < STR_TO_DATE('2023-06-18 01:00:00','%Y-%m-%d %H:%i:%s') and wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'SELF_PICKED' and wr.waybill_type in(0, 1, 8) and wr.pick_up_type != 2 and wol.after_first_status = 'COMPLETE' and wol.after_status = 'SELF_PICKED' and wol.action_time >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wol.action_time <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') group by wr.to_delivery_id union all select wr.to_delivery_id as dcNo, SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_DELIVERED' and wti.delivery_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.delivery_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.waybill_type in(0, 1, 8) then 1 else 0 end ) + SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_CUSTOMER_REJECTION' and wti.reject_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.reject_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.waybill_type in(0, 1, 8) then 1 else 0 end ) as totalOrderCount, SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_DELIVERED' and wti.delivery_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.delivery_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.waybill_type = 8 then 1 else 0 end ) + SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_CUSTOMER_REJECTION' and wti.reject_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.reject_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.waybill_type = 8 then 1 else 0 end ) as selfOrderCount, SUM( case when wr.waybill_type in(0, 1) and wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_DELIVERED' and wti.delivery_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.delivery_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') then 1 else 0 end ) + SUM( case when wr.waybill_type in(0, 1) and wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_CUSTOMER_REJECTION' and wti.reject_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.reject_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') then 1 else 0 end ) as outSideOrderCount, SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_DELIVERED' and wti.delivery_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.delivery_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.network_type = 1 then 1 else 0 end ) + SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_CUSTOMER_REJECTION' and wti.reject_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.reject_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.network_type = 1 then 1 else 0 end ) as furnitureOrderCount, SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_DELIVERED' and wti.delivery_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.delivery_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.network_type = 0 then 1 else 0 end ) + SUM( case when wr.waybill_status = 'COMPLETE' and wr.waybill_detail_status = 'TMS_CUSTOMER_REJECTION' and wti.reject_date >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') and wti.reject_date <= DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') and wr.network_type = 0 then 1 else 0 end ) as electricOrderCount from waybill_report wr force index (idx_waybill_type_time) inner join waybill_time_info wti on wr.waybill_no = wti.waybill_no where wr.is_delete = 0 and wti.is_delete = 0 and wr.create_time >= STR_TO_DATE('2023-06-18 00:55:00','%Y-%m-%d %H:%i:%s') and wr.create_time < STR_TO_DATE('2023-06-18 01:00:00','%Y-%m-%d %H:%i:%s') and wr.waybill_type in(0, 1, 8) and wr.pick_up_type != 2 group by wr.to_delivery_id ) t group by t.dcNo",
        "last_executed_sql":"select t.dcNo as dcNo, SUM(t.totalOrderCount) as deliveryFinishCount, SUM(t.selfOrderCount) as selfDeliveryFinishCount, SUM(t.outSideOrderCount) as outDeliveryFinishCount, SUM(t.furnitureOrderCount) as furnitureDeliveryFinishCount, SUM(t.electricOrderCount) as electricDeliveryFinishCount from ( select wr.to_delivery_id as dcNo, SUM( case when wr.waybill_type in(0, 1, 8) then 1 else 0 end ) as totalOrderCount, SUM( case when wr.waybill_type = 8 then 1 else 0 end ) as selfOrderCount, SUM( case when wr.waybill_type in(0, 1) then 1 else 0 end ) as outSideOrderCount, SUM( case when wr.network_type = 1 then 1 else 0 end ) as furnitureOrderCount, SUM( case when wr.network_type = 0 then 1 else 0 end ) as electricOrderCount from waybill_report wr force index (idx_delivery_task) ..."
    }

其中：

• “trx_started”:”2023-06-24 22:54:33″，”scan_time”:”2023-07-25 21:43:11″，分别是事务开始时间与监控扫描时间；
• “trx_exec_time_sec”:”2674118″，已执行一个月；
• “trx_state”:”RUNNING”，表明是执行中慢 SQL，因此判读是长事务导致 undo log 膨胀。

案例 2

现象

现象：从库磁盘打满，导致主从复制停止。

处理：将从库域名切换到主库，然后开始从库扩容。

问题：从库磁盘打满的原因是什么？

分析

如下所示，从库的系统表空间达到 271G，其中 263G 是共享表空间。

发现长事务，已执行 1734287s，相当于 20 天。

显示 History list length 439471467，表明 purge 线程延迟严重，长事务导致大量 undo log 未清理。

结论

系统表空间的主要问题是文件膨胀，常见原因包括：

• table and index data，未使用独立表空间

导致即使删除表，空间也不会释放，因此建议开启 innodb_file_per_table，使用独立表空间。

• undo log，如长事务

导致即使事务提交，空间也不会释放，因此建议开启  innodb_undo_tablespaces 和 innodb_undo_log_truncate。

与系统表空间膨胀类似，经常遇到的还有临时表空间膨胀：

• 系统表空间膨胀，对应 ibtmp 文件，常见原因是 SQL 中使用临时表，5.7 中重启可释放空间；
• 临时表空间膨胀，对应 ibdata 文件，常见原因是长事务，5.7 中逻辑导出数据可释放空间。

undo log 记录逻辑日志，包括以下两种类型：

• insert undo log，仅用于事务回滚，因此事务提交后可以马上删除；
• update undo log，用于事务回滚与一致性读（MVCC），因此在事务提交后按照事务提交的顺序挂载到 History List 上，由后台 purge 线程判断当没有其他事务需要访问旧版本的数据时删除。

undo log 膨胀的常见原因如：

• 长事务，运行时间很长的 select
• 大事务，大量 DML 操作
• purge 线程延迟，可以通过 InnoDB history list 参数评估

待办

• undo log
• segment、extent、page

参考教程

• 【MySQL】ibdata文件增大的原因

• MySQL ibdata1 文件“减肥”记
• MySQL · 引擎特性 · 庖丁解InnoDB之UNDO LOG

• MySQL 5.7 Reference Manual  /  The InnoDB Storage Engine  /  InnoDB Multi-Versioning

• MySQL undo膨胀了怎么办？
• The InnoDB history list length increased significantly

• MySQL undo log History list length夯住

• MySQL 临时表溢出
• 《MySQL 是怎样运行的：从根儿上理解 MySQL》