源码-数据库调优（tuning）初接触

数据库调优对我而言一直是比较高深的内容，今晚看了慕课网的一节视频课程后，有点领悟了。

问题：使用分组子句的情况下，如果要过滤查询结果，而where/having都能使用（即，过滤条件不含分组函数），请问用where好还是having好？

答案：使用where子句可以获得更好的性能，因为where是“先过滤，再分组”，而having是“先分组，再过滤”。

口说无凭，以下源码为证：

scott_pd@ORCL> ed已写入 file afiedt.buf  1* select deptno, avg(sal) from emp where deptno=10group by deptnoscott_pd@ORCL> /    DEPTNO   AVG(SAL)---------- ----------        10       5000执行计划----------------------------------------------------------Plan hash value: 2854459865-----------------------------------------------------------------------------| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT     |      |     1 |     6 |     3   (0)| 00:00:01 ||   1 |  SORT GROUP BY NOSORT|      |     1 |     6 |     3   (0)| 00:00:01 ||*  2 |   TABLE ACCESS FULL  | EMP  |     9 |    54 |     3   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("DEPTNO"=10)统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          7  consistent gets          0  physical reads          0  redo size        605  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedscott_pd@ORCL> ed已写入 file afiedt.buf  1* select deptno, avg(sal) from emp  group by deptno having deptno=10scott_pd@ORCL> /    DEPTNO   AVG(SAL)---------- ----------        10       5000执行计划----------------------------------------------------------Plan hash value: 2138686577----------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     1 |     6 |     4  (25)| 00:00:01 ||*  1 |  FILTER             |      |       |       |            |          ||   2 |   HASH GROUP BY     |      |     1 |     6 |     4  (25)| 00:00:01 ||   3 |    TABLE ACCESS FULL| EMP  |    38 |   228 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("DEPTNO"=10)统计信息----------------------------------------------------------          1  recursive calls          0  db block gets          7  consistent gets          0  physical reads          0  redo size        605  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedscott_pd@ORCL> spool当前正假脱机至 d:/20161010_group_by_tuning.txtscott_pd@ORCL> spool out

在源码（SQL运行记录）中，我们能看到，使用where子句，共占用了9（3+3+3）个CPU单位，而having子句占用了共11（4+4+3）个CPU单位。

在数据量很庞大的情况下，两者的差距想必也很大。

懂得了这个道理，以后，在写SQL语句的时候，就可以优先使用where了。姑且将本次学习经验称之为数据库调优初接触吧。