SQL优化

1:选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表或者驱动表 driving table)将被最先处理.

在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表

.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.

首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

demo1

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

demo2：

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 
例如:    EMP表描述了LOCATION表和CATEGORY表的交集. 
SELECT * 
FROM LOCATION L , 
CATEGORY C, 
EMP E 
WHERE E.EMP_NO BETWEEN 1000 AND 2000 
AND E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN 
将比下列SQL更有效率 
SELECT * 
FROM EMP E , 
LOCATION L , 
CATEGORY C 
WHERE   E.CAT_NO = C.CAT_NO 
AND E.LOCN = L.LOCN 
AND E.EMP_NO BETWEEN 1000 AND 2000 

demo3: 角色表ippc_xtgl_jsgl（316条记录）  权限表ippc_xtgl_qxgl（16条记录）  角色权限表ippc_xtgl_jsqx（4912条记录）

高效：

select * from
ippc_xtgl_jsgl a,
ippc_xtgl_qxgl b,
ippc_xtgl_jsqx c
where a.zdbh = c.jsid
and b.qxbm = c.qxbh;

低效：

select * from
ippc_xtgl_jsqx c,
ippc_xtgl_jsgl a,
ippc_xtgl_qxgl b
where a.zdbh = c.jsid
and b.qxbm = c.qxbh;

2:WHERE子句中的连接顺序

 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:SELECT子句中避免使用 ‘ * ‘

 Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。 

4：减少访问数据库的次数: :
Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。 

SELECT EMP_NAME , SALARY , GRADE 
FROM EMP 
WHERE EMP_NO = 342; 
SELECT EMP_NAME , SALARY , GRADE 
FROM EMP 
WHERE EMP_NO = 291; 

SELECT A.EMP_NAME , A.SALARY , A.GRADE, 
B.EMP_NAME , B.SALARY , B.GRADE 
FROM EMP A,EMP B 
WHERE A.EMP_NO = 342 
AND    B.EMP_NO = 291; 

5：删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)例子:  
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 

6: 用TRUNCATE替代DELETE . 

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。(TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML)。 

 

mysqll表内容常见的有两种方法：一种delete，一种是truncate 。

不带where参数的delete语句可以删除mysql表中所有内容，

使用truncate table也可以清空mysql表中所有内容。效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。 

TRUNCATE table ippc_base_dq；

delete from ippc_base_dq;

8.尽量多使用COMMIT: 

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少

a.        回滚段上用于恢复数据的信息. 
b.        被程序语句获得的锁 
c.        redo log buffer 中的空间 
d.       ORACLE为管理上述3种资源中的内部花费 

 

9:减少对表的查询.

在含有子查询的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 = 604) 
Update 多个Column 例子: 
低效: 
UPDATE EMP 
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), 
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) 
WHERE EMP_DEPT = 0020; 
高效: 
UPDATE EMP 
SET (EMP_CAT, SAL_RANGE) 
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) 
FROM EMP_CATEGORIES) 
WHERE EMP_DEPT = 0020; 

10:用EXISTS替代IN，用NOT EXISTS替代NOT IN . 

 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。

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

低效: 
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’) 

 

 

SELECT … 
FROM EMP 
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
FROM DEPT 
WHERE DEPT_CAT=’A’); 
为了提高效率.改写为: 
(方法一: 高效) 
SELECT …. 
FROM EMP A,DEPT B 
WHERE A.DEPT_NO = B.DEPT
AND B.DEPT_NO IS NULL 
AND B.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’); 

11.表关联查询和in exists

在 in或者exists查询语句中，如果不涉及主键和索引，通常情况下表关联查询比in exsit效率高

4.794s

select qxbh from ippc_xtgl_jsqx where jsid in(
select jsid from ippc_xtgl_yhjs where userid =10 );   

4.660s

select qxbh from ippc_xtgl_jsqx a where EXISTS (
select jsid from ippc_xtgl_yhjs where userid =10 and jsid = a.jsid );

0.007s

select a.qxbh from ippc_xtgl_jsqx a, ippc_xtgl_yhjs b
where b.userid =10 and a.jsid = b.jsid

 demo2 下面语句表关联查询 in exists 效率差不多，zdbh是表的主键

select a.zdbh,a.xm from ippc_xtgl_yhxx a , ippc_xtgl_zzjg b
where a.jgid1 = b.zdbh
and b.zdbh < 200000

select a.zdbh,a.xm from ippc_xtgl_yhxx a where a.jgid2
in
(
select b.zdbh from ippc_xtgl_zzjg b where b.zdbh < 200000
)

select a.zdbh,a.xm from ippc_xtgl_yhxx a where EXISTS (
select b.zdbh from ippc_xtgl_zzjg b where a.jgid1 = b.zdbh );

 

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

 

13. EXISTS 和 DISTINCT: 

mysql下： 下面的sql DISTINCT 效率比EXISTS 高，可能是因为用到了ippc_xtgl_zzjg 表的主键

select DISTINCT a.jgmc, a.zdbh from ippc_xtgl_zzjg a, ippc_xtgl_yhxx b where a.zdbh = b.jgid1

select d.jgmc, d.zdbh from ippc_xtgl_zzjg d where EXISTS ( SELECT 1 FROM ippc_xtgl_yhxx e where e.jgid1 = d.zdbh);

 

mysql下：下面的sql DISTINCT 效率比EXISTS 高

SELECT DISTINCT D.DEPT_NO,D.DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO; （0.959s）

SELECT D.DEPT_NO,D.DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 1 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); （2.696s）

 

14.SQL语句用大写的;因为Oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。  

15.在Java代码中尽量少用连接符“+”连接字符串。 

16.避免在索引列上使用NOT

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

17.避免在索引列上使用计算。

WHERE子句中,如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。 

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

18.用>=替代>: 

高效:SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3 

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

19.用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” 

20.总是使用索引的第一个列:  
如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略 了索引。

 

参考资料：

https://blog.csdn.net/killer_zr/article/details/7664024

https://www.aliyun.com/jiaocheng/1106157.html