innodb内部结构分析

Because InnoDB is a multi-versioned storage engine, it must keep information about old versions of rows in the tablespace. This information is stored in a data structure called a rollback segment (after an analogous data structure in Oracle).

----因为innodb提供了多版本支持,它必须>把老版本的行存储在称为回滚段的表空间中,这种结构与oracle的非常像

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 .

---在innodb内部,innodb对存储在数据库中的每行增加3个字段.6个字节的DB_TRX_ID 字段,代表了最后修改或插入行的事务号.删除操作在innodb内部被认为是更新操作,通过设置标记位为deleted来实现.每行也都包含有7个字节的DB_ROLL_PTR 字段,它指向存储在回滚段的撤销日志条目.如果某行被更新,innodb可以>通过存储在回滚段的信息,来恢复该行被更新前的值----通俗的说,就是提供行的前印象(所以说,事务的回滚是离不开回滚段支持的).还有一个6字节的DB_ROW_ID 列,它是随着数据的插入而单调递增的.如果innodb表没有主键,那么innodb会自动的用DB_ROW_ID 来作为主键(因为递增,所以能很好的保证唯一性,适合做主键);当然如果innodb表本身有主键,那么rowid将不会出现在任何索引中.

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.

----回滚段的两个作用:(1)让事务成功的回滚(2)提供一致读

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才能删除之.这种错误在oracle中,称之为snapshot too old,就是说你要的快照太旧了,我们已经找不到了(意即虽然事务已经被提交,撤销日志不用来提供回滚信息了,但是因为我们的空间不够用,我们把它删除了,你要的东西因为太旧了我们找不到了.^_^,那么,再来一 遍吧)----这也是oracel中非常经典的错误,可以通过增大回滚段来解决,避免回滚日志因为空间不够而被过早的重用.

You must remember to 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无法丢弃回滚日志------因为害怕你会回滚,害怕你需要读取数据的前印象.如果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 need for your rollback segment.

----通常回滚信息所占的空间比插入或更新的量要少.这可以用来估算回>滚段的大小.如果是插入,那么记录那个行的id号到回滚段就ok了,如果要删除,直接通过id号来定位行即可删除它,实现回滚.如果是一般的更新,那么直接记录对>应的行id和被更新的字段即可.但是如果是删除操作,innodb只需要存储一条删除日志到回滚段中,比如只包含rowid信息,回滚的时候,只需要把对应的rowid的标>记位deleted删除即可.