MySQL 一条 SQL 的执行流程这个题目我讲过不少次也在面试时问过不少人。能讲清楚的其实不多。大部分人都在背连接器、分析器、优化器、执行器这八个字但一遇到实际问题——为什么加了索引还是慢为什么执行计划显示走了 A 索引实际表现却像在扫全表为什么一条 UPDATE 要同时写两类日志——就答不上来了。这篇文章我按一条 SQL 从客户端发出到存储引擎返回结果的实际过程把每一层的职责、关键参数、常见坑都过一遍。适合准备面试的同学也适合正在被慢 SQL 折磨的后台开发和 DBA。理清这条链路之后再看执行计划、慢日志、锁等待这些问题会通透很多。1. 连接器与协议栈一条 SQL 进入 MySQL 的第一道关卡1.1 连接建立的过程与认证细节任何一条 SQL 要进入 MySQL第一步永远是建立连接。这个连接从底子上讲是 TCP 连接但 MySQL 在 TCP 之上还跑了一套自己的握手协议。过程大概是这样的客户端发起 TCP 三次握手和服务端建立网络连接。MySQL 服务器向客户端发送握手包包含版本号、连接 ID、认证插件等信息。客户端把用户名、密码、数据库名等信息加密后回传。服务端校验账号是否存在、密码是否正确。密码错误会直接报Access denied然后断开连接。认证通过后服务端还会把该用户拥有的权限数据加载到内存里。注意这里加载的是一份权限快照后续这条连接里所有操作都基于这份快照判断不会实时去读权限表。所以如果修改了权限已经存在的连接不会立刻生效要重新连接才能拿到新权限。这里有一个很多人踩过的坑MySQL 8.0 默认的认证插件改成了caching_sha2_password而 MySQL 5.7 时代大量客户端用的是mysql_native_password。如果拿老的客户端工具连 8.0会报Authentication plugin caching_sha2_password cannot be loaded之类的错误。解决方式有两个一是升级客户端驱动这是推荐做法二是把该用户的认证插件改成老的治标不治本不推荐在长期项目里这样干。1.2 连接参数与常见问题连接层的核心参数不多但每一个都能让线上出大事。max_connections是最常见的。默认值是 151很多业务连接池一扩容就会打满。一旦连接数满了新连接会直接报Too many connections。这里有个容易被忽略的细节MySQL 的max_connections数的是连接数而不是活跃连接数。连接池里的空闲连接也算在内所以就算你的服务没什么请求只要连接池初始化了 50 个连接这 50 个就占着名额。wait_timeout控制非交互连接的空闲超时默认 8 小时interactive_timeout控制命令行这种交互式连接的空闲超时。生产环境一般会把wait_timeout调小一些比如 60 秒或 300 秒免得空闲连接堆积。但调太小心也会遇到问题应用侧连接池还没把连接回收MySQL 已经把连接断掉了应用的连接池拿到一个死连接第一次用它执行 SQL 会报MySQL server has gone away。所以调完这个参数必须同时改应用连接池的空闲检测配置。还有一个容易被忽略的点MySQL 采用的是每个连接一个线程的模型。连接一多线程数就多上下文切换开销和内存占用都会上涨。MySQL 8.0 里线程池依然是企业版特性社区版没有。所以面对大量短连接要谨慎很多公司会在应用层做连接复用避免频繁创建销毁连接。1.3 连接层的排障视角实际排除慢 SQL 问题时第一步不是看 SQL 本身而是看这条 SQL 的时间消耗到底花在哪一段。show processlist是连接层最常用的排查命令它能把当前所有连接正在执行的命令和状态列出来。最常见的几种状态Sleep连接空闲没有在执行任何 SQL。Query正在执行查询。Sending data这个状态名有误导性它不是正在发送数据而是正在读取数据并把结果准备好SQL 卡在这个状态基本可以判定是存储引擎层面在拼命干活。Waiting for table metadata lock在等元数据锁通常是有人在执行 DDL把表锁住了。Waiting for ... lock在等待行锁或表锁。如果show processlist里一堆Query状态、Sending data耗时很高那问题大概率出在优化器或执行器上也就是本文后半部分要讲的内容。连接层本身并没有真正处理你的 SQL它只是负责把人放进来、把路修好。2. 解析与预处理MySQL 在真正执行前先要看懂你的 SQL2.1 查询缓存的兴衰一个已经消失的环节如果是老一点的 MySQL 教程讲到 SQL 执行流程时会在连接器之后先提查询缓存SQL 文本完全一致时直接返回缓存结果不再往下走。这个机制在 MySQL 8.0 里已经被彻底移除了原因是负面作用远大于收益。查询缓存有一个致命弱点只要表上发生任何写操作这张表相关的所有查询缓存全部失效。写多一点场景缓存命中率极低反而还要付出维护缓存的成本。单条 SQL 配合精确匹配的读多写少场景可能有点用但对绝大多数业务系统来说是纯负担。所以现在讨论一条 SQL 的执行流程直接从连接之后跳到了解析阶段。如果你在老文章里看到先查缓存这一步知道那是历史版本的行为即可。2.2 词法分析与语法分析SQL 是怎么被读进去的连接建立后SQL 文本会交给解析器。解析分两个阶段词法分析和语法分析。词法分析负责把 SQL 字符串拆成一个个 token。例如SELECT id, name FROM user WHERE age 20会被拆成SELECT、id、,、name、FROM、user、WHERE、age、、20这些基本单元。这个阶段会识别哪些是关键字、哪些是标识符、哪些是字面量。语法分析则把这些 token 按照 SQL 语法规则组合成一棵语法树。这棵语法树会表达出 SQL 的层次结构比如哪个条件是 WHERE 下的子节点JOIN 的左右表分别是谁。这个阶段如果语法错误MySQL 会直接报You have an error in your SQL syntax。这里有个实际体验MySQL 的语法报错位置经常让人摸不着头脑因为它从左往右解析可能被前面的某个歧义影响定位到的位置并不是真正写错的地方。遇到这类报错优先把 SQL 拆短分段执行比盯着错误位置死磕效率高得多。2.3 预处理阶段表、列、权限的校验语法树生成后还要经过预处理阶段MySQL 会做这么几件事检查表是否存在。检查列是否存在。检查函数和表达式是否合法。如果使用了视图会把视图的定义展开合并进来。对涉及的表做权限校验看当前用户是否有对应的 SELECT、INSERT、UPDATE、DELETE 权限。这个阶段的报错已经比语法错误友好很多了。Unknown column xxx in where clause就是典型例子。如果你在执行计划里看到 MySQL 优化器没有使用预期索引可以先回看这一层列名写错、表名写错都会在这里暴露根本走不到优化器。还有一个细节值得注意预处理阶段会进行常量折叠之类的简单优化。比如WHERE id 1 1会被改写成WHERE id 2这种等效变换在进入优化器之前就完成了。所以有些你自以为写得很聪明的表达式MySQL 早就帮你算过了。2.4 解析阶段的常见误区解析阶段不做任何业务优化也不管你的索引怎么设计。它只关心一件事这条 SQL 是不是合法的、对象是不是存在的。很多人以为SELECT *会在这个阶段把*展开成所有列实际上展开发生在预处理阶段。而且SELECT *对解析阶段没负担真正的代价在执行阶段——它会让 InnoDB 读出所有列的数据增加回表和网络传输成本。所以别指望解析器帮你优化字段列表这个工作得自己写清楚列名。3. 优化器决定怎么查才是最关键的一步3.1 优化器在优化什么语法树合法、表列权限都通过之后SQL 的执行权交给优化器。优化器要回答一个核心问题这条 SQL 怎么查最划算。比如SELECT * FROM t WHERE a 1 AND b 2如果 a 和 b 上都有单列索引优化器要决定走哪个索引如果其中一个条件选择性高另一个低还要考虑是否需要回表。多表 JOIN 时还要决定先查哪张表、后查哪张表连接顺序不同临时结果集大小可能差几十倍。这个阶段还会决定是否使用临时表、是否使用排序、是否可以把某些条件下推到存储引擎。优化器的输入是语法树和表的统计信息输出是一棵执行计划树。执行计划里包含了每一步的访问方式、读取顺序、预估行数等信息。EXPLAIN命令展示的就是这个执行计划的概要。3.2 成本模型与统计信息优化器靠的不是经验而是一套代价估算模型。每一条执行路径都会被打一个分这个分约等于IO 成本 CPU 成本。IO 成本主要指读取数据页的开销CPU 成本主要指行级处理和计算的消耗。既然要算成本就要知道表里有多少行、索引有多少不同的值。这些信息来自统计信息。二级索引上的Cardinality基数就是统计信息里的核心指标它表示索引列上有多少个不同的值。SHOW INDEX FROM user能看到每个索引的Cardinality。这里有个关键点Cardinality是从采样的数据页里估算出来的不是实时的精确值。数据频繁增删改之后统计信息可能滞后导致优化器对行数的估算偏差很大。很多执行计划显示走了索引但还是很慢的案例根源就是统计信息失真。遇到这种情况执行ANALYZE TABLE user重新收集统计信息往往能立竿见影。优化器还会参考一个额外的参数innodb_stats_persistent它控制统计信息是否持久化。默认是开启的统计信息会存到磁盘MySQL 8.0 里相关的采样页数也有独立参数控制。生产环境建议保持默认不要频繁手动采样因为大表执行ANALYZE本身也有成本。3.3 选错索引的经典场景与处理我见过最典型的优化器选错索引是等值条件里有一个低区分度列和一个高区分度列同时存在时发生的。举个实际一点的例子一张订单表status列只有三个值create_time列区分度很高。有idx_status和idx_create_time两个单列索引。查询条件是status 1 AND create_time 2024-01-01。优化器计算时status 1是等值条件它推断能快速定位到一小块区域create_time 2024-01-01是范围条件范围可能很大。于是它可能选择走idx_status先把所有 status1 的记录找出来再逐条过滤时间。但如果status1的记录占总量的 60%这一步就已经扛不住了。反过来走create_time索引虽然需要扫较大范围但配合 where 下推反而更快。遇到这种情况我的排查步骤是这样用EXPLAIN看当前走的是哪个索引预估行数多少。对比实际行数和预估行数偏差大就ANALYZE TABLE。统计信息正常但优化器还是选错可以用FORCE INDEX强制走正确索引作为短期手段。长期方案是调整索引结构把两个条件拼成联合索引让优化器有更好的选择。FORCE INDEX并非银弹。它的问题在于把优化器的选择固化成了程序员的选择一旦数据分布变化这个强制选择同样可能变差。所以在线上用FORCE INDEX时一定要留下注释和追踪过几个月回来看一次是否需要移除。3.4 干预优化器决策的几种手段除了FORCE INDEX/USE INDEX/IGNORE INDEX还有几个常用手段改写 SQL。比如把OR改成UNION ALL把子查询改成 JOIN。有些改写能让优化器看到更清晰的语义从而选择更优执行计划。调整optimizer_switch。MySQL 提供了一系列优化开关比如index_condition_pushdown、mrr、batched_key_access等。线上调整需要谨慎做好 A/B 对比。拆分排序。对应ORDER BY和LIMIT引发的filesort可以尝试让排序字段和查询条件走向同一个联合索引。使用直方图。MySQL 8.0 支持ANALYZE TABLE t UPDATE HISTOGRAM ON col为列生成直方图统计信息。某些非索引列上的等值和范围条件有了直方图后优化器判断会更准确。没有一种手段能通吃所有场景。我个人的习惯是先确认统计信息再确认执行计划最后才考虑用提示干预。顺序反了很容易变成头痛医头。4. 执行器与存储引擎数据真正被一行行读出来4.1 执行器与存储引擎的分工优化器产出执行计划后真正干活的是执行器。执行器和存储引擎是分开的Server 层负责执行流程控制、条件过滤、函数计算和结果集组装存储引擎层负责数据的实际读写。InnoDB 是 MySQL 默认且最常用的存储引擎下面的内容都基于 InnoDB 展开。执行器会按照执行计划逐步执行每执行一步就向存储引擎索要数据。比如一个简单的全表扫描执行器的逻辑就是读取第一行 - 判断条件 - 决定是否加入结果集 - 读取下一行 - 重复直到读完全部行。这里的关键接口是 InnoDB 的行读取接口。执行器拿到一行数据后会对它做 Server 层的过滤判断。也就是说WHERE 条件并不总是在存储引擎层就过滤完的有一部分是在 Server 层完成的。这个职责划分直接影响执行效率和索引设计。4.2 一行数据的旅程从磁盘到内存再到结果集InnoDB 读写的最小单位是 16KB 的数据页不是单行。所以哪怕你只查一行InnoDB 也先把这一行所在的数据页从磁盘加载到 Buffer Pool。Buffer Pool 就是 InnoDB 的内存缓存区读过的数据页会留在里面后续再读同一页就不用访问磁盘了。如果查询走的是二级索引过程会更复杂。二级索引的叶子节点存的是索引列值和主键值。查WHERE name 张三InnoDB 先在idx_name索引的 B 树里找到张三对应的叶子节点拿到主键再拿主键回聚簇索引主键索引去查完整行数据。这个回表动作是很多查询慢的主要来源。如果只需要索引里已经存在的列就不需要回表。比如idx_name覆盖了 name 和 age执行SELECT name, age FROM user WHERE name 张三直接在索引里拿数据就行。执行计划里 Extra 会显示Using index这被称为覆盖索引扫描性能远高于每次都回表。数据量一大回表的行数哪怕只有几千条如果分布在不同数据页里也会变成几千次随机 IO。顺序 IO 快、随机 IO 慢这是存储设备上无法绕开的物理规律。覆盖索引能够从根上消除回表所以在高频查询中设计联合索引时把要查询的字段带进去是回报很高的一件事。4.3 Server 层和引擎层的过滤职责ICP 能省多少事索引条件下推Index Condition Pushdown简称 ICP是理解过滤职责最典型的功能。举个例子SELECT * FROM user WHERE name 张三 AND age 20;假设只有idx_name没有联合索引。没有 ICP 时InnoDB 会先在索引里把name 张三的每条记录找出来把完整行数据回表读出来交给 Server 层判断age 20。如果有 ICPInnoDB 会在读取二级索引时就判断age 20这个条件不满足的直接跳过不触发回表。区别有多大如果张三有 100 条记录其中只有 10 条满足age 20无 ICP 要回表 100 次有 ICP 只回表 10 次。这个差距在数据量大时就是数量级的。执行计划里 Extra 显示Using index condition就是 ICP 生效的标志。ICP 是 MySQL 5.6 引入的默认开启通过optimizer_switch里的index_condition_pushdown控制。正常情况下不需要关闭但如果遇到踩坑怀疑是 ICP 引起的执行计划变化可以临时关闭做对比验证。4.4 从执行细节理解为什么有些索引设计是无效的理解了执行器的行为之后就能解释很多索引优化里的常识。在WHERE条件列上做函数运算比如WHERE DATE(create_time) 2024-01-01索引就失效了。原因不是 MySQL不认识函数而是索引列无法直接与传入值做匹配优化器只能走全表扫描。改写成create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00才能用上索引。隐式类型转换同理。字符串列phone和数字字面量比较时MySQL 会把字符串转成数字导致索引列作为函数参数处理索引失效。WHERE phone 13800000000大概率扫全表WHERE phone 13800000000才能走索引。联合索引的最左前缀原则本质上是因为二级索引 B 树按第一个字段、第二个字段...的顺序组织。跳过最左列索引无法定位跳过中间列只能做部分过滤。这些坑在执行器这一层都能得到解释。这也是为什么我说理解执行流程比背索引规则更重要——规则会忘原理不会。5. 一条更新语句的幕后redo log、binlog 与两阶段提交5.1 更新语句的真实执行链路前面主要讲查询但一条 UPDATE 或 DELETE 的执行流程更复杂因为它涉及数据修改、事务和日志。这里把一条 UPDATE 的完整过程拆开看。假设执行UPDATE user SET name 李四 WHERE id 10;一条 UPDATE 到达执行器后大致经历这些步骤执行器调用存储引擎接口找到id 10这一行。如果这行所在的数据页不在 Buffer Pool就先从磁盘读入内存。InnoDB 在内存中修改这一行的数据把name改成李四。这个修改过程会生成 undo log记录修改前的值用于回滚和 MVCC 快照读。同时写 redo log记录这个数据页上发生了什么修改状态标记为 prepare。真正提交事务时MySQL 写 binlog记录这条变更的逻辑日志。binlog 写完后redo log 状态更新为 commit。后台线程按刷盘时机把脏页Buffer Pool 中修改过的数据页写回磁盘。注意一个关键点修改先发生在 Buffer Pool 内存里不会立即刷盘。即使事务提交了数据页也可能还在内存中要等后台刷脏或未来某次检查点才会落盘。这就是所谓的 WALWrite-Ahead Logging机制日志先落盘数据页后落盘。这么设计的原因很实际每次提交都去刷数据页会产生海量随机写性能完全扛不住而日志是顺序追加写快得多。5.2 为什么需要两阶段提交redo log 是 InnoDB 引擎层的物理日志记录在哪个数据页、哪个偏移位置、做了什么修改binlog 是 Server 层的逻辑日志记录执行了哪个操作、导致哪些行发生了变化。两者服务对象不同redo log 用于崩溃恢复时恢复数据页binlog 用于主从复制和数据恢复。两条日志独立存在就会有一个问题如果它们的写入时机不一致崩溃后可能出现一边有一边没有的情况。设想一下如果先写 redo log 后写 binlogredo log 写完后、binlog 写之前系统崩溃。恢复后主库根据 redo log 能看到这条事务已提交但从库因为没有收到 binlog那行数据还是旧值主从不一致。如果先写 binlog 后写 redo logbinlog 写完、redo log 没写就崩溃恢复后从库应用了这条变更主库却因为 redo log 里没有对应记录而回滚了又不一致。两阶段提交two-phase commit就是为了同步这两条日志的写入状态。事务提交过程变成写入 redo log状态 prepare。写入 binlog。写入 redo log状态 commit。崩溃恢复时如果 redo log 状态是 commit事务一定已提交直接应用。如果 redo log 状态是 prepare 且 binlog 已写完事务也提交保证主从一致。如果 redo log 状态是 prepare 且 binlog 没写完事务回滚。这样任何时刻崩溃两条日志都不可能处于互相矛盾的状态。理解这个过程就能明白为什么提交事务时多写一次日志状态是必须的而不是性能浪费。5.3 日志刷盘策略与主从一致性线上调优常涉及innodb_flush_log_at_trx_commit和sync_binlog两个参数。innodb_flush_log_at_trx_commit控制 redo log 的刷盘策略值为 1每次事务提交都把 redo log 写入磁盘。值为 2每次提交先把 redo log 写入操作系统缓存由系统后台统一刷盘。如果数据库进程崩溃数据不丢如果整台机器断电可能丢最近 1 秒日志。值为 0每秒才刷一次 redo log数据库崩溃可能丢最近 1 秒日志。sync_binlog控制 binlog 刷盘值为 1每次提交都刷 binlog。值为 0由系统决定刷盘时机。最安全的组合是innodb_flush_log_at_trx_commit1、sync_binlog1每次提交都有两份日志落盘。代价是每次提交多了两次 fsync写入吞吐会下降。追求高性能但对数据安全要求稍低时很多团队会把innodb_flush_log_at_trx_commit调成 2因为单机意外断电的概率远低于数据库进程崩溃的概率。还有个实际体验主从延迟未必是网络造成的更多时候是从库在重放 binlog 时写入了大量数据。如果一条 UPDATE 在 binlog 里以行格式记录更新一万行就会记录一万条行变更事件从库要逐条重放延迟自然大。这也是为什么大事务要拆批执行不只是锁的问题日志重放也是实打实的成本。6. 从执行流程反推慢 SQL定位与优化实战6.1 先判断 SQL 卡在哪个环节理解了执行链路排慢 SQL 时就不会两眼一抹黑。一条 SQL 的总耗时可以粗略拆成几段连接建立耗时网络往返 握手认证。解析耗时纯 CPU 工作一般都很短。优化耗时通常也很短。执行耗时与执行计划、索引选择、扫描行数、回表次数强相关。返回结果耗时与结果集大小和网络带宽相关。大多数慢 SQL 都卡在执行阶段说明工作积压在存储引擎和 Server 层之间的循环里。如果看到Sending data状态持续很久基本跑不掉。定位手段主要有几个慢查询日志打开slow_query_log设置long_query_timeMySQL 会把超过阈值的 SQL 记录下来。用mysqldumpslow按耗时排序找出 TOP 慢 SQL。performance_schemaMySQL 5.7 之后逐步完善events_statements_summary_by_digest按 SQL 模板聚合统计总耗时、平均耗时、扫描行数。比慢日志更适合做全局统计。EXPLAIN对具体 SQL 看执行计划重点看type、key、rows、Extra。show profile或performance_schema的events_stages_history_long看一条 SQL 在哪个阶段耗时高。虽然后台系统里不一定能直接用但单机排查很管用。6.2 一次典型的慢查询排查过程分享一下之前排查过的一个案例。某报表查询在压测时执行时间越来越高从 100ms 涨到 1800ms而且越往后越慢。第一步开启慢查询日志抓到耗时最高的 SQL。这是一条对订单表按状态和时间范围查询的分页语句SELECT * FROM orders WHERE status 1 AND created_at 2024-06-01 00:00:00 ORDER BY id DESC LIMIT 20;第二步用EXPLAIN看执行计划发现 type 是ALL也就是全表扫描预估扫描行数已经到百万级。Extra里还有Using filesort。第三步看表结构。status和created_at上分别有单列索引但查询条件用 AND 连接优化器只能选其中一个而且排序字段id没有和 where 条件组成联合索引所以排序还要额外做。第四步调整索引。把索引改成idx_status_created_at (status, created_at)同时把排序字段也考虑进去改成idx_status_created_at_id (status, created_at, id)。这样 where 条件能走索引定位并且索引本身已经按 id 排好序Order by id不需要再额外排序。第五步压测验证耗时从 1800ms 降到 50ms 左右。这个案例值得复盘的是为什么之前没有慢。因为测试环境数据量只有几万行全表扫描和索引扫描区别不大线上数据到了百万级全表扫描的成本就藏不住了。数据规模是执行计划是否合理的最大变量压测前必须把数据量预估到位。6.3 从执行流程视角审视常用优化手段按对执行流程的影响优化手段可以分成几个层次。减少扫描行数走唯一索引、等值匹配、范围区间尽量窄这是收益最大的改动。减少回表次数设计覆盖索引让查询列直接落在索引里。减少排序和临时表让 ORDER BY、GROUP BY 字段和 where 条件共用联合索引避免Using filesort和Using temporary。减少返回数据量只查需要的列、使用合适的 LIMIT。返回一万行和返回一百万行网络开销完全不同。大部分慢 SQL 的根因可以归结为实际扫描行数远超预期。只要通过执行计划认识到这个问题优化方向就明确了。6.4 长期维护统计信息与执行计划优化器依赖统计信息做判断统计信息不准执行计划就可能选择错误路径。生产环境需要建一个习惯高变更率表定期ANALYZE TABLE或者在数据量发生大变化后手动触发一次。另外执行计划不是永远不变的。同一个 SQL 模板随着数据增长、索引变更、统计信息更新执行计划会漂移。所以监控慢查询不能只看 SQL 文本要看执行计划变化趋势。MySQL 8.0 提供了EXPLAIN ANALYZE可以直接输出执行每个节点花费的时间对定位执行计划中的瓶颈节点非常直观。最后再分享一个个人观点不要迷信优化技巧先理解执行链路。很多优化手段比如覆盖索引、ICP、最左前缀、拆批更新本质上都是针对执行器或存储引擎的工作方式做适配。把这条链路想明白了拿到任何一条慢 SQL你都能自己推导出问题出在哪而不是靠一套背下来的规则去套。这种能力才是处理线上性能问题真正值钱的地方。