MySQL 增删改查执行过程与生命周期

本文从客户端发出 SQL 到 InnoDB 落盘，系统讲解 SELECT / INSERT / UPDATE / DELETE 在 MySQL 8.0 中的执行路径、事务与 MVCC、日志与锁机制。
默认存储引擎为 InnoDB（MySQL 8.0 默认且推荐）。
官方参考：MySQL 8.0 Reference Manual — InnoDB

建议阅读顺序：先看一、总览与二、通用执行链路，再按 CRUD 分章深入。

一、总览

1.1 一条 SQL 会经过哪些阶段

无论增删改查，一条 SQL 在 Server 层都遵循大体相同的生命周期：

┌──────────┐   ┌──────────┐   ┌──────────┐   ┌──────────┐   ┌──────────┐   ┌──────────────┐
│  客户端   │──▶│ 连接/线程 │──▶│ 解析器    │──▶│ 优化器    │──▶│ 执行器    │──▶│ 存储引擎      │
│ (应用)   │   │ (mysqld) │   │ Parser   │   │ Optimizer│   │ Executor │   │ (InnoDB 等)  │
└──────────┘   └──────────┘   └──────────┘   └──────────┘   └──────────┘   └──────────────┘
                    │                                              │
                    ▼                                              ▼
              权限/数据字典                                   Buffer Pool / Redo / Undo / Binlog

阶段	职责	读操作侧重	写操作侧重
连接层	协议解析、会话状态、字符集	复用连接、设置隔离级别	同上
解析器	词法/语法分析 → AST	识别 SELECT 子句	识别 DML 目标表
预处理器	表/列存在性、视图展开、权限初检	列裁剪	约束信息收集
优化器	生成并选择执行计划	核心：索引、JOIN 顺序	为 UPDATE/DELETE 选扫描路径
执行器	按计划调引擎接口，汇总结果	逐行/批量读	逐行/批量写
存储引擎	页级读写、锁、MVCC、日志	一致性读	物理变更 + WAL

1.2 读与写的本质区别

维度	读（SELECT）	写（INSERT / UPDATE / DELETE）
默认锁	快照读，通常不加行锁（RC/RR 下）	当前读，通常加行锁 / 间隙锁
MVCC	用 Read View 判断版本可见性	产生新版本，写 Undo 供回滚与快照读
持久化	一般不写 Redo（纯读）	必须写 Redo；提交时协调 Binlog
优化器	计划选择是性能关键	INSERT 路径较固定；带条件的 UPDATE/DELETE 仍依赖优化器

二、通用执行链路

2.1 客户端到 Server

应用通过 MySQL Protocol（TCP 3306 或 Unix Socket）发送 COM_QUERY 报文。
连接线程（thread/sql/ 中每个连接对应一个线程，或线程池模式下的工作线程）接收 SQL 字符串。
会话上下文生效：autocommit、隔离级别、sql_mode、字符集、当前数据库等。

-- 这些设置会影响后续每条 SQL 的生命周期
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION autocommit = 1;

2.2 解析与预处理

解析器（Parser） 将 SQL 转为 AST（抽象语法树）。语法错误在此阶段返回 ER_PARSE_ERROR。

预处理器（Resolver） 会：

从 数据字典（8.0 事务字典，存于 InnoDB）解析表名、列名、索引信息；
展开视图、检查用户权限（SELECT / INSERT / UPDATE / DELETE 等）；
对部分语句做常量折叠、子查询转换等。

2.3 优化器

优化器为「如何访问数据」生成 执行计划（Execution Plan）。

EXPLAIN FORMAT=TREE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 10;

常见决策包括：

决策点	说明
访问类型	`const` / `ref` / `range` / `index` / `ALL`
JOIN 顺序与算法	Nested Loop / Hash Join（8.0.18+）/ BKA 等
索引选择	是否覆盖索引、是否索引下推（ICP）
排序与临时表	`filesort`、内存或磁盘临时表

SELECT：优化器是主角。
INSERT：简单插入通常走主键/唯一索引路径；INSERT ... SELECT 会对 SELECT 部分完整优化。
UPDATE / DELETE：带 WHERE 时，优化器先解决「如何找到行」，再交给执行器修改。

2.4 执行器与存储引擎接口

执行器通过 Handler API（handler:: 系列方法）调用存储引擎，例如：

Handler 方法	典型场景
`index_read` / `rnd_next`	按索引或全表扫描读行
`write_row`	INSERT
`update_row`	UPDATE
`delete_row`	DELETE

执行器还负责：

