MySQL教程 / 第 70 节

第07章：存储引擎深入理解

存储引擎是MySQL的核心组件，决定了数据的存储方式和性能特性

7.1 存储引擎概述

7.1.1 什么是存储引擎？

存储引擎（Storage Engine）：

MySQL的数据存储和管理机制
负责数据的存储、索引、事务等功能
不同的存储引擎有不同的特性和适用场景

MySQL的插件式存储引擎架构：

应用层
    ↓
SQL层（解析、优化）
    ↓
存储引擎层（InnoDB、MyISAM等）
    ↓
文件系统

7.1.2 查看存储引擎

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

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

-- 查看表使用的存储引擎
SHOW TABLE STATUS FROM database_name;
SHOW CREATE TABLE table_name;

7.1.3 MySQL 5.7支持的存储引擎

存储引擎	说明	事务	锁粒度
InnoDB	默认引擎，支持事务	✅	行锁
MyISAM	高性能，不支持事务	❌	表锁
Memory	内存存储，速度快	❌	表锁
Archive	压缩存储，适合归档	❌	行锁
CSV	CSV格式存储	❌	表锁
Blackhole	黑洞引擎，不存储数据	❌	-

7.2 InnoDB存储引擎 ⭐⭐⭐⭐⭐

7.2.1 InnoDB概述

InnoDB特点：

✅ 支持事务（ACID特性）
✅ 支持外键
✅ 行级锁（并发性能好）
✅ MVCC（多版本并发控制）
✅ 崩溃恢复（crash-safe）
✅ 聚簇索引（数据和索引存储在一起）

适用场景：

需要事务支持的应用（转账、订单等）
高并发读写场景
需要崩溃恢复的场景
大部分OLTP（在线事务处理）应用

MySQL 5.7默认存储引擎：InnoDB

7.2.2 InnoDB文件结构

# InnoDB数据文件
/var/lib/mysql/
├── ibdata1              # 系统表空间（共享表空间）
├── ib_logfile0          # redo log文件
├── ib_logfile1          # redo log文件
└── database_name/
    ├── table_name.frm   # 表结构定义文件
    └── table_name.ibd   # 表数据和索引文件（独立表空间）

文件说明：

ibdata1：系统表空间，存储数据字典、undo log等
ib_logfile：redo log，用于崩溃恢复
.frm：表结构定义文件（所有引擎都有）
.ibd：独立表空间，存储表数据和索引

7.2.3 InnoDB核心特性

1. 事务支持

-- InnoDB支持完整的ACID事务
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;  -- 或 ROLLBACK

2. 行级锁

-- InnoDB使用行级锁，并发性能好
-- 会话1
START TRANSACTION;
UPDATE users SET age = 26 WHERE id = 1;  -- 锁定id=1的行

-- 会话2（不会被阻塞）
UPDATE users SET age = 31 WHERE id = 2;  -- 可以正常执行

-- 会话3（会被阻塞）
UPDATE users SET age = 27 WHERE id = 1;  -- 等待会话1释放锁

3. 外键约束

-- InnoDB支持外键
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- 外键约束：
-- 1. 保证数据完整性
-- 2. 级联删除/更新
-- 3. 性能开销较大

4. MVCC多版本并发控制

-- InnoDB通过MVCC实现高并发
-- 读不加锁，写不阻塞读
-- 详见第08章：事务与并发控制

5. 崩溃恢复

-- InnoDB通过redo log实现崩溃恢复
-- MySQL异常关闭后，重启时自动恢复未提交的事务
-- 详见第15章：MySQL日志系统

7.2.4 InnoDB配置参数

[mysqld]
# InnoDB缓冲池大小（最重要的参数）
# 建议设置为物理内存的50%-70%
innodb_buffer_pool_size = 1G

# InnoDB日志文件大小
innodb_log_file_size = 512M

