SQL 与查询优化（PostgreSQL 篇）· 第一期

读懂执行计划 & 索引优化入门

本系列专注于 PostgreSQL 中 SQL 能力的深度挖掘与查询性能调优。第一期从最核心的工具——执行计划入手，结合统计信息与索引选择，带你建立系统性的优化思维。

一、为什么查询优化如此重要？

很多开发人员写出功能正确的 SQL，却忽略了执行效率。当数据量从万级增长到千万级，一个缺少索引或写法不佳的查询可能导致响应时间从毫秒变成分钟。PostgreSQL 提供了强大的优化器、丰富的索引类型和精准的代价模型，但前提是你要学会如何“引导”它。

优化不是盲目加索引，而是：

看懂执行计划 – 知道 PG 实际怎么执行你的 SQL。
理解统计信息 – 让优化器获得准确的基数估算。
合理选择索引与改写 SQL – 用最小的代价让查询走最优路径。

二、执行计划 – 透视 PG 的内部动作

2.1 基础命令：`EXPLAIN`

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

输出示例：

Seq Scan on orders  (cost=0.00..2043.00 rows=10 width=88)
  Filter: (customer_id = 12345)

Seq Scan：顺序扫描整张表。
cost：第一个数字是启动成本（获取第一行的代价），第二个数字是总成本。
rows：优化器估计返回的行数。
width：结果行平均字节数。

真正执行并返回实际耗时：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

ANALYZE 会真正运行查询，输出实际执行时间、实际行数、内存使用等，是分析的黄金工具。

⚠️ 注意：EXPLAIN ANALYZE 会修改数据（如果 SQL 包含 INSERT/UPDATE/DELETE），建议在事务中回滚或使用只读查询。

2.2 常见扫描方式

扫描类型	说明	适用场景
Seq Scan	全表顺序扫描	表很小、或需要读取大部分数据（>5%~10%）、或没有可用索引
Index Scan	索引扫描 + 回表	索引过滤条件强，返回行数较少
Index Only Scan	仅索引扫描	所有需要的字段都在索引中，不需要回表
Bitmap Index Scan + Bitmap Heap Scan	位图扫描	多个索引条件组合，或返回行数中等（减少随机 I/O）

举例：

-- 创建索引后
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

输出变为：

Index Scan using idx_orders_customer on orders  (cost=0.29..8.30 rows=10 width=88)
  Index Cond: (customer_id = 12345)

2.3 连接方法（Join 策略）

PostgreSQL 有三种常见连接算法：

连接方式	原理	复杂度	适用场景
Nested Loop	外层表每行去内层表匹配	O(outer * inner)	驱动表很小，内层表有索引
Hash Join	构建一个表的哈希表，另一表探测	O(outer + inner)	两表较大且等值连接，无索引可用
Merge Join	两表按连接键排序后归并	O(outer + inner)	两表已有序或连接键有序，常用于不等值连接

阅读执行计划时，注意最里层（缩进最多的节点）最先执行。例如：

Hash Join  (cost=...)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on customers c

先扫描 customers 构建 Hash 表，再扫描 orders 探测。

三、统计信息 – 优化器的“眼睛”

优化器依赖表统计信息来估算 rows 和 cost。不准的统计信息会导致走错索引或选错连接方式。

3.1 更新统计信息

ANALYZE orders;           -- 更新单表
ANALYZE;                  -- 更新整个数据库

PostgreSQL 默认 autovacuum 会自动触发 analyze，但大批量数据变更后建议手动执行。

3.2 查看统计信息

SELECT attname, n_distinct, correlation, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders';

n_distinct：不同值的数量（负数为比例）。
correlation：物理行顺序与逻辑顺序的相关系数。接近 1 或 -1 时，索引扫描效率极高。
most_common_vals / most_common_freqs：高频值及其频率。

当查询条件使用 column = constant，且 constant 出现在高频值中，优化器会准确估算行数。

3.3 代价参数影响

优化器的成本公式： 总成本 = 启动成本 + 运行成本，其中运行成本 = seq_page_cost <em> 页面数 + cpu_tuple_cost </em> 行数 + ...

默认参数适合通用硬件，但可针对 SSD 调低 random_page_cost（例如设为 1.1），让优化器更青睐 Index Scan。

四、索引优化基础

4.1 选择正确的索引类型

索引类型	关键词	适用操作符
B-tree（默认）	`USING BTREE`	`=, <, <=, >, >=, BETWEEN, IN, LIKE 'abc%'`
Hash	`USING HASH`	仅相等比较 `=`
GiST	几何数据、全文检索、距离排序	邻近搜索、交集等
GIN	数组、JSONB、全文检索	包含操作 `@>`、`?` 等
BRIN	很大且物理有序的表	时间序列、日志表

最常用的是 B-tree，覆盖 >90% 的场景。

4.2 复合索引与列顺序

假设查询：WHERE a = 1 AND b > 10

