Innodb简介
InnoDB是一个兼顾高可靠性和高性能的通用存储引擎。MySQL8.0中，InnoDB是默认的MySQL存储引擎。除非配置了不同的默认存储引擎，不带ENGINE子句的CREATE TABLE语句都会创建一个InnoDB 表。
InnoDB 的主要优势
- DML操作遵循ACID模型，事务具有提交、回滚和故障恢复功能以保护用户数据
- 行级锁和Oracle风格的一致性读取提高了多用户并发能力和性能
- InnoDB按能支持最优主键查询的方式在磁盘上组织用户数据，每个InnoDB表都有一个称为聚簇索引的主键索引，它以最小化主键查找IO成本的方式组织数据。
- 为了保持数据完整性，InnoDB支持FOREIGN KEY约束。对于外键，插入、更新和删除都通过校验来确保它们不会导致相关表之间的不一致
InnoDB 存储引擎特性
| 特征 | 是否支持 |
|---|---|
| B-tree indexes | yes |
| Backup/point-in-time recovery (server层实现，而非存储引擎) | yes |
| 数据库集群 | no |
| 聚簇索引 | yes |
| 数据压缩 | yes |
| 数据缓存 | yes |
| 数据加密(在服务器中通过加密函数实现；MySQL5.7及更高版本中支持静态数据加密) | yes |
| 外键支持 | yes |
| 全文搜索索引(MySQL 5.6 及更高版本中可用) | yes |
| 地理坐标数据支持 | yes |
| 地理坐标索引支持 | yes |
| 哈希索引(InnoDB 在内部利用哈希索引来实现其自适应哈希索引功能) | no |
| 索引缓存 | yes |
| 锁粒度 | 行锁 |
| MVCC | yes |
| 复制支持（在服务器中实现，而不是在存储引擎中) | yes |
| 存储限制 | 64TB |
| T树索引 | no |
| 事务 | yes |
| 更新数据字典的统计信息 | yes |
使用InnoDB表的好处
InnoDB 表具有以下优点：
- 如果服务器由于硬件或软件问题意外退出，无论当时数据库中发生了什么，都不需要在重新启动数据库后执行任何特殊操作。InnoDB崩溃恢复后会自动完成崩溃之前提交的更改，并撤消正在进行但未提交的更改，允许用户重新启动并从中断的地方继续
- InnoDB存储引擎维护自己的缓冲池，访问数据时在主内存中缓存表和索引数据。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息并加快处理速度。在专用数据库服务器上，通常会将高达 80%的物理内存分配给缓冲池
- 如果将相关数据拆分到不同的表中，则可以设置外键来强制执行参照完整性
- 如果数据在磁盘或内存中损坏，校验和机制会在使用伪造数据之前提醒用户。innodb_checksum_algorithm变量定义了InnoDB使用的校验和算法。
- 为数据库中每个表设计一个适当的主键列时，在WHERE子句、ORDER BY子句、GROUP BY子句和连接操作中使用主键列的操作会自动优化，执行非常快
- 插入、更新和删除通过称为ChangeBuffer自动机制进行优化。 InnoDB不仅允许对同一个表进行并发读写访问，通过还缓存变更的数据以减少磁盘I/O
- 当从表中反复访问相同的行时，自适应哈希索引会接管这些查找，使这些查找更快，就像它们来自哈希表一样
- 可以压缩表和相关索引
- 可以加密数据
- 创建和删除索引并执行其他DDL操作，对性能和可用性的影响要小得多
- Truncate一个file-per-table表空间非常快，并且能释放磁盘空间，这些空间不仅能被InnoDB重新使用，而且能被操作系统重用
- 使用DYNAMIC行格式，表数据的存储布局对于BLOB和长文本字段更高效。
- 可以通过查询 INFORMATION_SCHEMA 表来监视存储引擎的内部工作
- 可以通过查询 Performance Schema 表来监控存储引擎的性能细节
- 可以将InnoDB表与来自其他MySQL存储引擎的表混合使用，即使在同一条语句中也是如此。 例如可以使用连接操作在单个查询中组合来自InnoDB和MEMORY表的数据。
- InnoDB 专为处理大数据量时的 CPU 效率和最大性能而设计。
- InnoDB 表可以处理大量数据，即使在文件大小限制为 2GB 的操作系统上也是如此
InnoDB最佳实践
- 把最常查询的一个或多个列为指定为表的主键，如果没有明显的主键，则指定一个自动增量值。
- 在基于这些表的相同ID值从多个表中提取数据的任何地方使用连接。 为了提升连接性能，在连接列上定义外键，并在每个表中那些列声明相同的数据类型。 添加外键可确保为引用的列建立索引，从而提高性能。 外键还会将删除和更新传播到所有受影响的表，并在父表中不存在相应ID的情况下阻止在子表中插入数据。
- 关闭autocommit，每秒提交数百次会影响性能（受限于存储设备的写入速度）。
- 一组相关的DML事务操作通过用START TRANSACTION 和 COMMIT语句将它们括起来。不要发出运行数小时而不提交的大量 INSERT、UPDATE 或 DELETE 语句。
- 不要使用LOCK TABLES语句。 InnoDB可以处理多个会话，同时读取和写入同一个表而不会牺牲可靠性或高性能。 要获得对一组行的独占写访问权，请使用 SELECT ... FOR UPDATE 语法来锁定打算更新的行。
- 启用 innodb_file_per_table 变量或使用通用表空间将表的数据和索引放入单独的文件而不是系统表空间。 innodb_file_per_table 变量默认启用。
- 根据数据和访问模式是否受益于 InnoDB 表或页面压缩功能，可以在不牺牲读/写能力的情况下压缩 InnoDB 表。
- 使用 --sql_mode=NO_ENGINE_SUBSTITUTION 选项运行服务器，以防止使用不想使用的存储引擎创建表。
验证 InnoDB 是默认存储引擎
SHOW ENGINES 语句以查看可用的MySQL存储引擎。在SUPPORT列中查找 DEFAULT。 也可以查询 INFORMATION_SCHEMA.ENGINES 表是一样的结果
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
使用 InnoDB 进行测试和基准测试
如果 InnoDB 不是默认存储引擎，可以通过在命令行上定义 --default-storage-engine=InnoDB
或者在MySQL服务器选项文件的 [mysqld] 部分中定义：
[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 服务器并运行基准测试。
测试整个应用程序生命周期，从安装到大量使用，再到服务器重启。数据库繁忙时kill服务器进程模拟掉电，重启服务器时验证数据是否恢复成功。 测试任何复制配置，特别是如果在源服务器和副本上使用不同的 MySQL 版本和选项。
本文由 至简 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为:
2022/12/07 13:17