别瞎加索引用Explain揪出慢SQL根因做后端开发这么多年我见过太多人遇到SQL慢的问题第一反应就是“给字段加个索引”结果索引建了十几个查询速度没提上来写入性能先崩了。去年我接手了一个短视频平台的遗留项目一张800万数据的用户互动表随便拉一条评论列表的SQL都要跑4秒多开发团队前前后后建了21个索引不仅没解决问题高峰期插入评论的接口还频繁超时。后来我没新增任何索引只用Explain逐条分析执行计划调整了3条SQL的写法所有慢查询的耗时直接降到了20毫秒以内。很多人把Explain当成一个“点一下看看”的工具却不知道它每一列的输出都藏着数据库优化器的决策逻辑把这些逻辑摸透你根本不用靠盲目加索引来解决性能问题。今天我就把从几十次线上慢查询排查里攒下的Explain实战经验全部讲透让你拿到任何一条慢SQL都能通过Explain快速定位根因。一、Explain核心字段的底层含义很多人用Explain只会看type字段觉得只要不是ALL就万事大吉其实Explain的输出里有十几个关键字段每一个都对应着数据库执行这条SQL的真实路径漏掉任何一个都可能错过隐藏的性能问题。我之前排查过一条SQLtype显示是range看起来已经走了索引结果实际执行耗时还是超过了2秒仔细看Extra字段才发现里面有Using MRR数据库做了大量随机IO才导致查询变慢。1、 id字段是SQL执行顺序的标识它代表了查询中操作的优先级。id值越大越先被执行如果id值相同执行顺序就从上到下。当SQL里包含子查询或者关联查询时id会出现不同的数值通过id你能直接看清数据库先扫描哪张表后扫描哪张表。很多人写嵌套子查询的时候以为外层会先执行结果Explain一看子查询的id更大数据库先把子查询的全表数据扫了一遍才关联外层表直接导致全表扫描。2、 select_type字段用来标记当前查询的类型常见的有SIMPLE、PRIMARY、SUBQUERY、DERIVED等。SIMPLE代表最基础的单表查询没有任何子查询或者关联操作PRIMARY代表最外层的主查询SUBQUERY代表SELECT里的子查询DERIVED代表FROM子句里的衍生表。很多人写SQL的时候喜欢在FROM里套一层子查询生成临时表却不知道当衍生表的数据量超过1万行之后数据库会在磁盘上创建临时表查询性能会直接暴跌。3、 type字段是整个执行计划里最核心的指标它代表了数据库找到目标行的访问方式性能从差到好依次是ALL、index、range、ref、eq_ref、const、system。ALL就是我们常说的全表扫描数据库从头到尾扫完整张表的所有数据性能最差index是扫描整个索引树虽然走了索引但还是要遍历所有索引节点性能比全表扫描稍好range是索引范围扫描常见于带、、IN、BETWEEN的查询是生产环境里最常用的访问级别ref是非唯一索引的等值查询能快速定位到多个符合条件的行eq_ref是关联查询时使用主键或者唯一索引每次关联只能匹配到一行数据const是把条件作为常量直接优化比如通过主键查询单行数据数据库直接就能拿到结果不需要扫描任何数据行。4、 key字段显示的是执行这条SQL时数据库最终实际选择使用的索引。很多人以为自己建了索引SQL就一定会走结果Explain一看key字段是空的说明数据库根本没选中你建的索引还是走了全表扫描。这时候你就要排查是不是SQL里有函数运算、隐式类型转换导致索引失效或者当前数据分布下优化器认为走索引的代价比全表扫描更高主动放弃了索引。5、 rows字段是数据库预估要扫描的数据行数这个数值是优化器根据统计信息估算出来的不是真实的扫描行数但它能直接反映出查询的代价。比如一张100万的表预估扫描10万行说明这个查询的代价非常高哪怕走了索引性能也不会太好。很多时候你看SQL走了索引但rows字段显示要扫几十万行这时候哪怕索引生效查询速度也快不起来。6、 Extra字段是最容易被忽略但信息量最大的字段它会显示执行过程中的额外细节。Using filesort代表数据库无法利用索引完成排序需要在内存或者磁盘上进行额外的排序操作数据量大的时候性能会非常差Using temporary代表数据库创建了临时表来处理排序、分组或者去重操作这是非常危险的信号大表场景下很容易导致数据库CPU打满Using index代表使用了覆盖索引不需要回表就能拿到所有需要的数据这是性能最优的状态Using where代表拿到索引数据之后还要在服务层进行过滤操作说明索引没有完全覆盖查询条件。二、Explain对比的实战案例光讲理论很难有直观感受我拿短视频平台的评论表作为示例表名是video_comment核心字段有comment_id主键、video_id视频ID、user_id用户ID、content评论内容、like_count点赞数、create_time评论时间表里现在有820万条数据。我通过不同SQL写法的Explain结果对比让你直观看到不同写法带来的性能差异。首先我们先给这张表创建两个基础索引一个是针对视频评论的联合索引idx_vid_ctime(video_id, create_time desc)另一个是针对用户评论的联合索引idx_uid_ctime(user_id, create_time desc)。第一个对比案例是分页查询的两种写法第一种是传统的limit offset写法SQL语句如下sqlSELECT * FROM video_commentWHERE video_id 10086ORDER BY create_time DESCLIMIT 100000, 20;我们用Explain分析这条SQL的执行计划输出结果整理成表格如下表格id select_type table type possible_keys key rows Extra1 SIMPLE video_comment range idx_vid_ctime idx_vid_ctime 100020 Using where从执行计划里能看到这条SQL虽然走了idx_vid_ctime索引但是预估要扫描100020行数据数据库要先把前10万条数据全部从索引里读出来直接丢弃最后返回第100001到100020的20条数据。实际线上执行这条SQL耗时超过2.8秒完全无法满足用户的浏览需求。我们把这条SQL改成分页优化的写法通过子查询先拿到目标页的起始comment_id再关联查询完整数据优化后的SQL如下sqlSELECT a.* FROM video_comment aINNER JOIN (SELECT comment_id FROM video_commentWHERE video_id 10086ORDER BY create_time DESCLIMIT 100000, 20) b ON a.comment_id b.comment_id;我们再用Explain分析这条优化后的SQL执行计划如下表格id select_type table type possible_keys key rows Extra1 PRIMARY ALL NULL NULL 20 NULL1 PRIMARY a eq_ref PRIMARY PRIMARY 1 NULL2 DERIVED video_comment index idx_vid_ctime idx_vid_ctime 100020 Using index对比两个执行计划就能发现优化后的子查询部分走了Using index覆盖索引不需要回表直接从索引里就能拿到comment_id扫描10万行数据的耗时从原来的2.8秒降到了0.1秒以内然后通过主键关联查询20条完整数据最终整条SQL的执行耗时不到30毫秒性能提升了近100倍。第二个对比案例是排序逻辑的两种写法第一种写法在查询条件里带了点赞数的过滤同时按点赞数排序SQL语句如下sqlSELECT * FROM video_commentWHERE video_id 10086 AND like_count 100ORDER BY like_count DESCLIMIT 20;Explain分析这条SQL的执行计划如下表格id select_type table type possible_keys key rows Extra1 SIMPLE video_comment range idx_vid_ctime idx_vid_ctime 56000 Using where; Using filesort从Extra字段能看到这里出现了Using filesort数据库拿到56000条符合video_id条件的数据之后要在内存里对like_count进行全量排序耗时超过1.5秒。很多人遇到这个问题第一反应就是给video_id和like_count建联合索引但是这样会新增一个完全冗余的索引占用额外的写入开销。我们调整联合索引的字段顺序把原来的idx_vid_ctime改成idx_vid_like(video_id, like_count desc, create_time desc)然后执行原来的SQL再看Explain的执行计划表格id select_type table type possible_keys key rows Extra1 SIMPLE video_comment range idx_vid_like idx_vid_like 120 Using index condition调整索引之后Extra里的Using filesort直接消失了数据库直接利用索引的有序性就能按like_count倒序拿到前20条符合条件的数据预估扫描行数从56000降到了120实际执行耗时从1.5秒降到了10毫秒以内而且没有新增任何索引只是调整了原有索引的字段顺序完全没有增加写入开销。第三个对比案例是IN查询的两种写法第一种写法IN里面带了1000个用户IDSQL语句如下sqlSELECT * FROM video_commentWHERE user_id IN (1001,1002,……,2000)ORDER BY create_time DESC;Explain分析这条SQL的执行计划如下表格id select_type table type possible_keys key rows Extra1 SIMPLE video_comment range idx_uid_ctime idx_uid_ctime 86000 Using where; Using sort_union(idx_uid_ctime)从执行计划能看到数据库用了sort_union的方式合并1000个索引的结果然后还要对所有数据进行排序预估扫描86000行耗时超过1.2秒。我们把这条SQL拆成10条独立的查询每条IN里面只放100个用户ID然后用UNION ALL把结果合并起来优化后的SQL如下sqlSELECT * FROM video_comment WHERE user_id IN (1001,1002,……,1100) ORDER BY create_time DESCUNION ALLSELECT * FROM video_comment WHERE user_id IN (1101,1102,……,1200) ORDER BY create_time DESC……Explain分析优化后的SQL执行计划每一段子查询的type都是rangeExtra里没有了sort_union每一段预估扫描行数不到1万行最终整条SQL的执行耗时降到了80毫秒以内性能提升了15倍。三、Explain排查慢SQL的标准化流程很多人拿到一条慢SQL不知道从哪里下手分析我沉淀了一套标准化的Explain排查流程按步骤走10分钟之内就能定位99%的慢SQL根因。1、 第一步先看type字段如果type是ALL说明出现了全表扫描优先排查WHERE条件里的字段有没有建索引是不是出现了索引失效的场景比如函数运算、隐式类型转换、前导通配符等先把全表扫描的问题解决。2、 第二步看rows字段哪怕type不是ALL也要看预估扫描的行数如果rows超过了总数据量的10%说明这条SQL的代价还是很高哪怕走了索引性能也不会好。这时候要检查索引的区分度是不是太低或者联合索引的字段顺序不对导致扫描了大量不必要的数据。3、 第三步看key字段确认数据库选择的索引是不是你预期的索引如果key字段显示的索引不是你想要的说明优化器选错了索引。这时候可以用FORCE INDEX强制指定索引或者调整SQL的写法改变优化器的代价计算逻辑让它选择正确的索引。4、 第四步看Extra字段优先排查有没有Using filesort和Using temporary这两个高危信号。如果出现Using filesort优先检查能不能通过调整联合索引的字段顺序把排序字段放到索引里利用索引的有序性避免额外排序。如果出现Using temporary优先检查GROUP BY、DISTINCT的字段是不是索引的最左前缀能不能调整字段顺序避免创建临时表。5、 第五步验证真实扫描行数Explain的rows是预估的有时候统计信息不准会和真实扫描行数有很大偏差。这时候可以用SQL_NO_CACHE执行这条SQL查看Slow Log里的实际扫描行数确认和Explain的预估是否一致如果偏差很大执行ANALYZE TABLE更新表的统计信息让优化器拿到准确的数据分布。四、Explain使用的常见误区很多人用了很久Explain还是会踩很多误区我整理了线上最常见的几个坑帮你避开错误的优化方向。1、 不要觉得type达到ref就万事大吉很多时候ref级别索引的选择性很差扫描几十万行数据性能照样很差。我之前遇到过一条SQLtype是ref但是预估扫描30万行执行耗时超过3秒后来调整成联合索引把type升级成ref的同时把扫描行数降到了几百行性能才达标。2、 Explain只能看到一条SQL的执行计划看不到触发器、存储过程里的执行逻辑也看不到多语句事务里的执行细节。很多慢SQL是在事务里执行的单独拿出来Explain看起来没问题放到事务里就会出现锁等待这时候不能只靠Explain分析还要结合事务的执行日志一起排查。3、 Explain不会告诉你锁等待的问题哪怕执行计划非常完美如果这条SQL要等待其他事务释放锁它的执行耗时照样会很长。很多人排查慢SQL的时候只看Explain的执行计划查了半天找不到问题最后才发现是被其他长事务阻塞了白白浪费了很多时间。4、 不要过度依赖优化器的自动优化很多时候优化器会因为统计信息不准选错索引这时候不要盲目相信Explain的结果要结合真实的执行耗时和扫描行数来判断必要的时候可以用Hint来干预优化器的决策让SQL走你预期的执行路径。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围