一、存储引擎与"可插拔"架构
1.1 为什么说存储引擎是 MySQL 的核心
- MySQL 架构中，SQL 层（连接管理、解析、优化、缓存）与存储层解耦；
- 存储引擎决定了数据如何存储、如何索引、是否事务、如何加锁——同一张表的建表语法可以完全一样，但换引擎后行为天差地别；
- 这就是"可插拔"（Pluggable）的含义：引擎以插件形式接入 MySQL 的存储层接口。
1.2 "可插拔" ≠ 运行时随便卸载
| 引擎类型 | 是否能动态加载/卸载 |
|---|---|
| 内置引擎（InnoDB、MyISAM 等，编译进服务器） | 不能卸载，只能通过启动参数禁用（如 --skip-archive） |
| 第三方插件引擎（MyRocks、TokuDB 等，编译成 .so） | 可通过 INSTALL PLUGIN / UNINSTALL PLUGIN 动态加载/卸载 |
所以准确说法是：MySQL 提供了插件式的引擎接口，第三方引擎可以动态挂载；但官方内置引擎是编译内建的。
1.3 SHOW ENGINES 输出解读
Support 列的含义：
| 取值 | 含义 |
|---|---|
YES | 引擎已编译进服务器，且当前可用 |
DEFAULT | 已编译且是默认引擎（你的输出中 InnoDB 为 DEFAULT） |
NO | 未编译进该发行版，不可用（官方二进制默认不含 FEDERATED，所以它是 NO） |
DISABLED | 已编译，但被启动参数禁用（如 --skip-xxx） |
加粗要点：
NO不是"配置文件里没开"，而是"这个版本根本没编译它"；Transactions: NULL也表明引擎不可用。
1.4 常用查看命令
-- 查看默认引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- 查看某张表的引擎与行格式
SHOW TABLE STATUS LIKE 't1'\G
-- 查看支持的引擎
SHOW ENGINES;
二、InnoDB 存储引擎（默认引擎）
2.1 核心特性总表
| 特性维度 | InnoDB |
|---|---|
| 默认引擎 | ✅ MySQL 5.5 起为默认 |
| 事务 | ✅ 支持（ACID，最大特点） |
| 锁粒度 | 行级锁（S 共享 / X 排他） |
| MVCC | ✅ 支持（undo log 实现） |
| 索引类型 | B+Tree；聚簇索引（主键）；8.0 支持全文索引、空间索引 |
| HASH 索引 | ❌ 不支持用户显式创建；✅ 内部有自适应哈希索引（AHI） |
| 外键 | ✅ 支持 |
| 崩溃恢复 | ✅ redo + undo，自动恢复 |
| 主从复制 | ✅ 基于 binlog |
| 数据缓冲 | ✅ Buffer Pool 统一缓存数据页 + 索引页 |
| 表压缩 | ✅ 压缩行格式/页压缩，减少磁盘与内存占用 |
| 静态加密 | ✅ 表空间加密 |
| 单表大小上限 | 约 64TB（16KB 页） |
| 备份支持 | mysqldump / XtraBackup / binlog 定点恢复（PITR） |
2.2 逐项详解与答疑
2.2.1 聚簇索引
聚簇索引 = 索引与数据行存放在一起。主键索引的叶子节点直接存放整行数据；二级索引的叶子节点存放主键值（回表时再查主键索引）。所以：
- 一张 InnoDB 表只有一个聚簇索引（主键）；
- 没建主键时，MySQL 选第一个非空唯一索引，再没有就用隐藏的 rowid。
2.2.2 表压缩：磁盘和内存都省
- 磁盘：COMPRESSED 行格式 / 页压缩（8.0 支持
COMPRESSION建表选项）显著减少磁盘占用； - 内存：压缩页在 Buffer Pool 中保持压缩状态，也间接减少内存占用。
2.2.3 数据缓冲：局部性 + 预读
Buffer Pool 依据局部性原理：访问某页时，把相邻页预读进内存，热点数据常驻内存，减少磁盘 IO。这就是 InnoDB 快的关键之一。
2.2.4 InnoDB 的"索引缓存"是什么？
| 引擎 | 索引缓存 | 数据缓存 |
|---|---|---|
| InnoDB | ❌ 无独立索引缓存 | ✅ Buffer Pool 统一缓存数据页+索引页 |
| MyISAM | ✅ 独立 Key Cache（key_buffer_size） | ❌ 无数据缓存（靠 OS 文件缓存） |
结论：InnoDB 没有单独的"索引缓存"，数据页和索引页都放在 Buffer Pool 里统一管理；"索引缓存"（Key Cache）是 MyISAM 的专属特性。
2.2.5 行级锁
行锁分两种，语义完全不同：
| 锁类型 | 缩写 | 其他事务能否读 | 其他事务能否写 |
|---|---|---|---|
| 共享锁 | S | ✅ 可读 | ❌ 阻塞 |
| 排他锁 | X | ❌ 阻塞 | ❌ 阻塞 |
- 读操作加 S 锁，写操作加 X 锁；
- 行锁基于索引记录实现（锁的是索引项，不是物理行）；
- 行锁并发高，但有死锁风险、加锁开销更大；表锁实现简单、无死锁，但并发低。生产 OLTP 首选 InnoDB 行锁。
2.2.6 答疑：不支持 HASH 索引，但"内部支持"是怎么回事？
两层含义：
- 用户层面：InnoDB 不允许你显式
USING HASH建索引 - 内部层面：InnoDB 有自适应哈希索引（Adaptive Hash Index，AHI）——它自动在频繁等值访问的索引页上建立内存哈希索引，把 B+Tree 的多次 IO 降到 O(1) 查找。由
innodb_adaptive_hash_index控制（默认开启）。
技巧：模拟哈希索引——对很长的列（如 url），额外加一列存哈希值（如 CRC32），对该列建普通索引，等值查询走哈希列索引，避免长列索引膨胀。
2.2.7 T 树索引是什么？
T-Tree（T 树）是内存数据库（Oracle TimesTen、早期 SAP HANA 等）使用的索引结构：
- 一个节点存多个 key，减少内存指针开销；
- 针对 CPU 缓存、内存访问优化；
- InnoDB 是磁盘数据库，页大小对齐磁盘块（16KB），用 B+Tree 能把一次查询控制在少数几次磁盘 IO，所以不用 T-Tree。
一句话：T-Tree 属于内存数据库的索引方案，和 InnoDB 的 B+Tree 不是同一赛道。
2.2.8 update statistics for data dictionary
这不是一条日常 SQL 命令。优化器依赖统计信息（行数、列基数）选择执行计划，更新统计信息的标准方式是：
ANALYZE TABLE t1;
- 8.0 中统计信息持久化存储在数据字典里（
innodb_stats_persistent默认 ON，采样页数innodb_stats_persistent_sample_pages默认 20）； - 你看到的"update statistics for data dictionary"可理解为：InnoDB 支持维护数据字典相关的统计信息，属于内部机制，运维层面用
ANALYZE TABLE即可。
2.2.9 MVCC 与主从复制
- MVCC：靠 undo log + 版本链实现，读不阻塞写、写不阻塞读，是事务隔离的底层机制（详见事务篇）；
- 主从复制：通过 binlog 传递给从库。
2.3 物理文件
| 版本 | 表结构 | 数据 + 索引 |
|---|---|---|
| 5.7 及以前 | .frm 文件 | .ibd 文件 |
| 8.0 | 数据字典集中存储（mysql.ibd，不再有 .frm） | .ibd 文件（innodb_file_per_table=ON 默认） |
加粗要点：8.0 建表后，数据 + 索引在 .ibd，表结构在数据字典里
三、MyISAM 存储引擎
3.1 核心特性总表
| 特性维度 | MyISAM |
|---|---|
| 默认引擎 | ❌ 5.5 以前是默认，5.5 起让位 InnoDB |
| 事务 | ❌ 不支持 |
| 锁粒度 | 表级锁 |
| MVCC | ❌ 不支持 |
| 索引类型 | B+Tree、全文索引（FULLTEXT）；❌ 无聚簇索引（数据与索引分离） |
| HASH 索引 | ❌ 不支持 |
| 外键 | ❌ 不支持 |
| 索引缓存 | ✅ Key Cache（key_buffer_size），专门缓存索引块 |
| 数据缓存 | ❌ 无（数据靠 OS 文件缓存） |
| 崩溃恢复 | 弱（崩溃后可能损坏，需 repair） |
| 单表大小上限 | 约 256TB（取决于文件系统） |
3.2 物理文件
| 文件 | 内容 |
|---|---|
.MYD | 数据文件 |
.MYI | 索引文件 |
| 表结构 | 5.7 前是 .frm；8.0 起存数据字典（.sdi 是表空间描述，JSON） |
加粗要点：MyISAM = 数据（.MYD）与索引（.MYI）分离存储，这正是它没有聚簇索引、二级索引叶子直接存行指针（而非主键）的原因。
3.3 三种存储格式
3.3.1 静态格式（Fixed）
- 条件：表中不含可变长度列（VARCHAR/TEXT/BLOB 等）时自动使用，每行字节数固定；
- 特点：最简单、最安全、最快；行位置可精确计算，天然支持随机访问；
- NULL 列用 1 bit 额外空间记录是否为空。
答疑：VARCHAR 不是用多少给多少吗，为什么静态表要空格填充？
平时 InnoDB 里 VARCHAR 按实际长度存储，没错。但 MyISAM 静态格式为了保证"每行定长"，会把 VARCHAR 也按声明最大宽度分配空间，尾部用空格填充（读取时一般会去掉）。这是 MyISAM 静态格式的历史设计——用空间换"行定长"带来的随机访问速度。BINARY/VARBINARY 则用
0x00填充。
3.3.2 动态格式（Dynamic）
- 条件：表含可变长度列（VARCHAR/TEXT/BLOB），或显式指定；
- 特点：行长度可变，每行带长度标志；更新使行变长时，可能被移动到不连续空间，产生碎片；
- 碎片整理：
OPTIMIZE TABLE t1或myisamchk -r； - 每行前有 bitmap，标记哪些字符串列为空串/数字列为 0——值为 0/空串的列不占磁盘，节省空间；
- 缺点：行分散 → 随机 IO 增多（你感觉"IO 次数很多"是对的），整体比静态格式慢。
3.3.3 压缩格式（Compressed）
- 工具：
myisampack生成，只读表，内存占用极少，每行单独压缩、每列可选用不同压缩算法； - 解压/恢复：可用
myisamchk处理（官方提示：压缩表只读，如需修改要先解压/重建）。
3.4 InnoDB vs MyISAM 一句话对比
| 对比项 | InnoDB | MyISAM |
|---|---|---|
| 事务/MVCC/外键/崩溃恢复 | ✅ | ❌ |
| 锁 | 行锁（并发高） | 表锁（并发低） |
| 聚簇索引 | ✅ | ❌ |
| 缓存 | Buffer Pool（数据+索引） | 仅 Key Cache（索引） |
| 适用 | OLTP 读写混合 | 只读/读多写极少、全文检索场景 |
四、MEMORY 存储引擎
4.1 核心特性总表
| 特性维度 | MEMORY |
|---|---|
| 数据存储 | 内存中（表结构在数据字典），重启丢失 |
| 索引 | ✅ HASH 索引（默认），可指定 BTREE |
| 事务 | ❌ |
| 锁 | 表级锁 |
| 持久化 | ❌（仅结构定义保留） |
| 大小上限 | 受 max_heap_table_size 限制 |
| 适用 | 临时数据、缓存、会话级中间结果 |
4.2 建表与释放
-- 用查询结果直接建内存表
CREATE TABLE tmp_mem ENGINE=MEMORY SELECT * FROM t1;
-- 释放内存
DELETE FROM tmp_mem; -- 删除所有行，释放内存
TRUNCATE TABLE tmp_mem; -- 整表清空，释放更快
DROP TABLE tmp_mem; -- 彻底删除
注意：删除单行不会立即回收内存（碎片），只有 DELETE 全部行 / TRUNCATE / DROP 才整块释放。
4.3 三个问题
4.3.1 数据字典是什么？
数据字典（Data Dictionary）= MySQL 的元数据仓库：所有表结构、列、索引、权限、对象定义等集中存放。
- 5.7 及以前：每张表一个
.frm文件，分散管理； - 8.0：全部元数据集中存进 InnoDB 的
mysql.ibd（mysql库），DDL 具备原子性（要么成功要么回滚，不再有"改一半"状态）； .sdi文件：8.0 中伴随部分表空间出现的 JSON 格式表定义描述，用于部分场景（如ibd2sdi工具读取），不是日常运维对象。
4.3.2 和视图像吗？
本质不同：
| 维度 | MEMORY 表 | 视图 |
|---|---|---|
| 数据 | 真实物理存储（内存中） | 不存数据，查询时动态执行 SELECT |
| 可写 | ✅ 可增删改 | ❌ 基本只读（可更新视图限制极多） |
| 独立性 | 独立数据 | 依赖基表，基表变则结果变 |
视图是逻辑对象，内存表是物理数据。
4.3.3 被 Redis 替代了吗？
场景重叠，但没有完全替代：
- Redis：独立进程、网络协议、丰富数据结构、可持久化——适合跨应用共享缓存；
- MEMORY 表：MySQL 进程内，同一数据库会话/服务器内使用，事务内可见，SQL 直接操作；
- 关键保留场景：MySQL 内部临时表（排序、分组、
CREATE TEMP TABLE）大量使用内存表机制（8.0 起内部临时表默认用 TempTable 引擎，超出temptable_max_ram落盘）。
结论：对外缓存选 Redis；数据库内部临时存储用 MEMORY/TempTable，二者不冲突。
五、CSV 存储引擎
5.1 特性总表与文件
| 维度 | CSV |
|---|---|
| 存储形式 | 纯文本，逗号分隔（Comma-Separated Values） |
| 索引 | ❌ 不支持 |
| 事务/锁 | ❌ 不支持事务；写时锁整表 |
| NULL 支持 | ❌ 不支持（见 5.2） |
| 适用 | 与其他系统交换数据（可直接用文本编辑器/Excel 打开） |
建表后生成三个文件：
| 文件 | 内容 |
|---|---|
.CSV | 数据（纯文本，逗号分隔） |
.CSM | 表状态、元数据（行数、列信息） |
.sdi | 表结构描述（JSON，8.0） |
5.2 为什么"所有列都要求 NOT NULL"？
因为 CSV 是纯文本格式，无法表达 NULL 语义——空字段就是空字符串 ''，与 NULL 无法区分。所以 CSV 引擎不支持 NULL 列（创建时建议显式 NOT NULL）。
5.3 注意点
- 不支持索引、不支持 AUTO_INCREMENT、不支持分区；
- 增删改会重写整个文件（IO 开销大），不适合高频写；
- 典型用途：导入导出、与外部程序交换数据（如读 Excel 导出的 CSV）。
六、ARCHIVE 存储引擎
6.1 特性总表
| 维度 | ARCHIVE |
|---|---|
| 用途 | 归档存储：大量只写不查（或极少查）的历史数据 |
| 压缩 | ✅ zlib 压缩，磁盘占用极小 |
| 操作 | 主要支持 INSERT / SELECT；UPDATE/DELETE 受限或不可用（以当前版本官方文档为准） |
| 索引 | 支持极其有限（实践中基本无索引） |
| 事务 | ❌ |
| 适用 | 日志归档、审计记录、历史明细 |
6.2 文件
| 文件 | 内容 |
|---|---|
.ARZ | 压缩后的数据文件 |
.sdi | 表结构描述（JSON，8.0） |
.ARN | OPTIMIZE 优化期间可能出现的临时文件 |
注意：ARCHIVE 表数据只能追加，天然适合"写一次、存永久"的归档场景；不适合需要更新/删除的业务。
七、BLACKHOLE 存储引擎
7.1 特性
- 接收一切写操作，但什么都不存；查询永远返回空结果；
- 表结构定义正常存在，数据直接"丢进黑洞"。
7.2 用途
- 验证转储文件语法：把数据导入黑洞表，只验证 SQL 正确性；
- 测试二进制日志记录开销：对比开/关 binlog 的性能差异；
- 查找与存储引擎本身无关的性能瓶颈；
- 复制中间层：主库写 BLACKHOLE 表 + 开启 binlog → binlog 正常记录 → 从库收到并执行。可作为分发节点：多个从库共享一个写入入口，又不占主库磁盘。
八、MERGE（MRG_MYISAM）存储引擎
8.1 是什么
MERGE = 多个结构完全相同的 MyISAM 表的逻辑集合：
- 对外像一张表，查询时并行扫描所有成员表再合并结果；
- 建表方式：
ENGINE=MRG_MYISAM UNION=(t1,t2)。
8.2 典型场景
- 分表：把按月分出的
log_202501、log_202502… 合并成一张逻辑表查询； - 归档 + 查询：历史表只读归档，通过 MERGE 统一查询。
8.3 注意点
- 成员表必须同为 MyISAM、结构完全一致；
- 删除 MERGE 表不会删除成员表（成员独立存在）；
- 成员表缺一个/结构不一致都会导致 MERGE 表不可用；
- 现代分表更推荐 InnoDB 分区表 或 ShardingSphere 等中间件，MERGE 已属历史方案。
九、FEDERATED 存储引擎
9.1 是什么
FEDERATED 允许本机表映射到远程 MySQL 服务器上的表：
- 本地只存表结构定义，数据实际在远程；
- 类似"跨库/分布式访问"的最小实现（MySQL 版 dblink）。
9.2 注意点
- 官方发行版默认不编译（
SHOW ENGINES里 Support=NO），需要自行编译； - 性能差：每条查询都走远程网络往返，无法本地优化；
- 安全/权限/网络稳定性问题多；
- 生产慎用，跨库需求优先考虑应用层拆分或中间件。
十、EXAMPLE 存储引擎
- 用途：存储引擎 API 的示例模板，给开发者演示"如何写一个存储引擎插件"；
- 特性：不真正存储任何数据（只有框架逻辑）；
- 不是给你生产用的，是给写引擎的人看的。
十一、全引擎横向对比与选择建议
11.1 横向对比大表
| 引擎 | 事务 | 锁 | 索引 | 外键 | MVCC | 持久化 | 压缩 | 典型场景 |
|---|---|---|---|---|---|---|---|---|
| InnoDB | ✅ | 行 | B+Tree/聚簇/全文/空间 | ✅ | ✅ | ✅ | ✅ | OLTP 生产默认 |
| MyISAM | ❌ | 表 | B+Tree/全文，无聚簇 | ❌ | ❌ | ✅ | ✅(工具) | 只读、全文检索 |
| MEMORY | ❌ | 表 | HASH/BTREE | ❌ | ❌ | ❌ | ❌ | 临时表/缓存 |
| CSV | ❌ | 表 | ❌ | ❌ | ❌ | ✅(文本) | ❌ | 数据交换 |
| ARCHIVE | ❌ | — | 极有限 | ❌ | ❌ | ✅ | ✅ | 归档 |
| BLACKHOLE | ❌ | — | ❌ | ❌ | ❌ | ❌ | ❌ | 复制中间层/测试 |
| MERGE | ❌ | 表 | 同 MyISAM | ❌ | ❌ | ✅ | — | MyISAM 分表查询 |
| FEDERATED | 跟随远程 | 远程 | 远程 | 远程 | 远程 | 远程 | — | 跨库访问（慎用） |
| EXAMPLE | — | — | — | — | — | — | — | 引擎开发模板 |
11.2 选择建议（决策口诀）
| 业务场景 | 推荐引擎 |
|---|---|
| 日常 OLTP（读写混合、要事务） | InnoDB |
| 只读报表、全文搜索 | MyISAM |
| 会话级临时数据 | MEMORY |
| 跨系统导入导出 | CSV |
| 历史归档、只追加 | ARCHIVE |
| 复制分发、性能测试 | BLACKHOLE |
转载自 CSDN-专业IT技术社区
