在检查慢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;
执行时间：

wKioL1MBhMvhzpS9AAaAfFwXDUs635.jpg

耗时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;


执行时间：
wKioL1MBhO-hysWyAAafG9IVFSM971.jpg

耗时55.22秒


执行计划：

wKioL1MBioPDXmVoAAQCe6tCaA4446.jpg


结论：
默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER By NULL禁止排序。


参考手册：

wKioL1MBhTyynTKDAANBygQJ12k373.jpg