如何优化同表嵌套MAX子查询的旧SELECT查询语句？

原查询存在多层嵌套子查询、旧式表连接语法，且多次重复扫描TABLE_MAPPINGS表，在大数据量场景下IO开销极高，导致查询缓慢。以下是针对性的重构方案及优化建议：

一、核心意图拆解 #

原查询实际是要获取TABLE_MAPPINGS中每个MATCHCODE分组里ID最大的那条记录，再关联PERSONES和DATASOURCES表获取关联字段。

二、重构方案 #

方案1：使用窗口函数（推荐，最简洁高效） #

利用ROW_NUMBER()窗口函数仅扫描一次TABLE_MAPPINGS即可定位目标记录，避免多次嵌套扫描：

SELECT
    tm."ID",
    tm."R_PERSONES",
    tm."R_DATASOURCE",
    tm."MATCHCODE",
    d.NAME AS DATASOURCE,
    p.PDID
FROM (
    SELECT
        *,
        -- 按MATCHCODE分组，组内按ID降序排序，标记行号
        ROW_NUMBER() OVER (PARTITION BY MATCHCODE ORDER BY ID DESC) AS rn
    FROM TABLE_MAPPINGS
) tm
-- 用显式JOIN替代旧式逗号连接，优化器更易识别最优连接顺序
JOIN PERSONES p ON tm."R_PERSONES" = p.ID
JOIN DATASOURCES d ON tm."R_DATASOURCE" = d.ID
-- 取每组的第一条（即ID最大的记录）
WHERE tm.rn = 1;

方案2：用分组JOIN替代嵌套IN子查询 #

先分组获取每个MATCHCODE的最大ID，再与原表JOIN匹配，减少子查询嵌套层级：

SELECT
    tm."ID",
    tm."R_PERSONES",
    tm."R_DATASOURCE",
    tm."MATCHCODE",
    d.NAME AS DATASOURCE,
    p.PDID
FROM TABLE_MAPPINGS tm
-- 先得到每个MATCHCODE对应的最大ID
JOIN (
    SELECT MATCHCODE, MAX(ID) AS max_id
    FROM TABLE_MAPPINGS
    GROUP BY MATCHCODE
) tm_max 
    ON tm.MATCHCODE = tm_max.MATCHCODE 
    AND tm.ID = tm_max.max_id
JOIN PERSONES p ON tm."R_PERSONES" = p.ID
JOIN DATASOURCES d ON tm."R_DATASOURCE" = d.ID;

三、关键优化建议（大数据量必做） #

1. 创建复合索引加速分组/窗口查询：

   CREATE INDEX idx_tm_matchcode_id ON TABLE_MAPPINGS(MATCHCODE, ID DESC);
   

   该索引能让数据库快速定位每个MATCHCODE分组的最大ID，避免全表扫描。

2. 确保关联字段有索引：

   • 若PERSONES.ID不是主键，创建主键或唯一索引；
   • 若DATASOURCES.ID不是主键，创建主键或唯一索引；
   • 可选：给TABLE_MAPPINGS.R_PERSONES和TABLE_MAPPINGS.R_DATASOURCE创建普通索引，加速表连接：

     CREATE INDEX idx_tm_r_persones ON TABLE_MAPPINGS(R_PERSONES);
     CREATE INDEX idx_tm_r_datasource ON TABLE_MAPPINGS(R_DATASOURCE);
     

3. 修复原查询语法错误：
   原SELECT列表中存在多余逗号, ,tm."MATCHCODE"，需删除否则会触发语法报错。

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