MySQL执行流程原理深度解析(含代码)

一、架构总览：一条sql的生死簿

mysql架构只有两层，但大多数性能问题都发生在层间交互处：

┌─────────────────────────────────────────────────────────────┐
│  mysql server层（通用逻辑）                                  │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐         │
│  │connector│→│  parser │→│optimizer│→│ executor│         │
│  │连接/权限│ │词法/语法│ │执行计划│ │调用引擎│         │
│  └─────────┘ └─────────┘ └─────────┘ └────┬────┘         │
│                                             │             │
│  ┌──────────────────────────────────────────┘             │
│  │ binlog（逻辑日志，server层维护，所有引擎共享）          │
│  └───────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────┘
                              │
                              ▼ 引擎api（handler接口）
┌─────────────────────────────────────────────────────────────┐
│  存储引擎层（innodb）                                        │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐         │
│  │buffer   │→│undo log │→│redo log │→│ 磁盘文件 │         │
│  │pool     │ │(回滚)   │ │(wal)    │ │(ibd/data)│         │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘         │
└─────────────────────────────────────────────────────────────┘

关键认知：server层只负责"怎么执行"，innodb负责"数据在哪"。优化器选错索引？这是server层的锅。主从延迟？可能是binlog和redolog的协作问题。oom崩溃？大概率是buffer pool或连接内存管理的问题。

二、连接器：被低估的内存杀手

2.1 权限缓存的陷阱

连接器在tcp握手后做两件事：认证身份、查询权限表并缓存到连接对象。这意味着：

-- 场景：管理员 revoke 了某用户的delete权限
revoke delete on db.* from 'app_user'@'%';

-- 但已建立的连接不受影响，直到重连
-- 这在生产环境曾导致"权限已回收但数据仍被删"的事故

⚠️ 生产建议：修改权限后，务必执行 kill <thread_id> 断开已有连接，或等待 wait_timeout（默认8小时）后自然失效。

2.2 长连接的内存泄漏

mysql的连接内存不是线程池模式，而是每个连接独立分配：

连接内存 = 会话级变量 + 临时表 + 排序缓冲区 + 二进制日志缓存 + ...

当使用连接池（如hikaricp）保持长连接时，如果执行过大查询（如 select * from huge_table order by），排序缓冲区可能膨胀到数十mb。语句执行完毕后，这些缓冲区会被标记为空闲并在本会话的后续查询中复用，但从操作系统视角（resident memory）来看，内存并未真正归还给os，而是保留在线程的内存池中。因此长连接累积的"内存池占用"会持续增加，直到连接断开才释放。citeweb_search:2#0

解决方案：

1. mysql 5.7+：执行 mysql_reset_connection() 重置连接状态（无需重连，权限不变）
2. 连接池配置：设置 maxlifetime（hikaricp默认30分钟），强制轮换连接
3. 监控：关注 show processlist 中 memory 列，异常增长的连接需要排查

三、查询缓存：为什么mysql 8.0彻底删除了它？

查询缓存的kv设计（key=sql文本，value=结果集）看似美好，实则存在结构性缺陷：

💡 替代方案：将缓存上移到应用层（redis/memcached），或利用innodb的buffer pool（天然缓存数据页，不受写操作全量失效影响）。

四、分析器与优化器：从sql到执行计划的蜕变

4.1 词法分析的隐藏开销

分析器从 information_schema 读取表结构进行元数据校验。在表数量庞大的实例中，这会成为瓶颈：

-- 查看分析阶段耗时（mysql 8.0+）
select * from performance_schema.events_stages_history_long 
where event_name like '%sql/parse%';

4.2 优化器的成本模型

优化器基于**成本（cost）**选择执行计划，但成本估算可能严重偏差：

-- 案例：优化器误判索引选择
explain select * from orders where user_id = 100 and create_time > '2024-01-01';
-- 可能选择 idx_user_id，但实际 idx_create_time 更高效（时间范围过滤更严格）

优化器局限：

• 不会考虑数据分布的倾斜（如某个user_id有百万条记录）
• 多表join时，连接顺序的枚举是np-hard问题，只能启发式求解
• 对复杂子查询可能选择物化而非转换，导致临时表爆炸

💡 调优工具：explain analyze（mysql 8.0.18+）显示实际执行时间，比传统explain更准确。

五、执行器与存储引擎：权限校验的两次博弈

执行器在调用引擎前做最终权限校验（precheck无法覆盖触发器等运行时对象）。但真正的性能博弈在引擎层：

5.1 buffer pool：innodb的心脏

buffer pool不是简单的lru，而是改进版lru（midpoint insertion）：

