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

MySQL教程 / 进阶提升

第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章：事务与并发控制

第07章：存储引擎深入理解 - MySQL教程 - jackssybin 的个人博客

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

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

7.1 存储引擎概述

7.1.1 什么是存储引擎？

7.1.2 查看存储引擎

7.1.3 MySQL 5.7支持的存储引擎

7.2 InnoDB存储引擎 ⭐⭐⭐⭐⭐

7.2.1 InnoDB概述

7.2.2 InnoDB文件结构

7.2.3 InnoDB核心特性

1. 事务支持

2. 行级锁

3. 外键约束

4. MVCC多版本并发控制

5. 崩溃恢复

7.2.4 InnoDB配置参数

7.2.5 InnoDB索引结构

7.3 MyISAM存储引擎

7.3.1 MyISAM概述

7.3.2 MyISAM文件结构

7.3.3 MyISAM核心特性

1. 表级锁

2. 不支持事务

3. 不支持崩溃恢复

4. 支持压缩表

7.3.4 MyISAM索引结构

7.4 InnoDB vs MyISAM对比 ⭐⭐⭐⭐⭐

7.4.1 核心差异对比

7.4.2 性能对比

7.4.3 存储空间对比

7.4.4 如何选择？

7.5 其他存储引擎

7.5.1 Memory引擎

7.5.2 Archive引擎

7.5.3 CSV引擎

7.5.4 Blackhole引擎

7.6 存储引擎的使用

7.6.1 指定存储引擎

7.6.2 查看存储引擎

7.6.3 转换存储引擎

7.7 存储引擎最佳实践

7.7.1 选择建议

7.7.2 性能优化

7.7.3 常见问题

7.8 本章总结

练习题