索引 (a, b) 效果很好：先精准匹配 a，再范围扫描 b。索引 (b, a) 效果差：b 的范围条件可能导致无法高效使用 a 的等值过滤。

黄金规则：

等值条件列放在前面。
范围条件列放在后面。
选择性高的列放前面。

4.3 部分索引与表达式索引

部分索引：只为满足条件的行建索引，减少体积。

CREATE INDEX idx_active_user ON orders(user_id) WHERE status = 'active';

查询必须带相同的 WHERE status = 'active' 才能使用。

表达式索引：对函数或表达式建索引。

CREATE INDEX idx_lower_email ON users(lower(email));

查询：SELECT * FROM users WHERE lower(email) = '[email protected]'; 会走索引。

4.4 索引覆盖（Covering Index）

PostgreSQL 11+ 支持 INCLUDE 子句，将非索引列“携带”在叶子节点，避免回表。

CREATE INDEX idx_covering ON orders(customer_id) INCLUDE (order_date, total_amount);

查询 SELECT order_date, total_amount FROM orders WHERE customer_id = 123; 可走 Index Only Scan。

五、SQL 改写实战 – 从小处优化

5.1 避免在索引列上使用函数

❌ 慢：

SELECT * FROM orders WHERE date(created_at) = '2025-01-01';

✅ 快：

SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';

5.2 谨慎使用 `LIKE` 前缀通配符

LIKE 'abc%' → 可用 B-tree 索引。
LIKE '%abc' → 无法用 B-tree。考虑使用 pg_trgm 的 GIN 索引。

5.3 `OR` 改写为 `UNION ALL`（在特定场景）

当 OR 涉及不同列且选择性差时，优化器可能放弃索引。改写为 UNION ALL 加索引能提升性能。

❌ 原查询：

SELECT * FROM orders WHERE customer_id = 1 OR status = 'pending';

✅ 改写（假设各分支可独立走索引）：

SELECT * FROM orders WHERE customer_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id <> 1;

5.4 避免隐式类型转换

-- phone 字段是 varchar 类型
SELECT * FROM users WHERE phone = 13800138000;   -- 隐式转换，不走索引
SELECT * FROM users WHERE phone = '13800138000'; -- 显式文本，走索引

六、完整案例 – 从 3 秒到 10 毫秒

表结构：

CREATE TABLE orders (
    id bigserial primary key,
    customer_id int,
    order_date timestamptz,
    total_amount numeric(12,2),
    status text
);
INSERT INTO orders SELECT generate_series(1, 5000000), random()*100000, ...;

慢查询：

SELECT customer_id, sum(total_amount) 
FROM orders 
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY customer_id;

执行计划（EXPLAIN ANALYZE）：

Finalize GroupAggregate (actual time=3124.567..3124.789 rows=85000 loops=1)
  ->  Gather Merge (actual time=3124.123..3125.001 rows=85000 loops=1)
        ->  Partial HashAggregate (actual time=3110.234..3110.456 rows=28000 loops=4)
              ->  Parallel Seq Scan on orders (actual time=0.123..2567.890 rows=125000 loops=4)
                    Filter: (order_date >= '2025-01-01' AND order_date <= '2025-01-31')

问题：全表并行顺序扫描，扫描了 500 万行，过滤后约 50 万行，耗时 3 秒。

优化步骤：

创建索引

CREATE INDEX idx_orders_date ON orders(order_date);

再跑执行计划

Index Scan using idx_orders_date on orders (actual time=0.045..12.567 rows=500000 loops=1)
 Index Cond: (order_date >= '2025-01-01' AND order_date <= '2025-01-31')

扫描索引只取 1 月份数据，耗时 12ms。

进一步优化 – 覆盖索引避免回表 因为需要 customer_id 和 total_amount，且 group by 需要排序，可以建一个包含字段的复合索引。
```
CREATE INDEX idx_orders_date_customer_amount ON orders(order_date, customer_id, total_amount);
```
再执行，可能变为 Index Only Scan，并消除回表开销，总耗时降到 8ms。

七、总结 & 下期预告

本期要点

学会使用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 观察真实执行情况。
理解扫描方式与连接算法，识别 Seq Scan 何时是好的，何时是坏的。
定期更新统计信息（ANALYZE），关注 pg_stats 中的相关性、高频值。
选择合适的索引类型（B-tree 主导）、顺序（等值在前）、和附加特性（部分索引、表达式索引、覆盖索引）。
改写 SQL 时避免让优化器“自废武功”——函数、隐式转换、前缀模糊。

下期预告

第二期：窗口函数与 CTE 的深度优化

用 ROW_NUMBER() 高效去重
LAG() / LEAD() 替代自连接
CTE 物化屏障（MATERIALIZED / NOT MATERIALIZED）
递归 CTE 的优化陷阱与索引技巧

敬请期待，欢迎在评论区留下你在工作中遇到的典型慢查询，我们会在后续内容中选取分析。

优化之路，从看懂执行计划开始。 我们下期见。