文章目录
- MySQL 存储引擎讲透：InnoDB 与 MyISAM 到底差在哪，MySQL 8.0 该怎么选
- 一、存储引擎是什么：MySQL 的插件式架构
- 二、文件层面：一眼看出它是什么引擎
- 三、核心差异一：聚簇索引 vs 非聚簇索引
- 3.1 InnoDB：数据就长在主键索引的叶子节点上
- 3.2 MyISAM：索引里只有地址
- 四、核心差异二：事务与崩溃恢复
- 五、核心差异三：锁粒度与并发
- 六、核心差异四：那些容易忽略的行为差异
- 6.1 COUNT(*)
- 6.2 AUTO_INCREMENT
- 6.3 TRUNCATE / DELETE 整表
- 6.4 外键、全文索引、表压缩
- 七、一张大表看懂全部差异
- 八、其他引擎速览
- 九、MySQL 8.0 之后的变化（重点）
- 十、查看与修改存储引擎
- 十一、怎么选：一张场景对照表
- 十二、常见误区与最佳实践
- 十三、小结
MySQL 存储引擎讲透：InnoDB 与 MyISAM 到底差在哪，MySQL 8.0 该怎么选
“InnoDB 支持事务行锁，MyISAM 快但不支持事务”——这句话你已经背了很多年，
但它既没说清为什么，也没告诉你 MySQL 8.0 之后这个话题发生了什么变化。本篇从文件和索引结构出发，把两种引擎的差异讲透，并给出可直接落地的选型依据。
一、存储引擎是什么：MySQL 的插件式架构
MySQL 的一个独特之处在于：SQL 层和存储层是分开的。
客户端 │ ▼ 连接层（连接管理、认证、线程复用） │ ▼ SQL 层（解析器、优化器、执行器）── 这一层不看数据长什么样 │ ▼ 存储引擎层（可插拔）── 真正负责读写磁盘 │ ▼ 文件系统存储引擎是表级别的属性，不是数据库级别的。同一个库里可以有的表是 InnoDB、有的是 Memory，
甚至可以在一条 JOIN 里混用。这种设计让 MySQL 能同时适应 OLTP、只读归档、临时缓存等不同场景。
看看你的实例支持哪些引擎：
SHOWENGINES;典型输出（MySQL 8.0）：
| Engine | Support | 说明 |
|---|---|---|
| InnoDB | DEFAULT | 支持事务、行级锁、外键 |
| MRG_MYISAM | YES | 一组相同结构的 MyISAM 表的逻辑合并 |
| MEMORY | YES | 基于内存，哈希 / BTree 索引，适合临时数据 |
| BLACKHOLE | YES | 写入即丢弃，常用于测试 binlog 转发链路 |
| MyISAM | YES | 传统引擎，表级锁，无事务 |
| CSV | YES | 数据以 CSV 文本存储，方便和外部交换 |
| ARCHIVE | YES | 高压缩比，只支持 INSERT / SELECT |
| PERFORMANCE_SCHEMA | YES | 内部使用 |
| FEDERATED | NO(默认未启用) | 访问远程 MySQL 表 |
二、文件层面：一眼看出它是什么引擎
在磁盘上看一眼文件扩展名，是最快的判断方式：
SHOWVARIABLESLIKE'datadir';-- /var/lib/mysql/MyISAM 每张表三个文件：
| 文件 | 内容 |
|---|---|
t_order.frm | 表结构定义（MySQL 8.0 已移除，见第九节） |
t_order.MYD | MYData，真实数据行 |
t_order.MYI | MYIndex，索引树 |
InnoDB 每张表通常一个文件：
-- 独立表空间（8.0 默认开启）t_order.ibd-- 数据和所有索引都在里面，包括主键聚簇索引开启innodb_file_per_table=ON（5.6 起默认）后，每个表一个.ibd。
如果不开启，所有表的数据会堆到共享的系统表空间ibdata1里——
这个参数是几乎必须开着的，否则 DROP TABLE 之后磁盘空间不会还给操作系统。
这个差异本身就说明了很多问题：数据和索引是不是存在一起，决定了后面所有的行为差异。
三、核心差异一：聚簇索引 vs 非聚簇索引
这是两者最本质、也最影响性能的区别。
3.1 InnoDB：数据就长在主键索引的叶子节点上
InnoDB 的主键索引是聚簇索引（clustered index），B+Tree 的叶子节点存的是整行数据。
也就是说"表"本身就是那棵按主键排序的 B+Tree。
聚簇索引 B+Tree（按主键 id 排序） ┌──────────────────────────┐ │ 叶子节点 = 完整的行数据 │ id=1 │ name='张三' │ age=20 │ phone=... │ └──────────────────────────┘ 二级索引 idx_name（按 name 排序） ┌───────────────────┐ │ 叶子节点 = 主键值 │ '张三' │ id=1 │ ← 拿到 id 再回 clustered index 查整行 └───────────────────┘这带来三个必须知道的结论：
- InnoDB 一定要有主键。不显式建时，MySQL 会先找一个非空唯一索引顶替，
找不到就生成一个 6 字节的隐藏ROWID。别把这个活儿留给 MySQL，它会浪费一次全表扫描去找列。 - 二级索引查数据要"回表"：先在二级索引树里找到主键值，再回聚簇索引树查一遍。这就是为什么"覆盖索引"那么重要。
- 主键值会被所有二级索引冗余存储，所以主键越短越好 → 这就是推荐
BIGINT 自增而不是 UUID 的底层原因之一。
3.2 MyISAM：索引里只有地址
MyISAM 是非聚簇的，叶子节点存的是数据文件里的物理地址（行偏移量）：
主键索引树（在 MYI 文件里） ┌─────────────────┐ │ 叶子 = 数据地址 │ id=1 │ offset=0x1A30 │ └─────────────────┘ │ ▼ 拿着偏移量去 MYD 文件读整行 ┌────────────────────────┐ │ MYD 数据文件的第 0x1A30 字节 │ └────────────────────────┘主键索引和二级索引结构完全一样，都是"键 → 地址"，没有主次之分，也不需要二级索引回表两次。
听起来 MyISAM 反而少一次查找？理论上确实如此，所以纯主键点查上 MyISAM 可以略快。
但这个优势在现代硬件面前基本被其他短板吃掉了：
| 对比点 | InnoDB（聚簇） | MyISAM（非聚簇） |
|---|---|---|
| 主键点查 | 一次 B+Tree 查找拿到整行 | 一次索引查找 + 一次文件偏移读 |
| 二级索引查询 | 需要回表 | 同样要跳去数据文件读 |
| 范围扫描 | 叶子节点物理相邻，顺序 IO | 地址可能乱序，随机 IO |
| 按主键顺序插入 | 数据天然聚簇，页利用率高 | 无所谓，追加写即可 |
| 表体积变化大时 | 页分裂 / 合并，需要维护 | 行记录可能被拆成碎片 |
范围扫描那一行是关键：InnoDB 按主键顺序的相邻行在同一页或相邻页上，扫起来是顺序 IO；
MyISAM 的 secondary read 容易变成随机 IO。这就是为什么很多人"听说 MyISAM 快"，
实测在真实业务查询上却并不快。
四、核心差异二：事务与崩溃恢复
| 能力 | InnoDB | MyISAM |
|---|---|---|
| 事务（ACID） | ✅ 完整支持 | ❌ 完全不支持 |
| 崩溃后自动恢复 | ✅ 靠 redo log 自动前滚 / 回滚 | ❌ 需手工CHECK TABLE/REPAIR TABLE |
| 崩溃安全 | ✅ | ❌ 可能留下损坏的索引文件 |
| XA 分布式事务 | ✅ | ❌ |
| 保存点 SAVEPOINT | ✅ | ❌ |
MyISAM 的问题不是"没有 COMMIT 关键字"，而是一条 UPDATE 做到一半断电，数据就被写坏了：
可能改了数据文件没改索引，索引和数据对不上，需要myisamchk修复，
而修复本身对大表是小时级的、还冒着丢数据的风险。
InnoDB 靠 redo log 做崩溃恢复，靠 undo log 做回滚，这是一个完整的 WAL 体系。
在生产环境，"崩溃了不用管"这件事的价值远大于单条 SQL 快几毫秒。
顺带一个常考的点：autocommit。InnoDB 每条 SQL 都被包成一个事务自动提交；
多条写操作建议显式BEGIN ... COMMIT，既保证原子性，也减少 fsync 次数（反而更快）。
SETautocommit=0;-- 会话级关闭自动提交（不推荐长期开，容易忘 COMMIT）BEGIN;UPDATEaccountSETbalance=balance-100WHEREid=1;UPDATEaccountSETbalance=balance+100WHEREid=2;COMMIT;五、核心差异三：锁粒度与并发
| 维度 | InnoDB | MyISAM |
|---|---|---|
| 锁粒度 | 行级锁（record / gap / next-key） | 表级锁 |
| 读是否阻塞写 | ❌ 不阻塞（MVCC 快照读） | ✅ 阻塞 |
| 写是否阻塞读 | ❌ 不阻塞（读历史版本） | ✅ 阻塞 |
| 并发 INSERT | 好（行插入意向锁） | 仅尾部追加（concurrent_insert） |
| 死锁 | 可能，InnoDB 有检测与回滚 | 不存在（锁太粗） |
MyISAM 的典型故障形态：一个跑 30 秒的UPDATE（或一个大查询加写），
整张表被锁，后续所有读请求全部排队。这在 OLTP 系统里是灾难。
关于 InnoDB 行锁，有个流传很广的错误说法：“InnoDB 只有在 WHERE 用主键时才加行锁，否则锁全表”。
准确说法是：InnoDB 的行锁是加在索引记录上的。
如果这条 UPDATE 压根没走索引，优化器全表扫描，它就会把扫描到的每一条记录都锁上，
表现形式确实像锁全表，但机制完全不同——根因是没索引，不是 InnoDB 退化。
-- 假设 status 上没有索引UPDATEordersSETstatus=2WHEREcreate_time<'2026-01-01';-- 后果：扫描过的每一行都被加锁，期间别的会话改这些行全部等待ALTERTABLEordersADDINDEXidx_create_time(create_time);-- 加了索引后锁范围回到"命中的行"这也是为什么"更新/删除语句一定要走索引"在 InnoDB 上是一条安全铁律。
六、核心差异四：那些容易忽略的行为差异
6.1 COUNT(*)
经典结论：MyISAM 存了表的总行数，COUNT(*)不带 WHERE 时 O(1) 返回；InnoDB 要扫一遍。
这个说法对，但要补充两点：
- 加了 WHERE，两者都要扫，MyISAM 的优势立刻消失；
- InnoDB 的
COUNT(*)在 MySQL 5.7 之后会自动选择最小的可用二级索引来扫，
而不是扫聚簇索引。所以给一张大表留一个"很小的二级索引"（比如KEY idx_flag(flag)）
能显著降低COUNT(*)的 IO 成本。
-- InnoDB 下可行的优化：用极小的二级索引承担扫描ALTERTABLEbig_tableADDKEYidx_tiny(tiny_flag);SELECTCOUNT(*)FROMbig_table;-- 优化器会挑 idx_tiny 而不是主键树6.2 AUTO_INCREMENT
- InnoDB：自增列必须是某个索引的第一列（通常是主键）。插数据时 InnoDB 会在内存里维护自增值。
- MyISAM：自增列可以和其他列组成联合索引。
另外 8.0 之前 InnoDB 的自增值存在内存里，重启后会取MAX(id)+1重算，
这会导致"删除最大 id 的行再重启，自增值被复用"的现象（8.0 把自增值写进 redo log，解决了这个问题）。
6.3 TRUNCATE / DELETE 整表
MyISAM 的TRUNCATE是重建数据文件，秒级完成；
InnoDB 的TRUNCATE是（8.0 原子 DDL 加持下）drop + recreate tablespace，也很快。
但DELETE FROM t不带条件在 InnoDB 上是一行一行删 + 写 undo/redo，慢且产生大量 undo，
大表清数据务必用TRUNCATE。
6.4 外键、全文索引、表压缩
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 外键约束 | ✅ 支持（生产建议由应用层保证，见后文） | ❌ 不支持 |
| 全文索引 | ✅ 5.6 起支持，5.7 起支持中文（ngram） | ✅ 老版本唯一选择 |
| 表压缩 | ✅ 页压缩，ROW_FORMAT=COMPRESSED/KEY_BLOCK_SIZE | ✅myisampack压缩表（只读） |
| 索引长度上限 | 单列 3072 字节（DYNAMIC 行格式） | 单列 1000 字节 |
| 行格式 | DYNAMIC（8.0 默认）/ COMPACT / REDUNDANT / COMPRESSED | 静态 / 动态 / 压缩 |
七、一张大表看懂全部差异
| 对比维度 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ | ❌ |
| 锁粒度 | 行锁（走索引时） | 表锁 |
| MVCC 读写不互斥 | ✅ | ❌ |
| 外键 | ✅ | ❌ |
| 崩溃恢复 | ✅ 自动 | ❌ 手工修复 |
| 索引结构 | 聚簇，叶子是数据 | 非聚簇，叶子是地址 |
| 主键要求 | 必须有（隐藏 ROWID 兜底） | 可以没有 |
COUNT(*)无 WHERE | 扫描（可用最小二级索引优化） | O(1) 直接读计数 |
| 二级索引存主键值 | ✅（主键要短） | 存地址 |
| 缓存 | Buffer Pool，缓存数据 + 索引 | Key Cache，只缓存索引，数据依赖 OS cache |
| 全文索引 | 支持且更成熟 | 支持（历史更早） |
| 表锁导致写阻塞 | 基本不会 | 常见 |
| 适用场景 | OLTP、绝大多数业务 | 只读归档、统计类、极低并发 |
其中"缓存"这一行经常被忽略：InnoDB 的 Buffer Pool连数据一起缓存，
而 MyISAM 的 Key Cache 只缓存索引，数据行要靠操作系统 page cache——
这意味着 MyISAM 的数据访问受 OS 调度影响，可控性差很多。
八、其他引擎速览
它们用得少，但知道存在能救急：
| 引擎 | 特点 | 什么时候用 |
|---|---|---|
| MEMORY | 数据在内存，重启丢失，默认 HASH 索引 | 会话级临时结果；但注意受max_heap_table_size限制 |
| ARCHIVE | 极高压缩比，只支持 INSERT/SELECT，无索引（MySQL 8.0 起带 AUTO_INCREMENT 索引） | 归档冷数据、审计日志 |
| CSV | 数据就是 CSV 文本文件 | 和外部系统交换数据 |
| BLACKHOLE | 写入即丢弃，但仍记 binlog | 测试、级联复制中间节点、binlog 转发 |
| MERGE (MRG_MYISAM) | 多个结构相同的 MyISAM 表合并成一个逻辑表 | 老的分表方案，如今被分区表 / 中间件取代 |
| FEDERATED | 访问远程 MySQL 表 | 默认不启用，跨实例查询谨慎使用 |
⚠️ 关于 MEMORY 的一个坑：它用固定行长存储，VARCHAR会被当成CHAR处理，
所以一行VARCHAR(1000)无论实际多长都占满；而且它是表级锁，并发写很差。
MySQL 8.0 起内部临时表默认改用新的TempTable引擎，MEMORY 的存在感进一步下降。
九、MySQL 8.0 之后的变化（重点）
很多教材还停留在 5.5/5.6，这几件事在 8.0 已经变了：
- 没有
.frm文件了：8.0 把元数据统一收进 InnoDB 存储的数据字典（Data Dictionary），
表结构定义不再以文件形式散落在目录里。好处是元数据操作变成了事务性的。 - 系统表全面 InnoDB 化：
mysql.user等系统表由 MyISAM 改成 InnoDB。
以前手动改mysql.user的时代彻底结束了。 - 原子 DDL：InnoDB 支持 crash-safe DDL，
DROP TABLE/TRUNCATE执行到一半崩了不会再留下残文件。 - 内部临时表默认改用 TempTable 引擎（8.0），磁盘溢出时用 InnoDB，不再默认 MEMORY。
- MyISAM 分区支持被移除：MySQL 8.0 不再支持对 MyISAM 表做分区。
- 默认字符集改为
utf8mb4（其实是 5.7 就默认了，但 8.0 默认排序规则变成utf8mb4_0900_ai_ci）。
一句话总结：MySQL 8.0 把 MyISAM 从"另一个选项"降级成了"遗留兼容品"。
它还在那儿给你用，但引擎之外的一切新特性都是围绕 InnoDB 设计的。
十、查看与修改存储引擎
-- 方式一：看某张表的引擎（连带行数、数据长度等估算信息）SHOWTABLESTATUSFROMdemoWHEREName='orders'\G-- 方式二：最常用，看建表语句SHOWCREATETABLEorders\G-- 方式三：从元数据表查，适合批量SELECTTABLE_SCHEMA,TABLE_NAME,ENGINEFROMinformation_schema.TABLESWHERETABLE_SCHEMA='demo'ANDENGINE<>'InnoDB';改引擎：
ALTERTABLEordersENGINE=InnoDB;⚠️ 注意点：
- 这是一次全表重建，会锁表（8.0 默认
ALGORITHM=INPLACE也有 IO 与空间开销），大表挑低峰做 - MyISAM → InnoDB 时如果有全文索引，需要重新评估 parser 与参数（见上一篇全文索引的内容）
- InnoDB → MyISAM 在有外键时会失败；需要先
SET foreign_key_checks=0并删掉外键 - 修改默认引擎（只影响之后新建的表）：
[mysqld] default_storage_engine = InnoDB十一、怎么选：一张场景对照表
| 场景 | 推荐 | 理由 |
|---|---|---|
| 绝大多数 OLTP 业务 | InnoDB | 事务、行锁、崩溃恢复，没有第二选择 |
| 读写混合、有并发写 | InnoDB | MyISAM 表锁会让写阻塞所有读 |
| 对账、财务、账户余额 | InnoDB | 强一致 + 事务 |
| 只读的归档表、日志表（从不更新） | InnoDB 或 ARCHIVE | 若完全不更新且追求空间，可考虑 ARCHIVE |
| 报表类"批量导入后只读"的中间表 | MyISAM 理论上可行 | 但运维上多一个特例不值得，仍建议 InnoDB |
| 临时会话数据 | MEMORY / 临时表 | 注意重启丢失和容量限制 |
| 需要 FULLTEXT 全文检索 | InnoDB（8.0） | 别再为了全文索引退回 MyISAM |
| 服务器内存很小（< 2G） | 视情况 | MyISAM 占用少，但 8.0 时代这个理由很难成立 |
务实的结论：在 MySQL 8.0 里，默认 InnoDB，除非你有非常具体的理由并且能说清代价。
十二、常见误区与最佳实践
误区 1：MyISAM 比 InnoDB 快
——只在"无并发、纯全表 COUNT、纯追加写"这类窄场景下成立。真实业务有并发读写，MyISAM 表锁一上来就全崩。
而且这个结论来自十几年前硬件条件下的测试，SSD + 大内存的今天早已不适用。
误区 2：给 InnoDB 表加外键更安全
——技术上支持，但生产上很多团队刻意不用外键：外键会带来额外的锁、级联操作不可控、
阻碍分库分表和在线 DDL。约束逻辑上移到应用层是主流做法。这是个取舍，不是"支持就该用"。
误区 3：ALTER TABLE ... ENGINE=InnoDB是原地无损操作
——它是一次全表拷贝重建，磁盘空间占用临时翻倍，并且会重写所有索引。大表务必评估磁盘与主从延迟。
误区 4：SHOW TABLE STATUS里的 Rows 是准确行数
——InnoDB 的 Rows 是采样估算值（可能偏差 50%），MyISAM 才是精确值。
要看准确行数请乖乖COUNT(*)。这一点在做容量评估时经常坑人。
最佳实践清单：
innodb_file_per_table = ON（8.0 默认），否则 DROP TABLE 空间不释放- 每张 InnoDB 表都显式定义主键，且尽量短（推荐
BIGINT UNSIGNED AUTO_INCREMENT） - 所有 UPDATE / DELETE 的 WHERE 条件必须能走索引，否则锁范围失控
- 定期检查是否有"漏网"的非 InnoDB 表：
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema') AND ENGINE <> 'InnoDB';
十三、小结
- MySQL 是插件式存储引擎架构，引擎是表级属性，8.0 默认 InnoDB
- 文件上：MyISAM 是
.MYD+.MYI（8.0 前还有.frm），InnoDB 是.ibd（数据与索引共存） - 最本质差异在索引结构：InnoDB 聚簇索引（叶子是整行），MyISAM 非聚簇（叶子是数据地址）
- 由此推出：InnoDB二级索引要回表、主键要短、必须有主键
- InnoDB 有事务、行锁、MVCC、崩溃自动恢复；MyISAM 只有表锁、无事务、崩溃要手工修
COUNT(*)无 WHERE 时 MyISAM 是 O(1)，但带 WHERE 一样慢；InnoDB 会自动挑最小的二级索引扫描- InnoDB 行锁基于索引，没走索引的 UPDATE 会锁住扫描过的所有记录——根因是缺索引
- MySQL 8.0 移除了
.frm、系统表全面 InnoDB 化、支持原子 DDL，MyISAM 已成为遗留兼容品 SHOW TABLE STATUS里 InnoDB 的 Rows 是估算值，别拿它做精确统计- 选型结论：默认 InnoDB；MyISAM 只在极窄的只读场景才值得作为一个被论证过的例外
下一篇进入 InnoDB 内部：redo log、undo log、binlog 这三大日志如何配合，
把"事务不丢数据"这件事真正落地。
