求高效SQL查询：为Table1每行匹配Table2中ms更大的前两行

优化前提：添加索引 #

由于Table2数据量巨大，先给ms字段创建索引，这是提升查询效率的核心：

CREATE INDEX idx_table2_ms ON Table2(ms);

数据库适配的查询实现 #

适合PostgreSQL/MySQL 8.0+（LATERAL JOIN） #

SELECT
    t1.ms AS t1_ms,
    t1.price,
    t1.bid AS t1_bid,
    t1.ask AS t1_ask,
    t2_1.ms AS t2_ms_1,
    t2_1.bid AS t2_bid_1,
    t2_1.ask AS t2_ask_1,
    t2_2.ms AS t2_ms_2,
    t2_2.bid AS t2_bid_2,
    t2_2.ask AS t2_ask_2
FROM Table1 t1
-- 关联Table2中符合条件的第一行
LEFT JOIN LATERAL (
    SELECT ms, bid, ask
    FROM Table2 t2
    WHERE t2.ms > t1.ms
    ORDER BY t2.ms ASC
    LIMIT 1
) t2_1 ON TRUE
-- 关联Table2中符合条件的第二行
LEFT JOIN LATERAL (
    SELECT ms, bid, ask
    FROM Table2 t2
    WHERE t2.ms > t1.ms
    ORDER BY t2.ms ASC
    OFFSET 1 LIMIT 1
) t2_2 ON TRUE;

适合SQL Server（OUTER APPLY） #

SELECT
    t1.ms AS t1_ms,
    t1.price,
    t1.bid AS t1_bid,
    t1.ask AS t1_ask,
    t2_1.ms AS t2_ms_1,
    t2_1.bid AS t2_bid_1,
    t2_1.ask AS t2_ask_1,
    t2_2.ms AS t2_ms_2,
    t2_2.bid AS t2_bid_2,
    t2_2.ask AS t2_ask_2
FROM Table1 t1
OUTER APPLY (
    SELECT TOP 1 ms, bid, ask
    FROM Table2 t2
    WHERE t2.ms > t1.ms
    ORDER BY t2.ms ASC
) t2_1
OUTER APPLY (
    SELECT TOP 1 ms, bid, ask
    FROM Table2 t2
    WHERE t2.ms > t1.ms
    ORDER BY t2.ms ASC
    OFFSET 1 ROWS
) t2_2;

效率说明 #

• 索引idx_table2_ms将Table2的筛选、排序操作从全表扫描转为索引扫描，彻底避免交叉连接带来的O(n*m)级别的海量中间数据
• LATERAL/APPLY采用逐行关联逻辑，仅为Table1的每行匹配符合条件的前2条数据，中间结果量极小，多次执行也能保持高效

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