接手过一个线上订单系统的慢查询优化一条两千万数据量的统计SQL跑一次要三十多秒。打开Explain一看typeALL、rows3000万、Extra里挂着Using filesort整个查询基本在靠内存硬灌数据。后来优化完单次查询降到了几十毫秒。说白了Explain就是MySQL给开发者的一面镜子你写的SQL是健康还是病态它一眼就能照出来索引则是让SQL跑快的根本手段。这篇文章我打算把Explain的每一列掰开揉碎讲清楚再结合几个实际的索引设计和优化案例帮你彻底掌握这套排查SQL性能问题的方法论。无论你是刚接触数据库优化的新手还是准备面试的后端开发这篇都能给你一些实在的东西。1. Explain输出列逐行拆解每一列都在告诉你什么很多同学看Explain就盯着type和key两列看到typeref、key有值就觉得万事大吉。这是最常见的一个误区。Explain输出一共有12列每一列都从不同角度描述了查询执行的情况合在一起才能还原一条SQL的真实执行路径。我按实际排查时的阅读顺序把每一列都讲一遍。1.1 id与select_type确认SQL的执行顺序id列是查询的序号。这个序号不是从1到N那么简单它的排序规则是id越大越先执行id相同则从上往下执行。在一个多表JOIN的查询里如果id都是1说明MySQL把多张表当成一个整体来优化如果出现了子查询id就会递增数值大的那部分会先执行因为子查询通常是一个独立的执行单元。这里有个容易踩坑的地方派生表DERIVED在MySQL 5.7之前会被物化也就是说它会先被执行并把结果存到临时表里所以你在Explain里会看到一行指向派生表的记录。而MySQL 5.7及之后做了优化很多派生表可以被合并到外层查询中Explain的结果会变得简洁很多。所以如果你在不同版本的MySQL上看到同一个SQL的Explain输出不一致不用慌大概率是优化器版本差异导致的。select_type列标记这条查询的身份。常见的几个值SIMPLE表示没有子查询和UNION的简单查询PRIMARY是最外层查询SUBQUERY是普通子查询DERIVED是派生表UNION是UNION语句中后面的那个SELECT。这些类型本身不代表好坏但能帮你理解SQL的执行结构。我遇到过一个案例一条SQL里有三层嵌套子查询每层都查了同一张大表Explain出来三个SUBQUERY这种SQL哪怕索引全走对了性能也好不到哪去——因为本质上查了三次改造成JOIN之后一次扫描就搞定了。1.2 type访问类型的完整质量阶梯type列是衡量查询效率最直观的指标它表示了MySQL访问一张表的方式。这个字段的值从好到差有一条完整的链条实际开发中最好能优化到ref或range级别最差也不该是ALL。我用一个表来说明这个表是用户订单表有主键id、user_id、order_no、status等字段。下面这张表是我根据实际经验总结的每个类型都配了一个常见的例子。type级别含义典型场景是否建议system表只有一行系统表、临时表极少见const主键或唯一索引等值匹配WHERE id 100最佳eq_ref联表查询时被驱动表通过主键或唯一索引等值匹配JOIN ... ON t1.id t2.user_id最佳ref非唯一索引等值匹配WHERE user_id 100很好range索引范围扫描WHERE id 100 AND id 200不错index索引全扫描遍历整个索引树覆盖索引查询但没有过滤条件一般ALL全表扫描无索引条件或优化器放弃索引必须避免index和ALL虽然都是遍历但index是在索引树上遍历ALL是在聚簇索引的叶子节点上遍历两者IO成本差别很大。有一次我排查一个慢查询typeindexrows显示十几万看起来还行但查询耗时接近一秒原因就是虽然走了索引但扫描了整棵索引树。这种场景通常是因为WHERE条件里没有可用的过滤项或者过滤选择性太差。1.3 key和key_len实际用的索引及其长度possible_keys列列出了查询可能用到的索引但这不代表一定会用。真正决定走哪个索引的是key列它表示优化器最终选择的索引。如果key为NULL说明这条查询没有使用任何索引这是一个强烈的危险信号。key_len列很多人忽略了但它其实非常有用。key_len表示MySQL在索引中实际使用的字节数这个值能告诉你联合索引到底用到了哪几个字段。举个例子假设有个联合索引idx_user_status(user_id, status)其中user_id是BIGINT类型status是TINYINT类型那么key_len 8 1 9字节两者都不允许NULL。如果查询只用了user_idkey_len就是8如果两个条件都用上了key_len就是9。通过这个差值你一眼就能看出联合索引是否被完整使用。计算key_len时有几个细节要注意varchar类型的字段除了存储内容本身还需要额外的2个字节记录长度。如果字段默认允许NULL还需要额外1个字节作为NULL标志位。所以一个varchar(50)的字段如果是utf8mb4字符集且允许NULL它在索引里的长度是50×421203字节。这里utf8mb4一个字符最多占4字节这是很多人在计算key_len时容易算错的地方。1.4 rows和filtered估算成本的重要依据rows列是优化器预估的需要扫描的行数注意是预估不是精确值。这个数值来自统计信息而统计信息由表的数据分布和采样决定。在排查慢查询时我通常会把rows和实际返回行数做对比如果rows远大于实际返回行数说明这个查询走了大量无效扫描索引还有优化空间。filtered列表示经过索引条件过滤后剩余行数占总扫描行数的百分比。比如rows1000filtered10则表示预计有100行满足进一步的条件。这个值主要用在JOIN场景中驱动表的结果集越小被驱动表的扫描次数就越少。有个我在实际中反复验证的经验当一个查询的rows从几万级降到几十级时查询耗时会呈现断崖式下降。优化工作本质上就是在和rows斗智斗勇——通过合理的索引设计让扫描行数尽量接近实际结果行数让数据按图索骥而不是大海捞针。1.5 Extra一句话点破查询的隐藏问题Extra列是Explain输出里信息密度最高的一列它包含了MySQL执行查询时的额外行为描述。我把最常见的几个Extra值整理一下。Using index说明查询使用了覆盖索引所有需要返回的列都在索引树里不需要回表查聚簇索引这是最优状态。Using where表示MySQL在存储引擎层拿到数据后还需要在server层做进一步过滤。这个不能算坏但有时候它暗示着索引条件下推没有生效或者部分过滤条件没有被索引利用。Using filesort就是文件排序意味着MySQL需要用额外的排序操作通常发生在ORDER BY字段没有走索引时这是优化重点。Using temporary说明使用了临时表常见于GROUP BY和DISTINCT操作当数据量很大时临时表会落到磁盘性能极差。还有一个容易忽略的值Using index condition这是索引下推ICP的标志后面我会专门讲。当你看到这个标记时说明MySQL在索引遍历过程中就已经把一部分WHERE条件过滤掉了减少了回表次数这是一种优化效果。看到它别慌它是个加分项。2. 索引实践从慢查询定位到索引设计Explain只是诊断工具真正的核心功夫在于知道怎么设计索引。这一章我按照实际工作的流程来写先教你怎么定位慢SQL再讲联合索引的字段顺序怎么排最后分享前缀索引和覆盖索引的使用心得。2.1 用Explain定位慢SQL的完整流程我在公司里排查慢SQL时有一套固定的流程这里分享给你。第一步通过慢查询日志和performance_schema找到耗时最高的SQL。第二步在测试环境复现这条SQL加上EXPLAIN关键字查看执行计划。第三步对照上一章讲的那几个关键列逐一检查type是否为ALL或index、rows是否过大、Extra是否包含Using filesort或Using temporary。只要这三个里有一个中招这条SQL就基本可以判定为需要优化。定位到问题SQL之后我习惯先做最小化实验。什么意思就是把SQL的WHERE条件一个个去掉每去掉一个就用Explain看一次这样能快速定位到底是哪个条件导致了全表扫描。这个方法在排查复杂SQL时特别高效比盯着SQL空想要直观得多。另外有一个实用技巧在测试环境用EXPLAIN ANALYZE来替代EXPLAIN。这是MySQL 8.0新增的功能它不只显示执行计划还会真实执行SQL并输出每个步骤的耗时和行数比EXPLAIN的预估数据可靠得多。我自从用上EXPLAIN ANALYZE之后排查效率提升了一个档次。注意这个功能会真实执行SQL千万不能在线上直接跑尤其是UPDATE和DELETE语句。2.2 联合索引设计最左前缀原则与字段顺序联合索引是实际开发中用得最多的索引类型但也是设计错误率最高的。它的核心规则是最左前缀原则一个联合索引(a, b, c)实际上同时创建了(a)、(a, b)、(a, b, c)三个索引但查询条件如果从b或c开始就不满足最左前缀索引就用不上。设计联合索引时字段顺序参考两个维度等值条件优先其次是区分度高的字段优先最后是排序和分组字段。举个例子一个订单表经常用WHERE user_id ? AND status ? ORDER BY create_time这样的查询那么联合索引设计为(user_id, status, create_time)就是比较合理的选择。前两个字段负责精确定位第三个字段利用B树天然有序的特性帮ORDER BY省掉文件排序。这里有一个优化器的小秘密如果查询里同时有user_id ?和status IN (1,2,3)这种混合条件你把user_id放在最左边还是status放在最左边结果可能有很大差异。IN条件本质是多次等值查询的合并如果IN的值很多它有可能让优化器放弃索引。所以我一般建议把固定等值条件的字段放在联合索引最左边把IN或范围条件字段放在后面。2.3 前缀索引和覆盖索引的使用心得前缀索引是对字符串列的前N个字符建立索引主要用来解决超长字符串列带来的索引过大问题。比如一个表存了用户的邮箱地址如果对整个email字段建索引B树节点能容纳的条目会变少索引占用空间大查询效率反而下降。这时候可以只对email的前10个字符建索引索引体积大幅减小。但是前缀索引有一个致命缺陷它不能用于覆盖索引扫描。因为前缀索引只存储了字段的一部分内容MySQL必须回表才能取到完整字段值。另外前缀索引对ORDER BY也不友好因为它只存储了前缀部分的排序信息前缀相同的值无法继续比较。所以在设计前缀索引时需要权衡索引体积和查询覆盖率。我的经验是先用SELECT COUNT(DISTINCT LEFT(email, N)) / COUNT(*)计算不同N值下的区分度选择区分度不低于0.9的最小N值这样既能缩小索引体积又能保证查询选择性。覆盖索引是查询列全部包含在索引中不需要回表的优化手段。它的价值被很多人低估了。一个二级索引可能只有几个字段但如果我们把查询需要的列都设计进索引让Extra列显示Using indexIO次数会大幅减少。需要注意的是不要为了追求覆盖索引而盲目添加过多的索引字段因为索引字段越多写入开销越大B树层级也可能变深。覆盖索引是以空间换时间的典型设计时要确保收益大于成本。3. 索引失效场景全盘点这些坑我基本都踩过索引失效是面试高频题但很多人背了八股之后到真实场景里依然一脸懵。这一章我把常见的失效场景逐个拆解每个都给出原因和解决方案这些全是我在实际工作中踩过的坑。3.1 函数操作与算术运算索引的无形杀手在WHERE条件中对索引列使用函数是导致索引失效最典型的原因之一。比如WHERE DATE(create_time) 2024-06-01即便create_time上有索引MySQL也没法走索引因为它需要先对每一行计算DATE()的值才能和常量比较。正确的写法是改写为范围条件WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00这样不仅能用上索引还更准确地表达了语义。同样对索引列做算术运算也不行。有一个热搜词叫mysql中int5大概就是指这种场景WHERE age 5 30。这个SQL无法使用age列上的索引因为MySQL需要对每行执行加法运算后才能比较。解决办法很朴素把运算从索引列上移到常量那边改成WHERE age 30 - 5。别看这个改动很小在数据量大时就是全表扫描和索引查找的天壤之别。我处理过一个线上事故一张日志表有几千万条数据开发同学在代码里写了一条WHERE DATE(create_time) CURDATE()的统计SQL结果每次执行都要全表扫描直接把数据库CPU打满了。后来改成范围查询后耗时从几十秒降到了几十毫秒。这类问题的排查其实很简单——看到Explain里typeALL再看WHERE条件里有没有函数包裹索引列基本一抓一个准。3.2 隐式类型转换varchar字段的隐形陷阱隐式类型转换是索引失效的重灾区因为它藏得很深。最常见的场景是表里字段是varchar类型但查询条件传入了数字。比如user_no是一个varchar类型的字段你写了WHERE user_no 123456MySQL会隐式地把字段转换成数字再比较相当于对索引列应用了CAST函数索引自然失效。这个坑我踩过不止一次。有一次排查一个诡异的慢查询SQL看起来没有任何问题索引也建了但Explain就是显示typeALL。后来仔细确认才知道代码从接口里取到的参数被框架自动转成了int类型拼到SQL里就是WHERE user_no 123456导致隐式转换索引失效。解决方法是保证类型一致varchar字段就传字符串写SQL时明确加上引号比如WHERE user_no 123456。另外如果你的表里两个字段JOIN时类型不一致比如一个是varchar一个是int也会触发隐式类型转换这种问题更加隐蔽需要通过查看表结构来排查。我在优化联表查询时第一步永远是检查JOIN两边的字段类型是否一致这是一个容易被忽略但非常影响性能的细节。3.3 LIKE模糊查询和FIND_IN_SET的边界情况LIKE查询大家都很熟悉WHERE name LIKE 张%能走索引WHERE name LIKE %张不能走索引。原因在于B树的索引结构是按从左到右的顺序排列的张%前缀可以确定一个范围而%张不知道从哪里开始找只能全量扫描。这是一个经典且容易理解的失效场景。实际操作中如果业务真的需要后缀模糊匹配有几个替代方案一是把数据反转存储比如将name反转后存成rev_name查询时也反转关键字然后走前缀匹配二是使用全文索引MySQL的FULLTEXT索引支持更灵活的模糊搜索三是引入Elasticsearch这类搜索引擎处理复杂的文本搜索需求。具体怎么选取决于项目规模和成本。再说说FIND_IN_SET热搜词里有findinset能走索引吗这里直接给结论不能走索引。FIND_IN_SET是一个函数调用而且它的语义是判断某个值是否在一个逗号分隔的字符串列表中这种逻辑本质上是对列表做遍历匹配和B树的有序性完全不搭边。如果你的表里有这种逗号分隔的冗余字段并且经常要查它建议规范化设计拆成关联表或者用JSON类型加多值索引。3.4 优化器也有叛逆期什么时候它宁愿全表扫描有一种情况比较反直觉明明索引存在条件也符合索引规则但优化器就是不走索引选择全表扫描。原因很简单——当全表扫描的成本比使用索引更低时优化器会毫不犹豫地选择全表扫描。这在数据分布极不均匀时经常发生。比如一张表有一亿行数据status字段只有0和1两个值其中99%的行是status1。你查询WHERE status1时如果走索引需要回表读取近一亿行这个代价远超全表扫描所以优化器会选择ALL。相反如果查status0只涉及一百万行走索引就是划算的此时Explain就会显示typeref。遇到这种情况不要觉得是MySQL抽风而是要想办法改变成本评估。常用手段包括使用FORCE INDEX强制指定索引、改写SQL逻辑、或者给数据量极少的类别单独建表。在实际业务中如果某类数据只有极少量且访问频繁甚至可以考虑用缓存来扛不必每次都打数据库。4. 进阶优化索引下推、排序优化与实战复盘前几章的内容已经能覆盖大部分日常工作场景但进阶优化的知识点也能帮你拉开和普通开发之间的距离。这一章聊索引下推的原理和验证方法、如何消灭Using filesort然后完整复盘一条慢SQL的优化全过程。4.1 索引下推ICPMySQL 5.6带来的加分项索引下推Index Condition Pushdown简称ICP是MySQL 5.6引入的一个优化特性它解决的核心问题是减少回表次数。在没有ICP时联合索引的遍历过程是根据索引定位到记录然后回表取整行数据再在server层做WHERE条件过滤。有了ICP后MySQL会在存储引擎层遍历索引时直接利用索引中已有的字段做条件判断过滤掉不满足条件的记录只有真正满足条件的记录才回表。举个例子联合索引(name, age)查询条件是WHERE name LIKE 张% AND age 20。这条SQL的语义是先按张%定位索引项然后对每个索引项判断age是否大于20。没有ICP时所有name以张开头的记录都要回表再在server层过滤age有ICP时age 20这个条件被下推到存储引擎在索引遍历过程中直接过滤回表次数大幅减少。怎么判断ICP有没有生效看Extra列如果显示Using index condition说明ICP生效了。我在MySQL 5.7和8.0上验证过多次无论是数据量大小ICP对这类查询都有明显提升。需要注意ICP不能用于覆盖索引场景因为覆盖索引本身不需要回表也就谈不上减少回表。另外ICP对主键索引无效因为主键索引本身就能直接访问到完整行数据。4.2 告别Using filesort排序优化的关键思路ORDER BY导致的Using filesort是性能杀手。只要Explain的Extra列出现Using filesort就意味着MySQL在内存或磁盘上执行了一次额外的排序操作。在某些场景下排序的数据量超过了sort_buffer_sizeMySQL就会把排序数据写入磁盘临时文件性能急剧下降。根因在于索引本身就是有序的如果ORDER BY的字段顺序恰好符合索引的排列顺序MySQL就能直接按索引顺序读取数据省掉排序环节。所以优化的核心思路就是让排序字段尽可能融入索引。具体来说有几种做法。第一种是单字段排序直接在该字段上建索引即可。第二种是双重排序加条件过滤比如WHERE user_id ? ORDER BY create_time联合索引(user_id, create_time)就是最合适的因为它既能过滤user_id又能让create_time按顺序读取。第三种是排序方向和索引方向要一致如果索引顺序是升序而ORDER BY是DESCMySQL 8.0之前只能反向扫描或额外排序MySQL 8.0支持降序索引可以为这种场景专门建降序索引。我还有一个心得如果排序的数据量真的很大可以考虑在应用层做排序把数据量压缩到最小之后再排序或者用缓存预热数据。数据库用于排序是非常消耗资源的能在应用层解决就尽量别让数据库扛。4.3 实战复盘一条两千万数据量的慢SQL优化全过程最后分享一个我处理过的真实案例完整走一遍从发现问题到优化落地的流程。背景是一张订单流水表大约两千万行。原始SQL简化如下SELECT user_id, order_no, amount, status FROM order_flow WHERE status 1 AND create_time BETWEEN 2024-05-01 AND 2024-05-31 ORDER BY create_time DESC LIMIT 20;这条SQL在线上执行耗时约8秒用户体验极差。我拿到手后的第一步是加上EXPLAIN分析执行计划。结果如下typeALL全表扫描possible_keysNULLkeyNULLrows约2000万ExtraUsing where; Using filesort从执行计划来看整条SQL是全表扫描加文件排序等于把两千万行数据全部捞出来过滤之后再排序再取20条返回。问题的核心在于没有合适的索引来支撑WHERE条件和排序。分析一下SQL的查询模式status是等值条件create_time是范围条件排序字段是create_time。另外SELECT里除了这三个字段外还有user_id、order_no、amount。我综合考虑后建了一个联合索引ALTER TABLE order_flow ADD INDEX idx_status_time (status, create_time);这个索引的设计思路是status放在最左边用于等值过滤create_time紧随其后用于范围查询同时还能利用索引天然有序的特性让ORDER BY create_time DESC直接按索引倒序读取完全不产生文件排序。加完索引后再次执行Explaintyperefkeyidx_status_timerows约25万ExtraUsing index conditiontype从ALL变成了refrows从两千万降到了25万Using filesort消失了。但这里注意还有一个Using index condition说明查询使用了索引下推在索引遍历过程中过滤了create_time的范围条件减少了大量回表。这是因为范围条件在联合索引中无法同时用于过滤和排序ICP在这里帮了大忙。再看一下实际执行效果优化前8秒优化后约120毫秒性能提升了60多倍。用户从等半天出不来变成了即点即出。这个案例的核心经验就是联合索引字段顺序设计对了WHERE过滤、ORDER BY排序、索引下推这些优化机制才会协同工作。这里还有一个值得注意的点如果把create_time放在status前面创建索引比如idx(time_status)结果会完全不同。因为status等值条件不在最左边优化器无法直接利用该索引精确定位status1的记录查询效率会大打折扣。所以联合索引字段顺序的决定一定要结合实际的查询模式来设计而不是随意排列。再往后扩展如果这张表的查询模式进一步复杂化比如增加了更细的维度过滤比如shop_id、channel_source等字段那么索引策略可能需要调整为(status, shop_id, create_time)这样三列的组合。这需要根据业务的发展和新的慢查询日志持续迭代没有一劳永逸的索引设计。回到文章开头那个线上系统的优化案例它和这个案例的套路几乎一模一样先用Explain定位全表扫描和文件排序再根据WHERE和ORDER BY的条件组合设计联合索引最后验证性能。方法论是通用的关键在于你能不能坚持每个环节都做到位。我在实际排查中最大的体会是不要凭感觉优化SQL一定要以Explain的输出为事实依据每做一次改动就重新执行一次Explain对比效果这样才能保证每一步的优化都是有效且可控的。