MySQL 存储引擎

一、存储引擎概述

1.1 什么是存储引擎

存储引擎是 MySQL 的核心组件，负责数据的存储和读取。MySQL 采用插件式的存储引擎架构，支持多种存储引擎。

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

---------------------------------------------------------------------------------------------
| Engine             | Support | Comment                                                        |
---------------------------------------------------------------------------------------------
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys    |
| MyISAM             | YES     | Non-transactional engine with good full-text search support   |
| Memory             | YES     | Hash based, stored in memory, useful for temporary tables     |
| CSV                | YES     | CSV storage engine                                             |
| Archive            | YES     | Archive storage engine                                         |
| Blackhole          | YES     | /dev/null storage engine                                       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             |
---------------------------------------------------------------------------------------------

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users'\G

1.2 存储引擎对比

特性	InnoDB	MyISAM	Memory	Archive
事务支持	是	否	否	否
锁粒度	行锁	表锁	表锁	行锁
外键	是	否	否	否
MVCC	是	否	否	否
崩溃恢复	是	否	否	是
全文索引	是(5.6)	是	否	否
空间索引	是	是	否	否
数据缓存	是	否	N/A	否
索引缓存	是	是	N/A	否
压缩	是	是	否	是
存储限制	64TB	256TB	RAM	None

二、InnoDB 存储引擎

2.1 InnoDB 特点

• 事务支持：完整的 ACID 特性
• 行级锁定：高并发性能
• 外键约束：数据完整性保证
• MVCC：多版本并发控制
• 崩溃恢复：通过 Redo Log 实现
• 聚簇索引：数据按主键顺序存储

2.2 InnoDB 架构

InnoDB 架构说明：

💾 内存结构：

• Buffer Pool：InnoDB 的主要内存组件，缓存数据页、索引页等
• Change Buffer：缓存对辅助索引的修改操作
• Log Buffer：缓冲 Redo Log 日志

💿 磁盘结构：

• 系统表空间：存储数据字典、双写缓冲、Change Buffer、Undo Logs
• 独立表空间：每个表独立的 .ibd 文件，存储表数据和索引
• Undo 表空间：独立的 Undo 日志文件
• 临时表空间：存储临时表数据
• Redo Log：重做日志，用于崩溃恢复
• Binary Log：二进制日志，用于主从复制和恢复

2.3 Buffer Pool 详解

Buffer Pool 是 InnoDB 最重要的内存组件，用于缓存数据页和索引页。

-- 查看 Buffer Pool 配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-----------------------------------------------------
| Variable_name                       | Value          |
-----------------------------------------------------
| innodb_buffer_pool_size             | 134217728      | -- 128MB
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_load_at_startup  | ON             |
-----------------------------------------------------

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

----------------------------------------------------
| Variable_name                         | Value       |
----------------------------------------------------
| Innodb_buffer_pool_pages_total        | 8192        | -- 总页数
| Innodb_buffer_pool_pages_free         | 1024        | -- 空闲页数
| Innodb_buffer_pool_pages_data         | 7000        | -- 数据页数
| Innodb_buffer_pool_pages_dirty        | 100         | -- 脏页数
| Innodb_buffer_pool_read_requests      | 1000000     | -- 读请求次数
| Innodb_buffer_pool_reads              | 1000        | -- 从磁盘读次数
| Innodb_buffer_pool_write_requests     | 500000      | -- 写请求次数
----------------------------------------------------

-- 计算命中率
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 理想值 > 99%

Buffer Pool 配置建议：

[mysqld]
# 设置为物理内存的 60-80%
innodb_buffer_pool_size = 8G

# 多实例，建议等于 CPU 核数
innodb_buffer_pool_instances = 8

# 关闭时保存，启动时加载
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# 在线调整（MySQL 5.7）
SET GLOBAL innodb_buffer_pool_size = 10737418240;  -- 10G

2.4 Change Buffer

Change Buffer 用于缓存对二级索引页的修改，减少随机 I/O。

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

--------------------------------------
| Variable_name                 | Value |
--------------------------------------
| innodb_change_buffer_max_size | 25    | -- 占 Buffer Pool 的比例
| innodb_change_buffering       | all   | -- all/none/inserts/deletes/changes/purges
--------------------------------------

-- 查看 Change Buffer 状态
SHOW STATUS LIKE 'Innodb_ibuf%';

Change Buffer 适用场景：

• 写多读少的场景
• 二级索引较多的表
• 不适用于唯一索引（需要检查唯一性）

2.5 Log Buffer

-- 查看 Log Buffer 配置
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

-- 建议值：16M - 64M
SET GLOBAL innodb_log_buffer_size = 67108864;  -- 64M

-- 刷新策略
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 0: 每秒写入并刷盘（可能丢失1秒数据）
-- 1: 每次提交都刷盘（默认，最安全）
-- 2: 每次提交写入 OS 缓存，每秒刷盘