┌─────────────────────────────────────────────────────────────┐
│  young区（热数据，约5/8）                                     │
│  ┌─────┐ ┌─────┐ ┌─────┐        ┌─────┐ ┌─────┐          │
│  │  a  │→│  b  │→│  c  │  ...   │  x  │→│  y  │          │
│  └─────┘ └─────┘ └─────┘        └─────┘ └─────┘          │
│       ↑                                    ↑              │
│   频繁访问                                  midpoint      │
│                                                             │
│  old区（冷数据，约3/8）                                     │
│  ┌─────┐ ┌─────┐ ┌─────┐        ┌─────┐ ┌─────┐          │
│  │  m  │→│  n  │→│  o  │  ...   │  z  │→│     │          │
│  └─────┘ └─────┘ └─────┘        └─────┘ └─────┘          │
│       ↑                                    ↑              │
│   观察期（默认1秒）                         淘汰尾部        │
└─────────────────────────────────────────────────────────────┘

核心机制：新读入的页不直接放入头部，而是插入midpoint（冷区头部）。只有在old区度过观察期（innodb_old_blocks_time，默认1000ms）且再次被访问，才会晋升到young区。citeweb_search:2#4

这解决了全表扫描的缓存污染问题：扫描时顺序读取的页在old区，如果不再被访问，很快被淘汰；真正的热数据在young区不受影响。

5.2 三大链表协作

buffer pool通过三个链表管理页：citeweb_search:2#0

脏页刷盘策略：

• buf_flush_lru：从lru尾部扫描，发现脏页则刷盘（保证有足够空闲页）
• buf_flush_list：从flush list头部刷盘（按lsn顺序，推进checkpoint）
• buf_flush_single_page：极端情况下，用户线程被迫同步刷 单个脏页（性能杀手）citeweb_search:2#6

六、更新语句：日志系统的三重奏

6.1 wal与redo log

innodb采用wal（write-ahead logging）：先写redo log，再刷脏页。redo log是物理日志，记录"在某个数据页上做了什么修改"，采用循环写入（固定大小，如4个1gb文件）。

                    write pos
                       ↓
    ┌────────┬────────┬────────┬────────┐
    │ib_log  │ib_log  │ib_log  │ib_log  │
    │file_0  │file_1  │file_2  │file_3  │
    └────────┴────────┴────────┴────────┘
                       ↑
                   checkpoint

• write pos：当前写入位置
• checkpoint：已刷盘到数据文件的位置
• 两者之间的空间是可写区域，若write pos追上checkpoint，必须强制刷盘

6.2 redo log buffer与刷盘策略

redo log先写入内存的redo log buffer（默认16mb），再按策略刷盘：citeweb_search:2#8

⚠️ mysql 8.0变化：redo log写入改为多线程异步架构（log_writer、log_flusher、log_closer）。

版本权衡：5.7的单锁模型在低并发时延迟更低（无线程切换开销）；8.0的多线程模型在高并发时吞吐量更高（锁竞争分散）。两者各有优劣，需根据实际负载选择——低并发核心系统可保留5.7，高并发业务推荐8.0。citeweb_search:2#15

6.3 binlog：server层的归档日志

binlog是逻辑日志，记录sql语句的原始逻辑（如"给id=2的c字段加1"），采用追加写入，不覆盖历史日志。

七、两阶段提交：主从一致性的生死线

7.1 为什么必须两阶段提交？

redo log和binlog是两个独立的系统，如果不用2pc：

场景a：先写redo log，后写binlog

• redo log写完后崩溃，binlog未写
• 恢复后数据已更新，但binlog缺失
• 后果：从库复制时丢失该事务，主从不一致

场景b：先写binlog，后写redo log

• binlog写完后崩溃，redo log未写
• 恢复后数据未更新，但binlog已记录
• 后果：从库执行了该事务，主从不一致

7.2 2pc完整流程

阶段一（prepare）：
  ├─ 引擎将更新记录到redo log，标记为prepare状态
  └─ 告知执行器：随时可以提交

阶段二（commit）：
  ├─ 执行器生成binlog并写入磁盘
  └─ 执行器调用引擎提交接口，redo log改为commit状态

崩溃恢复规则：

1. redo log有commit标识 → 直接提交（事务完整）
2. redo log只有prepare → 用xid去binlog查找：
   • binlog存在且完整 → 提交（保证主从一致）
   • binlog不存在或不完整 → 回滚

八、生产故障案例集

案例1：主从延迟突然增大（binlog与redolog的协作问题）

