MySQL的引擎和底层文件存储_mysql存储引擎底层-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_64174887/article/details/146984131

1. engine是插件型架构

来自JavaGuide。

我们操作数据库的过程是:

1. JDBC/ODBC或shell命令对服务层操作

1.5 此处是通过MySQL协议，按给出的ip和port连接到服务层

2. 服务层查缓存、解析并优化CRUD语句，生成执行计划，调用存储引擎的接口来操作engine

3. 底层数据文件在不同engine下存储方式不同，不同存储引擎用不同形式操作底层文件

因此插件式代表着：引擎可以替换使用（但切换引擎会备份并复制数据文件）

我们可以用下边语句查看目前版本的MySQL支持的引擎

show engines

引擎还是很多的，这点更是体现了插件式：

最后提一嘴：引擎是表层面的，每一张表可以用不同的引擎，而不是数据库层面。在看下边内容时请记住这个前提！！

2. MyISAM和InnoDB对比

刚刚提到底层数据文件在不同engine下存储方式不同，我们来看下MyISAM和InnoDB存储方式的区别：

2.1 MyISAM：

MySQL5.5前的默认引擎，实力不必多说。

它的存储结构很是规范：

.frm：存储表结构，如列名、数据类型、索引等是个二进制文件

.MYD：存储 实际数据行（表的所有记录）

.MYI：存储 索引数据（包括主键和二级索引） B-Tree 结构

默认都存储在ProgramData文件夹下，比如：C:\ProgramData\MySQL\MySQL Server 5.7\data\数据库名\表名.{frm,MYD,MYI}

注意.frm在MySQL8.0后被遗弃，表结构装入mysql.ibd（数据字典）

为什么更换：

1. .frm是线程不安全的，8.0后的DDL利用mysql.ibd变成了原子性的

2. mysql.ibd支持redo log，安全性更高

2.2 InnoDB：

这位更是重量级，MySQL5.5后的默认引擎都是InnoDB（截至2025.4）

它的存储方式简洁很多：

.frm：存储：表结构，如列名、数据类型、索引等（是个二进制文件）

.idb：存储：索引+实际数据（相当于.MYD+.MYI）

而8.0后更是简洁，由于.frm被废除，表结构信息直接存储在对应表的.ibd中

也就是只有：

.idb：存储：表结构+索引+实际数据（相当于.MYD+.MYI+.frm）

可以用ibd2sdi 工具查看idb的内容

ibd2sdi /var/lib/mysql/数据库名/表名.ibd

整体文件结构大概是这样：

heritagetwo是数据库，每一个表都有对应的.ibd文件，存储：表结构+索引+数据

3. InnoDB补充知识：

3.1 另一种存储形式

上边提到InnoDB十分简洁，每一个表维护一个.ibd文件就行了，其实它还可以把所有表合起来只维护一个.ibd文件：

也就是 系统表空间模式 即 innodb_file_per_table=OFF时，所有表的ibd信息都合并在ibdata1.ibd这个文件中

3.2 优点

支持行级锁：

InnoDB支持行级锁+表级锁，而MyISAM只支持表级锁，因此并发时InnoDB性能会比MyISAM强很多（每次操作MyISAM都要锁整张表，其他线程全部阻塞）

支持事务：

支持事务，并实现了四种隔离级别：

读未提交
读已提交
可重复读
串行化

支持外键：

原本支持外键是好的，保证数据一致性，但我们不提倡使用数据库的外键，原因是：

1. 分布式存储场景下，外键不能跨机器，不能使用。

2. 每次增删改都要外键检查，性能低下。关联的表多时尤为明显

3. 难以维护，如果有需求要改字段，要考虑外键约束

替代方案：

1. 后端业务代码层面实现外键效果

2. SQL层面的触发器代替外键

CREATE TRIGGER check_relation
BEFORE INSERT ON child_table
FOR EACH ROW
BEGIN
  IF NOT EXISTS (SELECT 1 FROM parent_table WHERE id = NEW.parent_id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid parent ID';
  END IF;
END;

数据库崩溃后可恢复：

redo log实现崩溃前状态（有机会专门写一篇，这里不细说了）

数据存储更合理：

一方面：InnoDB将表结构+索引+数据存储到同一个.idb文件中，性能更好（索引和数据放在一起可以减少I/O次数）

另一方面：使用B+树为索引，叶子节点是相连的，支持范围查询

总结

MySQL的引擎是插件式的，可以以表为单位更换，由于存储形式不同，更换时要多一倍空间来备份数据。5.5前MyISAM是主要引擎，索引和数据分开存储，对并发的支持不够。5.5后InnoDB为王，以.ibd一站式存储为目标，不仅数据结构更优越，还有行级锁、事务、redo log等功能来支持并发，更适应当前的分布式时代。