2.6 Redo Log

Redo Log 用于崩溃恢复，保证事务的持久性。

-- 查看 Redo Log 配置
SHOW VARIABLES LIKE 'innodb_log%';

---------------------------------------
| Variable_name               | Value    |
---------------------------------------
| innodb_log_file_size        | 50331648 | -- 48M
| innodb_log_files_in_group   | 2        |
| innodb_log_buffer_size      | 16777216 | -- 16M
---------------------------------------

-- 配置建议
[mysqld]
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

Redo Log 循环写入机制：

write pos: 当前写入位置
checkpoint: 当前刷盘位置

┌────────────────────────────────────────┐
│                                        │
│   ib_logfile0        ib_logfile1       │
│  ┌──────────┐       ┌──────────┐       │
│  │██████░░░░│  →    │░░░░░░░░░░│       │
│  │  ↑   ↑   │       │          │       │
│  │  │   │   │       │          │       │
│  │  │   write pos   │          │       │
│  │  checkpoint      │          │       │
│  └──────────┘       └──────────┘       │
│                                        │
│  █: 已写入待刷盘                        │
│  ░: 可写入                             │
└────────────────────────────────────────┘

write pos 追上 checkpoint 时，必须等待刷盘

2.7 Undo Log

Undo Log 用于事务回滚和 MVCC。

-- 查看 Undo 配置（MySQL 8.0）
SHOW VARIABLES LIKE 'innodb_undo%';

-------------------------------------
| Variable_name           | Value      |
-------------------------------------
| innodb_undo_directory   | ./         |
| innodb_undo_log_encrypt | OFF        |
| innodb_undo_log_truncate| ON         |
| innodb_undo_tablespaces | 2          |
-------------------------------------

-- 查看 Undo 表空间
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE space_type = 'Undo';

2.8 Doublewrite Buffer

Doublewrite 机制用于防止页断裂（Partial Write）。

-- 查看 Doublewrite 配置
SHOW VARIABLES LIKE 'innodb_doublewrite';

-- 写入流程：
-- 1. 先将脏页写入 Doublewrite Buffer（顺序写）
-- 2. 再将脏页写入数据文件（随机写）
-- 3. 如果数据文件写入失败，可以从 Doublewrite Buffer 恢复

-- SSD 可以考虑关闭（SSD 支持原子写）
SET GLOBAL innodb_doublewrite = OFF;

三、MyISAM 存储引擎

3.1 MyISAM 特点

• 不支持事务
• 表级锁：并发性能差
• 不支持外键
• 支持全文索引（5.6 之前 InnoDB 不支持）
• 支持压缩表

3.2 MyISAM 文件结构

表文件组成：
├── table_name.frm  -- 表结构定义
├── table_name.MYD  -- 数据文件（MyISAM Data）
└── table_name.MYI  -- 索引文件（MyISAM Index）

3.3 MyISAM 适用场景

-- 1. 只读或读多写少的表
CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    message TEXT,
    created_at DATETIME
) ENGINE=MyISAM;

-- 2. 需要全文索引（MySQL 5.6 之前）
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content(content)
) ENGINE=MyISAM;

-- 3. 临时表
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
) ENGINE=MyISAM;

3.4 MyISAM 锁

-- MyISAM 使用表级锁
-- 读锁（共享锁）：允许其他会话读，不允许写
-- 写锁（排他锁）：不允许其他会话读写

-- 手动加锁
LOCK TABLES users READ;
LOCK TABLES users WRITE;
UNLOCK TABLES;

-- 查看锁状态
SHOW STATUS LIKE 'Table_locks%';

-----------------------------------
| Variable_name              | Value |
-----------------------------------
| Table_locks_immediate      | 100   | -- 立即获得锁的次数
| Table_locks_waited         | 5     | -- 等待锁的次数
-----------------------------------

-- 如果 Table_locks_waited / Table_locks_immediate > 1/20
-- 说明锁竞争严重，考虑换用 InnoDB

四、Memory 存储引擎

4.1 Memory 特点

• 数据存储在内存：重启后数据丢失
• 表级锁
• 支持 Hash 和 B-Tree 索引
• 不支持 BLOB/TEXT 类型
• 固定行长度：VARCHAR 转为 CHAR

4.2 Memory 适用场景

-- 1. 临时数据
CREATE TABLE session_data (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id BIGINT,
    data TEXT,
    expire_time INT
) ENGINE=Memory;

-- 2. 缓存表
CREATE TABLE cache_data (
    cache_key VARCHAR(100) PRIMARY KEY,
    cache_value VARCHAR(1000),
    INDEX USING HASH (cache_key)
) ENGINE=Memory;

-- 3. 中间计算结果
CREATE TABLE temp_calculation (
    id INT PRIMARY KEY,
    result DECIMAL(10,2)
) ENGINE=Memory;

4.3 Memory 配置

