MySQL存储引擎：InnoDB与MyISAM的区别

存储引擎是数据库的核心，DBMS（数据库管理系统）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。

一、查看存储引擎

▎查看当前库

show engines;

✦ 说明：使用上述命令可查看当前数据库支持的存储引擎类型，当前数据库总共支持9种！

Support 列的值表示某种引擎是否能使用

• YES：可以使用
• NO：不能使用
• DEFAULT：该引擎为当前默认的存储引擎

Ttansactions 表示是否支持事务机制

▎查看某张表

show table status from 数据库库名 where name='表名';

✦ 说明：如果没有特殊设置，默认引擎就是InnoDB，是从 MySQL5.5版本 (2010年) 开始代替 MyISAM 成为默认引擎 。主要原因是它支持事务

✦ 特别说明：在MySQL中，针对具体的要求，可以对每一个表使用不同的存储引擎。

▎修改某种表

一、已经存在的表，直接用命令修改表

ALTER TABLE user ENGINE=InnoDB;       -- 修改user表的引擎为InnoDB

注意：使用此种方式，数据量大会修改较慢，且修改时影响查询等性能

二、未存在的表，在创建表的时候指定

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(30) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='学生表'

二、InnoDB 存储引擎（推荐）

MySQL 5.1之后的默认存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎

▎优缺点

✦ 优点

1. 支持事务：实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
2. 聚集索引：主索引是聚簇索引，索引中保存了数据，避免直接读取磁盘，提升了查询性能。
3. 支持行锁：锁粒度默认为行级锁（也支持表锁），可以支持更高的并发。
4. 支持外键：通过外键保证数据的完整性和一致性，但是引入外键会使速度和性能下降。
5. 可以通过自动增长列，方法是auto_increment。
6. 支持在线热备份：配合一些热备工具可支持在线热备份；其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取
7. 存在着缓冲管理：通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

✧ 缺点

mysql 5.7前不支持全文检索，相比MyISAM，InnoDB写的处理效率要差一些，并且会占用更多的磁盘空间以保留数据和索引。

▎存储方式：2种

MySQL5.5默认是共享表空间 ，5.6中，默认是独立表空间。

第一种： 共享表空间  ibdata

所有的表数据和索引存放在同一个表空间中（ibdata），表空间可以由很多个文件组成，例 ibdata1、ibdata2、ibdata3、ibdata4。一个表可以跨多个文件存在。

其大小限制不再是文件大小的限制，而是其自身的限制。其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了（包括这个表的所有索引等其他相关数据）

第二种：独立表空间   表名.ibd

将每张表存放于独立的表空间。每张表都有一个 表名.frm 和 表名.ibd 文件。

• .frm文件：存放表结构
• .ibd文件：存放表数据和索引

如果是分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可在创建分区时指定每个分区的数据文件位置，将表的IO均匀分布在多个磁盘上。

注意：即使在独立表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和工作日志放在这个文件中，所以备份使用多表空间特性的表时直接复制.idb文件是不行的

▎如何查看存储方式？

使用sql命令：show variables like 'innodb_file_per_table';

✦ 说明：可以看到，默认是独立表空间存储方式， innodb_file_per_table选项开启时，表将被创建于表文件表空间中（独立表空间）。否则将被创建于系统表空间中（共享表空间）

 

▎两者数据结构区别

★ 独立表空间存储

 1、我们先来用可视化工具或者create创建表语句，来创建一张emp的表，如下：

我的本地连接中创建了2个数据库，分别是demo、demo2，在demo2数据库下创建一张emp表，字段没有要求，随便搞个id、name即可，主要是看存储空间

2、找到mysql安装目录，我的是 /usr/local/mysql/ ，再进入其下的data 目录

Data 目录用来存放数据库相关的数据信息，包括数据库信息，表信息等。

注意：如果data目录无法访问，需要先把该目录的everyone权限改成可读可写!