现象：监控告警 seconds_behind_master 从0秒突增到4784150秒（约55天），执行的是 delete from table（仅50万数据）。citeweb_search:2#7

排查：

-- 从库查看
show slave status\g;
-- seconds_behind_master: 4784150

select * from information_schema.innodb_trx\g;
-- trx_state: running
-- trx_query: delete from wggl_sjgdxq
-- trx_rows_modified: 136799
-- 发现是一个大事务在从库单线程执行

根因：

• 主库执行delete时，由于未加limit或分批，成为一个大事务
• 从库单线程sql线程（slave_parallel_workers=0）串行回放
• 同时从库磁盘io性能不足（ssd但随机读写性能差），导致回放极慢

解决：

1. 主库大事务拆分为小批量（如每次删除1000条）
2. 从库开启并行复制：slave_parallel_workers=4，slave_parallel_type=logical_clock
3. 升级从库磁盘为更高性能ssd，或调整 innodb_io_capacity 匹配硬件

案例2：mysql周期性oom重启（连接内存泄漏）

现象：mysql每2-3天被系统oom killer杀掉，重启后正常。

排查：

-- 查看连接内存占用
select 
  id, user, host, db, command, time, 
  max_memory_used/1024/1024 as mem_mb 
from performance_schema.threads 
order by max_memory_used desc;

-- 发现部分连接内存占用超过500mb

根因：

• 应用使用长连接池，执行过大量 order by、group by 操作
• 排序缓冲区（sort_buffer_size）和临时表内存（tmp_table_size）未释放
• 连接池未设置 maxlifetime，连接永久存活

解决：

1. 连接池设置 maxlifetime=1800000（30分钟）
2. 大查询添加 sql_big_result 提示，避免内存临时表
3. 定期执行 mysql_reset_connection()（mysql 5.7+）

案例3：全表扫描后buffer pool命中率暴跌（lru污染）

现象：凌晨备份任务后，白天业务高峰期buffer pool命中率从99%跌至85%，qps下降40%。

排查：

-- 查看buffer pool状态
show engine innodb status\g;
-- pages made young: 突然激增
-- buffer pool hit rate: 从1000/1000降至850/1000

-- 查看是否全表扫描
select * from performance_schema.events_statements_history_long 
where sql_text like '%select%backup_table%';

根因：

• 备份任务执行 select * from huge_table（全表扫描）
• 原生lru会将所有扫描页放入young区，冲掉真正的热数据
• innodb的midpoint机制本应防止此问题，但 innodb_old_blocks_time=0（被误改）

解决：

1. 恢复 innodb_old_blocks_time=1000（默认1秒观察期）
2. 备份任务添加 select sql_no_cache（虽然查询缓存已移除，但可显式避免其他缓存干扰）
3. 备份改为从从库执行，或限制 innodb_buffer_pool_size 的扫描影响

九、核心参数速查与版本差异

9.1 关键参数配置

9.2 版本差异注意点

十、总结：一张图看懂全链路

查询语句：
客户端 → 连接器（认证+权限缓存） → 查询缓存（5.7存在，8.0已移除）
→ 分析器（词法/语法/元数据校验） → 优化器（成本模型选索引）
→ 执行器（权限校验+调用引擎） → innodb（buffer pool命中？→ 返回/读磁盘）
→ 返回结果集

更新语句：
... → 执行器 → innodb：
   ├─ 读取数据页（buffer pool/磁盘）
   ├─ 记录undo log（用于回滚/mvcc）
   ├─ 修改buffer pool数据（标记脏页，加入flush list）
   ├─ 写入redo log buffer → 刷盘（prepare状态）
   ├─ 执行器生成binlog → 写入磁盘
   └─ redo log改为commit状态（两阶段提交完成）
→ 返回更新结果

核心设计哲学：

• 连接器：权限缓存提升性能，但需注意权限变更的延迟生效
• 查询缓存：被移除是因为"失效成本 > 命中收益"，缓存应上移至应用层
• buffer pool：midpoint lru解决扫描污染，三大链表协作实现高效内存管理
• wal：用顺序写（redo log）替代随机写（数据页），是数据库性能的核心基石
• 两阶段提交：用xid关联redo log和binlog，保证崩溃恢复后主从一致

“理解mysql的执行流程，不是记住每个组件的名字，而是理解每个设计决策背后的权衡——性能 vs 一致性、内存 vs 磁盘、复杂度 vs 可靠性。”

本文基于mysql 5.7/8.0架构原理整理，参考mysql官方文档、innodb源码及生产故障案例。

到此这篇关于mysql执行流程原理深度解析的文章就介绍到这了,更多相关mysql执行流程原理内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！