MySQL不同存储引擎的磁盘存储方式

 MySQL各存储引擎在磁盘上的存储结构差异显著，这直接影响着数据库的性能特性和适用场景。以下是主要存储引擎的磁盘存储机制分析：

一、InnoDB存储引擎

文件结构

1. 系统表空间(ibdata1)

    存储位置：`datadir/ibdata1`

    包含内容：

      数据字典(元数据信息)

      变更缓冲区(change buffer)

      双写缓冲区(doublewrite buffer)

      回滚段(undo logs)

2. 独立表空间(.ibd文件)

    启用条件：`innodb_file_per_table=ON`(默认)

    文件命名：`表名.ibd`

    包含内容：

      表数据和索引(B+树结构)

      插入缓冲区(insert buffer)

      提交的事务ID信息

3. 重做日志文件(ib_logfile)

    默认2个文件(ib_logfile0, ib_logfile1)

    循环写入，保证崩溃恢复

存储特点

页式存储：默认16KB页大小，可通过`innodb_page_size`配置

B+树组织：主键索引聚集存储，二级索引包含主键值

动态行格式：支持COMPACT(默认)、DYNAMIC(5.7+推荐)和COMPRESSED格式

二、MyISAM存储引擎

文件结构

1. 表定义文件(.frm)

    存储表结构定义

    MySQL 8.0+ 不再需要，元数据存入数据字典

2. 数据文件(.MYD)

    纯数据存储，按插入顺序排列

    动态格式会产生碎片

3. 索引文件(.MYI)

    B-tree结构索引

    包含键值和指向.MYD文件的指针

存储特点

静态表：固定长度列，填充空格，快速随机访问

动态表：可变长度列(VARCHAR,BLOB等)，有行碎片问题

压缩表：只读，通过`myisampack`创建

三、MEMORY存储引擎

文件结构

仅.frm文件：存储表结构定义

无磁盘数据文件：数据仅存于内存

可选的持久化：通过`CREATE TABLE ... LIKE`+`INSERT ... SELECT`实现

四、ARCHIVE存储引擎

文件结构

1. .frm文件：表结构

2. .ARZ文件：数据文件(高度压缩)

3. .ARM文件：元数据文件

存储特点

行级压缩：使用zlib压缩库

批量插入优化：数据先缓存再压缩写入

不支持更新：只允许INSERT和SELECT

五、CSV存储引擎

文件结构

1. .frm文件：表结构

2. .CSV文件：纯文本数据文件

3. .CSM文件：元数据(状态和行数)

存储特点

逗号分隔文本：可直接用文本编辑器查看

无索引支持：全表扫描操作

Excel兼容：方便数据交换

六、NDB(Cluster)存储引擎

文件结构

1. 数据节点文件：

    `ndb_*_fs/D*`：数据文件

    `ndb_*_fs/L*`：日志文件

2. 配置文件：

    `config.ini`：集群配置

存储特点

内存+磁盘混合：热数据在内存，冷数据持久化

分区存储：数据自动分片到不同节点

冗余复制：多副本存储保证高可用

七、通用存储特征对比

八、存储优化建议

1. InnoDB优化：

   innodb_file_per_table = ON       启用独立表空间

   innodb_page_size = 16K          匹配SSD块大小

   innodb_flush_method = O_DIRECT  减少双写开销

2. MyISAM维护：

   OPTIMIZE TABLE table_name;   定期整理碎片

   REPAIR TABLE table_name;     修复损坏表

3. 文件位置规划：

   将不同引擎表分配到不同磁盘

   innodb_data_home_dir = /ssd/innodb

   myisam_data_home_dir = /hdd/myisam

理解不同引擎的存储机制有助于：

 合理选择存储引擎

 优化磁盘I/O性能

 设计有效的备份策略

 解决存储相关的性能问题