PHP小码农

Appearance

连接查询优化 ​

本节讲解三种连接算法（Nested Loop / Hash Join / Merge Join）的适用条件与代价、影响算法选择的因素、连接顺序与优化器的决策过程、缺失索引的识别方法，以及如何避免连接膨胀带来的性能灾难。学完本节，你将能读懂连接相关的执行计划并针对性地优化它。

前置知识

阅读本节前，建议先了解：自连接（Self Join）

基础概念 ​

连接查询优化 的核心是让优化器能为每个连接节点选到合适的算法，并为算法提供必要的索引或内存条件。算法选择错误、索引缺失、行数估算偏差 是连接慢的三大原因。

概念术语说明
连接算法Join AlgorithmNested Loop / Hash Join / Merge Join
驱动表Outer Table连接中被逐行读出的那一侧
内表Inner Table被反复探测的那一侧
连接顺序Join Order优化器决定的实际执行顺序
行数估算Row Estimate优化器依据统计信息预估的 rows=
行数膨胀Row Inflation一对多连接导致行被复制
半连接Semi Join只判断"存在"，不复制右表列，由 EXISTS/IN 产生
反连接Anti Join只判断"不存在"，由 NOT EXISTS/NOT IN 产生
遗传算法Genetic Optimizer连接表数超过 geqo_threshold 时启用的搜索策略

三种连接算法的本质差异在于"谁被扫描、谁被探测、需要什么前提"：

对比项Nested LoopHash JoinMerge Join
工作方式逐行取驱动表，用索引探测内表小表建哈希表，大表逐行探测哈希两侧排序后归并
适用行数驱动表小（几十~几万行）两侧都较大两侧都大且已有序
连接条件等值/非等值均可仅等值等值（非等值支持差）
必需前提内表连接列有索引work_mem 足够容纳哈希表两侧已排序或有可用索引
复杂度O(N × log M)O(N + M)O(N log N + M log M)
是否阻塞否，可立即返回首行建表阶段阻塞排序阶段阻塞
典型场景OLTP 按主键取少量行大表关联大表大表关联大表且输入有序

语法与用法 ​

连接优化的诊断入口只有一条：EXPLAIN (ANALYZE, BUFFERS)。

text
EXPLAIN [(ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT|JSON|YAML)]
查询语句;

准备本节贯穿使用的示例表：

sql
-- 用户表：分为 vip 与普通用户，用于制造偏斜数据
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表：100 万条，user_id 有分布偏斜
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 订单明细
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL
);

-- 商品表
CREATE TABLE products (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      TEXT    NOT NULL UNIQUE,
  name     TEXT    NOT NULL,
  category TEXT    NOT NULL
);

-- 制造数据：20 万用户 + 100 万订单 + 300 万明细
INSERT INTO users (name, city, vip)
SELECT 'user_' || i,
       (ARRAY['beijing','shanghai','shenzhen','chengdu'])[1 + (i % 4)],
       (i % 10 = 0)
FROM generate_series(1, 200000) AS i;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT 'SO-' || lpad(i::text, 8, '0'),
       1 + (i % 200000),
       (i % 1000)::numeric / 100,
       CASE WHEN i % 20 = 0 THEN 'pending' ELSE 'paid' END,
       DATE '2026-01-01' + (i % 90)
FROM generate_series(1, 1000000) AS i;

INSERT INTO order_items (order_id, product_id, qty, price)
SELECT 1 + (i % 1000000), 1 + (i % 5000), 1 + (i % 5), (i % 300)::numeric
FROM generate_series(1, 3000000) AS i;

INSERT INTO products (sku, name, category)
SELECT 'SKU-' || lpad(i::text, 6, '0'), '商品_' || i, (ARRAY['peripherals','accessories','storage'])[1 + (i % 3)]
FROM generate_series(1, 5000) AS i;

ANALYZE users, orders, order_items, products;

创建索引前后对比的前提 —— 只建主键索引：

sql
-- 此时只有主键索引，连接列无索引
CREATE INDEX idx_orders_user_id       ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product  ON order_items (product_id);
CREATE INDEX idx_orders_status        ON orders (status);

详细说明 ​

三种连接算法的执行计划形态 ​