3、 进入demo2数据库的文件目录里，可以看到我们创建表之后，生成了一个 表名.ibd文件

由于我的MySQL8.0是8.0版本，8.0版本之前，会生成 .frm 和 .ibd 文件，表结构保存在.frm文件中；表数据和索引放在.ibd文件中。

8.0之后没有.frm文件了，Oracle 将frm文件的信息及更多信息移动到叫做序列化字典信息（Serialized Dictionary Information，SDI），SDI被写在 ibd文件，它是数据字典包含的信息的一个冗余副本。（即frm的文件信息合并到ibd文件中了！）

✦ 解析：可以看到，当前表被存放在独立的 表名.ibd 文件中（emp.ibd ）

★ 共享表空间存储

1、首先修改 innodb_file_per_table 的值为 OFF，即关闭独立表空间，换成共享表空间，如下：

-- 执行以下sql
set global innodb_file_per_table=ON;

 2. 此时创建一张student表，如下图：

3、student表创建成功之后，此时再去 demo2 目录下查看文件，发现并没有一个名为 student.ibd 的文件，说明此时的表已经存放于共享表空间了。

4、 与demo2同级目录，位于data 目录下

5、InnoDB采用将数据存储在表空间（tablespace）的设计。默认配置下会有一个默认为10M，名称为ibdata1的文件，是默认的表空间文件。如下可查看：

我使用的mysql8默认是12M，其中含义是默认表空间ibdata1大小12M，当用完，autoextent参数使其自动增加。

如果两个表空间在不同的磁盘上，负载可能被平分，提升整体的性能。表空间可设置多个，如示：

ibdata1:12M;ibdata2:12M:autoextend

 

▎独立表空间 vs 共享表空间

◈ 共享表空间

优点：

可将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。

缺点：

1. 一个文件中保存了多个表数据，并发操作时可能会产生 IO 瓶颈（不适合频繁写入的场景）
2. 所有的数据和索引存放到一个文件中，虽可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用

例如，当系统空间不够用时，希望通过删除一些无效数据来腾出来一些表空间，如果使用了共享表空间，即使无效数据删除了，表空间还是不会缩小！

◈ 独立表空间

优点：

1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。（因为每张表都有独立的数据表文件）
4. 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能。
5. 空间可以回收（除drop table操作处，表空不能自己回收）

• Drop table操作自动回收表空间，如果对于统计分析或是日志表，删除大量数据后可通过:alter table TableName engine=innodb;回缩不用的空间。
• 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

缺点：

1. 单表增加过大，如超过100个G。（单表增长过大，可通过分区来解决）
2. 相比较之下，使用独占表空间的效率以及性能会更高一点。

▎常见问题

问题一：MySQL8.0为什么取消掉了frm文件？

答：在8.0版本之后，原来的frm文件内容，合并到了ibd文件中，使得我们更容易的解析文件和表定义，并且提供了比frm原本文件内容更多详细信息。 比如（一段对DB对象的描述、列数组和相关信息、索引数组、分区信息、表空间信息）

问题二：MySQL8.0 frm文件没有了，如何提取表结构的信息？

答：为了从IBD文件中提取SDI信息，Oracle提供了一个应用程序 ibd2sdi。这个工具不需要下载，mysql8自带的有，只要你配好环境变量就能到处用。

☛ 提取表结构操作

以上面  存储方式为独立表空间的演示例子为例（数据库：demo2    数据库其下的表：emp）

1. 首先cd 进入到 ibd文件的存储目录，也就是mysql 安装目录的data数据目录下，进入名为demo2的数据文件夹   
2. 执行以下 ibd2sdi 命令 （ibd2sdi命令有很多，感兴趣的自行百度，这里就不一一演示了）

# ibd2sdi就会把***.ibd 里存储的表结构以json的格式输出到 ***.txt中。
ibd2sdi --dump-file=***.txt  ***.ibd

☛  完整演示如下：

