一条 SQL 在生产库上跑了 30248 秒才出结果你会怎么处理30248 秒换算一下就是 8 小时 24 分钟足够你踏实睡一觉再起来吃个早饭。这不是标题党也不是段子是我去年接手的一起真实性能事故也是我做 SQL 优化以来数字跨度最夸张的一次。今天不绕弯子把完整的排查链路、每一步改写逻辑、踩过的坑、以及最终方案全摊开写清楚希望能给正在被慢 SQL 折磨的同学一点真正能用的参考。文章会覆盖慢 SQL 定位、执行计划解读、索引失效场景、主键索引和唯一索引的区别、InnoDB 存储引擎选型对优化上限的影响以及单条 SQL 优化到极限之后的并行思路。1. 还原现场一条跑了 8 小时 24 分钟的报表查询1.1 业务背景一张必须跑完才能发出去的结算报表先说业务场景。我们当时维护的是一个物流结算系统每天凌晨有个对账任务要把前一天所有有效订单的明细金额汇总出来和订单主表金额做比对不一致的捞出来人工复核。这套任务跑了好几年以前数据量几百上千万行的时候半小时内能出结果大家都习惯了。等订单量涨到几千万、明细表冲到 8000 万行之后事情开始不对劲。先是跑了 3 个小时后来变成 6 个小时最后那个周末直接跑到 30248 秒还没结束。DBA 半夜被叫起来业务方在群里问报表是不是挂了实际上它没挂只是慢到让人怀疑人生。这种事故最难受的地方在于它不是报错不是死锁它就是单纯地慢。系统 CPU 和 IO 都在动数据库连接也不超时你甚至不知道怎么优雅地终止它。所以接到这种问题第一步不是改 SQL而是先搞清楚它到底在干什么、为什么干这么久。1.2 原始 SQL 长什么样我把当时那条惹祸的 SQL 简化了一下核心结构是这样的SELECT u.user_name, o.order_no, (SELECT SUM(d.amount) FROM t_order_detail d WHERE d.order_id o.order_id) AS detail_amount, o.amount, r.region_name FROM t_order o LEFT JOIN t_user u ON u.user_id o.user_id LEFT JOIN t_region r ON r.region_id o.region_id WHERE DATE(o.create_time) BETWEEN 2024-01-01 AND 2024-01-31 AND o.order_status IN (1, 2, 5) ORDER BY o.amount DESC LIMIT 5000;说实话第一次看到这条 SQL 的时候我脑子里就一句话这慢得不冤。它几乎把新手容易犯的错都踩全了——函数包裹索引列、相关子查询、无脑 ORDER BY 大字段排序。但慢得不冤不代表你能直接扔给开发一句你自己改吧你要能说出具体是哪个环节吞掉了 8 个小时否则没人信你。1.3 表结构和数据量隐患早就埋下了当时四张表的数据规模大概是这样表名用途行数关键索引t_order订单主表约 3600 万主键 order_id唯一索引 order_no普通索引 user_id、create_timet_order_detail订单明细表约 8000 万主键 detail_id普通索引 order_idt_user用户表约 500 万主键 user_idt_region区域字典表约 3000主键 region_id从索引配置看基础不算太差create_time 有索引、order_id 有索引、user_id 有索引。但索引存在和索引能用是两回事。这条 SQL 的问题恰恰就是有索引但用不上并且用了一种最煎熬的方式——逐行执行子查询。后面我详细拆。2. 定位病根慢查询日志加执行计划三板斧锁死元凶2.1 第一步慢查询日志圈出高耗时 SQL遇到性能事故我一般先看三个东西慢查询日志、processlist、执行计划。慢查询日志用来确认哪条 SQL 最可疑processlist 用来确认当前数据库在忙什么执行计划用来确认这条 SQL 为什么慢。我们的生产库本来就开了慢查询日志long_query_time设置的是 2 秒。按理说这条跑了 8 小时的 SQL 应该第一时间被记录但实际查日志发现它记录的是语句开始执行的时间和语句结束执行的时间中间过程没有任何分段采样。所以我只能看到总耗时 30248s却看不到它具体卡在哪个阶段。这也是一个很重要的教训慢查询日志只能告诉你谁慢不能告诉你为什么慢。真正定位得靠 EXPLAIN靠对 SQL 结构的理解必要时候还得用 performance_schema 去看语句各阶段的耗时分布。2.2 第二步EXPLAIN 里的三个红色警报拿到原始 SQL 之后我直接跑了 EXPLAIN结果基本符合预期。简化后的执行计划关键列长这样idselect_typetabletyperowsExtra1PRIMARYoALL36000000Using where; Using filesort1PRIMARYueq_ref1NULL1PRIMARYreq_ref1NULL2DEPENDENT SUBQUERYdref6NULL三个警报一眼就能看出来第一个警报t_order 表是typeALL也就是全表扫描预估要扫 3600 万行。明明 create_time 上有索引为什么不用因为 WHERE 条件写的是DATE(o.create_time) BETWEEN ...。DATE() 这个函数把 create_time 的原始值包了一层B 树索引里存的是原生 DATETIME 值MySQL 没法拿函数处理后的结果去做索引查找只能把行全捞出来逐行调用 DATE() 再判断。第二个警报select_typeDEPENDENT SUBQUERY。这玩意儿是性能杀手里的头号选手。它的含义是外层每处理一行内层子查询就要执行一次。外层 t_order 符合条件的有多少行1 月份有效订单大约 360 万行左右。也就是说光这个子查询就要在 t_order_detail 上执行 360 万次每次按照 order_id 去索引里找对应明细再把多条明细的 amount 加起来。360 万次访问 8000 万行的明细表哪怕每一次都是索引命中累积起来的随机 IO 请求也是天文数字。尤其凌晨跑批的时候Buffer Pool 里根本装不下这么大的热数据大量访问都要落盘这一块就是整个查询最核心的耗时来源。第三个警报Using filesort。ORDER BY o.amount DESC 这个排序在 WHERE 过滤完 360 万行之后进行。MySQL 语义上的 filesort 不一定是文件排序数据量大时会创建临时文件做归并排序无论如何对 360 万行做降序排列再取 5000 条也是一个不小的开销。它在这条 SQL 里不是主凶但它是压垮性能的第三根稻草。2.3 第三步确认逐行子查询这个吞时怪兽有人可能会问为什么不是全表扫描最伤3600 万行的全表扫描确实很伤但它是顺序读InnoDB 顺序读的效率其实还行几十分钟到一两个小时总能扫完。真正让人绝望的是那个相关子查询——它是随机读而且是 360 万次随机读。我做了一个粗略估算假设每次子查询平均要扫描 6 条明细每条明细的访问大概要消耗掉两三次 buffer pool 页面查找。如果这些页面频繁被淘汰、需要从磁盘读单次子查询的延迟可能从 0.1ms 级别拉升到 1~2ms 甚至更高。360 万次乘以 2ms算出来差不多就是 7200 秒。再叠加全表扫描、filesort、以及大量并发任务争抢 IO跑到 30248 秒完全说得通。所以结论很明确病根在相关子查询导火索在函数索引失效帮凶在 filesort。接下来所有的优化动作都围绕这三个点展开。3. 为什么索引建了等于没建索引失效和两种索引的本质区别这条 SQL 优化完之后其实带出一个更值得讨论的大问题很多人以为建了索引就一定快实际上索引被查询写法坑掉的场景太多了。我先系统地过一遍这些都是热搜词里大家问得最多的问题。3.1 函数包裹索引列索引直接废掉最典型的就是WHERE DATE(create_time) 2024-01-01。DBA 说 create_time 上明明有索引为什么执行计划还是全表扫描因为在 MySQL 的索引结构里叶子节点存的是 create_time 的原始值比如2024-01-01 08:30:00。当你用DATE(create_time)去比较时优化器必须对每一行的 create_time 调用 DATE() 函数然后拿函数结果去匹配。它不可能提前把 DATE() 之后的结果排序存进索引因为索引只存原始值。解决办法非常朴素把函数条件改成范围条件。-- 错误写法索引失效 WHERE DATE(o.create_time) BETWEEN 2024-01-01 AND 2024-01-31 -- 正确写法索引可用 WHERE o.create_time 2024-01-01 AND o.create_time 2024-02-01这个改写不是语法层面抠字眼而是底层逻辑变了优化器可以在 B 树上直接做范围扫描从 1 月 1 日零点这个位置开始往后读读到 2 月 1 日零点之前停止完全不用碰 1 月之前和 2 月之后的数据。类似的坑还有WHERE YEAR(create_time) 2024、WHERE LEFT(order_no, 4) DEP-、WHERE CONCAT(a, b) xx。只要索引列被函数包裹一律失效。这不是 MySQL 的 bug是 B 树索引就这脾气。3.2 隐式类型转换与字符集不一致两个隐形杀手第二个常见失效场景是隐式类型转换。比如某张表的 user_id 是 VARCHAR 类型你写WHERE user_id 123MySQL 会把比较双方统一成数值类型。问题是它默认把列值转成数字相当于对 user_id 列执行 CAST 操作索引又废了。反过来如果列是 INT 类型你写WHERE user_id 123MySQL 会把字符串常量123转成数字列本身不动索引往往还能用。所以规则是类型转换加在哪一侧很关键要加在常量侧不能加在列侧。第三个隐形杀手是字符集不一致。两张表 join 的时候如果一列是 utf8mb4另一列是 latin1MySQL 为了保证可比性会隐式把其中一列做转换转换动作一旦落到索引列上索引就失效。我们之前排查过一个诡异的慢 join最后发现就是一张表建得早用了 utf8mb3另一张表后来统一成 utf8mb4关联条件a.user_id b.user_id让优化器对其中一列做了 CAST。我整理了一份常见的索引失效场景对照表做慢 SQL Review 的时候对着查就行查询写法失效原因改写建议WHERE DATE(col) 2024-01-01函数包裹索引列改成范围条件WHERE varchar_col 123隐式类型转换应用层传字符串或对常量加引号WHERE col LIKE %关键词%前导通配符无法定位起点改LIKE 关键词%或上全文索引WHERE a 1 OR b 2多分支无法有效利用单索引UNION 两个查询或建复合索引WHERE status IN (1,2,3)且 status 基数极低选择率太高优化器放弃索引结合高频过滤列建复合索引join 两侧字符集不一致隐式 CAST 落到索引列统一字符集和排序规则3.3 主键索引和唯一索引到底差在哪搜热词里不少人问主键索引和唯一索引的区别我干脆在这里一起讲清楚因为这次优化里也用到了这个知识点。InnoDB 的主键索引本质上是聚簇索引它不只是约束而是决定了整张表的数据物理组织形式。整张表就是一棵以主键为序的 B 树叶子节点直接存放整行数据。所以通过主键查一行是在一棵数据本身就是树叶的树里定位找到叶子就拿到全部字段没有多余的一次回表。唯一索引则是二级索引它的叶子节点不存整行数据只存主键值。查询走唯一索引时先在唯一索引这棵 B 树里找到对应的主键值然后再拿这个主键值去聚簇索引里找一次完整行。这一步叫回表是个额外的随机 IO 成本。对比项主键索引唯一索引物理角色聚簇索引决定数据物理顺序普通二级索引是否允许 NULL不允许允许且可以有多个 NULL一个表的数量只能有一个可以有多个叶子节点内容整行数据主键值查询方式直接定位先定位主键再回表这个区别有两个实战影响。第一设计二级索引时主键越短越好因为每个二级索引的叶子节点都要冗余一份主键值主键是 VARCHAR 超长字符串的话索引体积会成倍膨胀。第二如果查询需要的字段恰好都包含在二级索引里MySQL 可以不回表直接返回这就是覆盖索引。后面我优化 detail 表时就用到了(order_id, amount)这个覆盖索引让 SUM 聚合完全不碰主表数据。3.4 InnoDB 存储引擎决定了优化天花板再补一个背景知识。MySQL 默认存储引擎是 InnoDB它的事务、行级锁、MVCC、崩溃恢复能力是 MyISAM 等老引擎完全比不了的。但也正因为 InnoDB 是聚簇索引组织二级索引查询天然多了回表这道工序所以索引设计对性能的影响比 MyISAM 时代更明显。MyISAM 时代大家习惯表级锁 非聚簇索引全文扫描和读多写少的场景感觉还行。现在的业务基本都用 InnoDB行级锁保证了高并发写入不被整表阻塞但前提是你能走对索引。如果一条 SQL 全表扫描InnoDB 所有行锁相关的优势全白搭Buffer Pool 还被大量无效页占满进而拖垮其他正常查询。另外InnoDB 的 Buffer Pool 设置也很关键。像我们这台机器内存 128Ginnodb_buffer_pool_size设了 80G目的就是让热数据尽量留在内存里。但如果一条 SQL 的访问模式是360 万次随机小查询Buffer Pool 会持续发生页面淘汰和重新载入命中率再高也被打穿。所以与其纠结 Buffer Pool 参数不如先把 SQL 访问模式改对。4. 改写实战从 30248s 到 0.001s 的每一步好背景知识铺垫完了回到事故本身。我按顺序执行了三刀每一刀都单独验证过效果不是一把梭。4.1 第一刀把相关子查询改成 JOIN 后预聚合这是最狠的一刀。原始 SQL 里那个(SELECT SUM(d.amount) FROM t_order_detail d WHERE d.order_id o.order_id)是外层一行、内层一次的结构必须干碎它。我的改写思路是既然要拿订单明细的汇总金额那就先把符合条件的订单集合拿到然后用一条 GROUP BY 聚合语句一次性把明细表扫一遍最后再 JOIN 回订单主表。这样的好处是 t_order_detail 只用被扫描一次而不是 360 万次。SELECT u.user_name, o.order_no, d.amount_sum AS detail_amount, o.amount, r.region_name FROM t_order o LEFT JOIN t_user u ON u.user_id o.user_id LEFT JOIN t_region r ON r.region_id o.region_id LEFT JOIN ( SELECT od.order_id, SUM(od.amount) AS amount_sum FROM t_order_detail od JOIN t_order o2 ON o2.order_id od.order_id WHERE o2.create_time 2024-01-01 AND o2.create_time 2024-02-01 AND o2.order_status IN (1, 2, 5) GROUP BY od.order_id ) d ON d.order_id o.order_id WHERE o.create_time 2024-01-01 AND o.create_time 2024-02-01 AND o.order_status IN (1, 2, 5) ORDER BY o.amount DESC LIMIT 5000;这里要注意一个细节为什么内层聚合要再 JOIN 一次 t_order 并带上时间和状态条件因为如果不加聚合会把全表 8000 万行明细都扫一遍那就得不偿失了。先通过 t_order 的索引过滤出一个月的订单 id 集合再拿这个集合去关联明细表等于把明细表的扫描范围也压到了一个月。MySQL 5.7 之后对派生表FROM 子句里的子查询有自动加索引的优化JOIN 条件 order_id 会被识别并建上临时索引所以这个改写后的 JOIN 执行效率是可靠的。如果你还在用 MySQL 5.6这个版本对派生表优化很弱最好先把子查询结果落临时表再手动加索引。这一刀改完全表扫描和逐行子查询都消失了但 SQL 还没到最优状态因为 WHERE 条件里的 DATE() 函数还在订单主表依然可能走上全表扫描。紧接着补第二刀。4.2 第二刀函数条件改写成范围条件第二刀很简单就是把DATE(o.create_time) BETWEEN 2024-01-01 AND 2024-01-31改成o.create_time 2024-01-01 AND o.create_time 2024-02-01。这个改写的关键点是边界处理。用 BETWEEN 去包 1 月 1 日到 1 月 31 日看着没毛病但它会把 1 月 31 日当天的所有时刻也包括在内而且 DATE() 函数先截断到天再比较逻辑上其实已经隐含了不要时分秒的意图。改成和之后区间是左闭右开create_time 2024-02-01天然排除掉 2 月 1 日的任何记录。更重要的是create_time 上的普通索引终于能用上了。执行计划的 type 从 ALL 变成了 range预估扫描行数从 3600 万降到大约 360 万直接少了一个数量级。4.3 第三刀复合索引和覆盖索引让回表消失前两刀改完整体查询已经从8 小时级别掉到几十秒级别但还能再压。复盘执行计划发现聚合子查询里SUM(od.amount)每次都要通过 idx_order_id 找到明细行然后回表读取 amount 字段。8000 万行的明细表回表代价不小。我给 t_order_detail 加了一个复合索引ALTER TABLE t_order_detail ADD INDEX idx_order_id_amount (order_id, amount);这个索引的作用是覆盖索引。因为WHERE order_id ?的过滤列和SUM(amount)的聚合列都包含在(order_id, amount)这个二级索引里MySQL 可以直接在索引的 B 树上完成定位和求和不需要回表去主键索引里读整行。统计了一下这个索引让聚合子查询的访问成本又降了一截。复合索引的列顺序也值得说。(order_id, amount)这个顺序是正确的order_id 是等值匹配条件放在最前面amount 是聚合取值列跟在后面。如果把 amount 放前面、order_id 放后面就没法按 order_id 快速定位了整个索引就废了。4.4 优化前后对照不止数字的变化三刀之后我做了完整的对比验证指标优化前优化后总耗时30248 秒约 1.9 秒t_order 访问方式ALL 全表扫描range 范围扫描子查询执行次数约 360 万次0 次改成 JOIN明细表聚合方式每次子查询单独聚合全表只扫一次回表次数每次聚合都回表覆盖索引零回表至于标题里的 0.001s我得严谨一点说明1.9 秒是这张月报 SQL 的最终耗时能到 1ms 量级的是优化后衍生出来的热路径查询——比如按 order_no 或 order_id 单点查订单和明细汇总。这类高频查询命中唯一索引和覆盖索引后耗时确实在 0.001 秒左右。很多人会把单条 SQL 极致和批量报表耗时混在一起实际上百万行级的聚合不可能 1ms 跑完所谓 0.001s 永远指的是高频率单点路径。这一点想清楚你才不会在后续优化里被单个数字带偏。5. 当单条 SQL 到了极限换个思路做并行 SQL优化做完之后我一直在想一个问题如果 1.9 秒还不能满足业务呢如果这张报表要覆盖三年数据单条 SQL 再怎么优化也可能到不了理想范围。这时候就得聊到热词里的并行 SQL 优化。5.1 MySQL 为什么不支持并行执行一条 SQL很多从 Oracle 或 PostgreSQL 生态转过来的同学天然以为 MySQL 也能并行跑一条大查询。实际情况是MySQL 社区版一直缺少通用的并行查询执行能力。Oracle 有 Parallel ExecutionPostgreSQL 从 9.6 开始支持并行顺序扫描。MySQL 直到 8.0 才针对 InnoDB 聚簇索引的全表扫描场景典型就是 COUNT 类查询引入了innodb_parallel_read_threads参数默认 4允许 COUNT(*) 在扫描聚簇索引时并行读页。但这跟把一条复杂 JOIN 拆成多个线程同时跑完全是两码事。MySQL 的并行能力目前非常有限遇到超大聚合任务主流做法是业务级分片并行——把一条大 SQL 人为拆成多条小 SQL由多个会话并发执行。5.2 业务级分片并行把一条大 SQL 拆成多条小 SQL举例来说如果我们要重算 2021 年到 2023 年三年的订单日汇总单条 SQL 一次性 GROUP BY 三年数据哪怕索引齐全也要跑很久。正确姿势是按月分片比如 36 个分片每个分片单独跑一个月的聚合然后把结果合并。SHARDS [ (2021-01-01, 2021-02-01), (2021-02-01, 2021-03-01), # ... 共 36 个分片 ] def run_shard(start, end): sql SELECT %s AS ym, order_status, SUM(amount) AS total_amount, COUNT(*) AS order_cnt FROM t_order WHERE create_time %s AND create_time %s GROUP BY order_status # 执行并返回结果 return execute(sql, (start[:7], start, end)) with ThreadPoolExecutor(max_workers8) as pool: results list(pool.map(run_shard, SHARDS))每个分片都是走 create_time 索引的窄范围扫描互不干扰8 个并发连接同时干活理论上能接近 8 倍的吞吐。合并结果时可以直接在 MySQL 里 INSERT 到汇总表也可以拉到应用层做 merge。这种做法的核心是分片边界必须正交、可合并最常见的就是按时间、按主键范围、或者按 user_id 取模。分片之间不能有重叠否则结果会重复写入汇总表前要对目标分区先做幂等清理。5.3 哪些场景值得上并行哪些不值得并行不是银弹用错了反而添乱。我的经验是同时满足以下条件才值得做扫描范围天然可切分。时间字段、自增主键、分区表都行如果找不到一个干净的切分维度硬拆会导致大量数据重叠或笛卡尔积风险。单条 SQL 很重但频率很低。典型是 T1 批处理、离线重算、数据订正任务。这类任务跑 5 分钟还是 5 秒业务体感差异巨大值得投入。数据库连接池和 IO 有冗余。并行数要从 4 开始压测不要一上来就开 32 个连接MySQL 的连接本身要占内存每个会话还有排序和临时表空间连接风暴会把库打挂。反过来业务高峰期的高频小查询绝对不要上并行额外的连接开销和上下文切换只会让平均延迟变差。另外如果你们已经引入了 ClickHouse、StarRocks 或者 TiDB 这类大数据组件分析类 SQL 直接路由过去比在 MySQL 上硬搞并行划算得多。MySQL 做好在线交易这摊事就够了。6. 这次事故留给我的慢 SQL 治理清单最后聊点务实的这条 SQL 救回来了但下一条可能已经在路上了。我跟大家分享一下现在每天在做的几个动作不一定全但每条都是这次事故换来的教训。6.1 日常巡检的四个习惯第一慢查询日志必须开而且阈值要敢设。生产环境long_query_time我建议从 2 秒降到 1 秒一周导一次日志用 pt-query-digest 按总耗时排序。你只关心十分钟跑完的大 SQL更麻烦的是那些每秒跑一次、每次 1.5 秒的高频慢查询它们单看不致命累积起来能把 CPU 吃满。第二做索引评审而不是索引堆砌。很多人一看到慢 SQL 就加索引结果索引比数据还大写入性能被拖垮。我现在的习惯是每季度跑一次 pt-index-usage找出那些建了但从来没用过的冗余索引该删就删。索引不是越多越好是每一张索引都要能在某个执行计划里被真正用到。第三大表变更后用 ANALYZE TABLE 刷新统计信息。MySQL 优化器依赖统计信息估算行数一旦数据量大起大落统计信息陈旧它就可能选错执行计划。尤其是从 3600 万行里突然删掉 2000 万行这种操作必须手动 ANALYZE。第四SQL Review 纳入代码评审。开发提测之前凡是访问大表的 SQL 都要贴 EXPLAINDBA 或资深后端看一眼执行计划再放行。这次事故最根本的原因不是解法多难而是这条 SQL 在几个迭代里都没人认真看过执行计划。6.2 对了不起的 0.001s保持清醒再回到开头那个数字。30248s 到 0.001s听起来很爽但我想给所有做 SQL 优化的朋友提个醒单点查询 1ms 只能代表一条热路径的极致不能代表系统的健康度。我见过不少人把某个查询优化到毫秒级之后就开始吹自己搞定了性能问题结果一个月后同一张表因为别的一条烂 SQL 又崩了。我的习惯是优化完必须验证两个东西。第一用EXPLAIN ANALYZEMySQL 8.0.18看实际执行行数和耗时别只看成本估算第二并发压测同样的查询在 50 个并发下跑 200 次看平均延迟和 p99单次单线程的数字没有任何工程意义。你可以在手边维护一份大查询清单把每条 SQL 的用途、表规模、索引情况、优化前耗时、优化后耗时全都记下来。下次再遇到性能瓶颈先查清单再看慢日志再用执行计划说话。SQL 优化这个事七分靠规范三分靠临场排查能把日常规范做到位大多数性能瓶颈的噩梦根本就不会发生。