Nested Loop 的识别特征是 loops= 大于 1，且子节点代价随循环放大：

sql
-- 小结果集驱动大表：典型 Nested Loop 场景
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_no, u.name
FROM orders AS o
JOIN users  AS u ON u.id = o.user_id
WHERE o.order_no = 'SO-00000001';
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Nested Loop  (cost=0.86..20.70 rows=1 width=40) (actual time=0.035..0.041 rows=1 loops=1)
   ->  Index Scan using orders_order_no_key on orders o  (cost=0.42..8.44 rows=1 width=12)
                                                          (actual time=0.021..0.023 rows=1 loops=1)
         Index Cond: (order_no = 'SO-00000001'::text)
   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=36)
                                               (actual time=0.011..0.013 rows=1 loops=1)
         Index Cond: (id = o.user_id)
 Planning Time: 0.420 ms
 Execution Time: 0.078 ms

Hash Join 的识别特征是 Hash 子节点与 Hash Cond：

sql
-- 大表关联大表：典型 Hash Join 场景
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.city, count(*)
FROM users  AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.city;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=40002.00..40002.04 rows=4 width=12) (actual time=812.334..812.336 rows=4 loops=1)
   Group Key: u.city
   ->  Hash Join  (cost=7416.00..35002.00 rows=1000000 width=8) (actual time=118.552..622.993 rows=1000000 loops=1)
         Hash Cond: (o.user_id = u.id)
         ->  Seq Scan on orders o  (cost=0.00..18333.00 rows=1000000 width=8) (actual time=0.010..142.884 rows=1000000 loops=1)
         ->  Hash  (cost=6166.00..6166.00 rows=200000 width=8) (actual time=118.156..118.157 rows=200000 loops=1)
               Buckets: 262144  Batches: 1  Memory Usage: 11248kB
               ->  Seq Scan on users u  (cost=0.00..6166.00 rows=200000 width=8) (actual time=0.009..58.730 rows=200000 loops=1)
 Planning Time: 0.986 ms
 Execution Time: 813.156 ms

Merge Join 的识别特征是两侧都有排序节点（或都靠索引有序），节点名带 Merge Cond：

sql
-- 两侧按连接键有序：Merge Join 优势场景
CREATE INDEX idx_orders_user_id_amount ON orders (user_id, amount);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders AS o1
JOIN orders AS o2 ON o2.user_id = o1.user_id
WHERE o1.id = 42;
text
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=4210.55..4210.56 rows=1 width=8) (actual time=0.317..0.318 rows=1 loops=1)
   ->  Nested Loop  (cost=0.86..4210.54 rows=5 width=0) (actual time=0.058..0.312 rows=5 loops=1)
         ->  Index Scan using orders_pkey on orders o1  (cost=0.42..8.44 rows=1 width=4)
                                                        (actual time=0.021..0.022 rows=1 loops=1)
               Index Cond: (id = 42)
         ->  Index Only Scan using idx_orders_user_id_amount on orders o2
               (cost=0.43..4193.10 rows=500 width=4) (actual time=0.033..0.278 rows=5 loops=1)
               Index Cond: (user_id = o1.user_id)
               Heap Fetches: 0

影响算法选择的因素 ​

优化器在代价模型中比较三种算法，影响判断的因素如下：

因素对 Nested Loop 的影响对 Hash Join 的影响对 Merge Join 的影响
驱动表行数行数越小越好影响探测次数影响归并次数
内表索引必须有可用索引不需要不需要（但有助于免排序）
work_mem无影响不足则 Batches > 1 溢盘排序不足则外部排序
输入有序性无影响无影响显著受益
结果集大小支持早停（LIMIT 友好）需建完哈希表需排完两侧
连接条件类型支持非等值仅等值等值为主

work_mem 不足引发的哈希溢盘是隐蔽的性能杀手：

sql
-- 查看当前 work_mem
SHOW work_mem;

-- 会话级调大（生产环境谨慎，它是每操作每进程的内存）
SET work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM users u JOIN orders o ON o.user_id = u.id;
text
                QUERY PLAN
------------------------------------------
 ...
 ->  Hash  (cost=6166.00..6166.00 rows=200000 width=8)
       Buckets: 262144  Batches: 1  Memory Usage: 11248kB   -- 1 batch：未溢盘

