第 8 章 存储引擎对比与选型面试精讲

📖 本篇简介：存储引擎是 MySQL "Server 层 + 插件引擎"架构的特色。本篇用 15 道高频题对比 InnoDB、MyISAM、Memory、Archive、CSV 等常用引擎，讲清 5.5 之后为什么 InnoDB 成为默认、各自适用场景、数据目录文件区别、选型原则。学完本篇，你能根据业务场景（OLTP/报表/缓存/归档）说出该选哪个引擎，并解释为什么现代业务几乎只用 InnoDB。前置知识：第 3~7 章事务、锁、MVCC、架构；本系列示例基于 MySQL 8.0。

目录

• 1 存储引擎高频考点地图
• 题目 1MySQL 支持哪些存储引擎默认是哪个
• 题目 2InnoDB 和 MyISAM 的核心区别
• 题目 3MyISAM 为什么被淘汰了
• 题目 4Memory 引擎是什么什么场景用
• 题目 5Archive 引擎适合什么场景
• 题目 6CSV 引擎是干什么的
• 题目 7怎么看一张表用的什么引擎
• 题目 8怎么修改一张表的存储引擎有什么风险
• 题目 9InnoDB 为什么 55 之后成为默认引擎
• 题目 10不同引擎在磁盘上对应哪些文件
• 题目 11还有什么场景适合用 MyISAM
• 题目 12InnoDB 和 MyISAM 对外键和全文索引的支持
• 题目 13怎么根据业务场景选存储引擎
• 题目 1480 废弃了哪些引擎
• 题目 15一个数据库实例里能混用不同引擎的表吗
• 存储引擎速查表
• 常见坑与注意事项
• 复习清单
• 练习题

1 存储引擎高频考点地图

| 难度 | 典型问题 | 本篇对应 |

|---|---|---|

| 入门 | 默认引擎？InnoDB vs MyISAM？ | 题目 1、2 |

| 基础 | Memory/Archive？怎么改引擎？ | 题目 4、5、8 |

| 进阶 | 数据目录文件？外键？ | 题目 10、12 |

| 实战 | 选型？8.0 废弃？ | 题目 13、14 |

一条主线记住：InnoDB 是 OLTP 默认，其他引擎都是 niche。面试重点是 InnoDB vs MyISAM 对比，其他引擎能说出用途即可。

题目 1：MySQL 支持哪些存储引擎？默认是哪个？

考点

• SHOW ENGINES；
• InnoDB 默认；
• 插件架构。

参考答案

-- 查看所有支持的引擎（MySQL 8.0）
SHOW ENGINES;

| 引擎 | 是否支持事务 | 典型用途 |

|---|---|---|

| InnoDB | ✅ | 默认引擎，OLTP |

| MyISAM | ❌ | 历史遗留，读多写少 |

| Memory | ❌ | 数据全在内存，临时表 |

| CSV | ❌ | CSV 文件表 |

| Archive | ❌ | 归档压缩 |

| BLACKHOLE | ❌ | 丢数据（复制用） |

• MySQL 5.5 之前默认是 MyISAM，5.5 起默认 InnoDB；
• 存储引擎是插件式，Server 层负责 SQL 解析、优化，引擎负责数据存取。

追问

• 问：Server 层和引擎层怎么分工？答：Server 层负责连接、SQL 解析、优化、binlog；引擎层负责数据读写、事务、锁。
• 问：一张表能在查询时换引擎吗？答：不能，引擎是表级属性，建表时定。

题目 2：InnoDB 和 MyISAM 的核心区别？

考点

• 事务、锁、外键、MVCC、崩溃恢复；
• 性能特点；
• 数据存储。

参考答案

| 对比 | InnoDB | MyISAM |

|---|---|---|

| 事务 | ✅ 支持 | ❌ |

| 锁粒度 | 行锁（默认） | 表锁 |

| 外键 | ✅ | ❌ |

| 崩溃恢复 | ✅ redo log | ❌ 损坏难恢复 |

| MVCC | ✅ | ❌ |

| 全文索引 | ✅（8.0 InnoDB） | ✅（老版本） |

| 适用 | OLTP 高并发读写 | 读多写少、报表 |

• 一句话：InnoDB 支持事务 + 行锁 + 崩溃恢复，MyISAM 啥都没有但读快。

-- 建表指定引擎
CREATE TABLE t_innoDB (...) ENGINE=InnoDB;
CREATE TABLE t_myisam  (...) ENGINE=MyISAM;

追问

