【原创】通过explain显示using filesort来理解mysql之group by的本质

blogdaren 2015-10-21 1评论 2273人次

mysql官方手册上有段说明:

By default, MySQL sorts all GROUP BY col1, col2, … queries as if you specified ORDER BY col1, col2, … in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL

博主翻译如下:

默认情况下, mysql 在使用 group by col1,col2,... 对列进行分组的时候也也会所有的对应列就行排序,这就好比你显式的指定了 order by col1,col2... 一样. 如果你明确的指定了order by col1,col2... 语句, 尽管仍有 filesort 排序发生, 但是mysql会自行优化并且没有任何性能损失. 如果你希望使用 group by 语句的时候避免出现 filesort, 那么只需在其后追加 order by null 语句即可, 即: group by ... order by null

截图对比说明:

33.png

44.png

结论:

(1) group by本质是先分组后排序绝不是先排序后分组

(2) group by默认会出现 Using filesort, 很多场景我只需要分组后的列【即被去重的列】, 众所周知这个东东会影响查询性能, 解决方法就是 group by ... order by null

(3) group by column 默认会按照column分组, 然后根据column升序排列;  group by column order by null 则默认按照column分组,然后根据标的主键ID升序排列


#group##by##using##filesort#

版权声明:除非注明,本文由( blogdaren )原创,转载请保留文章出处。

本文链接:【原创】通过explain显示using filesort来理解mysql之group by的本质

用户评论:

2015-10-21 14:02
不错

发表评论:

您的昵称:
电子邮件:
个人主页: