InnoDB
是一种兼顾了高可靠性和高性能的通用存储引擎。在MySQL 5.7中,InnoDB
是默认的MySQL存储引擎。除非您配置了其他默认存储引擎,否则CREATE TABLE
不带ENGINE=
子句的语句将创建一个InnoDB
表。
InnoDB的主要优势
- 它的DML操作遵循 ACID模型,事务具有提交、回滚和崩溃恢复功能,以保护用户数据。有关更多信息,请参见 第14.2节“ InnoDB和ACID模型”。
- 行级锁定和Oracle风格的一致读取提高了多用户并发性和性能。 See Section 14.7, “InnoDB Locking and Transaction Model” for more information.
- nnoDB tables根据主键排列磁盘上的数据以优化查询。每个InnoDB表都有一个主键索引,称为聚集索引,它组织数据以最小化主键查找的I/O。 See Section 14.6.2.1, “Clustered and Secondary Indexes” for more information.
- 为了保持数据的完整性,InnoDB支持外键约束。对于外键,将检查插入、更新和删除,以确保它们不会导致不同表之间的不一致。See Section 13.1.18.6, “FOREIGN KEY Constraints” for more information.
表14.1 InnoDB存储引擎功能
Feature | Support |
---|---|
B-tree indexes | Yes |
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) | Yes |
Cluster database support | No |
Clustered indexes | Yes |
Compressed data | Yes |
Data caches | Yes |
Encrypted data | Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.) |
Foreign key support | Yes |
Full-text search indexes | Yes (InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.) |
Geospatial data type support | Yes |
Geospatial indexing support | Yes (InnoDB support for geospatial indexing is available in MySQL 5.7 and later.) |
Hash indexes | No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.) |
Index caches | Yes |
Locking granularity | Row |
MVCC | Yes |
Replication support (Implemented in the server, rather than in the storage engine.) | Yes |
Storage limits | 64TB |
T-tree indexes | No |
Transactions | Yes |
Update statistics for data dictionary | Yes |
使用InnoDB表的好处
您可能会发现InnoDB表有以下好处:
- 如果您的服务器由于硬件或软件问题而崩溃,无论当时数据库中发生了什么,重新启动数据库后不需要执行任何特殊操作。InnoDB crash recovery会自动完成崩溃前提交的任何更改,并撤消正在进行但未提交的任何更改。重新开始,接着你离开的地方。
- InnoDB存储引擎维护自己的缓冲池,当数据被访问时,缓冲池将表和索引数据缓存在主内存中。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息并加快处理速度。在专用数据库服务器上,多达80%的物理内存通常分配给缓冲池。
- 如果将相关数据拆分为不同的表,则可以设置强制引用完整性的外键。更新或删除数据,其他表中的相关数据将自动更新或删除。尝试在主表中没有相应数据的情况下将数据插入到辅助表中,错误数据将自动被踢出。
- 如果数据在磁盘上或内存中损坏,校验和( checksum)机制会在使用之前提醒您使用虚假数据。
- 当为每个表设计具有适当主键列的数据库时,涉及这些列的操作将自动优化。在WHERE子句、ORDER BY子句、GROUP BY子句和join操作中引用主键列非常快。
- 插入、更新和删除由一种称为更改缓冲的自动机制优化。InnoDB不仅允许对同一个表进行并发读写访问,还缓存更改后的数据以优化磁盘I/O。
- 性能优势不仅限于具有长时间运行查询的大型表。当从一个表中一遍又一遍地访问相同的行时,一个称为自适应哈希索引的特性将接管这些查找,使其更快,就好像它们是从哈希表中出来的一样。
- 您可以压缩表和相关的索引。
- 您可以创建和删除对性能和可用性影响小得多的索引。
- 截断一个 file-per-table表空间非常快,可以释放磁盘空间供操作系统重用,而不是释放系统表空间中只有InnoDB可以重用的空间。
- 对于BLOB和长文本字段,使用动态( DYNAMIC)行格式,表数据的存储布局更有效。
- 您可以通过查询 INFORMATION_SCHEMA 表来监视存储引擎的内部工作。
- 您可以通过查询 Performance Schema表来监视存储引擎的性能详细信息。
- 即使在同一条语句中,也可以将InnoDB表与其他MySQL存储引擎中的表自由混合。例如,可以使用join操作将InnoDB和内存表中的数据合并到一个查询中。
- 在处理大数据量时,InNDB已经被设计用于CPU效率和最大性能。
- InnoDB表可以处理大量数据,即使在文件大小限制为2GB的操作系统上也是如此。
有关您可以在应用程序代码中应用的特定于InnoDB的优化技术,see Section 8.5, “Optimizing for InnoDB Tables”.
InnoDB表的最佳实践
本节介绍使用InnoDB
表时的最佳做法 。
- 使用最常查询的列为每个表指定主键,如果没有明显的主键,则为其指定自动增量值。
- 在根据来自多个表的相同ID值从多个表中提取数据时使用联接( joins)。为了快速连接性能,在连接列上定义外键,并在每个表中声明具有相同数据类型的列。添加外键可确保对引用列进行索引,从而提高性能。外键还将删除或更新传播到所有受影响的表,并防止在父表中不存在相应的id时在子表中插入数据。
- 关闭自动提交。每秒提交数百次会限制性能(受存储设备的写入速度限制)。
- 通过用START TRANSACTION和COMMIT语句将相关DML操作集分组到事务中。当您不想太频繁地提交,但也不想发出大量的INSERT、UPDATE或DELETE语句,这些语句在运行数小时后都没有提交。
- 不使用锁表(
LOCK TABLES
)语句。InnoDB可以同时处理多个会话,所有的读写都在同一个表上,而不会牺牲可靠性或高性能。要获得对一组行的独占写访问权,请使用SELECT ... FOR UPDATE
语法只锁定要更新的行。 - 启用
innodb_file_per_table
选项或使用常规表空间将表的数据和索引放入单独的文件,而不是系统表空间 system tablespace。默认情况下,innodb_file_per_table
选项处于启用状态。 - 评估您的数据和访问模式是否受益于InnoDB表或页面压缩特性。您可以压缩InnoDB表而不牺牲读/写功能。
- 如果CREATE TABLE的engine=子句中指定的引擎,请使用选项
--sql_mode=NO_ENGINE_SUBSTITUTION
运行服务器以防止使用其他存储引擎创建表。
验证InnoDB是默认存储引擎
发出SHOW ENGINES语句以查看可用的MySQL存储引擎。在InnoDB行中查找默认值。
mysql> SHOW ENGINES;
或者,查询INFORMATION_SCHEMA.ENGINES表。
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
使用InnoDB进行测试和基准测试
如果InnoDB
不是默认存储引擎,通过在命令行中定义--default storage engine=InnoDB,或者在MySQL server选项文件的[mysqld]部分中定义default storage engine=InnoDB,可以确定数据库服务器或应用程序是否与InnoDB一起正常工作。
由于更改默认存储引擎只会在创建新表时影响它们,请运行所有应用程序安装和安装步骤以确认所有内容都安装正确。然后练习所有应用程序功能,以确保所有数据加载、编辑和查询功能正常工作。如果表依赖于特定于另一个存储引擎的功能,您将收到一个错误;将ENGINE=other_ENGINE_name子句添加到 CREATE TABLE
语句以避免错误。
如果您没有对存储引擎做出深思熟虑的决定,并且您希望预览使用InnoDB创建某些表时如何工作,请发出命令ALTER TABLE table_name ENGINE=InnoDB
;对于每一张表。或者,要在不干扰原始表的情况下运行测试查询和其他语句,请复制:
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM other_engine_table;
要在实际工作负载下评估完整应用程序的性能,请安装最新的MySQL服务器并运行基准测试。
测试整个应用程序生命周期,从安装到大量使用,以及服务器重启。在数据库正忙时终止服务器进程以模拟电源故障,并在重新启动服务器时验证数据是否已成功恢复。
测试任何复制配置,特别是在主服务器和从服务器上使用不同的MySQL版本和选项时。
关闭InnoDB
Oracle建议将InnoDB作为典型数据库应用程序的首选存储引擎,从运行在本地系统上的单用户Wiki和博客,到性能受到限制的高端应用程序。在MySQL 5.7中,InnoDB是新表的默认存储引擎。
重要
无法禁用InnoDB。--skip innodb选项已弃用且没有任何效果,其使用将导致警告。它将在未来的MySQL版本中被删除。这也适用于它的同义词(--innodb=OFF、--disable innodb等等)。