• 问：MyISAM 读为什么快？答：没有事务开销、没有 MVCC、没有行锁，结构简单；但写一来全堵。
• 问：MyISAM 表损坏怎么办？答：REPAIR TABLE，但可能丢数据；InnoDB 崩溃自动恢复。

题目 3：MyISAM 为什么被淘汰了？

考点

• 不支持事务；
• 表锁并发差；
• 崩溃易损坏；
• 5.5 默认 InnoDB。

参考答案

• MyISAM 的硬伤：

1. 不支持事务： atomicity 没有，业务出错不能回滚；

2. 表锁：写操作锁全表，并发写几乎不可用；

3. 崩溃恢复差：没有 redo log，宕机后表容易损坏，要 REPAIR TABLE，可能丢数据；

4. 不支持外键：引用完整性靠业务；

• 随着互联网高并发业务兴起，MyISAM 扛不住；5.5 官方默认改 InnoDB；8.0 里 MyISAM 仍保留但只用于系统表（如 mysql.slow_log）。

追问

• 问：MyISAM 现在还有用吗？答：几乎没有；历史老系统可能还在用，新业务一律 InnoDB。
• 问：迁移 MyISAM 到 InnoDB 要注意什么？答：大表用 pt-online-schema-change；注意表锁变行锁后行为变化。

题目 4：Memory 引擎是什么？什么场景用？

考点

• 数据全在内存；
• 重启丢数据；
• 默认哈希索引；
• 临时表/缓存。

参考答案

• Memory 引擎：数据存在内存里，表结构落盘、数据在内存，重启后数据全丢；
• 索引默认 HASH（等值快），也支持 B-Tree；
• 不支持 TEXT/BLOB 大字段；
• 场景：

1. 临时表、中间结果；

2. 对性能要求极高、可丢失的缓存；

• 生产一般不用它做业务存储（Redis 更合适）。

-- Memory 表演示（MySQL 8.0）
CREATE TABLE t_cache (
  k VARCHAR(64) PRIMARY KEY,
  v VARCHAR(255)
) ENGINE=MEMORY;
INSERT INTO t_cache VALUES('a', '1');
SELECT * FROM t_cache;   -- 内存读，极快
-- 重启 MySQL 后表还在，但数据没了

追问

• 问：Memory 和 Redis 谁快？答：内存数据库都快，但 Redis 持久化、集群、丰富数据类型更成熟；MySQL Memory 适合简单临时表。
• 问：Memory 表锁粒度？答：表锁，写并发差。

题目 5：Archive 引擎适合什么场景？

考点

• 高压缩、只插入；
• 日志归档；
• 不支持索引（除自增列）。

参考答案

• Archive 引擎：专门为归档数据设计，高度压缩（约 InnoDB 的 1/10 空间）；
• 特点：

1. 只支持 INSERT 和 SELECT，不支持 DELETE/UPDATE（有限制）；

2. 除自增列外不建索引；

3. 数据压缩存盘，磁盘占用小；

• 场景：日志、审计记录等只需追加、很少查询的冷数据。

CREATE TABLE t_log_archive (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  log_time DATETIME,
  msg TEXT
) ENGINE=ARCHIVE;

追问

• 问：Archive 能 SELECT 条件查询吗？答：能，但没索引要全表扫，适合低频次归档查询。
• 问：为什么不直接用 InnoDB 压缩？答：InnoDB 压缩表有额外 CPU 开销；Archive 是"为压缩而生"，更省空间。

题目 6：CSV 引擎是干什么的？

考点

• 表 = CSV 文件；
• 数据交换；
• 不支持索引。

参考答案

• CSV 引擎：把 CSV 文件直接当表读写，每行对应 CSV 一行；
• 场景：

1. 与外部系统交换数据（导出 Excel 格式）；

2. 简单数据导入导出；

• 不支持索引、不支持事务，性能一般；
• 生产业务表不用它。

CREATE TABLE t_csv (
  id INT, name VARCHAR(50)
) ENGINE=CSV;
-- 对应数据库目录下生成 t_csv.CSV 文件，可直接编辑

追问

• 问：CSV 表怎么备份？答：直接复制 .CSV 文件就行。
• 问：和 SELECT ... INTO OUTFILE 区别？答：OUTFILE 是导出语句；CSV 引擎是把 CSV 当表实时读写。

题目 7：怎么看一张表用的什么引擎？

考点

• SHOW TABLE STATUS；
• information_schema.TABLES；
• 建表语句。

参考答案

-- 方法 1：看建表语句末尾
SHOW CREATE TABLE t_user\G
-- 末尾有 ENGINE=InnoDB

