01.3 - 存储引擎对比

定位：理解不同存储引擎的设计取舍——为什么 InnoDB 是默认引擎？什么场景需要别的引擎？ 面试高频度：⭐⭐⭐⭐ 考查方式：InnoDB vs MyISAM 对比、引擎选型场景题

一、这是什么？为什么需要它？

MySQL 的可插拔存储引擎架构意味着同一个数据库可以同时使用不同引擎的表。选择哪个引擎，取决于业务对事务、并发、查询类型、写入模式的需求。

为什么需要多个引擎？

没有"万能"的存储引擎——不同业务场景对数据库的要求截然不同：

场景	需求	适合引擎
银行转账	事务 ACID 保证	InnoDB
日志归档	高吞吐写入、不常读	MyISAM (或 Archive)
临时表缓存	数据重启可丢失、速度要快	Memory
大数据分析	列存、压缩比高	ColumnStore (扩展)

核心 insight：引擎选型是权衡的艺术——每增加一个特性（事务、行锁、崩溃恢复）都带来性能开销。理解 InnoDB 为什么成为默认引擎，就理解了 OLTP 场景的核心需求。

二、原理拆解

2.1 InnoDB vs MyISAM 全方位对比

对比维度	InnoDB	MyISAM	为何有这个差异
事务支持	✅ ACID 事务	❌ 不支持	MyISAM 设计于 90 年代，那时 Web 应用还不需要事务
锁粒度	行锁（Row Lock）	表锁（Table Lock）	行锁需要更多内存管理锁信息，但并发高
外键	✅ 支持外键约束	❌ 不支持	外键检查有性能开销
MVCC	✅ 支持	❌ 不支持	MVCC 需要 Undo Log，MyISAM 没有
崩溃恢复	✅ Redo Log + Double Write	❌ 需要 `repair table`	MyISAM 写入时没有 WAL，崩溃后可能完全损坏
聚集索引	✅ 数据按主键物理排序	❌ 数据和索引分离	聚簇索引让主键查询快 30%+
数据压缩	❌ 支持压缩（Barracuda）	✅ 支持压缩	压缩以 CPU 换空间
全文索引	✅（5.6+）	✅	InnoDB 5.6 才加入
磁盘占用	较大（有 Redo Log、Undo Log）	较小（无事务日志）	事务保证的代价
内存占用	较大（Buffer Pool 占大头）	较小（只有 Key Cache）	Buffer Pool 提升性能

2.2 数据文件结构对比

InnoDB：

/var/lib/mysql/
├── ibdata1              ← 系统表空间（数据字典、Double Write）
├── ib_logfile0          ← Redo Log 文件
├── ib_logfile1
├── mysql/               ← 系统表
├── mydb/
│   ├── users.ibd        ← 独立表空间（数据+索引）
│   └── orders.ibd
└── undo_001             ← Undo Log（8.0 独立文件）

MyISAM：

/var/lib/mysql/mydb/
├── users.frm          ← 表结构定义（8.0 前，8.0 后在数据字典中）
├── users.MYD          ← 数据文件 (MYData)
└── users.MYI          ← 索引文件 (MYIndex)

2.3 其他常用存储引擎

引擎	特点	适用场景
Memory	全部数据在内存，重启丢失	临时表、缓存表、会话中间结果
Archive	只支持 INSERT + SELECT，zlib 压缩	日志归档、审计数据
CSV	数据就是 CSV 文件	数据交换、导出导入
Blackhole	不存数据，只记录 Binlog	复制分发、审计
NDB (MySQL Cluster)	内存集群，高可用	电信、实时系统

2.4 引擎选型决策树

应用场景？
    │
├── 需要事务 ACID？──→ 必须 InnoDB
│
├── 只读、大量 SELECT？──→ 可能适合 MyISAM（但 8.0 已不支持）
│                       ↓
│               实际推荐仍然 InnoDB——MyISAM 在 8.0 已不可用
│               InnoDB 只读性能经过优化，不比 MyISAM 差
│
├── 临时数据，可丢失？──→ Memory 引擎
│
├── 日志/时序数据？──→ Archive 引擎（高压缩比）
│
└── 数据做 ETL/交换？──→ CSV 引擎

现状：MySQL 8.0 已移除 MyISAM 系统表。系统表全部改用 InnoDB。第三方存储引擎（如 TokuDB、MyRocks）也通过插件方式支持。

三、图解全景

      MySQL 存储引擎生态

