如何修改Inner Join查询以返回表的最新记录并优化性能？

你原查询返回最早记录的核心原因是：使用GROUP BY table1.idm时，未明确指定要取每组内的最新记录，数据库会默认返回分组后的第一条记录（具体取决于存储引擎的排序规则）。你自己实现的查询虽能满足需求，但IN子查询搭配GROUP BY的组合在数据量较大时性能较差，以下是更高效的优化方案：

方案1：窗口函数实现（推荐，简洁高效） #

利用窗口函数ROW_NUMBER()直接对每个idm分组的记录按id倒序排序，取每组第一条即为最新记录：

SELECT sl, idm
FROM (
    SELECT 
        table1.sl, 
        table1.idm,
        -- 按idm分组，每组内按id倒序排，给最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY table1.idm ORDER BY table1.id DESC) AS rn
    FROM table1
    INNER JOIN table2 ON table1.idm = table2.idm
    WHERE table2.idc = 132
) t
WHERE rn = 1;

方案2：JOIN替代IN子查询 #

将获取每个idm最大id的子查询与原表做关联，避免IN子查询的性能瓶颈：

SELECT t1.sl, t1.idm
FROM table1 t1
-- 先预计算每个idm对应的最大id
INNER JOIN (
    SELECT idm, MAX(id) AS max_id
    FROM table1
    GROUP BY idm
) t_max ON t1.idm = t_max.idm AND t1.id = t_max.max_id
-- 关联table2筛选指定客户
INNER JOIN table2 t2 ON t1.idm = t2.idm
WHERE t2.idc = 132;

关键性能优化建议 #

• 给table1创建联合索引：

  CREATE INDEX idx_table1_idm_id ON table1(idm, id DESC);
  

  该索引可让分组取最大id、窗口函数排序直接使用索引，避免全表扫描。
• 给table2创建联合索引：

  CREATE INDEX idx_table2_idc_idm ON table2(idc, idm);
  

  该索引能快速筛选出指定idc的记录，提升关联效率。

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