如何优化30次以上JOIN且无表修改权限的SQL查询性能

问题描述 #

我负责从现有表中查询大量信息，每行需要提取非常多字段。我是通过第三方软件进行操作的，因此无法编辑表、列或数据，仅能执行SELECT操作查看数据。
由于使用的是第三方软件，我的操作权限十分受限：所有信息都存储在2张表中，不知为何两张表加起来仅能访问60个列。
受限于此，我需要查询分散在两列中的员工信息，因此我在姓名/唯一值相同的条件下对其进行JOIN，这部分运行正常。
接下来我需要查询考勤信息、工作场所信息、过往任职经历等内容，这些信息都存储在独立的行中，且列名存在重叠，因此我在指定字段相等的条件下进行JOIN操作。
另外，由于是第三方软件，我没有删除条目的权限，因此我需要取每行的最新版本：信息更新时不会删除旧数据，只会插入包含该员工信息的新行。
目前我的实现方式是对两张表进行34次自连接来获取所有需要的行，该查询逻辑正确，每次都能返回正确结果，但存在性能问题：根据员工姓名不同，查询耗时从60秒到180秒不等，不符合预期。
我想咨询是否有方法可以提升该查询的速度？我已经将全连接改为INNER JOIN，也重新调整了WHERE子句的顺序来优化效率，但查询速度仍然很慢。
如前文所述，我仅能访问两张合计60列的表，无法删除或覆盖条目，也无法创建表或调整数据，就我目前所知只能通过现有方式查询。
任何帮助都将不胜感激，谢谢。

原查询SQL #

SELECT * 
      FROM table2 sm1
       INNER JOIN table2 sm2 ON sm1.col_40 = sm2.col_40
       INNER JOIN table1 im on sm1.unique_val = im.unique_val 
       INNER JOIN table1 im2 on sm2.unique_val = im2.unique_val 
       INNER JOIN table2 op ON sm1.col_25 = op.col_3 
       INNER JOIN table2 op1 ON sm2.col_37 = op1.col_3 
       INNER JOIN table2 op2 ON im2.we = op2.col_3 
       INNER JOIN table2 op3 ON im2.dem_1 = op3.col_3 
       INNER JOIN table2 op4 ON im2.dem_3 = op4.col_3 
       INNER JOIN table2 op5 ON im2.dem_5 = op5.col_3 
       INNER JOIN table2 op6 ON im2.dem_7 = op6.col_3 
       INNER JOIN table2 op7 ON im2.dem_9 = op7.col_3 
       INNER JOIN table2 op8 ON im2.mat_1 = op8.col_3 
       INNER JOIN table2 op9 ON im2.areas = op9.col_3 
       INNER join table1 opim1 on op.unique_val = opim1.unique_val 
       INNER join table1 opim2 on op1.unique_val = opim2.unique_val 
       INNER join table1 opim3 on op2.unique_val = opim3.unique_val 
       INNER join table1 opim4 on op3.unique_val = opim4.unique_val 
       INNER join table1 opim5 on op4.unique_val = opim5.unique_val 
       INNER join table1 opim6 on op5.unique_val = opim6.unique_val 
       INNER join table1 opim7 on op6.unique_val = opim7.unique_val 
       INNER join table1 opim8 on op7.unique_val = opim8.unique_val 
       INNER join table1 opim9 on op8.unique_val = opim9.unique_val 
       INNER join table1 opim10 on op9.unique_val = opim10.unique_val 
       INNER JOIN (SELECT col_40, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_40) 
       groupedSM ON groupedSM.col_40 = sm1.col_40 
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped ON grouped.col_8 = op.col_8 
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped1 ON grouped1.col_8 = op1.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped2 ON grouped2.col_8 = op2.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped3 ON grouped3.col_8 = op3.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped4 ON grouped4.col_8 = op4.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped5 ON grouped5.col_8 = op5.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped6 ON grouped6.col_8 = op6.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped7 ON grouped7.col_8 = op7.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped8 ON grouped8.col_8 = op8.col_8  
       INNER JOIN (SELECT col_8, MAX(time_added) AS MaxTime FROM table2 GROUP BY col_8) 
       grouped9 ON grouped9.col_8 = op9.col_8  
       WHERE sm1.col_3 = '*EmployeeName*' 
       AND sm2.col_3 = sm1.col_3 
       AND sm1.col_39 = sm2.col_39  
       and sm1.col_4 = 'Part1' 
       and sm2.col_4 = 'Part2' 
       and groupedSM.MaxTime = sm2.time_added 
       and im.type = im2.type
       AND opim1.ID = 188374 
       AND opim2.ID = 188374 
       AND opim3.ID = 188374 
       AND opim4.ID = 188374 
       AND opim5.ID = 188374 
       AND opim6.ID = 188374 
       AND opim7.ID = 188374 
       AND opim8.ID = 188374 
       AND opim9.ID = 188374 
       AND opim10.ID = 188374 
       AND grouped.MaxTime = op.time_added 
       AND grouped1.MaxTime = op1.time_added 
       AND grouped2.MaxTime = op2.time_added 
       AND grouped3.MaxTime = op3.time_added 
       AND grouped4.MaxTime = op4.time_added 
       AND grouped5.MaxTime = op5.time_added 
       AND grouped6.MaxTime = op6.time_added 
       AND grouped7.MaxTime = op7.time_added 
       AND grouped8.MaxTime = op8.time_added 
       AND grouped9.MaxTime = op9.time_added

