以下以 MySQL 8.0 InnoDB 引擎 为例，详细解析一条 SQL 语句（如 SELECT * FROM users WHERE id = 100）的完整执行过程。

Client → [连接器] → [查询缓存（8.0已移除）] → [分析器] → [优化器] → [执行器] → [存储引擎（InnoDB）]

执行过程及任务：

  1. 连接层（建立连接、认证请求、权限验证）

  2. 服务层（缓存、解析器、优化器、执行器）

    1. 分析器： 词法 + 语法分析 = 生成解析树
    2. 优化器： 逻辑优化 + 物理优化 = 生成执行计划
    3. 执行器： 调用存储引擎接口

  3. 存储引擎层（数据存储和检索，表空间、数据页16K、索引、事务日志）

  4. 数据文件

MySQL SQL 执行流程（以 SELECT * FROM users WHERE id = 100; 为例）:

flowchart TD
    A[客户端发送 SQL] --> B[连接器
（Connection Manager）]
    B --> C{用户认证 & 权限检查}
    C -->|失败| D[返回错误]
    C -->|成功| E[查询缓存
（MySQL 8.0 已移除）]
    E -->|命中缓存| F[直接返回结果]
    E -->|未命中/无缓存| G[解析器
（Parser）]
    G --> H[词法分析 + 语法分析
生成 AST（抽象语法树）]
    H --> I{语法是否正确?}
    I -->|否| J[返回 'Syntax Error']
    I -->|是| K[预处理器
（Preprocessor）]
    K --> L[检查表/列是否存在
解析视图、别名等]
    L --> M[优化器
（Optimizer）]
    M --> N[生成执行计划
• 选择索引
• 决定 JOIN 顺序
• 估算成本]
    N --> O[执行器
（Executor）]
    O --> P[调用存储引擎 API
（如 InnoDB）]
    P --> Q[InnoDB 存储引擎]
    Q --> R{是否命中 Buffer Pool?}
    R -->|是| S[从内存返回数据]
    R -->|否| T[从磁盘读取页 → 加载到 Buffer Pool]
    T --> S
    S --> U[执行器处理行数据
• 权限二次检查
• 过滤、排序、聚合等]
    U --> V[返回结果给客户端]

连接器（Connector）

客户端通过 TCP/IP 或 socket 连接到 MySQL。

  • 验证用户名和密码；

  • 检查用户是否有权限连接；

  • 获取该用户的所有权限（缓存在本次连接生命周期内）；

  • 维持连接（可通过 wait_timeout 控制空闲超时）。

    1
    2
    -- 所有活跃连接
    SHOW PROCESSLIST;

网络通信模型

  • 基于 TCP/IP 或 Unix Socket；

  • 使用 单线程 accept + 多线程（或线程池）处理连接（可通过 thread_handling 参数配置）；

  • 每个连接分配一个独立的 THD（Thread Handler Descriptor） 结构体，代表一个会话上下文。

认证流程

  1. 客户端发送 COM_CONNECT 包；

  2. 服务端生成随机 scramble 字符串；

  3. 客户端用密码 + scramble 计算 token = SHA1(SHA1(password)) XOR SHA1(scramble + SHA1(SHA1(password)))；

  4. 服务端比对 token 是否匹配（避免明文传密码）；

  5. 若匹配，加载用户权限到 THD->security_ctx 中。

连接资源

  • 每个连接独占：

    • 会话内存（如 tmp_table_size 分配的临时表空间）
    • 线程栈（默认 256KB~1MB）
    • 状态变量副本

  • 受 max_connections 限制（默认 151）

  • 使用 连接池避免频繁建立连接；

  • 启用 mysql_native_password 或更安全的 caching_sha2_password 认证插件。

缓存（Cache）

也称为查询缓存，存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。

MYSQL5.6默认是关闭缓存的，并且在 8.0 直接被移除了，因为缓存代价过大。

1
2
3
4
5
6
7
8
9
10
11
-- 查看缓存配置：
show variables like 'have_query_cache';