调小后可以复现溢盘：

sql
SET work_mem = '1MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM users u JOIN orders o ON o.user_id = u.id;
text
 ->  Hash  (cost=6166.00..6166.00 rows=200000 width=8)
       Buckets: 131072  Batches: 4  Memory Usage: 1024kB    -- 4 batches：已溢写磁盘

Batches: 4 意味着哈希表被切成 4 批分批处理，磁盘 I/O 显著增加执行时间。

连接顺序与行数估算 ​

连接顺序由优化器决定，与 SQL 书写顺序无关。当连接表数不超过 geqo_threshold（默认 12）时，优化器用动态规划穷举所有顺序：

sql
SHOW geqo_threshold;    -- 默认 12

-- 查看优化器对连接顺序的决策（先出现的是驱动侧）
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, p.name
FROM users       AS u
JOIN orders      AS o ON o.user_id = u.id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE u.city = 'beijing' AND o.status = 'paid';
text
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Hash Join  (cost=... rows=... width=...) (actual time=... rows=75000 loops=1)
   Hash Cond: (i.product_id = p.id)
   ->  Hash Join  (cost=... ) (actual time=... rows=75000 loops=1)
         Hash Cond: (i.order_id = o.id)
         ->  Seq Scan on order_items i  (cost=... ) (actual time=... rows=3000000 loops=1)
         ->  Hash  (cost=... ) (actual time=... rows=50000 loops=1)
               ->  Hash Join  (cost=... ) (actual time=... rows=50000 loops=1)
                     Hash Cond: (o.user_id = u.id)
                     ->  Seq Scan on orders o  (cost=... ) (actual time=... rows=50000 loops=1)
                           Filter: (status = 'paid'::text)
                     ->  Hash  (cost=... ) (actual time=... rows=50000 loops=1)
                           ->  Seq Scan on users u  (cost=... ) (actual time=... rows=50000 loops=1)
                                 Filter: (city = 'beijing'::text)

即使 users 写在最前面，计划也可能把它放到最内层。

行数估算偏差是连接优化的最大敌人。 比较 rows=（估算）与 actual rows=（实际）：

sql
-- 若估算 rows=1 而实际 rows=100000，计划很可能选错算法
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE status = 'pending';
text
                      QUERY PLAN
-----------------------------------------------------
 Aggregate  (cost=23833.00..23833.01 rows=1 width=8) (actual time=245.112..245.113 rows=1 loops=1)
   ->  Seq Scan on orders  (cost=0.00..20833.00 rows=50000 width=0) (actual time=0.012..219.440 rows=50000 loops=1)
         Filter: (status = 'pending'::text)
         Rows Removed by Filter: 950000

估算与实际一致说明统计信息健康。若偏差超过一个数量级，应执行：

sql
-- 刷新统计信息
ANALYZE orders;

-- 对偏斜列提高统计目标（默认 100，最高 10000）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 查看列级统计
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

缺失索引的识别 ​

缺失索引的典型信号是 Seq Scan 出现在内表位置，且 loops 很大：

sql
-- 先删除连接列索引，观察退化
DROP INDEX idx_orders_user_id;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.id < 100;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=... rows=1 width=8) (actual time=... rows=1 loops=1)
   ->  Hash Join  (cost=... ) (actual time=... rows=99 loops=1)
         Hash Cond: (o.user_id = u.id)
         ->  Seq Scan on orders o  (cost=0.00..18333.00 rows=1000000 width=4)
                                   (actual time=0.011..198.472 rows=1000000 loops=1)
         ->  Hash  (cost=... ) (actual time=... rows=99 loops=1)
               ->  Index Scan using users_pkey on users u  (cost=... ) (actual time=... rows=99 loops=1)
                     Index Cond: (id < 100)

预期只取 99 行，却全表扫了 100 万行建哈希表。重建索引后：

