Appearance
MySQL 存储引擎
存储引擎是 MySQL 区别于其他数据库的核心特性。它决定了数据如何存储、如何索引、如何支持事务等。MySQL 支持插件式存储引擎架构，允许用户根据不同业务场景选择最合适的引擎。
1. 存储引擎概述
1.1 什么是存储引擎
存储引擎是 MySQL 中负责数据存储和提取的软件模块。不同的存储引擎提供不同的数据存储机制、索引技术和锁定水平。MySQL 服务层通过统一的 API 与存储引擎交互，使得不同引擎可以无缝切换。
1.2 查看支持的引擎
sql
-- 查看所有支持的存储引擎
SHOW ENGINES;
-- 查看当前默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';
-- 查看某张表的存储引擎
SHOW TABLE STATUS LIKE 'table_name';
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';2. InnoDB 存储引擎
InnoDB 是 MySQL 5.5.5 之后的默认存储引擎，也是生产环境中最常用的引擎。它提供了完整的事务支持（ACID）和高并发处理能力。
2.1 核心特性
聚集索引（Clustered Index）：
- 数据按主键顺序物理存储，主键索引的叶子节点存储整行数据。
- 如果没有显式定义主键，InnoDB 会选择第一个非空唯一索引作为聚集索引；如果都没有，则自动生成一个 6 字节的隐藏主键 ROW_ID。
事务支持（ACID）：
- 通过 redo log 保证持久性（Durability）。
- 通过 undo log 保证原子性（Atomicity）。
- 通过锁机制保证隔离性（Isolation）。
- 通过
NOT NULL、唯一约束等保证一致性（Consistency）。
MVCC（多版本并发控制）：
- 通过 undo log 保存数据的历史版本，实现非锁定一致性读。
- 读操作不阻塞写操作，写操作不阻塞读操作（读已提交和可重复读级别下）。
- 隐藏列：
DB_TRX_ID（最近修改的事务 ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（行 ID）。
行级锁（Row-Level Locking）：
- 支持行级共享锁（S Lock）和排他锁（X Lock）。
- 支持间隙锁（Gap Lock）和临键锁（Next-Key Lock），解决幻读问题。
- 通过索引实现行锁，如果 SQL 没有使用索引，会退化为表锁。
外键约束：
- 支持外键，保证数据参照完整性。
- 外键列必须建立索引。
2.2 适用场景
- 大多数 OLTP 业务场景（电商、社交、金融等）。
- 需要事务支持的应用。
- 高并发读写场景。
- 需要数据完整性和崩溃恢复能力。
2.3 关键参数
sql
-- 查看 InnoDB 相关参数
SHOW VARIABLES LIKE 'innodb%';
-- 关键参数
innodb_buffer_pool_size -- 缓冲池大小，建议物理内存的 50%-80%
innodb_flush_log_at_trx_commit -- redo log 刷盘策略，生产环境建议 1
innodb_file_per_table -- 独立表空间，建议 ON
innodb_log_file_size -- 单个 redo log 文件大小，建议 1-2G
innodb_log_files_in_group -- redo log 文件组数量，默认 2
innodb_io_capacity -- IO 吞吐量，SSD 建议 2000-50003. MyISAM 存储引擎
MyISAM 是 MySQL 5.5 之前的默认存储引擎。它不支持事务和行级锁，但在某些只读场景下仍有优势。
3.1 核心特性
表级锁：
- 读操作加共享锁（表级），写操作加排他锁（表级）。
- 读写互斥，写操作会阻塞所有读操作。
- 并发能力较差，不适合高并发写入场景。
不支持事务：
- 没有 COMMIT 和 ROLLBACK。
- 崩溃后数据可能损坏，需要手动修复。
全文索引：
- 原生支持全文索引（FULLTEXT），InnoDB 在 MySQL 5.6 之前不支持。
- 适用于文本搜索场景。
压缩表：
- 支持使用
myisampack工具压缩只读表。 - 压缩后表变为只读，空间占用大幅减少。
存储格式：
- 静态表（Fixed）：所有列定长，访问速度快，但占用空间大。
- 动态表（Dynamic）：包含变长列，占用空间小，但可能产生碎片。
- 压缩表（Compressed）：只读，由 myisampack 工具创建。
3.2 文件结构
| 文件类型 | 扩展名 | 内容 |
|---|---|---|
| 表定义文件 | .frm | 表结构定义 |
| 数据文件 | .MYD | 表数据 |
| 索引文件 | .MYI | 表索引 |
3.3 适用场景
- 只读或读多写少的场景（如数据仓库、日志分析）。
- 不需要事务支持的场景。
- 临时统计表、配置表。
- 全文搜索（MySQL 5.5 及以前）。
3.4 关键参数
sql
key_buffer_size -- 索引缓存大小，MyISAM 最重要的参数
table_open_cache -- 打开表的缓存数量
bulk_insert_buffer_size -- 批量插入缓存4. Memory 存储引擎
Memory 引擎（以前叫 HEAP）将所有数据存储在内存中，数据库重启后数据丢失。
4.1 核心特性
哈希索引：
- 默认使用哈希索引（HASH），等值查询非常快，O(1) 时间复杂度。
- 也可指定 BTREE 索引，支持范围查询。
表级锁：
- 与 MyISAM 一样使用表级锁，并发写入性能较差。
内存存储：
- 数据全部在内存中，访问速度极快。
- 使用
max_heap_table_size控制单表最大大小。 - 重启后数据丢失，表结构保留。
不支持 BLOB/TEXT：
- 不支持 BLOB 和 TEXT 列。
4.2 适用场景
- 临时表、会话级别的缓存。
- 查找表、映射表（如省份代码映射）。
- 快速计算的中间结果集。
4.3 关键参数
sql
max_heap_table_size -- 单表最大大小，默认 16MB
tmp_table_size -- 内存临时表最大大小，超过则转为磁盘临时表5. Archive 存储引擎
5.1 核心特性
高压缩比：
- 使用 zlib 压缩，压缩比可达 1:10 甚至更高。
- 插入时压缩，读取时解压。
仅支持 INSERT 和 SELECT：
- 不支持 UPDATE、DELETE、REPLACE。
- 适合日志归档、流水表。
不支持索引：
- 不支持索引（MySQL 5.5+ 支持自增列上的索引）。
- 查询需要全表扫描。
行级锁：
- 支持行级锁，INSERT 和 SELECT 可以并发执行。
5.2 适用场景
- 日志归档、审计日志。
- 流水记录、点击流数据。
- 需要长期存储但很少查询的数据。
6. 其他存储引擎
6.1 CSV 引擎
- 数据以 CSV 文件格式存储，可以直接用文本编辑器查看。
- 不支持索引，不支持 NULL。
- 适合数据交换场景。
6.2 Blackhole 引擎
- 写入的数据被丢弃，查询永远返回空。
- 常用于主从复制的中继、性能测试。
6.3 Federated 引擎
- 不存储数据，而是访问远程 MySQL 服务器上的表。
- 类似于 Oracle 的 DBLINK。
6.4 NDB（MySQL Cluster）
- 分布式存储引擎，支持数据分片和高可用。
- 数据全部在内存中，提供高吞吐和低延迟。
- 适用于电信级高可用场景。
6.5 Merge 引擎
- 将多个相同结构的 MyISAM 表合并为一个逻辑表。
- 适用于分区存储场景。
7. 存储引擎对比
| 特性 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 256TB | RAM |
| 事务 | 支持 | 不支持 | 不支持 |
| 锁粒度 | 行级锁 | 表级锁 | 表级锁 |
| MVCC | 支持 | 不支持 | 不支持 |
| 外键 | 支持 | 不支持 | 不支持 |
| 全文索引 | 支持（5.6+） | 支持 | 不支持 |
| 哈希索引 | 自适应哈希 | 不支持 | 支持 |
| B+Tree 索引 | 支持 | 支持 | 支持 |
| 聚集索引 | 支持 | 不支持 | 不支持 |
| 数据缓存 | Buffer Pool | 无（仅索引缓存） | 数据在内存 |
| 压缩 | 支持（5.7+） | 支持 | 不支持 |
| 地理空间索引 | 支持 | 支持 | 不支持 |
| 崩溃恢复 | 自动恢复 | 可能损坏 | 数据丢失 |
| 备份方式 | 热备 | 热备（需锁表） | 数据不持久 |
| 适用场景 | OLTP、高并发 | 只读、日志 | 临时表、缓存 |
8. 如何选择存储引擎
8.1 选择决策树
是否需要事务？
├── 是 → InnoDB（绝大多数场景）
└── 否 → 是否需要高并发写入？
├── 是 → InnoDB（MyISAM 表锁不适合）
└── 否 → 数据是否需要持久化？
├── 是 → 是否只读？
│ ├── 是 → MyISAM 或 Archive
│ └── 否 → InnoDB
└── 否 → Memory8.2 场景化推荐
| 场景 | 推荐引擎 | 原因 |
|---|---|---|
| 电商订单系统 | InnoDB | 事务、行锁、高并发 |
| 博客/论坛 | InnoDB | 事务、行锁、崩溃恢复 |
| 日志归档 | Archive | 高压缩比、仅追加 |
| 数据仓库（只读） | MyISAM | 批量加载快 |
| 全文搜索（旧版） | MyISAM | 原生全文索引 |
| 会话存储 | Memory | 内存存储，速度快 |
| 配置表 | Memory | 读多写少，数据量小 |
| 数据交换 | CSV | 通用格式 |
| 分布式集群 | NDB | 高可用、分片 |
8.3 选择原则
- 默认选择 InnoDB：绝大多数业务场景都适合 InnoDB。
- 事务是刚需：需要事务必须用 InnoDB。
- 高并发必须用 InnoDB：MyISAM 表锁不适合高并发写入。
- 归档用 Archive：日志归档场景，Archive 压缩比高，空间占用小。
- 临时数据用 Memory：临时表、缓存表，数据不需要持久化。
- 不要混用引擎：JOIN 操作涉及不同引擎的表，性能可能很差。
9. 存储引擎管理命令
9.1 查看引擎信息
sql
-- 查看所有引擎
SHOW ENGINES;
-- 查看某张表的引擎
SHOW TABLE STATUS FROM db_name LIKE 'table_name';
-- 从 information_schema 查看
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test';
-- 查看引擎相关变量
SHOW VARIABLES LIKE '%engine%';
SHOW VARIABLES LIKE 'innodb%';9.2 修改表引擎
sql
-- 方法一：ALTER TABLE（会锁表，重建表）
ALTER TABLE table_name ENGINE = InnoDB;
-- 方法二：mysqldump 导出再导入（适合大表）
-- mysqldump -u root -p db_name table_name > dump.sql
-- 修改 dump.sql 中 ENGINE=xxx
-- mysql -u root -p db_name < dump.sql
-- 方法三：创建新表，插入数据
CREATE TABLE new_table LIKE old_table;
ALTER TABLE new_table ENGINE = InnoDB;
INSERT INTO new_table SELECT * FROM old_table;
RENAME TABLE old_table TO old_table_bak, new_table TO old_table;9.3 设置默认引擎
sql
-- 会话级别
SET default_storage_engine = InnoDB;
-- 全局级别
SET GLOBAL default_storage_engine = InnoDB;
-- 永久生效（my.cnf）
-- [mysqld]
-- default_storage_engine = InnoDB9.4 引擎维护
sql
-- 检查表
CHECK TABLE table_name;
-- 修复表（MyISAM）
REPAIR TABLE table_name;
-- 分析表（更新统计信息）
ANALYZE TABLE table_name;
-- 优化表（碎片整理）
OPTIMIZE TABLE table_name;
-- 查看表碎片
SELECT TABLE_NAME, DATA_FREE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name' AND DATA_FREE > 0;10. 总结
- InnoDB 是生产环境首选引擎，支持事务、行级锁、MVCC、崩溃恢复。
- MyISAM 适合只读或读多写少的场景，不支持事务，使用表级锁。
- Memory 引擎将数据存储在内存中，适合临时表，重启后数据丢失。
- Archive 引擎压缩比高，适合日志归档，仅支持 INSERT 和 SELECT。
- 选择引擎应基于业务需求：事务、并发、持久化、压缩等。
- 可通过
ALTER TABLE在线切换引擎，但大表建议用重建方式。