上周帮同事排查一条线上慢查询SQL 很简单就是按用户查最近一段时间的订单结果执行了 6 秒多。我们打开 EXPLAIN 看了一眼索引也用上了rows 也不算离谱但就是慢。后来一路顺着 MySQL 的执行链路往下追才发现真正的瓶颈藏在排序和回表里。从那之后我意识到一件事网上讲“一条 SQL 查询语句是如何执行的”的文章很多但大多数停留在背八股——连接器、分析器、优化器、执行器背得滚瓜烂熟真到了排查问题的时候这些知识点根本对不上号。这篇文章我想换个讲法。我们不只把 MySQL 执行一条 SQL 查询的完整链路拆开还会把每一条链路对应到真实的排查场景里比如你什么时候会碰到权限问题、为什么优化器选错了索引、EXPLAIN 里的 Using filesort 到底意味着什么。理解了这条链路你再去分析慢查询、去设计索引、去回答 MySQL 相关面试题都会顺手很多。这篇文章适合三类人正在准备 MySQL 面试的开发者、刚接触 MySQL 想搞明白执行原理的新手以及写过很多 SQL 但从来没深究过“一条查询到底怎么跑完”的同行。内容不要求你有很深的底层基础我会尽量讲明白每一步在干什么以及这一步没干好会出什么问题。1. 先记住 MySQL 的两层架构这条查询的“主干道”和“分岔路”1.1 Server 层和存储引擎层到底怎么分工要说清一条 SQL 的执行过程第一步不是看“分析器”和“优化器”而是先搞懂 MySQL 的分层。MySQL 从宏观上分两层Server 层和存储引擎层。Server 层包括连接器、查询缓存8.0 之前才有、分析器、优化器、执行器还有内置函数、存储过程、触发器、视图这些通用功能。这一层不关心你的数据是怎么存到磁盘上的它只负责“处理 SQL 本身”——把 SQL 解析清楚、想出一个方案、去调用底层接口。存储引擎层则负责数据的具体读写。InnoDB 是默认引擎还有 MyISAM、Memory 等。这一层做的事情很“脏活累活”从磁盘读页、维护 B 树索引、处理行锁和事务。Server 层不关心 InnoDB 的索引结构到底是 B 树还是哈希表它只通过统一的 API 向引擎层要数据。我习惯用一个类比Server 层像是餐厅的餐厅经理他负责点菜、传菜、处理客人投诉存储引擎层是后厨经理对后厨说“给我上一盘番茄炒蛋”至于番茄怎么切、蛋怎么炒、锅用哪口经理不管。一条 SQL 查询走的是餐厅的流程但真正把数据端上来的是后厨。1.2 为什么理解分层对排查问题很重要因为它决定了你在排查问题时去哪个层面找原因。举个例子你写了SELECT * FROM user WHERE id 1结果报错说user表不存在这是 Server 层在分析器或者预处理器阶段就拦下来的根本到不了存储引擎。反过来如果你的查询走了索引还是很慢那问题可能出在 Server 层的优化器选错了索引也可能出在存储引擎层做了太多回表甚至可能出在磁盘 IO 本身。再比如你在 MyISAM 和 InnoDB 之间切换建表引擎你会发现上层 SQL 完全不用改因为它们都遵循同一套 Server 层接口协议。但是 MyISAM 不支持事务、不支持行锁这纯粹是引擎层能力的差异。以前有人问我“MySQL 的事务是怎么实现的”我说这个问题一半在 Server 层——比如事务的启动、提交语句是靠日志做保障另一半在 InnoDB 层——比如 MVCC、锁那些机制。你如果分不清两层这类问题很难讲透。2. 第一步到第四步连接器、查询缓存与分析器2.1 连接器你以为“连上数据库”这件事隐藏了权限校验的时机一条查询到达 MySQL 后第一站永远是连接器。连接器负责跟客户端建立连接、获取用户名密码、校验权限、管理连接。这里有一个非常容易在面试里被追问的点**权限验证是什么时候读取的**答案是连接建立时。也就是说MySQL 在连接建立的瞬间会把你的权限读取到内存里后面这条连接的所有操作都以这份快照为准。如果你在连接建立之后用另一个管理员账号改了权限当前这条已存在的连接不会立即生效必须重连才能用上新权限。实际运维中很多人改完权限会执行FLUSH PRIVILEGES如果连接没变化问题可能就出在这个“旧连接还拿着旧权限快照”上。连接器的另一个常踩坑点是连接超时。MySQL 默认的wait_timeout是 8 小时连接空闲超过这个时间会被服务端断开。但很多客户端连接池不会立刻感知导致你某天突然发现“连接断开了”程序里再拿这个连接执行 SQL 就报错。这个和 SQL 执行链路没关系但排查线上问题时你会频繁遇到它。建议接入方的连接池配置maxLifetime小于服务端的wait_timeout避免拿到半死连接。此外连接数量也是一个容易被忽略的压力点。max_connections默认上限是 1515.7 默认值具体看版本如果连接数打满新连接会报Too many connections。很多人以为这是慢查询导致的其实可能是连接没有及时释放或连接池配置过大。一条 SQL 的执行从连接开始而连接管理往往决定了这条 SQL 能否顺利“进门”。2.2 查询缓存8.0 之前曾存在的“高速公路”为什么后来被拆了连接建立并通过权限校验后MySQL 会检查查询缓存。查询缓存是一个 KV 结构key 是完整的 SQL 文本value 是查询结果。如果这条 SQL 之前执行过而且对应的表数据没变过命中缓存后就直接返回结果后面的分析器、优化器、执行器全部跳过。听起来很美好但查询缓存在 MySQL 8.0 里被彻底移除了。原因很简单缓存失效太频繁。只要表上任何一个数据发生了修改这个表所有的查询缓存条目全部失效。对于写入频繁的表缓存基本没命中率还要付出维护缓存的代价。网上有一堆“为什么 MySQL 不建议开 query cache”的文章核心就是这个。如果你还在用 5.7 或 5.6并且表是典型的读多写少可以考虑把query_cache_type设置为DEMAND然后只在需要的 SQL 里用SQL_CACHE关键字开启缓存。这样既不浪费维护成本又能精准缓存你想要的查询。不过大多数场景我不建议折腾这个因为它的收益已经被后来的内存缓冲池和索引优化覆盖得差不多了而且配置不好容易出现“慢得不明显但缓存碎片一堆”的局面。2.3 分析器一条 SQL 是怎么变成 MySQL 能理解的东西的绕过查询缓存之后SQL 文本交给分析器。分析器分两步词法分析和语法分析。词法分析是把 SQL 字符串拆成一个个“单词”识别哪些是关键字、哪些是表名、哪些是字符串常量。比如SELECT name FROM user WHERE id 1分析器会识别出SELECT是关键字name是字段名user是表名1是数字常量。这个过程不需要真正去数据库里查表它只是做“分词”。语法分析则是检查这些单词组合起来是否符合 SQL 语法规则最终生成一棵抽象语法树AST。如果你少写了一个逗号或者把WHERE写成了WHER E语法分析阶段就会报You have an error in your SQL syntax。这个报错信息一直被人吐槽难懂但它其实很明确——MySQL 在语法树构建失败时会指出它是在哪个字符附近“看不懂”的。注意分析器只负责语法层面它不会检查表是否存在、字段是否存在。这一步是后面预处理器要干的。**很多人把这层搞混了面试时被问到“分析器会检查表存在吗”容易答错。**分析器眼里SELECT * FROM not_exist_table和SELECT * FROM user都是合法 SQL只有当表名在数据库里找不到时才报错——那已经是下一阶段的事。3. 预处理器与优化器这条查询要访问什么、走哪条路3.1 预处理器真正告诉你表不存在、字段不存在的地方在分析器生成语法树之后、优化器接手之前MySQL 会做语义检查。这一步属于预处理器Preprocessor的职责。举几个它要做的事检查表是否存在检查字段是否存在展开SELECT *把星号替换成具体字段列表解析视图的定义所以说你执行SELECT * FROM user WHERE name 张三如果user表里没有name字段报错虽然发生在分析阶段之后但很多人把这个错误归到“分析器”其实严格来说是预处理器发现的。语义检查这一步在正常的八股文里很少被单独提出来但实际解释报错时你会频繁碰到。举个例子你执行一条SELECT * FROM orders WHERE order_time 2023-01-01报错Unknown column order_time in where clause。这个报错不是优化器给的也不是执行器给的而是预处理器做的语义校验。知道这一步的存在你排查“为什么字段名写错了错误却半天才出来”这类问题时思路会更清晰。3.2 优化器决策一条查询怎么执行的“导航系统”预处理通过后SQL 进入优化器。优化器的核心工作是决定用哪种方式执行这条查询包括选择使用哪个索引决定多表连接的顺序决定是否用嵌套循环连接、哈希连接8.0 之后支持决定ORDER BY是走索引顺序还是走文件排序这里的关键是优化器不是“人”它是基于统计数据做代价估算的。MySQL 会收集表的行数、索引基数Cardinality、索引选择性等信息然后估算不同执行路径的代价选一个它认为代价最小的。我见过很多人抱怨“明明有索引为什么优化器不走”。这个问题的本质往往是优化器算了一笔账觉得走索引回表的代价比全表扫描还高。比如表里 100 万行某个索引的区分度很低可能查出来的数据占了全表的 30%这种情况下优化器认为全表扫描更快因为它要按索引顺序读取再回表回表次数太多反而不如顺序扫描。这是很多“索引失效”误区的真相——不是索引失效了而是优化器权衡后放弃了它。3.3 使用 EXPLAIN 观察优化器的选择想确认优化器的决策最直接的工具就是EXPLAIN。看一条查询的执行计划重点看这几列type访问类型从好到差一般是system、const、eq_ref、ref、range、index、ALL。ALL说明全表扫描。possible_keys可能用到的索引列表。key实际使用的索引。rows优化器估算需要扫描的行数。Extra额外信息常见的有Using index覆盖索引、Using where、Using filesort文件排序、Using temporary临时表。以前有个同事告诉我他看 EXPLAIN 只看key是不是有值我建议他再多看一眼Extra。key有值只代表走了一个索引但Extra里的Using filesort和Using temporary往往才是真正影响性能的隐患。比如一个查询定位的数据量不大但因为ORDER BY走了文件排序反而可能比直接扫索引慢很多。4. 执行器与存储引擎真正开始触碰数据的环节4.1 执行器一行一行“搬运”数据的打工者优化器决定好执行计划后SQL 进入执行器。这一步才是真正开始调起存储引擎接口、读取数据、返回结果的环节。执行器会调用存储引擎的接口按照执行计划逐步读取数据。它做的事情可以理解为先调用引擎接口取第一行满足条件的记录再判断是否符合其他条件如果符合就把这行放到结果集里然后继续取下一行直到取完满足条件的所有行为止。这里有一个很关键的细节执行阶段还会再做一次权限校验。连接器阶段已经校验过一次用户权限了但那是粗粒度的“能不能连上”执行器阶段会对具体操作做更细的检查比如你有没有这张表的 SELECT 权限。所以你在某些场景下建好了连接执行查询时才报SELECT command denied to user就是因为这个阶段的权限校验拦住了你。执行器也会统计扫描行数。你在慢查询日志里看到的Rows_examined就是执行器实际从存储引擎层读取的行数。Rows_sent是最终返回给客户端的行数。如果Rows_examined远远大于Rows_sent说明查询在 Server 层过滤掉了很多行典型的可能就是“索引区分度低”或“回表后二次过滤”导致的。4.2 存储引擎层InnoDB 的数据读取流程Server 层通过统一接口调用存储引擎但“读取一行数据”这个动作最终是 InnoDB 来完成的。InnoDB 存储数据的基本单位是页Page默认 16KB。查询时InnoDB 会先把数据页从磁盘加载到内存的 Buffer Pool 中然后再从内存页里找到目标行。InnoDB 的索引是 B 树结构。主键索引聚簇索引的叶子节点保存的是整行数据二级索引的叶子节点保存的是主键值。如果你用二级索引查询且查询的字段不在索引里就需要拿着主键值再去聚簇索引里查一次整行数据这个过程叫“回表”。回表是很多查询变慢的根源。比如你执行SELECT * FROM user WHERE name 张三name字段上有普通索引。InnoDB 先在二级索引 B 树里找到名字对应的主键值列表然后逐个主键去聚簇索引里查整行。数据量少还好数据量大了每一次回表都是一次随机 IO性能会很糟糕。所以“尽量用覆盖索引”是一条非常核心的优化思路。如果查询需要的字段都包含在索引里InnoDB 直接在二级索引上就能拿全数据不需要回表这就是Using index提示的含义。4.3 排序、去重与临时表Server 层经常藏着的“隐性成本”在很多查询里真正拖慢速度的不是“查”这个动作而是“排”和“去重”。先看ORDER BY。如果排序字段恰好可以用索引顺序来满足那查询可以直接按索引顺序读不需要额外排序。比如二级索引是基于(user_id, create_time)建的你按WHERE user_id 123 ORDER BY create_time查B 树本来就是按这个复合索引排序的直接从索引里顺序读出来即可非常快。但如果排序字段不在索引里MySQL 就需要把数据先取出来再用sort_buffer做排序。sort_buffer_size默认约 256KB如果数据量超过它就要用磁盘临时文件做外部排序那个开销会大很多。EXPLAIN 里Using filesort指的就是这个。再看DISTINCT和GROUP BY。这两个操作通常需要借助临时表完成去重或分组。数据量一旦变大临时表可能会从内存临时表退化成磁盘临时表性能断崖式下降。我用过很多次这种查询印象最深的是有人写SELECT DISTINCT user_id FROM orders WHERE ...结果 orders 表几千万行这个查询跑了快二十秒。后来改成先缩小 WHERE 范围再 DISTINCT秒回。没有深入理解执行过程的话你根本不会意识到“临时表”这个中间产物才是真正的瓶颈。5. 慢查询排查实录顺着执行链路把一条 6 秒的 SQL 改到 0.02 秒前面讲的是原理这里我想用一个真实例子把整条链路串起来。这是我去年在处理订单报表时遇到过的一个典型案例。业务背景订单表orders约 700 万行。查询需求是查某个用户在最近 30 天下的订单按下单时间倒序排列。表上有两个单列索引idx_user_id(user_id)、idx_create_time(create_time)。SQL 大概是SELECT id, order_no, amount, create_time FROM orders WHERE user_id 10086 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC;第一次用 EXPLAIN 查看时结果是这样的possible_keysidx_user_id,idx_create_timekeyidx_user_idrows约 16 万ExtraUsing where; Using filesort看到Using filesort的第一反应是排序字段create_time不是索引的一部分所以要先拉出 16 万行再在sort_buffer里排序。当时我和同事直接认为“加一个复合索引就能解决”于是准备把索引改成(user_id, create_time)。但真正改之前我们用了optimizer_trace看了一下优化器的完整决策过程。它在每一步算的代价列得很清楚SET optimizer_trace enabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;从 trace 里看到优化器一开始也在纠结走idx_user_id能定位到 16 万行但之后要回表、要排序走idx_create_time能按时间顺序扫描但要在二级索引过滤 user_id之后还要回表。两个方案都不完美最终它选了它认为代价更低的那个。看了 trace 之后我们确认光靠单列索引怎么组合都无法同时解决范围条件和排序的问题必须重建一个真正匹配业务的复合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);因为WHERE user_id 10086 AND create_time ...用到了复合索引的前缀字段再加上ORDER BY create_time恰好可以利用复合索引的第二字段所以查询可以做到用索引定位到 10086 用户的所有订单然后直接沿着索引顺序读取天然就是按时间倒序的不需要额外排序。改完之后执行计划里的Using filesort消失了查询从 6 秒降到了 0.02 秒。这个案例给我最大的启发是看 EXPLAIN 不能只看key有没有值还要看Extra里的隐形操作。一条 SQL 的执行是一场“链路接力”分析器把 SQL 拆清楚优化器做选择执行器去执行存储引擎去读取。你只盯着其中某一环永远找不到性能瓶颈的全貌。6. 常见问题速查执行链路里最容易踩的 5 个坑顺着上面这条链路我整理了平时收到问得最多的几个问题基本都能在“某一条 SQL 执行过程的某一环”里找到答案。现象核心原因对应环节建议改完权限不生效已建立的连接还持有旧权限快照连接器重连或执行FLUSH PRIVILEGES后再确认报错说表名/字段不存在预处理器语义检查未通过预处理器检查表名、字段名拼写及库名建了索引却不走优化器认为回表成本高优化器复核索引区分度必要时FORCE INDEX或重建复合索引查询带ORDER BY很慢排序字段不满足索引顺序触发文件排序执行器/存储引擎建立与查询匹配的复合索引查询结果很小但Rows_examined很大索引定位后回表过滤了大量无关行存储引擎使用覆盖索引或缩小扫描范围还有两个小细节也值得单独说。第一不要在索引列上使用函数。比如WHERE DATE(create_time) 2024-01-01会导致索引失效因为优化器无法直接利用 B 树上的有序值。正确做法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二防止 SQL 注入永远要靠参数化查询不要靠拼接字符串。这条 SQL 在分析器阶段就会发现问题因为拼接用户输入会改变整条 SQL 的语法结构。最小权限原则在这里也适用业务账号只给需要的表授权别一上来就GRANT ALL。这条建议虽然不属于执行流程的性能问题但它属于“SQL 到达执行链路之前是否安全”的问题一次注入就能让你的整条链路被人玩弄于股掌之间。7. 写在最后我的一点体会回看这条链路的每个环节你会发现它不算复杂但真正想把它用好我个人的体会是不要死记硬背模块名称而是找一条真实慢查询沿着链路排查一遍。我第一次真正理解“一条 SQL 是如何执行的”不是在看文档的时候而是在定位一条耗时的线上 SQL 时一步步去看它的执行计划、数据扫描量、排序方式最后发现问题出在索引设计上的那一刻。建议你可以拿自己的业务表做个小实验找一条平时跑得慢的查询先EXPLAIN看执行计划再看Rows_examined和Extra最后用optimizer_trace看优化器的决策过程。把这些东西过一遍你头脑里的“连接器、分析器、优化器、执行器”就不再是八股文里的名词而是一张张能直接指导你优化 SQL 的地图。对我个人来说理解一条查询的执行链路的最大收获是排查问题不再靠猜也不再靠网上搜各种“灵丹妙药”而是能按图索骥顺着代码和数据一层层挖到根因。如果你看完之后也能试着把这条链路复述给别人听并解释清楚“为什么优化器可能不选你想要的索引”那我这篇文章的目的就算达到了。
MySQL一条SQL查询的完整执行链路：从索引选择到慢查询排查实战
上周帮同事排查一条线上慢查询SQL 很简单就是按用户查最近一段时间的订单结果执行了 6 秒多。我们打开 EXPLAIN 看了一眼索引也用上了rows 也不算离谱但就是慢。后来一路顺着 MySQL 的执行链路往下追才发现真正的瓶颈藏在排序和回表里。从那之后我意识到一件事网上讲“一条 SQL 查询语句是如何执行的”的文章很多但大多数停留在背八股——连接器、分析器、优化器、执行器背得滚瓜烂熟真到了排查问题的时候这些知识点根本对不上号。这篇文章我想换个讲法。我们不只把 MySQL 执行一条 SQL 查询的完整链路拆开还会把每一条链路对应到真实的排查场景里比如你什么时候会碰到权限问题、为什么优化器选错了索引、EXPLAIN 里的 Using filesort 到底意味着什么。理解了这条链路你再去分析慢查询、去设计索引、去回答 MySQL 相关面试题都会顺手很多。这篇文章适合三类人正在准备 MySQL 面试的开发者、刚接触 MySQL 想搞明白执行原理的新手以及写过很多 SQL 但从来没深究过“一条查询到底怎么跑完”的同行。内容不要求你有很深的底层基础我会尽量讲明白每一步在干什么以及这一步没干好会出什么问题。1. 先记住 MySQL 的两层架构这条查询的“主干道”和“分岔路”1.1 Server 层和存储引擎层到底怎么分工要说清一条 SQL 的执行过程第一步不是看“分析器”和“优化器”而是先搞懂 MySQL 的分层。MySQL 从宏观上分两层Server 层和存储引擎层。Server 层包括连接器、查询缓存8.0 之前才有、分析器、优化器、执行器还有内置函数、存储过程、触发器、视图这些通用功能。这一层不关心你的数据是怎么存到磁盘上的它只负责“处理 SQL 本身”——把 SQL 解析清楚、想出一个方案、去调用底层接口。存储引擎层则负责数据的具体读写。InnoDB 是默认引擎还有 MyISAM、Memory 等。这一层做的事情很“脏活累活”从磁盘读页、维护 B 树索引、处理行锁和事务。Server 层不关心 InnoDB 的索引结构到底是 B 树还是哈希表它只通过统一的 API 向引擎层要数据。我习惯用一个类比Server 层像是餐厅的餐厅经理他负责点菜、传菜、处理客人投诉存储引擎层是后厨经理对后厨说“给我上一盘番茄炒蛋”至于番茄怎么切、蛋怎么炒、锅用哪口经理不管。一条 SQL 查询走的是餐厅的流程但真正把数据端上来的是后厨。1.2 为什么理解分层对排查问题很重要因为它决定了你在排查问题时去哪个层面找原因。举个例子你写了SELECT * FROM user WHERE id 1结果报错说user表不存在这是 Server 层在分析器或者预处理器阶段就拦下来的根本到不了存储引擎。反过来如果你的查询走了索引还是很慢那问题可能出在 Server 层的优化器选错了索引也可能出在存储引擎层做了太多回表甚至可能出在磁盘 IO 本身。再比如你在 MyISAM 和 InnoDB 之间切换建表引擎你会发现上层 SQL 完全不用改因为它们都遵循同一套 Server 层接口协议。但是 MyISAM 不支持事务、不支持行锁这纯粹是引擎层能力的差异。以前有人问我“MySQL 的事务是怎么实现的”我说这个问题一半在 Server 层——比如事务的启动、提交语句是靠日志做保障另一半在 InnoDB 层——比如 MVCC、锁那些机制。你如果分不清两层这类问题很难讲透。2. 第一步到第四步连接器、查询缓存与分析器2.1 连接器你以为“连上数据库”这件事隐藏了权限校验的时机一条查询到达 MySQL 后第一站永远是连接器。连接器负责跟客户端建立连接、获取用户名密码、校验权限、管理连接。这里有一个非常容易在面试里被追问的点**权限验证是什么时候读取的**答案是连接建立时。也就是说MySQL 在连接建立的瞬间会把你的权限读取到内存里后面这条连接的所有操作都以这份快照为准。如果你在连接建立之后用另一个管理员账号改了权限当前这条已存在的连接不会立即生效必须重连才能用上新权限。实际运维中很多人改完权限会执行FLUSH PRIVILEGES如果连接没变化问题可能就出在这个“旧连接还拿着旧权限快照”上。连接器的另一个常踩坑点是连接超时。MySQL 默认的wait_timeout是 8 小时连接空闲超过这个时间会被服务端断开。但很多客户端连接池不会立刻感知导致你某天突然发现“连接断开了”程序里再拿这个连接执行 SQL 就报错。这个和 SQL 执行链路没关系但排查线上问题时你会频繁遇到它。建议接入方的连接池配置maxLifetime小于服务端的wait_timeout避免拿到半死连接。此外连接数量也是一个容易被忽略的压力点。max_connections默认上限是 1515.7 默认值具体看版本如果连接数打满新连接会报Too many connections。很多人以为这是慢查询导致的其实可能是连接没有及时释放或连接池配置过大。一条 SQL 的执行从连接开始而连接管理往往决定了这条 SQL 能否顺利“进门”。2.2 查询缓存8.0 之前曾存在的“高速公路”为什么后来被拆了连接建立并通过权限校验后MySQL 会检查查询缓存。查询缓存是一个 KV 结构key 是完整的 SQL 文本value 是查询结果。如果这条 SQL 之前执行过而且对应的表数据没变过命中缓存后就直接返回结果后面的分析器、优化器、执行器全部跳过。听起来很美好但查询缓存在 MySQL 8.0 里被彻底移除了。原因很简单缓存失效太频繁。只要表上任何一个数据发生了修改这个表所有的查询缓存条目全部失效。对于写入频繁的表缓存基本没命中率还要付出维护缓存的代价。网上有一堆“为什么 MySQL 不建议开 query cache”的文章核心就是这个。如果你还在用 5.7 或 5.6并且表是典型的读多写少可以考虑把query_cache_type设置为DEMAND然后只在需要的 SQL 里用SQL_CACHE关键字开启缓存。这样既不浪费维护成本又能精准缓存你想要的查询。不过大多数场景我不建议折腾这个因为它的收益已经被后来的内存缓冲池和索引优化覆盖得差不多了而且配置不好容易出现“慢得不明显但缓存碎片一堆”的局面。2.3 分析器一条 SQL 是怎么变成 MySQL 能理解的东西的绕过查询缓存之后SQL 文本交给分析器。分析器分两步词法分析和语法分析。词法分析是把 SQL 字符串拆成一个个“单词”识别哪些是关键字、哪些是表名、哪些是字符串常量。比如SELECT name FROM user WHERE id 1分析器会识别出SELECT是关键字name是字段名user是表名1是数字常量。这个过程不需要真正去数据库里查表它只是做“分词”。语法分析则是检查这些单词组合起来是否符合 SQL 语法规则最终生成一棵抽象语法树AST。如果你少写了一个逗号或者把WHERE写成了WHER E语法分析阶段就会报You have an error in your SQL syntax。这个报错信息一直被人吐槽难懂但它其实很明确——MySQL 在语法树构建失败时会指出它是在哪个字符附近“看不懂”的。注意分析器只负责语法层面它不会检查表是否存在、字段是否存在。这一步是后面预处理器要干的。**很多人把这层搞混了面试时被问到“分析器会检查表存在吗”容易答错。**分析器眼里SELECT * FROM not_exist_table和SELECT * FROM user都是合法 SQL只有当表名在数据库里找不到时才报错——那已经是下一阶段的事。3. 预处理器与优化器这条查询要访问什么、走哪条路3.1 预处理器真正告诉你表不存在、字段不存在的地方在分析器生成语法树之后、优化器接手之前MySQL 会做语义检查。这一步属于预处理器Preprocessor的职责。举几个它要做的事检查表是否存在检查字段是否存在展开SELECT *把星号替换成具体字段列表解析视图的定义所以说你执行SELECT * FROM user WHERE name 张三如果user表里没有name字段报错虽然发生在分析阶段之后但很多人把这个错误归到“分析器”其实严格来说是预处理器发现的。语义检查这一步在正常的八股文里很少被单独提出来但实际解释报错时你会频繁碰到。举个例子你执行一条SELECT * FROM orders WHERE order_time 2023-01-01报错Unknown column order_time in where clause。这个报错不是优化器给的也不是执行器给的而是预处理器做的语义校验。知道这一步的存在你排查“为什么字段名写错了错误却半天才出来”这类问题时思路会更清晰。3.2 优化器决策一条查询怎么执行的“导航系统”预处理通过后SQL 进入优化器。优化器的核心工作是决定用哪种方式执行这条查询包括选择使用哪个索引决定多表连接的顺序决定是否用嵌套循环连接、哈希连接8.0 之后支持决定ORDER BY是走索引顺序还是走文件排序这里的关键是优化器不是“人”它是基于统计数据做代价估算的。MySQL 会收集表的行数、索引基数Cardinality、索引选择性等信息然后估算不同执行路径的代价选一个它认为代价最小的。我见过很多人抱怨“明明有索引为什么优化器不走”。这个问题的本质往往是优化器算了一笔账觉得走索引回表的代价比全表扫描还高。比如表里 100 万行某个索引的区分度很低可能查出来的数据占了全表的 30%这种情况下优化器认为全表扫描更快因为它要按索引顺序读取再回表回表次数太多反而不如顺序扫描。这是很多“索引失效”误区的真相——不是索引失效了而是优化器权衡后放弃了它。3.3 使用 EXPLAIN 观察优化器的选择想确认优化器的决策最直接的工具就是EXPLAIN。看一条查询的执行计划重点看这几列type访问类型从好到差一般是system、const、eq_ref、ref、range、index、ALL。ALL说明全表扫描。possible_keys可能用到的索引列表。key实际使用的索引。rows优化器估算需要扫描的行数。Extra额外信息常见的有Using index覆盖索引、Using where、Using filesort文件排序、Using temporary临时表。以前有个同事告诉我他看 EXPLAIN 只看key是不是有值我建议他再多看一眼Extra。key有值只代表走了一个索引但Extra里的Using filesort和Using temporary往往才是真正影响性能的隐患。比如一个查询定位的数据量不大但因为ORDER BY走了文件排序反而可能比直接扫索引慢很多。4. 执行器与存储引擎真正开始触碰数据的环节4.1 执行器一行一行“搬运”数据的打工者优化器决定好执行计划后SQL 进入执行器。这一步才是真正开始调起存储引擎接口、读取数据、返回结果的环节。执行器会调用存储引擎的接口按照执行计划逐步读取数据。它做的事情可以理解为先调用引擎接口取第一行满足条件的记录再判断是否符合其他条件如果符合就把这行放到结果集里然后继续取下一行直到取完满足条件的所有行为止。这里有一个很关键的细节执行阶段还会再做一次权限校验。连接器阶段已经校验过一次用户权限了但那是粗粒度的“能不能连上”执行器阶段会对具体操作做更细的检查比如你有没有这张表的 SELECT 权限。所以你在某些场景下建好了连接执行查询时才报SELECT command denied to user就是因为这个阶段的权限校验拦住了你。执行器也会统计扫描行数。你在慢查询日志里看到的Rows_examined就是执行器实际从存储引擎层读取的行数。Rows_sent是最终返回给客户端的行数。如果Rows_examined远远大于Rows_sent说明查询在 Server 层过滤掉了很多行典型的可能就是“索引区分度低”或“回表后二次过滤”导致的。4.2 存储引擎层InnoDB 的数据读取流程Server 层通过统一接口调用存储引擎但“读取一行数据”这个动作最终是 InnoDB 来完成的。InnoDB 存储数据的基本单位是页Page默认 16KB。查询时InnoDB 会先把数据页从磁盘加载到内存的 Buffer Pool 中然后再从内存页里找到目标行。InnoDB 的索引是 B 树结构。主键索引聚簇索引的叶子节点保存的是整行数据二级索引的叶子节点保存的是主键值。如果你用二级索引查询且查询的字段不在索引里就需要拿着主键值再去聚簇索引里查一次整行数据这个过程叫“回表”。回表是很多查询变慢的根源。比如你执行SELECT * FROM user WHERE name 张三name字段上有普通索引。InnoDB 先在二级索引 B 树里找到名字对应的主键值列表然后逐个主键去聚簇索引里查整行。数据量少还好数据量大了每一次回表都是一次随机 IO性能会很糟糕。所以“尽量用覆盖索引”是一条非常核心的优化思路。如果查询需要的字段都包含在索引里InnoDB 直接在二级索引上就能拿全数据不需要回表这就是Using index提示的含义。4.3 排序、去重与临时表Server 层经常藏着的“隐性成本”在很多查询里真正拖慢速度的不是“查”这个动作而是“排”和“去重”。先看ORDER BY。如果排序字段恰好可以用索引顺序来满足那查询可以直接按索引顺序读不需要额外排序。比如二级索引是基于(user_id, create_time)建的你按WHERE user_id 123 ORDER BY create_time查B 树本来就是按这个复合索引排序的直接从索引里顺序读出来即可非常快。但如果排序字段不在索引里MySQL 就需要把数据先取出来再用sort_buffer做排序。sort_buffer_size默认约 256KB如果数据量超过它就要用磁盘临时文件做外部排序那个开销会大很多。EXPLAIN 里Using filesort指的就是这个。再看DISTINCT和GROUP BY。这两个操作通常需要借助临时表完成去重或分组。数据量一旦变大临时表可能会从内存临时表退化成磁盘临时表性能断崖式下降。我用过很多次这种查询印象最深的是有人写SELECT DISTINCT user_id FROM orders WHERE ...结果 orders 表几千万行这个查询跑了快二十秒。后来改成先缩小 WHERE 范围再 DISTINCT秒回。没有深入理解执行过程的话你根本不会意识到“临时表”这个中间产物才是真正的瓶颈。5. 慢查询排查实录顺着执行链路把一条 6 秒的 SQL 改到 0.02 秒前面讲的是原理这里我想用一个真实例子把整条链路串起来。这是我去年在处理订单报表时遇到过的一个典型案例。业务背景订单表orders约 700 万行。查询需求是查某个用户在最近 30 天下的订单按下单时间倒序排列。表上有两个单列索引idx_user_id(user_id)、idx_create_time(create_time)。SQL 大概是SELECT id, order_no, amount, create_time FROM orders WHERE user_id 10086 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC;第一次用 EXPLAIN 查看时结果是这样的possible_keysidx_user_id,idx_create_timekeyidx_user_idrows约 16 万ExtraUsing where; Using filesort看到Using filesort的第一反应是排序字段create_time不是索引的一部分所以要先拉出 16 万行再在sort_buffer里排序。当时我和同事直接认为“加一个复合索引就能解决”于是准备把索引改成(user_id, create_time)。但真正改之前我们用了optimizer_trace看了一下优化器的完整决策过程。它在每一步算的代价列得很清楚SET optimizer_trace enabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;从 trace 里看到优化器一开始也在纠结走idx_user_id能定位到 16 万行但之后要回表、要排序走idx_create_time能按时间顺序扫描但要在二级索引过滤 user_id之后还要回表。两个方案都不完美最终它选了它认为代价更低的那个。看了 trace 之后我们确认光靠单列索引怎么组合都无法同时解决范围条件和排序的问题必须重建一个真正匹配业务的复合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);因为WHERE user_id 10086 AND create_time ...用到了复合索引的前缀字段再加上ORDER BY create_time恰好可以利用复合索引的第二字段所以查询可以做到用索引定位到 10086 用户的所有订单然后直接沿着索引顺序读取天然就是按时间倒序的不需要额外排序。改完之后执行计划里的Using filesort消失了查询从 6 秒降到了 0.02 秒。这个案例给我最大的启发是看 EXPLAIN 不能只看key有没有值还要看Extra里的隐形操作。一条 SQL 的执行是一场“链路接力”分析器把 SQL 拆清楚优化器做选择执行器去执行存储引擎去读取。你只盯着其中某一环永远找不到性能瓶颈的全貌。6. 常见问题速查执行链路里最容易踩的 5 个坑顺着上面这条链路我整理了平时收到问得最多的几个问题基本都能在“某一条 SQL 执行过程的某一环”里找到答案。现象核心原因对应环节建议改完权限不生效已建立的连接还持有旧权限快照连接器重连或执行FLUSH PRIVILEGES后再确认报错说表名/字段不存在预处理器语义检查未通过预处理器检查表名、字段名拼写及库名建了索引却不走优化器认为回表成本高优化器复核索引区分度必要时FORCE INDEX或重建复合索引查询带ORDER BY很慢排序字段不满足索引顺序触发文件排序执行器/存储引擎建立与查询匹配的复合索引查询结果很小但Rows_examined很大索引定位后回表过滤了大量无关行存储引擎使用覆盖索引或缩小扫描范围还有两个小细节也值得单独说。第一不要在索引列上使用函数。比如WHERE DATE(create_time) 2024-01-01会导致索引失效因为优化器无法直接利用 B 树上的有序值。正确做法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二防止 SQL 注入永远要靠参数化查询不要靠拼接字符串。这条 SQL 在分析器阶段就会发现问题因为拼接用户输入会改变整条 SQL 的语法结构。最小权限原则在这里也适用业务账号只给需要的表授权别一上来就GRANT ALL。这条建议虽然不属于执行流程的性能问题但它属于“SQL 到达执行链路之前是否安全”的问题一次注入就能让你的整条链路被人玩弄于股掌之间。7. 写在最后我的一点体会回看这条链路的每个环节你会发现它不算复杂但真正想把它用好我个人的体会是不要死记硬背模块名称而是找一条真实慢查询沿着链路排查一遍。我第一次真正理解“一条 SQL 是如何执行的”不是在看文档的时候而是在定位一条耗时的线上 SQL 时一步步去看它的执行计划、数据扫描量、排序方式最后发现问题出在索引设计上的那一刻。建议你可以拿自己的业务表做个小实验找一条平时跑得慢的查询先EXPLAIN看执行计划再看Rows_examined和Extra最后用optimizer_trace看优化器的决策过程。把这些东西过一遍你头脑里的“连接器、分析器、优化器、执行器”就不再是八股文里的名词而是一张张能直接指导你优化 SQL 的地图。对我个人来说理解一条查询的执行链路的最大收获是排查问题不再靠猜也不再靠网上搜各种“灵丹妙药”而是能按图索骥顺着代码和数据一层层挖到根因。如果你看完之后也能试着把这条链路复述给别人听并解释清楚“为什么优化器可能不选你想要的索引”那我这篇文章的目的就算达到了。