-- 方法 2：
SHOW TABLE STATUS LIKE 't_user'\G
-- Engine 列

-- 方法 3：查 information_schema
SELECT table_name, engine
FROM information_schema.TABLES
WHERE table_schema = 'demo' AND table_name = 't_user';

追问

• 问：怎么批量查某库所有表引擎？答：上面 SQL 去掉 table_name 条件即可。
• 问：8.0 系统表用什么引擎？答：mysql 库的系统表仍有 MyISAM/CSV，业务表都该 InnoDB。

题目 8：怎么修改一张表的存储引擎？有什么风险？

考点

• ALTER TABLE ENGINE；
• 整表重建；
• 大表风险。

参考答案

-- 修改表引擎（MySQL 8.0）
ALTER TABLE myisam_table ENGINE=InnoDB;

• 原理：InnoDB 新建一张同结构表 → 把旧数据逐行插入 → 改名替换；
• 风险：

1. 大表锁表/慢：ALTER 期间可能阻塞读写（8.0 Online DDL 缓解）；

2. 空间翻倍：重建过程要临时空间；

3. 索引、外键行为变化：MyISAM 无外键，转 InnoDB 要补外键约束；

• 大表迁移推荐用 pt-online-schema-change 或 gh-ost。

-- 大表改引擎的安全姿势（gh-ost 思路）：
-- 1. 建影子表 _t_new
-- 2. 双写（触发器/binlog 同步增量）
-- 3. 一次性切名

追问

• 问：ALTER 改 ENGINE 会丢数据吗？答：不丢，但要确保磁盘空间够、备份好。
• 问：8.0 Online DDL 还锁表吗？答：ALGORITHM=INPLACE, LOCK=NONE 多数改引擎不阻塞读写，但仍有 MDL 瞬间。

题目 9：InnoDB 为什么 5.5 之后成为默认引擎？

考点

• 事务、行锁、崩溃恢复；
• 互联网高并发需求；
• Oracle 收购 MySQL 后的战略。

参考答案

• 5.1 及之前默认 MyISAM；
• 原因：

1. 业务需求：电商、金融需要事务（订单、转账）；

2. 并发：行锁比表锁适合高并发写；

3. 数据安全：崩溃自动恢复，不用 REPAIR TABLE；

4. Oracle 收购 InnoDB 后（2005），把 InnoDB 整合进 MySQL；

• 5.5（2010）起 InnoDB 成默认，此后 MyISAM 逐步退出历史舞台。

追问

• 问：InnoDB 是 MySQL 公司自己开发的吗？答：不是，是 Innobase 公司开发，2005 被 Oracle 收购；MySQL 以插件形式集成。
• 问：8.0 还支持 MyISAM 吗？答：支持，但只用于 mysql 库的系统表，业务表不推荐。

题目 10：不同引擎在磁盘上对应哪些文件？

考点

• .frm 表结构；
• .ibd InnoDB 数据；
• .MYD/.MYI MyISAM；
• 8.0 数据字典。

参考答案

MySQL 数据目录下每个库一个目录，表文件：

| 文件 | 作用 | 引擎 |

|---|---|---|

| .frm | 表结构定义 | 5.x 所有引擎（8.0 取消，数据字典统一） |

| .ibd | 表数据 + 索引 | InnoDB（file_per_table=ON） |

| .MYD | 表数据 | MyISAM |

| .MYI | 表索引 | MyISAM |

| .CSM/.CSV | CSV 表元数据/数据 | CSV 引擎 |

| ibdata1 | 系统表空间（数据字典、undo、doublewrite） | InnoDB 共享 |

• 8.0 把 .frm 统一到数据字典（mysql.ibd），不再每表一个 .frm。

SHOW VARIABLES LIKE 'datadir';   -- 数据目录位置
SHOW VARIABLES LIKE 'innodb_file_per_table';  -- ON = 每表一个 .ibd

追问

• 问：InnoDB 数据都在 .ibd 吗？答：独立表空间在 .ibd；系统表空间在 ibdata1。
• 问：MyISAM 表删了数据文件能直接拷吗？答：能，文件级复制即可（但 InnoDB 不行，要 mysqldump）。

题目 11：还有什么场景适合用 MyISAM？

考点

• 几乎没有；
• 历史上的报表/只读；
• 现在都用 InnoDB。

参考答案

• 现代业务几乎没有必须用 MyISAM 的场景：

1. 历史上"读多写少的报表"用 MyISAM，但 InnoDB 现在读性能也足够；

2. 全文索引老版本 MyISAM 强，8.0 InnoDB 支持全文索引；

3. 压缩归档用 Archive 或 InnoDB 压缩表更合适；

