MySQL 存储引擎

存储引擎是 MySQL 最核心的组件之一，决定了数据如何存储、索引如何构建、事务如何处理。理解存储引擎的工作原理，对于数据库设计、性能调优和故障排查都至关重要。本章将深入讲解 MySQL 各种存储引擎的特点、原理和应用场景。

什么是存储引擎？

存储引擎是 MySQL 体系架构中的底层组件，负责数据的物理存储和提取。MySQL 采用插件式存储引擎架构，这意味着你可以根据应用需求为不同的表选择不同的存储引擎。

存储引擎的职责

存储引擎主要承担以下职责：

数据存储：决定数据在磁盘上的物理组织方式
索引管理：构建和维护索引结构
事务处理：实现事务的 ACID 特性
锁机制：控制并发访问
崩溃恢复：保证数据持久性和一致性

为什么需要多种存储引擎？

不同的应用场景对数据库有不同的需求：

应用场景	核心需求	适合的引擎
电商交易系统	事务支持、数据一致性	InnoDB
日志分析系统	高速写入、压缩存储	MyISAM/Archive
临时缓存	极速访问、无需持久化	Memory
数据归档	高压缩比、低存储成本	Archive

MySQL 的插件式架构允许在同一数据库中为不同表选择不同引擎，这种灵活性是 MySQL 被广泛应用的重要原因之一。

查看存储引擎

查看支持的存储引擎

-- 查看所有支持的存储引擎
SHOW ENGINES;

输出示例：

+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                      | Transactions | XA   | Savepoints |
+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys   | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                        | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables    | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO         | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                        | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                           | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                       | NO           | NO   | NO         |
+--------------------+---------+--------------------------------------------------------------+--------------+------+------------+

关键字段说明：

Support：DEFAULT 表示默认引擎，YES 表示支持，NO 表示不支持
Transactions：是否支持事务
XA：是否支持分布式事务
Savepoints：是否支持保存点

查看表的存储引擎

-- 方式一：查看表状态
SHOW TABLE STATUS LIKE 'users'\G

-- 方式二：查看建表语句
SHOW CREATE TABLE users;

-- 方式三：查询信息模式
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME;

查看数据库中所有表的引擎

SELECT 
    TABLE_NAME AS '表名',
    ENGINE AS '存储引擎',
    TABLE_ROWS AS '行数',
    ROUND(DATA_LENGTH/1024/1024, 2) AS '数据大小(MB)',
    ROUND(INDEX_LENGTH/1024/1024, 2) AS '索引大小(MB)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE';

InnoDB 存储引擎

InnoDB 是 MySQL 5.5 之后的默认存储引擎，也是最通用、最强大的存储引擎。它完全支持 ACID 事务、行级锁定、外键约束，是大多数生产环境的首选。

核心特性

特性	说明	实际意义
事务支持	完整的 ACID 特性	保证数据一致性，支持复杂业务逻辑
行级锁定	只锁定被修改的行	高并发场景下性能优异
外键约束	支持外键关系	数据库层面保证引用完整性
崩溃恢复	自动恢复机制	断电、崩溃后数据不丢失
MVCC	多版本并发控制	读写互不阻塞，高并发性能
聚簇索引	数据按主键组织	主键查询极快
缓冲池	内存缓存数据和索引	减少磁盘 I/O

InnoDB 架构详解

InnoDB 的架构分为内存结构和磁盘结构两大部分：

