data-sql-optimization

SQL 优化

不在范围内： OLAP 引擎和湖仓调优。对于 ClickHouse、DuckDB、Doris、StarRocks、Iceberg、Delta Lake 或 Hudi，请使用 data-lake-platform。

快速参考

脚本

脚本	功能	用法
scripts/pg_slow_query_triage.sql	基于 pg_stat_statements 生成包含六个部分的排查报告：总耗时、平均耗时、I/O、方差、缓存命中率，以及溢写/规划/WAL	复制并粘贴到 psql 或任意 SQL 客户端中；需要 pg_stat_statements 扩展
scripts/explain_collector.py	通过 psql 收集估算的 JSON 执行计划；使用 --analyze 可选择执行查询	DATABASE_URL=postgresql://... python explain_collector.py --queries slow.txt
scripts/test_explain_collector.py	离线输入、执行模式和 JSON 封装回归测试	python scripts/test_explain_collector.py

# Triage: paste directly into psql
psql "$DATABASE_URL" -f skills/universal/data-sql-optimization/scripts/pg_slow_query_triage.sql

# Collect estimated plans for reviewed queries (default):
python scripts/explain_collector.py --queries queries.txt --no-analyze --output plans.jsonl

# Collect actual plans (executes queries — review side effects first):
DATABASE_URL=postgresql://user:pass@test-db:5432/db \
  python scripts/explain_collector.py --queries queries.txt --analyze --output plans.jsonl

需求	从这里开始	适用场景
慢查询排查	template-slow-query.md	修改任何内容之前，需要先安全地收集信息
执行计划审查	references/explain-analysis.md	已有 EXPLAIN、EXPLAIN ANALYZE、Query Store 或 Performance Schema 证据
索引设计或删除索引	references/index-patterns.md	正在考虑添加、调整、设为不可见或删除索引
查询重写	references/query-tuning-patterns.md	查询结构或估算问题可能是瓶颈
连接饱和	references/connection-pooling-patterns.md	涉及应用连接池、PgBouncer、RDS Proxy、Supavisor 或 Cloud SQL 连接池
监控和告警	references/monitoring-alerting-patterns.md	需要数据库性能仪表板、基线或告警
锁 / 死锁	template-lock-analysis.md	问题是阻塞、死锁或长事务，而非查询本身的开销
分区	references/partition-strategies.md	保留策略、分区裁剪或表增长是变更原因
备份和恢复设计	references/recovery-strategy-design.md	需要根据故障场景制定恢复能力，而不只是备份任务
安全或 RLS 审查	template-security-audit.md	正在审查最小权限、SQL 注入防护或租户隔离

覆盖范围模型

引擎	状态	说明
PostgreSQL	主要	覆盖最深入。此处使用的版本门控功能（跳过扫描、AIO、uuidv7()、在 pg_upgrade 期间保留统计信息）于 PostgreSQL 18 中推出
MySQL	主要	生产环境建议使用 LTS 版本线，而非 Innovation 版本。依赖 HyperGraph 优化器生成的计划之前，请先确认它在用户所用版本中是否默认启用
SQL Server	主要	对计划回归使用 Query Store；IQP 功能有不同的发布和兼容性前提条件。推荐具体功能之前，请查看 Microsoft 功能矩阵
Oracle	次要	针对优化器特有的边缘情况，使用模板和官方文档
SQLite	次要	重点关注索引、规划器行为、WAL 和 PRAGMA optimize

版本查询：首先通过 SELECT version()（或 @@version）获取用户的准确版本；当前次要版本见 data/versions.json（postgresql、mysql），该文件由脚本更新。有关支持哪些主版本或 LTS 版本线，以及各版本何时结束生命周期，请查阅供应商的版本管理或支持政策页面；应在当前使用的版本线达到 EOL 之前规划升级。推荐使用较新主版本的功能之前，请在发行说明中确认该主版本已正式发布（GA）；不要推荐仅适用于测试版的功能用于生产环境。

首次响应检查清单

推荐更改之前，请收集：

1. 数据库引擎和准确版本
2. 查询文本或工作负载形态
3. 相关架构、索引和估算行数
4. 实际证据：执行计划输出、等待统计信息、查询统计信息或错误文本
5. 最近的更改：架构、配置、部署、流量激增或数据倾斜
6. 并发情况：应用连接池、服务端连接池或副本拓扑
7. 成功指标：p95 延迟、CPU、读取量、锁定时间、错误率或连接数

如果缺少其中任何一项，请先索取相关信息，或使用信息收集模板，再建议生产环境更改。

由 EXPLAIN 驱动的诊断检查清单

