- 用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 例如: 低效:
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION
GROUP BY REGION HAVING REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH'
高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION
WHERE REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' GROUP BY REGION