-- 查看最大表大小
SHOW VARIABLES LIKE 'max_heap_table_size';

-- 设置最大表大小
SET max_heap_table_size = 268435456;  -- 256M

-- 注意：临时表由 tmp_table_size 和 max_heap_table_size 共同控制
SHOW VARIABLES LIKE 'tmp_table_size';

五、其他存储引擎

5.1 Archive 存储引擎

适用于存储归档数据，只支持 INSERT 和 SELECT。

CREATE TABLE access_logs_archive (
    id BIGINT PRIMARY KEY,
    log_time DATETIME,
    log_content TEXT
) ENGINE=Archive;

-- 特点：
-- 1. 高压缩比（约 1:8）
-- 2. 只支持 INSERT 和 SELECT
-- 3. 不支持索引
-- 4. 行级锁

5.2 CSV 存储引擎

数据以 CSV 格式存储。

CREATE TABLE csv_data (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    value DECIMAL(10,2) NOT NULL
) ENGINE=CSV;

-- 特点：
-- 1. 数据文件是标准 CSV 格式
-- 2. 可以直接编辑 .CSV 文件
-- 3. 不支持索引
-- 4. 所有列必须 NOT NULL

5.3 Blackhole 存储引擎

数据写入后立即丢弃，用于特殊场景。

CREATE TABLE blackhole_test (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=Blackhole;

-- 用途：
-- 1. 复制中继：只转发 binlog，不存储数据
-- 2. 性能测试：测试纯写入性能
-- 3. 日志记录：写入触发 binlog，但不存储

5.4 Federated 存储引擎

用于访问远程 MySQL 数据库。

-- 需要先开启
SET GLOBAL federated = ON;

-- 创建 Federated 表
CREATE TABLE remote_users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=FEDERATED
CONNECTION='mysql://user:pass@remote_host:3306/db_name/users';

-- 查询时会访问远程数据库
SELECT * FROM remote_users;

六、存储引擎选择

6.1 选择决策树

🔐 InnoDB：默认引擎，支持事务、行锁、MVCC，适合大多数场景

📁 MyISAM：表锁引擎，适合读多写少、需要全文索引的场景

📦 Archive：高压缩比引擎，适合大量只读数据归档

💾 Memory：内存引擎，适合临时数据、缓存数据，速度快但数据易丢失

6.2 场景推荐

场景	推荐引擎	原因
通用业务	InnoDB	事务、行锁、MVCC
读多写少	InnoDB	行锁，MVCC 读不加锁
写密集	InnoDB	行锁，Change Buffer
日志归档	Archive	高压缩比
临时数据	Memory	高性能
数据仓库	InnoDB/ColumnStore	OLAP 可考虑列式存储

6.3 引擎转换

-- 查看当前引擎
SHOW CREATE TABLE users\G

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

-- 注意事项：
-- 1. 会重建整个表，需要较长时间
-- 2. 期间会锁表
-- 3. 需要足够的磁盘空间（临时文件）
-- 4. 建议在低峰期操作

-- 使用 pt-online-schema-change 在线修改
pt-online-schema-change \
  --alter="ENGINE=InnoDB" \
  D=database,t=table_name \
  --execute

七、InnoDB 配置优化

7.1 内存配置

[mysqld]
# Buffer Pool（最重要）
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

# Log Buffer
innodb_log_buffer_size = 64M

# Change Buffer（默认 25%）
innodb_change_buffer_max_size = 25

7.2 I/O 配置

[mysqld]
# Redo Log
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1

# I/O 线程
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 刷脏页速度
innodb_io_capacity = 200        # HDD
innodb_io_capacity = 2000       # SSD
innodb_io_capacity_max = 4000

# 预读
innodb_read_ahead_threshold = 56

7.3 并发配置

[mysqld]
# 并发线程数
innodb_thread_concurrency = 0  # 0 表示不限制

# 锁等待超时
innodb_lock_wait_timeout = 50

7.4 数据完整性

[mysqld]
# Doublewrite
innodb_doublewrite = ON

# Checksum
innodb_checksum_algorithm = crc32

# 崩溃恢复
innodb_force_recovery = 0  # 正常情况为 0

八、总结

存储引擎核心要点

1. InnoDB 是默认选择：支持事务、行锁、MVCC、崩溃恢复
2. MyISAM 已过时：不建议新项目使用
3. Memory 适合临时数据：注意数据会丢失
4. Archive 适合归档数据：高压缩比

InnoDB 核心组件

1. Buffer Pool：核心内存组件，缓存数据和索引
2. Change Buffer：缓存二级索引修改
3. Redo Log：保证持久性
4. Undo Log：支持回滚和 MVCC

配置建议

1. Buffer Pool 设置为物理内存的 60-80%
2. Redo Log 设置足够大（256M-1G）
3. 根据存储类型调整 I/O 容量
4. 保持默认的数据完整性配置