-- 查看是否开启：
show variables like 'query_cache_type';

-- 查看缓存占用大小：
show variables like 'query_cache_size';

-- 查看缓存状态信息：
show status like 'Qcache%';

缓存失效场景：

1、查询语句不一致（必须完全一致，包括参数）。

2、查询语句中含有一些不确定的值时。比如 now()、current_date()、curdate()、curtime()、rand()、uuid()等。

3、不使用任何表查询。如 select ‘A’;

4、查询 mysql、information_schema 或 performance_schema 数据库中的表时，不会走查询缓存。

5、在存储的函数，触发器或事件的主体内执行的查询。

6、如果表更改，则使用该表的所有高速缓存查询都变为无效并从缓存中删除，这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句改变，如 insert、update、delete、truncate rable、alter table、drop table、drop database。

上面的失效场景可以看出缓存是很容易失效的，所以如果不是查询次数远大于修改次数的话，使用缓存不仅不能提升查询效率还会拉低效率（每次读取后需要向缓存中保存一份，而缓存又容易被清除）

分析器（Parser）

解析SQL字符串，验证其合法性。

然后将 SQL 字符串转换为结构化的内部表示

词法分析

使用 有限状态自动机 扫描输入字符串，输出 Token 流：如 SELECT → SYM_SELECT，users → SYM_IDENT；

处理引号、注释、分隔符等。

将 SQL 字符串拆分为“词元”（tokens）：

  • SELECT 、FROM 等关键字
  • 通配符* 、运算符（=）
  • 标识符（users）、各种字段
  • id, 100 标识符和常量

语法分析

  • 根据 MySQL 语法规则，验证 SQL 是否合法；

  • 根据 Token 流和状态表，构建 解析树（Parse Tree），通常表示为 SELECT_LEX + Item 树。

  • 不检查表/列是否存在（语义检查在后续阶段），错误会直接抛出异常（如 ER_PARSE_ERROR）。

例如：SELECT * FROM t ，生成 SELECT_LEX 对象，包含：

  • select_list: Item_field(*)
  • table_list: TABLE_LIST("t")

SELECT_LEX 代表一个 SELECT 语句的结构体，包含 FROM、WHERE、ORDER BY 等子句。

Item 树：表达式树，如 Item_field("name")、Item_func_eq(Item_field("id"), Item_int(100))。

优化器（Optimizer）

生成代价最低的执行计划，用最少的资源（I/O、CPU、内存）最快得到结果。

  • 逻辑优化（等价变换）

  • 物理优化（选择执行计划）

  • 成本估算（Cost Model）

以下为优化流程的两阶段：

逻辑优化

  • 消除无用条件：WHERE 1=1 AND id=100 → WHERE id=100

  • 常量折叠：WHERE id = 50 + 50 → WHERE id = 100

  • 常量传播：WHERE a=1 AND b=a → b=1

  • 谓词下推：将 WHERE 条件推到 JOIN 内部

  • 子查询优化：

    • 转为 Semi-Join
    • 物化（Materialization）
    • IN → EXISTS 转换

  • 视图合并（View Merging）

物理优化

  • 选择访问路径：

    • 全表扫描（Table Scan）
    • 索引扫描（Index Scan）
    • 索引覆盖（Covering Index）
    • Index Skip Scan（8.0+）

  • 选择连接算法（JOIN ）：

    • Nested-Loop Join (NLJ)：默认
    • Hash Join（8.0.18+）：用于无索引等值 JOIN
    • Batched Key Access (BKA)：优化 NLJ 的磁盘访问

  • 确定连接顺序：A JOIN B JOIN C → 哪个表先驱动（如 A JOIN B 还是 B JOIN A）？

    • 使用 贪心算法（如 Greedy Search） 或 动态规划（小表时）
    • 考虑 STRAIGHT_JOIN 强制顺序

  • 选择索引：如果有多个索引，选哪个代价最低？

  • 排序与分组优化：

    • 是否使用索引避免 filesort
    • 是否使用临时表

成本估算

