MySQL查询优化之高效获取每个分组的最新记录_Mysql

引言

在数据分析或业务查询中，我们经常需要 “按某个字段分组，并取每组中时间最新的记录”。例如：

按 data_time 分组，取每组 insert_time 最大的记录。
按用户 id 分组，取每个用户的最新订单。

但 mysql 的 group by 默认行为可能无法直接满足需求，尤其是当查询依赖 order by 时。本文将深入探讨一种常见的 hack 方案（依赖 limit），并分析其问题，最终给出更高效、更标准的解决方案。

1. 常见问题：如何获取每个分组的最新记录？

假设我们有一个表 spider.nbs_data，结构如下：

create table spider.nbs_data (
    id bigint primary key,
    index_id varchar(20),
    data_time date,
    insert_time datetime,
    -- 其他字段...
);

需求：查询 index_id = '0000120460' 且 data_time >= '2020-12-31' 的数据，按 data_time 分组，并取每组 insert_time 最大的记录。

2. 一种常见的 hack 方案：依赖 limit 的 group by

2.1 原始 sql

select * from (
    select * 
    from spider.nbs_data 
    where index_id = '0000120460' and data_time >= '2020-12-31' 
    order by `insert_time` desc 
    limit 10000000000  -- 超大 limit 值
) t 
group by t.data_time  -- 按 data_time 分组
order by `data_time`;

2.2 为什么它能工作？

内层查询：

先筛选符合条件的数据。
按 insert_time desc 排序，确保最新记录排在前面。
limit 10000000000 强制 mysql 先执行 order by 再 group by，避免优化器打乱顺序。

外层 group by：

mysql 默认取每组的第一条记录（即 insert_time 最大的那条）。
最终按 data_time 排序输出。

2.3 问题与风险

依赖 mysql 的 group by 行为：

如果 select * 包含非分组列，可能触发 only_full_group_by 错误（mysql 5.7+ 默认启用）。
不同 mysql 版本或 sql 模式可能导致结果不一致。

性能问题：

limit 10000000000 相当于全表扫描，数据量大时性能极差。
如果数据量超过 limit 值，可能丢失数据。

代码可读性差：

limit 10000000000 是一个 hack 行为，容易让后续维护者困惑。

3. 更优方案：标准 sql 实现

方案 1：join + 子查询（兼容所有 mysql 版本）

select t1.*
from spider.nbs_data t1
join (
    select data_time, max(insert_time) as max_insert_time
    from spider.nbs_data
    where index_id = '0000120460' and data_time >= '2020-12-31'
    group by data_time
) t2 on t1.data_time = t2.data_time and t1.insert_time = t2.max_insert_time
where t1.index_id = '0000120460'
order by t1.data_time;

优点：

清晰、标准，不依赖 group by 行为。
性能较好（子查询先聚合，再 join）。
兼容所有 mysql 版本。

方案 2：row_number()（mysql 8.0+）

with ranked_data as (
    select *,
           row_number() over (partition by data_time order by insert_time desc) as rn
    from spider.nbs_data
    where index_id = '0000120460' and data_time >= '2020-12-31'
)
select * from ranked_data where rn = 1 order by data_time;

优点：

语法简洁，逻辑清晰。
性能优秀（窗口函数优化较好）。
适用于复杂分组排序场景。

缺点：

仅 mysql 8.0+ 支持。

4. 性能对比

方案	适用场景	性能	兼容性
`limit` hack	快速验证、临时查询	差（全表扫描）	所有版本
`join` + 子查询	所有 mysql 版本	优	所有版本
`row_number()`	mysql 8.0+	优	8.0+

5. 总结

避免依赖 limit 的 hack 方案：虽然能工作，但性能差、可读性低、存在风险。
推荐 join + 子查询：兼容性好，性能稳定，适合大多数场景。
mysql 8.0+ 优先用 row_number()：语法简洁，性能更优。

最终推荐 sql：

-- mysql 5.7 及以下版本
select t1.*
from spider.nbs_data t1
join (
    select data_time, max(insert_time) as max_insert_time
    from spider.nbs_data
    where index_id = '0000120460' and data_time >= '2020-12-31'
    group by data_time
) t2 on t1.data_time = t2.data_time and t1.insert_time = t2.max_insert_time
where t1.index_id = '0000120460'
order by t1.data_time;

-- mysql 8.0+
with ranked_data as (
    select *,
           row_number() over (partition by data_time order by insert_time desc) as rn
    from spider.nbs_data
    where index_id = '0000120460' and data_time >= '2020-12-31'
)
select * from ranked_data where rn = 1 order by data_time;