一条SQL的完整旅程 — MySQL 查询执行原理图解

一条SQL的完整旅程

从客户端发送SQL到存储引擎返回数据，MySQL内部经历了哪些环节？每一步对应的源码函数是什么？

图1-1：MySQL SQL执行完整流水线 — 从客户端到磁盘再返回

⚙ 核心机制

SQL = 查询指令（你要查什么数据）

Parser = 词法/语法解析（SQL格式对不对？SELECT能解析，SELCT就报错）

Optimizer = 优化器选择执行计划（走索引还是全表扫描？先过滤还是先JOIN？选成本最低的方案）

Executor = 执行器调用存储引擎接口（按计划逐行获取数据）

InnoDB = 存储引擎层（数据在哪，Buffer Pool=内存缓存，Disk=磁盘）

// sql/sql_parse.cc — MySQL查询执行核心调度 int mysql_execute_command(THD *thd) { switch (thd->lex->sql_command) { case SQLCOM_SELECT: // ④ 解析阶段已完成，此时进入执行 res = execute_sqlcom_select(thd, all_tables); break; case SQLCOM_UPDATE: res = mysql_update(thd, all_tables); break; } } bool execute_sqlcom_select(THD *thd, TABLE_LIST *tables) { // ⑤ 优化器：选择最优执行计划 optimize_sql(thd); // ← optimize_sql() // ⑥ 执行器：按计划调用存储引擎API Query_expression::Execute(thd); // 内部循环调用 handler->ha_index_read() // 最终调用 ha_innobase::index_read() }

各阶段核心职责

① 客户端发送SQL — 通过TCP连接发送文本SQL语句到MySQL Server

② 连接处理 — 验证用户权限、检查连接数阈值、分配线程

③ 查询缓存 — MySQL 8.0已移除此功能（命中率低、维护成本高）

④ 解析器 — 词法分析(拆token) + 语法分析(建AST) + 语义检查(表/列是否存在)

⑤ 优化器 — 基于成本选择执行计划：选哪个索引、JOIN顺序、是否走全表扫描

⑥ 执行器 — 按执行计划迭代调用存储引擎API，逐行返回结果

⑦ InnoDB — 在Buffer Pool中查找数据页，未命中则从磁盘加载

💡 为什么MySQL 8.0移除了查询缓存？

查询缓存在高并发写入场景下频繁失效，命中率极低。每次表数据变更都需要清空相关缓存，锁竞争严重。8.0用prepared statement缓存替代。

InnoDB存储引擎内部 — 数据住在哪里

数据在内存和磁盘之间如何流转？B+Tree如何组织数据？Buffer Pool如何管理热数据？

图2-1：InnoDB内存与磁盘架构总览

图2-2：B+Tree 查找 id=5 的完整路径 — 从Root到Leaf只需3层IO

📖 查字典类比

B+Tree = 查字典（部首检字表 → 页码 → 正文，逐层缩小范围）

Root = 部首目录（先定位到哪个大部），Branch = 检字表（再定位页码），Leaf = 正文页（最终找到内容）

Page = 书的一页（每页16KB，InnoDB最小IO单位）

图2-3：Buffer Pool改进LRU — 中点插入防止全表扫描污染热数据

📚 书架类比

Buffer Pool = 书架（常用书放前面，新来的书先放中间观察区）

Young区 = 书架前排（经典好书，反复翻阅不会被挤走）

Old区 = 书架后排（新书先放这，被再次翻到才升入前排）

// storage/innobase/btr/btr0cur.cc — B+Tree逐层查找 void btr_cur_search_to_nth_level( dict_index_t *index, ulint level, const dtuple_t *tuple, ulint mode, ulint latch_mode, btr_cur_t *cursor) { // 从Root开始逐层向下搜索 page_id_t page_id(dict_index_get_space(index), dict_index_get_page(index)); // Root Page while (page_level > level) { // 在当前页内二分查找，确定子节点指针 offset = page_cur_search(block->frame, tuple); // 获取子节点页号 child_page = page_get_child(page, offset); // 加载子节点到Buffer Pool block = buf_page_get(child_page); // 可能触发磁盘IO page_level = page_get_level(block->frame); } // 到达目标层，cursor指向找到的记录 cursor->page_cur.rec = page_cur_get_rec(); } // storage/innobase/buf/buf0lru.cc — 中点插入策略 void buf_LRU_add_block(buf_block_t *block, ibool old) { if (old) { // 新页插入LRU链中点(Old区头部) UT_LIST_INSERT(LRU, buf_pool->LRU, old_start, block); } else { // 被再次访问，移到Young区头部 UT_LIST_INSERT(LRU, buf_pool->LRU, start, block); } }

特性	B+Tree	B-Tree	Hash索引
结构	非叶节点只存key，数据全在叶节点	每个节点都存key+data	哈希表，key→slot
范围查询	叶节点链表，天然支持	需要中序遍历，效率低	不支持
等值查询	O(log N)	O(log N)	O(1) 最快
磁盘IO	扇出大，树矮，IO少	扇出小，树高，IO多	1次(内存)或1次IO
排序	叶节点有序，支持ORDER BY	不支持高效排序	无序
InnoDB使用	所有索引的底层结构	不使用	自适应哈希索引(AHI)