调用权限检查（如列级权限）；
触发器（BEFORE / AFTER）；
汇总引擎返回的行，编码为协议结果集发给客户端。

2.5 事务边界与提交

`autocommit`	行为
`1`（默认）	每条 DML 隐式为独立事务：执行 → 引擎提交 → 返回客户端
`0`	需 `COMMIT` / `ROLLBACK`；多条语句共享同一事务上下文

提交时（InnoDB + 开启 Binlog） 典型顺序涉及 两阶段提交（2PC），保证 Redo 与 Binlog 一致：

  写 Undo（写操作） → 写 Redo（prepare） → 写 Binlog → 写 Redo（commit） → 返回客户端 OK

崩溃恢复时，InnoDB 用 Redo 重放已提交页变更；用 Binlog 协调判断事务最终是否提交。

三、SELECT（查）的执行过程

3.1 生命周期概览

SQL 文本
  → 解析 / 预处理
  → 优化器生成计划（例如：idx_status → 回表 → Nested Loop Join → filesort → LIMIT）
  → 执行器循环调用引擎读接口
  → InnoDB：在 Buffer Pool 中定位页 → MVCC 判断行版本 → 返回可见行
  → 执行器编码结果集 → 客户端

3.2 InnoDB 如何读一行

定位 B+ 树：按主键或二级索引，从根节点向下查找；目标页优先在 Buffer Pool 中，未命中则从磁盘读入。
一致性读（默认）：
- 为当前语句或事务创建 Read View（与隔离级别有关）；
- 沿 Undo Log 链 找到对该 Read View 可见的行版本。
当前读（加锁读）：SELECT ... FOR UPDATE / FOR SHARE 走当前读，读取最新已提交版本并加锁。

-- 快照读：不加行锁（在 RR/RC 下）
SELECT * FROM accounts WHERE id = 1;

-- 当前读：加锁，阻塞其他事务修改该行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

3.3 隔离级别对「读」的影响

隔离级别	快照读行为（简化）
READ UNCOMMITTED	可能读到未提交数据（InnoDB 实现上仍多用 MVCC，但不保证一致）
READ COMMITTED	每条语句新建 Read View → 避免不可重复读，但可能前后两次读不一致
REPEATABLE READ（默认）	事务内第一次一致性读建立 Read View，之后复用
SERIALIZABLE	普通 SELECT 隐式 `LOCK IN SHARE MODE`

3.4 只读语句的终点

纯 SELECT（无锁、无临时表落盘、无写入）在返回结果集后：

释放语句级资源（Read View、临时内存）；
不产生 Redo；
连接保持，等待下一条 SQL。

四、INSERT（增）的执行过程

4.1 生命周期概览

SQL 文本
  → 解析 / 预处理（目标表、列、约束、触发器）
  → 优化器（简单 INSERT 或 INSERT ... SELECT）
  → 执行器调用 write_row
  → InnoDB：
       分配事务 ID → 写 Undo（可回滚）→ 插入聚簇索引叶子页
       → 维护二级索引 → 写 Redo → 提交时写 Binlog
  → 返回 affected rows / last_insert_id

4.2 聚簇索引与二级索引

InnoDB 表数据按 聚簇索引（通常即主键） 组织为 B+ 树：

在聚簇索引叶子页插入完整行记录；
每个 二级索引 叶子节点插入 (索引列, 主键值)；
若唯一索引冲突，在插入阶段失败并回滚语句（语句级）。

INSERT INTO users (id, email, name) VALUES (1001, 'a@b.com', 'Alice');
-- 1. 聚簇索引 PK(id) 插入 (1001, 'a@b.com', 'Alice')
-- 2. 二级索引 idx_email 插入 ('a@b.com', 1001)

4.3 自增列

AUTO_INCREMENT 由 InnoDB 维护计数器；8.0 默认 innodb_autoinc_lock_mode = 2（交错模式），高并发下 ID 可能不连续，但吞吐更好。

4.4 页分裂与空间分配

当叶子页满时触发 页分裂，可能级联调整父节点——这些物理变更都通过 Redo Log 记录，保证崩溃后可恢复。

4.5 INSERT 的结束

场景	结束状态
`autocommit=1`	单语句事务提交 → Binlog 落盘 → 客户端收到 OK
显式事务中	变更在事务缓冲区内，等待 `COMMIT`
失败（唯一键、外键等）	语句回滚，Undo 撤销本语句变更，返回错误

五、UPDATE（改）的执行过程

5.1 生命周期概览

