存储引擎对比

导学

CREATE TABLE 时写的 ENGINE=InnoDB 不是摆设——它决定了这张表的数据怎么存、支不支持事务、崩溃后能不能恢复。MySQL 5.7 支持多种存储引擎，但生产环境 99% 的场景只需要 InnoDB。理解各引擎的核心差异，才能在特定场景（如只读归档、临时缓存）做出正确选择，而不是盲目跟风或固守单一方案。

定义

存储引擎（Storage Engine）：MySQL 中负责数据存储、索引管理、事务支持、并发控制等底层操作的插件式组件。不同引擎在磁盘格式、内存结构、锁机制、事务支持上完全不同，对同一张表执行 SELECT/INSERT，底层可能走完全不同的代码路径。

核心引擎对比

SQL 示例

场景一：查看支持的存储引擎

执行语句：

SHOW ENGINES;

操作后结果（MySQL 5.7 典型输出）：

结果解读：

• Support = DEFAULT：InnoDB 是 5.7 的默认存储引擎
• Support = YES：可用但未默认；NO：未启用或不可用
• Transactions = YES：只有 InnoDB 支持事务，其他引擎的 DML 都是自动提交的
• XA = YES：只有 InnoDB 支持 XA 分布式事务

场景二：创建不同引擎的表并观察差异

执行语句：

-- InnoDB 表（默认，支持事务和行锁）
CREATE TABLE employees_innodb (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
) ENGINE=InnoDB;

-- MyISAM 表（表锁，不支持事务）
CREATE TABLE employees_myisam (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
) ENGINE=MyISAM;

-- MEMORY 表（数据存内存，重启丢失）
CREATE TABLE employees_memory (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
) ENGINE=MEMORY;

-- 插入相同数据
INSERT INTO employees_innodb (emp_name, dept, score) VALUES ('大翔', '技术部', 100);
INSERT INTO employees_myisam (emp_name, dept, score) VALUES ('大翔', '技术部', 100);
INSERT INTO employees_memory (emp_name, dept, score) VALUES ('大翔', '技术部', 100);

查看表文件（Linux 数据目录下）：

# InnoDB: 数据在共享表空间或独立 .ibd 文件
ls employees_innodb*
# employees_innodb.frm  employees_innodb.ibd

# MyISAM: 数据和索引分开存储
ls employees_myisam*
# employees_myisam.frm  employees_myisam.MYD  employees_myisam.MYI

# MEMORY: 只有表结构定义文件，无数据文件
ls employees_memory*
# employees_memory.frm

结果解读：

• InnoDB：.frm（表结构）+ .ibd（数据和索引，如果 innodb_file_per_table=ON）
• MyISAM：.frm（表结构）+ .MYD（数据文件）+ .MYI（索引文件）。数据和索引分离，可以单独压缩数据或修复索引
• MEMORY：只有 .frm 文件，所有数据存在内存中，MySQL 重启后数据全部丢失

场景三：事务支持差异（InnoDB vs MyISAM）

当前数据状态：employees_innodb 和 employees_myisam 各有 1 条数据。

执行语句：

-- ========== InnoDB：支持事务回滚 ==========
START TRANSACTION;
INSERT INTO employees_innodb (emp_name, dept, score) VALUES ('白歌', '技术部', NULL);
ROLLBACK;

SELECT * FROM employees_innodb;

InnoDB 操作后结果：

-- ========== MyISAM：不支持事务，ROLLBACK 无效 ==========
-- MyISAM 没有 START TRANSACTION 语义，每条语句自动提交
INSERT INTO employees_myisam (emp_name, dept, score) VALUES ('白歌', '技术部', NULL);

-- 尝试"回滚"（MyISAM 没有事务，这条语句只是空操作）
ROLLBACK;

SELECT * FROM employees_myisam;

MyISAM 操作后结果：

结果解读：

• InnoDB 的 ROLLBACK 成功撤销了未提交的插入，数据保持 1 条
• MyISAM 没有事务概念，INSERT 立即持久化，ROLLBACK 对其无效，数据变成 2 条
• 这是选择引擎的首要原则：需要事务一致性（转账、订单）必须用 InnoDB

场景四：MEMORY 引擎的数据易失性

当前数据状态：employees_memory 有 1 条数据。

执行语句：

-- 查看 MEMORY 表数据
SELECT * FROM employees_memory;

操作后结果：

-- 模拟重启后（实际执行 FLUSH TABLES 或重启 MySQL 服务）
-- 数据全部丢失，只剩空表结构
SELECT * FROM employees_memory;