# InnoDB刷新日志策略
# 0: 每秒刷新（性能最好，可能丢失1秒数据）
# 1: 每次事务提交刷新（最安全，性能较差）⭐ 推荐
# 2: 每次事务提交写入OS缓存，每秒刷新
innodb_flush_log_at_trx_commit = 1

# InnoDB文件格式
innodb_file_format = Barracuda

# 独立表空间（推荐开启）
innodb_file_per_table = 1

# InnoDB IO线程数
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# InnoDB锁等待超时时间（秒）
innodb_lock_wait_timeout = 50

7.2.5 InnoDB索引结构

-- InnoDB使用聚簇索引（Clustered Index）
-- 特点：
-- 1. 主键索引的叶子节点存储完整的行数据
-- 2. 二级索引的叶子节点存储主键值
-- 3. 通过二级索引查询需要回表

-- 示例
CREATE TABLE users (
    id INT PRIMARY KEY,        -- 聚簇索引
    username VARCHAR(50),
    age INT,
    INDEX idx_age (age)        -- 二级索引
) ENGINE=InnoDB;

-- 查询过程：
-- SELECT * FROM users WHERE age = 25;
-- 1. 通过idx_age找到age=25的主键id
-- 2. 通过主键id回表查询完整数据

7.3 MyISAM存储引擎

7.3.1 MyISAM概述

MyISAM特点：

❌ 不支持事务
❌ 不支持外键
✅ 表级锁（并发性能差）
✅ 支持全文索引（MySQL 5.6之前）
✅ 压缩表（节省空间）
✅ 查询速度快（简单查询）

适用场景：

只读或读多写少的应用
不需要事务的场景
日志、历史数据等
MySQL 5.7已不推荐使用

7.3.2 MyISAM文件结构

# MyISAM数据文件
/var/lib/mysql/database_name/
├── table_name.frm   # 表结构定义文件
├── table_name.MYD   # 数据文件（MYData）
└── table_name.MYI   # 索引文件（MYIndex）

文件说明：

.frm：表结构定义
.MYD：表数据
.MYI：表索引

7.3.3 MyISAM核心特性

1. 表级锁

-- MyISAM使用表级锁，并发性能差
-- 会话1
UPDATE users SET age = 26 WHERE id = 1;  -- 锁定整个表

-- 会话2（被阻塞）
UPDATE users SET age = 31 WHERE id = 2;  -- 等待表锁释放
SELECT * FROM users WHERE id = 3;        -- 读操作也可能被阻塞

2. 不支持事务

-- MyISAM不支持事务
START TRANSACTION;  -- 无效
UPDATE users SET age = 26 WHERE id = 1;
ROLLBACK;  -- 无法回滚，数据已经修改

3. 不支持崩溃恢复

-- MyISAM没有redo log
-- MySQL异常关闭可能导致数据损坏
-- 需要使用REPAIR TABLE修复
REPAIR TABLE table_name;

4. 支持压缩表

-- MyISAM支持压缩表（只读）
-- 使用myisampack工具压缩
-- 可以节省50%-80%的磁盘空间

7.3.4 MyISAM索引结构

-- MyISAM使用非聚簇索引（Non-Clustered Index）
-- 特点：
-- 1. 索引和数据分开存储
-- 2. 索引的叶子节点存储数据的物理地址
-- 3. 主键索引和二级索引结构相同

-- 示例
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    age INT,
    INDEX idx_age (age)
) ENGINE=MyISAM;

-- 查询过程：
-- SELECT * FROM users WHERE age = 25;
-- 1. 通过idx_age找到age=25的数据物理地址
-- 2. 通过物理地址直接读取数据

7.4 InnoDB vs MyISAM对比 ⭐⭐⭐⭐⭐

7.4.1 核心差异对比

