如何优化Left Join获取关联表中DATA非空的最新记录查询？

需求回顾 #

已知Table1的ID，获取其关联的Table3中DATA列不为空的最新记录（以Table3的ID最大作为“最新”判断依据）。原查询因嵌套子查询和低效关联导致大数据量下性能极差，以下是优化方案：

方案一：JOIN+聚合查询（精简执行逻辑） #

将原查询中的嵌套子查询改为JOIN，提前过滤有效数据，缩小查询范围：

SELECT t1.ID, toad.NAME, toad.DATA
FROM Table1 t1
-- 用INNER JOIN替代LEFT JOIN，只保留有有效关联的记录
INNER JOIN Table2 toa ON t1.ID = toa.Table1_ID
INNER JOIN Table3 toad ON toa.Table3_ID = toad.ID
WHERE toad.DATA IS NOT NULL
  AND t1.ID = [目标Table1ID] -- 替换为你要查询的Table1 ID
  -- 直接通过JOIN获取当前Table1关联的最大有效Table3 ID
  AND toad.ID = (
      SELECT MAX(toad2.ID)
      FROM Table2 toa2
      INNER JOIN Table3 toad2 ON toa2.Table3_ID = toad2.ID
      WHERE toa2.Table1_ID = t1.ID 
        AND toad2.DATA IS NOT NULL
  )

方案二：窗口函数（逻辑清晰，适配复杂场景） #

使用ROW_NUMBER()窗口函数对符合条件的记录排序，直接取每组的第一条（最新记录），避免多次子查询：

WITH RankedValidRecords AS (
    SELECT 
        t1.ID AS Table1_ID,
        toad.ID AS Table3_ID,
        toad.NAME,
        toad.DATA,
        -- 按Table1分组，Table3 ID倒序排序，标记每条记录的排名
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY toad.ID DESC) AS record_rank
    FROM Table1 t1
    INNER JOIN Table2 toa ON t1.ID = toa.Table1_ID
    INNER JOIN Table3 toad ON toa.Table3_ID = toad.ID
    WHERE toad.DATA IS NOT NULL
      AND t1.ID = [目标Table1ID] -- 替换为目标Table1 ID
)
-- 取排名为1的记录（即每个Table1对应的最新有效Table3记录）
SELECT Table1_ID, Table3_ID, NAME, DATA
FROM RankedValidRecords
WHERE record_rank = 1;

核心性能优化措施 #

• 添加针对性索引：
  • 给Table2建立联合索引：CREATE INDEX idx_table2_t1_t3 ON Table2(Table1_ID, Table3_ID);，加速Table1到Table3的关联查询
  • 给Table3建立联合索引：CREATE INDEX idx_table3_id_data ON Table3(ID, DATA);，快速过滤DATA非空的记录并按ID排序
• 替换LEFT JOIN为INNER JOIN：需求只需要有有效关联的记录，INNER JOIN会直接过滤掉无匹配的无效数据，减少数据库扫描量
• 避免多层嵌套子查询：原查询的嵌套IN子查询会导致优化器生成低效执行计划，改用JOIN或窗口函数能让数据库更高效地处理关联逻辑

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