从估算计划开始。只有在检查语句、函数和触发器后，才捕获实际执行计划：ANALYZE 会执行语句，而回滚无法撤销序列变更或外部影响。收集器要求每条语句占一行，并会拒绝内部包含分号的语句，包括字符串字面量或注释中的分号。

-- PostgreSQL: estimated plan first; opt into ANALYZE for reviewed execution
EXPLAIN (VERBOSE, FORMAT TEXT) <query>;

-- MySQL: get JSON plan for detailed cost breakdown
EXPLAIN FORMAT=JSON <query>;

-- SQL Server: turn on I/O and CPU evidence
SET STATISTICS IO, TIME ON;
<query>;

步骤	检查内容	危险信号
1	运算符耗时和循环次数	父级耗时包含子级工作；不要将它们相加，也不要把成本单位当作毫秒
2	估算行数与实际行数	任一方向的比率 >10x
3	循环次数 * 每次循环的行数 = 处理的总行数	即使单次循环看起来成本低，总量仍可能很高
4	共享缓冲区命中数与读取数（PostgreSQL）	热门查询的 reads 远大于 hits
5	排序或哈希溢出	Sort Method: external merge、Hash Batches > 1
6	热点路径上的键/书签查找	每个父行都要查找多次；添加 INCLUDE 列
7	嵌套循环的外层行数 × 内层工作量	选择连接策略前，检查估算结果和内层重复扫描
8	等待时间远大于执行时间	排查锁或连接池饱和，而非执行计划

瓶颈决策表：

执行计划显示	可能原因	首选措施
顺序扫描，读取行数/返回行数很高	缺少/不可用的索引，或这是合理的扫描选择	检查选择性和可搜索性；比较索引试验结果
索引扫描，但循环次数很高	N+1 或连接顺序不佳	批量处理或修正估算
实际行数远大于估算行数	统计信息过时/不足	ANALYZE；CREATE STATISTICS（PG）；直方图（MySQL）
执行计划随参数变化	参数敏感性	Query Store / OPPO（SQL Server）；拆分查询形态
排序溢出	投影过宽；缺少顺序匹配的索引	缩小投影范围；添加覆盖索引
执行计划成本低，但实际耗时长	等待：锁、I/O、连接池	检查 pg_stat_activity、等待事件、连接池统计信息

工作流

1. 在建议变更前，确认引擎、工作负载、症状和可用证据。
2. 如果 SQL 调优并非主要问题，则将搜索、数据湖仓、后端架构或高度依赖可观测性的工作转交给相邻技能。
3. 提出索引、重写或配置变更前，先收集执行计划、统计信息和工作负载上下文。
4. 每次只调整一个因素，并在每一步之后验证正确性和性能影响。
5. 最终给出建议前，使用导航参考重新检查与版本相关的行为。

生产变更准入门槛

根据要实施的变更选择验证方法和回滚方案：

变更	试验	回滚触发条件	回滚
查询重写	重放有代表性的参数和并发负载；比较结果集	返回错误行，或 p95/读取数/锁的表现变差	还原查询或关闭功能标志
新索引	在可用时通过引擎的在线/并发路径创建；确认执行计划已采用	写入延迟、锁定时间或存储空间超出预算	检查依赖项后，通过安全的在线路径删除
统计信息变更	捕获不同偏斜值下变更前后的执行计划	其他参数类别出现性能回退	恢复目标值/统计信息设置并执行 analyze
连接池/配置变更	对一个服务或连接池进行金丝雀发布；监控等待和饱和情况	排队、超时或连接抖动增多	恢复先前值，并仅重建受影响的连接池
分区/架构变更	使用符合生产特征的数据进行演练，并验证双读/双写	行数不匹配、写入方受阻或复制延迟	停止切换，将流量恢复到旧路径

不要仅凭一次预热缓存后的执行就宣称调优成功。在相同的工作负载时间窗口内，记录正确性、p50/p95/p99、逻辑/物理读取数、CPU、锁和写入影响；注明任何无法测量的指标。

路由指南

如果问题是查询缓慢

• 从 template-slow-query.md 开始
• 然后使用：
  • PostgreSQL：template-pg-explain.md
  • MySQL：template-mysql-explain.md
  • SQL Server：template-mssql-explain.md
  • Oracle：template-oracle-explain.md

如果问题可能与基数或估算器偏差有关

• PostgreSQL：查看 references/operational-patterns.md，了解 CREATE STATISTICS、pg_upgrade 统计信息保留和 PG18 规划器变更
• MySQL：使用 references/operational-patterns.md 中的直方图和优化器统计信息
• SQL Server：检查 template-mssql-explain.md 中的 Query Store、参数敏感性和 OPPO

如果问题与索引设计有关

• 从 references/index-patterns.md 开始
• 使用厂商模板：
  • PostgreSQL：template-pg-index.md
  • MySQL：template-mysql-index.md
  • SQL Server：template-mssql-index.md

