[翻译] MySQL 之 InnoDB 特性和最佳实践

mysql

官方文档：15.1 Introduction to InnoDB

一、MySQL 之 InnoDB 总体介绍

InnoDB 是 MySQL 中一个平衡了 高可用 和 搞性能 的通用存储引擎。在 MySQL 8.0 中，除非通过 my.cnf 配置了默认的存储引擎，或者在 CREATE TABLE 语句中显式的通过 ENGINE=xxx 指定了别的存储引擎，InnoDB 将作为 MySQL 的默认存储引擎。

1. InnoDB 的明显优势

• 在 InnoDB 的事务中，DML (Data Manipulation Language) 满足事务的 ACID 原则，支持事务的提交 (commit) 和回滚 (rollback) 功能，同时基于灾难恢复 (crash-recovery) 的能力确保了用户数据的安全性。
• 更小粒度的 行锁 和 Oracle 风格的一致性读 (consistent read) 保证了多用户访问时的并发能力，提高了整体性能。
• InnoDB 在磁盘上的数据存储方式，对主键 (primary keys) 查询做了优化。在每一个包含主键索引，准确地说是聚簇索引 (clustered index)，的 InnoDB 表中，聚簇索引以一种 使得主键查找 IO 次数最少 的方式在磁盘上存放数据。
• InnoDB 支持外键 (foreign key) 限制，进一步保证了数据的完整性 (integrity)。外键限制，在增、删、改时会验证多张表中的数据关联，能够避免不一致的数据产生。

2. InnoDB 具体特性

特性	是否支持
B-Tree 索引	支持
server层的备份和事实恢复 (point-in-time recovery)	支持
数据库集群	不支持
聚簇索引(clustered index)	支持
压缩数据	支持
数据缓存	支持
server层的数据加密	支持
外键限制	支持
全文索引 (fulltext index)	5.6后支持
空间索引 (Geospatial index)	5.7后支持
Hash索引	不支持
索引缓存	支持
锁粒度	行
MVCC(Multi-Version Concurrency Controll)多版本并发控制	支持
server层的复制(replication)	支持
存储限制	64TB
T-Tree 索引	不支持
事务	支持
数据字典的更新统计	支持

二、表使用 InnoDB 的好处

表使用 InnoDB 存储引擎的好处有：

• 不管服务器由于软件、硬件问题宕机，无论当时 MySQL 上正在执行什么任务，你只需要重新启动 MySQL，InnoDB 的灾难恢复 (crash-recovery) 能力会自动帮你：把 宕机时已提交的变更恢复回来，把 未提交的变更舍弃掉，继续后续的工作，只需要重新启动就可以了。
• InnoDB 会维护一个自己的内存缓存 buffer pool，把访问过的数据和索引缓存到内存中，而经常访问的数据是直接从内存中获取的。这个内存 buffer pool 缓存缓存了很多数据，进一步提升 InnoDB 的处理速度。在专门做数据库的服务器上，甚至经常会将80%的内存分配给这个 buffer pool。
• 如果你将相关的数据分开存储到了多张表中，InnoDB 中的外键 (foreign key) 限制将使你能保证数据的完整性 (integrity)。
  • 更新或删除主表中的数据，子表的更新和删除是由外键限制自动完成的。
  • 当直接插入子表时，外键限制会自动检查主表的主键是否存在，直接剔除不完整的插入。
