ZrobDev/sql-tuning

SQL 调优（优先 PostgreSQL）

在更改任何内容之前先进行测量。没有前后对比计划和计时的调优更改就是瞎猜。

步骤

1. 定位目标：按总成本排序，而不是按单次最慢调用（参见排查循环）。
2. 捕获基线：在生产规模数据上执行 EXPLAIN (ANALYZE, BUFFERS) <查询>。微小的开发数据会选择不同的计划。
3. 定位时间或行数出现问题的节点（参见下面的计划解读规则）。仅修复该节点。
4. 重新执行相同的计划捕获。仅当更改在时间和缓冲区上都胜出时才保留。
5. 记录创建的索引或重写的 SQL 及其原因，放在 schema 迁移脚本旁边。

将 INSERT/UPDATE/DELETE 的 EXPLAIN ANALYZE 包裹在 BEGIN; ... ROLLBACK; 中，因为 ANALYZE 会实际执行语句。

计划解读

• 从下往上读，先读内层节点。每个节点的 actual time 是单次循环的时间；乘以 loops 以获取实际成本。
• 比较 rows（估算行数）和 actual rows（实际行数）。10倍或以上的差异是根本原因，这比缺少索引要常见得多：请运行 ANALYZE <表>，提高列统计信息目标，或为相关列添加扩展统计信息（CREATE STATISTICS ... (dependencies, ndistinct)）。
• 如果 Rows Removed by Filter 远大于返回的行数，意味着该索引（或缺少索引）对此谓词不具有选择性。
• Buffers: shared read 表示磁盘或操作系统缓存；shared hit 表示共享缓冲区。重复执行时高 reads 意味着工作集无法容纳。
• Sort Method: external merge Disk: 表示此排序的 work_mem 太小。请按会话或按角色提高，而不是全局提高，因为它会按每个排序节点和每个连接成倍增加。
• Nested Loop 具有大的内层 loops 计数且内边未索引，是经典的慢连接。Hash Join 溢出（Batches: >1）同样是 work_mem 的问题。
• 当查询读取表的大部分数据时，Seq Scan 是正确的。不要为了消除返回 20% 行数的 Seq Scan 而添加索引。
• 在 Index Only Scan 上的 Heap Fetches 意味着可见性映射已过时；请对该表运行 VACUUM。

选择索引

仅为你测量过的谓词、连接或 ORDER BY 创建索引。每个索引都会消耗写时间、WAL 和清理工作。

• btree：用于相等、范围、排序的默认选择。列顺序：等值列在前，然后是范围或排序列。PG 18 中的 Skip Scan 有助于某些缺少前导列的情况，但不要围绕它设计。
• Partial（WHERE status = 'open'）：当查询针对一个小的、稳定的数据切片（未关闭项、未处理的行）时。查询的 WHERE 必须隐含索引谓词。
• Covering（INCLUDE (col)）：当一个索引唯一扫描可以为一个热点查询消除堆表获取时。包含的列不是可搜索的键。
• Expression（ON (lower(email))，ON ((payload->>'id'))）：当查询对列应用函数时。查询必须使用完全相同的表达式。
• BRIN：适用于巨大的仅追加表，其中列与物理顺序相关（时间戳、序列ID）。索引很小，粗粒度裁剪。检查 pg_stats.correlation 接近1；如果很低，BRIN 将无法裁剪。
• GIN：jsonb 包含操作、数组、全文搜索。GiST：范围类型、几何类型、排除约束。
• 在在线表上使用 CREATE INDEX CONCURRENTLY 创建索引；它不能在事务块内运行，失败的运行会留下一个 INVALID 索引需要删除。
• 只有在检查 pg_stat_user_indexes.idx_scan 完整业务周期（财务工作负载为月末）的数据，并且在副本上也检查之后，才能删除未使用的索引。

可索引性（Sargability）

谓词只有在索引列独立出现在一侧时才能使用索引。

• 使用 WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' 而不是 WHERE extract(year from created_at) = 2026 或 date_trunc(...) = ...。
• 使用类型匹配的 WHERE col = $1；列侧的类型转换如 numeric_col = '5'::text 会阻止索引。应转换参数，而不是列。
• 避免前导通配符 LIKE '%x'；使用 pg_trgm GIN 索引或全文搜索。
• 跨不同列的 OR 经常会丢失索引；尝试使用两个索引分支的 UNION ALL。
• NOT IN (子查询) 对 NULL 不友好且速度慢；使用 NOT EXISTS。
• 对于半连接，优先使用 EXISTS 而不是连接后的 DISTINCT，因为它会在第一次匹配时停止，并且不会重复行。