特性	InnoDB	MyISAM
事务	✅ 支持	❌ 不支持
外键	✅ 支持	❌ 不支持
锁粒度	行锁	表锁
MVCC	✅ 支持	❌ 不支持
崩溃恢复	✅ 支持	❌ 不支持
索引类型	聚簇索引	非聚簇索引
全文索引	✅ 5.6+支持	✅ 支持
压缩	✅ 支持	✅ 支持
存储空间	较大	较小
查询性能	较好	简单查询更快
写入性能	较好	较差（表锁）
适用场景	OLTP	OLAP（只读）

7.4.2 性能对比

-- 读性能对比
-- 简单查询：MyISAM略快（无事务开销）
-- 复杂查询：InnoDB更快（行锁、MVCC）

-- 写性能对比
-- 单线程写入：MyISAM可能更快
-- 多线程写入：InnoDB远超MyISAM（行锁）

-- 并发性能对比
-- 读写混合：InnoDB远超MyISAM（MVCC）
-- 只读场景：MyISAM略有优势

7.4.3 存储空间对比

-- 创建测试表
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;

-- 插入相同数据
-- InnoDB文件大小：约1.2倍MyISAM
-- 原因：InnoDB存储事务信息、MVCC版本等

7.4.4 如何选择？

选择InnoDB的场景（推荐）：

✅ 需要事务支持（转账、订单等）
✅ 高并发读写
✅ 需要崩溃恢复
✅ 需要外键约束
✅ 大部分应用场景

选择MyISAM的场景（不推荐）：

✅ 只读或读多写少
✅ 不需要事务
✅ 历史数据、日志表
⚠️ MySQL 5.7已不推荐使用

建议：

MySQL 5.7默认使用InnoDB
99%的场景都应该使用InnoDB
除非有特殊需求，否则不要使用MyISAM

7.5 其他存储引擎

7.5.1 Memory引擎

-- Memory引擎：数据存储在内存中
CREATE TABLE cache_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=Memory;

-- 特点：
-- ✅ 速度极快（内存访问）
-- ❌ 数据易丢失（重启后数据消失）
-- ❌ 不支持TEXT/BLOB类型
-- ❌ 表级锁

-- 适用场景：
-- 临时数据、缓存
-- 会话数据
-- 中间结果集

7.5.2 Archive引擎

-- Archive引擎：压缩存储，适合归档
CREATE TABLE log_archive (
    id INT AUTO_INCREMENT,
    log_time TIMESTAMP,
    log_content TEXT,
    KEY(id)
) ENGINE=Archive;

-- 特点：
-- ✅ 高压缩比（1:10）
-- ✅ 支持INSERT和SELECT
-- ❌ 不支持UPDATE和DELETE
-- ❌ 不支持索引（除了AUTO_INCREMENT）

-- 适用场景：
-- 历史数据归档
-- 日志存储
-- 只增不改的数据

7.5.3 CSV引擎

-- CSV引擎：以CSV格式存储数据
CREATE TABLE csv_table (
    id INT,
    name VARCHAR(50),
    age INT
) ENGINE=CSV;

-- 特点：
-- ✅ 数据以CSV格式存储，可直接编辑
-- ❌ 不支持索引
-- ❌ 不支持NULL值

-- 适用场景：
-- 数据导入导出
-- 与Excel等工具交互

7.5.4 Blackhole引擎

-- Blackhole引擎：黑洞引擎，不存储数据
CREATE TABLE blackhole_table (
    id INT,
    data VARCHAR(100)
) ENGINE=Blackhole;

-- 特点：
-- 写入的数据会被丢弃
-- 但会记录binlog

-- 适用场景：
-- 主从复制中的中继服务器
-- 性能测试
-- 过滤不需要的数据

7.6 存储引擎的使用

7.6.1 指定存储引擎

-- 方法1：创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
) ENGINE=InnoDB;

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

-- 方法3：设置默认存储引擎
SET default_storage_engine=InnoDB;

-- 方法4：配置文件中设置
-- my.cnf
[mysqld]
default-storage-engine=InnoDB

7.6.2 查看存储引擎

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users';
SHOW CREATE TABLE users;