基于统计信息（行数、索引基数、数据分布）估算 I/O + CPU 成本。选择总成本最低的计划

1
2
3
SELECT u.name, o.total 
FROM users u JOIN orders o ON u.id = o.user_id 
WHERE u.age > 30;

对此SQL，优化器可能决定：

  • 先用索引扫描 users 表（WHERE age > 30）
  • 对每个匹配用户，用 user_id 索引查找 orders
  • 而非全表扫描 orders 再 JOIN

执行器（Executor）

按执行计划调用存储引擎，处理结果并返回给客户端。

  1. 执行计划驱动

    根据优化器生成的执行计划，逐行调用存储引擎接口，逐表访问数据；

    • 调用存储引擎接口（如 ha_index_read()、ha_rnd_next()）。

    • 每个操作符（如 TableScan、IndexLookup、Filter）实现 iterator->Read() 接口；

    • 执行器通过 嵌套调用 获取下一行；

  2. 表达式计算与过滤

    执行 WHERE、SELECT 中的函数计算（如 UPPER(name)），过滤不满足条件的行。

  3. 权限检查（二次验证）

    检查用户是否有权限访问具体列（列级权限）。

  4. 事务与结果管理

    管理事务上下文（开始、提交、回滚）；

    • 将结果行通过 协议层（Protocol） 发送给客户端；

    • 支持流式返回（无需全加载到内存）。

    • 执行器维护 事务状态（THD->transaction）；

    • 调用 trans_begin()、trans_commit()，处理自动提交（autocommit=1） 和 显式事务。

  5. 返回结果集给客户端

    • 行数据通过 Protocol 类发送给客户端（如 Protocol_text）；
    • 支持流式返回（无需全加载到内存）。

  6. 错误与异常处理

    处理存储引擎返回的错误（如死锁、锁等待超时）。

示例：

执行计划要求“用主键索引查 users 表”：

  1. 执行器调用 InnoDB 的 index_read 接口；
  2. InnoDB 返回一行数据；
  3. 执行器检查列权限；
  4. 执行器将 name 字段发送给客户端；
  5. 循环直到无更多行。

存储引擎（Engine）

  • 管理 Buffer Pool（缓存数据页和索引页）；

  • 处理索引查找（B+ 树）；

  • 管理事务（MVCC、Undo Log、Redo Log）；

  • 加锁（行锁、间隙锁、临键锁）；

  • 写入磁盘（通过 WAL 崩溃恢复：Write-Ahead Logging）。

存储结构

  • 表空间（Tablespace）：.ibd 文件（或系统表空间）

  • 页（Page）：16KB 单位，最小 I/O 单元

  • B+ 树索引：

    • 聚簇索引（Clustered Index）：主键索引，叶子节点存完整行
    • 二级索引（Secondary Index）：叶子节点存主键值

  • 行格式（Row Format）：COMPACT / DYNAMIC，支持变长字段溢出

缓存机制（Buffer Pool）

  • LRU 列表缓存数据页和索引页；

  • 多个后台线程：

    • Page Cleaner：刷脏页
    • LRU Manager：管理冷热分离（避免全表扫描污染缓存）

并发控制

  • MVCC（多版本并发控制）：

    • 每行包含 DB_TRX_ID（最后修改事务ID）
    • 通过 Undo Log 构建历史版本
    • Read View 决定可见性

  • 锁系统：

    • 记录锁（Record Lock）：锁索引记录
    • 间隙锁（Gap Lock）：锁索引间隙
    • 临键锁（Next-Key Lock）：记录 + 间隙（默认 RR 隔离级别）

核心日志

Undo Log（回滚日志）、Redo Log（重做日志） 和 Binlog（二进制日志） 是三大核心日志，分别服务于事务回滚、崩溃恢复和主从复制/数据恢复。

Bin Log（记录日志）

  1. 主从复制（Replication）：主库将 Binlog 发送给从库重放；

  2. 基于时间点的数据恢复（Point-in-Time Recovery）；

  3. 审计与变更追踪。

