InnoDB简介

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等等）。