窗口函数 vs 自联接

当目标是从同一集合中相关行获取每行值时，使用窗口函数：运行总计、排名、lag/lead、“每个键的最新行”（row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1），因为一次扫描和一次排序替代了表与自身的连接。 仅当第二边需要自己的过滤器或索引探测时，才使用自联接或 LATERAL（从大表中获取每个键的 Top-N：JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3) 配合索引 (k, ts DESC)）。 DISTINCT ON (k) ... ORDER BY k, ts DESC 是 PostgreSQL 中用于每个键最新行的简短形式。

公用表表达式 (CTE)

从 PG 12 开始，一个非递归、无副作用的 CTE（仅被引用一次）会像子查询一样被内联。默认情况下以可读性为目标编写。

• 当 CTE 开销很大且被多次引用时，或者当必须阻止优化器将不良谓词推入其中时，添加 AS MATERIALIZED。
• 当外部过滤器使 CTE 变得廉价时，添加 AS NOT MATERIALIZED 以强制内联一个被多次引用的 CTE。
• 数据修改型 CTE 始终执行一次；它们不会被优化掉。

pg_stat_statements 排查循环

需要 pg_stat_statements 位于 shared_preload_libraries 中（需要重启）并执行 CREATE EXTENSION。

1. 按 total_exec_time DESC 排序（同时检查 shared_blks_read，temp_blks_written，rows）。一个被调用 4000 万次、每次 5毫秒的查询，优于一个每晚运行一次、耗时 30秒的查询。
2. 取前 3 个。对于每个查询，获取一组真实的参数（统计信息是标准化的），并执行步骤 2 中的流程。
3. 修复、部署，然后仅对该查询执行 SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>)，等待一个有代表性的时间窗口，进行比较。
4. 重复直到排名第一的条目是一个合理的成本。然后停止。 配合使用 auto_explain（保守使用 log_min_duration，log_analyze）来捕获你无法重现的查询的计划。

仅在必要时使用 PL/pgSQL

当函数或过程能减少无法用基于集合的方法解决的逐行逻辑的往返次数、强制触发器不变量、或封装多语句事务时，才使用它们。不要用它来循环行并每行执行一次 UPDATE：将其重写为一个 UPDATE ... FROM 或 INSERT ... SELECT。正确标记纯函数为 IMMUTABLE/STABLE（错误标签会导致错误的缓存结果或阻止内联）。在可能的情况下优先使用纯 SQL 函数，以便优化器可以内联它们。

N+1 和分页

• N+1：来自应用程序或 ORM 代码的、每个父行一个查询。使用一次性的 WHERE id = ANY($1) 批处理、一个 JOIN、或 ORM 的预加载选项来修复。通过 pg_stat_statements 检测：一个标准化语句的 calls 接近父行计数。
• OFFSET 分页会读取并丢弃所有跳过的行，因此第 N 页的成本是 O(N)。使用键集分页：WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50 配合索引 (created_at DESC, id DESC)。决胜列是强制性的，否则行会被跳过或重复。键集分页不能跳转到第 N 页；仅对小的有界数据集使用 OFFSET。
• 在大表上执行 count(*) 是一个全表扫描；显示近似计数（reltuples）或使用 SELECT count(*) FROM (SELECT 1 ... LIMIT 10001) 进行上限计数。

批量 ETL 说明

使用 COPY（或 INSERT ... SELECT）进行批量加载，而不是单行插入；仅对大型初始加载删除或延迟二级索引，之后执行 ANALYZE。大型 UPDATE 会使表膨胀：按键范围分批，每批提交，让 autovacuum 跟上。

其他方言（简要）

• BigQuery：成本是按扫描的字节数计费，而不是按行数。在日期/时间戳列上进行分区，并使用字面量或参数过滤它以实现分区裁剪；在下一个过滤最多的列上进行聚簇。SELECT * 会对每个列收费；请列出列名。在执行前使用 dry run 查看字节数。LIMIT 不会减少扫描的字节数。
• Snowflake：裁剪使用微分区的最小/最大元数据，因此请在按加载时自然有序的列上进行过滤；在查询配置文件中检查“扫描的分区数 vs 总数”。仅为具有稳定过滤模式的大型表添加聚簇键，因为重新聚簇会消耗积分。对于溢出（配置文件中的本地/远程溢出），向上调整仓库大小；对于排队/并发，向外扩展（多集群）。启用自动挂起（短时间）。

当前版本和文档链接：references/landscape-2026-11.md。实际使用中的经验教训：references/gotchas.md。