SQL Join性能优化：基于序列关联两张订单表的查询提速

问题概述 #

表A存储时序订单数据、表B存储独立订单数据，两者的序列号保持同步（表A序列号越大，对应订单发生在表B更小序列号的记录之后）。需为表A每条记录匹配表B中紧邻其之前的订单，原通过LEAD()生成lead_sequence后做范围Join的方案逻辑可行，但性能较差。

优化方法 #

1. 采用LATERAL JOIN精准匹配（推荐） #

利用LATERAL JOIN（PostgreSQL、SQL Server等主流数据库支持）结合ORDER BY + LIMIT 1，直接为表A每条记录快速定位表B中最大的小于当前序列号的记录，避免全表范围匹配：

SELECT 
    a.*,
    b.*
FROM table_a a
LATERAL (
    SELECT *
    FROM table_b b
    WHERE b.sequence < a.sequence
    ORDER BY b.sequence DESC
    LIMIT 1
) b;

2. 基于窗口函数优化区间关联 #

如果数据库不支持LATERAL JOIN，可以通过子查询+窗口函数预计算表B的序列号区间，同时减少冗余字段查询：

SELECT 
    a.*,
    b.*
FROM table_a a
INNER JOIN (
    SELECT 
        sequence,
        LEAD(sequence, 1, 999999999999) OVER (ORDER BY sequence) AS lead_sequence,
        -- 只保留业务需要的字段，避免SELECT *
        order_id, order_content
    FROM table_b
) b 
ON a.sequence > b.sequence AND a.sequence < b.lead_sequence;

3. 强制建立序列号索引 #

为两张表的sequence字段建立单列索引，这是所有优化的基础，能让数据库快速定位符合条件的记录，避免全表扫描：

CREATE INDEX idx_table_a_sequence ON table_a(sequence);
CREATE INDEX idx_table_b_sequence ON table_b(sequence);

4. 避免冗余数据查询 #

不要使用SELECT *，只查询业务需要的字段，减少数据传输量和内存占用：

SELECT 
    a.sequence AS a_order_seq,
    a.order_amount,
    b.sequence AS b_order_seq,
    b.order_detail
FROM table_a a
LATERAL (
    SELECT sequence, order_detail
    FROM table_b b
    WHERE b.sequence < a.sequence
    ORDER BY b.sequence DESC
    LIMIT 1
) b;

5. 静态数据用物化视图预计算区间 #

如果表B数据更新不频繁，可以将序列号区间预计算到物化视图中，定期刷新即可：

CREATE MATERIALIZED VIEW mv_table_b_seq_intervals AS
SELECT 
    sequence AS start_seq,
    LEAD(sequence, 1, 999999999999) OVER (ORDER BY sequence) AS end_seq,
    order_id, order_detail
FROM table_b;

-- 为物化视图的区间字段建立联合索引
CREATE INDEX idx_mv_b_seq_range ON mv_table_b_seq_intervals(start_seq, end_seq);

之后直接关联该物化视图，性能会比实时计算lead_sequence更优。

优化核心逻辑 #

原方案的范围Join会产生大量无效的行匹配（笛卡尔积后过滤），优化后的方案要么通过LATERAL JOIN精准获取单条目标记录，要么通过预计算区间减少匹配范围，再结合索引大幅降低IO和计算开销。

内容的提问来源于stack exchange，提问作者John Roberts