InnoDB 是一个多版本存储引擎。它保留已更改行的旧版本信息，以支持诸如并发性和回滚等事务功能。这些信息存储在称为回滚段的数据结构中的撤销表空间中。请参见 第 17.6.3.4 节，“撤销表空间”。InnoDB 使用回滚段中的信息来执行事务回滚中所需的撤销操作。它还使用这些信息来构建行的早期版本，以实现一致性读取。请参见 第 17.7.2.3 节，“一致的非锁定读取”。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 undo tablespaces in a data structure called a rollback segment. See Section 17.6.3.4, “Undo Tablespaces”. InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read. See Section 17.7.2.3, “Consistent Nonlocking Reads”.

在内部，InnoDB 向存储在数据库中的每一行添加三个字段：Internally, InnoDB adds three fields to each row stored in the database:

回滚段中的撤销日志分为插入撤销日志和更新撤销日志。插入撤销日志仅在事务回滚时需要，并且在事务提交后可以立即丢弃。更新撤销日志也用于一致性读，但只有在不存在事务使用InnoDB分配的快照，而在一致性读中可能需要更新撤销日志中的信息来构建数据库行的早期版本时，才可以丢弃。有关撤销日志的更多信息，请参见第 17.6.6 节，“撤销日志”。Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could require the information in the update undo log to build an earlier version of a database row. For additional information about undo logs, see Section 17.6.6, “Undo Logs”.

建议您定期提交事务，包括仅执行一致性读的事务。否则，InnoDB无法丢弃更新撤销日志中的数据，回滚段可能会过大，从而填满其所在的撤销表空间。有关管理撤销表空间的信息，请参见第 17.6.3.4 节，“撤销表空间”。It is recommend that you commit transactions regularly, including transactions that issue only consistent reads. Otherwise, InnoDB cannot discard data from the update undo logs, and the rollback segment may grow too big, filling up the undo tablespace in which it resides. For information about managing undo tablespaces, see Section 17.6.3.4, “Undo Tablespaces”.

回滚段中撤消日志记录的物理大小通常比相应的插入或更新行小。您可以使用此信息来计算回滚段所需的空间。The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space needed for your rollback segment.

在 InnoDB 多版本方案中，当您使用 SQL 语句删除一行时，行并不会立即从数据库中物理删除。InnoDB 仅在丢弃为删除操作写入的更新撤消日志记录时才会物理删除相应的行及其索引记录。这种删除操作称为清理（purge），速度相当快，通常与执行删除的 SQL 语句所需的时间在同一数量级。In the InnoDB multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. InnoDB only physically removes the corresponding row and its index records when it discards the update undo log record written for the deletion. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

如果您以大约相同的速度在表中批量插入和删除行，清理线程可能会开始滞后，表会因为所有的 “死” 行而变得越来越大，使所有操作受制于磁盘，速度非常慢。在这种情况下，应限制新行操作的速度，并通过调整 innodb_max_purge_lag 系统变量，为清理线程分配更多资源。更多信息请参见 第 17.8.9 节，“清理配置”。If you insert and delete rows in smallish batches at about the same rate in the table, the purge thread can start to lag behind and the table can grow bigger and bigger because of all the “dead” rows, making everything disk-bound and very slow. In such cases, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable. For more information, see Section 17.8.9, “Purge Configuration”.