• 结论：新业务一律 InnoDB，老 MyISAM 表慢慢迁移。

追问

• 问：只读数据仓库呢？答：用专门的分析引擎（ClickHouse、Doris），不是 MyISAM。
• 问：MyISAM 表锁表了怎么办？答：KILL 写连接；长期解是迁 InnoDB。

题目 12：InnoDB 和 MyISAM 对外键和全文索引的支持？

考点

• 外键只 InnoDB；
• 全文索引 8.0 InnoDB 支持；
• 实践选型。

参考答案

| 特性 | InnoDB | MyISAM |

|---|---|---|

| 外键 FOREIGN KEY | ✅ | ❌ |

| 全文索引 FULLTEXT | ✅（5.6+，8.0 ngram 中文） | ✅（老版本） |

| 空间索引 SPATIAL | ✅ | ✅ |

• 互联网高并发一般拆掉外键（业务层维护引用完整性），所以外键不是选引擎的决定因素；
• 全文检索大规模用 Elasticsearch，MySQL 全文索引只做中小场景。

-- InnoDB 全文索引（8.0 ngram 中文）
CREATE TABLE t_article (
  id BIGINT PRIMARY KEY,
  body TEXT,
  FULLTEXT KEY ft_body (body) WITH PARSER ngram
) ENGINE=InnoDB;

追问

• 问：外键为什么高并发不推荐？答：外键检查加锁、级联更新阻塞、故障难排查。
• 问：InnoDB 全文索引和 ES 差距？答：ES 支持分词定制、相关性打分、集群；MySQL 全文索引是"够用但不专业"。

题目 13：怎么根据业务场景选存储引擎？

考点

• OLTP 用 InnoDB；
• 临时数据 Memory；
• 归档 Archive；
• 分析列存。

参考答案

| 业务场景 | 推荐引擎 |

|---|---|

| 在线交易（订单、账户、用户） | InnoDB |

| 读写都多的业务表 | InnoDB |

| 纯内存临时计算 | Memory / 临时表 |

| 日志归档冷数据 | Archive / InnoDB 压缩表 |

| 大数据分析报表 | 不在 MySQL，用 ClickHouse/Doris |

| 数据交换 CSV | CSV 引擎（少用） |

• 现代 MySQL 部署99% 用 InnoDB，其他引擎是"知道有就行"。

追问

• 问：大宽表分析用 InnoDB 行吗？答：行存不适合分析，分析要列存（ClickHouse）。
• 问：Redis 能替代 Memory 引擎吗？答：能，而且更成熟，生产缓存一律 Redis。

题目 14：8.0 废弃了哪些引擎？

考点

• 不支持的引擎；
• 保留的引擎；
• 行为变化。

参考答案

• 8.0 仍支持：InnoDB、MyISAM、Memory、CSV、Archive、Blackhole、FEDERATED（可选编译）；
• 8.0 的变化：

1. MyISAM 不再用于业务，只保留系统表；

2. mysql 系统表里一些 MyISAM 表改 InnoDB；

3. 不推荐 MERGE 引擎、FEDERATED 引擎（前者用视图替代，后者性能差）；

• 没有完全删除 MyISAM（兼容老系统），但官方明确推荐 InnoDB。

追问

• 问：FEDERATED 引擎是干什么的？答：访问远程 MySQL 表，像本地表一样；性能差，一般用中间件替代。
• 问：MERGE 引擎呢？答：把多个 MyISAM 表当一个，8.0 用 UNION 视图替代。

题目 15：一个数据库实例里能混用不同引擎的表吗？

考点

• 引擎表级属性；
• 跨引擎 JOIN 限制；
• 运维注意。

参考答案

• 可以，引擎是表级属性，同一实例不同表可用不同引擎；
• 但要注意：

1. 跨引擎 JOIN 没问题（Server 层处理）；

2. 跨引擎外键不行（外键要求两边都 InnoDB）；

3. 备份恢复、监控、参数调优要分别考虑；

• 实践中尽量统一 InnoDB，减少认知负担。

-- 同一个库混合引擎
CREATE TABLE t_order (...) ENGINE=InnoDB;
CREATE TABLE t_log_archive (...) ENGINE=ARCHIVE;
-- 可以 JOIN：
SELECT o.*, l.msg FROM t_order o JOIN t_log_archive l ON ...;

追问

• 问：复制时引擎不一致会怎样？答：主从引擎要一致，否则行为不同（MyISAM 表从库崩溃恢复差）。
• 问：分库分表后引擎要统一吗？答：建议统一 InnoDB，便于运维。

