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
语句结束的事务中当你不想频繁提交时,就不需要生成大量的、运行数小时都不能进行提交的
INSERT
、UPDATE
、DELETE
类型的批处理语句 -
不要使用锁表语句(
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;

四、使用 InnoDB 进行测试和基准测试
如果InnoDB不是默认的存储引擎,可以以下面两种重置默认存储引擎的方式来重启MySQL服务器,然后判断数据库和应用程序能够正确地进行工作
- 在命令行指定
--default-storage-engine=InnoDB
选项 - 在配置文件中的[mysqld]栏目下,指定
default-storage-engine=innodb
配置
由于改变默认的存储引擎,只会影响新创建的表,运行应用程序的按照与设置步骤来确认一切都得到了正确安装,然后测试应用程序特性,使得数据加载、编辑以及查询特性可以工作。如果某张表依赖于其他存储引擎中的某个特性,你就会得到一个错误。 在这种情况下,在创建表的语句中定义ENGINE=other_engine_name
语句来避免这个错误。
如果你不知道选择哪个存储引擎,并且你希望在使用了InnoDB来创建表结构时,预览指定表是怎样工作的,请使用 ALTER TABLE table_name ENGINE=InnoDB;