如果执行第二步命令报以下错误，则需要先把该 ibd文件的权限，everone改成可读可写！
[ERROR] ibd2sdi: Unable to open file /usr/local/mysql/data/demo2/emp.ibd

 

a. 上述ibd2sdi语句执行完毕后，查看该目录：生成了一个txt文件！

 b. 用文本编辑模式打开该txt文件：

由于文件过长，就不分段截图展示了，该文件包含信息大致分为：一段对DB对象的描述、列数组和相关信息、索引数组、分区信息、表空间信息

三、MyISAM存储引擎

设计简单，数据以紧密格式存储。MyISAM拥有较高的插入、查询速度，但不支持事物。提供了大量的特性，包括压缩表、空间数据索引等。

▎优缺点

✦ 优点

1. 全文检索
2. 非聚集索引、查询速度快
3. 体积小，质量大：索引和数据是分开的，索引是有压缩的，内存使用率就对应提高了不少。

✧ 缺点

不支持事务、不支持外键、仅仅支持表锁；数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；。

▎存储方式

① 静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

② 动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

③ 压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

特点：不支持事务，故存储速度快，如只追求速度，读写操作允许有错误数据，选MyISAM

▎数据文件

MyISAM数据表在磁盘存储成3个文件，其文件名都和表名相同，扩展名分别是：

① .frm：存储数据表结构定义。（mysql8.0版本前）

② .MYD：存储表数据。

③ .MYI：存储表索引。

④ .sdi：MySQL 8.0 后代替.frm文件， .sdi文件是一个紧凑的json文件。名称由表名+表的id 组成

注意：其中数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

指定索引文件和数据文件的路径，需要在创建表的时候通过data directory和index directory语句指定。（文件路径需要是绝对路径并且具有访问的权限）

☛ 结构如下图所示：

✦ 分析： .sdi文件是一个紧凑的json文件。名称由表名+表的id 组成，该sdi文件内容，等同与我们上述用 ibd2sdi命令提取 表名.ibd 文件后的内容（emp.txt）

四、MyISAM 和 InnoBD 对比总结

	MyISAM	InnoDB
关注点	性能	事务
结构区别	每个MyISAM在磁盘上存储成三个文件：  ①  .frm/sdi ：表结构文件  ②  .MYD (MYData)：数据文件  ③  .MYI (MYIndex)：索引文件	表空间文件（区分共享表和独立表） ① .ibd 文件 ② .frm：表结构文件（mysql8.0已取消）
事务支持	不支持	支持
外键约束	不支持	支持
全文检索	支持	MySQL5.7版本之前不支持，之后支持
表空间	较小	较大，约为2倍
锁粒度	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	支持行锁、表锁
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
崩溃后的安全恢复	不支持	支持
增删改查	查询多的场景，选MyISAM	1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表   2.DELETE   FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
统计行数	MyISAM对表的总行数（row）存储在磁盘上，执行count()函数时，直接返回总数据 注意：当count语句包含where条件时，两种表的操作是一样的	全表扫描，会先把数据读出来，一行一行的累加，最后返回总数量。

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

MyISAM速度可能超快，占用存储空间也小，对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

☛ 额外扩展：使用Delete删除数据，重启数据库现象

delete 和 truncate 都能删除数据，用truncate会清空自增列值，重新从1开始，delete不会，删除完毕后，但如果重启数据库，delete也会存在清空自增列值的！

• InnoDB ：自增列会从1开始（存在内存当中，断电即失）
• MyISAM ：继续从上一个自增量开始（存在文件中的，不会丢失）

☁ 思考一：表t中 id为自增主键，当insert了17条记录后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

• MyISAM：是18，MyISAM表会把自增主键的最大id记录到数据文件中，重启也不会丢失；
• InnoDB：是15，InnoDB 表只是把自增主键的最大ID记录到内存中，重启数据库或对表进行option操作，都会导致最大ID丢失。

☁ 思考二：为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，当数据越大时，语句就越耗时。这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。