┌─────────────────────────────────────────────────────────────────────────────┐
│                           InnoDB 架构                                        │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                        内存结构 (In-Memory)                          │   │
│  │  ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌────────────┐ │   │
│  │  │ Buffer Pool  │ │Change Buffer │ │ Adaptive Hash│ │Log Buffer  │ │   │
│  │  │  缓冲池       │ │  变更缓冲     │ │   Index      │ │ 日志缓冲   │ │   │
│  │  │              │ │              │ │  自适应哈希   │ │            │ │   │
│  │  │ 数据页+索引页 │ │ 二级索引变更  │ │    索引      │ │ 重做日志   │ │   │
│  │  └──────────────┘ └──────────────┘ └──────────────┘ └────────────┘ │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                    │                                        │
│                                    ▼                                        │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                        磁盘结构 (On-Disk)                            │   │
│  │  ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌────────────┐ │   │
│  │  │  System      │ │ File-Per-Table│ │   Redo Log   │ │ Undo Log   │ │   │
│  │  │  Tablespace  │ │  Tablespace   │ │   重做日志    │ │ 撤销日志   │ │   │
│  │  │  系统表空间   │ │  独立表空间   │ │              │ │            │ │   │
│  │  └──────────────┘ └──────────────┘ └──────────────┘ └────────────┘ │   │
│  │  ┌──────────────┐ ┌──────────────┐ ┌──────────────┐                 │   │
│  │  │ Doublewrite  │ │ Undo         │ │  Temporary   │                 │   │
│  │  │ Buffer       │ │ Tablespace   │ │  Tablespace  │                 │   │
│  │  │ 双写缓冲      │ │ 撤销表空间    │ │  临时表空间   │                 │   │
│  │  └──────────────┘ └──────────────┘ └──────────────┘                 │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Buffer Pool（缓冲池）

Buffer Pool 是 InnoDB 最重要的内存结构，也是性能调优的关键。它缓存表数据和索引数据，让频繁访问的数据可以直接从内存读取。

Buffer Pool 的工作原理

Buffer Pool 使用改进的 LRU（最近最少使用）算法管理数据页：

Buffer Pool LRU 列表结构：

┌────────────────────────────────────────────────────────────────┐
│                        LRU 列表                                 │
│                                                                │
│  ┌────── 新子列表（Young） ─────────────┐┌─── 旧子列表（Old）─┐│
│  │                                      ││                    ││
│  │  Head ─→ [页1]─→ [页2]─→ ... ─→ [页N]││→ [页N+1]─→ ... ─→ ││
│  │    ↑                                 ││                    ││
│  │   最近访问的页                        ││  较少访问的页       ││
│  │   (5/8 的空间)                       ││  (3/8 的空间)       ││
│  └──────────────────────────────────────┘└────────────────────┘│
│                                                                │
│                                           ↑                    │
│                                      中点插入位置                │
│                                    (新读取的页从这里插入)         │
└────────────────────────────────────────────────────────────────┘

这个设计的精妙之处：

新读取的页从"中点"插入，而不是头部。这样全表扫描等操作不会把真正热点的数据"挤出去"
访问旧子列表中的页会将其移动到新子列表的头部，成为"热点数据"
淘汰从尾部开始，优先淘汰旧子列表末尾的数据

Buffer Pool 配置

-- 查看 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 默认值：128MB，对于生产环境通常太小

-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

关键指标解读：

-- 查看 Buffer Pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

+---------------------------------------+--------+
| Variable_name                         | Value  |
+---------------------------------------+--------+
| Innodb_buffer_pool_read_requests      | 125847 |  -- 从 Buffer Pool 读取的次数
| Innodb_buffer_pool_reads              | 234    |  -- 从磁盘读取的次数
+---------------------------------------+--------+

计算命中率：命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

健康的系统命中率应该在 99% 以上。

Buffer Pool 最佳实践

[mysqld]
# Buffer Pool 大小：建议设置为物理内存的 70-80%
# 但要为操作系统和其他进程留足够内存
innodb_buffer_pool_size = 4G

# Buffer Pool 实例数：大 Buffer Pool 建议拆分多个实例
# 每个 实例至少 1GB，减少锁竞争
innodb_buffer_pool_instances = 4

# 旧子列表比例：默认 37（即 3/8）
# 如果有大量扫描操作，可以调大
innodb_old_blocks_pct = 37