底层原理：

  • 由 Server 层（非存储引擎）生成；

  • 记录的是 逻辑 SQL 语句或行变更；

  • 只有成功提交的事务才会写入 Binlog（与 Redo Log 的两阶段提交协同）。

  • 刷盘策略由 sync_binlog 控制：

    • 1（最安全）：每次事务提交都刷盘 → 保证主从一致
    • N：每 N 次事务刷一次盘 → 性能提升，可能丢最多 N 个事务

三种格式：

格式 内容 优点 缺点
STATEMENT 原始 SQL 语句
UPDATE users SET age=30 WHERE id=1;		 日志小 非确定性函数（如 NOW()）可能导致主从不一致
ROW（推荐） 每一行的变更前/后值
“UPDATE: users (id=1, before: age=25,after: age=30)”		 安全、精确 日志较大
MIXED 自动切换（默认 STATEMENT，必要时转 ROW） 折中 行为复杂

  • 基于SQL语句的复制STATEMENT (statement-based replication,SBR)
    记录每一条修改数据的sql（减少了binlog日志量，节约了IO，提高性能。但是必须保证所有SQL顺序执行。可能会出现特定函数无法复制的问题）

  • –基于行的复制(row-based replication,RBR)
    记录所有变更的行数据（会产生大量的日志内容。但不会出现某些情况下存储过程、函数或trigger的调用和触发无法被正确复制的问题）

  • –混合模式复制(mixed-based replication,MBR)。
    Statement与Row的结合一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog

1
2
3
4
5
6
#获取binlog文件列表
mysql> show binary logs;
#查看指定binlog文件的内容
mysql> show binlog events in ‘mysql-bin.000001’;
#通过mysqlbinlog命令可以查看binlog的内容
[root@localhost ~]# mysqlbinlog  /home/mysql/binlog/binlog.000003  | more

写入时机：新binlog创建的条件

  • 事务提交时，在 Redo Log 之后（通过 两阶段提交 保证一致性）；

  • 当停止或重启服务器时，服务器会把日志文件记入下一个日志文件，Mysql会在重启时生成一个新的日志文件，文件序号递增；

  • 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）

  • 调用命令，日志被刷新时，会新生成一个日志文件。如：flush logs

存储结构和配置:

  • 文件名：mysql-bin.000001, mysql-bin.000002 …

  • 索引文件：mysql-bin.index 记录所有 Binlog 文件名；

  • 自动轮转：达到 max_binlog_size（默认 1GB）后新建文件；

  • 可手动清理：PURGE BINARY LOGS TO 'mysql-bin.000010';

  • 可长期保留（用于 PITR 恢复）；

  • 可设置自动过期：expire_logs_days（8.0 用 binlog_expire_logs_seconds）

  • 可开启/关闭：log_bin = ON/OFF，关闭后无法做主从复制或基于时间点恢复！

Redo Log（重做日志）

确保事务的持久性（Durability）：即使数据库宕机，已提交的事务数据也不会丢失。

redo log记录的是事务执行修改后的新数据，主要保证事务的持久性和提高事务提交效率。

数据库发生意外时用来进行数据恢复，且只能恢复到上次的记录（没有多个版本记录）

底层原理：

  • WAL（Write-Ahead Logging）机制：先写日志，再写磁盘数据。

  • 当修改数据时，InnoDB 先将变更写入 Redo Log Buffer，再异步刷盘；

  • 即使 Buffer Pool 中的脏页还没刷到磁盘，只要 Redo Log 已持久化，崩溃后就能恢复

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，这时候buffer pool中的数据页与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据。

如果这时发生非正常的DB服务重启，这些数据还在内存，并没有同步到磁盘文件中，也就会发生数据丢失。

如果当buffer pool 中的数据页变更结束后，把相应修改记录到redo log，那么当DB服务发生宕机，进行恢复的时候，可以根据redo log的记录内容，重新持久化刷新到磁盘文件，保持数据的一致性。

