InnoDB的多版本特性

1 问题背景

前面研究了MySQL事务以及隔离级别官方解释以及MySQL事务隔离级别实战演示，今天研究MySQL的InnoDB引擎的多版本（Multi-versioning）特性。

参考自：MySQL官方文档的InnoDB Multi-Versioning

2 前言

本文章仅从官方文档进行理论学习。适合对MySQL多版本特性方面是零基础的伙伴阅读。文章涉及较多英文，本意是提供最原始的文档供考证，如果看不懂英文可直接看中文。

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 15.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 15.7.2.3, “Consistent Nonlocking Reads”.

InnoDB是一个多版本存储引擎。它保存了已更改行的旧版本信息以支持事务功能（比如并发和回滚）。这些信息以称为 “回滚段” 的数据结构被保存在 “撤销表空间” 中。InnoDB使用回滚段中的信息来执行事务回滚所需的撤销操作。InnoDB也使用这些回滚段中的信息来构建每行的早期版本以实现一致的读取。

4 隐藏字段

原文

• A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.

• A 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.

• A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

为了实现多版本特性，InnoDB会给数据库表每一行存储3个隐藏字段，如下所示：

• 6个字节的DB_TRX_ID：表示插入或更新行的最后一个事务的事务标识。并且，一个删除操作被内部视为更新操作，其中设置了行的一个特殊二进制位以将其视为已删除。
• 7个字节的DB_ROLL_PTR：称为滚动指针。回滚指针指向存在回滚段中的撤销日志记录。如果行被更新，撤销日志记录包含着能重新构建当前行被更新前的所需信息。
• 6个字节的DB_ROW_ID:该字段包含一个行ID，随着插入新行而单调递增。如果InnoDB自动生成一个聚簇索引，则该索引包含行ID值。否则，该DB_ROW_ID列不会出现在任何索引中。

5 撤销日志

原文
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 15.6.6, “Undo Logs”.

撤销日志在回滚段中分为插入撤销日志以及更新撤销日志。插入撤销日志仅在事务回滚时需要，并且可以在事务提交后立即丢弃。更新撤销日志也用在一致性读取，但只有在没有事务存在并且InnoDB为其分配快照的情况下才能被丢弃。在一致性读取中可能需要更新撤销日志中的信息来构建较早版本的数据库行。

6 多版本的删除机制

原文
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多版本方案中，当您使用 SQL 语句删除某行时，不会立即从数据库中物理删除该行。InnoDB只有在丢弃为删除而写入的更新撤消日志记录时，才物理删除相应的行及其索引记录。这种删除操作称为清除，它非常快，通常与执行删除操作的 SQL 语句花费的时间顺序相同。

7 清除配置

原理
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 15.8.9, “Purge Configuration”.

如果在某个表以大致相同的速度小批量插入或删除行，清除线程可能会开始滞后并且表会由于所有的”死行”变得越来越大，从而导致所有内容受磁盘的限制以及变得非常慢。在这种情况下，通过限制新行操作或者通过调整系统变量innodb_max_purge_lag的值来分配更多的资源给清除线程。

8 多版本和二级索引

由于笔者目前暂未涉及到二级索引的场景，在此不做详细的研究，有兴趣的小伙伴可前往InnoDB的多版本特性官方文档研究。