Explain实战：从看懂执行计划到根治慢SQL

Explain实战从看懂执行计划到根治慢SQL做数据库运维和后端开发的人几乎都经历过那种线上紧急排障的惊魂时刻凌晨两点的告警电话突然炸响线上业务的响应时间从平时的几十毫秒直接跳到十几秒DBA登上服务器一看数据库CPU使用率已经冲到100%慢查询日志里密密麻麻躺着几十条执行了几百秒的SQL。很多人第一反应是先急着加索引、改配置折腾几个小时问题还是反复出现根本找不到性能瓶颈的根源。其实90%的慢SQL问题根本不需要靠盲猜来解决Explain就是我们打开数据库执行计划黑盒的钥匙只要你能真正读懂Explain输出的每一个字段含义就能精准定位到每一条慢SQL的性能卡点从根源上解决问题。今天我就把在互联网电商平台做了8年数据库优化沉淀下来的Explain实战经验全部拆解开来从字段解读、对比分析到线上真实故障案例帮你彻底掌握用Explain优化SQL的核心能力以后遇到慢查询再也不用靠瞎试碰运气。一、Explain的核心作用与基础原理很多开发者用Explain只看type和rows两个字段觉得只要type不是ALL、rows数值小就是优化到位了结果上线之后还是出现了大量意料之外的慢查询。Explain的本质是模拟MySQL优化器生成执行计划的全过程它不会真正执行你写的SQL语句而是把优化器在后台生成的执行路径完整地展示出来小到索引的选择、扫描的行数大到是否创建临时表、是否进行额外排序所有影响性能的细节都会在输出结果里体现出来。1、我们首先要搞清楚Explain的执行逻辑当你在SQL前面加上Explain关键字之后数据库会先对SQL进行语法解析和语义校验然后查询优化器会根据表的统计信息生成多个候选的执行计划再通过成本模型计算每个计划的磁盘IO开销、CPU运算开销和内存占用最后选出它认为成本最低的方案把这个方案的所有细节通过Explain的结果返回给你。很多时候你写的SQL逻辑看起来很简单但优化器生成的执行计划可能完全偏离你的预期这时候只有通过Explain才能发现问题。2、Explain的输出结果里每一行都对应执行计划里的一个独立操作单元当你的SQL涉及多表关联或者子查询时Explain会返回多行结果每一行代表对一个表的操作逻辑。很多新手看到Explain返回多行结果就觉得无从下手其实我们只需要从第一行开始从上到下依次分析每一个表的访问类型、使用的索引、扫描行数就能完整梳理出整条SQL的执行路径。3、很多人有一个误区觉得Explain返回的结果就是SQL实际执行的路径实际上优化器的成本计算是基于统计信息预估出来的如果表的统计信息和实际数据分布偏差很大优化器很可能选择错误的执行计划这时候Explain展示的计划和SQL实际执行的路径就会出现不一致。我们可以通过执行EXPLAIN ANALYZE命令让SQL真正执行之后返回实际的执行耗时和扫描行数这个结果比普通Explain的预估数据要精准得多是我们排查复杂慢SQL的利器。二、Explain核心字段的深度解读很多人看Explain的输出只关注type字段忽略了其他字段里藏着的大量优化线索我整理了线上优化过程中最常用的几个核心字段的解读方法每一个都对应着具体的优化方向掌握之后你就能从Explain结果里直接定位到慢SQL的所有性能问题。1、id字段是执行计划里操作的执行顺序标识id值越大的操作会越先执行如果id值相同执行顺序就是从上到下依次执行。当SQL里包含子查询时子查询的id值会比外层查询的id值大说明优化器会先执行子查询再执行外层查询。如果Explain结果里出现id为NULL的行说明这一步是优化器生成的衍生表操作通常出现在派生表、临时表的场景里遇到这种情况我们就要重点关注临时表的开销判断是否需要优化SQL逻辑消除临时表。2、select_type字段代表了当前查询的类型常见的类型有SIMPLE、PRIMARY、SUBQUERY、DERIVED等等。SIMPLE代表最基础的简单查询没有子查询和关联操作PRIMARY代表最外层的主查询SUBQUERY代表子查询里的第一个SELECT操作DERIVED代表派生表也就是子查询生成的临时表。如果线上慢SQL的select_type出现了DEPENDENT SUBQUERY说明子查询的执行依赖外层查询的结果子查询会被外层循环执行成千上万次这种场景的性能会非常差我们必须把相关的子查询改写成JOIN关联的形式大幅降低执行次数。3、type字段是访问类型也是判断查询性能最核心的指标性能从差到好依次是ALL、index、range、ref、eq_ref、const、system。ALL就是全表扫描意味着数据库要扫描整张表的所有数据行这种查询在百万级以上的表里几乎一定会成为慢SQL我们线上优化的最低要求就是把所有ALL类型的查询全部优化掉。index代表遍历整个索引树虽然比全表扫描快一点但还是要扫描大量的索引数据性能也很差。range代表走索引的范围查询是线上大表查询的最低合格标准ref代表非唯一索引的等值查询eq_ref代表关联时使用主键或者唯一索引的等值查询const代表查询条件是常量优化器可以在优化阶段就直接算出结果不需要访问表数据性能是最高的。4、possible_keys字段代表优化器在执行过程中认为可能可以用到的索引key字段代表优化器最终实际选择使用的索引。如果possible_keys里有合适的索引但key字段显示为NULL说明优化器判断走索引的开销比全表扫描还大最终放弃了索引选择全表扫描这时候我们就要分析是不是索引的区分度太低或者查询条件的过滤性太差导致索引没有被选中。5、rows字段是优化器预估的需要扫描的行数这个数值越接近SQL实际返回的行数说明索引的效率就越高。如果预估要扫描10万行数据最后实际只返回100行结果说明数据库需要过滤掉99.9%的扫描数据这种查询的性能肯定好不了。我们优化SQL的核心目标之一就是尽可能把rows字段的数值降到最小最好能控制在几百行以内。6、Extra字段是最容易被忽略但信息量最大的字段这里面藏着大量直接的优化提示。如果出现Using filesort说明数据库在拿到数据之后还要进行额外的文件排序操作这种操作在数据量大的时候CPU开销会非常高我们必须通过调整索引结构把排序字段加入联合索引让排序操作直接利用索引的有序性完成彻底消除filesort。如果出现Using temporary说明数据库创建了临时表来处理分组或者去重逻辑临时表的读写开销非常大在大表场景下很容易把数据库的磁盘IO打满遇到这种情况我们必须优先调整SQL逻辑通过索引优化消除临时表的创建。如果出现Using index说明查询用到了覆盖索引不需要回表读取数据这是我们做索引优化最希望看到的状态。三、Explain对比分析的实战案例光讲字段解读很难真正落地我整理了电商平台订单查询场景下的四组Explain对比数据做成了标准对比表格同一个业务逻辑在不同索引策略下的执行计划差异一目了然你可以清晰看到每一步优化带来的性能变化。表格优化阶段 type字段值 key字段使用索引 rows预估行数 Extra字段信息 实际执行耗时无任何索引 ALL NULL 1320000 Using where 3.7s给user_id建单列索引 ref idx_user_id 11500 Using where; Using MRR 0.9s建立(user_id,create_time)联合索引 range idx_user_ctime 2300 Using index condition 0.21s建立(user_id,create_time,order_amount)覆盖索引 range idx_user_ctime_amt 1900 Using index 0.02s从这个表格里我们能直观看到不同优化阶段的性能差距原本接近4秒的慢SQL通过逐步调整索引策略最后优化到了20毫秒以内完全满足线上高并发场景的性能要求。接下来我给大家拆解这个案例的完整优化过程每一步都附上对应的SQL代码和Explain结果分析。最开始这条SQL是用来查询某个用户近一个月的订单总金额当时订单表已经有1300万条数据开发没有给任何字段建索引SQL语句如下sqlSELECT SUM(order_amount) FROM order_infoWHERE user_id 10086 AND create_time 2025-06-01;第一次执行Explain结果显示type是ALLkey字段为NULLrows是132万Extra里只有Using where意味着数据库要扫描整张表的所有数据执行耗时3.7秒高峰期直接把数据库的CPU打满。我们第一次优化给user_id字段建了普通单列索引再次执行Explaintype变成了refkey字段使用了idx_user_idrows降到了11500但是Extra里显示Using where和Using MRR说明数据库需要回表读取所有该用户的订单数据再在服务端过滤时间条件执行耗时0.9秒还是达不到线上200毫秒以内的性能要求。第二次优化我们调整了索引结构创建了(user_id,create_time)的联合索引再次执行Explaintype变成了rangekey字段使用了新的联合索引rows降到了2300Extra里显示Using index condition这时候数据库可以直接在索引里过滤时间条件不需要回表读取所有用户订单性能提升到了0.21秒但还是需要回表读取order_amount字段存在额外的IO开销。最后我们把order_amount字段也加入联合索引做成覆盖索引再次执行ExplainExtra里直接显示Using index不需要任何回表操作rows降到了1900执行耗时直接降到了20毫秒完全满足了线上的性能要求。我们还遇到过一个典型的多表关联慢SQL案例三个表关联查询最开始的执行计划选择了错误的驱动表导致关联操作要扫描几十万行数据执行耗时超过5秒。我们通过Explain分析发现优化器选择了数据量最大的订单表作为驱动表先扫描全表再关联另外两张表开销非常大。我们通过STRAIGHT_JOIN关键字强制指定小表作为驱动表调整之后的执行计划先从用户表查出符合条件的少量数据再用这些数据去关联订单表和商品表整个关联过程的扫描行数从几十万降到了几百行执行耗时直接降到了30毫秒性能提升了100多倍。四、线上复杂慢SQL的Explain排查流程很多人遇到复杂的多表关联慢SQL就不知道怎么下手分析我在团队里沉淀了一套标准化的Explain排查流程按照步骤一步步走哪怕是涉及5、6张表的复杂慢SQL也能在10分钟之内定位到所有性能卡点。1、第一步先看Explain结果的所有行找到type字段是ALL的行这些全表扫描的操作就是性能问题的核心卡点优先给这些表加上合适的索引消除全表扫描这一步就能解决线上80%的慢SQL问题。很多人排查慢SQL的时候先去分析复杂的关联逻辑结果发现只是某一个表没有建索引导致全表扫描把索引加上问题就直接解决了。2、第二步检查所有行的rows字段把所有表的预估扫描行数相乘得到整个关联操作的总扫描行数如果总扫描行数超过100万说明这个SQL的关联逻辑存在严重问题哪怕每一步的type都不是ALL整体的性能也会非常差。我们要调整驱动表的选择让数据量最小的过滤结果作为驱动表尽可能把总扫描行数降到1万以内。3、第三步检查Extra字段找出所有出现Using filesort和Using temporary的行优先优化这些操作。如果出现Using filesort就把排序字段加入到对应的联合索引里利用索引的有序性消除额外排序如果出现Using temporary就调整分组字段的顺序让分组字段和联合索引的最左前缀保持一致消除临时表的创建。4、第四步验证优化之后的执行计划重新执行Explain确认所有优化点都生效type字段都达到range以上的级别没有全表扫描Extra里没有Using filesort和Using temporary再放到压测环境里验证实际执行耗时确认性能达标之后再上线。我之前处理过一个线上的报表慢SQL涉及5张表关联执行耗时超过20秒按照这个流程排查第一步就发现其中一张商品表没有建关联字段的索引执行全表扫描加上索引之后type从ALL变成了eq_ref耗时直接降到了5秒。第二步计算总扫描行数发现还是超过了200万调整驱动表的选择之后总扫描行数降到了2万耗时降到了1秒。第三步发现Extra里有Using temporary调整分组字段的索引之后临时表被消除耗时最后降到了80毫秒整个排查过程只用了不到15分钟就彻底解决了困扰团队很久的报表性能问题。五、Explain使用的常见误区与避坑指南很多开发者用Explain的时候存在很多认知误区导致明明看了执行计划优化之后还是达不到预期的效果我整理了线上优化过程中遇到的几个最常见的坑点帮你避开这些容易踩的陷阱。1、不要迷信优化器的选择很多时候优化器会因为统计信息不准确选择错误的执行计划明明有合适的索引却没有被用到这时候我们不能完全依赖优化器的判断可以通过FORCE INDEX强制指定索引或者通过STRAIGHT_JOIN强制指定关联顺序让SQL按照我们预期的高效路径执行。我之前遇到过一个场景优化器选择了一个区分度很低的索引导致查询耗时超过3秒强制指定正确的联合索引之后耗时直接降到了几十毫秒。2、不要忽略统计信息的影响如果表的统计信息很久没有更新和实际的数据分布偏差很大Explain返回的预估rows数值会和实际扫描行数差几十倍导致我们对执行计划的判断完全出错。我们要定期执行ANALYZE TABLE命令更新表的统计信息保证优化器能拿到准确的数据分布生成正确的执行计划。3、不要用Explain分析正在执行的大事务Explain本身不会锁表但是如果你的SQL是一个正在执行的大更新、大删除语句用Explain分析的时候可能会触发元数据锁导致线上业务的其他查询被阻塞。我们分析这类写SQL的执行计划时最好在测试环境里用相同结构的表做验证不要直接在线上主库的大事务上执行Explain。4、不要只看单条SQL的Explain结果要结合业务的调用量综合判断一条SQL单独执行耗时100毫秒看起来性能还可以但如果每天的调用量超过100万次累计产生的CPU开销会非常大很容易把整个数据库的负载打高。这种场景下我们要继续优化把执行耗时降到10毫秒以内才能支撑高并发的业务场景。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围