引言：

今天同事翻看之前我写的sql时,问我我这个sql和他写的相比查询效率哪个更好。乍眼一看，竟然没看懂他写的sql,(⊙﹏⊙)b汗。仔细一看，还真是很巧妙，必须要研究研究！

所以便有了本篇内容:mysql如何先查询后分组（求每个分组的 top1）

问题重现：有这样一个需求，需要查询每个分组的某个字段最新（最大）对应的整条记录。举个栗子：假如有个员工表，有id(主键),salary(薪水),depart_id(部门id)，求出每个部门薪水最高的员工记录。

实现：

在这之前，我所知道比较简单明了的实现有下面这两种（为了简单，我创建了一个测试表，只包含排序字段和分组字段）

以下是建表语句

create table sort_group(
sort int,
gp int
)

这里并没有建立索引，具体测试时再对比建立索引时的效率。

第一种实现：

select a.sort,a.gp from (
select * from sort_group order by sort desc
) a
group by a.gp

这种实现很好理解，按照语义就是先查询后排序。但是仔细一看，可以看出一点问题。用了分组查询，查的字段却没有都进行分组（这里指的是sort字段），在一些数据库比如oracle，这段sql就会报错。mysql没有报错但是总有取巧的嫌疑。还有一个问题，就是在不同mysql版本中，运行的结果却不一致。比如本人在mysql5.6版本下运行可以达到目的，但是在5.7版本下查出的数据却不是每个分组最大的。以下是在不同的版本下测试的结果。

表中数据如下

5.6版本测试结果

5.7版本测试结果

造成如上的原因是5.7版本会把子查询的order by语句优化掉。

第二种实现，利用group_concat()函数

SELECT
SUBSTRING_INDEX(
GROUP_CONCAT(sort ORDER BY sort DESC),
',',
1
),
gp
FROM
sort_group
GROUP BY
gp

如果需要所有的字段可以考虑先查出每个分组下最大的记录对应的id,利用子查询将整条记录查出。

第三种实现:

SELECT a.* from sort_group a
left JOIN sort_group b
on a.gp = b.gp and a.sort < b.sort
where b.sort is null

这种实现利用了左连接，乍一看很神奇是不是？原理将表根据分组字段进行自连接，然后根据a.sort < b.sort过滤连接，那么连接好的记录中，右表为空时，左表中的a.sort

肯定是最大的,这样最后便得到了需求的记录。

效率:

下面测试一下在不建立索引的情况下执行效率。

为了方便模拟数据，本人写了一个存储函数模拟插入数据

create PROCEDURE random_insert (IN s int,IN g int,IN len int)
CONTAINS SQL
Begin
DECLARE i int;
set i = 0;
start transaction;
while i <= len DO
insert into sort_group(sort,gp) values(FLOOR(Rand() * s),FLOOR(Rand() * g));
set i = i + 1;
end while;
commit;
END

先测试每个组中平均有10条数据的情况，为了保证sort不重复，s值尽量大，执行以下语句：

call random_insert(1000000,10000,100000);

基于此运行3条sql,花费的时间分别是：

0.105s 0.095s 100+s(汗)

接下啦测试每组中平均有1000条的情况

call random_insert(1000000,100,100000);

0.126s 0.091s 100+s

然后我们给两个字段加上索引重复上面两次测试

0.106 0.135s 1000+s

0.101s 0.120s 100+s

从测试结果上看第三种完全不可用，不难分析原因，第三种产生了笛卡尔积，并且一个组内数量越多，产生的越多。

这里就不通过explain分析查询策略了，结果已经很明显了。

个人建议使用第二种来完成需求。当然这也不是绝对的，需要具体情况具体分析。

秒客网

mysql中的先排序后分组

引言：

实现：

效率:

相关文章