文章目录
InnoDB 是一个平衡了高可靠性和高性能的通用存储引擎,是 MySQL 8.0 的默认存储引擎。无需使用带有 ENGINE
子句的 CREATE TABLE
语句即可创建一个 InnoDB 表,除非配置了其他默认存储引擎。
主要优势:
- DML 操作遵循 ACID 模型,使用具有提交、回滚、崩溃恢复功能的事务来保护用户数据。见 15.2 InnoDB 和 ACID 模型 。
- 行级锁和 Oracle 风格的一致性读操作提高了多用户并发性和性能。见 15.7 InnoDB 锁和事务模型 。
- InnoDB 表在磁盘上排列数据来优化基于主键的查询。每个 InnoDB 表都有一个主键索引,称为 聚簇索引(或聚集索引,Clustered Index) ,用于组织数据以最小化主键查找 I/O 。见 15.6.2.1 聚簇和次级索引 。
- 为了保持数据的完整性,InnoDB 支持
FOREIGN KEY
外键约束。使用外键时,将检查插入、更新和删除,以确保它们不会导致关联表之间的不一致。见 13.1.20.5 FOREIGN KEY 约束 。
表15.1 InnoDB 存储引擎特性
特性 | 支持性 |
---|---|
B-tree 索引 | 是 |
备份/基于时间点的恢复(实现于服务器层,而不是存储引擎层。) | 是 |
支持数据库集群 | 否 |
聚簇索引 | 是 |
数据压缩 | 是 |
数据高速缓存 | 是 |
加密数据 | 是(在服务器上通过加密函数实现;MySQL 5.7 |
支持外键 | 是 |
全文(Full-text)检索索引 | 是(MySQL 5.6 以后支持 FULLTEXT 索引。) |
支持地理空间数据类型 | 是 |
支持地理空间索引 | 是(MySQL 5.7 以后支持地理空间索引。) |
哈希(散列)索引 | 否(InnoDB 内部使用 Hash 索引用于其“自适应哈希索引”特性) |
索引高速缓存 | 是 |
锁粒度 | 行级 |
MVCC | 是 |
支持复制(实现于服务器层,而不是存储引擎层。) | 是 |
存储限制 | 64TB |
T-tree 索引 | 否 |
事务 | 是 |
更新数据字典统计信息 | 是 |
为了将 InnoDB 的特性与 MySQL 提供的其他存储引擎比较,请查阅 第 16 章 可选存储引擎 中的 存储引擎特性 表。
InnoDB 增强和新特性
有关 InnoDB 增强和新特性的信息,请参考:
- 1.3 MySQL 8.0 的新增功能 中列出的InnoDB 增强
- 版本发布声明
额外的 InnoDB 信息和资源
- 有关 InnoDB 相关术语和定义,请参阅 MySQL 词汇表。
- 有关 InnoDB 存储引擎的专用论坛,请参阅 MySQL Forums::InnoDB 。
- InnoDB 是在与 MySQL 相同的 GNU GPL 许可证版本 2(1991年6月)下发布的。有关 MySQL 许可的更多信息,请参阅 http://www.mysql.com/company/legal/licensing/ 。
MVCC (Multiversion Concurrency Control) 中文全称叫 多版本并发控制 ,是现代数据库(包括 MySQL 、Oracle 、 PostgreSQL 等)引擎实现中常用的处理读写冲突的手段, 目的在于提高数据库高并发场景下的吞吐性能,一定程度上实现了读写分离,避免并发冲突。
15.1.1 使用 InnoDB 表的好处
- 因硬件或软件导致的服务器崩溃后,不管此时发生了什么,重启后都不需要做其他任何事情。 InnoDB 崩溃恢复 会自动完成崩溃时间点以前提交的改变,并撤销任何仍在处理而未提交的改变,允许你重启并在你断开的位置继续。见 15.18.2 InnoDB 恢复 。
- InnoDB 存储引擎维护自己的缓冲池,在访问数据时将表和索引数据缓存在主内存中。常用数据直接从内存中处理。此缓存适用于许多类型的信息并加快处理速度。在专用的数据库服务器上,通常分配 最多 80% 的物理内存给缓冲池。见 15.5.1 缓冲池 。
- 如果将关联数据分割到不同的表中,可以建立外键来保障参照完整性。见 13.1.20.5 FOREIGN KEY 约束。
- 如果磁盘或内存中的数据损坏,CheckSum 校验和机制 会在您使用这些假数据之前向您发出警告。
- 当使用每个表的适当主键列设计数据库时,涉及这些列的操作将自动优化。在
WHERE
子句、ORDER BY
子句、GROUP BY
子句和join
操作中引用主键列非常快。见 15.6.2.1 聚簇索引和次级索引 。 - 插入、更新和删除都通过被称为 更改缓冲(Change Buffering) 的自动机制进行优化。InnoDB 不仅允许对同一个表进行并发读写访问,它还缓存了修改后的数据以简化磁盘 I/O 。见 15.5.2 更改缓冲区 。
- 性能优势并不局限于长时间运行查询的大型表。当从一个表一次又一次地访问相同的行时,一个称为 自适应哈希索引(Adaptive Hash Index) 的特性会使这些查找变得更快,就好像它们来自一个哈希(或散列)表一样。见 15.5.3 自适应哈希索引 。
- 支持压缩表和相关索引。见 15.9 InnoDB 表和页压缩 。
- 支持加密数据。见 15.13 InnoDB Data-at-Rest 加密 。
- 现在可以以更小的性能和可用性影响来创建、删除索引和其他DDL操作。见 15.12.1 在线 DDL 操作 。
- 截断
file-per-table
表空间非常快,而且可以释放磁盘空间供操作系统而不仅仅是 InnoDB 重用。 - 对于
BLOB
和长文本字段,使用DYNAMIC
动态行格式的表数据存储布局更加高效。见 15.10 InnoDB 行格式 。 - 可以通过查询
INFORMATION_SCHEMA
表来监视存储引擎的内部工作。见 15.15, “InnoDB INFORMATION_SCHEMA 表 。 - 可以通过查询 Performance Schema 表来监视存储引擎的性能细节。见 15.16 InnoDB 与 MySQL Performance Schema 的集成 。
- 可以自由地将 InnoDB 表与来自其他 MySQL 存储引擎的表混合使用,即使是在同一语句中。例如,可以在一个查询中使用
join
操作来组合来自 InnoDB 的数据和 MEMORY 表。 - InnoDB 旨在处理大量数据时提高 CPU 效率和最大性能。
- InnoDB 表可以处理大量数据,即使在文件大小限制为 2GB 的操作系统上也是如此。
注意
- CheckSum 校验和机制 :在 InnoDB 中,是一种当表空间中的页从磁盘读入 InnoDB 缓冲池时检测损坏的验证机制。该特性由 MySQL 5.5 中的
innodb_checksum
配置选项控制。innodb_checksum
在 MySQL 5.6.3 中被弃用,代之以innodb_checksum_algorithm
。file-per-table
: 是innodb_file_per_table
选项控制的设置的通称,该选项是影响 InnoDB 文件存储、特性可用性和 I/O 特性的重要配置选项。从 MySQL 5.6.7 开始,innodb_file_per_table
在默认情况下是启用的。启用innodb_file_per_table
选项后,可以在自己的.ibd
文件中创建表,而不是在system
表空间的共享ibdata
文件中创建表。当表数据存储在单独的.ibd
文件中时,您可以更灵活地选择 数据压缩 等特性所需的行格式。TRUNCATE
表操作也更快,操作系统可以使用回收的空间,而不是保留给 InnoDB 。dynamic row format
: InnoDB 行格式。因为过长的变长列值存储在保存行数据的页面之外,所以对于包含大型对象的行来说,这是非常有效的。由于大型字段通常不会被访问用来评估查询条件,所以它们不会经常被放入缓冲池,从而减少了 I/O 操作,提高了缓存内存的利用率。从 MySQL 5.7.9 开始,默认行格式由innodb_default_row_format
定义,默认值为DYNAMIC
。有关 InnoDB 动态行格式 的更多信息,请参阅 DYNAMIC 行格式 。
对于可以应用于 MySQL 服务器和应用程序代码的 InnoDB 特定调优技术,请参阅 8.5 优化 InnoDB 表 。
15.1.2 InnoDB 表的最佳实践
- 使用查询最频繁的一个列、一组列或自增值(如果没有明显的主键)为每个表指定一个主键。
- 在基于相同的 ID 值从多个表中提取数据的地方使用连接。为了加速连接性能,在连接列上定义外键,并在每个表中使用相同的数据类型声明这些列。添加外键可以确保引用的列被索引,这可以提高性能。外键还传播删除或更新到所有受影响的表,并防止在父表中没有相应的 ID 时在子表中插入数据。
- 关闭自动提交。每秒提交数百次会限制性能(受存储设备的写入速度限制)。
- 使用
START TRANSACTION
和COMMIT
语句包裹住一系列相关的DML操作组合成一个事务。虽然您不希望过于频繁地提交,但也不希望处理大量运行数小时而不提交的INSERT
、UPDATE
或DELETE
语句。 - 不使用
LOCK TABLES
语句。InnoDB 可以同时处理多个会话对同一个表的读写,而不会牺牲可靠性和高性能。要获得对一组行的独占写访问权,请使用SELECT … FOR UPDATE
语法,仅锁定要更新的行。 - 启用
innodb_file_per_table
选项或使用常规表空间将表的数据和索引放入单独的文件中,而不是系统表空间。innodb_file_per_table
选项默认是启用的。 - 评估您的数据和访问模式是否受益于 InnoDB 表或页面压缩特性。您可以压缩 InnoDB 表而不牺牲读/写功能。
- 使用选项
--sql_mode=NO_ENGINE_SUBSTITUTION
来运行您的服务器,以防止如果CREATE TABLE
语句的ENGINE=
子句指定的引擎存在问题时,表被创建成使用了您不想使用的存储引擎的形式。
15.1.3 验证 InnoDB 是默认的存储引擎
执行 SHOW ENGINES
语句来查看可用的 MySQL 存储引擎。在 InnoDB 行寻找 DEFAULT
字段。
mysql> SHOW ENGINES;
也可以查询 INFORMATION_SCHEMA.ENGINES
表。
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
15.1.4 使用 InnoDB 进行测试和基准测试
如果 InnoDB 不是默认的存储引擎,您可以通过在命令行重启服务器时使用 --default-storage-engine=InnoDB
或 在MySQL服务器选项文件中 [mysqld]
部分定义 default-storage-engine=innodb
来确定您的数据库服务器是否可以使用 InnoDB 正确工作。
由于更改默认存储引擎只会在创建新表时影响它们,因此请运行应用程序安装和设置步骤,以确认所有内容都已正确安装。然后测验应用程序特性,以确保所有的数据加载、编辑和查询特性都能工作。如果一个表依赖于特定于另一个存储引擎的特性,则会收到一个错误;添加 ENGINE=other_engine_name
子句到 CREATE TABLE
语句以避免错误。
如果您没有仔细考虑存储引擎,并且希望预览使用 InnoDB 创建某些表时的工作方式,那么可以为每个表运行 ALTER TABLE table_name ENGINE= InnoDB;
命令。或者,要运行测试查询和其他语句而不影响原始表,请复制:
CREATE TABLE ... ENGINE=InnoDB AS SELECT * FROM other_engine_table;
要在实际工作负载下评估完整应用程序的性能,请安装最新的 MySQL 服务器并运行基准测试。
测试完整的应用程序生命周期,从安装到大量使用,再到服务器重启。在数据库繁忙时终止服务器进程以模拟电源故障,并在重新启动服务器时验证数据是否成功恢复。
测试任何复制配置,特别是在主服务器和从服务器上使用不同的 MySQL 版本和选项时。