sql优化

前些天学习了下项目中的优化 其中总结了下一些常见的sql优化 

 

 

从宏观上：

Sql语法和代码结构方面

能否使用索引

 

1.SELECT子句中避免使用 *，尽量应该根据业务需求按字段进行查询

 

a) 举例：如果表中有个字段用的是clob或者是blob或者是varchar(2)4000

   这种大数据字段的话，他们的查询应该根据业务需要来进行添加切记勿直接用*

 

2.用TRUNCATE替代DELETE

a) 要点：TRUNCATE不记录日志，DELETE记录日志，所以TRUNCATE要

  快于DELETE但是一旦TRUNCATE删除但就不能进行恢复。

  mysql,sqlserver中如果id为自增类型，那么如果用TRUNCATE删除，

  则id字段再插入数据时从1开始，如果delete删除的话，

  则从删除之前的id的值继续增长。

 

3.尽量多使用COMMIT(锁)

举例：如对大数据量的分批次批量提交

   1.回滚段上用于恢复数据的信息。 

2.被程序语句获得的锁。 

   3.redo log buffer 中的空间。 

    4.Oracle为管理上述3种资源中的内部花费。 

 

4.用UNION-ALL 替换UNION

a) 因为UNION-ALL不会过滤重复数据，所执行效率要快于UNION,并且UNION可以自动排序，而UNION-ALL不会

 

5.避免在索引列上使用计算，索引不能使用函数

a) 举例: 

低效： SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 

高效:   SELECT … FROM DEPT WHERE SAL > 25000/12;

 

6.用EXISTS替代IN、用NOT EXISTS替代NOT IN： 

a) 此操作是强列推荐不使用的，因为它不能应用表的索引。

b) 推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

i. 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。

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

一个全表遍历)。

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

例子： 

（高效）SELECT * FROM EMP (基础表) 

WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT 

WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') 

 

Select e.* from emp e left join dept d on d.LOC = 'DALLAS' 

and e.deptno=d.deptno and e.empno > 0

 

(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 

AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB') 

 

7.>=优于>

a) 举例: 

> 及 < 操作符（大于或小于操作符）

   大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下也需要对其进行优化。

如一个表有100万记录，一个数值型字段A，

      A=0时，有30万条；

      A=1时，有30万条；

      A=2时，有39万条；

      A=3时，有1万记录。

    那么执行 A>2 与 A>=3 的效果就有很大的区别了，因为 A>2 时，ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

 

8.在业务密集的SQL当中尽量不采用IN操作符（会导致全表扫描）

a) 用IN写出来的SQL的优点是，比较容易写、也比较清晰易懂。

但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析，使用IN的SQL与不用IN的SQL有以下区别：

 

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录， 如果转换成功则直接采用多个表的连接方式查询。

由此可见用IN的SQL至少多了一个转换的过程。

一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

 

9.删除重复记录(oracle)

a) 最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

i. DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) 

FROM  EMP X  WHERE  X.EMPNO = E.EMPNO);

 

ii. 常规方法不用rowid移植性好

delete from emp where empno != 

(select min(empno) from emp group by empno)

 

Select * from emp where empno !=

(select min(empno) from emp group by empno  having  count(empno) > 1)

 

10.使用表的别名(Alias)： 

a) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。 

 

11.用UNION替换OR (适用于索引列)： 

a) 通常情况下, 用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”