一个订单量上千万的系统慢查询日志里每天刷出来一批三秒以上的SQL打开一看都是很普通的写法——等值条件、排序、limit。加过索引还是慢EXPLAIN看不出明显问题数据量一大照样把数据库拖垮。这类问题我在实际项目里遇到太多次了最后发现几乎都和索引策略有关而EXPLAIN才是能把问题定位到具体那一列的钥匙。这篇文章不打算讲空洞的理论就围绕索引策略和EXPLAIN对比把我自己在生产环境里排过的一批慢SQL完整复盘一遍适合已经被慢查询折磨过、想真正提升SQL排查能力的后端开发。1. 慢SQL通常不是SQL的错索引设计最常见的三个误区先说我观察到的现象大多数团队遇到慢SQL第一反应是改SQL写法第二反应是加索引但加索引也经常凭直觉——哪个字段出现在WHERE里就给哪个字段建单列索引。这种做法在小数据量下没有问题数据量到了千万级靠直觉建索引反而会制造新的问题。1.1 误区一索引越多越好没考虑写放大我见过一张业务表上挂了十几个单列索引的情况每个索引都对应一条查询CUD操作全部变慢。InnoDB的每个二级索引都是一棵独立的B树插入一行数据要同时刷新所有索引树等于把写放大倍数直接乘以索引数量。更麻烦的是优化器面对多个单列索引有时会选择一个选择性很差的索引明明两个索引分别都能用却没法同时用两个单列索引高效过滤MySQL 8.0能做的index merge反而在某些条件下变慢。提示判断索引是否该建先看查询频率和数据基数不要因为某个字段出现在WHERE里就建索引。索引是给高频查询准备的不是给所有查询准备的。1.2 误区二索引能覆盖等值查询就够了忽略了排序和回表很多开发以为EXPLAIN里出现Using index就算万事大吉实际上Using index分两种含义一种是覆盖索引真正不用回表另一种只是在索引条件下推时不那么完整。更常见的问题是ORDER BY在索引里找不到对应顺序执行计划里出现Using filesort。filesort不是不能有但当排序数据量几百上千万行的时候MySQL要把结果集先放进sort buffer做快排再逐条返回代价远大于索引天然的有序性。1.3 误区三EXPLAIN只是看看而已没看懂它的每列含义EXPLAIN输出里最关键的信息往往不是那一行rows数而是key、key_len、Extra的组合。很多人只看type是不是ALL、有没有Using filesort却没分析key_len到底用到联合索引的第几列也没看Extra里的Using index condition背后隐含的回表代价。数据量小的时候这些差异感知不出来一旦数据量上来任何一次多余的回表都是以十万为单位的随机IO。2. 动手之前先看懂EXPLAIN几个最容易被忽略的字段EXPLAIN每个字段都能说半天但排障时真正要抓的是type、key_len、rows、Extra这四个。我结合一个实际例子逐个讲。2.1 type从system到ALL读懂访问路径的九级阶梯type值的好坏顺序大致是system const eq_ref ref range index ALL。多数慢SQL的问题集中在range以下。const主键或唯一索引等值匹配最多返回一行MySQL会把这一行当作常量处理。eq_ref多表JOIN时被驱动表通过主键或唯一索引匹配驱动表每个驱动行最多找到一行。ref普通二级索引等值匹配返回多行这是日常查询最理想的级别。range索引范围扫描BETWEEN/IN/大于小于都归到这一类能用但要注意范围大小。index遍历整棵索引树比全表扫好因为数据更紧凑但如果覆盖不了所有字段仍然需要回表。ALL全表扫描慢SQL重灾区。我在排障时如果看到type从ref退化成ALL第一反应不是SQL写错而是查索引是否失效或优化器统计信息是否过时。2.2 key_len的计算逻辑联合索引命中的真实边界联合索引最大的坑在于你以为建了三个字段的索引实际上因为查询条件限制只用了前面一列或两列。key_len就是用来验证这个问题的。以idx_user_status_time(user_id, status, create_time)为例如果表结构里user_id是varchar(20)且字符集utf8mb4且允许NULLstatus是tinyint NOT NULL那么命中user_id这一列时key_len约为 20*4 2变长字段长度前缀 1NULL标志 83再命中status时key_len加 tinyint的1字节变成84左右排序用到create_time不会体现在key_len里因为key_len只统计WHERE条件里真正参与索引匹配的列通过对比三个版本SQL的key_len变化就能确认联合索引到底用到了第几列不用猜。2.3 rows与filtered成本估算但不是最终真相rows是优化器按统计信息和索引基数估算出来的扫描行数filtered表示经过WHERE过滤后剩余行数的百分比。两者结合可以估算实际返回行数但注意只是估算不是真实值。什么时候会被误导当表统计信息过期时rows可能偏差数倍。我遇到过一张表频繁大批量更新统计信息没来得及刷新EXPLAIN显示rows只有几百实际执行却扫了几百万行。处理方式就是执行ANALYZE TABLE之后再确认执行计划是否变化。2.4 ExtraUsing index、Using filesort、Using temporary的三个警示牌Extra字段的信息量比type更直接Using index覆盖索引查询所需字段全部在索引树里不需要回表这是最理想的。Using index condition索引条件下推ICP部分过滤下推到存储引擎层但最终还是要回表读取完整行性能中等。Using where先把数据读出来再在Server层过滤通常意味着索引没有完全包含过滤条件。Using filesort排序无法走索引必须额外排序数据量大时是主要性能杀手。Using temporary用了临时表常见于DISTINCT、GROUP BY、某些JOIN严重时要特别注意。提示重点看Extra里面同时出现的组合。比如Using index condition; Using filesort说明索引帮你过滤了一部分行但排序仍然在Server层做生产环境里这类SQL数据量一大就出问题。3. 联合索引策略实战一次覆盖索引改造的完整对比前面偏认知这一节开始动手。用一个订单查询场景把单列索引改成联合索引并逐步做EXPLAIN对比。3.1 场景定义一个典型的用户订单查询表结构示意如下CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id varchar(20) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, amount decimal(10,2) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;业务需求是查询某个用户在某个状态下的订单按创建时间倒序分页。SQL写出来是这样SELECT id, amount FROM orders WHERE user_id U12345 AND status 1 ORDER BY create_time DESC LIMIT 20;数据量约2000万行该用户订单总量约几十万status1约占其中三分之一。3.2 第一版索引只给user_id建单列索引ALTER TABLE orders ADD INDEX idx_user (user_id);EXPLAIN结果示意typerefkeyidx_userkey_len82varchar(20) NOT NULLutf8mb4下是802rows约30万ExtraUsing where; Using filesort这里的问题是user_id筛选后仍有30万行status过滤在Server层做还要对create_time做filesort。真正运行时这30万行全部要先读出来、排序再取20行代价非常大。3.3 第二版索引联合索引(uid, status, create_time)ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);EXPLAIN结果示意typerefkeyidx_user_status_timekey_len83user_id 82 status 1rows约10万ExtraUsing where; Using filesort注意key_len从82变成83说明status这一列也进了索引匹配扫描行数从30万降到10万但Extra里仍然有Using filesort。为什么因为user_id和status是等值条件走的索引序是(user_id, status, create_time)前缀。排序需要create_time倒序索引叶子节点天然按create_time正序排列理论上倒序可以用索引逆序扫但这里LIMIT 20只取前20条理论上也可以走索引逆序取前20条再回表检查status。可执行计划仍然报filesort——原因是这个version的优化器对“前缀等值排序字段”的组合如果不选择覆盖索引就可能退化为Server层排序。3.4 第三版索引覆盖全部查询字段ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time, amount);或者把查询改成只取索引内的字段SELECT id, amount FROM orders WHERE user_id U12345 AND status 1 ORDER BY create_time DESC LIMIT 20;如果索引是(user_id, status, create_time, id)而amount不在索引里仍然需要回表。但可以利用覆盖索引把排序和过滤完全消灭在索引树里。EXPLAIN结果示意typerefkeyidx_user_status_timekey_len83rows约10万ExtraUsing where; Using indexUsing index说明整个查询包括ORDER BY create_time都在索引树里完成不需要回表也不需要filesort。这个版本在实测里从原来300ms级别降到5ms以内。3.5 为什么字段顺序这么重要最左前缀的真实约束联合索引的字段顺序不是随便排的。规则很简单能用等值匹配的字段放前面范围匹配放中间排序字段放最后覆盖查询字段放末端。如果上面把索引建成(status, create_time, user_id)查询条件WHERE user_idU12345 AND status1能用上status和后面的部分吗根据最左前缀原则第一列status可以定位但第二列是create_time不是user_id所以user_id无法直接参与索引匹配只能在前面筛选结果基础上做过滤回退到非常糟糕的计划。提示联合索引列顺序的判断标准不是“我经常用哪个字段”而是“条件里哪些字段能提供等值匹配哪些只能提供范围匹配哪些需要参与排序”。等值匹配列永远放在范围列和排序列前面。4. 排序与去重的隐形开销filesort和临时表的EXPLAIN征兆很多慢SQL不是死在WHERE过滤而是死在ORDER BY和DISTINCT/GROUP BY。这两个操作的EXPLAIN特征非常明显分别是Using filesort和Using temporary。4.1 filesort的两种实现优先队列排序与归并排序排序数据能全部放进sort_buffer_size时MySQL用优先队列排序内存排序相对还好。一旦数据量超过sort buffer就会把排序结果分块写到磁盘临时文件再用归并排序合并这时的IO开销非常恐怖。排查时可以从SHOW STATUS LIKE Sort_merge_passes查看归并次数次数越多越危险。4.2 用索引消除filesort让ORDER BY走树序排序能用索引的前提是ORDER BY字段必须和索引列顺序完全一致方向可以相反并且这些列前面的所有索引列都必须是等值条件。回到订单场景索引(user_id, status, create_time)中user_id和status被等值条件锁死剩下create_time的顺序正好是索引树的自然顺序所以完全不必要filesort。实际验证时对比下面两个SQL的Extra-- A. 排序命中索引无filesort SELECT id, amount FROM orders WHERE user_id U12345 AND status 1 ORDER BY create_time DESC LIMIT 20; -- B. 排序字段乱序出现filesort SELECT id, amount FROM orders WHERE user_id U12345 AND status 1 ORDER BY amount DESC LIMIT 20;B方案里amount不在索引树的有效顺序中虽然在索引末端但前缀等值后order by amount与create_time排列不等价所以必然filesort。4.3 一个容易忽视的回退场景范围查询打乱排序序如果查询条件里create_time变成了范围查询比如SELECT id, amount FROM orders WHERE user_id U12345 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;这时候索引(user_id, create_time)可以发挥作用user_id等值确定第一列create_time范围确定第二列ORDER BY create_time仍然可以沿索引顺序走。但如果再加一个status等值条件而索引设计成(user_id, create_time, status)那么status没法直接在索引里定位因为第二列已经被范围条件跳过。这也是为什么在联合索引设计时要把所有等值条件列放到范围列之前的原因之一。4.4 DISTINCT和GROUP BY的临时表问题DISTINCT操作如果无法直接利用索引去重优化器会创建一个临时表EXPLAIN里的Extra会出现Using temporary。GROUP BY同理如果分组字段不满足最左前缀也会用到临时表和filesort。常见优化手法是让分组字段本身成为联合索引的最左前缀这样分组天然有序既省掉临时表也省掉排序。5. 索引失效排查用EXPLAIN定位那些“假索引”加了索引但没用上这是最搓火的场景。下面讲几个我在真实项目里反复踩的坑以及对应的EXPLAIN特征。5.1 隐式类型转换字段是字符串条件却是数字表里user_id是varchar(20)SQL写成WHERE user_id 12345MySQL会把字段转成数字再比较导致索引列上发生隐式函数转换索引失效。EXPLAIN里type直接变成ALL。曾经线上有一个慢查询就是这种问题接口层传参是数值类型框架自动拼SQL时脱掉了引号结果几百万行的表全表扫描。排查方法是把条件改成字符串形式或者干脆统一接口传参类型。验证方式就是对两条写法分别EXPLAIN看key和rows变化。5.2 对索引列使用函数导致无法使用索引典型的写法是WHERE DATE(create_time) 2024-01-01这会让索引列上套了函数B树的排序完全失效只能全表扫描。改成WHERE create_time 2024-01-01 AND create_time 2024-01-02之后type就能从ALL变回rangekey也能正确命中。另一个常见函数场景是WHERE LEFT(phone, 3) 138这类写法从一开始就不该让字段进索引匹配。5.3 OR条件惹的祸优化器的无奈选择OR两边都能命中索引但不同索引之间难以直接合并优化器经常选择全表扫描而不是分别用两个索引再合并。一个常见优化方案是拆成两个查询用UNION ALL合并-- 改法示意 SELECT * FROM orders WHERE user_id U12345 UNION ALL SELECT * FROM orders WHERE status 1;注意如果业务上两个条件可能重叠要改成UNION自动去重或业务层处理重复。这个改法在MySQL 8.0.4之后如果设置了合适的optimizer_switch也能靠index merge自动优化但生产环境里我还是更倾向于主动控制执行计划而不是赌优化器行为。5.4 排查链路怎么走先看key再逐条回放一旦怀疑索引失效我的排查顺序是先EXPLAIN看key字段是否为NULL是NULL说明完全没用上。如果key非NULL看key_len是否足够长判断联合索引用了几列。再看Extra里有没有Using where、Using index condition判断过滤是在存储引擎层还是Server层。最后结合rows和实际执行时间确认是否还有回表放大。这个链路基本能把90%的假索引排掉。6. 一次真实优化全程复盘从3.2秒到38毫秒纸上谈兵不如复盘一个完整案例。这是我在一个支付订单表上的真实优化经历。6.1 初始慢SQL和问题表现表pay_log约5000万行核心字段有merchant_id(varchar)、trade_time(datetime)、notify_status(tinyint)、amount。线上监控发现下面这条SQL平均执行3.2秒SELECT id, merchant_id, amount, notify_status FROM pay_log WHERE merchant_id M100 AND trade_time BETWEEN 2024-06-01 AND 2024-06-30 ORDER BY id DESC LIMIT 50;初看SQL没什么问题等值范围主键排序limit 50按道理不该这么慢。EXPLAIN结果type是ALLkey为NULL全表扫描。原因是表上只有主键索引和merchant_id单列索引trade_time没有索引优化器认为用merchant_id单列索引过滤出的数据量太大干脆走了全表。6.2 逐步定位问题所在先看数据分布merchant_idM100的订单一个月约350万行占整表比例不到7%。单独给trade_time建索引再用merchant_id M100 AND trade_time BETWEEN ...用哪个索引都不划算。原因是两个单列索引只能各自过滤一部分再回表合并优化器对这种情况的cost估算往往偏高。接着尝试加入联合索引(merchant_id, trade_time)EXPLAIN的type变成rangekey_len正确rows降到了约350万仍然不算理想但比全表扫描好太多。真正让查询提速到38毫秒的是把排序和limit也吃进索引里。6.3 最终方案联合索引吃进排序字段把索引改成(merchant_id, trade_time, id)。理由是merchant_id等值确定第一列trade_time决定了第二列的范围ORDER BY id本质上就是第三列的逆序LIMIT 50意味着引擎只取最后50条路就可以不需要把350万行全排序。改完后EXPLAIN结果typerangekeyidx_merchant_time_idkey_lenmerchant_id字段长度 trade_time字段长度rows范围估算大幅降低ExtraUsing index condition执行时间从3.2秒降到38毫秒。这里的核心不是让索引完全覆盖所有查询字段而是把排序和limit的代价通过索引序消除掉回表只需要按索引顺序取出50条记录即可。6.4 可复用的优化checklist把这次复盘总结成可以照做的清单先看慢SQL出现频率和数据量级确认值得优化后再动手。把SQL拆成三部分过滤条件、排序条件、返回字段分别分析。过滤条件里等值列放最前范围列随后排序列尽量放进索引尾部。用EXPLAIN对比改造前后的type、key_len、rows、Extra四项指标。如果Extra里还有Using filesort优先考虑排序字段能否进索引。如果还有Using temporary优先看DISTINCT/GROUP BY字段是否符合最左前缀。最后用真实业务数据量验证不要用小数据量得出的结论做判断。我个人的经验是SQL优化最值钱的不是某个技巧而是养成一个习惯每次写SQL前先想清楚索引树的结构写完之后用EXPLAIN验证自己的想法。把索引当成一棵可排序的树来用慢SQL的大部分问题都能在设计阶段规避而不是等到上了生产再用监控去找。这套方法我在多个项目里验证过最慢的一类SQL几乎都能从秒级降到毫秒级如果你也被类似问题困扰可以按这个思路把线上的慢SQL逐条过一遍效果会很明显。