InnoDB引擎是一个平衡了高可靠与高性能特性的存储引擎
在MySQL 8.0版本中,InnoDB是默认的存储引擎。除非你手动配置或修改了存储引擎,否则在使用 CREATE TABLE 形式的DDL语句创建表结构时,如果没有包含ENGINE语句,MySQL将会使用InnoDB作为存储引擎。

一、InnoDB的关键优势

  • DML操作遵循ACID模型,为了能保护用户数据,具有提交回滚特性的事务功能以及灾难恢复能力
  • 行级别的锁以及与Oracle风格一致地读,从而增加了多用户操作的并发性和性能
  • InnoDB表,基于主键对磁盘中的数据进行了重新组织,从而优化了查询
  • 为了维护数据完整性,InnoDB支持外键约束。使用了外键,插入、更新以及删除都会受到约束,从而保证了外键值在多表之间的一致性

InnoDB引擎的特性表

特性 是否支持
B-tree索引
聚集索引(Clustered indexes)
全文搜索索引(Full-text search indexes) 是(5.6及之后版本就开始支持)
哈希索引(Hash indexes)
T-tree索引(T-tree indexes)
索引缓存(Index caches)
支持事务(Transactions support)
数据缓存(Data caches)
压缩数据(Compressed data)
加密数据(Encrypted data)
外键支持(Foreign key support)
锁粒度 行锁(Row)
MVCC(Multiversion Concurrency Control、数据库多版本并发控制、无锁操作)
存储大小限制 64TB
备份 / 按时间点恢复(是在MySQL服务端实现的,而不是存储引擎中实现的)
复制支持(是在MySQL服务端实现的,而不是存储引擎中实现的)
地理空间数据类型支持(Geospatial data type support)
地理空间索引支持(Geospatial indexing support) 是(5.7版本后开始支持)
更新数据字典的统计信息
集群数据库支持(Cluster database support)

二、InnoDB表使用最佳实践

  • 使用最经常使用的查询列作为每张表的主键列,或者如果没有显示指定主键列,将会使用自动增长列作为主键列

  • 基于相同的ID值,通过连接操作来将不同表中的数据列信息整合到一起

    为了更快速地进行连接操作,为要连接的列添加外键约束,并且将不同表中的这些列定义成相同的数据类型。

    添加外键,能够保证被引用列能够被索引到,这就可以提高性能。当外键发生了删除或更新操作,这些变化会传播到受影响的相关表中,

  • 关闭自动提交

    每秒进行上百次提交操作,对性能会有一个很大的影响(这是由存储设备的写入速度决定的)

  • 将一批业务相关联的 DML 操作打包成一组,放在一个以START TRANSACTION语句开始且以COMMIT语句结束的事务中

    当你不想频繁提交时,就不需要生成大量的、运行数小时都不能进行提交的INSERTUPDATEDELETE类型的批处理语句

  • 不要使用锁表语句(LOCK TABLES语句)

    因为InnoDB能够在不牺牲可靠性或高性能的前提下,允许多个会话对相同的表同时进行读操作和写操作

    要对某些行进行排他性地写操作,使用 SELECT ...FOR UPDATE语法 来只锁定需要修改的那些行。

  • 开启 innodb_file_per_table 变量 或者使用通用的表空间,将表数据及其索引放在独立的文件中,而不是放在系统的表空间中

    默认情况下,innodb_file_per_table 是开启的

  • 评估您的数据和访问模式是否能从 InnoDB 表得到好处 或者 使用页面压缩特性

    压缩InnoDB表,不会影响读写性能

  • 运行MySQL服务时,使用 --sql_mode=NO_ENGINE_SUBSTITUTION选项,来防止在创建表时带有我们不希望使用的数据库引擎的语句

三、验证InnoDB是不是默认的存储引擎

查看数据库支持的所有引擎

mysql> SHOW ENGINES;
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;

image-20220815215129298

说明:在 `Support` 列中可以看到 InnoDB是默认的引擎。

四、使用 InnoDB 进行测试和基准测试

如果InnoDB不是默认的存储引擎,可以以下面两种重置默认存储引擎的方式来重启MySQL服务器,然后判断数据库和应用程序能够正确地进行工作

  • 在命令行指定 --default-storage-engine=InnoDB 选项
  • 在配置文件中的[mysqld]栏目下,指定 default-storage-engine=innodb配置

由于改变默认的存储引擎,只会影响新创建的表,运行应用程序的按照与设置步骤来确认一切都得到了正确安装,然后测试应用程序特性,使得数据加载、编辑以及查询特性可以工作。如果某张表依赖于其他存储引擎中的某个特性,你就会得到一个错误。 在这种情况下,在创建表的语句中定义ENGINE=other_engine_name语句来避免这个错误。

如果你不知道选择哪个存储引擎,并且你希望在使用了InnoDB来创建表结构时,预览指定表是怎样工作的,请使用 ALTER TABLE table_name ENGINE=InnoDB;