SQL 文本
  → 解析 / 预处理
  → 优化器：为 WHERE 选择扫描路径（主键 / 二级索引 / 全表）
  → 执行器：定位每一行 → update_row
  → InnoDB（每一行）：
       当前读 + 加 X 锁 → 写 Undo 保存旧版本 → 生成新版本
       → 更新聚簇索引 → 若索引列变化则调整二级索引
       → Redo → 提交 / Binlog

5.2 本质：删除旧版本 + 插入新版本

InnoDB 的 UPDATE 在内部通常视为：

对旧行加 排他锁（X Lock）；
将旧行写入 Undo Log（供 MVCC 与回滚）；
在聚簇索引上写入新版本（若主键未变，多为原地更新；主键变更则相当于迁移 B+ 树位置）；
若更新了索引列，对应二级索引项 删除旧项 + 插入新项。

UPDATE orders SET status = 'shipped' WHERE id = 42;
-- 1. 通过 PK 定位 id=42
-- 2. 锁行 → Undo 旧行 → 更新 status 列
-- 3. 若 status 有二级索引，更新该索引项

5.3 与 SELECT 的关系

带条件的 UPDATE 在「找行」阶段与 SELECT 共享同一套优化器逻辑；差异在于找到行后必须 当前读 + 加锁 + 写日志。

5.4 锁与并发

场景	典型锁
命中主键/唯一索引	行锁（Record Lock）
范围更新（RR）	可能加间隙锁（Gap Lock） / Next-Key Lock，防幻读
未走索引的 UPDATE	可能锁全表或大量行 → 高风险

六、DELETE（删）的执行过程

6.1 生命周期概览

SQL 文本
  → 解析 / 预处理
  → 优化器：为 WHERE 选路径
  → 执行器：定位每一行 → delete_row
  → InnoDB（每一行）：
       当前读 + X 锁 → Undo 记录整行 → 标记删除（delete mark）
       → 二级索引项标记删除 → Redo → 提交 / Binlog
  → 后台 Purge 线程异步回收空间

6.2 逻辑删除与物理清理

InnoDB 的 DELETE 不会立刻从 B+ 树物理移除：

对行设置 delete mark；
写 Undo，使其他事务的快照读仍可能看到旧版本；
事务提交后，Purge 线程 在不再有事务需要该 Undo 时，真正清理索引项并回收空间。

因此大批量 DELETE 可能暂时增加表/索引体积，空间回收滞后。

DELETE FROM logs WHERE created_at < '2024-01-01';
-- 大量行被标记删除 → Purge 逐步清理 → 空间可能仍占用，需 OPTIMIZE 或重建表才能彻底紧缩（视版本与表空间而定）

6.3 TRUNCATE 与 DELETE 的区别

语句	本质	速度	可回滚	触发器
`DELETE`	逐行 DML + Undo	慢	事务内可回滚	触发
`TRUNCATE`	DDL，重建表	快	通常不可回滚（DDL 隐式提交）	不触发

七、日志体系：写操作如何「活下来」

写操作的生命周期离不开三类日志：

                    ┌─────────────┐
                    │   客户端     │
                    └──────┬──────┘
                           │ COMMIT
                           ▼
              ┌────────────────────────┐
              │         InnoDB          │
              │  Undo Log（逻辑回滚/MVCC）│
              │  Redo Log（物理页 WAL）   │
              └────────────┬───────────┘
                           │ 2PC
                           ▼
              ┌────────────────────────┐
              │  Binlog（逻辑复制/恢复）  │
              └────────────────────────┘

日志	作用	谁消费
Undo Log	事务回滚；构建旧版本供快照读	本事务 / Purge
Redo Log	崩溃恢复时重放已提交页的物理修改	InnoDB 恢复
Binlog	主从复制、基于时间点的逻辑恢复	Replica、mysqlbinlog

读操作通常只间接接触 Undo（读历史版本），不产生 Redo/Binlog。

八、锁与 MVCC 在 CRUD 中的协作

8.1 一句话对照

操作	MVCC	锁
SELECT（普通）	✅ 快照读	一般无行锁
SELECT FOR UPDATE / SHARE	当前读	✅ 行锁 / 间隙锁
INSERT	新版本立即可见（本事务）	插入意向锁、唯一检查
UPDATE / DELETE	写 Undo 保留旧版本	✅ 行锁，范围操作可能有间隙锁

8.2 幻读与 Next-Key Lock

在 REPEATABLE READ 下，InnoDB 用 Next-Key Lock（行锁 + 间隙锁） 约束范围扫描，使同一事务内重复范围读结果稳定（配合 MVCC）。