# 页在旧子列表停留时间（毫秒）
# 防止扫描操作把热点数据"挤"出去
innodb_old_blocks_time = 1000

动态调整 Buffer Pool 大小（MySQL 5.7.5+）：

-- 在线调整 Buffer Pool 大小
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB

-- 查看调整进度
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Change Buffer（变更缓冲）

Change Buffer 是一种特殊的数据结构，用于缓存二级索引的修改操作。

为什么需要 Change Buffer？

二级索引通常是非唯一的，插入顺序可能不是有序的。如果每次插入都立即更新索引，会产生大量的随机 I/O。Change Buffer 的作用就是将这些随机 I/O 合并为顺序 I/O。

无 Change Buffer：
┌─────────────────────────────────────────────────────────────┐
│  INSERT → 随机位置写入索引页 → 大量随机 I/O → 性能差       │
└─────────────────────────────────────────────────────────────┘

有 Change Buffer：
┌─────────────────────────────────────────────────────────────┐
│  INSERT → 缓存到 Change Buffer → 合并到索引页（时机：      │
│           查询访问/后台线程/服务器空闲）→ 性能好           │
└─────────────────────────────────────────────────────────────┘

适用场景

写多读少的场景效果最好
非唯一二级索引
对于读密集型应用，Change Buffer 可能成为瓶颈

配置 Change Buffer

-- 查看 Change Buffer 配置
SHOW VARIABLES LIKE 'innodb_change_buffer%';

-- 控制缓冲哪些操作
-- all: 缓冲所有操作（默认）
-- changes: 仅缓冲插入和删除
-- inserts: 仅缓冲插入
-- deletes: 仅缓冲删除
-- none: 不缓冲
SET GLOBAL innodb_change_buffering = 'all';

-- Change Buffer 最大占用 Buffer Pool 的比例
SET GLOBAL innodb_change_buffer_max_size = 25;  -- 默认 25%

Redo Log（重做日志）

Redo Log 是 InnoDB 保证数据持久性和崩溃恢复的核心机制。

Redo Log 的作用

当执行写操作时，InnoDB 不直接将数据写入数据文件，而是：

先将修改记录写入 Redo Log
然后再在适当时候异步将数据刷入数据文件

这种方式称为 WAL（Write-Ahead Logging），它的优势是：

顺序写比随机写快得多：Redo Log 是顺序写入，数据文件是随机写入
崩溃恢复：如果系统崩溃，可以通过 Redo Log 重放未完成的操作

写操作流程：

客户端请求
    │
    ▼
┌─────────────────────┐
│  修改 Buffer Pool   │  ←── 内存中修改数据页
│  中的数据页         │
└─────────────────────┘
    │
    ▼
┌─────────────────────┐
│  写入 Redo Log      │  ←── 顺序写入，极快
│  （持久化到磁盘）    │
└─────────────────────┘
    │
    │ 后台异步执行
    ▼
┌─────────────────────┐
│  刷入数据文件       │  ←── 随机写入，较慢
└─────────────────────┘

返回成功给客户端 ─────→ （在 Redo Log 写入后即可返回）

Redo Log 配置

-- MySQL 8.0.30+ 使用 innodb_redo_log_capacity
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
-- 默认 100MB，对于生产环境通常需要调大

-- 动态调整
SET GLOBAL innodb_redo_log_capacity = 8589934592;  -- 8GB

-- 查看状态
SHOW STATUS LIKE 'Innodb_redo_log%';

Redo Log 刷盘策略

innodb_flush_log_at_trx_commit 控制事务提交时 Redo Log 的刷盘行为：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

值	行为	安全性	性能
0	每秒刷盘一次，事务提交不刷	可能丢失 1 秒数据	最高
1	每次事务提交都刷盘（默认）	最安全，不丢数据	较低
2	事务提交写入 OS 缓存，每秒刷盘	可能丢失 1 秒数据	较高

生产环境建议：使用默认值 1，除非你能接受数据丢失。

