一直没有时间写分享,终于等到双十一,任何需求都不准上,这才抽出时间整理一下搜集了好几天的SQL。
前提:时间和id都是递增
需求:查出用户最近一条登录记录。(110w条)
第一种:select * from user_login_log where id in(select max(id) from user_login_log group by user_id); 耗时6.35s
第二种:select * from user_login_loga u where exists (select 1 from (select max(id) as max_id from user_login_log group by user_id) m where u.id=m.max_id);耗时3.47s
第三种:select * from user_login_log a join (select max(id) as id from user_login_log group by user_id) b on a.id=b.id;耗时3.65s
第四种:select * from user_login_log a ,(select max(id) as id from user_login_log group by user_id) b where a.id=b.id;耗时3.65s
第五种:select * from user_login_log a where 1>=(select count(1) from user_login_log b where a.user_id=b.user_id and a.id<=b.id) order by user_id ,id desc; 耗时过长(1w条耗时62.0s)
第六种:select a.* from user_login_log a left join user_login_log b on a.user_id=b.user_id and a.id<=b.id group by a.id,a.user_id having count(1)=1 order by a.user_id,a.id desc;耗时过长(1w条耗时15.8s)
第七种:select *,GROUP_CONCAT(login_ip_str order by id desc) from user_login_log group by user_id;耗时200.32s
第八种:
set @customer_no=0,@row_number=0;select * from(select (@row_number:=CASE WHEN @customer_no = user_id THEN @row_number + 1 ELSE 1 END) AS num, @customer_no:=user_id AS user_id, id, login_ip_str,created_date FROM user_login_log ORDER BY user_id,id desc)x where x.num=1;耗时25.5s
方案一:in+max()
解释:选出每个用户的最大id,然后用in查询。适用于全部数据库,但是它默认把id最大当成了时间最近,因此它使用前提是时间和id都是自增。
方案二:exists+max()
解释:选出每个用户的最大id。然后把这些id和外表做关联。这里把关联上的作为exists生效的条件。
方案三:join+max()
解释:选出每个用户的最大id。然后根据id取join全表,取出id对应的全部信息。
方案四:where+max()
解释:where联查会被优化成join查询,所以效果和方案三相同。
方案五:自关联+count()
解释:思路就是判断每一条记录,是否有比本地大的记录的条数是否为1,如果只有一条比自己大,则符合。筛选出全部记录,最后按组名和值排序。但是他进行了n次count(*)计算,因此性能极差。
方案六: 外链接+having count()
解释:两个表做链接查询,笛卡尔积,然后用haing count(1)做筛选出比当前条数大的条数。
方案七:GROUP_CONCAT+截取
解释:利用GROUP_CONCAT的排序功能,将每个用户的所有记录排序并拼接成一个字段,再截取第一项。适用于mysql,但是此方法效率极低,方法只能返回了最近一条的某个字段,如果需要返回最近一条的全部信息需要在函数中使用*,所以一般只做思路。
方案八:临时变量
解释:借助两个两个会话变量,来实现取最近一条。其实就是判断当前行user_id值是否与上一行user_id值相同,当不相同时重新编号(输出1),从而实现了分组顺序编号的功能。(case中判断条件在customer_no赋值之前),这种计算最近几条,可以规定limit来缩短时间,缩短到1s。注意,声明变量和使用变量需要在同一个SQLSession中。如果是mybatis,你可能需要配置allowMultiQueries=true。
耗时比较:方案二<方案三=方案四<方案一<方案八<方案七<方案六<方案五
1.方案五、六、七、八不仅可以实现取最近一条,还可以实现最近n条的需求。
2.Hive、Oracle、SqlServer一般用ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)实现分组取Top n的问题。
3.mysql8增加了开窗函数,可以更轻松的实现Top N的需求。
欢迎补充~!