存储引擎速查表

| 引擎 | 事务 | 锁 | 崩溃恢复 | 典型场景 |

|---|---|---|---|---|

| InnoDB | ✅ | 行锁 | ✅ | OLTP 默认 |

| MyISAM | ❌ | 表锁 | ❌ | 历史遗留 |

| Memory | ❌ | 表锁 | ❌ | 临时表、内存缓存 |

| Archive | ❌ | 行锁 | ⚠️ | 日志归档压缩 |

| CSV | ❌ | 表锁 | ❌ | 数据交换 |

| BLACKHOLE | ❌ | - | - | 复制测试 |

| 文件 | 引擎 | 内容 |

|---|---|---|

| .ibd | InnoDB | 数据 + 索引（独立表空间） |

| .MYD/.MYI | MyISAM | 数据 / 索引 |

| .CSV/.CSM | CSV | 数据 / 元数据 |

| ibdata1 | InnoDB | 系统表空间 |

常见坑与注意事项

1. 建表忘了写 ENGINE=InnoDB：8.0 默认 InnoDB，但 5.7 及之前老库可能默认 MyISAM，迁移时要查。

2. MyISAM 表崩溃靠运气：REPAIR TABLE 可能丢数据，重要表必须 InnoDB。

3. Memory 表当业务缓存：重启丢数据，高并发不如 Redis。

4. ALTER 大表改引擎：没备份、磁盘不够、没 gh-ost，中途失败要重来。

5. 跨引擎外键：外键两边必须都是 InnoDB，MyISAM 表外键静默失败。

6. 以为 MyISAM 读快就该用：读快是因为没事务没锁，高并发写一来全堵，得不偿失。

7. CSV 引擎直接编辑文件：绕过 SQL 层可能损坏，别手贱。

复习清单

| 题目 | 核心结论 | 易错点 |

|---|---|---|

| 默认引擎 | 5.5 起 InnoDB | 答成 MyISAM |

| InnoDB vs MyISAM | 事务/行锁/崩溃恢复 | 漏 MVCC |

| MyISAM 淘汰原因 | 表锁、不支持事务 | 说"性能差"不准确 |

| Memory | 内存表重启丢 | 以为是持久缓存 |

| Archive | 高压缩只插入 | 忘了不支持索引 |

| 改引擎 | 整表重建 | 大表直接 ALTER |

| 数据文件 | .ibd/.MYD/.MYI | 8.0 无 .frm |

| 外键 | 只 InnoDB | MyISAM 也支持 |

| 选型 | 业务表一律 InnoDB | 分析用列存不是 MyISAM |

| 混引擎 | 可以但不推荐 | 外键跨引擎不行 |

练习题

练 1：一张 5000 万行的 MyISAM 表要迁到 InnoDB，直接 ALTER TABLE ... ENGINE=InnoDB 有什么风险？怎么做更安全？

<details>

<summary>参考答案</summary>

直接 ALTER 的风险：8.0 Online DDL 虽然不阻塞读写，但整表重建要时间（几小时），期间 MDL 排队、空间翻倍、磁盘不足可能失败。安全做法：

1. 低峰期开始；

2. 确认磁盘剩余 ≥ 表大小 2 倍；

3. 用 pt-online-schema-change 或 gh-ost 影子表方案；

4. 迁移后 ANALYZE TABLE 刷新统计信息；

5. 业务回归测试（行为从表锁变行锁）。

</details>

练 2：业务要存"用户操作日志"，每月 5 亿行，几乎不查只追加。选什么引擎？怎么设计？

<details>

<summary>参考答案</summary>

不选 InnoDB 主表存这么多热数据。方案：

1. 热数据（近 3 个月）InnoDB 在线查；

2. 冷数据（3 个月以上）归档到 Archive 表或单独库；

3. 更适合：直接写 Kafka/ES/ClickHouse，MySQL 只存摘要。

Archive 引擎适合"只追加很少查"的场景，但 5 亿行仍要分库分表。

</details>

练 3：为什么 Redis 比 MySQL Memory 引擎更适合做业务缓存？

<details>

<summary>参考答案</summary>

1. Redis 持久化（RDB/AOF），重启不丢；Memory 引擎重启丢数据；

2. Redis 集群、高可用、丰富数据结构（hash/zset/list）；

3. Memory 引擎表锁，写并发差；Redis 单线程模型高并发；

4. Redis 过期策略、内存淘汰策略成熟。

Memory 引擎只适合 MySQL 内部临时表。

</details>

下一篇预告：第 9 章 JOIN与多表查询面试精讲