SQL 查询优化
本页给出真实可运行的优化示例：EXPLAIN ANALYZE 输出解读（Seq Scan vs Index Scan 对比）、复合索引列顺序规则、N+1/全表扫描/缺失索引的具体 SQL 修复、IN vs EXISTS / 子查询 vs JOIN 性能对比，以及 PgBouncer 连接池关键参数配置；让 Agent 给出可验证的改写假设。
先归类：全表扫描、错误基数估计、隐式类型转换、OR 导致索引失效、排序与临时文件过大等；输出时按可能性排序并引用计划中的关键算子。
索引建议需包含选择性、最左前缀与写放大权衡；复合索引列顺序应贴合 WHERE + ORDER BY 的实际组合。
禁止凭空断言「加缓存即可」：若提出缓存，须说明键设计、失效策略与一致性要求，与 SQL 优化结论分开陈述。
- 分页：深分页优先 keyset / seek 而非大 OFFSET。
- JOIN：驱动表选择与 NL/Hash/Merge 适用场景简要说明。
- 验证：给出 before/after 的计时或行数对比步骤（含代表性参数）。
执行计划工作流（ASCII）
从语句到可行动假设的典型阅读顺序；具体算子名随引擎而异，但层级逻辑一致。
-- EXPLAIN ANALYZE 真实输出示例（PostgreSQL）
-- 慢查询：全表扫描
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';
-- 输出示例（未建索引）：
-- Seq Scan on orders (cost=0.00..4821.00 rows=3 width=128)
-- (actual time=0.042..23.847 rows=3 loops=1)
-- Filter: ((user_id = 12345) AND (status = 'pending'))
-- Rows Removed by Filter: 98997
-- Planning Time: 0.124 ms
-- Execution Time: 23.923 ms ← 全表扫描 99000 行只返回 3 行
-- Buffers: shared hit=2257
-- 建复合索引后：
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- 优化后输出：
-- Index Scan using idx_orders_user_status on orders
-- (cost=0.42..12.43 rows=3 width=128)
-- (actual time=0.024..0.031 rows=3 loops=1)
-- Index Cond: ((user_id = 12345) AND (status = 'pending'))
-- Planning Time: 0.201 ms
-- Execution Time: 0.058 ms ← 从 23ms 降到 0.06ms，提升 400x
-- Buffers: shared hit=5
-- 关注点：
-- 1. Seq Scan + Rows Removed by Filter 大 → 缺索引
-- 2. actual rows 与 estimated rows 差异大 → 统计信息过期（ANALYZE）
-- 3. Sort / Hash / Temp → 内存不足溢出磁盘（work_mem 调整）
-- 4. Nested Loop 驱动表 rows 大 → 连接顺序可能选错┌─────────────┐ ┌──────────────┐ ┌─────────────────┐
│ 慢查询日志 │────▶│ EXPLAIN │────▶│ 找「最贵」子树 │
│ / 监控阈值 │ │ (ANALYZE) │ │ 扫描行·回表·排序 │
└─────────────┘ └──────────────┘ └────────┬────────┘
│
┌─────────────────────────────┼─────────────────────────────┐
▼ ▼ ▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ 访问路径 │ │ 连接顺序/算法 │ │ 物化/临时 │
│ Seq vs Index │ │ NL / Hash / │ │ Sort / Agg │
└───────┬───────┘ └───────┬───────┘ └───────┬───────┘
│ │ │
└─────────────────────────────┴─────────────────────────────┘
│
▼
┌──────────────────┐
│ 对照统计信息 │
│ ndv / 直方图 / 相关│
└────────┬─────────┘
│
▼
┌──────────────────┐
│ 提出索引/改写假设 │
│ + 可重复验证步骤 │
└──────────────────┘
索引选择启发
以下为经验法则，最终以实际计划与数据分布为准。
-- 复合索引列顺序规则：等值列优先，范围列靠后，排序列最后
-- 查询模式：WHERE user_id = ? AND created_at > ? ORDER BY created_at
-- 正确顺序（等值列在前）：
CREATE INDEX idx_orders_uid_created
ON orders (user_id, created_at DESC);
-- user_id 等值过滤后，created_at 的有序性可用于 ORDER BY（避免 Sort 节点）
-- 错误顺序（范围列在前）：
-- CREATE INDEX idx_orders_created_uid ON orders (created_at, user_id);
-- 结果：created_at 范围扫描后 user_id 无法走索引有序性
-- 覆盖索引：避免回表
-- 若 SELECT 只需 user_id, status, amount，可 INCLUDE 额外列
CREATE INDEX idx_orders_cover
ON orders (user_id, status)
INCLUDE (amount, created_at);
-- 效果：Index Only Scan，无需回表读 heap
-- 部分索引：低基数列 + 常用过滤条件
CREATE INDEX idx_orders_pending
ON orders (user_id)
WHERE status = 'pending';
-- 适合：99% 行是 completed，只 1% 是 pending 的场景| 案例 | 倾向 | 注意 |
|---|---|---|
| 等值过滤列多 | 高选择性列靠前；= 条件优先进入复合索引左侧 | 范围条件后的列常无法继续走索引有序性 |
| 排序 + 过滤 | 尝试 (过滤前缀, ORDER BY 列…) | 与 SELECT * 回表成本权衡；覆盖索引可减少回表 |
| 多表 JOIN | 小结果集驱动；连接键上有索引 | 统计过期会导致选错驱动表与算法 |
| 低选择性列 | 单独索引收益可能差 | 考虑组合索引、部分索引或改写查询 |
| 写多读少表 | 节制索引数量 | 每次写入需维护所有相关二级索引 |
N+1 查询
N+1：外层 1 次查询拿到 N 行后，对每一行再发 1 条子查询或关联加载，总往返 ≈ 1+N。ORM 默认懒加载时最常见。Agent 分析时应区分「单次慢 SQL」与「多次轻 SQL 叠加」；缓解方向包括 JOIN / IN 批量预取、显式 eager load、数据加载器批处理或改写为一条聚合子查询。
-- ❌ N+1 反模式（伪代码 + SQL）
-- 第 1 次查询：SELECT id FROM users WHERE active = true → 1000 行
-- 然后对每个 user 发：SELECT * FROM orders WHERE user_id = ? → 1000 次
-- 总计：1001 次查询
-- ✅ 修复：JOIN 一次获取
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true;
-- ✅ 修复：IN 批量预取（数据加载器模式）
SELECT * FROM orders
WHERE user_id = ANY(ARRAY[1,2,3,...,1000]); -- 一次性批量
-- ✅ IN vs EXISTS 性能对比
-- IN（子查询结果集小时高效）：
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE plan = 'premium');
-- EXISTS（外层表大、子查询短路时高效）：
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM users u
WHERE u.id = o.user_id AND u.plan = 'premium'
);
-- 规则：子查询结果集小 → IN；外层表大且子查询有索引 → EXISTS
-- 子查询 vs JOIN 可读性与性能
-- ❌ 相关子查询（每行执行一次）：
SELECT u.id, (SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS total
FROM users u;
-- ✅ 改写为 JOIN + GROUP BY（一次扫描）：
SELECT u.id, COALESCE(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;-- PgBouncer 连接池关键参数配置（pgbouncer.ini）
[databases]
payments = host=127.0.0.1 port=5432 dbname=payments
[pgbouncer]
pool_mode = transaction ; transaction/session/statement
max_client_conn = 1000 ; 客户端最大连接数
default_pool_size = 20 ; 每个 (db, user) 对的连接池大小
min_pool_size = 5 ; 最小保持连接数（避免冷启动）
reserve_pool_size = 5 ; 应急保留连接
reserve_pool_timeout = 5 ; 等超时后启用保留连接（秒）
max_db_connections = 100 ; 对同一 DB 的总后端连接上限
server_idle_timeout = 600 ; 空闲后端连接关闭超时（秒）
client_idle_timeout = 0 ; 0=禁用客户端超时
auth_type = md5
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
; 推导：若 QPS=500, 单查询 p95=50ms，需要并发 = 500 * 0.05 = 25
; default_pool_size 设为 25~30，留 20% 余量SQL 关键字与粗成本序
在文本框中粘贴语句：下方同步高亮常见 SQL 关键字；并给出子句的粗略成本相对排序（仅作阅读提示，非真实代价模型）。
关键字高亮预览
---
name: sql-query-tuning
description: 分析慢 SQL，输出 EXPLAIN 解读、索引 DDL 与改写草案
---
# 诊断步骤
1. EXPLAIN (ANALYZE, BUFFERS) 获取真实计划
2. 识别：Seq Scan + Rows Removed 大 → 缺索引
3. 识别：actual rows 与 estimate 差异大 → ANALYZE 统计信息过期
4. 识别：Sort / Hash / Temp → work_mem 不足（调整会话级参数测试）
# 索引策略
等值列靠前，范围列靠后，ORDER BY 列在末尾（同向）
覆盖索引 INCLUDE 减少回表；部分索引 WHERE 过滤低选择性
建大表索引用 CONCURRENTLY；不在事务块内执行
# 常见慢查询修复
N+1: LEFT JOIN ... GROUP BY 或 IN(SELECT id ...) 批量预取
全表扫描: 建索引或改写 WHERE 避免函数包裹索引列
相关子查询: 改写为 JOIN + GROUP BY（一次扫描）
IN vs EXISTS: 子查询结果集小→IN, 外层大且有索引→EXISTS
# 连接池
PgBouncer pool_mode=transaction; default_pool_size=QPS*p95_sec*1.2
max_client_conn 与应用实例数 * 线程数对齐
# 验证
before/after Execution Time 对比；生产需代表性参数 + ANALYZE 后测试