475万条记录的设备历史表查询超时，求SQL优化方案

针对475万条数据的场景，你当前的SQL用了相关子查询，每条记录都要单独执行一次MAX查询，开销拉满，这就是全量查询超时的核心原因。以下是几种高效的优化方案：

方案1：使用窗口函数（通用型，支持绝大多数现代数据库） #

用ROW_NUMBER()窗口函数批量分组排序，一次性计算出每个设备的最新记录，避免重复子查询：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY FIELD_A ORDER BY DateFIELD_B DESC) AS rn
    FROM TABLE_A
) t
WHERE rn = 1;

窗口函数会在一次扫描中完成所有分组和排序计算，比原语句的逐条子查询效率提升非常明显。

方案2：先聚合再关联（兼容旧版数据库） #

先一次性聚合出每个设备的最新日期，再和原表关联获取完整记录，只做一次聚合操作：

SELECT t1.*
FROM TABLE_A t1
JOIN (
    SELECT FIELD_A, MAX(DateFIELD_B) AS max_date
    FROM TABLE_A
    GROUP BY FIELD_A
) t2 ON t1.FIELD_A = t2.FIELD_A AND t1.DateFIELD_B = t2.max_date;

这个方案把原语句的N次子查询减少为1次聚合，大幅降低数据库计算量。

方案3：数据库专属语法（PostgreSQL） #

PostgreSQL支持DISTINCT ON语法，写法更简洁，执行效率也很高：

SELECT DISTINCT ON (FIELD_A) *
FROM TABLE_A
ORDER BY FIELD_A, DateFIELD_B DESC;

它会按FIELD_A分组，直接取每组中DateFIELD_B最大的第一条记录。

索引优化（关键补充） #

确保你建的是复合索引：

CREATE INDEX idx_device_date ON TABLE_A (FIELD_A, DateFIELD_B DESC);

这个索引能让数据库直接通过索引完成分组、排序和MAX计算，无需扫描全表数据。如果之前只建了单个字段的索引，优化效果会大打折扣。

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