1.问题说明
SQL语句中含有临时表且临时表中含有filesort操作(eg:order by),进而对临时表进行分组(group by)会导致分组后的结果不是预想的结果。
2 SQL分析
一张t_user表,查询出en_name和hobby相同的最近一条数据;
2.1 错误代码示例
2.1.1 错误SQL语句
select
*
from
(
select
tu.id,
tu.name,
tu.en_name,
tu.sex ,
tu.age ,
tu.hobby ,
tu.create_time
from
t_user tu
order by
tu.en_name ,
tu.hobby ,
tu.create_time desc) tu_temp
group by
tu_temp.en_name,
tu_temp.hobby;
2.1.2 错误执行结果
2.2 正确代码示例
2.2.1 正确SQL语句
select
*
from
(
select
distinct(tu.id),
tu.name,
tu.en_name,
tu.sex ,
tu.age ,
tu.hobby ,
tu.create_time
from
t_user tu
order by
tu.en_name ,
tu.hobby ,
tu.create_time desc) tu_temp
group by
tu_temp.en_name,
tu_temp.hobby;
2.2.2 正确执行结果
3.原因分析
因为临时表(派生表derived table)中使用order by且使其生效,必须满足三个条件:
(1).外部查询禁止分组或者聚合
(2).外部查询未指定having,order by等
(3).外部查询将派生表或者视图作为from句中唯一指定源
不满足以上三个条件,order by会被忽略。
4.结论
一旦外部表使用了group by,那么临时表(派生表 derived table)将不会执行filesort操作(即order by 会被忽略)。使用了distinct可以使其生效,原因是因为要使派生表order by生效,派生表可以通过使用group by、limit、having、distinct等等使其生效。
5.参考资料
https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html