InnoDB 多版本控制
发布于 7 个月前 作者 yuanliandu 194 次浏览 来自 服务端

原文

InnoDB is a multi-versioned 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 tablespace in a data structure called a rollback segment (after an analogous data structure in Oracle). 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.

innodb是一个多版本存储引擎。他可以保存已更改行的旧版本信息,以支持事务特性,如并发和回滚。这种信息以数据结构形式存储在表空间中,称之为回滚段。在orcacle中有类似的数据结构。InnoDb使用回滚段中的信息来执行事务中的撤销工作。它还使用这些信息构建行的早期版本,以便进行一致的读取。

Internally, InnoDB adds three fields to each row stored in the database. 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. Each row also contains 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.

mysql-1 (2).png

在内部,innodb为存储在数据库的每一行信息增加了三个字段。

6字节的 DB_TRX_ID,代表了最新插入或者更新行事务的标识符。

同时删除在内部被视为更新,但是行中某个特殊的位置被标识为已删除。

每一行还包含一个七字节的DB_ROLL_PTR,称之为滚指针。滚指针指向被写进回滚断中的一个撤销操作。如果更新该行,则在更新之前,撤销日志记录包含重新构建该行内容的必要信息。

6字节的DB_ROW_ID字段包含了一个行-id,row_id随着行的插入自动增长。如果innodb自动生成一个聚簇索引,则该索引包含row id值。除此之外,DB_ROW_ID不会出现在任何索引之中。

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 need the information in the update undo log to build an earlier version of a database row.

撤销日志在回滚段中被分为插入和更新撤销日志。插入撤销日志只在事务回滚中被需要,而且可以再事务提交的同时被丢弃。更新撤销日志也被用于一致性读,但是它们只能在Innodb为其事务分配的快照不存在之后才能被丢弃,因为在一致性读中需要更新撤销日志中的信息来构建早期数据库行的版本。

Commit your transactions regularly, including those 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 your tablespace.

定期提交事务,包括那些只发出一致性读的事务。否则,Innodb就不能丢弃更新撤销日志的数据,然后回滚段体积日益变大,填满表空间。

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.

回滚段中,撤销日志记录的体积大小通常小于相应的行插入或者更新。你可以使用这个信息计算回滚段需要的信息。

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删除语句的时序。

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 a case, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable. See Section 15.13, “InnoDB Startup Options and System Variables” for more information.

如果以表中相同的速率在小批次中进行行的插入、删除,由于所有死行,清理线程会开始落后于表的增大,以至于东西都绑在磁盘上,而且会非常慢。

在这种情况下,限制行的操作,并通过系统变量innodb_max_purge_lag为清理线程分配更多的资源。

Multi-Versioning and Secondary Indexes

InnoDB multiversion concurrency control (MVCC) treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary index records do not contain hidden system columns nor are they updated in-place.

innodb多版本并发管理对待二级索引的方式不同于聚簇索引。 聚簇索引中的记录可以就地更新,他们隐藏的系统列指向撤销日志条目,可以重新构建早起的记录版本。二级索引记录不像聚簇索引记录,包含系统隐藏列,也就不能就地更新。

When a secondary index column is updated, old secondary index records are delete-marked, new records are inserted, and delete-marked records are eventually purged. When a secondary index record is delete-marked or the secondary index page is updated by a newer transaction, InnoDB looks up the database record in the clustered index. In the clustered index, the record’s DB_TRX_ID is checked, and the correct version of the record is retrieved from the undo log if the record was modified after the reading transaction was initiated.

当更新二级索引列时,旧的二级索引记录被标识为删除,新的记录被插入,标识删除的记录最终被清理。当一个二级索引记录被表示被删除时,或者一个二级索引页被新的事务更新时,innodb会在聚簇索引寻找数据库记录。在聚簇索引中,检查记录的DB_TRX_ID,如果在读取事务启动后修改了记录,则从撤消日志中取回记录的正确版本。

If a secondary index record is marked for deletion or the secondary index page is updated by a newer transaction, the covering index technique is not used. Instead of returning values from the index structure, InnoDB looks up the record in the clustered index.

如果二级索引记录被标记为删除,或者二级索引页被更新的事务更新,则不使用覆盖索引技术。InnoDB不从索引结构返回值,而是在聚集索引中查找记录。

However, if the index condition pushdown (ICP) optimization is enabled, and parts of the WHERE condition can be evaluated using only fields from the index, the MySQL server still pushes this part of the WHERE condition down to the storage engine where it is evaluated using the index. If no matching records are found, the clustered index lookup is avoided. If matching records are found, even among delete-marked records, InnoDB looks up the record in the clustered index.

但是,如果启用了索引条件下推(ICP)优化,并且可以只使用索引中的字段来评估部分where条件,那么mysql服务器仍然会将where条件的这部分下推到存储引擎,在那里使用索引来评估它。如果找不到匹配的记录,将避免聚集索引查找。如果找到匹配的记录,即使在删除标记的记录中,InnoDB也会在聚集索引中查找该记录。

回到顶部