慢查询优化诊断器

当查询从 4 秒变成 88 毫秒

在拥有 50 万订单的报表系统里，一个连接三张表的查询能吃掉 4.2 秒的响应时间。更糟的是，Django 列表页每次渲染发出 201 条 SQL，其中 200 条只是重复加载关联对象。这类问题不会在代码评审中暴露，只有当生产环境的实际执行计划摆到面前，你才会看到顺序扫描扫过整张表、索引缺失导致的嵌套循环爆炸、以及 ORM 懒加载在循环里堆出的 N+1 查询。

这个技能解决什么

这个技能的核心是把慢查询诊断变成一套可重复的流程，而不是靠经验猜测。

能力覆盖：

• 解读 EXPLAIN ANALYZE 与 EXPLAIN FORMAT=JSON 的实际执行计划
• 定位顺序扫描、低效 JOIN、排序操作和估算与实际行数的巨大偏差
• 给出具体的索引策略：复合索引、覆盖索引，以及避免不必要的表回查
• 重写子查询为 JOIN，消除 SELECT * 带来的 I/O 浪费
• 识别 ORM 层面的 N+1 模式，并建议改用 select_related、joinedload 或 include 等预加载方式
• 在修改后重新运行计划对比，提供可测量的改进证据

工作流程：

1. 收集慢查询来源：从慢查询日志、APM 工具或用户反馈中提取问题查询，记录当前执行时间、涉及表规模和调用频率，优先处理高频慢查询。
2. 分析执行计划：对 PostgreSQL 运行 EXPLAIN ANALYZE，对 MySQL 运行 EXPLAIN FORMAT=JSON，寻找全表扫描、高行数估计的嵌套循环、未索引列排序，以及估算行数与实际行数之间的巨大差距。
3. 确定优化方案：基于计划给出可执行的修改——为 WHERE、JOIN、ORDER BY 涉及的列创建索引，把子查询改写成 JOIN，用具体列替换 SELECT *，添加 LIMIT，利用覆盖索引避免回表，清理冗余条件。
4. 逐一应用并测量：每次只改一处，分别创建索引、重写查询或调整 ORM 用法，这样可以独立量化每个改动带来的收益。
5. 验证结果：重新运行 EXPLAIN ANALYZE，对比执行时间、扫描行数和计划结构，确认查询返回结果不变，并检查新索引是否让写入性能下降到不可接受。
6. 搭建持续监控：配置 log_min_duration_statement（如 PostgreSQL 100ms 阈值），集成 pg_stat_statements、Datadog 或 Grafana 跟踪查询性能趋势，尽早发现回归。

适用边界与注意点

这套方法依赖数据库自身的执行计划信息，要求数据库账号具备运行 EXPLAIN ANALYZE 和查询系统统计视图的权限。对于已经做过极致优化的系统，索引数量本身可能成为写入负担，因此每次新增索引后都需要在写密集场景下做基准测试。

容易踩的坑：

• 统计信息漂移：大量数据导入后，查询规划器仍使用过期统计，需要手动 ANALYZE 或 ANALYZE TABLE。
• 索引膨胀：高变更表上频繁 UPDATE/DELETE 会让索引体积膨胀，需定期 REINDEX 或 OPTIMIZE TABLE。
• 视图里藏着关联子查询：看起来简单的查询可能引用包含逐行执行的视图，分析时务必展开视图查看完整计划。
• 计划缓存与参数嗅探：某个参数值的缓存计划对另一个参数值可能很差，PostgreSQL 中可用 PREPARE/EXECUTE 或调整 plan_cache_mode 来缓解。
• ORM 生成多余 JOIN：有些 ORM 会把 INNER JOIN 生成为 LEFT JOIN，或添加不必要的子查询，需要用 QuerySet.query 或 .toSQL() 检查实际 SQL 并在必要时改用原生查询。

最终，这个技能给出的不是万能调优配方，而是一套以执行计划为依据、以前后对比为验证手段的工程化诊断流程。它适合那些已经出现明确性能瓶颈、愿意用数据而不是直觉来驱动优化决策的团队。