内容和写入时机：

  • 记录的是 “物理日志”：在某个数据页上做了什么修改，所以事务执行过程中，每次修改数据都会生成 Redo Log（写入内存 Buffer）；

  • 不关心 SQL 语义，只记录页级别的变更（面向存储引擎）。

  • 仅用于 崩溃恢复，不用于数据恢复或复制。恢复完成后，日志可被覆盖

  • 刷盘策略由 innodb_flush_log_at_trx_commit 控制：

    • 1（默认）：每次事务提交，日志刷盘 → 最安全（ACID）
    • 2：每次提交写 OS 缓存，每秒刷盘 → 少量性能提升，OS 崩溃会丢 1 秒数据
    • 0：每秒写 OS 缓存并刷盘 → 性能最高，MySQL 崩溃会丢 1 秒数据

redo log的组成：

  • redo log buffer 日志缓存：存在于内存中，容易发生丢失。

  • redo log file 日志文件：存在于磁盘中，不容易发生丢失。

从redo log buffer写日志到磁盘的redo log file中，过程如下：

为了确保日志每次都能写入到事务日志文件，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作

存储结构和配置：

  • 文件名：ib_logfile0、ib_logfile1 …（默认 2 个，可配置）

  • 循环写入：日志文件是固定大小的环形缓冲区（默认 48MB × 2）；

  • Checkpoint 机制：当 Redo Log 写满时，会触发脏页刷盘，释放日志空间。

  • 可配置文件数量、大小：innodb_log_files_in_group、innodb_log_file_size

  • 不能关闭（InnoDB 必须启用）

Undo Log（回滚日志）

undo log记录的是数据更新前的样子，主要保证事务的原子性。

  1. 事务回滚（Rollback）：撤销未提交事务的修改；

  2. MVCC（多版本并发控制）：为其他事务提供一致性读视图（快照读）。

数据库发生意外，或事务异常时用来进行数据恢复，可以恢复到指定版本（有多个版本记录）

底层原理：

  • 每次修改数据前，先将旧值保存到 Undo Log；

  • 事务回滚时，通过 Undo Log 逆向操作恢复数据；

  • 读操作（如 SELECT）通过 Undo Log 构建历史版本，实现“读不加锁”。

  • 写入位置：系统表空间或独立 Undo 表空间（8.0 默认独立）；

  • 异步刷盘，不强制每次事务提交都刷盘（由后台线程处理），由 Purge 线程 在确认无事务需要后异步清理。

记录的是 “逻辑日志”，包含：

  • 旧数据值
  • 事务 ID（trx_id）
  • 回滚指针（roll_ptr），指向更早版本

存储结构和配置：

  • MySQL 8.0 起，Undo Log 默认存放在 独立的 Undo 表空间（undo_001, undo_002）；

  • 以 段（Segment） 形式组织，属于 InnoDB 内部结构；

  • 与普通表一样，存于 B+ 树页中。

  • 可配置独立 Undo 表空间数量：innodb_rollback_segments

  • 可指定 Undo 表空间文件路径（8.0+）

  • 不能关闭（InnoDB 必须启用）

三大日志协同

为保证 Redo Log 和 Binlog 的一致性，MySQL 采用 两阶段提交：

  1. Prepare 阶段：InnoDB 写 Redo Log，状态为 PREPARE

  2. Commit 阶段：

    • Server 写 Binlog
    • InnoDB 写 Redo Log，状态为 COMMIT

假设先写 Binlog 再写 Redo Log： 如果写完 Binlog 后崩溃，Redo Log 没写主库回滚，但从库已执行导致 主从不一致。两阶段提交确保：要么都成功，要么都失败。

特性 Redo Log Undo Log Binlog
所属层 InnoDB 存储引擎 InnoDB 存储引擎 MySQL Server 层
目的 崩溃恢复 事务回滚 + MVCC 主从复制 + 数据恢复
日志类型 物理日志（页级） 逻辑日志（逆向操作） 逻辑日志（SQL 或 行变更）
写入时机 修改数据时（内存） 修改数据前 事务提交后
是否持久化 可控（innodb_flush_log_at_trx_commit） 异步 可控（sync_binlog）
文件名 ib_logfile* undo_*（8.0+） mysql-bin.*
是否循环写 是 否（Purge 清理） 否（轮转文件）
可关闭 不可 不可 可（但失去复制/恢复能力）
用于复制 否 否 是

