logo

InnoDB 引擎

Published on

InnoDB 简介

InnoDB 是 MySQL 的默认存储引擎，专为高可靠性和高性能而设计。自 MySQL 8.4 起，InnoDB 成为默认的存储引擎，除非另行配置，否则 CREATE TABLE 语句会默认创建 InnoDB 表。

InnoDB 的主要优势

  1. InnoDB 的 DML 操作遵循 ACID 模型，提供事务的提交、回滚和崩溃恢复功能，确保用户数据的安全性和一致性。

  2. 通过行级锁定和 Oracle 风格的一致性读取，InnoDB 提高了多用户环境下的并发性和性能。

  3. InnoDB 表在磁盘上以优化的方式排列数据，特别是针对主键的查询。每个 InnoDB 表都有一个称为聚簇索引的主键索引，组织数据以最小化主键查找的 I/O 操作。

  4. 为了维护数据完整性，InnoDB 支持外键约束。外键确保插入、更新和删除操作不会导致相关表之间的数据不一致。

InnoDB 表的规则

  1. 为每个表指定一个主键，使用最常查询的列或列组合，或者在没有明显主键的情况下使用自增值。

  2. 当从多个表中提取数据时，使用连接操作。为了快速的连接性能，在连接列上定义外键，并在每个表中声明相同的数据类型。添加外键可以确保引用的列被索引，从而提高性能。外键还可以传播删除和更新到所有受影响的表，并防止在子表中插入数据时，如果父表中不存在相应的 ID。

  3. 关闭自动提交。每秒提交数百次会限制性能（受限于存储设备的写入速度）。

  4. 通过使用 START TRANSACTION 和 COMMIT 语句将相关的 DML 操作分组到事务中。虽然不希望过于频繁地提交，但也不希望发出运行数小时而不提交的大批量 INSERT、UPDATE 或 DELETE 语句。

  5. 不要使用 LOCK TABLES 语句。InnoDB 可以处理多个会话同时读取和写入同一个表而不牺牲可靠性或高性能。要获得对一组行的独占写访问，请使用 SELECT ... FOR UPDATE 语法仅锁定您打算更新的行。

  6. 启用 innodb_file_per_table 变量或使用通用表空间将表的数据和索引放入单独的文件中，而不是系统表空间。innodb_file_per_table 变量默认启用。

  7. 评估您的数据和访问模式是否受益于 InnoDB 表或页面压缩功能。您可以在不牺牲读/写能力的情况下压缩 InnoDB 表。

  8. 使用 --sql_mode=NO_ENGINE_SUBSTITUTION 选项运行服务器，以防止使用您不想使用的存储引擎创建表。

InnoDB 和 ACID 模型

ACID 模型是一组数据库设计原则，强调对业务数据和关键任务应用程序至关重要的可靠性方面。MySQL 包含如 InnoDB 存储引擎等组件，严格遵循 ACID 模型，以确保数据不被损坏，结果不因软件崩溃和硬件故障等异常情况而失真。依赖于 ACID 兼容的功能，您无需重新发明一致性检查和崩溃恢复机制的轮子。在某些情况下，如果您有额外的软件保护、超可靠的硬件，或可以容忍少量数据丢失或不一致的应用程序，您可以调整 MySQL 设置，以在某些 ACID 可靠性和更高性能或吞吐量之间进行权衡。

以下部分讨论了 MySQL 特性，特别是 InnoDB 存储引擎如何与 ACID 模型的各个类别交互：

A: 原子性

原子性主要涉及 InnoDB 事务。相关的 MySQL 特性包括：

  • autocommit 设置。
  • COMMIT 语句。
  • ROLLBACK 语句。

C: 一致性

一致性主要涉及 InnoDB 内部处理以保护数据免受崩溃影响。相关的 MySQL 特性包括：

  • InnoDB 双写缓冲区。
  • InnoDB 崩溃恢复。

I: 隔离性

隔离性主要涉及 InnoDB 事务，特别是适用于每个事务的隔离级别。相关的 MySQL 特性包括：

  • autocommit 设置。
  • 事务隔离级别和 SET TRANSACTION 语句。
  • InnoDB 锁定的低级细节。可以在 INFORMATION_SCHEMA 表（“InnoDB INFORMATION_SCHEMA 事务和锁定信息”）和 Performance Schema 的 data_locks 和 data_lock_waits 表中查看。

