MisakaTang's Blog

14.3 InnoDB的多版本

InnoDB是一个多版本存储引擎:它会保留关于行变动的旧版本信息用来支持事务特性,例如并发和回滚(rollback).这些信息被称为回滚段(rollback segment),存储在表空间中.InnoDB使用回滚段中的信息来执行事务回滚中所需要的撤销操作.还使用该信息构建早期版本以进行一致的行读取.

在内部,InnoDB为存储在数据库的每一行添加三个字段.一个6位的DB_TRX_ID字段用来标识插入或更新行的最后一个事务.此外,删除在内部被视为更新,行的一个特殊位用来标记是否删除.每一行还包含一个7位的DB_ROLL_PTR字段叫做滚动指针(roll pointer).滚动指针指向写入回滚段的撤消日志记录.如果行被更新了,那么撤消日志记录了用来重建被更新之前的行内容所必要的信息.一个6位的DB_ROW_ID字段包含了一个行ID在插入的时候递增.如果InnoDB自动生成聚簇索引,这个索引会包含行ID的值.否则,DB_ROW_ID列不会出现在任何索引中.

回滚段中的撤销日志分为插入和更新的撤销日志(undo logs).插入的回滚日志只在事务回滚的时候需要并且在事务提交之后可以立即丢弃.更新的撤销日志也被用于一致性读,但是只有在InnoDB没有分配快照的事务之后才能丢弃它们,在一致读取中可能需要更新撤消日志中的信息来构建数据库行的早期版本.

定期提交你的事务,包括那些只有一致性读的事务.否则,InnoDB将不能从更新的撤销日志里丢弃数据使得回滚段变得过大而填满表空间.

回滚段中撤消日志记录的物理大小通常小于相应的插入或更新行.你可以使用此信息计算回滚段所需的空间.

在InnoDB多版本方案中,当你使用sql语句删除行的时候不会马上从数据库中物理删除.InnoDB只在丢弃为删除而写的更新撤销日志的时候才会物理的删除相应的行.这个删除操作叫做清除(purge),它很快,通常采用和sql语句删除时相同的顺序.

如果你在表中以相同的速率执行小批量的插入和删除操作,清除线程可能开始落后,因为那些”死掉”的行,表会变得越来越大,使得一切都受磁盘限制变得很慢.在这种情况下,通过调整innodb_max_purge_lag系统变量来限制新行操作,并为清除线程分配更多资源.

多版本和二级索引

InnoDB的多版本并发控制(MVCC)看待二级索引和聚簇索引是不同的.聚簇索引中的记录更新是立即的,它们的隐藏系统列指向撤消日志条目，从中可以重建早期版本的记录.与聚簇索引记录不同,二级索引记录不包含隐藏的系统列,也不会立即更新.

当一个二级索引列被更新的时候,旧的二级索引记录会被标记删除,新的记录插入,最终清除被标记删除的记录.当一个二级索引记录被标记删除或二级索引页面被一个新事务更新的时候,InnoDB在聚合索引中查找数据库记录.在聚合索引中,记录的DB_TRX_ID会被检查并且记录的正确版本会从撤销日志中恢复如果在读事务开始之后记录被修改的话.

如果二级索引被标记为缺失或者二级索引页被一个新事务更新了,索引覆盖技术不会使用.代替从索引结构中返回值,InnoDB会从聚合索引中查找记录.

然而,如果启用了索引条件下推(ICP)优化并且where条件中的部分可以只用索引字段计算的话,MySQL服务器仍然会下推这一部分的where条件到使用索引计算的存储引擎中去.如果没有找到匹配的记录则聚合索引的查找会被避免.如果找到匹配的记录,即使是被标记删除的记录,InnoDB也会到聚合索引中查找记录.

14.2 InnoDB 和 ACID 模型

ACID模型是一系列的数据库设计准则,强调对业务数据和关键应用非常重要的可靠性方面.MySQL包括了像InnoDB存储引擎这样遵守ACID模型的组件所以数据不会因为例如软件崩溃和硬件故障这样的意外情况而遭到破坏或者丢失.当你依赖ACID相关特性的时候,你不需要重新发明一致性检查和崩溃恢复机制.如果你有其他附加的软件保障,高可用的硬件或者一个可以容忍小部分数据的丢失或者不一致的话,你可以调整MySQL设置用ACID可靠性来交换更好的性能和吞吐量.

下面讨论MySQL的特性尤其是InnoDB存储引擎是如何与ACID模型关联的.

• A: 原子性(atomicity).
• C: 一致性(consistency).
• I: 隔离性(isolation).
• D: 持久性(durability).

原子性

主要涉及InnoDB事务,相关MySQL特性:

• 自动提交(autocommit) 设置
• COMMIT子句
• ROLLBACK子句
• INFORMATION_SCHEMA表中的操作数据

一致性

主要涉及InnoDB内部保护数据,相关特性:

• InnoDB 双写缓存(doublewrite buffer)
• InnoDB 崩溃恢复

隔离性

主要涉及InnoDB事务,特别是每个事务的隔离级别,相关特性:

• 自动提交(autocommit) 设置
• SET ISOLATION LEVEL子句
• InnoDB锁的底层细节

持久性

主要涉及到和MySQL所在物理硬件相关配置,相关特性:

• InnoDB 双写缓存(doublewrite buffer)
• innodb_flush_log_at_trx_commit 配置选项
• sync_binlog 选项
• innodb_file_per_table 选项
• 存储设备的写入缓存,例如磁盘驱动,SSD,RAID
• 存储设备的电池备份
• 用来运行MySQL的操作系统
• 不间断电源服务(UPS)用来保护MySQL服务器
• 你的备份策略
• 对于分布式应用取决于MySQL服务器在数据中心扮演的角色和数据中心之间的网络连接.

14.1 InnoDB简介

InnoDB是一个平衡了高可用和高性能的通用存储引擎.在Mysql 5.7中已经作为默认的存储引擎使用.

关键优势:

• DML操作遵循ACID原则,具有事务的提交,回滚和崩溃恢复能力
• 行级锁和Oracle风格的一致性读保证了多用户并发和性能
• InnoDB在硬盘上基于主键来管理数据用来优化查询.每一个InnoDB表都有一个主键索引被称作聚簇索引来组织数据,在主键查找的时候可以最小化I/O.
• 为了保持数据完整性,InnoDB支持外键约束.在外键作用下,inserts,updates和deletes操作会被检查以确保在不同的表间不会出现数据不一致.

InnoDB存储引擎特性:

14.1.1 使用InnoDB表的优势

• 如果你的服务器因为硬件或软件原因崩溃了,不管那时候数据库发生了什么,在重启数据库之后你不需要做任何特殊操作.InnoDB的崩溃恢复机制会自动完成崩溃之前的一切变动并且撤销没有被提交的改动.
• InnoDB存储引擎维护自己的缓存池,在数据被访问的时候缓存表和索引数据到主存中.频繁使用的数据会直接从内存中读取.此缓存支持多种类型的信息并加快处理过程.在专用的数据库服务器上,通常将80%的物理内存分配给缓存池.
• 如果你把有关联的数据拆分到不同的表中,你可以设置外键(foreign keys)来确保引用完整性.更新或删除数据时,其他表中相关的数据会被自动地更新或删除.如果尝试在辅助表中插入数据而主表中没有相对应的数据在主表中,”坏”数据会被自动”踢”出.
• 如果数据在磁盘或内存中损坏,警告机制会在你使用问题数据之前发出警告.
• 当你在设计数据库的时候为每张表使用了合适的主键,涉及到这些列的操作会被自动地优化.在where子句,order by子句,group by子句和join操作里引用主键是非常快的.
• inserts,updates和deletes操作会被一个叫做change buffering的机制自动组织.InnoDB不仅允许对同一张表的并发读写,还会缓存更改的数据来优化磁盘I/O.
• 性能优势不仅限于对巨型表的长时间查询.当相同的行从一张表中被一遍遍访问的时候,一个叫做Adaptive Hash Index的特性会接手来使得这种查找变得更快,就好像是从一张Hash表中读出来的.
• 你可以压缩表和相关联的索引
• 你可以创建和删除索引,这对性能和可用性的影响小得多.
• 截断每个表的文件表空间非常快并且可以释放磁盘空间以供操作系统重用,而不是释放系统表空间中只有InnoDB才能重用的空间.
• 使用DYNAMIC行格式之后,对BLOB和长文本字段的存储布局效率更好了.
• 你可以通过查询INFORMATION_SCHEMA表监控存储引擎的内部工作情况.
• 你可以通过查询Performance Schema表监控存储引擎的性能细节.
• 你可以将InnoDB表与其他MySQL存储引擎中的表自由混合,甚至在同一个语句中.
• InnoDB为处理大量数据时的CPU效率和最强性能设计.
• InnoDB表可以处理大量数据,甚至在操作系统的文件大小限制为2GB的情况下.

14.1.2 InnoDB表最佳实践

• 使用查询频率最高的列或几列为每个表指定主键,没有明显主键就使用一个自增值
• 从多表中基于他们相同的ID值提取数据的时候使用joins.为了最快的join性能,在join的列上定义主键并且在每个表中为这些列声明相同的数据类型.添加外键确保被引用的列被索引来提高性能.外键也会传播删除或更新操作到所有受影响的表,并且防止在父表没有对应ID的情况下向子表插入数据.
• 关闭自动提交(autocommit).每秒提交上百次会影响性能(受限于你的存储设备的写速度).
• 把相关的DML操作组织成事务,用START TRANSACTION和COMMIT语句包围它们.虽然你不想提交得太频繁,但是你也不希望发出大量的INSERT,UPDATE或DELETE语句运行好几个小时而不提交.
• 不要使用LOCK TABLES语句.InnoDB可以在不牺牲可靠性和高性能的情况下处理多个会话对一张表的同时读和写.要获取对一些行的独占的写权限,使用SELECT ... FOR UPDATE语法来锁住你想要更新的行.
• 启用innodb_file_per_table选项或者使用通用表空间来放置表的数据和索引到单独的文件中来代替系统表空间(system tablespace).innodb_file_per_table选项默认启用.
• 评估你的数据和访问模式是否受益于InnoDB表或页面压缩功能,你可以在不牺牲读/写功能的情况下压缩InnoDB表.
• 使用--sql_mode=NO_ENGINE_SUBSTITUTION选项启动你的服务器来防止CREATE TABLE的ENGINE=子句存在问题的时候使用不同的存储引擎创建表.

14.1.3 确认InnoDB是默认的存储引擎

• mysql> SHOW ENGINES;
• mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;

14.1.5 关闭InnoDB

Oracle建议将InnoDB作为典型数据库应用程序的首选存储引擎,从运行在本地系统的单用户wiki和博客,到推到性能极限的高端应用程序.在MySQL 5.7中,InnoDB是新表的默认存储引擎.