-- 查看所有表的存储引擎
SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';

7.6.3 转换存储引擎

-- 方法1：ALTER TABLE（推荐）
ALTER TABLE users ENGINE=InnoDB;

-- 方法2：导出导入
-- 1. 导出数据
mysqldump -u root -p database_name table_name > table_name.sql

-- 2. 修改SQL文件中的ENGINE
-- 3. 导入数据
mysql -u root -p database_name < table_name.sql

-- 方法3：CREATE...SELECT
CREATE TABLE users_innodb LIKE users;
ALTER TABLE users_innodb ENGINE=InnoDB;
INSERT INTO users_innodb SELECT * FROM users;

-- 注意事项：
-- 1. 转换过程会锁表
-- 2. 大表转换时间较长
-- 3. 建议在业务低峰期操作

7.7 存储引擎最佳实践

7.7.1 选择建议

-- ✅ 推荐：默认使用InnoDB
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ✅ 特殊场景：临时数据使用Memory
CREATE TABLE session_data (
    session_id VARCHAR(64) PRIMARY KEY,
    data TEXT
) ENGINE=Memory;

-- ✅ 特殊场景：归档数据使用Archive
CREATE TABLE access_log_archive (
    id BIGINT AUTO_INCREMENT,
    access_time TIMESTAMP,
    log_content TEXT,
    KEY(id)
) ENGINE=Archive;

7.7.2 性能优化

-- InnoDB优化建议
-- 1. 合理设置innodb_buffer_pool_size
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1GB

-- 2. 使用独立表空间
SET GLOBAL innodb_file_per_table = 1;

-- 3. 优化事务提交策略
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 4. 定期优化表
OPTIMIZE TABLE users;

-- 5. 分析表统计信息
ANALYZE TABLE users;

7.7.3 常见问题

-- 问题1：如何查看InnoDB状态？
SHOW ENGINE INNODB STATUS;

-- 问题2：如何查看表空间大小？
SELECT
    TABLE_NAME,
    ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
    ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database_name';

-- 问题3：如何回收表空间？
-- InnoDB
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;

-- MyISAM
OPTIMIZE TABLE users;

7.8 本章总结

本章学习内容：

✅ 存储引擎概述
✅ InnoDB存储引擎详解 ⭐⭐⭐⭐⭐
✅ MyISAM存储引擎详解
✅ InnoDB vs MyISAM对比 ⭐⭐⭐⭐⭐
✅ 其他存储引擎（Memory、Archive、CSV、Blackhole）
✅ 存储引擎的使用和转换
✅ 最佳实践

重点掌握：

InnoDB是MySQL 5.7的默认存储引擎
InnoDB支持事务、外键、行锁、MVCC、崩溃恢复
MyISAM不支持事务，使用表锁，不推荐使用
InnoDB使用聚簇索引，MyISAM使用非聚簇索引
99%的场景都应该使用InnoDB

InnoDB vs MyISAM核心差异：

事务：InnoDB支持，MyISAM不支持
锁：InnoDB行锁，MyISAM表锁
崩溃恢复：InnoDB支持，MyISAM不支持
并发：InnoDB更好，MyISAM较差

面试重点：

InnoDB和MyISAM的区别
为什么选择InnoDB
聚簇索引和非聚簇索引的区别
InnoDB的MVCC机制
如何选择存储引擎

下一章预告： 事务与并发控制

练习题

说明InnoDB和MyISAM的主要区别
什么是聚簇索引和非聚簇索引？
为什么InnoDB的并发性能比MyISAM好？
如何查看表使用的存储引擎？
如何将MyISAM表转换为InnoDB表？
Memory引擎适合什么场景？
什么情况下会选择Archive引擎？
InnoDB的主要配置参数有哪些？
如何优化InnoDB的性能？
为什么MySQL 5.7默认使用InnoDB？

继续学习： 第08章：事务与并发控制