实践配置建议：

  1. Redo Log：

    • 生产环境设 innodb_flush_log_at_trx_commit = 1
    • 适当增大 innodb_log_file_size（如 1~4GB），减少 checkpoint 压力

  2. Undo Log：

    • MySQL 8.0 启用独立 Undo 表空间；
    • 监控长事务，避免 Undo Log 膨胀

  3. Binlog：

    • 开启（log_bin = ON），格式设为 ROW；
    • 设置 sync_binlog = 1 + expire_logs_seconds = 604800（7天）

示例详解（Demo）

此三者协作流程：

  • 分析器：读懂 SQL，输出结构化查询对象（Item 树）；

  • 优化器：想出最优解法，怎么执行最快？输出执行计划（QEP）；

  • 执行器：动手干活并交差。返回结果集（行数据）。

示例 SQL: SELECT name FROM users WHERE id = 100

  1. [分析器]：词法/语法分析输出树：SELECT_LEX + Item树（id=100）
  2. [优化器]：发现 id 是主键，决定使用主键索引扫描输出执行计划：access_type=const, key=PRIMARY
  3. [执行器]：调用 InnoDB: “用 PRIMARY 索引查 id=100”，InnoDB 返回行执行器提取 name 字段，发送给客户端

查询数据

flowchart TD
    A[客户端发送 SELECT] --> B[连接器
（认证 + 权限）]
    B --> C[解析器
（生成 AST）]
    C --> D[预处理器
（表/列校验）]
    D --> E[优化器
（生成执行计划）]
    E --> F[执行器]
    F --> G[调用 InnoDB API
（如 row_search_mvcc）]
    G --> H{数据在 Buffer Pool?}
    H -->|是| I[从内存读取行]
    H -->|否| J[从磁盘加载页 → Buffer Pool]
    J --> I
    I --> K[执行器应用 WHERE/ORDER BY/GROUP BY]
    K --> L[返回结果集]

  • 无写操作：不涉及 Redo Log、Undo Log、Binlog；

  • MVCC 一致性读：通过 Read View + Undo Log 链实现快照读（非锁定读）；

  • Buffer Pool 加速：热点数据常驻内存；

  • 权限二次检查：执行时验证列级权限。

若使用 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE，则转为当前读，会加锁并读取最新提交版本。

更新数据

1
UPDATE users SET name = '张三' WHERE id = 100;

  1. 连接器：验证连接和权限；

  2. 分析器：解析 SQL 语法；

  3. 优化器：确认用主键索引定位；

  4. 执行器：

    • 请求 InnoDB 查找 id=100 的行；
    • InnoDB 返回当前行；
    • 执行器检查权限，构造新行；

  5. InnoDB（引擎层）：

    • 加行锁；
    • 写 Undo Log（用于回滚和 MVCC）；
    • 修改 Buffer Pool 中的页；
    • 写 Redo Log（保证崩溃恢复）；
    • 提交事务（Redo Log 刷盘）；

  6. 返回“受影响行数”。

2012006-20201203220840727-213780904

更新流程六大关键步骤：

步骤 组件 作用
1 InnoDB 写 Undo Log（逻辑日志，用于回滚和 MVCC 快照）
2 InnoDB 修改 Buffer Pool 中的数据页（脏页）
3 InnoDB 写 Redo Log（物理日志，保证崩溃恢复）到 Log Buffer
4~5 Server 层 写 Binlog（逻辑日志，用于主从复制、Point-in-Time Recovery）
6 InnoDB + Server 两阶段提交（2PC）： - Prepare 阶段：Redo Log 持久化 - Commit 阶段：Binlog 持久化后，Redo Log 标记 COMMIT

两阶段提交：确保 Redo Log 与 Binlog 逻辑一致。若只写其一，崩溃恢复时可能主从数据不一致。