如何将前序查询结果用于后续查询WHERE子句以优化SQL性能

问题背景 #

原业务流程分两步执行，效率极低：

1. 先查询100条参数记录：

SELECT character_id, foe_id, location_id, date_time, damage, points 
FROM events 
ORDER BY date_time DESC 
LIMIT 100

2. 循环将每条记录的参数代入下方复杂SQL执行：

SELECT *, A1+B1-C1-D1-E1-F1+G1+H1 AS A2 
FROM 
    (SELECT * 
     FROM 
         (SELECT id_, cnt_7, date_diff_7 , NTH_VALUE (A0,1) OVER() AS A1, NTH_VALUE (A0,2) OVER() AS B1, NTH_VALUE (B0,1) OVER() AS C1, NTH_VALUE (B0,2) OVER() AS D1 
          FROM 
              (SELECT damage AS A0, points AS B0, RootQuery_7.id as id_, count(*) over() as cnt_7,  max(date_diff) over() as date_diff_7 
               FROM 
                   (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff 
                    FROM events 
                    WHERE character_id = 12695 
                      AND status = 1 
                      AND location_id = 818 
                      AND date_time < '2024-05-02 23:00:00' 
                    ORDER BY date_time DESC 
                    LIMIT 10 OFFSET 0) AS RootQuery_7) 
            AS BaseQuery7 LIMIT 1) wnd_1 
        JOIN (SELECT id_, cnt_8, date_diff_8 , NTH_VALUE (D0,2) OVER() AS H1, NTH_VALUE (C0,1) OVER() AS E1, NTH_VALUE (C0,2) OVER() AS F1, NTH_VALUE (D0,1) OVER() AS G1 FROM 
            (SELECT damage AS C0, points AS D0, RootQuery_8.id as id_, count(*) over() as cnt_8,  max(date_diff) over() as date_diff_8 FROM 
                (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff FROM events WHERE foe_id = 33997 AND status = 1 AND location_id = 818 AND date_time < '2024-05-02 23:00:00' ORDER BY date_time DESC LIMIT 15 OFFSET 0)
                    AS RootQuery_8)
            AS BaseQuery8 LIMIT 1) wnd_2
    ON wnd_1.id_ <> 0 WHERE cnt_7 >= 10 AND date_diff_7 <= 150 AND cnt_8 >= 10 AND date_diff_8 <= 150) AS layer_1

尝试合并为单条关联查询时，深层子查询的WHERE子句无法引用外层参数查询的字段，导致无法实现批量关联。

解决方案：使用LATERAL JOIN实现批量关联 #

利用LATERAL JOIN（PostgreSQL、MySQL 8.0+等主流数据库支持）允许子查询引用外层查询字段的特性，将原循环逻辑合并为单条查询，大幅提升执行效率。

改写后的完整SQL #

SELECT 
    ref.*,
    wnd_1.*,
    wnd_2.*,
    wnd_1.A1 + wnd_1.B1 - wnd_1.C1 - wnd_1.D1 - wnd_2.E1 - wnd_2.F1 + wnd_2.G1 + wnd_2.H1 AS A2
FROM 
    -- 外层参数查询（原100条记录）
    (SELECT character_id, foe_id, location_id, date_time, damage, points 
     FROM events 
     ORDER BY date_time DESC 
     LIMIT 100) AS ref
-- 关联第一个子查询（对应原wnd_1部分）
LEFT JOIN LATERAL (
    SELECT 
        id_, cnt_7, date_diff_7,
        NTH_VALUE(A0, 1) OVER() AS A1,
        NTH_VALUE(A0, 2) OVER() AS B1,
        NTH_VALUE(B0, 1) OVER() AS C1,
        NTH_VALUE(B0, 2) OVER() AS D1
    FROM (
        SELECT 
            damage AS A0, points AS B0,
            RootQuery_7.id AS id_,
            COUNT(*) OVER() AS cnt_7,
            MAX(date_diff) OVER() AS date_diff_7
        FROM (
            SELECT 
                *,
                EXTRACT(DAY FROM date_time - LAG(date_time) OVER(ORDER BY date_time)) AS date_diff
            FROM events
            WHERE 
                character_id = ref.character_id  -- 直接引用外层ref的字段
                AND status = 1
                AND location_id = ref.location_id
                AND date_time < ref.date_time
            ORDER BY date_time DESC
            LIMIT 10 OFFSET 0
        ) AS RootQuery_7
    ) AS BaseQuery7
    LIMIT 1
) AS wnd_1 ON TRUE
-- 关联第二个子查询（对应原wnd_2部分）
LEFT JOIN LATERAL (
    SELECT 
        id_, cnt_8, date_diff_8,
        NTH_VALUE(D0, 2) OVER() AS H1,
        NTH_VALUE(C0, 1) OVER() AS E1,
        NTH_VALUE(C0, 2) OVER() AS F1,
        NTH_VALUE(D0, 1) OVER() AS G1
    FROM (
        SELECT 
            damage AS C0, points AS D0,
            RootQuery_8.id AS id_,
            COUNT(*) OVER() AS cnt_8,
            MAX(date_diff) OVER() AS date_diff_8
        FROM (
            SELECT 
                *,
                EXTRACT(DAY FROM date_time - LAG(date_time) OVER(ORDER BY date_time)) AS date_diff
            FROM events
            WHERE 
                foe_id = ref.foe_id  -- 直接引用外层ref的字段
                AND status = 1
                AND location_id = ref.location_id
                AND date_time < ref.date_time
            ORDER BY date_time DESC
            LIMIT 15 OFFSET 0
        ) AS RootQuery_8
    ) AS BaseQuery8
    LIMIT 1
) AS wnd_2 ON TRUE
-- 原过滤条件
WHERE 
    wnd_1.cnt_7 >= 10 
    AND wnd_1.date_diff_7 <= 150 
    AND wnd_2.cnt_8 >= 10 
    AND wnd_2.date_diff_8 <= 150
    AND wnd_1.id_ <> 0;

关键说明 #

• LATERAL JOIN 让两个子查询可以直接引用外层ref查询的character_id、foe_id、location_id、date_time字段，完美解决深层子查询无法引用外层参数的问题。
• 原循环执行100次的逻辑合并为单条查询，数据库可以优化执行计划，减少连接开销和重复计算。
• 完全保留原SQL的所有计算逻辑和过滤条件，确保输出结果与原流程一致。

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