MySQL 体系结构与一条 SQL 的执行旅程

本文是 MySQL 系统学习系列的 L1 入门篇。前置：无。学完可以配合面试题食用：InnoDB 存储引擎架构

为什么先讲体系结构

写 SQL 的人多，能说清一条 SQL 在 MySQL 内部经历了什么的人少。这个差别在排查问题时会直接暴露：慢查询到底是网络等待还是执行慢？为什么 8.0 关了查询缓存反而更快？长事务为什么会拖垮整个连接池？这些问题的答案都在体系结构里。

先把 MySQL 拆成两层看：Server 层负责"听懂并决策"，存储引擎层负责"真正存取数据"。类比餐厅：Server 层是前台和服务员，接单、看菜单、决定怎么做；InnoDB 是后厨，管火候和食材。你点的菜不合口味（SQL 写错语法），前台直接打回；菜怎么做才最快（用哪个索引），前台定不了，需要结合后厨的存货情况（统计信息）一起算。

Server 层的五个组件

一条 SQL 进来，按顺序过五道关：

mermaid

flowchart TD
    A[客户端<br/>JDBC / mysql CLI] -->|建立连接| B[连接器<br/>身份认证 / 长短连接]
    B --> C{分析器}
    C -->|词法+语法解析| D[优化器<br/>选索引 / 决定 join 顺序]
    D --> E[执行器<br/>调用存储引擎接口]
    E --> F[InnoDB / MyISAM<br/>存储引擎层]
    F -->|返回行数据| E
    E -->|结果集| A

连接器：负责 TCP 握手、身份认证、维持连接。mysql -hxxx -uxxx -p 敲下去，第一站就是它。连接分长连接（连接复用，生产环境标配）和短连接（用完即断，频繁建连开销大）。连接是内存资源，wait_timeout 默认 8 小时自动断开闲置连接；连接数上限由 max_connections 控制，一般设 500-2000。

查询缓存：8.0 之前有个模块，SQL 文本完全一致直接返回缓存结果。听着美好，实际是负优化：任何一张表的任何一次写操作都会把涉及该表的缓存全部清空。稍有写入的业务，缓存命中率趋近于零，还要白白付出维护开销。MySQL 8.0 直接移除了这个模块，想要缓存层，请在应用层做 Redis。

分析器：做词法分析和语法分析。把 SELECT * FROM t 拆成关键字和标识符，检查语法是否合法。报错 You have an error in your SQL syntax 就是这一步拦下来的。

优化器：决定"怎么执行"。表上有多个索引选哪个、多表 join 先驱动哪张表、WHERE a=1 OR b=2 走不走索引，都是它算的。它依据的是统计信息（cardinality），不是每次都精确计算，所以偶尔会选错索引——这是慢查询的常见来源之一。

执行器：先校验对这张表有没有权限，然后按优化器的方案调用存储引擎接口取数据。比如走主键查一行，执行器对 InnoDB 说"给我取 id=5 这行"，拿到后判断是否符合剩余条件，符合就放进结果集。

Server 层和 InnoDB 层的分界线

这是理解 MySQL 的关键分界：执行器之前的一切，与数据怎么存无关；执行器调用存储引擎接口之后，才进入 InnoDB 的地盘。

职责	归属
连接管理、语法解析、权限校验	Server 层
优化器选执行计划	Server 层（依赖引擎提供的统计信息）
事务 ACID、MVCC、锁	InnoDB 层
Buffer Pool、redo/undo log	InnoDB 层

同一张表换个引擎，Server 层代码一行不改，照样能跑——这就是插件式引擎架构的意义。binlog 归 Server 层（所有引擎共用），redo log 归 InnoDB 层，两份日志的分工与两阶段提交在日志三件套那篇详解，这里先记结论：写操作两份日志都要记，靠两阶段提交保证一致。

一条 UPDATE 的旅程（简版）

UPDATE user SET age = 30 WHERE id = 5 进来后，Server 层流程和查询一样：连接、分析、优化（发现可以走主键）、执行器开始干活。真正复杂的是 InnoDB 内部这段：

mermaid

sequenceDiagram
    participant E as 执行器
    participant BP as Buffer Pool
    participant I as InnoDB
    participant B as binlog(Server层)
    E->>I: 取 id=5 这一行
    I->>BP: 数据页在内存吗？
    alt 页不在内存
        I->>I: 从磁盘读入数据页
    end
    I->>E: 返回旧行
    E->>I: 更新 age=30
    I->>I: 写 undo log（记录旧值，供回滚和 MVCC）
    I->>I: 写 redo log（prepare 状态）
    I->>B: 写 binlog
    B-->>I: 写入成功
    I->>I: redo log 置为 commit 状态
    I-->>E: 更新完成