• 当磁盘、内存中的数据被破坏时，InnoDB 的 checksum 机制，会提醒你数据的不完整性。
• 当你在数据库设计时，如果给每张表都设置了一个合适的主键 (primary keys) ，那么涉及到这些主键 (primary keys) 的操作都会自动优化，对于使用了主键 (primary keys) 的 where order by group by join 都将非常快。
• change buffering 机制会自动优化插入、更新、删除操作。InnoDB不仅允许对同一个表进行并发读写访问，还缓存更改后的数据以优化磁盘I/O。
• 性能的提升不仅仅是支持大表的重查询 (long-running queries)，当频繁的从一个表获取相同数据时，自适应 hash 索引 (Adaptive Hash Index) 将会缓存这样的查询结果，使得查询非常快，就像使用了 hash 索引的表一样。
• 支持多表和索引的压缩
• 使得创建、销毁索引对性能和可用性上的影响变得很小
• 对于清空一个单文件表空间 (file-per-table tablespace) 来说，变得更快。使得磁盘空间释放出来给操作系统用，而不是仅仅从 MySQL 的 InnoDB 表空间中释放出来，给 InnoDB 表用。
• 使用了动态行结构 (dynamic row format)，这让 InnoDB 对 blob 和 text 字段的存储和访问上更高效。
• 可以通过查询 INFORMATION_SCHEMA 表，对 InnoDB 内部的工作进行监控。
• 可以通过查询 Performance Schema 表，对 InnoDB 的性能细节进行监控。
• 可以自由地在一个语句中混合 (mix) 使用 InnoDB 和其他存储引擎。例如：可以在一条语句中使用 join 合并 InnoDB 和 memory 存储引擎的数据。
• InnoDB 在处理大量数据的 CPU 运算上有很好的性能。
• 即使在一个文件最大 2GB 的操作系统上，InnoDB 的表可以存储大量数据。

三、InnoDB 表的最佳实践

InnoDB 表的最佳实践包含：

• 为每个存在大量查询的表指定一个主键 (primary keys)，或者选择一个自增类型的字段作为主键 (primary keys)。
• 基于每个表的 id 字段使用 join 操作。出于性能考虑，在 join 的字段上增加外键 (foreign key) 限制，并且将每个表中的这个字段设置为同样的数据类型。增加外键 (foreign key) 限制保证了不同表中的关联字段可以使用索引，同时使得删除和更新这些字段能影响到所有（有外键 (foreign key) 限制的）表，还能保证子表和主表的数据完整性。
• 关闭事务的自动提交 (autocommit)。每秒钟提交数百个事务将限制 InnoDB 的性能。
• 将大量的 DML (Data Manipulation Language) 语句，分组放到多个事务中，使用 start transaction 和 commit 包起来。虽然你不想太过频繁的提交事务，但你肯定也不想看到，运行了几个小时的 insert 语句，最后没有提交。
• 不要使用 lock table 语句。InnoDB 在不牺牲可用性和高性能的前提下，支持多个会话 (session) 同时对同一个表的并发读写。为了排他性 (exclusive) 地访问一些行的写入操作，可以使用 select ... for update 语句，单独只锁定你需要的行。
• 开启 innodb_file_per_table 选项或者使用普通表空间 (general tablespace)，使得每张表的数据和索引分开存储在不同的文件中，而不是使用系统表空间 (system tablespace)。 innodb_file_per_table 默认是开启的。
• 评估是否你的数据、访问方式能从 InnoDB 的表、页 (page) 的压缩功能中获益。InnoDB 在不牺牲读写能力的前提下，支持压缩功能。
• 如果在 create table 时使用 engine=xxx 有问题的话，可以在启动 MySQL 服务时，通过 --sql_mode=NO_ENGINE_SUBSTITUTION 选项禁止创建其他存储引擎的表。

四、确认 InnoDB 是否是默认的存储引擎

可以通过 show engines; 或者 SELECT * FROM INFORMATION_SCHEMA.ENGINES; 来查看 InnoDB 是否是默认的存储引擎。注意 InnoDB 那行的 default 字样。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

五、找个表测试一下 InnoDB

• 如果你没有把 InnoDB 作为默认的存储引擎，可以通过启动参数中增加 --default-storage-engine=InnoDB 或者在 my.cnf 的 [mysqld] 下增加 default-storage-engine=innodb 的方式，来检验下你的数据服务、应用是否正常。
• 如果你的应用有依赖其他存储引擎的某个特性，将得到一个 add the ENGINE=other_engine_name clause to the CREATE TABLE statement to avoid the error. 的错误。
• 如果你想看一下某个表在 InnoDB 存储引擎下的具体表，可以通过 ALTER TABLE table_name ENGINE=InnoDB; 的方式修改指定表使用的存储引擎。
• 如果你不想破坏原表、或者影响正在运行的应用，可以通过 CREATE TABLE table_xxx_innodb (...) ENGINE=InnoDB AS SELECT * FROM table_xxx; 的方式，制作一个备份表。
• 当你的数据服务是一个集群时，要特别注意 master 和 slaves 的版本差异。