MySQL三大存储引擎对比：InnoDB vs MyISAM vs MEMORY

事务支持 (ACID)

• InnoDB: 核心优势之一。 完全支持事务（BEGIN, COMMIT, ROLLBACK），满足 ACID（原子性、一致性、隔离性、持久性）特性。这是需要确保数据完整性和一致性的应用（如银行交易、订单系统）的必备选择。
• MyISAM / MEMORY: 不支持事务。 执行 INSERT、UPDATE、DELETE 等操作会直接修改磁盘/内存数据，无法回滚。如果操作中途出错或系统崩溃，数据可能处于不一致状态。

锁机制 (Locking)

• InnoDB: 默认采用行级锁。当修改数据时，只锁定需要修改的特定行，其他行仍可被并发访问。这极大地提高了在高并发写入场景下的性能和吞吐量。它也支持表级锁。
• MyISAM: 只支持表级锁。任何写操作（INSERT, UPDATE, DELETE）都会锁定整个表，阻塞该表上所有其他的写入和读取操作（写锁排斥所有操作）。并发写性能很差，尤其在写密集场景下。读操作会加共享锁（允许其他读锁）。
• MEMORY: 也使用表级锁。并发写入性能同样受限。

外键约束 (Foreign Keys)

• InnoDB: 支持外键约束。数据库本身可以强制维护表与表之间引用的完整性（REFERENCES 和 ON DELETE/UPDATE CASCADE/SET NULL 等规则）。
• MyISAM / MEMORY: 不支持外键约束。引用完整性需要在应用层代码中维护。

崩溃恢复 (Crash Recovery)

• InnoDB: 具有强大的崩溃恢复能力。它使用 Write-Ahead Logging 机制（主要是 redo log）。事务提交时，先将修改记录到 redo log (顺序写，很快)，然后再异步刷新到磁盘数据文件中。如果数据库崩溃，重启时可以根据 redo log 重做已经提交但未写入数据文件的事务，或者回滚未提交的事务，保证数据的一致性和持久性。
• MyISAM: 崩溃恢复能力较弱。发生崩溃后，表更容易损坏（如索引文件 .MYI 损坏）。通常需要使用 CHECK TABLE / REPAIR TABLE 命令来检查并尝试修复表，但修复可能导致数据丢失。
• MEMORY: 重启后数据丢失。由于数据只存储在内存中，MySQL 服务重启或服务器重启都会导致所有 MEMORY 表中的数据完全丢失。

MVCC (Multi-Versioning Concurrency Control)

• InnoDB: 支持 MVCC。这是实现高并发读的关键机制。它为每个读取操作提供一个数据库在某个时间点的快照（一致性读视图）。读操作不会阻塞写操作，写操作也不会阻塞读操作（除了写操作之间需要的行锁）。
• MyISAM / MEMORY: 不支持 MVCC。读操作需要等待写锁释放（在 MyISAM 表级锁下），或者写操作需要等待读锁释放（较少见），并发读写性能较差。

索引结构 (Indexing)

• InnoDB: 使用聚簇索引。这意味着表数据本身是按照主键的顺序物理存储在磁盘上的（主键索引的叶子节点直接包含行数据）。因此基于主键的查询非常高效。所有二级索引的叶子节点存储的是主键值（而不是指向行的物理指针）。这可能导致二级索引查找需要回表（通过主键再查一次聚簇索引）。支持 B-Tree 索引（默认）和自适应哈希索引（内部自动管理）。
• MyISAM: 使用非聚簇索引（堆组织表）。数据文件（.MYD）和索引文件（.MYI）是分开存储的。索引的叶子节点存储的是指向数据文件中行位置的物理指针（如偏移量）。支持 B-Tree 索引（默认）、R-Tree 空间索引（用于 GIS 数据）和 Full-text 全文索引。压缩表仅支持查询。
• MEMORY: 默认使用 Hash 索引。Hash 索引对于精确匹配查询（=， IN）效率极高（O(1) 时间复杂度）。也支持 B-Tree 索引（通过 USING BTREE 创建）。不支持 TEXT/BLOB 类型。

物理存储

• InnoDB: 数据存储在表空间中。可以是共享表空间（ibdata1）或独立表空间（每个表一个 .ibd 文件，现代版本的默认方式）。数据和索引通常存储在一起（聚簇索引）。
• MyISAM: 每个表存储在磁盘上的三个文件中：
  • .frm: 表结构定义文件。
  • .MYD (MYData): 实际数据文件。
  • .MYI (MYIndex): 索引文件。
• MEMORY: 数据仅存储在内存中。表结构定义存储在磁盘上的 .frm 文件中。没有对应的磁盘数据文件。MySQL 服务重启后，表结构保留，但数据丢失。

缓存

• InnoDB: 使用 缓冲池 (Buffer Pool)。这是内存中的一大块区域，用于缓存表数据和索引。这是 InnoDB 性能的关键，能极大减少磁盘 I/O。
• MyISAM: 主要依赖操作系统的文件系统缓存来缓存数据（.MYD）。Key Buffer 用于缓存索引（.MYI）。
• MEMORY: 数据本身就在内存中。不需要额外的缓存机制来避免磁盘 I/O。

压缩

• InnoDB: 支持页级别压缩（需要底层文件系统支持）。
• MyISAM: 支持表级别压缩（使用 myisampack 工具），生成只读压缩表，非常适合归档或只读日志数据。
• MEMORY: 不支持压缩。

适用场景总结

• InnoDB: 绝大多数情况下的首选引擎。 适用于需要事务支持（保证一致性）、高并发读写（行级锁）、外键约束、良好崩溃恢复能力的场合。如核心业务表、订单系统、用户账户信息等。对于写密集型应用（如高并发插入、更新）表现更好。
• MyISAM: 逐渐被 InnoDB 取代。 适用于：
  • 只读或读多写少的应用（表级锁在纯读时并发好）。
  • 不需要事务支持。
  • 不需要外键约束。
  • 对崩溃恢复要求不高（或能接受手动修复）。
  • 需要全文索引（但在 MySQL 5.6+ InnoDB 也支持了）或空间索引（GIS 数据）。
  • 日志表、数据仓库的只读阶段表（可利用压缩节省空间）。
• MEMORY: 临时数据存储。 适用于：
  • 需要极高速度访问的、临时的、非关键的数据。
  • 数据量不大且能完全放入内存（受 max_heap_table_size 限制）。
  • 数据可以容忍重启后丢失。
  • 如会话管理（如果不需要持久化）、缓存中间结果、查找表（如邮编映射）、临时处理区域等。不适合存储大量数据或需要持久化的数据。 注意：MEMORY 表使用固定长度行格式，VARCHAR 会转换为 CHAR，可能浪费空间。