MySQL底层存储系统发展之路-CSDN博客

MySQL底层存储系统发展之路

本文档旨在帮助新人快速理解 MySQL 存储结构的历史演变，特别是从 MySQL 5.7 到 MySQL 8.0 引入统一数据字典的重大变更。我们将重点对比两个版本之间的差异，并分析其优势。
在这里插入图片描述

1. MySQL 5.7 及之前

1.1 InnoDB引擎

在 MySQL 5.7 及更早版本中：
.frm 文件保存了表的元数据信息（例如字段定义、索引等）
.ibd 文件则包含实际的表数据和索引
这导致了一个问题：当 DDL 操作失败时，.frm 和 .ibd 文件可能会处于不一致状态。

-- 示例命令
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

执行上述语句会生成：

users.frm   -- 表结构定义文件（操作系统层面）
users.ibd   -- InnoDB 表空间文件（引擎内）

❌ 若在创建过程中发生错误，可能留下残缺或无效的 .frm 文件，需要人工干预修复。

1.2 MyISAM引擎

MyISAM 使用三种不同的物理文件来存储一张表：
.MYD（数据文件）
.MYI（索引文件）
.frm（表结构定义）
这种方式使得表结构依赖于操作系统级别的文件一致性保障，容易因意外中断造成损坏。

2. MySQL8.0及之后

自 MySQL 8.0 起，针对InnoDB存储引擎，官方移除了 .frm 文件，转而采用内置的事务型数据字典（Transaction Data Dictionary）。该机制基于 InnoDB 存储引擎实现，保证了所有元数据操作的原子性和持久性。这次改动主要新增了几个核心的系统表，包括但不限于：

可参考MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/system-schema.html

mysql.columns：存储所有列的信息
mysql.tables：存储所有表的基础信息
mysql.schemata：存储数据库 schema 元信息
…

2.1 InnoDB引擎

MySQL 8.0 中继续使用 InnoDB 作为默认存储引擎，但不再需要 .frm 文件：

表结构由 mysql.columns, mysql.tables 等系统表维护
实际数据仍保留在 .ibd 文件中（可配置共享或独立表空间）

// 以前的方式 - 多个独立文件
// users.frm  (文件系统文件)
// users.ibd  (InnoDB表空间文件)

// 现在的方式 - 统一存储引擎管理
// mysql.tables         (InnoDB表)
// mysql.columns        (InnoDB表)
// 用户数据表.ibd       (InnoDB表空间)

mysql8系统表结构：https://dev.mysql.com/doc/refman/8.0/en/system-schema.html

columns: Information about columns in tables.主要存储所有表的列（字段）详细信息，包含内容主要有
列名称 (COLUMN_NAME)
数据类型 (DATA_TYPE)
列的默认值
是否允许为空
字符集和排序规则
列在表中的位置序号

tables: Information about tables in databases.主要存储所有表的基本信息，包含内容主要有：
表名称 (TABLE_NAME)
数据库名称 (TABLE_SCHEMA)
表类型和存储引擎
表的字符集和排序规则
创建时间等元数据

根据官方要求，数据字典相关表是不允许被select或者show出来的，会报权限问题：
在这里插入图片描述

但我们可以通过下面SQL获取对应表相关的元数据：

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

-- 查询数据库下的某个表
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'test_db';

--通过下面SQL，我们可以
--SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ziyi_users';
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = 'ziyi_users';

在这里插入图片描述
可以看到上面的结果展示了表相关的元数据信息，比如：表名、表对应字段名、字段类型、字段是否为空等信息。

.frm删除改为数据字典之后：

--------------MySQL 5.7时代-------------
-- 执行 CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
-- 1. 在文件系统创建 users.frm (表结构定义)
-- 2. 在InnoDB内部创建表数据和索引
-- 3. 如果步骤2失败，users.frm文件仍然存在，造成不一致

-- 如果此时系统崩溃：
-- 需要手动检查和清理不一致的 .frm 文件


-------------MySQL 8.0时代-------------
-- 执行 CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
-- 1. 开启事务
-- 2. 在数据字典表中插入表结构信息
-- 3. 在InnoDB表空间中创建表数据和索引
-- 4. 提交事务，所有操作原子性完成

-- 如果此时系统崩溃：
-- 重启后通过事务日志自动恢复到一致状态
-- 不存在孤立的元数据文件

-- 创建表的操作现在是事务性的
START TRANSACTION;
INSERT INTO mysql.tables (...) VALUES (...); -- 写入表定义
CREATE TABLE your_table (...) ENGINE=InnoDB; -- 创建真实表
COMMIT;

如果中途出错，则整个事务回滚，不会有残留的元数据文件。

2.2 MyISAM引擎

虽然 MyISAM 引擎仍然支持，但由于其非事务特性，在现代应用中已逐渐被 InnoDB 替代。它依旧使用旧有的三类文件模式：
.MYD（数据文件）
.MYI（索引文件）
.frm（表结构文件）

⚠️ 注意：即使在 MySQL 8.0 中，MyISAM 也并未完全移除 .frm 支持，但这并不代表它是推荐使用的存储引擎。

特性	MySQL 5.7 及以前	MySQL 8.0 后
元数据存储方式	分散在多个 .frm 文件	集中存储在 InnoDB 表中
原子性支持	不支持	支持事务性 DDL
安全性	易受崩溃影响	崩溃后可通过事务日志恢复
性能表现	需频繁访问文件系统	缓冲池管理元数据提升效率
管理便利性	分布式管理复杂	统一管理简化运维

拓展

InnoDB vs MyISAM

在MySQL5.5版本之前，采用MyISAM作为默认数据存储引擎。MyISAM存储引擎的优势是性能极佳，同时提供了功能特性，比如全文索引、压缩、空间函数等。

但MyISAM的缺点也很明显，它不支持事务和行级锁，而且在异常情况下（（例如断电或系统崩溃时）无法安全恢复数据。
在MySQL5.5版本之后，MySQL引入了InnoDB作为默认数据存储引擎，取代了之前的默认存储引擎 MyISAM。相较于ISAM和MyISAM，InnoDB最大的特色就是支持了ACID兼容的事务功能。

	InnoDB	MyISAM
事务支持	支持 ACID 事务，具有完整的事务特性（原子性、一致性、隔离性、持久性）	不支持事务，无法进行回滚操作
锁机制	行级锁定，支持高并发读写操作	表级锁定，写操作时会锁定整个表
外键约束	支持外键约束，保证数据完整性	不支持外键约束
存储结构	数据和索引存储在一起，支持聚集索引	数据和索引分开存储，使用非聚集索引
应用场景	事务支持的业务系统、高并发读写操作，如：社交平台、金融系统、电商系统	读多写少，无需事务支持场景，如：日志系统、数据仓库、内容管理
优点	数据安全性高，支持崩溃恢复、支持高并发操作、支持MVCC	查询速度快，适合读多写少的场景、存储空间相对较小
缺点	存储空间相对较大、查询性能在某些简单场景下不如 MyISAM	不支持事务，数据安全性较低、表级锁定影响并发性能、崩溃后无法自动恢复数据