InnoDB 在使用 SQL 语句删除行时，不会立即从数据库中物理删除该行。只有当 InnoDB 丢弃为删除操作写入的 undo 日志记录时，行及其索引记录才会被物理删除。这个删除操作只有在该行不再用于多版本并发控制（MVCC）或回滚时才会发生，称为清除（purge）。InnoDB does not physically remove a row from the database immediately when you delete it with an SQL statement. A row and its index records are only physically removed when InnoDB discards the undo log record written for the deletion. This removal operation, which only occurs after the row is no longer required for multi-version concurrency control (MVCC) or rollback, is called a purge.

清除操作按周期性计划运行。它解析并处理来自历史列表的 undo 日志页，历史列表是 InnoDB 事务系统维护的已提交事务的 undo 日志页列表。清除操作在处理完这些 undo 日志页后，会释放历史列表中的这些日志页。Purge runs on a periodic schedule. It parses and processes undo log pages from the history list, which is a list of undo log pages for committed transactions that is maintained by the InnoDB transaction system. Purge frees the undo log pages from the history list after processing them.

配置清除线程Configuring Purge Threads

清理操作由一个或多个清理线程在后台执行。清理线程的数量由 innodb_purge_threads 变量控制。如果可用逻辑处理器的数量 <= 16，默认值为 1，否则默认值为 4。Purge operations are performed in the background by one or more purge threads. The number of purge threads is controlled by the innodb_purge_threads variable. The default value is 1 if the number of available logical processors is <= 16, otherwise the default is 4.

如果 DML 操作集中在单个表上，该表的清除操作将由单个清除线程执行，这可能导致清除操作变慢、清除延迟增加，并且如果 DML 操作涉及大对象值，表空间文件大小也会增加。如果超过了 innodb_max_purge_lag 设置，清除工作将自动在可用的清除线程之间重新分配。在这种情况下，过多的活跃清除线程可能会与用户线程竞争，因此应适当管理 innodb_purge_threads 设置。innodb_max_purge_lag 变量默认设置为 0，这意味着默认情况下没有最大清除延迟。If DML action is concentrated on a single table, purge operations for the table are performed by a single purge thread, which can result in slowed purge operations, increased purge lag, and increased tablespace file size if the DML operations involve large object values. If the innodb_max_purge_lag setting is exceeded, purge work is automatically redistributed among available purge threads. Too many active purge threads in this scenario can cause contention with user threads, so manage the innodb_purge_threads setting accordingly. The innodb_max_purge_lag variable is set to 0 by default, which means that there is no maximum purge lag by default.

如果 DML 操作集中在少数几张表上，请将 innodb_purge_threads 设置保持较低，以避免线程在访问繁忙表时相互竞争。如果 DML 操作分布在多张表上，可以考虑将 innodb_purge_threads 设置得更高。最大清除线程数为 32。If DML action is concentrated on few tables, keep the innodb_purge_threads setting low so that the threads do not contend with each other for access to the busy tables. If DML operations are spread across many tables, consider a higher innodb_purge_threads setting. The maximum number of purge threads is 32.

innodb_purge_threads 设置表示允许的最大清除线程数。清除系统会自动调整使用的清除线程数量。The innodb_purge_threads setting is the maximum number of purge threads permitted. The purge system automatically adjusts the number of purge threads that are used.

配置清除批量大小Configuring Purge Batch Size

innodb_purge_batch_size 变量定义了从历史列表中一次批量解析和处理的撤销日志页的数量。默认值是 300。在多线程清理配置中，协调员清理线程将 innodb_purge_batch_size 除以 innodb_purge_threads，并将该页数分配给每个清理线程。The innodb_purge_batch_size variable defines the number of undo log pages that purge parses and processes in one batch from the history list. The default value is 300. In a multithreaded purge configuration, the coordinator purge thread divides innodb_purge_batch_size by innodb_purge_threads and assigns that number of pages to each purge thread.

清理系统还会释放不再需要的回滚日志页。它每处理 128 次回滚日志操作时执行一次。此外，除了定义每批次解析和处理的回滚日志页数外，innodb_purge_batch_size 变量还定义了清理每 128 次回滚日志操作时释放的回滚日志页数。The purge system also frees the undo log pages that are no longer required. It does so every 128 iterations through the undo logs. In addition to defining the number of undo log pages parsed and processed in a batch, the innodb_purge_batch_size variable defines the number of undo log pages that purge frees every 128 iterations through the undo logs.