优化方案 #

• 复用重复聚合逻辑：原查询重复执行了10次完全相同的col_8最新时间聚合、1次col_40最新时间聚合，数据库会重复计算全表聚合带来极大开销，可以用CTE提前计算一次结果，后续所有关联直接复用。
• 过滤条件下推缩小数据集：原查询先做全表关联再过滤目标员工数据，可在最内层就先筛选出目标员工、对应业务类型的少量行，把参与关联的数据集从全表压缩到几十条以内，后续关联开销指数级下降。
• 条件聚合替代多次自连接：这是性能提升最明显的优化点，原查询为了取不同业务类型的字段做了十几次自连接，实际上可以只关联1次表，用CASE WHEN按条件提取对应字段实现行转列，彻底避免几十次自连接的笛卡尔积开销。
• *避免SELECT ，按需提取字段：只查询实际需要的字段，减少不必要的数据读取、传输和解析开销。

优化后示例SQL #

WITH latest_col8 AS (
    -- 一次性计算所有col_8对应的最新时间，仅执行1次
    SELECT col_8, MAX(time_added) AS MaxTime 
    FROM table2 
    GROUP BY col_8
),
latest_col40 AS (
    -- 一次性计算所有col_40对应的最新时间，仅执行1次
    SELECT col_40, MAX(time_added) AS MaxTime 
    FROM table2 
    GROUP BY col_40
),
target_employee AS (
    -- 提前过滤出目标员工的核心数据，直接缩小关联范围
    SELECT 
        sm1.col_25, sm2.col_37, im2.we, im2.dem_1, im2.dem_3, 
        im2.dem_5, im2.dem_7, im2.dem_9, im2.mat_1, im2.areas
        -- 按需补充其他需要的基础字段
    FROM table2 sm1
    INNER JOIN table2 sm2 
        ON sm1.col_40 = sm2.col_40 
        AND sm2.col_3 = sm1.col_3 
        AND sm1.col_39 = sm2.col_39
        AND sm2.col_4 = 'Part2'
    INNER JOIN latest_col40 groupedSM 
        ON groupedSM.col_40 = sm1.col_40 
        AND groupedSM.MaxTime = sm2.time_added
    INNER JOIN table1 im 
        ON sm1.unique_val = im.unique_val
    INNER JOIN table1 im2 
        ON sm2.unique_val = im2.unique_val
        AND im.type = im2.type
    WHERE sm1.col_3 = '*EmployeeName*' 
        AND sm1.col_4 = 'Part1'
)
SELECT 
    te.*,
    -- 按业务类型提取对应字段，替代多次自连接
    MAX(CASE WHEN op.col_3 = te.col_25 THEN op.需要的考勤字段 END) AS 考勤字段,
    MAX(CASE WHEN op.col_3 = te.col_37 THEN op.需要的工作场所字段 END) AS 工作场所字段,
    MAX(CASE WHEN op.col_3 = te.we THEN op.需要的任职经历字段 END) AS 任职经历字段,
    -- 剩余dem_1、dem_3等字段按上述格式补充即可
FROM target_employee te
INNER JOIN table2 op
    ON op.col_3 IN (te.col_25, te.col_37, te.we, te.dem_1, te.dem_3, te.dem_5, te.dem_7, te.dem_9, te.mat_1, te.areas)
INNER JOIN latest_col8 lc8
    ON lc8.col_8 = op.col_8 
    AND lc8.MaxTime = op.time_added
INNER JOIN table1 opim
    ON op.unique_val = opim.unique_val
    AND opim.ID = 188374
GROUP BY te.所有从target_employee提取的字段

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