索引查找的完整路径 — 从Root到Leaf

同一个查询，走索引和全表扫描有多大差距？二级索引回表是什么？覆盖索引为何是最优解？

图3-1：同一条SQL的三种执行路径 — 索引决定性能差距百倍

📖 查书类比

二级索引 = 书的目录（知道页码后还要翻到那一页，即"回表"）

覆盖索引 = 目录里已有答案（不需要翻页，目录直接告诉你结果）

全表扫描 = 从第一页翻到最后一页（逐页找，最笨但有时是唯一选择）

PRO TIP 覆盖索引是最优解。EXPLAIN的Extra列出现Using index说明命中覆盖索引，无需回表。设计索引时，尽量让查询列都包含在索引中。

-- Case 1: 二级索引 + 回表 EXPLAIN SELECT * FROM user WHERE name='张三'; -- type: ref, key: idx_name, Extra: NULL (需要回表) -- Case 2: 覆盖索引 (联合索引 name+age) EXPLAIN SELECT name, age FROM user WHERE name='张三'; -- type: ref, key: idx_name_age, -- Extra: Using index ← 命中覆盖索引！不回表 -- Case 3: 全表扫描 (name无索引) EXPLAIN SELECT * FROM user WHERE name='张三'; -- type: ALL, key: NULL, rows: 1000000 -- Extra: Using where ← 全表扫描+服务层过滤

事务的一生 — 从BEGIN到COMMIT

WAL如何保证数据不丢失？MVCC如何实现快照读？Undo Log版本链如何工作？

图4-1：WAL两阶段提交流程 — 先写日志再写数据，崩溃也不丢

📝 草稿盖章类比

WAL = 先写草稿再正式提交（Redo Log = 草稿，Commit = 盖章生效）

两阶段提交 = 草稿先签"待定"，正式文件写完再签"确认"（保证Redo和Binlog一致）

Buffer Pool脏页 = 改了但还没归档的文件（后台线程异步刷盘）

图4-2：MVCC版本链 + ReadView — 不同隔离级别看到不同版本

🕵 眼镜类比

MVCC = 每个人戴不同颜色眼镜看同一件商品（看到的是不同版本）

Undo Log = 商品的历史版本（商品被改过，但旧版本还在仓库里）

ReadView = 你入场时拿的入场券（上面写着你能看到哪个时间点之后的版本）

RC = 每看一次换一张新入场券（能看到别人刚提交的），RR = 全程用同一张入场券（看到的一直不变）

// storage/innobase/trx/trx0trx.cc — 事务生命周期 void trx_start(trx_t *trx) { trx->state = TRX_STATE_ACTIVE; trx->id = trx_sys_get_new_trx_id(); // 分配事务ID } void trx_commit(trx_t *trx) { // 两阶段提交： // Phase 1: Redo Log Prepare trx_commit_prepare(trx); // ← mtr_commit()写Redo // 写Binlog (Server层) MYSQL_BIN_LOG::write(thd, &binlog_evt); // Phase 2: Redo Log Commit trx_commit_commit(trx); // ← 标记Commit // 释放ReadView, 清理Undo read_view_close(trx->read_view); } // storage/innobase/read/read0read.cc — ReadView判断可见性 bool trx_read_view_sees( const read_view_t *view, trx_id_t trx_id) { // trx_id < view->up_limit_id → 可见(事务已提交) if (trx_id < view->up_limit_id) return true; // trx_id >= view->low_limit_id → 不可见(事务后开始) if (trx_id >= view->low_limit_id) return false; // trx_id in view->m_ids → 不可见(活跃事务) return !view->m_ids.contains(trx_id); }

锁的运行流程 — 谁等谁，怎么等

行锁、间隙锁、临键锁各自锁定什么范围？死锁如何发生？InnoDB如何检测？

图5-1：InnoDB锁层次 — 从表锁到临键锁

图5-2：行锁竞争 — T2等待T1释放锁后才能继续执行

图5-3：间隙锁防止幻读 — 锁住间隙阻止INSERT

图5-4：死锁场景 — 循环等待，InnoDB自动检测并回滚代价最小的事务

🔒 柜子走廊类比

行锁 = 锁柜子（别人不能开这个柜子，但可以开其他柜子）

间隙锁 = 锁走廊（别人不能进这段走廊放新柜子，防止"凭空多出柜子"=幻读）

临键锁 = 锁柜子+锁走廊（既锁住当前柜子，又锁住相邻走廊）

死锁 = 你拿A钥匙等B，他拿B钥匙等A（两个人互相等，谁也动不了）