[mysqld]
# 最安全配置
innodb_flush_log_at_trx_commit = 1

# 性能优先配置（可接受少量数据丢失）
# innodb_flush_log_at_trx_commit = 2

Undo Log（撤销日志）

Undo Log 记录数据修改前的值，用于：

事务回滚：撤销未提交的修改
MVCC：提供历史版本供其他事务读取

-- 查看 Undo 表空间配置
SHOW VARIABLES LIKE 'innodb_undo%';

-- MySQL 8.0+ 默认使用独立 Undo 表空间
-- innodb_undo_tablespaces = 2（默认）
-- innodb_undo_directory = ./（数据目录下）

表空间（Tablespace）

表空间是 InnoDB 存储数据的逻辑容器。

表空间类型

类型	说明	文件
System Tablespace	系统表空间，存储数据字典、Change Buffer	`ibdata1`
File-Per-Table Tablespace	每表独立表空间	`表名.ibd`
General Tablespace	通用表空间，可存多个表	自定义
Undo Tablespace	Undo 日志表空间	`undo_001`, `undo_002`
Temporary Tablespace	临时表空间	`ibtmp1`

独立表空间 vs 共享表空间

-- 查看是否启用独立表空间
SHOW VARIABLES LIKE 'innodb_file_per_table';
-- 默认 ON，每张表使用独立的 .ibd 文件

独立表空间的优势：

表空间回收：DROP TABLE 或 TRUNCATE TABLE 后磁盘空间立即释放
文件管理灵活：可以将不同表的文件放在不同存储设备
性能隔离：单表 I/O 不影响其他表

InnoDB 索引结构

InnoDB 使用 B+ 树作为索引结构，分为聚簇索引和二级索引。

聚簇索引

聚簇索引是表数据本身的存储方式，特点：

数据按主键顺序存储
一张表只能有一个聚簇索引
如果没有主键，InnoDB 会选择第一个非空唯一索引；如果没有，则生成隐藏的 6 字节 ROWID

聚簇索引结构：

           ┌──────────────┐
           │   根节点      │
           │ 主键: 50     │
           └───────┬──────┘
          ┌────────┴────────┐
          ▼                 ▼
    ┌──────────────┐  ┌──────────────┐
    │ 主键: 25     │  │ 主键: 75     │
    └───────┬──────┘  └───────┬──────┘
            │                 │
    ┌───────┴───────┐ ┌───────┴───────┐
    ▼               ▼ ▼               ▼
┌─────────┐   ┌─────────┐   ┌─────────┐
│叶子节点  │   │叶子节点  │   │叶子节点  │
│主键: 10 │   │主键: 30 │   │主键: 60 │
│完整行数据│   │完整行数据│   │完整行数据│
└─────────┘   └─────────┘   └─────────┘

二级索引

二级索引的叶子节点存储的是主键值，而不是数据的物理地址：

二级索引结构：

索引列：name

        ┌──────────────┐
        │  name: 'M'   │
        └───────┬──────┘
       ┌────────┴────────┐
       ▼                 ▼
 ┌──────────────┐  ┌──────────────┐
 │ name: 'G'    │  │ name: 'T'    │
 └───────┬──────┘  └───────┬──────┘
         │                 │
 ┌───────┴───────┐ ┌───────┴───────┐
 ▼               ▼ ▼               ▼
┌─────────┐   ┌─────────┐   ┌─────────┐
│叶子节点  │   │叶子节点  │   │叶子节点  │
│'Alice'  │   │'Bob'    │   │'Tom'    │
│主键: 5  │   │主键: 2  │   │主键: 8  │
└────┬────┘   └────┬────┘   └────┬────┘
     │             │             │
     ▼             ▼             ▼
  回表查询聚簇索引获取完整数据

回表查询：通过二级索引查找数据时，需要先从二级索引找到主键，再到聚簇索引找到完整数据。这就是为什么主键应该尽量短的原因。

