xingliuhuaMySQL-02 架构深度解析
目录
MySQL 架构深度解析
目录
1. 整体架构概览
┌─────────────────────────────────────────────────────────────────┐
│ MySQL Server │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 连接管理层 │ │
│ │ 连接器 │ 连接池 │ 身份认证 │ 权限验证 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ SQL 处理层 │ │
│ │ 查询缓存(8.0已移除) │ 解析器 │ 预处理器 │ 优化器 │ 执行器 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 存储引擎层（插件化） │ │
│ │ InnoDB │ MyISAM │ Memory │ CSV │ ... │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────┘
│
┌─────────┴─────────┐
│ 文件系统 │
│ 数据文件 | 日志文件 │
└───────────────────┘
MySQL 采用经典的 C/S 架构，分为三大层次：
- 连接管理层：处理客户端连接、认证、权限
- SQL 处理层（Server 层）：解析、优化、执行 SQL
- 存储引擎层：负责数据的存储与读取，可插拔
2. 连接管理层
2.1 连接器
客户端与 MySQL 建立连接的第一站，负责：
- TCP 握手：建立 TCP 连接
- 身份认证：验证用户名密码
- 权限加载：认证成功后，从
mysql.user表读取该用户的权限并缓存
客户端 MySQL
│ │
│──── TCP SYN ──────────►│
│◄─── TCP SYN+ACK ───────│
│──── TCP ACK ──────────►│ TCP 握手完成
│ │
│◄─── 握手包（版本/Challenge）──│
│──── 认证包（用户名+密码哈希）──►│
│◄─── OK/ERR ─────────────│ 认证完成
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看连接详情
SHOW FULL PROCESSLIST;
2.2 连接的状态
一个连接（线程）的状态流转：
Sleep → 空闲等待客户端命令
Query → 正在执行查询
Locked → 等待表锁（基本是 MyISAM，InnoDB 少见）
Sending data → 发送数据给客户端（可能是在读取数据）
Sorting result → 结果排序
Copying to tmp table → 写临时表（JOIN/GROUP BY 等）
-- 查看各状态线程数
SELECT COMMAND, STATE, COUNT(*) cnt
FROM information_schema.PROCESSLIST
GROUP BY COMMAND, STATE
ORDER BY cnt DESC;
2.3 长连接 vs 短连接
短连接：每次操作都建立新连接，开销大（TCP 握手 + 认证），不推荐高频场景。
长连接：连接建立后复用。问题：长连接使用期间内存持续增长（查询执行时临时内存在连接关闭前不释放）。
解决长连接内存泄漏：
-- 方案1：定期执行，重置连接内存（MySQL 5.7+）
RESET CONNECTION; -- 等价于重新连接，但不用重新认证
-- 方案2：控制连接生命周期（在应用层连接池设置 MaxLifetime）
-- 方案3：等待超时自动断开
SHOW VARIABLES LIKE 'wait_timeout'; -- 非交互式连接超时（默认 28800s = 8h）
SHOW VARIABLES LIKE 'interactive_timeout'; -- 交互式连接超时
3. SQL 处理层
3.1 查询缓存（Query Cache，已废弃）
MySQL 5.7 及以前有查询缓存，将 SQL + 结果缓存起来，完全相同的 SQL 直接返回缓存。
为什么 8.0 彻底移除？
- 命中率极低：SQL 必须字节级完全相同（包括大小写、空格）
- 失效代价大：表任何写操作都会使该表相关的所有缓存失效
- 全局锁竞争：多线程并发时查询缓存的锁成为瓶颈
结论：不要依赖 MySQL 查询缓存，在应用层用 Redis 做缓存。
3.2 解析器（Parser）
对 SQL 字符串进行词法分析 + 语法分析：
SELECT * FROM users WHERE id = 1
词法分析：
[SELECT] [*] [FROM] [users] [WHERE] [id] [=] [1]
语法分析（生成 AST）：
SelectStmt
├── Fields: [*]
├── From: users
└── Where: id = 1
语法错误在此阶段抛出：
SLECT * FROM users; -- ERROR 1064: You have an error in your SQL syntax
3.3 预处理器（Preprocessor）
在 AST 基础上做语义检查：
- 检查表是否存在
- 检查列是否存在
- 解析
*为具体列名 - 权限验证（部分权限在此检查）
SELECT * FROM non_exist_table; -- ERROR 1146: Table doesn't exist
3.4 优化器（Optimizer）
最核心的组件，负责生成执行计划。主要工作：
-
逻辑优化
- 条件化简（
1=1去除、IN转=） - 子查询转 JOIN
- 视图展开
- 常量传播
- 条件化简（
-
物理优化
- 选择访问方式（全表扫描 vs 索引扫描）
- 选择索引（基于统计信息，CBO 基于代价的优化器）
- JOIN 顺序优化（小表驱动大表）
- 排序优化（filesort vs 索引排序）
-- 查看优化器 trace（详细的优化过程）
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';
优化器局限性：
- 统计信息不准确时可能选错索引 → 用
USE INDEX/FORCE INDEX提示 - 复杂 JOIN 时排列组合爆炸 → 使用
optimizer_search_depth控制 - NULL 值处理有特殊逻辑
3.5 执行器（Executor）
执行器按照优化器生成的执行计划，逐步调用存储引擎接口读取/修改数据。
以 SELECT * FROM users WHERE age > 18 为例（无索引）：
- 调用 InnoDB 接口，取第一行
- 判断
age > 18，满足则放入结果集 - 重复直到取完所有行
- 返回结果集给客户端
4. 存储引擎层
存储引擎通过统一接口与 Server 层交互：
// 伪代码，展示 Handler 接口
type StorageEngine interface {
Open(table string) error
Close() error
// 读操作
IndexRead(index string, key []byte) (Row, error)
TableScan() RowIterator
// 写操作
Insert(row Row) error
Update(row Row) error
Delete(key []byte) error
// 事务
BeginTransaction() error
Commit() error
Rollback() error
}
4.1 InnoDB vs MyISAM
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ | ❌ |
| 外键 | ✅ | ❌ |
| 锁粒度 | 行锁 + 表锁 | 表锁 |
| MVCC | ✅ | ❌ |
| 崩溃恢复 | ✅（Redo Log） | 部分支持 |
| 全文索引 | ✅（5.6+） | ✅ |
| 主键 | 聚簇索引 | 非聚簇 |
| 数据文件 | .ibd |
.MYD + .MYI |
| 适用场景 | 读写均衡，事务场景 | 大量读、归档 |
5. InnoDB 内存架构
┌────────────────────────────────────────────────────────────┐
│ InnoDB 内存结构 │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Buffer Pool（缓冲池） │ │
│ │ │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌───────────┐ │ │
│ │ │ 数据页缓存 │ │ 索引页缓存 │ │ 插入缓冲 │ │ │
│ │ │ (Data Pages)│ │ (Index Pages)│ │ (Change │ │ │
│ │ └──────────────┘ └──────────────┘ │ Buffer) │ │ │
│ │ └───────────┘ │ │
│ │ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ 自适应哈希 │ │ 锁信息缓存 │ │ │
│ │ │ 索引(AHI) │ │ │ │ │
│ │ └──────────────┘ └──────────────┘ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────┐ ┌──────────────┐ ┌─────────────┐ │
│ │ Redo Log Buffer │ │ Undo 表空间 │ │ 额外内存池 │ │
│ └──────────────────┘ └──────────────┘ └─────────────┘ │
└────────────────────────────────────────────────────────────┘
5.1 Buffer Pool（缓冲池）—— InnoDB 最重要的内存区域
作用：缓存磁盘中的数据页（默认 16KB/页）和索引页，减少磁盘 I/O。
大小配置：生产环境建议设为物理内存的 60%~80%：
innodb_buffer_pool_size = 8G
# MySQL 5.7+ 支持多实例，减少锁竞争
innodb_buffer_pool_instances = 8 # 建议 = CPU 核数，最大 64
内部使用改进的 LRU 算法：
传统 LRU 的问题：全表扫描会污染 Buffer Pool（大量冷数据挤出热数据）。
InnoDB 的解决方案——冷热分区：
LRU List 被分为 New Sublist(热区) 和 Old Sublist(冷区)
默认比例：New 占 63%，Old 占 37%（由 innodb_old_blocks_pct 控制）
新读入的页先放 Old 区头部
超过 innodb_old_blocks_time（默认 1000ms）后再次被访问，才晋升到 New 区
全表扫描的页大量进入 Old 区，但因为很快扫完不会再被访问，不会晋升
-- 查看 Buffer Pool 使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 命中率（应 > 99%）
SELECT
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)
/ Innodb_buffer_pool_read_requests * 100 AS hit_rate
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) r, (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) p;
5.2 Change Buffer（写缓冲）
作用：当二级索引页不在 Buffer Pool 中时，不直接写磁盘，而是将修改记录在 Change Buffer 中，等待二级索引页被读入 Buffer Pool 时再合并（merge）。
好处：减少随机 I/O（二级索引的写入天然是随机 I/O）
适用条件：只对非唯一二级索引有效（唯一索引必须先读取页验证唯一性）
innodb_change_buffer_max_size = 25 # 占 Buffer Pool 的最大百分比，默认 25%
5.3 Adaptive Hash Index（自适应哈希索引，AHI）
InnoDB 监控热点数据页的访问模式，对频繁等值查询的索引，自动在内存中建立哈希索引，将 B+Tree 的 O(log n) 查询变为 O(1)。
innodb_adaptive_hash_index = ON # 默认开启
# 高并发场景下 AHI 的锁竞争可能成为瓶颈，可以关闭
5.4 Redo Log Buffer
事务提交前，先将 Redo Log 写入内存中的 Redo Log Buffer，然后按策略刷盘（详见日志篇）。
6. InnoDB 磁盘架构
┌────────────────────────────────────────────────────────┐
│ InnoDB 磁盘文件 │
│ │
│ 系统表空间 ibdata1 │
│ Undo 表空间 undo_001, undo_002 │
│ Temp 表空间 ibtmp1 │
│ │
│ 用户表空间（每表一个 .ibd 文件，innodb_file_per_table） │
│ mydb/orders.ibd │
│ mydb/users.ibd │
│ │
│ Redo Log ib_logfile0, ib_logfile1（环形写入） │
│ Binlog mysql-bin.000001, ... │
│ │
└────────────────────────────────────────────────────────┘
6.1 表空间（Tablespace）
# 推荐：每表独立 .ibd 文件，便于管理和回收空间
innodb_file_per_table = ON # MySQL 5.6+ 默认 ON
-- 查看表空间文件
SELECT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ROUND(DATA_FREE/1024/1024, 2) AS free_mb
FROM information_schema.FILES
WHERE FILE_TYPE = 'TABLESPACE';
6.2 页（Page）—— InnoDB 最小 I/O 单元
InnoDB 默认页大小：16KB（innodb_page_size）
一个 .ibd 文件由多个 Segment（段）组成
一个 Segment 由多个 Extent（区，64个页=1MB）组成
一个 Extent 由多个 Page（页，16KB）组成
页类型：
FIL_PAGE_INDEX 数据页/索引页（最重要）
FIL_PAGE_UNDO_LOG Undo 日志页
FIL_PAGE_INODE Segment 信息页
FIL_PAGE_IBUF_BITMAP Change Buffer 位图
...
7. 一条 SQL 的完整执行链路
以 SELECT * FROM users WHERE id = 1 为例：
1. 客户端发送 SQL → MySQL 连接器
2. 连接器：验证权限，分配线程
3. （8.0 以前）查询缓存：KEY=SQL字符串，未命中则继续
4. 解析器：词法+语法分析，生成 AST
5. 预处理器：验证表/列是否存在，解析通配符
6. 优化器：
a. 确定使用 PRIMARY KEY 索引（id 是主键）
b. 生成执行计划：走聚簇索引等值查找
7. 执行器：
a. 调用 InnoDB 接口：index_read(PRIMARY, id=1)
b. InnoDB 检查 Buffer Pool，命中则直接返回页数据
c. 未命中则从磁盘读取 .ibd 文件对应页，放入 Buffer Pool
d. 从页中提取 id=1 的行记录
8. 执行器将行记录返回给客户端
以 UPDATE users SET name='Alice' WHERE id = 1 为例：
1-6. 同 SELECT 的 1-6 步骤
7. 执行器调用 InnoDB 接口：
a. InnoDB 找到 id=1 的行（Buffer Pool 或 磁盘）
b. 加行锁（排他锁 X）
c. 记录旧值到 Undo Log（用于回滚 + MVCC）
d. 在 Buffer Pool 中修改数据页（Dirty Page）
e. 写 Redo Log Buffer（物理日志，记录"某页某偏移改为某值"）
8. 事务提交时：
a. Redo Log Buffer 刷盘（ib_logfile，WAL）
b. 写 Binlog（逻辑日志）
c. Redo Log 标记 commit
9. 后台线程异步将 Buffer Pool 中的脏页刷到磁盘（.ibd 文件）
WAL（Write-Ahead Logging）：先写日志（Redo Log），再写磁盘（数据文件）。顺序写日志比随机写数据文件快得多，这是 InnoDB 高性能写入的关键。
8. 连接池与线程模型
8.1 MySQL 线程模型
MySQL 默认采用 one-connection-per-thread 模型：每个客户端连接对应一个 OS 线程。
客户端连接 1 ── Thread 1
客户端连接 2 ── Thread 2
客户端连接 3 ── Thread 3
...
问题：连接数过多（如 10000+）时，线程切换开销大。
解决：Thread Pool（Percona/MySQL Enterprise 企业版特性）：
- 固定数量的 worker 线程池
- 连接请求排队，由 worker 处理
- 适合大量短查询的场景
8.2 连接数规划
-- 查看当前和最大连接数
SHOW STATUS LIKE 'Threads_connected'; -- 当前连接
SHOW STATUS LIKE 'Max_used_connections'; -- 历史最大
SHOW VARIABLES LIKE 'max_connections'; -- 允许最大
-- 查看等待锁的连接
SELECT * FROM information_schema.PROCESSLIST
WHERE STATE LIKE '%lock%';
连接数设置原则：
# 不是越大越好，考虑内存消耗
# 每个连接约占 256KB ~ 1MB 内存（根据查询复杂度）
# 公式：max_connections = 可用内存 / 单连接内存
max_connections = 1000 # 生产常见配置
# 预留系统账户的连接数
max_user_connections = 950
8.3 Go 应用的连接池配置
// 连接池关键参数说明
db.SetMaxOpenConns(100)
// 最大打开连接数，包括使用中和空闲的
// 超过此数的请求会排队等待
// 建议：与 MySQL max_connections 对应，留余量
db.SetMaxIdleConns(10)
// 最大空闲连接数（空闲连接池大小）
// 设太大浪费连接，设太小频繁创建/销毁连接
// 建议：根据平均并发量设置
db.SetConnMaxLifetime(time.Hour)
// 连接最大存活时间
// 用于应对 MySQL 的 wait_timeout（连接空闲超时自动断开）
// 建议：< wait_timeout，通常设 1h
db.SetConnMaxIdleTime(30 * time.Minute)
// 空闲连接最大存活时间（Go 1.15+）
// 超时的空闲连接会被主动关闭
// 建议：< wait_timeout
典型问题：invalid connection 错误
原因：MySQL 端因 wait_timeout 断开了连接，但 Go 连接池还保留着这个连接句柄。
解决：
- 设置
ConnMaxLifetime< MySQLwait_timeout - 驱动层自动重试（go-sql-driver 支持
?maxAllowedPacket=0） - 应用层做重试
小结
| 层次 | 组件 | 职责 |
|---|---|---|
| 连接管理 | 连接器 | TCP 握手、认证、权限加载 |
| SQL 处理 | 解析器 | 词法+语法分析 → AST |
| SQL 处理 | 预处理器 | 语义检查（表/列存在性） |
| SQL 处理 | 优化器 | 生成最优执行计划（CBO） |
| SQL 处理 | 执行器 | 按计划调用存储引擎接口 |
| 存储引擎 | Buffer Pool | 数据页缓存，减少磁盘 I/O |
| 存储引擎 | Change Buffer | 二级索引写入缓冲 |
| 存储引擎 | AHI | 热点数据自适应哈希索引 |
| 存储引擎 | Redo Log | WAL，保证崩溃恢复 |