锁类型	锁定范围	与什么冲突	何时自动加	源码函数
Record Lock	索引上一条记录	另一个Record Lock(X)	UPDATE/DELETE/SELECT FOR UPDATE命中行	lock_rec_lock(LOCK_REC)
Gap Lock	索引记录之间的间隙(开区间)	INSERT（只阻塞插入）	RR级别下范围查询/等值未命中	lock_rec_lock(LOCK_GAP)
Next-Key Lock	Record + 前面Gap(左开右闭)	INSERT + Record Lock	RR级别下默认加锁方式	lock_rec_lock(LOCK_ORDINARY)
Insert Intention	插入位置间隙	Gap Lock	INSERT时自动请求	lock_rec_lock(LOCK_INSERT_INTENTION)

// storage/innobase/lock/lock0lock.cc — InnoDB锁核心 dberr_t lock_rec_lock( bool impl, ulint mode, const buf_block_t *block, ulint heap_no, que_thr_t *thr) { // mode: LOCK_REC / LOCK_GAP / LOCK_ORDINARY // LOCK_ORDINARY = Next-Key Lock (默认) // 1. 检查是否与已有锁冲突 if (lock_rec_has_conflict(lock, mode)) { // 2. 冲突 → 进入等待队列 lock_wait_suspend_thread(thr); // ← 阻塞等待 } else { // 3. 无冲突 → 授予锁 lock_rec_add_to_queue(mode, block, heap_no, trx); } } // 死锁检测 — 每1秒触发 void DeadlockChecker::check(trx_t *trx) { // 构建等待图(Wait-for Graph) // 检测是否存在环(循环等待) if (has_cycle) { // 选择代价最小的事务回滚 trx_rollback(victim_trx); // 返回 ERROR 1213 } } // 锁释放 — 事务COMMIT时 void lock_rec_unlock(trx_t *trx) { // 释放该事务持有的所有行锁 // 唤醒等待队列中的下一个事务 lock_wait_release_thread(waiting_thr); }

面试速答

MySQL内部原理高频面试题，每题30秒速答版

Q1: 一条SELECT语句在MySQL中经历了哪些阶段？

答：客户端发送SQL → 连接处理(权限校验) → 解析器(词法语法分析生成AST) → 优化器(选执行计划/选索引) → 执行器(调用存储引擎API) → InnoDB(Buffer Pool查找/磁盘IO) → 返回结果。对应源码：mysql_parse() → optimize_sql() → mysql_execute_command() → ha_innobase::index_read()。

Q2: 为什么InnoDB选择B+Tree而不是B-Tree或Hash？

答：B+Tree非叶节点只存key不存data，一个16KB页能放更多key，扇出更大，树更矮，磁盘IO更少。叶节点链表支持高效范围查询。B-Tree每个节点存key+data导致扇出小、树高。Hash不支持范围查询和排序。InnoDB用B+Tree做主结构，自适应哈希索引(AHI)做等值查询加速补充。

Q3: 什么是回表？覆盖索引如何避免回表？

答：二级索引叶节点存的是主键值，不是完整行。通过二级索引找到主键后，还要回聚簇索引查一次完整行，这就是"回表"(row_sel_get_clust_rec())。覆盖索引指查询需要的列全部包含在索引中，无需回聚簇索引。EXPLAIN Extra列显示Using index即命中覆盖索引。

Q4: WAL两阶段提交是怎么回事？

答：事务提交时先写Redo Log(Prepare) → 写Binlog → Redo Log(Commit)。两阶段保证Redo和Binlog一致：崩溃恢复时，Prepare+Binlog完整→提交；Binlog不完整→回滚。这样主从复制(Binlog)和崩溃恢复(Redo)不会矛盾。

Q5: MVCC的ReadView在RC和RR下有什么区别？

答：RC(READ COMMITTED)：每次SELECT都创建新的ReadView，能看到其他事务已提交的最新数据。RR(REPEATABLE READ)：事务内只有第一次SELECT创建ReadView，后续复用，所以同一事务内多次读同一行结果一致。核心函数：trx_read_view_sees()判断版本可见性。

Q6: 间隙锁什么时候加？和死锁有什么关系？

答：RR级别下默认加Next-Key Lock(Record+Gap)。等值查询未命中时退化为Gap Lock，命中时退化为Record Lock。间隙锁之间不冲突，只阻塞INSERT。死锁发生在：T1锁A等B，T2锁B等A，形成循环等待。InnoDB通过DeadlockChecker::check()自动检测，回滚代价最小的事务。避免方法：固定顺序访问、保持事务短小、降低隔离级别。

速记口诀

SQL旅程：连接→解析→优化→执行→引擎

B+Tree：非叶只存key，数据全在叶，叶间有链表，范围查询快

索引三路径：二级索引要回表，覆盖索引不回表，没有索引全表扫

WAL：先写日志再写数据，两阶段保一致

MVCC：RC每次新视图，RR复用旧视图，Undo链存历史

锁：行锁锁记录，间隙锁锁区间，临键锁都锁，死锁自动检

核心三原则速记

MySQL三原则：① 索引设计优先于SQL优化 → ② 避免在WHERE条件中使用函数（索引失效）→ ③ 大查询用EXPLAIN验证执行计划