┌─────────────────────────────────────┐
│          存储引擎 (Plugin)           │
│                                     │
│  ┌───────────┐  ┌───────────────┐  │
│  │  InnoDB   │  │    MyISAM     │  │
│  │  (默认)    │  │  (8.0 弃用)   │  │
│  ├───────────┤  ├───────────────┤  │
│  │  行锁      │  │  表锁         │  │
│  │  事务      │  │  无事务        │  │
│  │  事务:ACID  │  │  完整性:差    │  │
│  │  恢复:Redo │  │  恢复:repair  │  │
│  └───────────┘  └───────────────┘  │
│                                     │
│  ┌───────────┐  ┌───────────────┐  │
│  │  Memory   │  │   Archive     │  │
│  ├───────────┤  ├───────────────┤  │
│  │  全部内存  │  │  zlib 压缩    │  │
│  │  重启丢失  │  │  只 INSERT    │  │
│  │  哈希索引  │  │  + SELECT     │  │
│  └───────────┘  └───────────────┘  │
│                                     │
│  ┌───────────┐  ┌───────────────┐  │
│  │    NDB    │  │  第三方引擎    │  │
│  │ (集群引擎) │  │ TokuDB/       │  │
│  │  高可用   │  │ MyRocks/...  │  │
│  └───────────┘  └───────────────┘  │
└─────────────────────────────────────┘

四、实战验证

查看和切换引擎

sql

-- 查看表使用的引擎
SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database';

-- 建表指定引擎
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENGINE=InnoDB;

-- 修改现表的引擎（⚠️ 会重建表，大表谨慎）
ALTER TABLE my_table ENGINE=InnoDB;

引擎性能对比测试

sql

-- 创建对比表
CREATE TABLE test_innodb (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE test_myisam (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data VARCHAR(100)
) ENGINE=MyISAM;

-- 批量写入对比
DROP PROCEDURE IF EXISTS insert_test;
DELIMITER $$
CREATE PROCEDURE insert_test(tbl_name VARCHAR(50), cnt INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET @sql = CONCAT('INSERT INTO ', tbl_name, '(data) VALUES (?)');
  PREPARE stmt FROM @sql;
  WHILE i < cnt DO
    SET @data = CONCAT('row-', i);
    EXECUTE stmt USING @data;
    SET i = i + 1;
  END WHILE;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 测试写入 10 万行
-- InnoDB:
CALL insert_test('test_innodb', 100000);

-- MyISAM (如果可用):
CALL insert_test('test_myisam', 100000);

预期结果：InnoDB 写入会慢一些（事务日志 + 行锁管理），但并发安全。

检查表碎片

sql

-- 查看表的碎片情况（InnoDB 特有）
SELECT 
  TABLE_NAME,
  DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE       -- 碎片空间
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' 
  AND ENGINE = 'InnoDB'
ORDER BY DATA_FREE DESC;

五、面试视角

追问	答案要点
InnoDB 和 MyISAM 的核心区别？	InnoDB 支持事务+行锁+崩溃恢复，MyISAM 不支持；InnoDB 是聚簇索引，MyISAM 非聚簇
为什么不直接用 MyISAM，它更快？	MyISAM 的"快"是无事务无锁管理的快，但不安全。崩溃后可能全表损坏
什么场景 MyISAM 仍有优势？	8.0 前：只读报表、数据仓库（压缩）。8.0 后：已不可用
Memory 引擎有什么坑？	重启数据全丢、表锁、不支持 TEXT/BLOB、行长度有限、哈希索引只支持等值查询
如何选择存储引擎？	95% 场景用 InnoDB。特殊场景：归档用 Archive，缓存用 Memory，集群用 NDB
为什么 8.0 取消 MyISAM 系统表？	系统表需要事务保证（如 DDL 原子性），InnoDB 更可靠

📚 相关链接

InnoDB 深入：**InnoDB架构**
上层架构：**MySQL架构总览**
索引深入：**索引与查询优化**
← 返回 **架构与存储引擎索引**

架构与存储引擎

索引与查询优化

SQL与事务

锁机制

日志系统

高可用与架构

性能调优

01.3 - 存储引擎对比

一、这是什么？为什么需要它？

二、原理拆解

2.1 InnoDB vs MyISAM 全方位对比

2.2 数据文件结构对比

2.3 其他常用存储引擎

2.4 引擎选型决策树

三、图解全景

四、实战验证

查看和切换引擎

引擎性能对比测试

检查表碎片

五、面试视角

📚 相关链接

01.3 - 存储引擎对比 ​

一、这是什么？为什么需要它？ ​

二、原理拆解 ​

2.1 InnoDB vs MyISAM 全方位对比 ​

2.2 数据文件结构对比 ​

2.3 其他常用存储引擎 ​

2.4 引擎选型决策树 ​

三、图解全景 ​

四、实战验证 ​

查看和切换引擎 ​

引擎性能对比测试 ​

检查表碎片 ​

五、面试视角 ​

📚 相关链接 ​

01.3 - 存储引擎对比

一、这是什么？为什么需要它？

二、原理拆解

2.1 InnoDB vs MyISAM 全方位对比

2.2 数据文件结构对比

2.3 其他常用存储引擎

2.4 引擎选型决策树

三、图解全景

四、实战验证

查看和切换引擎

引擎性能对比测试

检查表碎片

五、面试视角

📚 相关链接