15.1 Introduction to InnoDB

15.1 Introduction to InnoDB

15.1.1 Benefits of Using InnoDB Tables

15.1.2 Best Practices for InnoDB Tables

15.1.3 Verifying that InnoDB is the Default Storage Engine

15.1.4 Testing and Benchmarking with InnoDB

InnoDB 是一个通用的存储引擎，平衡了高可靠性和高性能。在 MySQL 8.0 中，InnoDB 是默认的 MySQL 存储引擎。除非你配置了不同的默认存储引擎，否则在没有 engine = xxx 子句的情况下使用CREATE TABLE 语句将创建一个 InnoDB 存储引擎的表。

Key Advantages of InnoDB

InnoDB 的主要优势包括：

DML 操作遵循 ACID 模型，具有提交、回滚和崩溃恢复功能的事务，以保护用户数据。

Row-level locking 行级锁定和一致性读取提高了多用户并发性和性能。

InnoDB 表将数据放在磁盘上，根据主键优化查询。每个 InnoDB 表都有一个主键索引，称为聚集索引，它组织数据以最小化主键查找的 I/O。

为了维护数据的完整性，InnoDB 支持外键约束。对外键的插入、更新和删除进行检查，以确保它们不会导致不同表之间的不一致。

15.1.1 Benefits of Using InnoDB Tables

如果你的服务器因为硬件或软件问题而崩溃，不管当时数据库中发生了什么，在重新启动数据库之后，你不需要做任何特殊的事情。InnoDB 崩溃恢复自动完成在崩溃之前提交的任何更改，并删除正在进行但未提交的任何更改。

InnoDB 存储引擎维护自己的缓冲池，在访问数据时将表和索引数据缓存到主内存中。在专用数据库服务器上，多达 80% 的物理内存通常分配给 InnoDB 缓冲池。

如果你将相关数据分割成不同的表，你可以设置外键来加强引用的完整性。更新或删除数据将自动更新或删除其他表中的相关连的数据。尝试将数据插入到辅助表中，而主表中没有相应的数据，那么坏数据将自动被踢出。

如果数据在磁盘或内存中被损坏，校验和机制会在使用前提醒你注意伪造的数据。

当你为每个表设计适当的主键列时，涉及这些列的操作将自动优化。在 WHERE 子句、ORDER BY 子句、GROUP BY 子句和 JOIN 操作中快速引用主键列。

插入、更新和删除通过一种称为变更缓冲（change buffer）的自动机制进行优化。InnoDB 不仅允许对同一表的并发读写访问，还缓存已更改的数据以简化磁盘 I/O。

性能优势并不仅限于具有长时间查询的大型表。当从一个表中反复访问相同的行时，一个称为 Adaptive Hash Index 的特性将接管这些查找，使它们更快，就像它们来自散列表一样。

可以压缩表和相关索引。

可以创建和删除索引，对性能和可用性的影响要小得多。

截断每个表的文件表空间非常快，可以释放磁盘空间供操作系统重用，而不是释放只有 InnoDB 才能重用的系统表空间中的空间。

表数据的存储布局对 BLOB 和长文本字段更有效，采用动态行格式。

可以通过查询 INFORMATION_SCHEMA 表来监视存储引擎的内部工作。

可以通过查询性能模式表来监视存储引擎的性能细节。

可以自由地将 InnoDB 表与其他 MySQL 存储引擎中的表混合在一起，即使是在同一个语句中。例如，可以使用连接操作将 InnoDB 和内存表中的数据合并到一个查询中。

InnoDB 设计用于处理大数据量时的 CPU 效率和最高性能。

InnoDB 表可以处理大量数据，即使在文件大小限制为 2GB 的操作系统上也是如此。

15.1.2 Best Practices for InnoDB Tables

使用经常查询的唯一列为每个表指定一个主键，如果没有明显的主键，则指定一个自动递增值（auto-increment）为主键。

根据来自这些表的相同 ID 值从多个表中提取数据时使用 join。为了快速连接性能，在连接列上定义外键，并在每个表中声明那些具有相同数据类型的列。添加外键可以确保引用的列被索引，这可以提高性能（这块很多人就开始犯糊涂了，说外键会降低性能为什么反而会增加性能呢？这是因为被定义为外键的列都是会定义为索引的，是索引增加了性能）。外键还将删除或更新传播到所有受影响的表中，并防止在父表中不存在相应的 id 时将数据插入子表中。但是目前在多数的架构设计中，已经去除了外键的设计，由业务去保证数据的完整性，外键只保留在最初的模式设计中，以便清楚整个架构中，表之间的关联关系。

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

将相关的 DML 操作分组到事务中，并将它们与 START 事务和 COMMIT 语句进行绑定。虽然你不希望太频繁地提交，但也不希望发出大量的插入、更新或删除语句，这些语句在没有提交的情况下运行数小时。

不使用锁表语句（LOCK TABLES）。InnoDB 可以同时处理多个会话，所有的读写操作都放在同一个表中，而不会牺牲可靠性或高性能。要获得对一组行的独占写访问，请使用 SELECT ... FOR UPDATE 以锁定你打算更新的行。

允许 innodb_file_per_table 选项将每个表的数据和索引放在单独的文件中，而不是放在单个大型系统表空间中。这种设置需要使用其他一些特性，如表压缩和快速截断。innodb_file_per_table 选项在 MySQL 5.6.6 时默认启用。

评估你的数据和访问模式是否受益于 CREATE TABLE 语句上的 InnoDB 表压缩特性（ROW_FORMAT = COMPRESSED）。你可以压缩 InnoDB 表而不牺牲读/写功能。

使用选项——sql_mode=NO_ENGINE_SUBSTITUTION 运行服务器，以防止在创建表的 engine = xxx 子句中指定的引擎出现问题时使用不同的存储引擎创建表。

15.1.3 Verifying that InnoDB is the Default Storage Engine

要验证 InnoDB 是默认的存储引擎，请使用 SHOW ENGINES 命令查看不同的 MySQL 存储引擎。或者查询 INFORMATION_SCHEMA ENGINES 表。

# SHOW ENGINES;

# SELECT * FROM INFORMATION_SCHEMA.ENGINES;

15.1.4 Testing and Benchmarking with InnoDB

如果 InnoDB 不是你的默认存储引擎，你可以通过在 my.cnf 的配置文件的 [mysqld] 部分中定义default-storage-engine=InnoDB 重新启动服务器来确定你的数据库服务器或应用程序是否使 InnoDB 正确地工作。

如果你没有对存储引擎做出深思熟虑的决定，并且你只想预览在 InnoDB 下创建的某些表是如何工作的，那么可以使用命令 ALTER TABLE table_name ENGINE=InnoDB;