SQL常用技巧总结

查询优化基本准则

1、ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此 FROM 子句中写在最后的表(基础表 driving table)将被最先处理。
在FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

例如：
表 TAB1 16384 条记录
表 TAB2 1 条记录
选择 TAB2作为基础表 (最好的方法)
select count() from tab1，tab2 执行时间 0.96秒
选择 TAB2作为基础表 (不佳的方法)
select count() from tab2，tab1 执行时间 26.09

2、ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理，表之间的连接必须写在其他 WHERE 条件之前， 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。

例如：
(低效，执行时间 156.3秒)
SELECT … FROM
EMP E WHERE SAL > 50000 AND JOB =
‘MANAGER’ AND 25 < (SELECT COUNT() FROM EMP WHERE MGR=E.EMPNO);
(高效,执行时间 10.6秒)
SELECT … FROM EMP E WHERE 25 < (SELECT COUNT() FROM
EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

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

例如：
低效
SELECT TAB_NAME FROM
TABLES WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER FROM
TAB_COLUMNS WHERE VERSION = 604)
高效
SELECT TAB_NAME FROM TABLES WHERE
(TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS WHERE VERSION = 60;

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、用NOT EXISTS替代 NOT IN
在子查询中， NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下， NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历而且不能应用表的索引)。
为了避免使用 NOT IN ，我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS。

例如：
SELECT … FROM EMP WHERE DEPT_NO
NOT IN (SELECT DEPT_NO FROM DEPT
WHERE DEPT_CAT=‘A’);
为了提高效率。改写为：
SELECT …. FROM EMP E WHERE NOT EXISTS
(SELECT ‘X’ FROM DEPT D WHERE
D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);

6、用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);

7、用表连接替换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’ ;

8、避免在索引列上使用计算
WHERE 子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
这是一个非常实用的规则，请务必牢记。

举例：
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效：
SELECT … FROM DEPT WHERE SAL > 25000/12

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

举例：
低效：(这里，不使用索引)
SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0 ;
高效： (这里，使用了索引)
SELECT … FROM DEPT WHERE DEPT_CODE > 0

10、用>=替代>

举例：
如果 DEPTNO 上有一个索引，
高效： SELECT * FROM EMP WHERE DEPTNO >=4
低效：
SELECT * FROM EMP WHERE DEPTNO >3;

两者的区别在于， 前者 DBMS将直接跳到第一个 DEPT 等于4的记录而后者将首先 定位到 DEPTNO=3的记录并且向前扫描到第一个DEPT 大于3的记录。

11、<>操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描

举例： A<>0 -> A>0 OR A<0 A<>‘’ -> A>‘’

12、用(UNION)UNION ALL替换OR (适用于索引列)
通常情况下， 用 UNION替换 WHERE 子句中的 OR将会起到较好的效果。
对索引列使用 OR将造成全表扫描。注意， 以上规则只针对多个索引列有效。
如果有 column没有被索引， 查询效率可能会因为你没有选择 OR而降低。
在下面的例子中， LOC_ID 和 REGION上都建有索引。

低效： SELECT LOC_ID ， LOC_DESC ， REGION
FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
高效： SELECT LOC_ID ， LOC_DESC ， REGION
FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID ， LOC_DESC ， REGION
FROM LOCATION WHERE REGION = “MELBOURNE”

如果你坚持要用 OR， 那就需要返回记录最少的索引列写在最前面。
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率
可能会因为你没有选择OR而降低。

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

低效：
SELECT JOB ， AVG(SAL) FROM EMP GROUP by JOB HAVING JOB =
‘PRESIDENT’ OR JOB = ‘MANAGE’
高效：
SELECT JOB ， AVG(SAL) FROM EMP WHERE
JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’GROUP by JOB

使用 where 而不是 having ，where是用于过滤行的，而having是用来过滤组的，因为行被分组后，having 才能过滤组，所以尽量用 WHERE 过滤。