D: 持久性

持久性涉及 MySQL 软件特性与特定硬件配置的交互。由于取决于 CPU、网络和存储设备的能力，可能性众多，因此提供具体指南最为复杂。（这些指南可能采取“购买新硬件”的形式。）相关的 MySQL 特性包括：

  • InnoDB 双写缓冲区。
  • innodb_flush_log_at_trx_commit 变量。
  • sync_binlog 变量。
  • innodb_file_per_table 变量。
  • 存储设备（如磁盘驱动器、SSD 或 RAID 阵列）中的写缓冲。
  • 存储设备中的电池支持缓存。
  • 用于运行 MySQL 的操作系统，特别是其对 fsync() 系统调用的支持。
  • 保护所有运行 MySQL 服务器和存储 MySQL 数据的计算机服务器和存储设备电力的不间断电源（UPS）。
  • 您的备份策略，例如备份的频率和类型，以及备份保留期。
  • 对于分布式或托管数据应用程序，MySQL 服务器硬件所在数据中心的特定特性，以及数据中心之间的网络连接。

InnoDB 多版本控制

InnoDB 是一个多版本存储引擎。它保留关于已更改行的旧版本的信息，以支持事务特性，如并发性和回滚。这些信息存储在称为回滚段的撤销表空间中。InnoDB 使用回滚段中的信息来执行事务回滚所需的撤销操作。它还使用这些信息来构建一致读取的早期版本的行。

在内部，InnoDB 为存储在数据库中的每一行添加三个字段：

  • 一个 6 字节的 DB_TRX_ID 字段，指示最后插入或更新该行的事务标识符。此外，删除在内部被视为一种更新，其中行中的一个特殊位被设置为标记为已删除。
  • 一个 7 字节的 DB_ROLL_PTR 字段，称为回滚指针。回滚指针指向写入回滚段的撤销日志记录。如果行被更新，撤销日志记录包含在更新前重建行内容所需的信息。
  • 一个 6 字节的 DB_ROW_ID 字段，包含一个随着新行插入而单调增加的行 ID。如果 InnoDB 自动生成聚簇索引，则索引包含行 ID 值。否则，DB_ROW_ID 列不会出现在任何索引中。

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

建议您定期提交事务，包括仅发出一致读取的事务。否则，InnoDB 无法从更新撤销日志中丢弃数据，回滚段可能会变得过大，填满其所在的撤销表空间。

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

在 InnoDB 多版本控制方案中，当您使用 SQL 语句删除行时，该行不会立即从数据库中物理删除。InnoDB 仅在丢弃为删除写入的更新撤销日志记录时，才物理删除相应的行及其索引记录。此删除操作称为清除，速度相当快，通常与执行删除的 SQL 语句所需的时间相同。

如果您以大约相同的速率在表中插入和删除小批量的行，清除线程可能会开始滞后，并且由于所有“死”行，表可能会变得越来越大，使一切都依赖于磁盘并且非常慢。在这种情况下，限制新行操作，并通过调整 innodb_max_purge_lag 系统变量为清除线程分配更多资源。

多版本控制和二级索引

InnoDB 多版本并发控制（MVCC）对二级索引的处理方式与聚簇索引不同。聚簇索引中的记录是就地更新的，其隐藏的系统列指向撤销日志条目，可以从中重建记录的早期版本。与聚簇索引记录不同，二级索引记录不包含隐藏的系统列，也不是就地更新的。

当更新二级索引列时，旧的二级索引记录被标记为删除，新的记录被插入，标记为删除的记录最终被清除。当二级索引记录被标记为删除或二级索引页被较新的事务更新时，InnoDB 在聚簇索引中查找数据库记录。在聚簇索引中，检查记录的 DB_TRX_ID，如果记录在读取事务启动后被修改，则从撤销日志中检索记录的正确版本。

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

然而，如果启用了索引条件下推（ICP）优化，并且可以仅使用索引中的字段评估 WHERE 条件的部分，MySQL 服务器仍然会将 WHERE 条件的这一部分下推到存储引擎，在那里使用索引进行评估。如果没有找到匹配的记录，则避免聚簇索引查找。如果找到匹配的记录，即使在标记为删除的记录中，InnoDB 也会在聚簇索引中查找记录。