InnoDB 行格式

InnoDB 支持多种行格式，影响存储效率和性能：

-- 查看表的行格式
SHOW TABLE STATUS LIKE 'users'\G

-- 或
SELECT TABLE_NAME, ROW_FORMAT 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE();

行格式	说明	适用场景
REDUNDANT	旧格式，兼容性好	兼容旧版本
COMPACT	紧凑存储	默认，通用场景
DYNAMIC	变长列存储在溢出页	大字段、TEXT/BLOB
COMPRESSED	压缩存储	存储敏感场景

设置行格式：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    content TEXT
) ROW_FORMAT=DYNAMIC;

-- 或修改现有表
ALTER TABLE users ROW_FORMAT=COMPRESSED;

InnoDB 配置优化

[mysqld]
# ====== 内存配置 ======
# Buffer Pool 大小（最重要参数）
innodb_buffer_pool_size = 4G

# Buffer Pool 实例数
innodb_buffer_pool_instances = 4

# Log Buffer 大小
innodb_log_buffer_size = 32M

# ====== 日志配置 ======
# Redo Log 容量（MySQL 8.0.30+）
innodb_redo_log_capacity = 2G

# 事务提交刷盘策略（生产环境用 1）
innodb_flush_log_at_trx_commit = 1

# ====== 并发配置 ======
# IO 读线程数
innodb_read_io_threads = 8

# IO 写线程数
innodb_write_io_threads = 8

# 并发线程数限制（0 表示不限制）
innodb_thread_concurrency = 0

# ====== 刷盘配置 ======
# 脏页刷新比例
innodb_max_dirty_pages_pct = 75

# 刷新脏页时每秒最大 IOPS
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# ====== 文件配置 ======
# 使用独立表空间
innodb_file_per_table = 1

# 文件格式
innodb_file_format = Barracuda

MyISAM 存储引擎

MyISAM 是 MySQL 5.5 之前的默认存储引擎，虽然现在 InnoDB 是首选，但了解 MyISAM 对于理解存储引擎的差异和维护旧系统仍然重要。

核心特性

特性	说明
不支持事务	适合只读或读多写少的应用
表级锁定	写操作锁定整张表
全文索引	早期支持全文搜索（InnoDB 5.6+ 也支持了）
存储紧凑	数据压缩效率高
*COUNT() 快**	存储了总行数，直接返回

MyISAM 存储结构

每个 MyISAM 表对应三个文件：

表名.MYD  ←── 数据文件（MyData）
表名.MYI  ←── 索引文件（MyIndex）
表名.frm  ←── 表结构定义（MySQL 8.0 前使用）

数据文件和索引文件分离存储，这是 MyISAM 的一个特点。

索引结构

MyISAM 使用 B+ 树索引，但与 InnoDB 不同：

数据文件是堆表，按插入顺序存储
索引的叶子节点存储数据的物理地址（偏移量）
主键索引和二级索引结构相同

MyISAM 索引结构：

索引（主键或二级）：

        ┌──────────────┐
        │   根节点      │
        └───────┬──────┘
                │
    ┌───────────┴───────────┐
    ▼                       ▼
┌─────────┐           ┌─────────┐
│叶子节点  │           │叶子节点  │
│键值: 10 │           │键值: 30 │
│地址: 0x │           │地址: 0x │
│  100    │           │  200    │
└────┬────┘           └────┬────┘
     │                     │
     ▼                     ▼
┌────────────────────────────────────┐
│            数据文件                 │
│  偏移 0x100: [完整行数据...]        │
│  偏移 0x200: [完整行数据...]        │
└────────────────────────────────────┘

适用场景

-- 适合：只读表、日志表、统计表
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45),
    url VARCHAR(500),
    created_at TIMESTAMP
) ENGINE=MyISAM;

-- MyISAM 的 COUNT(*) 特别快
SELECT COUNT(*) FROM access_logs;  -- 直接返回存储的行数

