数据库优化方面的总结

1.distinct用于查询过滤时，查询出来的相应字段数据是没有重复的。

例子：select distinct(name) from emp /*--去掉重复记录--*/

2.聚合函数一般用在from前面，在后面的时候是在having 后面使用，而不在where后面使用

3.设置表的字段长度，一定要在jsp页面做限制(或者在实体上做限制)，肯定字数不能超过数据库表中字段的长度。

4.使用索引优化表查询时：

1>避免在索引列上使用not，当Oracle遇到not时，他就会停止使用索引转而执行全表扫描

2>避免在索引列上使用算法,如果索引列是函数的一部分，优化器将不在使用索引而使用全表扫描。

3>避免在索引列上使用is null和is not null.

低效:(索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效:(索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

4>注意通配符的影响：使用通配符的情况下Oracle可能会停止该索引

SELECT…FROM DEPARTMENT WHERE DEPT_CODE like ‘%123456%'（无效）。

SELECT…FROM DEPARTMENT WHERE DEPT_CODE = ‘123456'（有效）

5>避免改变索引列的类型：

当比较不同数据类型的数据时，Oracle自动对列进行简单的类型转换.在Oracle中会对数字和日期进行隐式转换

MySQL中select ...from emp where empno=’123’在Oracle中select ...from emp where empno=TO_NUMBER(’123’)幸运的是索引列不会改变。

5.除了使用索引，还有其他能减少资源消耗的办法

1>用exists替换distinct(只有在用到子查询时)

在查询时，避免在select子句中使用distinct,一般可以考虑用exist替换，exists使查询更为迅速，因为rdbms核心模块将在子查询条件一旦满足后，立刻返回结果。

eg:(低效):

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP EWHERE D.DEPT_NO = E.DEPT_NO And E.sex =man

(高效):

 SELECT DEPT_NO,DEPT_NAME FROM DEPT DWHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO And E.sex =man );

2>用(union)union all替换or(适用于索引列),或用union all代替union

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

eg:高效:

 SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALLSELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效:

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

3>用union all替换union(如果有可能的话)

4>Order by语句加在索引列，最好是主键PK上

5>避免使用耗费资源浪费的操作

有distinct,union,minus,intersect的sql语句会启动sql引擎 执行耗资源的排序(sort)功能，distinct需要执行一次排序操作，而其他的至少需要执行两次排序，一般情况，带有union,minus,intersect的sql语句都可以用其他方式重写，如果你的数据库的sort_area_size调配得好，使用union,minus,intersect也是可以考虑的，毕竟他们的可读性强

6>使用Where替代Having(如果可以的话)

优化GROUP BY: 
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多

eg:低效

SELECT JOB , AVG(SAL)FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX

高效:

SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX

7>.通常来说，如果语句能够避免子查询的 使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。具体的例子在后面的案例“一条SQL的优化过程”中

8>用instr代替like,处理速度显著提高

9>使用 exists 而不用 IN 因为 Exists 只检查行的存在，而 in 检查实际值

10>对于连续的数值能用between就不要用in或not in

eg：select id from t where num in(1,2,3)        
    select id from t where num between 1 and 3

6.关于Oracle中count(1)和count(*)的区别：

1>如果你数据表没有主键，那么count（1）比count(*)快

2>如果有主键的话，那主键(联合主键)作为count的条件也比count(*)要快

3>如果你的表只有一个字段的话那count(*)就是最快的了。

4>count(1)是聚索引，id,那肯定是count(1)快，但是差的很小的。因为count(*),自动会优化指定的哪一个字段。所以没必要去count(?),用count(*),sql会帮你优化的。

5>count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）。distinct 列名,得到的结果将是除去值为null和重复数据后的结果