MySQL 体系结构与存储引擎-优快云博客

引擎	事务支持	锁粒度	崩溃恢复	适用场景
InnoDB	✅	行级锁	支持	OLTP、高并发事务处理
MyISAM	❌	表级锁	不支持	读密集型场景（已逐渐被取代）
Memory	❌	表级锁	不支持	临时表、会话级缓存数据
Archive	❌	行级锁	支持	历史数据存储与压缩
CSV	❌	表级锁	不支持	CSV文件映射表

1. InnoDB (默认引擎)

特点：

支持完整ACID事务
行级锁定
外键支持
支持崩溃恢复
使用聚集索引（主键索引包含完整数据）

适用场景：

需要事务支持的OLTP应用
高并发读写
需要外键约束的应用

文件结构：

.ibd 文件：表空间文件（每个表单独文件）
ibdata1：系统表空间（共享表空间）
ib_logfile0/1：重做日志文件

2. MyISAM

特点：

不支持事务
表级锁定
全文索引支持
较高的读取性能
使用非聚集索引（索引和数据分离）

适用场景：

只读或读多写少的应用
需要全文索引的应用
数据仓库类应用

文件结构：

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

3. Memory (HEAP)

特点：

数据存储在内存中
表级锁定
不支持BLOB/TEXT类型
服务器重启后数据丢失

适用场景：

临时表
缓存中间结果
高速查找表

4. 其他存储引擎

引擎名称	特点	适用场景
Archive	高压缩比，只支持INSERT/SELECT	日志归档
CSV	数据以CSV格式存储	数据交换
Federated	访问远程MySQL表	分布式应用
Merge	合并多个MyISAM表	数据仓库

三、InnoDB 存储引擎深度解析

1. 核心架构组件

内存结构：

Buffer Pool：缓存表和索引数据
Change Buffer：缓存非唯一索引的变更
Adaptive Hash Index：自动构建哈希索引
Log Buffer：重做日志缓冲区

磁盘结构：

Tablespaces：系统表空间、独立表空间、通用表空间
Redo Logs：事务日志(ib_logfile)
Undo Logs：回滚日志(存储在系统表空间)

2. 关键特性

事务实现机制：

MVCC (多版本并发控制)：通过ReadView实现一致性读
Undo Log：记录修改前的数据用于回滚
Redo Log：记录修改后的数据用于恢复

锁机制：

共享锁(S锁)：读锁，允许多个事务同时读取
排他锁(X锁)：写锁，独占资源
意向锁(IS/IX)：表级锁，提高锁检查效率
记录锁(Record Lock)：锁定索引记录
间隙锁(Gap Lock)：锁定索引记录间隙
临键锁(Next-Key Lock)：记录锁+间隙锁组合

3. 缓冲池(Buffer Pool)优化

 -- 查看缓冲池状态
 SHOW ENGINE INNODB STATUS\G
 
 -- 重要配置参数
 innodb_buffer_pool_size = 8G  # 通常设为物理内存的50-75%
 innodb_buffer_pool_instances = 8  # 缓冲池实例数
 innodb_old_blocks_pct = 37  # LRU列表中old子列表占比
 innodb_old_blocks_time = 1000  # 页在old子列表停留时间(ms)

四、存储引擎选择策略

1. 选择标准

事务需求：需要事务选择InnoDB
并发性能：高并发写选择InnoDB，读多写少考虑MyISAM
特殊功能：全文索引、地理空间数据等
恢复需求：需要崩溃恢复选择InnoDB

2. 混合使用场景

 -- 创建表时指定引擎
 CREATE TABLE logs (
     id INT AUTO_INCREMENT,
     log_time DATETIME,
     message TEXT,
     PRIMARY KEY (id)
 ) ENGINE=Archive;
 
 -- 修改表引擎
 ALTER TABLE my_table ENGINE=InnoDB;

五、存储引擎性能优化

1. InnoDB 优化

 -- 配置独立表空间
 innodb_file_per_table = ON
 
 -- 调整日志文件大小
 innodb_log_file_size = 256M
 innodb_log_files_in_group = 2
 
 -- 优化IO特性
 innodb_flush_method = O_DIRECT
 innodb_flush_neighbors = 0  # SSD建议关闭
 innodb_io_capacity = 2000   # 根据IOPS能力设置

2. MyISAM 优化

 -- 键缓存配置
 key_buffer_size = 256M
 
 -- 修复表
 REPAIR TABLE my_table;
 
 -- 优化表(重建索引)
 OPTIMIZE TABLE my_table;

六、存储引擎监控与诊断

1. InnoDB 监控

 -- 查看InnoDB状态
 SHOW ENGINE INNODB STATUS\G
 
 -- 查看锁等待
 SELECT * FROM information_schema.INNODB_LOCKS;
 SELECT * FROM information_schema.INNODB_LOCK_WAITS;
 
 -- 查看缓冲池统计
 SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

2. 性能诊断工具

 -- 查看表状态
 SHOW TABLE STATUS LIKE 'table_name'\G
 
 -- 查看索引统计
 ANALYZE TABLE table_name;
 SHOW INDEX FROM table_name;
 
 -- 查看引擎相关系统变量
 SHOW VARIABLES LIKE '%innodb%';
 SHOW VARIABLES LIKE '%myisam%';

七、MySQL 8.0 存储引擎新特性

1. InnoDB 增强

原子DDL：DDL操作支持原子性
哈希索引优化：改进自适应哈希索引
临时表优化：临时表使用InnoDB引擎
JSON增强：改进JSON文档存储和查询

2. 通用表空间

 -- 创建通用表空间
 CREATE TABLESPACE `my_tablespace` 
 ADD DATAFILE 'my_tablespace.ibd' 
 ENGINE=InnoDB;
 
 -- 将表分配到表空间
 CREATE TABLE t1 (id INT) TABLESPACE `my_tablespace`;
 ALTER TABLE t2 TABLESPACE `my_tablespace`;