不适用场景

需要事务支持的应用
高并发写入场景
需要外键约束的应用
需要行级锁的应用

MyISAM 优化

[mysqld]
# MyISAM 键缓冲大小
key_buffer_size = 256M

# 同时打开的 MyISAM 表数量
table_open_cache = 2000

# MyISAM 排序缓冲
myisam_sort_buffer_size = 64M

Memory 存储引擎

Memory 引擎将数据完全存储在内存中，适用于临时数据和高速缓存。

特点

-- 创建内存表
CREATE TABLE session_cache (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT,
    data TEXT,
    expire_at TIMESTAMP,
    INDEX USING HASH (user_id)  -- 支持 HASH 索引
) ENGINE = MEMORY;

特性	说明
数据在内存中	访问速度极快
不支持事务	数据不持久
支持 Hash 索引	等值查询极快
不支持 BLOB/TEXT	受限的数据类型
表级锁定	并发性能有限
服务器重启数据丢失	仅适合临时数据

使用场景

会话缓存
查询中间结果
临时统计
数据库连接池状态

配置

[mysqld]
# 内存表最大大小
max_heap_table_size = 256M

# 临时表最大大小（内存临时表超过此值会转为 MyISAM）
tmp_table_size = 256M

Archive 存储引擎

Archive 引擎专为数据归档设计，提供高压缩比存储。

CREATE TABLE historical_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_time TIMESTAMP,
    log_level VARCHAR(10),
    message TEXT
) ENGINE=ARCHIVE;

-- 特点：
-- 1. 高压缩比，节省存储空间
-- 2. 只支持 INSERT 和 SELECT
-- 3. 不支持索引（除主键外）
-- 4. 不支持 UPDATE 和 DELETE
-- 5. 不支持事务

适用场景：

日志归档
历史数据存储
审计记录

CSV 存储引擎

CSV 引擎以 CSV 格式存储数据，便于与其他系统交换数据。

