MySQL-存储引擎对比

MySQL 存储引擎对比

存储引擎（针对表，而非整个数据库）是 MySQL 处理数据的核心组件，负责数据的存储、读取、锁机制等核心逻辑。MySQL 支持多存储引擎，可通过 SHOW ENGINES; 查看所有引擎，核心差异如下：

一、核心存储引擎对比表

特性	InnoDB（默认）	MyISAM	Memory（Heap）
事务支持	✅（ACID 完整支持）	❌	❌
锁机制	行锁（高并发友好）	表锁（低并发）	表锁（低并发）
外键支持	✅	❌	❌
崩溃恢复	✅（redo/undo log）	❌（数据易损坏）	❌（重启数据丢失）
数据存储位置	磁盘（.ibd 独立表空间）	磁盘（.sdi/表结构.MYD数据/.MYI索引）	内存（无持久化）
索引类型	B + 树（聚簇索引）	B + 树（非聚簇索引）	哈希 / B + 树
全文索引	✅（MySQL 5.6+）	✅	❌
缓存机制	缓存数据 + 索引（缓冲池）	仅缓存索引	全内存缓存
适用场景	核心业务（电商 / 金融 / 交易）	只读 / 少写（日志 / 报表）	临时表 / 高并发临时查询
存储限制	受磁盘空间限制	最大 256TB	受内存大小限制

其他不常用存储引擎见后续简要说明

二、各引擎核心特点与适用场景

1. InnoDB（MySQL 默认）

• 核心特点：支持事务、行锁、外键，崩溃可恢复，聚簇索引提升查询效率。

• 适用场景：所有需要数据一致性、高并发的核心业务（如订单表、用户表、交易表）。

• 关键配置：innodb_file_per_table=ON（独立表空间）、innodb_buffer_pool_size（缓存池，设为内存 50%-70%）。

2. MyISAM

• 核心特点：不支持事务 / 外键，表锁导致并发差，但查询速度快，占用空间小。

• 适用场景：只读 / 少写的场景（如日志表、报表统计、归档历史数据）。

• 缺点：崩溃后数据易损坏，无恢复机制。

3. Memory

• 核心特点：数据全存内存，读写极快，重启后数据丢失（可配置持久化）。

• 适用场景：临时表、高并发临时查询（如秒杀活动的库存临时表）。

• 注意：仅支持固定长度字段（如 VARCHAR 转 CHAR），避免内存碎片。

4. CSV

• 核心特点：数据以 CSV 格式存储，可直接用 Excel 打开，无索引。

• 适用场景：数据导入导出（如批量导入第三方数据、导出报表）。

• 缺点：查询效率极低，仅适合小数据量。

5. Archive

• 核心特点：数据压缩存储，仅支持 INSERT/SELECT，不支持 UPDATE/DELETE。

• 适用场景：日志归档、历史数据存储（如几年前的订单日志）。

6. Blackhole（黑洞引擎）

• 核心特点：写入的数据全部丢弃，无存储，仅记录 Binlog。

• 适用场景：主从复制中做 “中转库”（如收集多主库 Binlog 同步到从库）。

7. Federated

• 核心特点：本地表映射远程 MySQL 数据库的表，无需同步数据，直接访问远程。

• 适用场景：跨库查询（如访问其他服务器的数据库表）。

• 缺点：性能差，依赖网络，稳定性低。

三、引擎选择原则

1. 优先选 InnoDB：90% 业务场景（需事务、并发、数据安全）。

2. 只读场景选 MyISAM：如报表、日志，追求查询速度。

3. 临时高速查询选 Memory：如秒杀、临时统计，注意数据持久化。

4. 特殊场景选专用引擎：CSV（导入导出）、Archive（归档）、Blackhole（同步）。

四、修改表存储引擎

-- 查看表的存储引擎
SHOW CREATE TABLE 表名;

-- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = InnoDB;

-- 创建表时指定引擎
CREATE TABLE 表名 (
  id INT PRIMARY KEY
) ENGINE = MyISAM DEFAULT CHARSET=utf8mb4;

五、关键注意事项

1. 不同引擎的表无法关联事务（如 InnoDB 表和 MyISAM 表在同一事务中，MyISAM 操作不会回滚）。

2. Memory 引擎表若需持久化，可定期导出为 InnoDB 表。

3. 升级 MySQL 时，MyISAM 表建议迁移为 InnoDB（MySQL 8.0 后逐步淘汰 MyISAM）。