性能优化关键点
驱动表选择
规则：优化器会选择 小表 作为驱动表（外层循环）。
好的选择：
小表 movies (4 行) × 大表 actors (1000000 行)
= 4 次外层循环 × log(1000000) ≈ 80 次操作
差的选择：
大表 actors (1000000 行) × 小表 movies (4 行)
= 1000000 次外层循环 × log(4) ≈ 2000000 次操作
结论：驱动表选择错误，性能可能相差 10 倍以上。
索引是性能的关键
JOIN 列必须有索引，否则内表会全表扫描，成本从 O(M × log N) 变成 O(M × N)。B-Tree 索引适合大多数场景，Hash 索引适合等值查询，复合索引可以覆盖多个查询条件。
JOIN 顺序优化
多表 JOIN 时，优化器会重排执行顺序，选择中间结果最小的方案。
SELECT *
FROM movies m
JOIN actors a ON m.id = a.movie_id
JOIN awards aw ON a.id = aw.actor_id;优化器可能先执行 actors JOIN awards（中间结果 3 行），再 JOIN movies，而不是按书写顺序。中间结果越小，后续 JOIN 越快。
用 EXPLAIN 找瓶颈
EXPLAIN ANALYZE
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id;关键信息：
- Seq Scan（全表扫描）→ 考虑加索引
- actual rows 远大于 rows（预估） → 运行
ANALYZE更新统计信息 - actual time 很大 → 找到慢的步骤，针对性优化
避免常见性能陷阱
陷阱 1：在 JOIN 列使用函数
-- ❌ 慢：函数导致索引失效
SELECT m.title, a.name
FROM movies m
JOIN actors a ON LOWER(m.title) = LOWER(a.favorite_movie);
-- ✅ 快：使用函数索引（Expression Index）
CREATE INDEX idx_lower_title ON movies(LOWER(title));陷阱 2：JOIN 大表后再过滤
-- ❌ 慢：先 JOIN 1000 万行，再过滤到 10 行
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id
WHERE m.year = 2020;
-- ✅ 快：先过滤再 JOIN
SELECT m.title, a.name
FROM (SELECT * FROM movies WHERE year = 2020) m
JOIN actors a ON m.id = a.movie_id;陷阱 3：SELECT * 浪费资源
-- ❌ 慢：返回所有列（可能很大）
SELECT *
FROM movies m
JOIN actors a ON m.id = a.movie_id;
-- ✅ 快：只查需要的列
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id;陷阱 4：多次 JOIN 同一张表
-- ❌ 慢：JOIN actors 两次
SELECT m.title, a1.name, a2.name
FROM movies m
JOIN actors a1 ON m.id = a1.movie_id
JOIN actors a2 ON m.id = a2.movie_id
WHERE a1.id != a2.id;
-- ✅ 快：用窗口函数或聚合
SELECT m.title, STRING_AGG(a.name, ', ') as actors
FROM movies m
JOIN actors a ON m.id = a.movie_id
GROUP BY m.id, m.title;高级优化技巧
使用覆盖索引（Covering Index）
覆盖索引让查询所需列都在索引中，无需回表。
-- 普通索引：只包含 user_id
CREATE INDEX idx_user ON orders(user_id);
-- 覆盖索引：包含查询需要的所有列
CREATE INDEX idx_user_covering ON orders(user_id) INCLUDE (order_id, amount, created_at);
-- 查询时无需回表
SELECT order_id, amount, created_at
FROM orders
WHERE user_id = 123; -- 所有数据都在索引中性能提升 2-5 倍，适合高频查询的场景。
使用分区表
分区表将大表按某个字段（如时间、地区）分成多个小表。
CREATE TABLE movies (
id INT,
title VARCHAR(100),
year INT
) PARTITION BY RANGE (year);
CREATE TABLE movies_2020 PARTITION OF movies
FOR VALUES FROM (2020) TO (2021);
-- JOIN 时只扫描相关分区
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id
WHERE m.year = 2020; -- 只扫描 movies_2020 分区适合超大表（> 1000 万行）且查询通常带分区键过滤条件的场景。未分区时扫描 1000 万行，分区后只扫描 10 万行，提升 100 倍。
视图（Materialized View）
物化视图预先计算并存储 JOIN 结果，把复杂查询的 100ms 降到 1ms。
CREATE MATERIALIZED VIEW mv_movies_actors AS
SELECT m.title, a.name, m.year
FROM movies m
JOIN actors a ON m.id = a.movie_id;
CREATE INDEX idx_mv_year ON mv_movies_actors(year);
-- 查询视图（无需 JOIN）
SELECT title, name FROM mv_movies_actors WHERE year = 2020;
-- 定期刷新
REFRESH MATERIALIZED VIEW mv_movies_actors;适合复杂 JOIN、数据更新不频繁（每天/每小时刷新）、查询频繁但可以接受一定延迟的场景。
并行查询
并行查询让多个 CPU 核心同时执行 JOIN，把 10 秒降到 3-4 秒。
SET max_parallel_workers_per_gather = 4;
SELECT m.title, a.name
FROM movies m
JOIN actors a ON m.id = a.movie_id;适合大表 JOIN（> 100 万行）且服务器有多核 CPU 的场景。注意：小表反而变慢（并行开销 > 收益），高并发场景慎用（CPU 竞争激烈）。
使用 CTE（Common Table Expression）
CTE将复杂查询拆分成多个步骤，代码更清晰，优化器也能独立优化每个部分。
WITH recent_movies AS (
SELECT id, title FROM movies WHERE year >= 2020
),
top_actors AS (
SELECT actor_id, COUNT(*) as movie_count
FROM movie_actors
GROUP BY actor_id
HAVING COUNT(*) > 5
)
SELECT rm.title, a.name, ta.movie_count
FROM recent_movies rm
JOIN movie_actors ma ON rm.id = ma.movie_id
JOIN actors a ON ma.actor_id = a.id
JOIN top_actors ta ON a.id = ta.actor_id;索引维护
定期运行 ANALYZE 更新统计信息，否则优化器可能选错执行计划。用 pg_stat_user_indexes 视图查看索引使用情况，清理长期未使用的索引。
实战案例
慢查询问题：
SELECT o.order_id, u.name, p.product_name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2020-01-01';orders 表有 1000 万行，查询很慢。
第 1 步：用 EXPLAIN 找瓶颈
(经验之谈：其实不看 EXPLAIN，看 SQL 也能预判需要哪些索引：WHERE 条件的列 + JOIN 列通常都需要索引。但 EXPLAIN 能告诉你实际瓶颈在哪，以及优化后的效果，更可靠。)
EXPLAIN ANALYZE
SELECT o.order_id, u.name, p.product_name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2020-01-01';执行计划输出：
Hash Join (cost=1456.00..334567.89 rows=50000 width=128) (actual time=23.456..8234.567 rows=48923 loops=1)
Hash Cond: (o.product_id = p.id)
-> Hash Join (cost=728.00..312456.78 rows=50000 width=96) (actual time=12.234..7891.234 rows=48923 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..289234.56 rows=50000 width=68) (actual time=0.123..7234.567 rows=48923 loops=1)
Filter: (created_at >= '2020-01-01'::date)
Rows Removed by Filter: 9951077
-> Hash (cost=456.00..456.00 rows=10000 width=32) (actual time=5.678..5.678 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 512kB
-> Seq Scan on users u (cost=0.00..456.00 rows=10000 width=32) (actual time=0.012..2.345 rows=10000 loops=1)
-> Hash (cost=364.00..364.00 rows=5000 width=36) (actual time=3.456..3.456 rows=5000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 256kB
-> Seq Scan on products p (cost=0.00..364.00 rows=5000 width=36) (actual time=0.008..1.234 rows=5000 loops=1)
Planning Time: 0.234 ms
Execution Time: 8237.891 ms
一次或者多次 EXPLAIN 排查问题：
-
Seq Scan on orders（第 5 行）created_at没有索引，导致必须扫描所有行- 耗时最长：7234 ms
-
Hash Join代价很高（第 2-3 行）- 虽然用了 Hash Join，但因为 orders 表太大，构建和探测 hash 表都很慢
- 如果
user_id和product_id有索引，可以用 Nested Loop + Index Scan，会更快
第 2 步：给过滤列和 JOIN 列加索引
-- WHERE 过滤列：解决全表扫描
CREATE INDEX idx_orders_created_at ON orders(created_at);
-- JOIN 列：让优化器可以选择 Nested Loop + Index Scan
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);为什么需要这三个索引：
created_at：让Seq Scan变成Index Scan，从扫描 1000 万行变成扫描 5 万行user_id和product_id：让优化器可以用更高效的 Nested Loop + Index Scan，而不是 Hash Join
再次 EXPLAIN，执行计划会变成: Seq Scan → Index Scan using
第 3 步：如果还不够快，用覆盖索引
-- 包含查询所需的所有列，避免回表
CREATE INDEX idx_orders_covering
ON orders(created_at)
INCLUDE (order_id, user_id, product_id, amount);为什么覆盖索引更快：普通索引只包含 created_at，查询还需要 order_id、amount 等列，必须回表查数据。覆盖索引把这些列都包含进来，直接从索引读取，省去回表的 I/O。
适用场景：高频查询，且查询列不多（索引不会太大）。