sql-optimizer

SQL 优化器

系统地分析 SQL 性能：解析查询结构、解读 EXPLAIN 计划、检测反模式（N+1、全表扫描、笛卡尔连接）、推荐索引并重写查询——说明每项更改为何能提升性能，而不只是说明改了什么。

参考文件

文件	内容	何时加载
references/anti-patterns.md	常见 SQL 反模式及其检测规则和修复方法	始终
references/index-strategies.md	索引类型选择、复合索引排序、覆盖索引	需要索引建议时
references/explain-guide.md	解读 PostgreSQL、MySQL、SQLite 的 EXPLAIN 输出	提供了 EXPLAIN 计划时
references/join-optimization.md	连接类型选择、连接顺序优化、子查询转连接	查询包含连接或子查询时

前置条件

• 要优化的 SQL 查询
• 数据库引擎（PostgreSQL、MySQL、SQLite）——优化方式因引擎而异
• 表结构和大致行数（有帮助，但不是必需的）
• EXPLAIN 输出（如果有，价值很高）

工作流

阶段 1：查询分析

解析 SQL，理解其结构：

1. 识别操作——SELECT 列、FROM 表、JOIN 条件、WHERE 过滤条件、 GROUP BY、ORDER BY、HAVING、子查询。
2. 梳理表关系——连接了哪些表？使用什么键？是否存在 隐式笛卡尔积？
3. 检测明显问题：
   • SELECT *——获取了不必要的列
   • 在 WHERE 中对索引列使用函数——导致索引无法使用
   • WHERE 中使用 OR——通常会导致索引无法使用
   • 相关子查询——可能造成 N+1
   • DELETE/UPDATE 缺少 WHERE——很危险

阶段 2：解读 EXPLAIN

如果提供了 EXPLAIN 计划：

1. 扫描类型——顺序扫描（大表上性能差）、索引扫描（好）、 仅索引扫描（最佳）、位图索引扫描（可接受）。
2. 连接方法——嵌套循环（适合小表）、哈希连接（适合 等值连接）、归并连接（适合已排序的数据）。
3. 行数估算——比较估算行数与实际行数。差异较大 表明统计信息已过时（ANALYZE）。
4. 成本热点——成本最高的节点是瓶颈。优先优化该处。
5. 排序操作——外部排序（磁盘排序）成本很高。可考虑创建与 ORDER BY 匹配的索引。

阶段 3：检测反模式

检查已知的性能反模式（参见 references/anti-patterns.md）：

模式	检测方式	影响
SELECT *	选择列表中包含星号	传输不必要的数据
N+1 查询	循环内执行查询	增加 N 次往返
在索引列上使用函数	WHERE UPPER(name) = 'X'	绕过索引
隐式类型转换	字符串与整数比较	绕过索引
缺少连接条件	笛卡尔积	行数呈指数级增长
LIKE '%prefix'	前置通配符	全表扫描
对不同列使用 OR	WHERE a=1 OR b=2	绕过索引
将 SELECT DISTINCT 当作权宜之计	隐藏了产生重复行的连接	应修复连接，而非如此处理

阶段 4：优化

1. 索引建议——根据 WHERE、JOIN、ORDER BY、GROUP BY 列提出建议。 对多列条件考虑使用复合索引。
2. 重写查询——将相关子查询转换为 JOIN，将 IN (SELECT...) 替换为 EXISTS；使用 CTE 提高可读性，且不影响性能（PostgreSQL 12+ 可能会内联 CTE）。
3. 架构建议——反规范化、物化视图、分区 （仅在查询级优化不足时提及）。

阶段 5：输出

展示原始查询、检测到的问题、推荐的索引、重写后的查询， 并解释每项更改。

输出格式

## SQL Optimization Analysis

### Original Query
```sql
{original SQL}
```

### Issues Detected

| #   | Issue   | Severity          | Location            | Impact           |
| --- | ------- | ----------------- | ------------------- | ---------------- |
| 1   | {issue} | {High/Medium/Low} | {WHERE/JOIN/SELECT} | {what it causes} |

### EXPLAIN Interpretation

{If EXPLAIN provided}

- **Bottleneck:** {node type} on `{table}` (cost: {N})
- **Rows scanned:** {N} (estimated {M})
- **Index used:** {name or "None"}
- **Key insight:** {what this reveals}

### Recommended Indexes

```sql
-- {Reason for this index}
CREATE INDEX {name} ON {table}({columns});
```

### Optimized Query

```sql
{rewritten query}
```

### Change Explanation

1. **{Change}** — {Why this improves performance. Include estimated impact.}

### Expected Improvement

- Scan type: {before} → {after}
- Estimated rows scanned: {before} → {after}
- Index usage: {before} → {after}

配置范围

模式	输入	深度	适用场景
quick	单条查询	反模式扫描 + 索引建议	开发期间快速获取反馈
standard	查询 + 架构	完整分析并重写	优化请求的默认模式
deep	查询 + EXPLAIN + 架构 + 行数	完整分析并通过统计数据进行验证	生产环境性能调查

校准规则

1. 先测量，再优化。 提出更改建议前，请先提供 EXPLAIN 输出。 对查询性能的直觉并不可靠——看起来“很慢”的查询可能在索引合适时运行很快，而看起来“简单”的查询也可能扫描数百万行。
2. 谨慎使用索引。 每个索引都会带来写入开销。不要推荐实际查询工作负载不会使用的索引。请考虑读写比。
3. 解释原因，而不只是说明做什么。 “在 users.email 上添加索引”并不完整。 “在 users.email 上添加索引，因为 WHERE 子句按邮箱筛选，目前会导致对 100 万行进行顺序扫描”才具有可操作性。
4. 保持正确性。 重写查询必须返回相同结果。如果重写会改变语义（例如，INNER JOIN 与 LEFT JOIN），请明确指出。
5. 数据库引擎很重要。 PostgreSQL、MySQL 和 SQLite 使用不同的优化器、索引类型和功能。请始终针对具体引擎进行优化。

错误处理

问题	解决方案
未提供 EXPLAIN 输出	分析查询结构和反模式。请说明未提供 EXPLAIN 时，建议仅供参考。
数据库引擎未知	询问使用的是哪种引擎。默认的反模式分析适用于所有引擎。
查询使用 ORM 生成的 SQL	优化 SQL，然后建议在 ORM 层进行更改（例如，在 Django 中使用 select_related，或进行预加载）。
未提供架构	根据查询推断表结构，并说明所做的假设。
查询已经最优	说明无法实现显著改进。建议采用非查询优化（缓存、反规范化）。
包含复杂的多 CTE 查询	分别分析每个 CTE，然后分析它们的组合。

不应优化的情况

以下情况应提出异议：

• 查询很少运行，且性能可以接受（一次性的管理员查询）
• 优化需要进行影响许多使用方的架构更改——建议改用 ADR
• 真正的问题出在应用层（ORM 循环导致 N+1）——应修复应用代码，而不是 SQL
• 查询由工具自动生成（ORM 迁移、BI 工具）——应在工具层进行优化