查询工具箱
从过滤与 JOIN 到 CTE、窗口函数和安全参数化
可维护查询的基本形状
SELECT
o.id,
c.email,
o.total_cents,
o.placed_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
AND o.placed_at >= $2
ORDER BY o.placed_at DESC, o.id DESC
LIMIT $3;这条查询明确了输出、连接条件、参数、稳定排序和上限。$1、$2、$3 由驱动绑定；不要用字符串拼接用户输入。
JOIN 的决策
| 目标 | 使用 |
|---|---|
| 只保留两边匹配行 | INNER JOIN / JOIN |
| 保留左表全部行 | LEFT JOIN |
| 判断相关行是否存在 | EXISTS，常比“JOIN 后 DISTINCT”更清楚 |
| 找没有关联行的数据 | NOT EXISTS，避免 NOT IN 的 NULL 语义陷阱 |
SELECT c.id, c.email
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);聚合与窗口不是一回事
GROUP BY 把多行折叠为一行；窗口函数保留明细行，同时在窗口内计算。
SELECT
customer_id,
id AS order_id,
total_cents,
row_number() OVER (
PARTITION BY customer_id
ORDER BY placed_at DESC, id DESC
) AS recency_rank,
sum(total_cents) OVER (PARTITION BY customer_id) AS lifetime_cents
FROM orders;CTE 的用途
CTE 应为复杂查询命名阶段，而不是自动优化按钮。
WITH recent_paid AS (
SELECT customer_id, total_cents
FROM orders
WHERE status = 'paid'
AND placed_at >= now() - interval '30 days'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM recent_paid
GROUP BY customer_id;分页
大结果集优先 keyset pagination：
SELECT id, placed_at, total_cents
FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC
LIMIT 50;与很大的 OFFSET 相比，它不需要不断跳过前面的行，并在并发写入时更稳定。游标必须包含排序的全部键。
写查询前的检查
- 输出列是否稳定并最小化？
- 每个 JOIN 是否可能放大行数？
NULL的含义是否明确？- 排序是否有唯一的最终 tie-breaker？
- 参数是否由驱动绑定？
- 是否需要超时和结果行数上限？
Last updated on