在检查慢SQL时，发现一条统计SQL执行过慢，如下：
原SQL
|
SELECT platform, channel, COUNT(DISTINCT(platformUserId)) as cnt FROM(
SELECT platform, channel, platformUserId, MIN(insertTimestamp) as rtime
FROM tsz_user
GROUP BY platform, channel, platformUserId
) a where a.rtime >= 1392393600 and a.rtime < 1392480000
GROUP BY platform, channel;
|
执行时间：
耗时2分33秒
优化后SQL
|
SELECT platform, channel, COUNT(DISTINCT(platformUserId)) as cnt FROM(
SELECT platform, channel, platformUserId, MIN(insertTimestamp) as rtime
FROM tsz_user
GROUP BY platform, channel, platformUserId
order by null
) a where a.rtime >= 1392393600 and a.rtime < 1392480000
GROUP BY platform, channel
order by null;
|
执行时间：
耗时55.22秒
执行计划：
结论：
默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER By NULL禁止排序。
参考手册：
本文转自hcymysql51CTO博客，原文链接： http://blog.51cto.com/hcymysql/1359738，如需转载请自行联系原作者
