SQL查询简单优化 博客分类： Oracle Oracle查询优化

1、ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此 FROM 子句中写在最后的表(基础表 driving table)将被最先处理。在FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
例如：
表ceshi_xiao有969条记录，emp_xiao有14条记录。
select count(*) from emp_xiao, ceshi_xiao;（低效方法）
select count(*) from ceshi_xiao, emp_xiao;（高效方法）

注意：这里由于ceshi_xiao表记录太少差别不明显，但已经能看出差别。当表记录上百万条时，该差距会无限放大。

2、ORACLE 采用自下而上的顺序解析 WHERE 子句。
根据这个原理，表之间的连接必须写在其他 WHERE 条件之前， 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。
例如：
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO); (低效，执行时间 156.3秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';(高效,执行时间 10.6秒)
注意：在进行多表关联时，多用 Where 语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联，以使结果集数据量最小化。

3、减少对表的查询。
在含有子查询的 SQL语句中，要特别注意减少对表的查询。

4、用EXISTS替代IN。
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这 种情况下， 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。使用 exists 而不用 IN 因为 Exists 只检查行的存在，而 in 检查实际值。
例如：
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB')（低效）
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB') （高效）
用 IN 的 SQL 性能总是比较低，原因是：对于用 IN 的 SQL 语句 ORACLE 总是试图将其转换成多个表的连接，如果转换不成功则先执行 IN里面的子查询，再查询外层的表记录如果转换成功就转换成多个表的连接。因此 不管理怎么，用 IN 的 SQL 语句总是多了 一个转换的过程。因此在业务密集的SQL当中尽量不采用IN操作符。

5、用EXISTS替换DISTINCT。
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT 子句 中使用 DISTINCT. 一般可以考虑用 EXIST 替换。
例如：SELECT DISTINCT DEPT_NO,DEPT_N
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO（低效）
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X'
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);（高效）

6、用表连接替换EXISTS。
通常来说 ，采用表连接的方式比 EXISTS 更有效率。
例如：
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X'
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A');
为了提高效率。改写为：
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A' ;

7、避免在索引列上使用计算。
WHERE 子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。这是一个非常实用的规则，请务必牢记。
例如：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000; （低效）
SELECT …
FROM DEPT
WHERE SAL > 25000/12; （高效）

8、避免在索引列上使用NOT。
通常，我们要避免在索引列上使用 NOT，NOT 会产生在和在索引列上使用函数相同 的影响。当ORACLE“遇到”NOT，他就会停止使用索引转而执行全表扫描。

9、不使用<>、!=、~=、^=操作符。
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
a <> 0 ==> a > 0 or a < 0

10、用>=替代>。
SELECT *
FROM EMP
WHERE DEPTNO >3（低效）
SELECT *
FROM EMP
WHERE DEPTNO >=4（高效）
两者的区别在于， 前者 DBMS将直接跳到第一个 DEPT 等于 4的记录而后者将首先定位到 DEPTNO=3的记录并且向前扫描到第一个 DEPT 大于 3的记录。

11、不使用like 操作符。
遇到 需要用到 LIKE 过滤的SQL语句，完全可以用 instr 代替，处理速度将显著提高。

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

13、优化GROUP BY。
提高 GROUP BY 语句的效率， 可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。
例如：
SELECT JOB ， AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'（低效）
SELECT JOB ， AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'GROUP by JOB（高效）
使用 where 而不是 having ，where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having 才能过滤组，所以尽量用 WHERE 过滤。

14、避免改变索引列的类型。
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换。

15、SQL书写的影响。
同一功能同一性能不同写法SQL的影响。
例如：
如一个SQL在A程序员写的为select * from zl_yhjbqk
B程序员写的为select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为select * from DLYX.ZLYHJBQK（大写表名）
D程序员写的为select *  from DLYX.ZLYHJBQK（中间多了空格）
四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

总结：
1).应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
2).应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3).应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
4).in 和 not in 也要慎用，否则会导致全表扫描。
5).在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
6).任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。