如果问题是阻塞或锁等待

• 使用 template-lock-analysis.md
• 优先通过调整事务结构解决问题，再考虑更改配置

如果问题是连接压力

• 使用 references/connection-pooling-patterns.md
• 区分连接辅助工具和实际的连接池。Cloud SQL Auth Proxy 和语言连接器本身都不是连接池。

如果请求涉及 PostgreSQL 租户隔离或权限审查

• 使用 template-pg-rls.md
• 搭配 template-security-audit.md

导航与模板

仅在记录相应决策时，加载跨数据库引擎工作表：

决策	模板
多症状事件诊断	诊断
重写与结果等价性审查	查询调优
新建架构或完整性审查	架构设计
记录基线、实验和验证	调优工作表
记录跨数据库引擎的执行计划审查	EXPLAIN 分析
记录索引试验及其写入影响	索引设计
演练架构发布与回滚	迁移
演练恢复并验证已恢复的数据	备份/还原
规划复制拓扑、延迟和故障转移	MySQL 复制/HA、PostgreSQL 复制/HA
调优嵌入式 SQLite 工作负载	SQLite 优化

参考指南 — 按需加载：

• references/explain-analysis.md — 阅读 EXPLAIN/EXPLAIN ANALYZE 输出时加载：行数估算、连接顺序、内存溢出，以及各数据库引擎的采集命令。
• references/index-patterns.md — 决定是否添加、调整或弃用索引时加载；涵盖复合、部分、覆盖、BRIN、不可见索引和 PG18 跳跃扫描。
• references/query-tuning-patterns.md — 可能需要修改 SQL 本身来修复问题时加载：可搜索性、OR 重写、键集分页、N+1 合并和估算修正。
• references/sql-best-practices.md — 获取基于工作负载的调优默认值和安全变更工作流时加载；适合在进行生产环境变更前参考。
• references/sql-antipatterns.md — 在架构或查询代码审查期间加载，以发现并处理常见反模式（SELECT *、N+1、EAV、不可搜索谓词）。
• references/query-optimization-research-runtime.md — 建议依赖特定版本的数据库引擎行为时加载（PG18 AIO、MySQL HyperGraph、SQL Server 2025 IQP、LITHE 重写研究）。
• references/partition-strategies.md — 表增长、数据保留或清理压力促使考虑分区时加载；包含迁移模式和 pg_partman 指南。
• references/connection-pooling-patterns.md — 问题表现为连接饱和、连接池配置错误，或需要选择云托管连接池（PgBouncer、RDS Proxy、Supavisor、Cloud SQL）时加载。
• references/monitoring-alerting-patterns.md — 为 PostgreSQL、MySQL 或 SQL Server 设置查询统计、等待事件监控或警报阈值时加载。
• references/operational-patterns.md — 生产环境调优工作流、安全迁移检查清单、数据库引擎特定的运维注意事项、work_mem 容量规划、事务空闲时的锁级联，以及在线架构变更工具（gh-ost 与 pt-osc）相关内容。
• references/recovery-strategy-design.md — 设计或审查备份与恢复方案时加载：故障场景分类、各故障类别的检测方式、存储分层，以及作为交付成果的恢复测试。

主要来源位于 data/sources.json。

已知陷阱

• 顺序扫描、哈希连接、物化、子查询和 CTE 都可能是正确的选择。重写前应先要求提供执行计划证据。
• 每增加一个索引，都会增加写入和维护工作。检查是否存在重叠索引，并在衡量读取收益的同时测量写入影响。
• 开发数据会掩盖生产环境中的数据倾斜和租户热点；应在并发条件下比较具有代表性的参数。
• 查询、架构和统计信息检查完毕后，可将规划器提示和会话参数作为诊断实验；不要在不同引擎之间照搬提示。

相关技能

• software-backend - 应用查询生成、ORM 行为以及 API/数据库交互
• software-security-appsec - SQL 注入防护、身份验证、机密信息和加固
• ops-devops-platform - 基础设施、故障转移自动化和运维手册
• qa-observability - 遥测、告警和 SLO 设计
• qa-debugging - 生产事故调试工作流
• data-lake-platform - OLAP 引擎、Parquet 以及数据仓库/湖仓调优

经验总结循环

如果先前的决策或陷阱与当前情况相关，请查阅 learnings.consolidated.md（如果存在）；只有在需要了解历史记录或将其作为可用的备用来源时，才使用 learnings.md。否则跳过两者。

应用经验后，如果遇到了值得记住的模式、值得避免的错误，或令你意外的领域事实，请通过 agents-skills-feedback-loop/scripts/append_learning.py 向 learnings.md 追加一条带日期的条目。不要修改 SKILL.md 本身。