上个月接了公司一个报表接口的排查任务用户反馈查询从原来的 1 秒多一路涨到了 8 秒多页面都快要超时了。一群人围着这个接口看了半天代码最后定位到问题就是一条 SQL 的执行计划走了全表扫描另外还叠加了深分页和一个优化器“选错路”的子查询。最终通过几轮优化把同一个查询从 8.3 秒压到了 400ms 左右差不多是 20 倍的提升。这篇文章不写虚的直接拆解我完整做一次 SQL 优化 的路径怎么定位慢 SQL、怎么设计索引、哪些 SQL 写法需要动手改写、什么时候才需要上并行和架构级手段以及大家经常误会的“视图加速”问题。如果你平时被慢查询折磨过或者刚接手一个埋了雷的老库这篇应该能帮你省下不少时间。1. 慢SQL没定位到根因之前先不要急着改SQL很多同学一看到慢查询第一反应就是“加索引”。这种直觉不完全是错的但如果你没搞清楚这条 SQL 到底慢在哪一层加索引很可能白加甚至把写入拖慢。我接手这个报表接口后做的第一件事不是改代码而是把整条 SQL 的执行链路完整扒了一遍。1.1 慢查询日志先帮我圈定嫌疑对象建议先把目标范围内的 SQL 记录下来。MySQL 的慢查询日志是第一步配置方式很简单-- 开启慢查询日志阈值设置 1 秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 输出到表方便直接 SQL 查询 SET GLOBAL log_output TABLE;日志落地之后可以用内置工具快速排出 TOP 10mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log这里有个细节需要注意log_queries_not_using_indexes可以记录所有没走索引的查询这选项在低峰期试一两天很有价值但长开的话日志量和检索压力都不小生产环境不建议常开。我用的是输出到mysql.slow_log表的方式方便用 SQL 跑统计SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT(*), AVG(QUERY_TIME) FROM slow_log WHERE start_time NOW() - INTERVAL 2 DAY GROUP BY SCHEMA_NAME, DIGEST_TEXT ORDER BY AVG(QUERY_TIME) DESC LIMIT 20;这样能很快把“高耗时又高频率”的语句挑出来。记住优化要优先做高频且耗时长的那批不要拿一个跑了十分钟但每天只跑一次的冷查询当重点。PostgreSQL 和 SQL Server 也有类似的配置PG 用log_min_duration_statementSQL Server 用 Query Store思路一致先圈定目标再动手。1.2 EXPLAIN 中的每一个关键列都要看懂拿到嫌疑 SQL 后下一步就是EXPLAIN。不加任何修饰地执行EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status PAID;重点看type、key、rows、Extra这几列。type从好到差大致是system const eq_ref ref range index ALL如果看到ALL基本说明是全表扫描需要重点观察。rows是优化器预估的扫描行数不是精确值但数量级很有参考价值。Extra里出现Using filesort、Using temporary通常是需要优化的信号而Using index表示查询在索引中就能拿到所需数据不需要回表。有一次我遇到一条 SQLtype显示index看着“走了索引”其实还是在扫描整个索引。因为index类型的意思是遍历二级索引的所有叶子节点相比range这种范围查找代价依然不小。所以不要光看“有没有用到索引”更要看用的是什么访问方式。1.3 确认瓶颈层级CPU、IO、还是锁等待执行计划看着还行但查询就是慢这时候要考虑瓶颈可能根本不在 SQL 本身。常见有三类服务器资源不够CPU 或 IO 达到瓶颈、大量数据在应用层和数据库之间传输网络带宽、以及锁等待冲突。查看锁等待可以用SELECT * FROM sys.innodb_lock_waits;数据库层 IO 压力可以通过performance_schema里的table_io_waits_summary_by_table定位。如果一条 SQL 走了索引但每次执行都在磁盘上随机读大量页那光改 SQL 不一定有用可能要检查缓冲池命中率或者干脆把查询改成覆盖索引。我的经验是先看EXPLAIN再看锁等待和 IO 指标。排除了执行计划、锁、资源这三层才能踏实地把问题归到“SQL 写法或索引设计”上。2. 索引设计从覆盖到组合把索引效率压榨到极致定位到 SQL 本身有问题之后索引往往是性价比最高的解法。但索引不是“建了就完了”怎么建、怎么组合、怎么避免失效这里面的细节能直接决定你是提升了 10 倍还是白忙一场。2.1 覆盖索引为什么在报表查询中特别好用先说明白回表是怎么回事。普通二级索引的叶子节点存储的是索引列和主键值如果查询需要的其他列不在索引里就需要根据主键值再去聚簇索引查一次完整行这就是回表。每次回表都是一次随机 IO行数多的时候代价非常明显。我处理的这个报表查询原本是这样的SELECT order_id, user_id, status, amount FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY created_at LIMIT 50;原始执行计划用了idx_created_attyperange但是Extra有Using filesort因为排序字段和索引顺序虽然一致但查询还需要回表取amount等字段。后来把索引改成ALTER TABLE orders ADD INDEX idx_cover_pick (created_at, status, amount);执行计划立刻变成了Using indexUsing filesort也消失了。查询从原来的 1.2 秒左右降到了 80ms 左右接近 15 倍。这里的核心逻辑就是所有需要的列都能在索引里找到不需要回表也不需要额外的排序文件。覆盖索引在报表场景里几乎是必杀技但要注意别盲目把一堆列塞进索引。索引也是数据写入了需要维护索引列越多存储成本越高、写入越慢。建议只覆盖高频查询里真正用到的列。2.2 组合索引的列序选择区分度、等值、排序组合索引最怕的是列顺序不对明明建了索引却不生效或者生效了也只用到一部分列。一个常用的决策原则是等值条件的列放前面按区分度从高到低排列范围条件、、BETWEEN的列放到最后排序字段尽量和索引顺序对齐区分度可以用一个简单公式评估SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;越接近 1说明这一列取值越分散越适合放在组合索引的前面。比如status字段如果只有 3 个值而且大多数订单都是PAID区分度就很低把它放第一个很容易浪费索引空间。而created_at几乎每条数据都不一样区分度高更适合靠前。当然实际业务里如果某个查询固定以status为等值条件而且这个值本身过滤性足够好比如只查REFUNDED这种稀有状态那么把status放前面也没错。所以不要死记口诀要结合数据分布去看。2.3 导致索引失效的五个常见情形这块是经验教训的重灾区。我列了五个我在实际项目里踩过或者排查过的常见场景对索引列使用函数WHERE DATE(created_at) 2024-01-01会导致索引失效。优化方式是改成created_at 2024-01-01 AND created_at 2024-01-02。隐式类型转换比如user_id字段是VARCHAR查询条件写成WHERE user_id 123时数据库会把字段类型统一后做转换索引失效。前置通配符 LIKELIKE %keyword%无法利用索引只能考虑全文索引或者外置搜索引擎。OR 连接非索引列比如WHERE status PAID OR refund_reason IS NOT NULL如果有一个列没索引优化器可能直接放弃索引走全表扫描。组合索引中范围列之后的列失效这是最隐蔽的坑。索引(created_at, status, amount)中如果查询用created_at ... AND status PAID那么status只能在索引内做过滤无法参与后续的索引定位你以为是二级索引精确查找其实是在大范围上做了额外过滤。理解这些失效场景基本就能避免 80% 的“建了索引但还是慢”问题。3. 从改写SQL入手让优化器选对执行路径索引优化解决的是“有没有路可走”的问题但有时候路是有的优化器偏偏选了最绕的那一条。这时候就要从 SQL 写法上动手引导优化器走更优路径。3.1 子查询改写为 JOIN为什么有时候有效有时候没用常见的慢查询场景是IN (SELECT ...)子查询。旧版本 MySQL 对这类子查询的优化方式不够好外层每一行都可能触发一次子查询执行代价很高。改写前是这样的SELECT o.* FROM orders o WHERE o.customer_id IN ( SELECT c.id FROM customers c WHERE c.vip_level 3 );改写成 JOINSELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id c.id WHERE c.vip_level 3;JOIN 版本允许优化器先做过滤再和订单表做连接往往快得多。但注意并不是所有子查询都适合改成 JOIN。如果子查询里涉及去重或者右表是“一对多”关系改完 JOIN 可能导致数据翻倍。MySQL 8.0 对子查询的物化处理已经明显进步所以我的建议是改写前先 EXPLAIN看当前执行计划是不是真的差再决定要不要动。3.2 深分页优化延迟关联和键集分页很多报表系统的分页查询越翻越慢典型写法是SELECT * FROM orders WHERE status PAID ORDER BY created_at LIMIT 100000, 20;这条 SQL 即使完全走了索引执行时也需要先扫描前 100020 行再丢弃前 10 万行。数据量越大偏移量越大消耗越夸张。第一种改进是用延迟关联SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY created_at LIMIT 100000, 20 ) t ON o.id t.id;内层子查询只查id不需要回表扫描成本大幅下降然后拿这 20 个id再回原表取完整数据。第二种更彻底的改法是改成“键集分页”也就是前端记录上一页的最后一条排序值SELECT * FROM orders WHERE status PAID AND (created_at, id) (2024-01-01 10:00:00, 1000) ORDER BY created_at, id LIMIT 20;这个写法直接利用了索引定位扫描行数基本等于返回行数属于真正的 O(1) 级别优化。需要注意的是排序字段必须唯一或者组合一个唯一字段否则下一页可能漏行。3.3 其他常见的 SQL 改写优化点这里分享几个能快速见效的习惯性改写OR 改写为 UNION ALL如果两边都希望走索引拆分后各自走索引再合并通常比全表扫描强很多。除非业务确实需要去重否则用UNION ALL而不是UNION后者会多一次排序去重。避免在 WHERE 里对字段做计算WHERE price * 0.9 100改成WHERE price 111.11不光是索引问题也减少了每行的计算量。明确列名而不是 SELECT *这不仅减少网络传输更重要的是给覆盖索引创造了机会。大表 JOIN 前先缩小数据比如先按条件把订单表缩到一个小结果集再去 JOIN 其他表避免临时表膨胀。这些改写不需要很高深的技术但对执行路径的影响很大。不过要再三提醒每一条改完都要用 EXPLAIN 去验证不要想当然。4. 当单条SQL已经到极限并行和拆分才是下一个杠杆有些 SQL 在单条语句层面已经改到极限了索引也建到位了写法也优化过了但还是满足不了业务时序要求。此时就需要跳出这条语句从执行引擎和架构层面去借力。热搜里提到的“并行 SQL 优化”就是这个方向的典型话题。4.1 并行 SQL 优化的适用场景与成本并行查询是让一条 SQL 利用多个 CPU 核心协同处理。PostgreSQL 和 Oracle 的并行执行能力比较成熟MySQL 传统 OLTP 场景在这块并不算强项大家不要指望 MySQL 一条复杂查询会自动拆到多个 CPU 上跑出翻倍效果。并行适合大表扫描、大聚合、大排序这类分析型场景。比如一张千万级订单表做月度汇总单线程跑要好几秒如果让四个 worker 并行扫描四个分区理想情况下能压缩到原来的四分之一。以 PostgreSQL 为例可以针对某张表设置并行度ALTER TABLE orders SET (parallel_workers 4);但这里有个很容易踩的坑并行度并不是越高越好。每个并行 worker 都会消耗 CPU 和内存如果数据库同时还有很多小查询在跑并行大查询反而会抢占资源拖垮整体 RT。实际经验是并行参数要在业务低峰期做压测找到吞吐量和单查询耗时之间的平衡点不要盲目调到 8 或 16。我们曾经把一个分析查询从并行 4 调到并行 8单查询缩短了 20%但同一时段的小查询整体慢了近两倍最后又调了回来。4.2 读写分离和数据拆分对慢 SQL 的缓解如果慢 SQL 主要集中在报表、统计这类读场景而且业务允许最终一致性读写分离是很实用的手段。把主库的复杂查询挪到只读从库执行主库专心处理写入和高频小查询整体压力立刻降一档。需要注意的是主从延迟。以前我遇到过搜索列表查询刚写入的数据出现在报表里少了几条原因就是统计/报表跑在从库上而业务写入后立刻查主库两边数据不一致。解决方式是实时性要求高的查询走主库可容忍分钟级延迟的统计任务走从库。这个原则一定要在架构上确认清楚。如果单表数据量已经到亿级即使索引合理B 树的层级和叶子节点数量也会让 IO 开销变大这时候可以考虑分区或者分表。时间维度业务用分区表很自然比如按月份分区查询只扫描一个分区性能提升明显。但要注意分表后跨分片排序、聚合、分页都会变复杂不要还没把 SQL 和索引优化完就急着拆表否则问题只是从 SQL 转移到了中间层。4.3 缓存层的加入时机与粒度缓存是解决慢查询的最后一张牌不是第一张。我见过不少团队一遇到慢查询就上 Redis结果缓存穿透、数据不一致、缓存雪崩问题比原来还多。适合缓存的是实时性要求不高、访问频率极高的热点数据。比如统计报表的汇总结果半小时更新一次完全能接受或者某个热点商品的库存状态可以缓存几十秒。粒度和失效策略要设计好缓存“统计结果”时可以用 TTL 兜底同时每天或每小时主动刷新一次缓存“单行热点”时可以考虑写操作后主动删除缓存。如果你发现业务语义本身就要求“强实时”那缓存这条路基本走不通别硬上。缓存是为了把“多次重复的高代价查询”变成“一次查询加内存读取”它不能解决 SQL 本身低效的问题。底层的索引和 SQL 改写仍然要先做好。5. 视图加速查询的误会和慢SQL优化真正该有的顺序最后想专门花一章聊聊“视图能不能加快查询速度”这个话题。因为我在网上看到太多人把它当成优化手段但实际上大部分情况下这是一个误区。5.1 视图只是封装不是加速器视图本质是“虚拟表”它不存储数据。你在 MySQL 里创建一个视图查询这个视图时数据库会把它展开成底层的 SELECT 语句再执行。也就是说视图不会改变底层 SQL 的访问路径也不会自动添加索引创建视图本身不会让查询变快。某个查询慢你把它包在视图里查询视图还是慢这就好比你换了个快递箱包装包裹不会因此飞得更快。我甚至见过视图嵌套视图的情况三层嵌套之后数据库不得不生成临时表来缓存中间结果性能反而更差。那视图有没有正面价值有但主要体现在“开发效率和权限控制”上统一封装常用 JOIN避免业务侧每个同学写出来的关联查询各不相同或者让应用层只面向视图底层表结构变化时不影响接口层面。这些优化的是人效和维护成本不是查询速度。如果你的目标是物化后的结果那要看数据库是否支持真正会存储数据的物化视图比如 PostgreSQL 的MATERIALIZED VIEW、SQL Server 的索引视图这类视图需要定期刷新同时要付出存储和刷新成本使用场景是少数统计需求不是默认方案。5.2 别忽略统计信息过期导致执行计划劣化还有一种“慢 SQL”特别容易迷惑人SQL 本身没改表数据量也没暴增但查询突然变慢了。这种事我排查过好几次最后的根因往往是统计信息过期优化器基于陈旧的数据分布选了一个差执行计划。最简单直接的操作是主动更新统计信息ANALYZE TABLE orders;PostgreSQL 里的对应操作是ANALYZESQL Server 是UPDATE STATISTICS。更新完统计信息再看执行计划很多“突然变慢”的查询会自动恢复正常。MySQL 8.0 已经移除了 Query Cache所以不要想着靠查询缓存来兜住慢查询。真正可靠的执行计划稳定手段是让统计信息保持新鲜必要时可以用 hint 固定索引。比如SELECT /* INDEX(orders idx_cover_pick) */ ... FROM orders WHERE ...但 hint 是最后手段因为表结构一变hint 可能变成负优化。我的原则是先 ANALYZE再看执行计划真的走错路才考虑 hint。5.3 给普通团队的慢 SQL 治理清单如果你们团队还没有一套系统的慢 SQL 治理流程下面这个顺序可以直接抄作业打开慢查询日志采集 1 到 2 天的数据导出 TOP 20按“执行频率 × 单次扫描行数”排序别只关注单次耗时。对每条慢 SQL 做 EXPLAIN标记执行方式、扫描行数、Extra 指标。第一优先做索引优化优先用覆盖索引解决回表问题。第二优先做 SQL 改写处理子查询、深分页、OR 拼接等。仍然慢的少数 SQL再评估并行、读写分离、分区、缓存等手段。每次优化前后把执行计划、耗时留存对比防止未来回归。这套流程不需要 DBA 专家也能跑起来关键是养成“先定位再优化、改完必验证”的习惯。最后分享一个我自己的工作习惯。我处理完这条报表慢 SQL 后会把所有优化前后的 SQL、EXPLAIN 结果和执行耗时整理成一份文档。几个月后表数据量涨了几个量级同一个接口再次出现性能波动我翻出文档两相对比十分钟就定位到是统计信息过期导致索引被弃用。如果你手头也有一条压了很久的慢 SQL别急着背“加索引”口诀按这条路径走一遍大概率能收获非常直观的提速效果。