一条统计Group By语句优化
在检查慢SQL时,发现一条统计SQL执行过慢,如下:原SQLSELECTplatform,channel,COUNT(DISTINCT(platformUserId))ascntFROM(SELECTplatform,channel,platformUser
在检查慢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
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
GROUP BY platform, channel order by null;
执行时间:
耗时55.22秒
执行计划:
结论:
默认情况下,MySQL对所有GROUP BY col1,col2...的字段进行排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER By NULL禁止排序。
参考手册:
本文出自 “贺春旸的技术专栏” 博客,请务必保留此出处