自连接加 NOT EXISTS 改写全表最大值关联取最新记录

业务里经常遇到这样一张表：设备状态流水 device_status，每台设备每上报一次就插一条记录，同一台设备会有多条历史行。报表要的却是「每台设备最新那一条」——也就是按 device_id 分组、取 reported_at 最大（或 id 最大）的那一行，并且要把这一行的完整字段（状态、温度、电量、上报人）都取出来，而不是只取一个聚合值。

很多人第一反应是 SELECT device_id, MAX(reported_at) FROM device_status GROUP BY device_id，但这条 SQL 只返回两列，拿不到那条记录上的其他字段。于是又绕回去写 GROUP BY 包全部列，被 ONLY_FULL_GROUP_BY 拦下；或者在子查询里再关联一次，结果写出笛卡尔积、扫全表三遍、超时告警。这篇讲的是最稳、可移植性最好的一种改写：自连接 + NOT EXISTS 反连接，把「取最新一条」翻译成「不存在比它更新的另一条」，从语义到执行计划都说得清楚。

为什么 MAX() 子查询关联容易写错

先看一张真实的建表语句，后面所有例子都基于它。注意 reported_at 上有没有索引会直接决定改写效果，这里先不加索引，后面对比。

CREATE TABLE device_status (
    id           BIGINT       NOT NULL AUTO_INCREMENT,
    device_id    INT          NOT NULL,
    status       VARCHAR(16)  NOT NULL,   -- online / offline / fault
    temperature  DECIMAL(5,2) DEFAULT NULL,
    battery      TINYINT      DEFAULT NULL,
    reported_at  DATETIME     NOT NULL,
    reporter     VARCHAR(32)  DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_device_reported (device_id, reported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

最常见的错误写法是这样的：

-- 写法 A：错误示范，取到的不是同一行
SELECT d.device_id, d.status, d.temperature, MAX(d.reported_at) AS last_at
FROM device_status d
GROUP BY d.device_id;

在 MySQL 5.7 默认开启 ONLY_FULL_GROUP_BY 之后，这条语句会直接报错：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'demo.d.status' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

如果你为了跑通把 sql_mode 改掉（很多老项目就是这么干的），结果是 status、temperature 来自组内任意一行，reported_at 是最大值，两者可能不属于同一条记录。这种「张冠李戴」的错误在测试环境很难发现，因为数据量小、顺序稳定，一上生产数据一乱就露馅。

写法 B 用子查询关联是相对正确的思路：

-- 写法 B：正确但可能很慢
SELECT d.*
FROM device_status d
JOIN (
    SELECT device_id, MAX(reported_at) AS last_at
    FROM device_status
    GROUP BY device_id
) m ON m.device_id = d.device_id AND m.last_at = d.reported_at;

它语义正确，但有两个隐患：

• 同一时刻重复上报会炸行数。如果某设备在同一秒上报两次（reported_at 精度到秒），MAX(reported_at) 会匹配出 2 行，报表里同一台设备出现两条，前端去重一阵手忙脚乱。这时需要再加一层 MAX(id) 兜底。
• 派生表无法使用索引下推。MySQL 5.6 之后虽然有了 derived merge，但带 GROUP BY 的派生表往往会被物化成临时表，大表上 Using temporary; Using filesort 跑不掉。数据到千万级，这条 SQL 就是慢查询榜常客。

自连接 + NOT EXISTS 的语义转换

核心思路只有一句话：一条记录是「最新」的，当且仅当不存在另一条同设备、上报时间更晚的记录。这是典型的反连接（anti-join），用 NOT EXISTS 表达最自然。

-- 写法 C：自连接 + NOT EXISTS
SELECT d.id, d.device_id, d.status, d.temperature, d.battery, d.reported_at, d.reporter
FROM device_status d
WHERE NOT EXISTS (
    SELECT 1
    FROM device_status n
    WHERE n.device_id   = d.device_id
      AND n.reported_at > d.reported_at
);

逐行读一遍：对 device_status 里的每一行 d，去同表里找有没有「同一台设备、上报时间更晚」的 n。找到就说明 d 不是最新，被 NOT EXISTS 过滤掉；找不到就说明没有比它更新的，它就是最新一条。整个语句只返回完整的一行，不需要再关联第二次，也不会因为 GROUP BY 丢字段。

语义等价、但性能差得多的写法是 NOT IN，这里必须点名批评：

-- 写法 D：反面教材，别在生产用
SELECT d.*
FROM device_status d
WHERE d.reported_at NOT IN (
    SELECT MAX(reported_at)
    FROM device_status
    GROUP BY device_id
);

这条 SQL 逻辑上不是「每台设备最新」，而是「上报时间不等于任何设备最大时间的行」——语义就错了。即便改成 (device_id, reported_at) NOT IN (SELECT device_id, MAX(...) ...)，MySQL 对多列 NOT IN 的优化也很差，而且一旦子查询结果里出现 NULL，整个 NOT IN 返回 UNKNOWN，结果集直接变成空。这就是那个著名的坑：

-- 假设子查询可能返回 NULL
SELECT 1 WHERE 5 NOT IN (1, 2, NULL);   -- 返回空集，不是 1

NOT EXISTS 不受 NULL 影响，因为它是存在性判断，三值逻辑在这里不会坑你。

处理同一时刻重复上报：加 id 做第二排序键

reported_at 只精确到秒，设备重连时可能一秒内连报两条。上面写法 C 会把两条都返回。真实业务里通常约定 id 自增即插入顺序，用 (reported_at, id) 做联合比较即可唯一确定最新行：

SELECT d.id, d.device_id, d.status, d.temperature, d.battery, d.reported_at, d.reporter
FROM device_status d
WHERE NOT EXISTS (
    SELECT 1
    FROM device_status n
    WHERE n.device_id = d.device_id
      AND (n.reported_at > d.reported_at
           OR (n.reported_at = d.reported_at AND n.id > d.id))
);

注意这个 OR 写法是「行值比较」的展开形式。MySQL 8.0.19 之后支持行构造器比较，可以写得更紧凑：

SELECT d.*
FROM device_status d
WHERE NOT EXISTS (
    SELECT 1
    FROM device_status n
    WHERE n.device_id = d.device_id
      AND (n.reported_at, n.id) > (d.reported_at, d.id)
);

行值比较的好处是语义一目了然，坏处是优化器可能无法把它转换成索引范围扫描。在 MySQL 8.0 上实测，行值比较形式的执行计划更容易退化成对 n 的全表扫描或索引全扫。生产环境建议用展开的 OR 形式，或者干脆确保 (device_id, reported_at, id) 上有联合索引，让两种写法都能走 ref。

执行计划验证：索引到底吃没吃上

光看 SQL 写得漂亮没用，必须 EXPLAIN。先看建表时已经带了 idx_device_reported (device_id, reported_at) 的情况：

EXPLAIN
SELECT d.id, d.device_id, d.status, d.reported_at
FROM device_status d
WHERE NOT EXISTS (
    SELECT 1 FROM device_status n
    WHERE n.device_id = d.device_id
      AND (n.reported_at > d.reported_at
           OR (n.reported_at = d.reported_at AND n.id > d.id))
);

典型输出（MySQL 8.0）：

+----+--------------+-------+------+------------------------+------------------------+---------+---------------------+------+-------------+
| id | select_type  | table | type | possible_keys          | key                    | key_len | ref                 | rows | Extra       |
+----+--------------+-------+------+------------------------+------------------------+---------+---------------------+------+-------------+
|  1 | PRIMARY      | d     | ALL  | NULL                   | NULL                   | NULL    | NULL                | 9821 | NULL        |
|  2 | DEPENDENT SUBQ | n   | ref  | idx_device_reported    | idx_device_reported    | 4       | demo.d.device_id    |    3 | Using where |
+----+--------------+-------+------+------------------------+------------------------+---------+---------------------+------+-------------+

关键几列：

• 外层 d 是 ALL 全表扫描。这是必然的——我们要输出每一台设备的最新行，本质上要把整张表过一遍。全表扫不一定是问题，问题在于扫的代价。
• 子查询 n 是 DEPENDENT SUBQUERY + ref，key_len = 4，说明只用了 device_id 这一列前缀（INT = 4 字节），reported_at 没用上索引做范围过滤，而是落到 Using where 里。原因是条件里有 OR，优化器没法把它简单拆成范围。
• DEPENDENT SUBQUERY 意味着外层每一行都要跑一次子查询。外层 9821 行，子查询平均 3 行，总代价大概 3 万次索引查找，通常几十毫秒能跑完，可以接受。

如果把索引改成 (device_id, reported_at, id)，并把条件改写成不含 OR 的单向比较形式，子查询能吃到更多索引列。但换个思路，很多时候更值得优化的是外层扫描量。比如只要「在线设备的最新状态」，加一个 WHERE d.status = 'online' 到外层，同时确保子查询里不重复这个条件（因为我们要在全部历史里找最新），语义要小心，不能想当然把过滤条件下推。

经验法则：NOT EXISTS 子查询里的条件只写「定位更新的那一条」，业务过滤条件全部留在外层。混着写虽然 SQL 短，但很容易把「在线设备最新状态」误写成「在线记录中最新的一条在线记录」，结果设备刚掉线时报表还显示在线。

同时取多台设备、限制范围时的写法

报表页往往只看某个机房或某个设备集合，这时候一定要把范围限制在外层，避免对全表做反连接：

SELECT d.id, d.device_id, d.status, d.temperature, d.reported_at
FROM device_status d
WHERE d.device_id IN (1001, 1002, 1003, 1004, 1005)
  AND NOT EXISTS (
      SELECT 1 FROM device_status n
      WHERE n.device_id = d.device_id
        AND (n.reported_at > d.reported_at
             OR (n.reported_at = d.reported_at AND n.id > d.id))
  );

-- 或者按机房关联维度表
SELECT d.id, d.device_id, dev.room, d.status, d.temperature, d.reported_at
FROM device_status d
JOIN device dev ON dev.device_id = d.device_id
WHERE dev.room = 'A-03'
  AND NOT EXISTS (
      SELECT 1 FROM device_status n
      WHERE n.device_id = d.device_id
        AND (n.reported_at > d.reported_at
             OR (n.reported_at = d.reported_at AND n.id > d.id))
  );

第二种写法里，dev.room = 'A-03' 会让 d 走上 idx_device_reported 的 device_id 前缀，或者由 dev 驱动，外层行数从几万降到几十，子查询调用次数同比减少，整体从几百毫秒掉到几毫秒。这是这类改写最实在的收益点：反连接的成本和外层行数成正比，能缩外层就先缩外层。

如果确实要一次输出全部设备（后台导出、夜间批量任务），且表已经上千万行，可以考虑分页或分批：按 device_id 区间切分，每一批单独执行上面的 SQL，用 WHERE d.device_id BETWEEN ? AND ? 圈定范围，避免单条 SQL 长时间占用连接、撑大 undo。

和窗口函数、LATERAL 的取舍

MySQL 8.0 和 PostgreSQL 都支持窗口函数，写法更短：

-- MySQL 8.0 / PostgreSQL
SELECT id, device_id, status, temperature, reported_at
FROM (
    SELECT id, device_id, status, temperature, reported_at,
           ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY reported_at DESC, id DESC) AS rn
    FROM device_status
) t
WHERE rn = 1;

这条非常清晰，但要注意它必须把整张表按 device_id 分区排序。数据量大时，排序会落盘，EXPLAIN 里能看到 Using temporary; Using filesort。有合适的 (device_id, reported_at) 索引时，优化器有时能避免完全排序，但不要指望一定如此。

PostgreSQL 还支持 LATERAL，对每个设备单独 LIMIT 1，走索引效率极高：

-- PostgreSQL
SELECT d.device_id, s.id, s.status, s.temperature, s.reported_at
FROM device d
CROSS JOIN LATERAL (
    SELECT id, status, temperature, reported_at
    FROM device_status
    WHERE device_id = d.device_id
    ORDER BY reported_at DESC, id DESC
    LIMIT 1
) s;

三者的取舍很明确：

方案	适用版本	优点	风险点
GROUP BY + 再关联	全版本	写法常见，易读	同秒重复上报会炸行；派生表物化
自连接 + NOT EXISTS	全版本（含 5.6/5.7）	语义精确、无 NULL 陷阱、可吃联合索引	外层全扫，依赖子查询次数与行数成正比
ROW_NUMBER() 窗口函数	MySQL 8.0+ / PG	SQL 最短，逻辑最清楚	大数据量排序落盘
LATERAL + LIMIT 1	PostgreSQL / Oracle	每设备一次索引查找，性能最好	MySQL 不支持（8.0.14 起的 LATERAL 仅限派生表）

结论：数据库是 MySQL 5.7、或者代码需要兼容多个数据库时，NOT EXISTS 是最稳妥的选择；已经升级到 MySQL 8.0 且数据量不大，窗口函数可读性更好；如果是 PostgreSQL，直接上 LATERAL。不要为了「看起来高级」在 5.7 上硬写窗口函数，它不支持，直接报：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '(PARTITION BY device_id ORDER BY reported_at DESC, id DESC) AS rn' at line 3

常见问题（FAQ）

Q1：加了 NOT EXISTS 之后查询反而更慢，为什么？

先看 EXPLAIN 子查询那行是不是 type = ALL。如果 n.device_id = d.device_id 用不上索引，每一行外层记录都要扫一遍子表，复杂度是 O(N²)。检查 (device_id, reported_at) 联合索引是否存在且统计信息是最新的，必要时 ANALYZE TABLE device_status;。另外确认 n.reported_at 和 d.reported_at 数据类型一致（都是 DATETIME），如果一边是 VARCHAR，隐式转换会让索引失效，EXPLAIN 里 key 列显示 NULL。

Q2：为什么结果里同一台设备出现了两条？

几乎可以肯定是同一秒内重复上报，且你没有加 id 作为第二排序键。补上 (n.reported_at > d.reported_at OR (n.reported_at = d.reported_at AND n.id > d.id)) 即可。另一种可能是 id 不是自增（比如用了 UUID 或者手工指定），那么「最新」的定义本身就有歧义，需要业务方明确以哪个字段为准。

Q3：NOT EXISTS 和 LEFT JOIN ... IS NULL 哪个快？

在 MySQL 和 PostgreSQL 上，优化器通常会把两者转换成同一个反连接执行计划，性能基本一致。优先选 NOT EXISTS，因为它表达的是「不存在」的意图，不容易被后来维护的人误解成普通关联。如果非要写 LEFT JOIN，记得把 WHERE n.id IS NULL 放在 WHERE 而不是 ON 里，放 ON 里就变成外连接不过滤，结果全错。

Q4：能不能只查「最近 24 小时」的最新记录？

可以，但过滤条件的位置很关键。如果业务含义是「24 小时内上报过的设备，各自的最新一条」，且最新一条一定在 24 小时内，可以把 d.reported_at >= NOW() - INTERVAL 1 DAY 放外层，同时子查询保持全历史比较（不能加时间条件，否则某设备最后一次是 3 天前、但在 24 小时窗口外，会误判）。如果业务含义是「只看 24 小时窗口内的记录里最新的那条」，那就把时间条件同时加在外层和子查询上，语义清楚、性能也最好。两种都合理，取决于报表口径，写之前先问清楚。

Q5：执行时报 ERROR 1146 (42S02): Table 'demo.device_status' doesn't exist 怎么排查？

一是库名写错或没 USE 到目标库；二是大小写敏感问题——Linux 上 lower_case_table_names=0，Device_Status 和 device_status 是两张表。用 SHOW TABLES LIKE 'device%'; 确认实际表名，别凭记忆写。

小结

把「取每台设备最新记录」写成 NOT EXISTS 反连接，本质是把聚合问题翻译成存在性判断：没有比它更新的，它就是最新的。相比 GROUP BY 再回表，它天然返回整行、不会因为同秒重复上报产生多行（加了 id 兜底之后）、不受 NULL 影响、在 MySQL 5.7 上也能跑。落地时记住三件事：联合索引 (device_id, reported_at, id) 必须有；业务过滤条件尽量放外层以缩小驱动行数；上线前 EXPLAIN 确认子查询是 ref 而不是 ALL。做到这三点，几百万行的状态流水表上这条 SQL 稳定在百毫秒内不是难事。