高效查询：如何获取changes表中唯一组合的最新记录？

首先得明确：你当前的查询其实没完全满足需求，而且大数据量下性能确实会有问题。咱们先拆解问题，再给出靠谱的优化方案。

原查询的问题 #

你的SELECT DISTINCT ... ORDER BY changes.id DESC有两个核心问题：

1. 逻辑偏差：DISTINCT只是去除所有选中列完全重复的行，但如果同一个(firewall id, interface id, src, dest, port)组合存在多条不同changes.id的记录（也就是更新过的记录），DISTINCT会随机保留其中一行，而不是你想要的**最新（最大id）**的那条。
2. 性能瓶颈：大数据量下，DISTINCT需要对全量结果集做去重计算，再加上ORDER BY的全表排序，这两个操作都会吃掉大量CPU和内存，完全扛不住百万级数据。

优化方案 #

根据你的数据库版本，推荐两种高效实现方式，同时搭配索引优化，确保百万级数据也能快速查询。

方案1：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等现代数据库） #

窗口函数是处理这类“分组取最新/ oldest记录”场景的最优解，逻辑清晰且效率高：

SELECT 
    f.name AS firewall_name,
    i.name AS interface_name,
    c.firewall_id,
    c.interface_id,
    c.src,
    c.dest,
    c.port
FROM (
    SELECT 
        firewall_id,
        interface_id,
        src,
        dest,
        port,
        -- 按目标组合分组，每组内按id倒序排，最新的记录标记为1
        ROW_NUMBER() OVER (
            PARTITION BY firewall_id, interface_id, src, dest, port
            ORDER BY id DESC
        ) AS record_rank
    FROM changes
) c
INNER JOIN firewall f ON c.firewall_id = f.id
INNER JOIN interface i ON c.interface_id = i.id
-- 只保留每组的第一条（最新）记录
WHERE c.record_rank = 1;

方案2：子查询找最大id（兼容旧版本数据库，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用分组取最大id的方式实现：

SELECT 
    f.name AS firewall_name,
    i.name AS interface_name,
    c.firewall_id,
    c.interface_id,
    c.src,
    c.dest,
    c.port
FROM changes c
-- 先找到每个唯一组合的最大id（最新记录的id）
INNER JOIN (
    SELECT 
        firewall_id,
        interface_id,
        src,
        dest,
        port,
        MAX(id) AS latest_id
    FROM changes
    GROUP BY firewall_id, interface_id, src, dest, port
) latest_records ON c.id = latest_records.latest_id
INNER JOIN firewall f ON c.firewall_id = f.id
INNER JOIN interface i ON c.interface_id = i.id;

关键：添加索引优化 #

不管用哪种方案，必须给changes表建复合索引，否则百万级数据下还是会慢：

-- 给changes表建覆盖索引，包含分组/排序所需的所有列
CREATE INDEX idx_changes_unique_latest ON changes (firewall_id, interface_id, src, dest, port, id);

这个索引的作用是：让数据库不用扫描全表，直接从索引里就能拿到分组、排序所需的所有数据，避免回表查询，性能会提升一个量级。

另外，firewall和interface表的id作为主键，默认已经有主键索引，关联的时候效率会很高，不需要额外建索引。

总结 #

• 替换掉原有的DISTINCT + ORDER BY方案，改用上面两种逻辑正确且高效的查询
• 一定要加上推荐的复合索引，这是大数据量下性能保障的核心

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