几个关键点：数据页先在 Buffer Pool 里改，不直接写磁盘（随机写太慢）；redo log 先记下来"我改了什么"，崩溃后靠它恢复，这叫 WAL（Write-Ahead Logging）；旧值记进 undo log，回滚用它，MVCC 读旧版本也用它。redo 和 binlog 的两阶段提交（prepare/commit 两个状态）是保证两份日志一致的核心机制，细节留到日志篇展开。

动手实操：看清连接和 SQL 耗时

光看图不过瘾，上命令验证。

1. 看当前连接在干什么：

bash

# 客户端 A 执行一个慢 SQL 的同时，客户端 B 观察
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+------------+---------------------------+
| Id | User | Host            | db   | Command | Time | State      | Info                      |
+----+------+-----------------+------+---------+------+------------+---------------------------+
|  5 | root | localhost       | test | Query   |   12 | User sleep | select sleep(30) from t   |
|  8 | root | localhost:52130 | test | Sleep   |  120 |            | NULL                      |
+----+------+-----------------+------+---------+------+------------+---------------------------+

Command=Query 且 Time 持续增长，说明这条 SQL 正在执行且已经跑了 12 秒；Command=Sleep 是空闲连接，占着 max_connections 的名额。如果 Sleep 连接大量堆积，查两件事：应用端连接池的空闲回收配置，以及是否有长事务没提交（事务未提交时连接必须保持，后面 MVCC 篇会讲它对快照的影响）。

2. 用 performance_schema 拆解一条 SQL 各阶段耗时：

sql

-- 打开语句阶段跟踪（默认可能未开）
UPDATE performance_schema.setup_instruments
SET enabled = 'YES', timed = 'YES'
WHERE name LIKE 'stage/%';

UPDATE performance_schema.setup_consumers
SET enabled = 'YES' WHERE name LIKE 'events_stages%';

-- 跑一条待分析的 SQL（换个会话执行）
SELECT * FROM test.t WHERE non_index_col = 'abc';

-- 查看各阶段耗时
SELECT event_name, work_completed, TIMER_WAIT/1e12 AS time_sec
FROM performance_schema.events_stages_current
WHERE thread_id IN (
  SELECT thread_id FROM performance_schema.threads
  WHERE processlist_info LIKE '%non_index_col%'
);

stage/sql/Preparing、stage/sql/executing 等每个阶段的秒数一目了然。如果 executing 占了 99%，说明是引擎层慢（缺索引或扫描量大）；如果 send data 阶段异常长，可能是结果集太大或网络慢。这个定位思路比盲目加索引靠谱得多。

information_schema / performance_schema / sys 三库定位

排查问题时这三个库是主要情报来源，分工记住一句话：看元数据去 information_schema，看运行时指标去 performance_schema，嫌后者难查用 sys。

information_schema：表的元数据。表结构、索引、分区信息、每个表的行数估算。比如 SELECT * FROM information_schema.INNODB_TRX 看当前活跃事务。
performance_schema：运行时埋点。SQL 各阶段耗时、锁等待、内存使用、IO 统计。上面实操用的就是它。
sys：官方提供的视图层，把 performance_schema 的原始表翻译成人话。sys.statements_with_full_table_scans 直接列出全表扫描的语句，比手写 SQL 查埋点表友好得多。

常见误区与小结

查询缓存移除不是因为缓存没用，而是它的失效粒度太粗（表级），有写入的业务基本白搭——想要缓存去应用层做。
优化器不是神，它基于统计信息做选择，统计信息过期就会选错索引，ANALYZE TABLE 能重新采样。
"执行慢"要拆开看：可能慢在引擎层扫数据，也可能慢在 Server 层传结果，用 performance_schema 分阶段定位，别一上来就加索引。
UPDATE 不是改完磁盘才返回。数据页在 Buffer Pool 改，redo log 保证崩溃可恢复，写磁盘是后台异步刷的——这是 MySQL 写性能高的前提。
binlog 和 redo log 是两层各一份日志，不是冗余设计：binlog 给所有引擎做复制和恢复，redo log 是 InnoDB 的崩溃恢复机制，职责不同。

本文是 MySQL 系统学习系列的地基：Server 层 / InnoDB 层的分界、日志的分工，后面所有篇目（索引、事务、MVCC、锁）都建立在这个结构上。下一篇讲 B+ 树索引的内部实现，为什么 InnoDB 选它而不是 B 树或哈希表。

参考

参考：MySQL 8.0 官方参考手册 Chapter 8 "Optimization"（SQL 解析与执行流程）；《高性能 MySQL》第 1 章 MySQL 架构史；MySQL 8.0 源码 sql/sql_parse.cc。

MySQL 体系结构与一条 SQL 的执行旅程 ​

为什么先讲体系结构 ​

Server 层的五个组件 ​

Server 层和 InnoDB 层的分界线 ​

一条 UPDATE 的旅程（简版） ​

动手实操：看清连接和 SQL 耗时 ​

information_schema / performance_schema / sys 三库定位 ​

常见误区与小结 ​

参考 ​