加索引反而慢3倍，Explain对比复盘根因‌

加索引反而慢3倍Explain对比复盘根因‌去年线上突然出现一起非常反常的性能事故开发同学给一条高频查询SQL加了新索引本想把耗时从200毫秒压到20毫秒结果上线之后这条SQL的平均耗时直接涨到了600毫秒数据库CPU瞬间飙升了30%直接触发了核心接口超时告警。我们紧急回滚了索引之后性能才恢复正常。事后我们拿着两条完全相同的SQL分别在加索引前后用Explain生成执行计划逐行对比花了整整一下午才定位到根因优化器选错了索引原本走全表扫描的高效逻辑反而切换到了一个回表量极高的小索引直接把性能拖垮。这种“加索引反而变慢”的反常识场景几乎每个做数据库优化的开发者都遇到过很多人对着Explain输出的一堆字段一头雾水根本看不懂哪里出了问题。今天这篇文章我就把自己用Explain排查过上千条慢SQL的经验全部分享出来从每个字段的底层含义到真实线上案例的逐行对比帮你彻底搞懂怎么通过Explain看穿SQL的执行逻辑再也不做“瞎加索引”的无用功。数据库工程与Explain实战从执行计划看穿SQL性能本质‌一、90%的慢SQL排查失败都是因为没看懂Explain‌我见过太多开发者排查慢SQL的流程拿到慢查询之后直接对着WHERE条件加索引上线之后发现性能没提升甚至反而变慢最后完全找不到问题出在哪里。本质原因就是他们根本没看懂Explain输出的执行计划完全不知道数据库底层是怎么执行这条SQL的所有优化全靠蒙。我之前接手过一个遗留项目里面有一条用户列表查询SQL开发同学前前后后加了5个索引性能一点起色都没有最后我用Explain一看这条SQL根本没走到任何一个新索引还是在走主键索引的全表扫描所有加的索引完全白做了。1、只看type字段忽略Extra里的隐藏性能坑。很多人排查执行计划只看type是不是ALL觉得只要不是全表扫描就没问题完全忽略Extra里的Using filesort、Using temporary、Using index condition这些标记这些标记背后的性能损耗往往比全表扫描还要严重。我之前遇到过一条SQLtype是ref看起来走了索引但是Extra里出现了Using filesort最后排序操作花了90%的时间整体耗时比全表扫描还高。2、看不懂rows字段的真实含义盲目相信优化器的估算。很多人觉得Explain输出的rows是SQL实际扫描的行数实际上这个值是优化器根据统计信息估算出来的和真实扫描行数差距可能达到几十倍。我之前遇到过一条SQL优化器估算rows是100实际扫描行数超过了100万完全被估算值误导走了错误的索引。3、忽略filtered字段完全没意识到大量数据被过滤。很多人根本没注意到filtered字段这个字段代表经过索引条件过滤之后剩余的数据比例如果filtered的值只有10%意味着索引扫描出来的100行数据里有90行后续会被过滤掉大量的回表操作完全是无用功性能自然好不了。4、看不懂不同关联类型的差异多表关联场景完全乱优化。很多人面对多表关联的执行计划分不清SIMPLE、PRIMARY、SUBQUERY、DERIVED这些类型的区别根本找不到关联逻辑里的性能瓶颈最后优化完全找不到方向。那次“加索引反而变慢3倍”的事故之后我们给团队做了完整的Explain实战培训要求所有慢SQL优化之前必须先输出执行计划逐字段分析确认逻辑没问题之后才能上线后续半年里再也没有出现过类似的盲目加索引导致的性能事故。这也让我深刻意识到Explain不是一个用来走流程的工具而是你看穿SQL底层执行逻辑的眼睛看不懂它所有优化都是在瞎猜。二、Explain核心字段逐行拆解从底层逻辑理解每一个标记‌很多教程只会罗列Explain字段的含义根本不会讲每个字段背后对应的性能风险我把日常排查里最核心的字段全部拆解清楚结合真实场景告诉你每个标记代表的实际意义以及对应的优化方向。1、id字段。这个字段代表执行计划里每一行的执行顺序id越大越先执行id相同的情况下从上到下执行。如果出现id为NULL的行代表这一行是衍生出来的临时表一般是子查询或者UNION操作生成的出现这种情况你就要警惕很可能生成了超大临时表带来极高的内存和IO开销。2、select_type字段。这个字段代表当前行的查询类型SIMPLE代表普通的单表查询没有子查询和关联PRIMARY代表最外层的主查询SUBQUERY代表不依赖外层的独立子查询DEPENDENT SUBQUERY代表依赖外层的相关子查询这个类型是性能灾难的重灾区几乎只要出现就意味着子查询会被外层循环执行很多次必须优先优化DERIVED代表衍生临时表一般是FROM子句里的子查询生成的大表场景下很容易出现性能问题。3、type字段。这个字段代表MySQL找到数据的访问类型是判断SQL性能的核心指标性能从差到好依次是ALL、index、range、ref、eq_ref、const、system。ALL代表全表扫描是性能最差的类型千万级大表里出现ALL基本意味着SQL会跑几秒以上index代表扫描整个索引树比全表扫描好一点但还是要遍历整个索引性能依然很差range代表走索引的范围查询是日常场景里最常用的类型性能基本达标ref代表非唯一索引的等值查询性能很好eq_ref代表关联场景下使用主键或者唯一索引关联性能非常高const代表通过主键或者唯一索引直接匹配到一行数据是性能最优的类型。我日常排查里只要type低于range的SQL基本都会标记为需要优化的慢SQL。4、possible_keys和key字段。possible_keys代表优化器理论上可以选择的索引key代表优化器实际最终选择的索引。这里有一个非常重要的细节如果possible_keys里有很多索引但是最终key选了一个完全不符合预期的索引甚至直接选了NULL走全表扫描就意味着优化器选错了索引这就是我们那次“加索引反而变慢”事故的核心原因。很多人遇到这种场景完全不知道怎么处理要么调整索引的选择性要么用FORCE INDEX强制指定正确的索引就能解决问题。5、rows字段。这个值是优化器根据索引统计信息估算出来的需要扫描的行数注意它不是真实扫描行数只是估算值。如果这个估算值和你预期的差距很大意味着索引的统计信息出现了偏差你可以用ANALYZE TABLE命令更新统计信息让优化器做出正确的判断。千万级大表里统计信息偏差很容易导致优化器选错索引这个细节很多人都忽略了。6、filtered字段。这个字段代表经过当前索引条件过滤之后剩余的数据比例值的范围是0到100。如果filtered的值低于20%意味着索引扫描出来的大量数据后续会被其他条件过滤掉大量的回表操作完全是无用功。这种场景下你可以调整联合索引把后续过滤的字段加到索引里做成覆盖索引就能避免大量无用的回表性能直接提升好几倍。7、Extra字段。这个字段是整个执行计划里信息最多的部分也是隐藏性能坑最多的地方。Using index代表使用了覆盖索引不需要回表是非常好的标记Using where代表在服务器层使用WHERE条件过滤数据意味着索引没有覆盖所有过滤字段产生了回表Using filesort代表使用了文件排序当数据量超过sort buffer大小的时候排序会落到磁盘性能会非常差Using temporary代表生成了临时表一般出现在GROUP BY、DISTINCT场景下大表场景下生成临时表的性能损耗极高Using index condition代表使用了索引下推把部分过滤条件下推到引擎层执行减少回表次数是MySQL 5.6之后的优化特性。我把不同Extra标记对应的性能风险整理成表格方便你快速对照排查表格Extra标记 性能等级 优化方向Using index 优秀 无需优化覆盖索引逻辑正确Using where 一般 补充字段到索引做成覆盖索引Using filesort 较差 把排序字段加到联合索引里利用索引有序性排序Using temporary 很差 调整GROUP BY字段顺序利用索引有序性避免生成临时表Using index condition 良好 无需优化索引下推特性正常生效把这些字段全部理解清楚你拿到任何一条SQL的执行计划都能一眼看穿它的性能瓶颈在哪里再也不会对着一堆输出一头雾水。三、真实线上案例加索引前后Explain逐行对比‌我把那次“加索引反而慢了3倍”的事故完整拆解出来带你逐行对比加索引前后的两条执行计划你就能直观看到优化器选错索引带来的性能差异。这条SQL的业务逻辑是查询某个用户在指定时间范围内的已支付订单返回订单号、支付金额和创建时间。原始的SQL代码如下sqlSELECT order_no, pay_amount, create_timeFROM t_orderWHERE user_id 123456AND create_time 2024-01-01AND pay_status 1;加新索引之前这张表已经存在一个联合索引idx_userid_paytime(user_id, pay_time)这条SQL的执行计划完全正常我把它的核心字段输出整理出来表格字段 执行计划值 含义说明select_type SIMPLE 单表查询没有子查询type range 走索引范围查询possible_keys idx_userid_paytime 优化器可选索引只有这一个key idx_userid_paytime 实际选中这个联合索引rows 120 估算扫描120行数据filtered 100 过滤后剩余100%数据Extra Using index 完全走覆盖索引不需要回表这个执行计划非常完美完全走覆盖索引没有任何回表也没有额外的排序操作整条SQL的平均耗时稳定在200毫秒以内性能完全达标。后来开发同学为了优化这条SQL新增了一个独立索引idx_create_time(create_time)想通过时间字段快速过滤数据结果新增索引之后优化器的选择直接发生了变化生成了完全错误的执行计划我把加索引之后的核心字段输出整理出来表格字段 执行计划值 含义说明select_type SIMPLE 单表查询type range 走索引范围查询possible_keys idx_userid_paytime, idx_create_time 优化器可选索引变成两个key idx_create_time 优化器错误选中了时间索引rows 120000 估算扫描12万行数据filtered 1 过滤后剩余1%数据Extra Using where 需要回表在服务器层过滤数据优化器估算走时间索引只需要扫描12万行数据比走原来的联合索引估算的120行差距不大所以错误选择了时间索引。但实际执行的时候走时间索引需要扫描整整12万行数据每一行都要回表去匹配user_id和pay_status的条件99%的数据都会被过滤掉大量的随机IO直接把性能拖垮最终整条SQL的平均耗时涨到了600毫秒比原来慢了3倍。我们通过Explain逐行对比之后很快定位到了根因直接删掉了新增的错误索引性能立刻恢复正常。后续我们又给这条SQL调整了联合索引把pay_status加到索引里最终整条SQL的耗时直接降到了10毫秒以内比最开始的性能还要好。这个案例非常直观地展示了Explain的价值如果你不对比两条执行计划的差异你根本想不到加索引之后优化器会做出完全错误的选择只会觉得莫名其妙完全找不到性能问题出在哪里。四、Explain排查慢SQL的标准化流程‌我用Explain排查过上千条慢SQL之后总结出了一套标准化流程不管遇到多复杂的SQL按照这个步骤走都能快速定位性能瓶颈不用再瞎蒙乱试。1、第一步先看select_type字段优先干掉DEPENDENT SUBQUERY。只要执行计划里出现了依赖外层的相关子查询第一时间把它改成JOIN这种类型是90%的极端慢SQL的根源优化之后性能往往能直接提升几十倍。我之前遇到过一条SQLselect_type是DEPENDENT SUBQUERY外层循环执行了几十万次子查询改成JOIN之后耗时从几十秒直接降到了几百毫秒。2、第二步检查所有行的type字段把低于range的类型全部优化掉。只要出现ALL全表扫描优先给对应的表加合适的索引出现index全索引扫描调整索引结构避免遍历整个索引树。千万级大表里只要有一行的type是ALL基本就意味着这条SQL的性能不可能达标。3、第三步核对possible_keys和key字段确认优化器没有选错索引。如果possible_keys里有合适的索引但是key选了错误的索引甚至直接选了NULL要么更新表的统计信息要么调整索引的选择性必要的时候用FORCE INDEX强制指定正确的索引避免优化器做出错误判断。4、第四步核对rows和filtered字段确认总扫描量在合理范围内。把所有行的rows乘以filtered计算出最终的实际数据处理量如果这个数值超过了1万就意味着SQL的处理量太大需要进一步优化把数据处理量压到1万以内性能基本就能达标。5、第五步逐行检查Extra字段消灭Using temporary和Using filesort。只要出现Using filesort就把排序字段加到联合索引里利用索引的有序性直接完成排序避免文件排序只要出现Using temporary就调整GROUP BY或者DISTINCT的字段顺序让操作可以利用索引的有序性完成避免生成临时表。6、第六步多表关联场景从第一行开始逐行确认驱动顺序。关联场景下执行计划里排在最前面的表就是驱动表确认驱动表是所有表里结果集最小的保证小表驱动大表如果优化器选错了驱动顺序用STRAIGHT_JOIN强制调整避免大表驱动小表带来的性能灾难。我把这套流程落地到团队之后新人排查慢SQL的效率提升了好几倍再也不会出现对着慢SQL瞎加索引的情况基本所有慢SQL都能在10分钟之内定位到根因。五、Explain实战的高阶技巧和避坑指南‌最后分享几个我踩过无数坑总结出来的高阶技巧帮你避开那些容易被忽略的Explain使用陷阱。1、Explain不会执行SQL估算值和真实值可能差距极大。Explain只是输出执行计划不会真正执行SQL所以rows是估算值和真实扫描行数可能差几十倍。你可以用EXPLAIN ANALYZE命令MySQL 8.0支持让SQL真正执行之后输出真实的扫描行数比普通Explain的参考价值高很多。2、不要盲目相信优化器的选择很多时候它会犯低级错误。数据分布极端不均匀的时候比如某个user_id的订单数据占了全表的80%优化器很容易选错索引这种场景下不要犹豫直接用FORCE INDEX强制指定正确的索引就能解决问题。3、不要用Explain分析INSERT语句它看不到写入的性能瓶颈。Explain只能分析SELECT语句对于INSERT、UPDATE、DELETE这类写入语句你可以把它改写成对应的SELECT语句用Explain分析WHERE条件的索引命中情况就能定位写入语句的性能问题。4、执行计划会随着数据分布动态变化不要一劳永逸。同一条SQL在数据量小的时候执行计划很完美数据量涨到千万级之后优化器可能就会切换到完全错误的执行计划。你需要定期巡检慢SQL的执行计划及时发现优化器的错误选择避免线上出现性能事故。5、不要为了让执行计划好看盲目调整索引。很多人为了让type达到ref甚至eq_ref盲目加很多无用索引最后索引数量爆炸拖垮写入性能。你要平衡索引的维护成本和查询性能不要为了执行计划的标记好看做完全没必要的优化。Explain从来不是一个用来应付流程的工具它是你和数据库之间对话的窗口你通过它能看到数据库底层是怎么执行你的SQL的。很多开发者花了大量时间去背SQL优化的口诀却从来没有认真去逐行分析过一条执行计划最后优化全靠运气。真正的SQL优化高手从来不会瞎蒙乱试他们只需要看一眼Explain的输出就能立刻定位到性能瓶颈用最低的成本拿到最高的性能收益。这也是我做数据库优化这么多年最深刻的体会看懂执行计划才是SQL优化的真正入门。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围