最近接手一个订单系统的汇总报表需求时被一条SQL折腾得够呛。业务方反馈某个数据看板上线后越来越卡从最初的几百毫秒一路劣化到几十秒直接拖垮了整个报表模块。我接手排查时第一件事就是把这条慢SQL捞出来看执行计划结果发现问题比想象中典型得多——索引没生效、深分页、隐式转换全凑一块了。坦白说SQL语句性能优化这件事很多时候不是不会写SQL而是不知道SQL在数据库里到底怎么跑的。本文就用这个真实场景把性能优化的排查链路、执行计划解读和常见改写思路完整走一遍希望对天天跟数据库打交道的朋友有帮助。1. 一张慢了上百倍的SQL问题究竟出在哪1.1 一次典型的慢SQL告警现场那天的告警信息很直白一条查询语句的平均响应时间从0.3秒涨到了42秒且持续了将近二十分钟。业务方说没动过代码数据库也没做过DDL变更就是突然慢了。我连上生产环境的从库先把慢查询日志打开抓到这条SQL的完整文本。这是一条典型的订单汇总查询关联了三张表——订单主表、订单明细表、客户表外加一个时间范围的过滤条件。初步肉眼看下来几个明显的问题已经浮出来了时间范围条件直接写在create_time上但表里实际有另一个更精确的业务时间字段明细表关联时用了OR拼接了两个条件分页用的是传统的LIMIT offset, size写法offset已经到了一百多万。这三条中的任何一条单独出现都不至于让SQL慢成这样但叠加在一起执行计划就彻底崩了。1.2 响应时间的三段式拆解一条SQL从发起到返回响应时间大致可以分为三段网络传输时间从应用到数据库的往返耗时这部分在内网环境下通常很低但如果应用和数据库跨机房RTT可能会成为不可忽略的部分执行时间数据库内部真正干活的时间包括解析SQL、生成执行计划、执行算子、读取数据、排序聚合等返回数据时间结果集从数据库传回应用的时间数据量越大耗时越高。慢SQL优化主要压缩的是第二段时间也就是数据库的执行时间。而执行时间又可以进一步拆解为「寻找数据的时间」和「处理数据的时间」。寻找数据靠索引处理数据靠临时表、排序、连接算法这两部分正好对应了执行计划里最核心的几列信息。1.3 先解决问题还是先分析原因很多人遇到慢SQL的第一反应是把SQL改写一下比如加个索引、拆个子查询然后跑一下看结果对不对就以为完事了。这种做法在慢SQL不致命的时候可以接受但一旦SQL复杂到一定程度不分析原因直接动手大概率会陷入“改完MySQL还是走全表扫描”的困境。我的习惯是先把执行计划完整读一遍把瓶颈定位到具体的算子再动手优化。执行计划就是数据库给自己的“运货方案”它告诉了你数据是怎么被搬运的。方案哪里不对一眼就能看出来。2. 用EXPLAIN读执行计划先学会看门道再动手2.1 EXPLAIN的输出到底在说什么MySQL的EXPLAIN命令可以在不实际执行SQL的情况下输出优化器预估的执行计划。虽然优化器的预估有时候不那么准确但作为慢SQL的第一步排查工具它的信息量已经足够。我习惯先看下面几个关键列列名含义重点关注type访问类型从好到差依次是 system const eq_ref ref range index ALLkey实际使用的索引NULL 表示没用到索引rows预估扫描的行数数字越大说明访问的数据越多Extra额外的执行信息常见有 Using index、Using where、Using filesort、Using temporary对着一份执行计划优先看type是不是ALL全表扫描再看key是不是NULL然后看rows是不是特别大最后看Extra里有没有Using filesort和Using temporary。这四个点扫完SQL慢的大致原因就浮出水面了。2.2 type列访问方式的级别游戏type列是衡量SQL效率最直观的指标。system和const是最理想的表示最多只有一行数据匹配主键或唯一索引等值查询就是这种。eq_ref出现在JOIN场景下被驱动表通过主键或唯一索引关联。ref和range意味着走了非唯一索引或有范围的扫描这是日常业务中最常见的健康状态。真正的警报是ALL和index。ALL是全表扫描等于把整张表的每一个数据页都翻一遍index虽然是从索引树的根节点到叶子节点全扫比ALL稍微好一点但本质上也是全量遍历并没有实现精确命中。我那次排查的时候SELECT子句里关联的明细表type就是ALLrows显示要扫两百多万行一眼就知道问题核心在哪了。2.3 Extra列最容易暴露问题的角落Extra列里有几个很关键的标志。Using filesort表示排序操作没有走索引需要额外的排序步骤。如果数据量大这个排序就是实打实的性能黑洞。我看到这个标志的第一反应是看看能不能通过索引设计直接消除排序。Using temporary则意味着创建了临时表来完成任务常见于GROUP BY、ORDER BY、DISTINCT等操作。临时表如果被创建到了磁盘上那就更严重了。用EXPLAIN看不出来是内存临时表还是磁盘临时表需要配合SHOW STATUS LIKE Created_tmp_disk_tables这类状态变量去验证。另外一个常见的标志是Using where这表示存储引擎层返回数据之后Server层还要再过滤一遍。它本身不算坏事但如果和大量的rows结合往往意味着索引没有“覆盖”所有过滤条件需要回表查询。3. 索引失效的常见场景这几类问题最隐蔽3.1 条件列上做函数运算或隐式转换排查到那条SQL时我发现订单明细表的关联条件非常典型WHERE DATE(create_time) 2024-01-01这种写法从业务逻辑上看完全正确但从优化器的角度来说create_time被包进了函数里索引就失效了。数据库无法对函数处理后的结果直接使用B树来定位只能老老实实全表扫描然后把每一行的create_time都算一遍再比对。正确的写法是把条件改写为范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00还有一种特别容易忽略的隐式转换。比如user_id在表里是varchar类型但应用层传入的是数字SQL写成了WHERE user_id 1001。MySQL会把字段上的字符串转成数字再做比较结果又是索引失效。解决方法是保持类型一致或者在SQL里显式转成字符串再比较。3.2 前导模糊匹配与OR拼接LIKE %keyword%这种写法几乎人人知道百分号在前面索引无法命中。但实际业务里这种需求往往躲不开更温和的替代方案是考虑全文索引或者搜索引擎类的独立组件。OR拼接的问题更隐蔽一些。比如WHERE status PAID OR channel APP即使status和channel上各自都有索引优化器也可能不走索引。MySQL的优化器对OR的处理策略在不同版本间表现差异很大有些情况下它对多个索引的合并扫描成本估算更高结果反而选择了全表扫描。我通常的建议是把OR拆成两个查询用UNION ALL合并或者改用IN来改写。当然最根本的办法还是看业务能不能接受等价改写。3.3 联合索引的列顺序没有设计好这个问题很常见但容易被忽略。联合索引(status, channel, create_time)建立后如果SQL里的过滤条件只有channel而没有status这条SQL就走不上联合索引最左前缀原则。更反直觉的是即使条件里有status如果查询的是channel和create_time以外的东西依然可能触发回表甚至优化器会判断全表扫描更划算。联合索引的设计不是建了就完事必须根据实际查询模式反推索引字段顺序。我这里有个简单经验第一原则是「等值条件优先放前面」范围条件放后面。如果status是等值过滤channel也是等值过滤create_time是范围过滤那么联合索引(status, channel, create_time)就比(channel, create_time, status)更通用因为业务里常出现只查status固定的场景。4. 慢SQL的改写思路不止是加索引这么简单4.1 深分页问题的根治思路过往最常见的分页写法是SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20这种写法的本质是数据库必须先把前一百万行读出来、排好序然后直接丢弃只返回最后20行。越翻越慢页数越大越离谱。比较经典的规避方案有两种。一种是「延迟关联」先用覆盖索引查出目标ID再回表取完整数据。SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20 ) tmp ON o.id tmp.id由于子查询只需要访问索引树扫描一百万行索引比扫描一百万行完整行要快得多两张表再通过主键关联整体性能提升非常明显。另一种方案是“基于游标的分页”或者说“键集分页”即记住上一页最后一条记录的位置下一页直接从这个位置往后取SELECT * FROM orders WHERE create_time 2024-01-15 10:00:00 ORDER BY create_time DESC LIMIT 20这种方式彻底绕过了offset的概念每一页的耗时几乎恒定是大数据量分页最推荐的方式。唯一的代价是需要把“上一页末尾的游标”传到下一页业务上要多维护一个参数。4.2 SELECT * 不推荐的底层原因SELECT *最大的问题是它迫使数据库回表读取所有列。即使覆盖索引能够满足查询中的过滤和排序额外列的读取也会让InnoDB不得不做大量随机IO。举个例子如果只需要查询订单号和订单状态覆盖索引(status, order_no)就能直接返回全部所需数据而不需要去主键索引读一整行。但如果你写了SELECT *数据库无论如何都要回表一次索引的“覆盖”意义就被削弱了一半。更麻烦的是SELECT *会多传输很多用不到的字段数据增加网络开销和应用层内存占用。在报表系统里所有列都带回来结果就是Java堆里堆着大量无意义的字符串对象。4.3 关联查询、子查询与JOIN的取舍MySQL优化器对JOIN的处理通常是先选驱动表再对驱动表结果集逐条去被驱动表匹配。驱动表的选择有大学问过小的表被驱动回表太多次过大的表又容易造成中间结果集膨胀。我遇到过一种常见情况一个查询里有两张表一张是十万级别的基础表一张是几十万的扩展表用LEFT JOIN关联。由于驱动表选错了方向优化器把小表当成了驱动表每处理一行都要到大表做一次全表扫描匹配导致SQL跑了很久。排查时试着用STRAIGHT_JOIN强制驱动表顺序SQL立刻就快了几十倍。虽然STRAIGHT_JOIN不建议直接写到生产代码里但它对于定位问题是绝佳的调试工具。最终方案往往是通过调整查询条件中的过滤字段和建立合适的索引让优化器自然选择正确的驱动方式。子查询的问题类似。MySQL 5.6以后有了子查询优化策略但有些子查询仍然会被重写成非预期的JOIN方式。我的一般建议是数据量大的场景下把子查询单独拿出来执行一次把结果集放到应用层显式拼接反而更好排查、更好监控。5. 一条SQL的优化全流程复盘5.1 原始SQL与瓶颈初判这次要展示的是一条稍微脱敏后的汇总SQL它来自一个模拟的订单分析模块SELECT c.customer_name, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(oi.item_amount) AS total_amount FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id AND oi.status IN (NORMAL, RETURNED) LEFT JOIN customer c ON o.customer_id c.customer_id WHERE o.create_time 2024-01-01 AND o.create_time 2024-02-01 AND (o.source IN (APP, WEB) OR o.channel CALL) GROUP BY c.customer_name ORDER BY total_amount DESC LIMIT 50;刚看到这段SQL时第一感觉是逻辑不复杂但执行起来非常重。一是COUNT(DISTINCT)对订单明细表的去重统计需要在临时表里做大量聚合二是OR条件可能让orders表索引命中失败三是LEFT JOIN关联后做了聚合临时表的数据量被放大。5.2 执行计划解读与根因锁定用EXPLAIN拿到计划后问题暴露得很清楚orders表type是ALL全表扫描order_items表关联走了索引但那是因为我提前在(order_id, status, item_amount)上建了联合索引恰好被用上了Extra里有Using temporary因为COUNT(DISTINCT)和GROUP BY的组合导致MySQL需要使用临时表来做去重统计排序字段total_amount来源于聚合结果无法走索引排序额外出现了Using filesort。所以慢的核心不只是扫描行数多而是临时表文件排序大范围扫描三件事同时发生。单独修掉其中一个效果都不够明显必须组合拳。5.3 优化方案落地与前后对比第一步先解决orders表全表扫描的问题。create_time上有索引但OR条件让优化器在source IN (...)和channel CALL之间摇摆不定。我把OR重写为两个查询结果的UNION ALL每个分支都能充分利用已有的索引。第二步优化聚合逻辑。COUNT(DISTINCT o.order_id)其实等价于统计满足条件的订单数由于主表的order_id是唯一的可以直接改为COUNT(o.order_id)省掉去重开销。这里的业务前提是订单ID在主表中不会重复确认后就可以放心改。第三步把排序去掉total_amount的临时文件排序代价。由于SUM(oi.item_amount)的总额需要聚合后才知道这一项无法通过索引消除排序但可以缩小参与聚合的数据量来降低排序成本。增加order_items表status IN (NORMAL, RETURNED)的下推条件让明细表在关联前先把数据过滤掉一部分临时表变小后排序自然快。优化后的核心SQL长这样SELECT c.customer_name, COUNT(o.order_id) AS order_cnt, SUM(oi.item_amount) AS total_amount FROM orders o JOIN order_items oi ON o.order_id oi.order_id AND oi.status IN (NORMAL, RETURNED) LEFT JOIN customer c ON o.customer_id c.customer_id WHERE (o.source IN (APP, WEB) AND o.create_time 2024-01-01 AND o.create_time 2024-02-01) OR (o.channel CALL AND o.create_time 2024-01-01 AND o.create_time 2024-02-01) GROUP BY c.customer_name ORDER BY total_amount DESC LIMIT 50;跑了一轮压测响应时间从原来的42秒压到了1.1秒左右效果非常直观。这里仍旧用了OR条件但两个分支内部都有合适的索引MySQL能够分别走索引再合并结果比原来的全表扫描高效得多。5.4 回归验证与监控留痕SQL优化完之后不能只看一条查询的耗时还得做回归验证。我把优化后的SQL放到测试环境跑了完整的回归用例确认业务结果没有变化。之后在生产环境从库把慢查询日志调出来把这条SQL的查询计划定期记录到一张监控表里观察后续几天的执行情况。另外要给业务方提个醒任何SQL优化都建立在对业务语义的准确理解上。改SQL之前必须先问清楚业务方对于口径的定义否则只是把查询结果“优化”成了错误结果。6. 优化之后我养成的几个工作习惯6.1 每一条慢SQL都要留下排查记录我自己的做法是每处理一条慢SQL就把原始的SQL、执行计划截图、优化后的SQL、前后耗时对比记录在一个共享文档里。这样过几个月回头再看能总结出很多常见的坑对新人也很有参考价值。记录里我还会特别标注当时遗漏的点。比如某个SQL看起来是排序慢但实际上深层次原因是驱动表选择问题或者某个SQL在测试环境很快上生产就慢得离谱——这种环境差异通常和统计信息过期有关需要ANALYZE TABLE刷新统计信息。6.2 上线前统一做执行计划巡检在自己的数据库流程里我增加了一道检查所有新SQL上线前必须附上EXPLAIN结果并且type不能出现ALL特殊情况必须单独说明。这个规则很简单但能在早期拦住大概率的问题SQL。同时还要关注数据库参数层面的一些坑。比如临时表大小太小的tmp_table_size会导致内存临时表很容易转成磁盘临时表肉眼上看就是SQL突然变慢。通过SHOW GLOBAL STATUS观察Created_tmp_disk_tables的变化能快速判断是否有这种隐性问题。6.3 慢日志和监控指标是最后一道防线不是所有慢SQL都能在开发阶段发现。生产环境和测试环境的数据分布差异大统计信息也不同因此线上一定要开启慢查询日志并把超过阈值的SQL自动捞出来分析。我比较推荐把阈值都设置到一个相对激进的值宁可多记录一些也不要漏掉潜在的性能隐患。监控层面我重点看三个指标QPS、慢查询次数、临时表落盘次数。这三个指标一旦同时出现异常基本可以断定是有新SQL上线或者数据量突变导致的执行计划劣化。做SQL语句性能优化这么多年最深的体会是它考验的往往不是SQL语法功底而是对数据库底层原理的理解程度。你在EXPLAIN里每看到的一个标志都对应着内存里、磁盘上的一系列动作。理解了这些动作优化方向自然就不会跑偏。最后再分享一个小技巧拿到一条慢SQL后我习惯先在测试环境用EXPLAIN ANALYZE或者SHOW PROFILE看看实际执行的时间分布对比一下优化器的预估和真实执行之间的差距。这种差距本身就是一条很有价值的线索经常能帮你找到索引统计信息过期、优化器误判等隐藏得更深的问题。