对比MySQL不同版本下表结构和数据存储的差异

一、前言

在开始之前， 我们需要先了解一下 MySQL 的存储引擎有哪些。 MySQL常用的存储引擎有 InnoDB 和 MyISAM，而且两者都有各自的特点，适用于不同的场景。下面是对这两种存储引擎的简要对比：

• InnoDB存储引擎

  • 支持事务处理
  • 支持行级锁
  • 支持外键约束
  • 可以根据事务日志实现数据恢复

• MyISAM存储引擎

  • MyISAM 的读取性能要高于 InnoDB（无事务）
  • 支持全文索引
  • 不支持事务
  • 只支持表级锁
  • 不支持外键

通过对比 InnoDB 和 MyISAM 各自的特点，可以很明显的看到，两者特点是互补的。 这给用户很大的选择空间，可以根据需求自由选择。

从MySQL 5.5版开始，InnoDB是MySQL的默认存储引擎

二、MySQL不同版本间的差异

首先我们需要找到MySQL保存数据的文件夹，看看MySQL是如何保存数据的。可以使用 show variables like '%datadir%' 指令进行查询。

mysql> show variables like '%datadir%';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| datadir         | /var/lib/mysql/   |
+-----------------+-------------------+
1 row in set (0.00 sec)

可以看到数据都保存在/var/lib/mysql/目录下，这个目录下有很多文件夹，并且文件夹都是以数据库名称来命名的，数据库名称下又有很多以表名称命名的文件。结构如下所示：

[root@converts data]# tree 
.
├── converts
│   ├── Menu.ibd
│   └── User.ibd

1、MySQL 5.7+

1.1、InnoDB存储引擎

# 查看InnoDB存储引擎下converts数据库的文件结构
[root@converts ~]# ll /var/lib/mysql/converts
total 20
-rw-r----- 1 systemd-coredump input  1339 May  8 12:02 db.opt
-rw-r----- 1 systemd-coredump input  17407 May  8 12:02 Menu.frm
-rw-r----- 1 systemd-coredump input  26780 May  8 12:02 Menu.ibd

如上所示，随便查询了一个数据库进行查看，InnoDB存储引擎会生成三种形式的文件：.opt、.frm、.ibd，每种类型的文件都有着不同的作用，如下所示：

• opt：文件存储了表的结构定义，包括列定义、索引信息、约束条件等元数据。

• frm：InnoDB存储引擎特有的文件，它包含了表的数据和索引信息。

• ibd：主要用于存储数据库级别的选项设置，比如数据库的默认字符集和排序规则。

1.2、MyISAM存储引擎

# 查看MyISAM存储引擎下converts数据库的文件结构
[root@converts ~]# ll /var/lib/mysql/converts
total 20
-rw-r----- 1 systemd-coredump input  1339 May  8 12:42 db.opt
-rw-r----- 1 systemd-coredump input  12051 May  8 12:42 Menu.frm
-rw-r----- 1 systemd-coredump input  36864 May  8 12:42 Menu.MYD
-rw-r----- 1 systemd-coredump input  5356 May  8 12:42 Menu.MYI

如上所示，MyISAM存储引擎会生成种文件：.opt、.frm、.MYD、.MYI 。其中.opt、.frm和InnoDB的方式一致。.MYD、.MYI作用如下：

• MYD：这个文件扩展名是 “MY Data“的缩写，该文件存储了MySQL表的实际数据记录。换句话说，它是表的数据信息文件，包含了表中每一行的实际内容。

• MYI：这个文件扩展名是 “MY Index“的缩写，这个文件包含了表的索引信息。它存储了表数据文件中任何索引的数据树结构，用于加速数据的查询操作。索引使得数据库能够快速定位到特定的数据行，而无需全表扫描。

拓展：可以看到 InnoDB存储引擎的数据和索引在同一个文件中（.ibd）,而MyISAM数据和索引则分开存储（MYI、MYD）,从这里可以看出来InnoDB是聚集索引、MyISAM是非聚集索引。

2、MySQL 8+

在 MySQL 8+版本中， 官方删除了.frm文件，并增加序列化字典信息(Serialized Dictionary Information,SDI)的文件。具体可以看官网介绍，相应官网页面如下所示：

Removal of File-based Metadata Storage

2.1、InnoDB存储引擎

# 查看InnoDB存储引擎下converts数据库的文件结构
[root@converts ~]# ll /var/lib/mysql/converts
total 11
-rw-r----- 1 systemd-coredump input  36864 May  8 15:42 Menu.ibd

如上所示：MySQL 8.0+ 版本之后， InnoDB存储引擎只会生成一个.ibd文件，该文件存储着表的数据、索引。这也就意味着每个InnoDB表都会有一个独立的.ibd文件来存储其数据和索引，从而提高了空间利用率和管理灵活性。

2.2、MyISAM存储引擎

# 查看MyISAM存储引擎下converts数据库的文件结构
[root@converts ~]# ll /var/lib/mysql/converts
total 11
-rw-r----- 1 systemd-coredump input      2003 May  9 17:24 Menu_452.sdi
-rw-r----- 1 systemd-coredump input         0 May  9 17:24 Menu.MYD
-rw-r----- 1 systemd-coredump input      1024 May  9 17:24 Menu.MYI

如上所示：MySQL 8.0+ 版本之后，MyISAM存储引擎会生成种文件：.sdi、.MYD、.MYI，其中 .MYD、和.MYI 和5.7+的作用一样。那么.sdi有什么作用呢？官方也给出了相应的解释~~

2.3、序列化字典信息(Serialized Dictionary Information,SDI)

在 MySQL 8.0+ 版本之后，官方移除了 .frm 文件，那么去了哪里呢？就放在了sdi文件中。MySQL将.frm文件信息以及更多信息移动到叫做序列化字典信息(Serialized Dictionary Information,SDI)中，SDI在InnoDB下是被写在了ibd文件内部。为了从ibd文件中提取SDI信息，MySQL提供了一个应用程序ibd2sdi。(这个工具MySQL8+默认已经安装了。) 具体使用方法，请看官方文档：

ibd2sdi — InnoDB Tablespace SDI Extraction Utility