CREATE TABLE export_data (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=CSV;

INSERT INTO export_data VALUES (1, '张三', '[email protected]');

-- 数据直接存储为 CSV 文件，可被 Excel、Python 等直接读取

适用场景：

数据导入导出
与其他系统集成
日志分析

Blackhole 存储引擎

Blackhole 引擎像 /dev/null 一样，接收数据但不存储，写入的数据直接丢弃。

CREATE TABLE binlog_bridge (
    id INT,
    data VARCHAR(100)
) ENGINE=BLACKHOLE;

INSERT INTO binlog_bridge VALUES (1, 'test');  -- 数据被丢弃

-- 但 Binary Log 会记录这个操作
-- 可用于复制过滤、性能测试

适用场景：

复制中继
性能测试
审计日志记录

存储引擎对比

功能对比

特性	InnoDB	MyISAM	Memory	Archive
事务	✅	❌	❌	❌
行级锁	✅	❌（表锁）	❌（表锁）	❌（表锁）
外键	✅	❌	❌	❌
全文索引	✅（5.6+）	✅	❌	❌
崩溃恢复	✅	❌	❌	❌
MVCC	✅	❌	❌	❌
哈希索引	❌（自适应）	❌	✅	❌

性能对比

读性能（无并发）：
MyISAM ≈ InnoDB > Memory（小数据量）

读性能（高并发）：
Memory > InnoDB >> MyISAM

写性能（低并发）：
InnoDB ≈ MyISAM

写性能（高并发）：
Memory > InnoDB >> MyISAM

存储空间：
Archive < MyISAM < InnoDB

COUNT(*) 性能：
MyISAM > InnoDB（小表）
MyISAM ≈ InnoDB（大表，有 WHERE 条件）

修改表的存储引擎

ALTER TABLE 方式

-- 修改表的存储引擎
ALTER TABLE users ENGINE=InnoDB;

-- 注意：
-- 1. 会重建整张表，耗时较长
-- 2. 大表修改期间会锁表
-- 3. 需要足够的磁盘空间

安全迁移方式

对于大表，建议使用以下步骤：

-- 1. 创建新表
CREATE TABLE users_new LIKE users;
ALTER TABLE users_new ENGINE=InnoDB;

-- 2. 迁移数据（可分批）
INSERT INTO users_new SELECT * FROM users;

-- 3. 验证数据
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users_new;

-- 4. 重命名交换
RENAME TABLE users TO users_old, users_new TO users;

-- 5. 确认无误后删除旧表
DROP TABLE users_old;

使用 pt-online-schema-change

对于生产环境的大表，推荐使用 Percona Toolkit 的 pt-online-schema-change 工具，可以在不锁表的情况下修改表结构：

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=database,t=users \
  --execute

选择存储引擎的建议

决策流程

开始选择存储引擎
       │
       ├── 需要事务支持？ ────── 是 ──→ InnoDB
       │       │
       │       否
       │       │
       │       ▼
       ├── 需要外键约束？ ────── 是 ──→ InnoDB
       │       │
       │       否
       │       │
       │       ▼
       ├── 高并发写入？ ──────── 是 ──→ InnoDB
       │       │
       │       否
       │       │
       │       ▼
       ├── 只读或读多写少？ ──── 是 ──→ MyISAM（或 InnoDB）
       │       │
       │       否
       │       │
       │       ▼
       ├── 需要持久化？ ──────── 是 ──→ Archive（归档）
       │       │
       │       否
       │       │
       │       ▼
       └── 临时数据/缓存 ────────────→ Memory

常见场景推荐

场景	推荐引擎	原因
电商交易系统	InnoDB	事务支持、数据一致性
内容管理系统	InnoDB	默认选择，功能完备
日志分析系统	InnoDB/MyISAM	读密集，考虑压缩存储
数据归档	Archive	高压缩比
会话缓存	Memory	内存访问快
数据导入导出	CSV	标准格式

最佳实践

默认使用 InnoDB：除非有明确的理由选择其他引擎
不要混用引擎：同一业务系统的表尽量使用相同引擎
定期检查引擎：确保表使用正确的引擎
监控性能指标：根据实际负载调整

监控存储引擎

InnoDB 监控

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 关键指标
-- BUFFER POOL AND MEMORY: Buffer Pool 使用情况
-- LOG: Redo Log 信息
-- TRANSACTIONS: 活跃事务
-- FILE I/O: 文件 I/O 情况
-- INSERT BUFFER AND ADAPTIVE HASH INDEX: Change Buffer 和自适应哈希索引

Buffer Pool 监控

-- 查看 Buffer Pool 详细信息
SELECT 
    POOL_ID,
    POOL_SIZE,
    FREE_BUFFERS,
    DATABASE_PAGES,
    OLD_DATABASE_PAGES,
    MODIFIED_DATABASE_PAGES,
    PAGES_MADE_YOUNG,
    PAGES_NOT_MADE_YOUNG
FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

-- 查看哪些表在 Buffer Pool 中
SELECT 
    TABLE_NAME,
    COUNT(*) AS pages,
    SUM(IF(COMPRESSED_SIZE > 0, 1, 0)) AS compressed_pages
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
WHERE TABLE_NAME IS NOT NULL
GROUP BY TABLE_NAME
ORDER BY pages DESC;

锁等待监控

-- MySQL 8.0+ 查看锁等待
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w
JOIN INFORMATION_SCHEMA.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN INFORMATION_SCHEMA.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 使用 Performance Schema（MySQL 8.0+）
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    THREAD_ID,
    PROCESSLIST_ID
FROM PERFORMANCE_SCHEMA.DATA_LOCKS
WHERE OBJECT_SCHEMA = 'your_database';

小结

本章深入学习了 MySQL 存储引擎：

存储引擎概念：数据存储和管理的底层组件，MySQL 支持插件式引擎架构
InnoDB 深入：
- 内存结构：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer
- 磁盘结构：System Tablespace、File-Per-Table Tablespace、Redo Log、Undo Log
- 索引结构：聚簇索引、二级索引、回表查询
- 配置优化：内存、日志、并发、刷盘参数
其他引擎：MyISAM、Memory、Archive、CSV、Blackhole 的特点和使用场景
引擎选择：根据事务需求、并发要求、数据特性选择合适的引擎
监控与维护：监控 Buffer Pool、锁等待等关键指标

练习

查看当前数据库中所有表的存储引擎，分析是否合理
调整 InnoDB Buffer Pool 大小，观察性能变化
创建一个 InnoDB 表和一个 MyISAM 表，比较相同数据的存储空间
使用 SHOW ENGINE INNODB STATUS 分析当前数据库状态
实现一个大表存储引擎的安全迁移

什么是存储引擎？​

存储引擎的职责​

为什么需要多种存储引擎？​

查看存储引擎​

查看支持的存储引擎​

查看表的存储引擎​

查看数据库中所有表的引擎​

InnoDB 存储引擎​

核心特性​

InnoDB 架构详解​

Buffer Pool（缓冲池）​

Buffer Pool 的工作原理​

Buffer Pool 配置​

Buffer Pool 最佳实践​

Change Buffer（变更缓冲）​

为什么需要 Change Buffer？​

适用场景​

配置 Change Buffer​

Redo Log（重做日志）​

Redo Log 的作用​

Redo Log 配置​

Redo Log 刷盘策略​

Undo Log（撤销日志）​

表空间（Tablespace）​

表空间类型​

独立表空间 vs 共享表空间​

InnoDB 索引结构​

聚簇索引​

二级索引​

InnoDB 行格式​

InnoDB 配置优化​

MyISAM 存储引擎​

核心特性​

MyISAM 存储结构​

索引结构​

适用场景​

不适用场景​

MyISAM 优化​

Memory 存储引擎​

特点​

使用场景​

配置​

Archive 存储引擎​

CSV 存储引擎​

Blackhole 存储引擎​

存储引擎对比​

功能对比​

性能对比​

修改表的存储引擎​

ALTER TABLE 方式​

安全迁移方式​

使用 pt-online-schema-change​

选择存储引擎的建议​

决策流程​

常见场景推荐​

最佳实践​

监控存储引擎​

InnoDB 监控​

Buffer Pool 监控​

锁等待监控​

小结​

练习​

参考资料​

什么是存储引擎？

存储引擎的职责

为什么需要多种存储引擎？

查看存储引擎

查看支持的存储引擎

查看表的存储引擎

查看数据库中所有表的引擎

InnoDB 存储引擎

核心特性

InnoDB 架构详解

Buffer Pool（缓冲池）

Buffer Pool 的工作原理

Buffer Pool 配置

Buffer Pool 最佳实践

Change Buffer（变更缓冲）

为什么需要 Change Buffer？

适用场景

配置 Change Buffer

Redo Log（重做日志）

Redo Log 的作用

Redo Log 配置

Redo Log 刷盘策略

Undo Log（撤销日志）

表空间（Tablespace）

表空间类型

独立表空间 vs 共享表空间

InnoDB 索引结构

聚簇索引

二级索引

InnoDB 行格式

InnoDB 配置优化

MyISAM 存储引擎

核心特性

MyISAM 存储结构

索引结构

适用场景

不适用场景

MyISAM 优化

Memory 存储引擎

特点

使用场景

配置

Archive 存储引擎

CSV 存储引擎

Blackhole 存储引擎

存储引擎对比

功能对比

性能对比

修改表的存储引擎

ALTER TABLE 方式

安全迁移方式

使用 pt-online-schema-change

选择存储引擎的建议

决策流程

常见场景推荐

最佳实践

监控存储引擎

InnoDB 监控

Buffer Pool 监控

锁等待监控

小结

练习

参考资料