MySQL 常见的存储引擎及其核心区别

MySQL 支持多种存储引擎（Storage Engine），每种引擎有不同的设计目标和适用场景。以下是常见的存储引擎及其核心区别：

1. InnoDB

• 事务支持：支持 ACID 事务（通过 BEGIN/COMMIT/ROLLBACK 控制）。
• 锁机制：默认使用 行级锁（Row-level Locking），支持高并发。
• 外键：支持外键约束（Foreign Key）。
• 崩溃恢复：通过 Redo Log 和 Undo Log 实现崩溃后的自动恢复。
• 索引结构：使用 B+ 树索引，支持聚簇索引（数据与主键索引绑定存储）。
• 适用场景：OLTP（联机事务处理）、高并发读写、需要事务的场景。
• 其他特性：
  • 支持 MVCC（多版本并发控制），提高读并发。
  • 数据按页（16KB）存储，支持热备份（如 mysqldump）。

2. MyISAM

• 事务支持：不支持事务。
• 锁机制：仅支持 表级锁（Table-level Locking），并发性能低。
• 外键：不支持外键。
• 崩溃恢复：无崩溃自动恢复机制，易损坏需手动修复（REPAIR TABLE）。
• 索引结构：使用 B+ 树索引，非聚簇索引（数据与索引分离存储）。
• 适用场景：读多写少、不需要事务的 OLAP（如日志分析）、全文索引（MySQL 5.6 前唯一支持全文索引的引擎）。
• 其他特性：
  • 表由 .MYD（数据）、.MYI（索引）、.frm（表结构）文件组成。
  • 支持压缩表（只读场景）。

3. Memory（HEAP）

• 数据存储：数据存储在内存中，重启后数据丢失。
• 锁机制：表级锁。
• 索引结构：默认哈希索引，也支持 B+ 树索引。
• 适用场景：临时表、缓存表、快速读写但无需持久化的场景。
• 限制：
  • 不支持 TEXT/BLOB 类型。
  • 表大小受 max_heap_table_size 参数限制。

4. Archive

• 设计目标：高压缩比的只写/只读存储。
• 事务支持：不支持事务。
• 锁机制：行级锁（仅插入时）。
• 数据压缩：插入数据时自动压缩（压缩比高达 10:1），适合日志归档。
• 限制：
  • 仅支持 INSERT 和 SELECT，不支持 DELETE/UPDATE（需先解压再操作）。
  • 查询时需全表扫描。

5. CSV

• 数据格式：数据以纯 CSV 文件格式存储（.CSV 文件）。
• 事务支持：不支持事务。
• 适用场景：与其他系统交换数据（如 Excel 导入导出）。
• 限制：
  • 所有列必须为 NOT NULL。
  • 不支持索引。

6. Blackhole

• 数据存储：不存储任何数据，写入的数据直接被丢弃。
• 适用场景：
  • 主从复制架构中作为中继节点。
  • 测试 SQL 语句的合法性。

7. Federated

• 数据存储：不存储数据，通过远程 MySQL 表访问其他服务器的数据。
• 适用场景：分布式查询（类似数据库链接）。
• 限制：性能低，安全性差，MySQL 8.0 已默认禁用。

核心区别总结

如何选择存储引擎？

1. 需要事务和高并发 → InnoDB（默认选择）。
2. 只读或读多写少 → MyISAM（注意 MySQL 8.0 已移除对 MyISAM 的系统表支持）。
3. 临时数据或缓存 → Memory。
4. 日志归档 → Archive。
5. 跨服务器查询 → Federated（谨慎使用）。

注意

• MySQL 5.5+ 默认引擎是 InnoDB，建议优先使用 InnoDB。
• 可通过 SHOW ENGINES; 查看支持的引擎，或 CREATE TABLE ... ENGINE=xxx; 指定引擎。