sql
CREATE INDEX idx_orders_user_id ON orders (user_id);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.id < 100;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=... rows=1 width=8) (actual time=0.512..0.513 rows=1 loops=1)
   ->  Nested Loop  (cost=... ) (actual time=0.041..0.452 rows=99 loops=1)
         ->  Index Scan using users_pkey on users u  (cost=... ) (actual time=0.018..0.042 rows=99 loops=1)
               Index Cond: (id < 100)
         ->  Index Only Scan using idx_orders_user_id on orders o  (cost=... ) (actual time=0.010..0.004 rows=5 loops=99)
               Index Cond: (user_id = u.id)
               Heap Fetches: 0

同样是 99 行结果，改为 Nested Loop 后执行时间从约 200ms 降到不到 1ms。

实战示例 ​

下面用完整流程定位并修复一个连接性能问题。

sql
-- 步骤 1：记录一个"慢查询"基线
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.city,
  p.category,
  count(DISTINCT o.id)         AS orders,
  sum(i.qty * i.price)         AS gmv
FROM users       AS u
JOIN orders      AS o ON o.user_id = u.id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.created_at >= DATE '2026-03-01'
GROUP BY u.city, p.category
ORDER BY gmv DESC;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Sort  (cost=... rows=...) (actual time=4218.552..4218.558 rows=12 loops=1)
   ->  HashAggregate  (cost=... ) (actual time=4218.331..4218.412 rows=12 loops=1)
         ->  Hash Join  (cost=... ) (actual time=... rows=1000000 loops=1)
               ...
               ->  Seq Scan on orders o  (cost=... ) (actual time=... rows=1000000 loops=1)
                     Filter: (created_at >= '2026-03-01'::date)
                     Rows Removed by Filter: 0
 Execution Time: 4218.984 ms
sql
-- 步骤 2：确认过滤条件的选择性（本例过滤掉了 0 行，条件本身不具备选择性）
SELECT
  count(*)                                        AS total,
  count(*) FILTER (WHERE created_at >= DATE '2026-03-01') AS matched
FROM orders;
text
  total  | matched
---------+---------
 1000000 | 1000000
sql
-- 步骤 3：把选择性更强的条件放到驱动侧，减少中间结果
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.city,
  p.category,
  count(DISTINCT o.id) AS orders,
  sum(i.qty * i.price) AS gmv
FROM products    AS p
JOIN order_items AS i ON i.product_id = p.id
JOIN orders      AS o ON o.id = i.order_id
JOIN users       AS u ON u.id = o.user_id
WHERE p.category = 'storage'        -- 选择性更强
GROUP BY u.city, p.category;
text
 Execution Time: 1284.117 ms        -- 相比 4218ms 明显改善
sql
-- 步骤 4：为高频过滤列建立部分索引
CREATE INDEX idx_order_items_product_storage
  ON order_items (product_id)
  WHERE product_id <= 5000;

-- 若过滤集中在少数商品，部分索引体积远小于全列索引
SELECT
  pg_size_pretty(pg_relation_size('idx_order_items_product'))          AS full_idx,
  pg_size_pretty(pg_relation_size('idx_order_items_product_storage'))  AS partial_idx;
text
 full_idx | partial_idx
----------+-------------
 65 MB    | 26 MB
sql
-- 步骤 5：用 CTE 提前收窄数据，避免大表参与连接
EXPLAIN (ANALYZE, BUFFERS)
WITH storage_orders AS (
  SELECT o.id, o.user_id
  FROM orders      AS o
  JOIN order_items AS i ON i.order_id = o.id
  JOIN products    AS p ON p.id = i.product_id
  WHERE p.category = 'storage'
)
SELECT u.city, count(*) AS orders
FROM storage_orders AS s
JOIN users AS u ON u.id = s.user_id
GROUP BY u.city;
-- PostgreSQL 12+ 默认内联非递归 CTE，因此该写法不会额外物化
sql
-- 步骤 6：核对索引是否被真正使用
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname IN ('orders', 'order_items')
ORDER BY idx_scan DESC;
text
        indexrelname         | idx_scan | idx_tup_read
-----------------------------+----------+--------------
 orders_pkey                 |  1240812 |      1240830
 idx_orders_user_id          |    98122 |      4906100
 idx_order_items_order_id    |   100032 |     12003840
 idx_order_items_product     |     1024 |       501760

idx_scan = 0 的索引即为"从未被使用"，应评估删除以节省写入开销。