innodb_purge_batch_size 变量用于高级性能调优和实验。大多数用户无需将 innodb_purge_batch_size 从默认值更改。The innodb_purge_batch_size variable is intended for advanced performance tuning and experimentation. Most users need not change innodb_purge_batch_size from its default value.

配置最大清理滞后Configuring the Maximum Purge Lag

innodb_max_purge_lag 变量定义了期望的最大清除延迟。当清除延迟超过 innodb_max_purge_lag 阈值时，将对 INSERT、UPDATE 和 DELETE 操作施加延迟，以便清除操作有时间赶上。默认值为 0，这意味着没有最大清除延迟，也没有延迟。The innodb_max_purge_lag variable defines the desired maximum purge lag. When the purge lag exceeds the innodb_max_purge_lag threshold, a delay is imposed on INSERT, UPDATE, and DELETE operations to allow time for purge operations to catch up. The default value is 0, which means there is no maximum purge lag and no delay.

InnoDB 事务系统维护一个事务列表，这些事务的索引记录被 UPDATE 或 DELETE 操作标记为删除。该列表的长度就是清理延迟。The InnoDB transaction system maintains a list of transactions that have index records delete-marked by UPDATE or DELETE operations. The length of the list is the purge lag.

清理延迟的计算公式如下：The purge lag delay is calculated by the following formula:

(purge_lag/innodb_max_purge_lag - 0.9995) * 10000

延迟在清理批次开始时计算。The delay is calculated at the beginning of a purge batch.

对于有问题的工作负载，一个典型的 innodb_max_purge_lag 设置可能为 1000000（100 万），假设事务较小，仅 100 字节大小，并且允许存在 100MB 未清理的表行。A typical innodb_max_purge_lag setting for a problematic workload might be 1000000 (1 million), assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged table rows.

清除延迟显示为 TRANSACTIONS 部分 History list length 输出中的 SHOW ENGINE INNODB STATUS 值。The purge lag is presented as the History list length value in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS output.

mysql> SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
History list length 20

History list length 通常是一个较低的值，通常少于几千，但高写入负载或长时间运行的事务可能会导致其增加，即使是只读事务也是如此。长时间运行的事务可能导致 History list length 增加的原因是，在像 REPEATABLE READ 这样的一致读事务隔离级别下，事务必须返回与为该事务创建读视图时相同的结果。因此，InnoDB 多版本并发控制（MVCC）系统必须在撤销日志中保留数据的副本，直到所有依赖该数据的事务完成。以下是可能导致 History list length 增加的长时间运行事务示例：The History list length is typically a low value, usually less than a few thousand, but a write-heavy workload or long running transactions can cause it to increase, even for transactions that are read only. The reason that a long running transaction can cause the History list length to increase is that under a consistent read transaction isolation level such as REPEATABLE READ, a transaction must return the same result as when the read view for that transaction was created. Consequently, the InnoDB multi-version concurrency control (MVCC) system must keep a copy of the data in the undo log until all transactions that depend on that data have completed. The following are examples of long running transactions that could cause the History list length to increase:

为了防止在清除延迟变得极大的极端情况下出现过度延迟，你可以通过设置 innodb_max_purge_lag_delay 变量来限制延迟。innodb_max_purge_lag_delay 变量指定当超过 innodb_max_purge_lag 阈值时施加的延迟的最大微秒数。指定的 innodb_max_purge_lag_delay 值是由 innodb_max_purge_lag 公式计算出的延迟周期的上限。To prevent excessive delays in extreme situations where the purge lag becomes huge, you can limit the delay by setting the innodb_max_purge_lag_delay variable. The innodb_max_purge_lag_delay variable specifies the maximum delay in microseconds for the delay imposed when the innodb_max_purge_lag threshold is exceeded. The specified innodb_max_purge_lag_delay value is an upper limit on the delay period calculated by the innodb_max_purge_lag formula.

清理和撤销表空间截断Purge and Undo Tablespace Truncation

清理系统也负责截断撤销表空间。您可以配置 innodb_purge_rseg_truncate_frequency 变量来控制清理系统查找要截断的撤销表空间的频率。有关更多信息，请参见 截断撤销表空间。The purge system is also responsible for truncating undo tablespaces. You can configure the innodb_purge_rseg_truncate_frequency variable to control the frequency with which the purge system looks for undo tablespaces to truncate. For more information, see Truncating Undo Tablespaces.