269 lines
9.2 KiB
Markdown
269 lines
9.2 KiB
Markdown
---
|
||
tags: [MySQL, 架构, 进程模型, Server]
|
||
create time: 2026-05-16 00:00
|
||
---
|
||
|
||
# MySQL 架构与进程模型
|
||
|
||
## 概述
|
||
|
||
理解 MySQL 的内部架构是性能调优和故障排查的前提。本文将从 Client-Server 模型出发，逐层拆解 MySQL 的四大功能层以及不同存储引擎下的线程调度机制。
|
||
|
||
## 四层架构
|
||
|
||
MySQL 遵循经典的三层 Client-Server 架构，但在服务端内部被清晰划分为四层：
|
||
|
||
```mermaid
|
||
graph BT
|
||
subgraph "应用层"
|
||
A["应用程序<br/>(Go / Java / Python)"]
|
||
end
|
||
|
||
subgraph "连接层<br/>Connection Layer"
|
||
B["Thread Pool"]
|
||
C["Authentication"]
|
||
D["Connection Management"]
|
||
end
|
||
|
||
subgraph "SQL 层<br/>SQL Layer"
|
||
E["Query Cache ⚠️ 8.0 已移除"]
|
||
F["Parser → Preprocessor"]
|
||
G["Optimizer"]
|
||
H["Executor"]
|
||
end
|
||
|
||
subgraph "存储引擎层<br/>Storage Engine"
|
||
I["InnoDB"]
|
||
J["MyISAM"]
|
||
K["Memory"]
|
||
L["Archive"]
|
||
end
|
||
|
||
subgraph "文件系统"
|
||
M["Data Files"]
|
||
N["Binlog"]
|
||
O["Redo Log"]
|
||
end
|
||
|
||
A --> B
|
||
B --> F
|
||
F --> G --> H --> I
|
||
H --> J
|
||
H --> K
|
||
H --> L
|
||
I --> M
|
||
I --> N
|
||
I --> O
|
||
```
|
||
|
||
### 各层职责
|
||
|
||
**连接层**负责处理客户端的连接请求，包括身份验证、权限校验、SSL/TLS 协商以及线程分配。MySQL 采用「一连接一线程」模型——每个客户端连接对应一个独立的服务器线程，这意味着万级并发时会产生巨大的线程开销。
|
||
|
||
> [!NOTE] 为什么高并发场景要考虑线程池？
|
||
> MySQL 原生没有内置线程池（Percona Server 提供）。当并发量超过数千时，上下文切换的开销会成为瓶颈。解决方案有三：
|
||
> - **ProxySQL / MaxScale**：前端代理层做连接复用
|
||
> - **MySQL Thread Pool Plugin**：企业版功能或 Percona 分支
|
||
> - **连接池**：在应用层控制并发连接数（如 Go 的 `sql.DB`）
|
||
|
||
**SQL 层**是整个数据库的核心，完成 SQL 语句的解析、优化和执行。这一层实现了 SQL 标准语法、查询优化策略、函数系统和事务管理。值得注意的是，**很多用户熟悉的 SQL 特性其实都在这一层实现**——比如子查询改写、JOIN 顺序优化等。
|
||
|
||
**存储引擎层**提供了可插拔的存储方案。不同的引擎对索引、锁、事务的支持各不相同。InnoDB 承担了默认的事务型负载，而 MyISAM、Memory 等引擎则针对特定场景做了优化。由于大多数生产环境只使用 InnoDB，现代运维中这一层其实非常安静。
|
||
|
||
**文件系统层**将页（Page，默认 16KB）写入磁盘。InnoDB 有自己的 Buffer Pool 来管理数据缓存，同时也依赖操作系统的 Page Cache。**Linux 内核的 I/O 调度器对 MySQL 性能有直接影响**。
|
||
|
||
## 线程模型详解
|
||
|
||
MySQL 服务启动时会创建一组后台线程，同时为每个连接分配专用线程：
|
||
|
||
```mermaid
|
||
classDiagram
|
||
class MasterThread {
|
||
+binlog dump
|
||
+purge redo log
|
||
+truncate binary log
|
||
+DD event cleanup
|
||
+change buffer merge
|
||
}
|
||
|
||
class ConnectionThread {
|
||
+parse query
|
||
+optimize
|
||
+execute
|
||
+send result
|
||
}
|
||
|
||
class WorkerThreads {
|
||
+InnoDB read worker
|
||
+InnoDB log writer
|
||
+InnoDB page cleaner
|
||
}
|
||
|
||
class ThreadPool {
|
||
<<optional>>
|
||
+acceptor thread
|
||
+worker pool
|
||
+idle stack management
|
||
}
|
||
|
||
MasterThread ..> WorkerThreads : "coordinates"
|
||
ThreadPool --> ConnectionThread : "dispatches"
|
||
```
|
||
|
||
### 关键后台线程
|
||
|
||
| 线程 | 职责 |
|
||
|------|------|
|
||
| **Master Thread** | InnoDB 专属，协调写操作、刷新脏页、合并 Change Buffer |
|
||
| **Log Thread** | 将 Redo Log Buffer 刷入 Redo Log File |
|
||
| **Page Cleaner** | 主动将脏页从 Buffer Pool 刷盘，避免崩溃恢复时间过长 |
|
||
| **Purge Thread** | 删除 Undo Log 中标记为废弃的行记录 |
|
||
| **DD Event Cleanup** | 清理数据字典中的过期对象信息 |
|
||
|
||
### 连接线程生命周期
|
||
|
||
```mermaid
|
||
stateDiagram-v2
|
||
[*] --> New: 客户端发起 TCP 连接
|
||
New --> Handshake: 握手协议 + 认证
|
||
Handshake --> Authenticated: 认证成功
|
||
Handshake --> Closed: 认证失败
|
||
Authenticated --> Sleeping: 发送命令
|
||
Sleeping --> Executing: 收到新命令
|
||
Executing --> Sending: 执行完毕
|
||
Sending --> Sleeping: 返回结果集
|
||
Sleeping --> Closed: 客户端断开 / 超时
|
||
Sleeping --> Killed: 管理员 kill 掉
|
||
```
|
||
|
||
> [!QUESTION] 什么是 MySQL 连接超时？
|
||
> 如果客户端空闲太久不发消息会发生什么？MySQL 有两个关键超时参数：
|
||
> - **`wait_timeout`**（默认 28800 秒 = 8 小时）：非交互式连接的闲置超时
|
||
> - **`interactive_timeout`**（默认 28800 秒）：交互式连接（如 mysql CLI）的闲置超时
|
||
>
|
||
> 连接池中如果未正确配置这些参数，可能出现大量 ZOMBIE 连接占满 `MaxConnections`。这就是为什么 Go 的 `sql.DB.ConnMaxLifetime` 应该设为比 wait_timeout 更短的值。
|
||
|
||
## mysqld 启动流程
|
||
|
||
```mermaid
|
||
flowchart TD
|
||
A["加载 my.cnf 配置"] --> B["初始化全局变量"]
|
||
B --> C["加载插件"]
|
||
C --> D["初始化存储引擎<br/>innodb_init()"]
|
||
D --> E["打开数据目录"]
|
||
E --> F["恢复未提交事务<br/>redo log crash recovery"]
|
||
F --> G["启动后台线程"]
|
||
G --> H["监听端口"]
|
||
H --> I["就绪 ✨"]
|
||
```
|
||
|
||
启动过程中的关键阶段：
|
||
|
||
1. **配置加载**：按优先级读取配置文件 `/etc/my.cnf` → `/etc/mysql/my.cnf` → `~/.my.cnf`，命令行参数优先级最高
|
||
2. **引擎初始化**：InnoDB 会在启动时进行 Crash Recovery——重放 Redo Log 恢复到一致状态。如果数据文件损坏严重，这一步会卡住或报错
|
||
3. **Crash Recovery**：innodb_force_recovery 参数可以在恢复期间限制操作级别（1~6），用于紧急导出数据
|
||
|
||
## 核心架构图
|
||
|
||
```mermaid
|
||
graph TB
|
||
subgraph "连接管理"
|
||
C1["Accept Thread"] --> C2["Connection Pool"]
|
||
C2 --> C3["Read Command"]
|
||
end
|
||
|
||
subgraph "SQL 处理流水线"
|
||
C3 --> P1["Syntax Parser"]
|
||
P1 --> P2["AST Semantic Check"]
|
||
P2 --> P3["Preprocessing"]
|
||
P3 --> P4["Optimization"]
|
||
P4 --> P5["Execution"]
|
||
end
|
||
|
||
subgraph "InnoDB 执行路径"
|
||
P5 --> I1["Buffer Pool Read Page"]
|
||
I1 --> I2{"Hit?"}
|
||
I2 -->|"Yes"| I3["Return Result"]
|
||
I2 -->|"No"| I4["Read from Disk"]
|
||
I4 --> I1
|
||
I5["Change Buffer"] -.-> I1
|
||
end
|
||
|
||
C1 --> C3
|
||
style P4 fill:#FF9F43,color:#000
|
||
style I1 fill:#00B6BC,color:#fff
|
||
style I3 fill:#00D866,color:#fff
|
||
```
|
||
|
||
## 实战：在线诊断工具
|
||
|
||
在生产环境中快速定位问题，需要掌握以下几组命令行工具。它们分别作用于不同的架构层级：
|
||
|
||
### 1. SHOW PROCESSLIST — SQL 层视角
|
||
|
||
```sql
|
||
-- 最基础的线程状态查看
|
||
SHOW FULL PROCESSLIST;
|
||
|
||
-- 等效的 SQL 查询（可结合 WHERE 过滤）
|
||
SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep';
|
||
```
|
||
|
||
每个线程在 `PROCESSLIST` 中表现为一条记录，关键列：
|
||
|
||
| 列 | 含义 | 常见值 |
|
||
|----|------|--------|
|
||
| `ID` | 线程 ID，`KILL id` 使用 | |
|
||
| `State` | 当前执行阶段 | `Sending data`, `Creating sort index`, `Locked` |
|
||
| `Time` | 当前状态的持续秒数 | > 5s 需警惕 |
|
||
| `Info` | 正在执行的 SQL | NULL 表示空闲 |
|
||
|
||
> [!TIP] State 解读口诀
|
||
> - **`Waiting for handler lock`** → 行锁竞争，配合 `Innodb_row_lock_waits` 排查
|
||
> - **`Creating sort index`** → 大 ORDER BY/GROUP BY，检查是否缺少索引
|
||
> - **`Sending data`** → 不仅是在"发送结果"，而是**正在扫描和生成数据**，往往是最耗时的阶段
|
||
|
||
### 2. SHOW STATUS — 全局计数器
|
||
|
||
```sql
|
||
-- 查看慢查询总数（取决于 slow_query_log 配置）
|
||
SHOW GLOBAL STATUS LIKE 'Slow_queries';
|
||
|
||
-- 当前并发连接数 vs 上限
|
||
SHOW GLOBAL STATUS LIKE 'Threads_connected';
|
||
SHOW GLOBAL STATUS LIKE 'Max_connections';
|
||
|
||
-- InnoDB 缓冲池命中率（接近 100% 为佳）
|
||
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
|
||
```
|
||
|
||
> [!QUESTION] 为什么 Buffer Pool 命中率不是越高越好？
|
||
> 理论上 Hit Rate 越接近 100% 越好，但当数据集小于 Buffer Pool 时，Hit Rate 会固定在 100% 以上（因为缓存了多次）。生产环境中 **98%~99%** 已经足够优秀，不必盲目增大 `innodb_buffer_pool_size`。
|
||
|
||
### 3. Performance Schema — MySQL 5.7+ 的深层监控
|
||
|
||
`performance_schema` 是 MySQL 内置的性能采集框架，比 `SHOW STATUS` 更细粒度：
|
||
|
||
```sql
|
||
-- 当前等待事件（锁、I/O、网络）TOP 10
|
||
SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT
|
||
FROM performance_schema.events_waits_summary_global_by_event_name
|
||
ORDER BY COUNT_STAR DESC LIMIT 10;
|
||
|
||
-- 最近 10 条耗时 > 1s 的语句
|
||
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_ms
|
||
FROM performance_schema.events_statements_summary_by_digest
|
||
WHERE AVG_TIMER_WAIT > 1e12
|
||
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;
|
||
```
|
||
|
||
> [!NOTE] Performance Schema 的性能代价
|
||
> 开启 PS 会带来约 **5%~15%** 的 CPU 开销。建议在低峰期采样分析，或仅对目标线程启用 instrumentation。
|
||
|
||
---
|
||
|
||
## 关联笔记
|
||
|
||
- [[hhs/GORM/01-安装与初始化]] — GORM 连接 MySQL 的配置参数
|
||
- [[hhs/Redis/01-安装与部署]] — Redis 单线程 vs MySQL 多线程模型对比
|