-- 事务 A
START TRANSACTION;
SELECT * FROM orders WHERE amount > 100 FOR UPDATE;
-- 对满足条件的行及间隙加锁，阻止事务 B 插入 amount=150 的新行（幻读）

九、端到端时序示例

9.1 自动提交下的 INSERT

Client          mysqld (SQL Layer)              InnoDB
  |                    |                          |
  |-- INSERT --------->|                          |
  |                    |-- parse / optimize       |
  |                    |-- write_row ------------>|
  |                    |                          |-- insert undo
  |                    |                          |-- modify page (mem)
  |                    |                          |-- write redo (prepare)
  |                    |<-- ok -------------------|
  |                    |-- write binlog --------->| (server layer)
  |                    |-- commit --------------->|
  |                    |                          |-- redo commit
  |<-- OK -------------|                          |

9.2 事务中的 SELECT → UPDATE → COMMIT

Client          mysqld                         InnoDB
  |-- BEGIN --------->|                          |
  |-- SELECT -------->|-- 一致性读 -------------->| 建立 Read View，读可见版本
  |<-- rows ----------|                          |
  |-- UPDATE -------->|-- 当前读 + update_row -->| X 锁，Undo，新版本，Redo
  |-- COMMIT -------->|-- 2PC ------------------->| Binlog + Redo commit，释放锁
  |<-- OK ------------|                          |

十、影响生命周期的常见因素

因素	影响
索引	决定找行成本；UPDATE/DELETE 无索引易全表扫描 + 锁放大
事务长度	长事务阻塞 Purge、堆积 Undo、增大锁等待
隔离级别	RC 减少间隙锁；RR 默认防幻读但锁更多
触发器 / 外键	执行器层额外语句或引擎层级联检查
Binlog 格式	`ROW`（默认）记录行变更，复制更安全；`STATEMENT` 记录 SQL
组提交	多事务合并刷 Redo/Binlog，提高吞吐

十一、实践：如何观察执行过程

11.1 查看执行计划

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 100 AND status = 'open';

11.2 查看当前事务与锁

-- 事务
SELECT * FROM information_schema.INNODB_TRX;

-- 锁（8.0）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

11.3 开启性能模式跟踪（按需）

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/sql/%';

十二、小结

操作	Server 层	InnoDB 核心动作	持久化与复制
SELECT	优化器选路 → 引擎读	Buffer Pool + MVCC（快照读）	通常无
INSERT	write_row	聚簇/二级索引插入 + Undo	Redo + Binlog
UPDATE	找行 → update_row	锁行 + Undo 旧版 + 写新版 + 索引维护	Redo + Binlog
DELETE	找行 → delete_row	锁行 + delete mark + Undo	Redo + Binlog；Purge 异步清理

理解 CRUD 生命周期，有助于解释：为什么某些 UPDATE 很慢（没索引）、为什么长事务导致空间不涨、为什么 RR 下会死锁、为什么主从延迟与 Binlog/Redo 刷盘策略有关。

MySQL 增删改查执行过程与生命周期

MySQL 增删改查执行过程与生命周期

一、总览

1.1 一条 SQL 会经过哪些阶段

1.2 读与写的本质区别

二、通用执行链路

2.1 客户端到 Server

2.2 解析与预处理

2.3 优化器

2.4 执行器与存储引擎接口

2.5 事务边界与提交

三、SELECT（查）的执行过程

3.1 生命周期概览

3.2 InnoDB 如何读一行

3.3 隔离级别对「读」的影响

3.4 只读语句的终点

四、INSERT（增）的执行过程

4.1 生命周期概览

4.2 聚簇索引与二级索引

4.3 自增列

4.4 页分裂与空间分配

4.5 INSERT 的结束

五、UPDATE（改）的执行过程

5.1 生命周期概览

5.2 本质：删除旧版本 + 插入新版本

5.3 与 SELECT 的关系

5.4 锁与并发

六、DELETE（删）的执行过程

6.1 生命周期概览

6.2 逻辑删除与物理清理

6.3 TRUNCATE 与 DELETE 的区别

七、日志体系：写操作如何「活下来」

八、锁与 MVCC 在 CRUD 中的协作

8.1 一句话对照

8.2 幻读与 Next-Key Lock

九、端到端时序示例

9.1 自动提交下的 INSERT

9.2 事务中的 SELECT → UPDATE → COMMIT

十、影响生命周期的常见因素

十一、实践：如何观察执行过程

11.1 查看执行计划

11.2 查看当前事务与锁

11.3 开启性能模式跟踪（按需）

十二、小结

延伸阅读

公告