注意事项 ​

  • 不要用 enable_* 参数"优化"生产查询。 它们是诊断开关，不是调优手段。
sql
-- 错误！生产环境禁用连接算法会让所有查询变慢
SET enable_hashjoin = off;

-- 正确：仅用于验证假设，用完立即恢复
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_nestloop;
  • 不要在多对多连接上直接聚合。 两个一对多连接叠加会产生 N×M 行，聚合值成倍放大。
sql
-- 错误！orders 与 order_items、products 两重展开导致金额重复
SELECT sum(i.qty * i.price) FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products    p ON p.id = i.product_id;

-- 正确：先聚合到最细粒度，再向上汇总
WITH line AS (
  SELECT o.id AS order_id, sum(i.qty * i.price) AS amount
  FROM order_items i
  JOIN orders o ON o.id = i.order_id
  GROUP BY o.id
)
SELECT sum(amount) FROM line;
  • 不要在连接列上做类型转换。 任一侧加显式或隐式转换都会让索引失效。
sql
-- 错误！左侧转换后无法使用 users.id 的索引
SELECT * FROM users u JOIN orders o ON u.id::text = o.order_no;

-- 正确：保持两侧类型一致，转换放在常量侧
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.user_id = 42;
  • 不要忽略 loops 的乘积。 Nested Loop 内表 actual rows=1 loops=100000 意味着内表被探测 10 万次，实际工作量应是两者相乘。
  • 不要在小表驱动时强求 Hash Join。 Nested Loop 支持早停，配 LIMIT 时通常更快。
  • 不要在 WHERE 里对驱动表列套函数。 WHERE date_trunc('day', o.created_at) = ... 会让过滤条件无法走索引。
  • 不要盲目相信 rows=。 估算与实际差一个数量级时，先 ANALYZE，再考虑提高列统计目标。

最佳实践 ​

  1. 为每个连接列建索引，外键列优先。
sql
CREATE INDEX idx_orders_user_id       ON orders (user_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product  ON order_items (product_id);
  1. 把选择性最高的过滤条件放在驱动侧。 让优化器在小结果集上启动连接，是减小中间结果最直接的手段。
sql
-- 用 CTE 或子查询先收窄，再连接
WITH recent AS (
  SELECT id, user_id FROM orders
  WHERE created_at >= DATE '2026-03-01' AND status = 'paid'
)
SELECT u.city, count(*) FROM recent r JOIN users u ON u.id = r.user_id GROUP BY u.city;
  1. 确认 work_mem 是否够用。 出现 Batches > 1 时优先在会话级调大，而不是全局调大。
sql
SET work_mem = '64MB';     -- 会话级，仅影响当前连接

  1. 用 EXPLAIN (ANALYZE, BUFFERS) 对比优化前后。 关注 Execution Time、Batches、loops、Rows Removed by Filter 四个指标。

  2. 用半连接/反连接替代 JOIN + DISTINCT。 EXISTS 不复制右表列，避免了去重开销。

sql
-- 不推荐：先连接再多行去重
SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id = u.id;

-- 推荐：半连接，找到即停
SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
text
                              QUERY PLAN
-----------------------------------------------------------------------
 Hash Semi Join  (cost=... ) (actual time=... rows=200000 loops=1)
   Hash Cond: (u.id = o.user_id)
   ->  Seq Scan on users u  (cost=... )
   ->  Hash  (cost=... )
         ->  HashAggregate  (cost=... )
               Group Key: o.user_id
               ->  Seq Scan on orders o  (cost=... )
  1. 用反连接替代 NOT IN。 NOT IN 遇到 NULL 会返回空结果，且优化器无法把它转成 Anti Join。
sql
-- 错误！若子查询出现 NULL，结果恒为空
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- 推荐：NOT EXISTS 可被转为 Anti Join，且不受 NULL 影响
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  1. 控制连接表数量。 表数超过 geqo_threshold 后优化器改用遗传算法，计划质量下降，应用 CTE 分步处理。
sql
SHOW geqo_threshold;              -- 默认 12
-- 复杂报表拆分为多个 CTE，每个 CTE 内部连接表数控制在个位数

下一节

继续学习：标量子查询

参考链接 ​