SQL书写优化

对查询进行优化，应尽量避免全表扫描

1、首先应考虑在 where 及 order by 涉及的列上建立索引

2、应尽量避免在 where 子句中对字段进行 null 值判断 

        select id from t where num is null      (此种写法将导致引擎放弃使用索引而进行全表扫描)	
  可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：	
          select id from t where num=0	

3、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4、in 和 not in 也要慎用

    如：select id from t where num in(1,2,3) 会导致全表扫描。

    对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 

5、 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描   

    select id from t where num=10 or num=20	
    可以这样查询：	
    select id from t where num=10	
    union all	
    select id from t where num=20	

6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：	

    select id from t where num/2=100	
    应改为:	
    select id from t where num=100*2	

7、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

应改为: 

   select id from t where name like 'abc%'

8、很多时候用 exists 代替 in 是一个好的选择：

   select num from a where num in(select num from b)	
   用下面的语句替换：	
   select num from a where exists(select 1 from b where num=a.num)	

9、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

10、用EXISTS替代IN、用NOT EXISTS替代NOT IN： 

 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下，

 使用EXISTS(或NOT ,NOT IN子句将执行一个内部的排序和合并EXISTS)通常将提高查询的效率. 在子查询中

 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).

 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 

例子： 
（高效）SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (S        ELECT ‘X’  FROM DEPT  WHERE                  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB’) 

 ( 低效 )SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT                            WHERE  LOC = ‘MELB’) 

11、用>=替代> 

     高效: 

     SELECT * FROM  EMP  WHERE  DEPTNO >=4 

    低效: 

     SELECT * FROM EMP WHERE DEPTNO >3 

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

12、用UNION替换OR (适用于索引列) 
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 
高效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 
UNION 
SELECT 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” 

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面. 

13、提高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 

14、