重启后结果：

Empty set (0.00 sec)

结果解读：

• MEMORY 引擎所有数据保存在内存中，查询速度极快（类似 Redis）
• 但MySQL 重启、崩溃、甚至 FLUSH TABLES 都可能导致数据丢失
• 适用场景：临时计算结果、会话级缓存、中间计算表。不适合持久化存储
• MEMORY 表默认使用哈希索引，等值查询极快，但范围查询和排序性能差

场景五：ARCHIVE 引擎的高压缩存储

执行语句：

-- 创建 ARCHIVE 表（高压缩，只支持 INSERT 和 SELECT）
CREATE TABLE logs_archive (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;

-- ARCHIVE 支持插入
INSERT INTO logs_archive (message) VALUES
('用户登录成功'),
('订单创建: #1001'),
('支付完成: #1001');

SELECT * FROM logs_archive;

操作后结果：

-- ARCHIVE 不支持 UPDATE/DELETE/索引（主键也是逻辑上的，无实际索引）
UPDATE logs_archive SET message = '修改' WHERE log_id = 1;
-- ERROR 1031 (HY000): Table storage engine for 'logs_archive' doesn't have this option

结果解读：

• ARCHIVE 引擎使用 zlib 压缩，存储空间通常只有 InnoDB 的 1/5 ~ 1/10
• 只支持 INSERT 和 SELECT，不支持 UPDATE、DELETE、索引、事务
• 插入时会被压缩，查询时实时解压，所以查询比 InnoDB 慢
• 适用场景：审计日志、操作记录、历史归档——写入后不再修改，但需要长期保留备查

常见误区

面试考点

Q：InnoDB 和 MyISAM 的核心区别？

1. 事务：InnoDB 支持 ACID 事务，MyISAM 不支持；2. 锁：InnoDB 行级锁，MyISAM 表级锁；3. 崩溃恢复：InnoDB 有 redo/undo log，崩溃后可恢复，MyISAM 可能损坏需修复；4. 外键：InnoDB 支持，MyISAM 不支持；5. 存储：InnoDB 聚簇索引（数据和主键在一起），MyISAM 非聚簇（数据和索引分离）；6. 全文索引：MyISAM 原生支持，InnoDB 5.6.4+ 支持。MySQL 5.7 默认和推荐都是 InnoDB。

Q：什么场景还会用 MyISAM？

极少数场景：1. 纯只读且需要全文检索的老系统（5.6 之前 InnoDB 不支持全文索引）；2. 需要极度压缩的静态数据（MyISAM 支持 myisampack 压缩）；3. 某些 GIS 空间数据的旧应用。新项目不应使用 MyISAM。

Q：MEMORY 引擎的索引类型？

MEMORY 引擎默认使用哈希索引（HASH），等值查询（=、IN）极快，但范围查询（>、<、BETWEEN）和排序必须全表扫描。建表时可以指定 INDEX idx USING BTREE 改用 B-Tree 索引，支持范围查询。

Q：跨存储引擎的事务有什么问题？

如果事务中同时操作 InnoDB 表和 MyISAM 表，InnoDB 部分可以正常回滚，但 MyISAM 部分的变更已经自动提交，无法回滚。这会导致数据不一致。一个事务内的所有表应使用同一引擎，且需要事务支持时必须全部用 InnoDB。

Q：如何查看和修改表的存储引擎？

查看：SHOW CREATE TABLE table_name; 或 SHOW TABLE STATUS LIKE 'table_name';。修改：ALTER TABLE table_name ENGINE=InnoDB;。注意：修改引擎会重建整张表，大表需要维护窗口或使用 pt-online-schema-change。

小结

• InnoDB：MySQL 5.7 默认引擎，支持事务、行锁、崩溃恢复、外键，适合 99% 的 OLTP 场景
• MyISAM：表锁、无事务、数据和索引分离，只读/全文检索老系统可能用到，新项目不推荐
• MEMORY：数据存内存，查询极快但重启丢失，适合临时表和会话缓存
• ARCHIVE：高压缩、只支持 INSERT/SELECT，适合审计日志和历史归档
• 生产环境优先统一使用 InnoDB，跨引擎事务会导致不一致

下一章引子：存储引擎决定了表的"性格"，而 InnoDB 作为最复杂的引擎，内部还有 Change Buffer 和 Doublewrite Buffer 等机制来平衡性能与可靠性——它们是 InnoDB 既能高速写入又能保证数据不损坏的秘密武器。