‌数据库工程与Explain执行计划深度对比‌做了七年的后端开发和数据库优化工作我见过太多开发者拿到慢SQL之后就盲目加索引最后索引加了十几个慢查询的问题还是没解决甚至还把写入性能拖垮了。我印象最深的一次线上故障是一个刚入职半年的开发为了优化一条订单统计慢SQL连续给相关字段加了五个联合索引结果当天晚上订单提交的写入性能直接下降了60%大促高峰期有近一万笔订单提交超时团队排查了三个多小时才找到问题根源。事后复盘的时候发现那条慢SQL的问题根本不是缺索引而是SQL逻辑里写了一个隐式类型转换导致已经建好的索引完全失效哪怕加再多新索引也没用。很多开发者对Explain的认知只停留在看type字段的层面根本不知道不同执行计划之间的性能差距到底有多大也不会通过多组Explain结果的对比精准定位慢SQL的根因。今天我就把自己在多个亿级流量业务线沉淀下来的Explain深度对比实战经验完整拆解从字段含义解读、不同场景的执行计划对比到根因定位和优化落地带你彻底掌握这个SQL优化的核心工具让你拿到任何一条慢SQL都能通过Explain对比快速找到最优的优化方案。一、Explain核心字段的底层含义解读很多开发者用了很多年Explain对各个字段的理解还停留在表面只知道type字段要尽量到ref级别不知道每个字段背后对应的底层执行逻辑这样根本没法通过执行计划的细节差异定位到真正的性能瓶颈。想要用好Explain对比首先要把每个核心字段的底层含义彻底搞透知道不同字段取值对应的数据库内部执行动作这样才能从细微的差异里发现隐藏的性能问题。1、id字段这个字段代表了执行计划中每一行SQL的执行顺序它的取值规则很多人都搞不清楚。当所有行的id值都相同时执行顺序是从上到下依次执行当id值不同时id值越大的行优先级越高越先被执行如果出现id为NULL的行代表这一行是衍生表或者临时表最后才会被执行。很多人写了带子查询的SQL之后发现执行顺序完全和自己预想的不一样就是因为没搞懂id字段的规则比如写了一个相关子查询内层子查询的id值比外层大会先执行子查询最后导致整个SQL的执行次数被放大几十倍。2、select_type字段这个字段代表了当前行对应的SQL的查询类型不同的查询类型对应的执行开销天差地别。最常见的SIMPLE类型代表简单查询没有子查询和UNION执行开销最小PRIMARY类型代表最外层的主查询SUBQUERY类型代表不相关的内层子查询只会被执行一次DEPENDENT SUBQUERY类型代表相关子查询内层子查询会被外层的每一行驱动执行一次这种查询类型的性能特别差数据量大的时候很容易变成慢查询DERIVED类型代表衍生表也就是子查询生成的临时表这种类型会产生额外的临时表IO开销性能也会差很多。我之前遇到过一条慢SQL就是用了DEPENDENT SUBQUERY类型的相关子查询外层有十万行数据子查询就被执行了十万次最后执行时间超过了10秒。3、type字段这个字段代表了MySQL在表中找到目标行的访问方式是判断SQL性能最核心的字段。它的性能从最好到最差依次是system、const、eq_ref、ref、range、index、ALL。system和const是最优的访问类型代表通过主键或者唯一索引一次就能定位到一行数据性能几乎可以忽略不计eq_ref类型出现在多表关联的时候关联字段是主键或者唯一非空索引每次关联只能匹配到一行数据性能也非常高ref类型代表通过普通二级索引的等值查询可以匹配到多行数据是日常业务中最常见的高性能访问类型range类型代表索引的范围扫描比如between、大于小于、in查询这种访问类型的性能比ref稍差但还是属于可以接受的范围index类型代表扫描整个索引树虽然不需要回表但是要遍历整个二级索引性能已经比较差了ALL类型就是全表扫描遍历整个聚簇索引是性能最差的访问类型线上业务里要绝对避免出现。4、key字段这个字段代表MySQL最终实际选择使用的索引很多开发者以为自己建了索引SQL就一定会用到结果看Explain的key字段发现是NULL根本没走任何索引这就是很多人优化SQL踩坑的地方。MySQL的优化器是根据统计信息来选择索引的当统计信息不准的时候优化器很可能会选错索引明明有更合适的索引最后却选了一个性能很差的索引甚至直接走全表扫描。我之前遇到过一条SQL明明有user_id的联合索引但是优化器最后选择了create_time的索引导致SQL的执行时间从几十毫秒变成了几秒最后只能通过force index强制指定正确的索引。5、rows字段这个字段代表MySQL预估需要扫描的行数这个数值是基于索引统计信息估算出来的不是精准的实际扫描行数但是它的参考价值非常高。预估扫描的行数和最终实际返回的行数的比值是判断索引好坏的核心指标这个比值越小代表索引的过滤效果越好。如果预估扫描100万行最后只返回100行数据说明这个索引的选择率特别差哪怕走了索引性能也不会好这种场景下优化器很可能会直接放弃索引走全表扫描。6、Extra字段这个字段包含了很多执行计划的额外信息很多隐藏的性能问题都会在这个字段里体现出来。最常见的Using index代表覆盖索引不需要回表就能拿到所有需要的数据是非常好的状态Using where代表在存储引擎返回数据之后在server层做过滤这种状态说明索引的过滤效果不够好Using filesort代表文件排序当排序的字段没有在索引里的时候MySQL需要把所有符合条件的数据读取出来在内存或者磁盘里做排序性能开销特别大Using temporary代表使用了临时表通常出现在group by或者多表关联的场景里创建临时表的开销非常高数据量大的时候性能会特别差。很多慢SQL的问题在其他字段里都看不出异常最后都是在Extra字段里发现了Using filesort或者Using temporary找到了真正的性能瓶颈。为了让大家更清晰地对比不同type访问类型的性能差异我整理了一张基于1000万行测试数据的性能对比表表格type访问类型 预估扫描行数 平均执行耗时 推荐使用场景 线上风险等级const 1行 1ms 主键/唯一键等值查询 无风险eq_ref 1行/关联行 5ms 多表关联主键匹配 无风险ref 10~1000行 5~50ms 普通二级索引等值查询 低风险range 100~10000行 50~200ms 索引范围扫描查询 中风险index 1000万行 1~3s 全索引遍历统计 高风险ALL 1000万行 3~10s 全表扫描 极高风险二、典型场景的Explain执行计划深度对比想要真正掌握Explain的用法不能只停留在理论层面必须通过大量不同场景的执行计划对比才能直观感受到不同写法、不同索引下的性能差异知道什么样的执行计划是好的什么样的执行计划是有问题的。我挑选了四个线上最常见的SQL优化场景完整展示优化前后的Explain执行计划对比帮你建立直观的性能感知。第一个场景是隐式类型转换导致索引失效的对比。很多开发者写SQL的时候索引字段是varchar类型但是查询条件里传入的是数字类型MySQL会自动做隐式类型转换导致索引完全失效。我们的测试表order_info里order_no字段是varchar(32)类型并且已经建立了唯一索引idx_order_no。错误写法的SQL代码如下sqlSELECT * FROM order_info WHERE order_no 202607130001;查看这条SQL的Explain执行计划type字段是ALLkey字段是NULLExtra字段显示Using where代表这条SQL完全走了全表扫描预估扫描行数是1000万行执行耗时超过了5秒。正确写法的SQL代码如下sqlSELECT * FROM order_info WHERE order_no 202607130001;查看这条SQL的Explain执行计划type字段是constkey字段是idx_order_no预估扫描行数是1行执行耗时不到1毫秒。通过这两个执行计划的对比可以发现只是给查询条件的字符串加了一对单引号整个SQL的性能提升了几千倍很多开发者遇到这种场景根本不会想到是隐式类型转换的问题盲目去加新的索引完全是做无用功通过Explain的对比就能一眼发现问题所在。第二个场景是相关子查询和JOIN关联的执行计划对比。很多开发者习惯写IN子查询当子查询是相关子查询的时候性能会变得特别差。我们要查询用户ID为10086的所有已支付订单最初的写法用了IN相关子查询。相关子查询的SQL代码如下sqlSELECT * FROM order_info oWHERE o.order_id IN (SELECT p.order_id FROM pay_info pWHERE p.pay_status 1 AND p.user_id o.user_id);查看这条SQL的Explain执行计划子查询的select_type是DEPENDENT SUBQUERY代表子查询会被外层的每一行驱动执行一次外层预估扫描10万行数据子查询就要被执行10万次整个SQL的预估总扫描行数超过了10亿行执行时间超过了10秒。改写成JOIN关联的SQL代码如下sqlSELECT o.* FROM order_info oINNER JOIN pay_info p ON o.order_id p.order_idWHERE o.user_id 10086 AND p.pay_status 1;查看这条SQL的Explain执行计划两行的select_type都是SIMPLE第一行访问pay_info表的type是refkey字段用了idx_user_pay索引预估扫描行数是100行第二行关联order_info表的type是eq_ref用了主键索引预估扫描行数是1行整个SQL的总扫描行数只有100行执行耗时不到20毫秒。通过这两个执行计划的对比可以发现只是把相关子查询改写成了JOIN关联整个SQL的扫描行数从10亿行降到了100行性能提升了上万倍很多慢SQL的根因就是这种相关子查询通过Explain的select_type字段一眼就能识别出来。第三个场景是普通索引和覆盖索引的执行计划对比。我们要查询用户ID为10086的所有订单的order_id和create_time最初的索引是user_id的单列普通索引。用普通单列索引的SQL代码如下sqlSELECT order_id, create_time FROM order_info WHERE user_id 10086;查看这条SQL的Explain执行计划type是refkey字段用了idx_user_id索引但是Extra字段没有显示Using index代表需要通过二级索引的user_id回表到聚簇索引里读取order_id和create_time字段预估扫描行数是200行执行耗时大概是50毫秒。把索引改成user_id、order_id、create_time的联合覆盖索引之后同样的SQL查看Explain执行计划type还是refkey字段用了新的idx_user_cover索引Extra字段显示Using index代表不需要回表直接从二级索引里就能拿到所有需要的数据预估扫描行数还是200行但是执行耗时降到了不到5毫秒。通过这两个执行计划的对比可以发现同样是走ref类型的索引有没有Using index的覆盖索引状态性能差距可以达到10倍以上很多开发者优化SQL的时候只关注有没有走索引忽略了Extra字段里的Using index导致性能始终达不到最优。第四个场景是排序场景的执行计划对比。我们要查询用户ID为10086的所有订单按create_time倒序排列最初的索引是user_id的单列索引。用普通单列索引排序的SQL代码如下sqlSELECT * FROM order_info WHERE user_id 10086 ORDER BY create_time DESC;查看这条SQL的Explain执行计划type是refkey字段用了idx_user_id索引Extra字段显示Using filesort代表拿到所有符合条件的订单数据之后还要做一次文件排序预估扫描行数是200行执行耗时超过了200毫秒。把索引改成user_id、create_time的联合索引之后同样的SQL查看Explain执行计划type是refkey字段用了idx_user_ctime索引Extra字段里没有了Using filesort代表排序直接利用索引的有序性完成不需要额外的排序操作执行耗时降到了不到10毫秒。通过这两个执行计划的对比可以发现只是把排序字段加到了联合索引里就消除了Using filesort性能直接提升了20倍很多开发者遇到带排序的慢SQL不知道怎么优化通过Explain的Extra字段发现Using filesort之后就知道要把排序字段加到联合索引的最后面轻松解决排序性能问题。我整理了这四个场景优化前后的核心执行计划指标对比方便大家直观参考表格优化场景 优化前核心执行计划特征 优化后核心执行计划特征 耗时提升倍数隐式类型转换 typeALLkeyNULL全表扫描 typeconstkey唯一索引 5000倍相关子查询 select_typeDEPENDENT SUBQUERY select_typeSIMPLE两次关联 50000倍覆盖索引优化 无Using index需要回表 ExtraUsing index无需回表 10倍排序优化 ExtraUsing filesort 无文件排序利用索引有序性 20倍三、Explain深度对比的优化落地流程掌握了不同场景的执行计划对比之后我们可以总结出一套标准化的Explain优化落地流程不管遇到什么样的慢SQL都可以按照这个流程一步步对比排查快速定位根因完成优化。1、拿到慢SQL之后首先给它加Explain关键字拿到第一版原始的执行计划记录下所有核心字段的取值作为优化前的基准版本。很多开发者优化SQL的时候根本不记录原始的执行计划优化完之后不知道到底有没有提升很容易出现越优化性能越差的情况。2、 从id字段开始逐层排查检查有没有DEPENDENT SUBQUERY、DERIVED这类性能很差的查询类型如果有的话优先把相关子查询改写成JOIN关联把衍生表的逻辑拆分成分步查询先把最影响性能的查询类型问题解决掉。3、检查type字段把所有ALL类型的全表扫描全部找出来逐个分析为什么没有走索引是没有建对应的索引还是出现了隐式类型转换、索引字段上用了函数运算这类索引失效的情况先把全表扫描的问题全部解决把type字段至少提升到range及以上的级别。4、检查key和rows字段确认MySQL选择的索引是不是我们预期的最优索引预估扫描行数和实际返回行数的比值是不是在合理范围内如果优化器选错了索引就通过调整索引的统计信息或者用force index强制指定正确的索引保证索引的选择率足够高。5、 最后重点检查Extra字段把所有Using filesort、Using temporary的场景全部找出来通过调整联合索引的字段顺序把排序和分组的字段加到联合索引里消除文件排序和临时表最后尽量让查询达到Using index的覆盖索引状态实现性能的最优。6、每做一次优化调整就重新执行一次Explain拿到新的执行计划和优化前的基准版本做对比确认每一次调整都让执行计划变得更好性能得到提升避免出现调整之后执行计划反而变差的情况。优化完成之后还要在测试环境用线上的脱敏数据做压测验证实际的执行耗时确实达到了预期的性能目标再上线到生产环境。我在团队里推行这套标准化的Explain优化流程之后团队里的开发者优化慢SQL的效率提升了三倍以上之前很多要花几个小时才能定位的慢SQL问题现在十几分钟就能通过执行计划对比找到根因线上慢查询的数量直接下降了70%。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围