数据库sql优化规则

1. 选择最有效率的表名顺序 

按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当DB2处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

例如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录 

选择TAB2作为基础表 (最好的方法)

select count(*) from tab1,tab2 执行时间0.96秒 

选择TAB1作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间26.09秒

如果有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

2. WHERE子句中的连接顺序

  DB2采用自下而上的顺序解析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子句中避免使用 ‘ * ‘ 

   当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*'是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,DB2在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.

 

4. 整合简单,无关联的数据库访问 
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)例如:

SELECT NAME  FROM EMP  WHERE EMP_NO = 1234;

SELECT NAME  FROM DPT  WHERE DPT_NO = 10 ;

SELECT NAME  FROM CAT  WHERE CAT_TYPE = ‘RD';

上面的3个查询可以被合并成一个:

SELECT E.NAME , D.NAME , C.NAME  FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+)) AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+)) AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+)) AND E.EMP_NO(+) = 1234 AND D.DEPT_NO(+) = 10  AND C.CAT_TYPE(+) = ‘RD';

注：oracle :(+)外链接；左右查询，放在等号的左边就是左查询，放到右边就是右查询

        oracle : NVL函数的格式如下：NVL(expr1,expr2)  如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

        ROWID 是一个类似于rownum的伪列，用于定位数据库中一条记录的一个相对唯一地址值。使用ROWID来进行单记录定位速度是最快的

5. 尽量多使用COMMIT 

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

 

6. 用Where子句替换HAVING子句 

 

避免使用HAVING子句, HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

低效: SELECT REGION，AVG(LOG_SIZE)  FROM LOCATION  GROUP BY REGION HAVING REGION REGION != ‘SYDNEY'   AND REGION != ‘PERTH'

高效 

SELECT REGION，AVG(LOG_SIZE)  FROM LOCATION  WHERE REGION REGION != ‘SYDNEY'  AND REGION != ‘PERTH'  GROUP BY REGION

7. 减少对表的查询 
在含有子查询的SQL语句中,要特别注意减少对表的查询. 

例如:

低效 

SELECT TAB_NAME  FROM TABLES  WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS  WHERE VERSION = 604)  ANDDB_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;

 

8. 使用表的别名(Alias) 

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

 

9. 用EXISTS替代IN 

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或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')

 

10. 用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 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. 用表连接替换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' ;

 

12. 用EXISTS替换DISTINCT 

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换 

例如:

低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME  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);

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

 

13. 用>=替代> 

如果DEPTNO上有一个索引,

高效:

SELECT *  FROM EMP  WHERE DEPTNO >=4

低效:

SELECT *  FROM EMP  WHERE DEPTNO >3

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

 

14. 多个平等的索引

当SQL语句的执行路径可以使用分布在多个表上的多个索引时, DB2会同时使用多个索引并在运行时对它们的记录进行合并,检索出仅对全部索引有效的记录.在DB2选择执行路径时,唯一性索引的等级高于非唯一性索引.然而这个规则只有 当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较.这种子句在优化器中的等级是非常低的.如果不同表中两个相同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级.如果相同表中两个想同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME, FROM EMP  WHERE DEPT_NO = 20  AND EMP_CAT = ‘A';

这里,DEPTNO索引将被最先检索,然后同EMP_CAT索引检索出的记录进行合并.执行路径如下:

TABLE ACCESS BY ROWID ON EMP  AND-EQUAL INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

 

 

15. . 避免在索引列上使用计算． 

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

举例:

低效： 

SELECT … FROM DEPT  WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT  WHERE SAL > 25000/12;

 

 

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

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

举例:

低效: (这里,不使用索引)

SELECT … FROM DEPT  WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

SELECT … FROM DEPT  WHERE DEPT_CODE > 0;

需要注意的是,在某些时候, DB2优化器会自动将NOT转化成相对应的关系操作符.

NOT > to <=

NOT >= to <

NOT < to >=

NOT <= to >

 

17、归纳一下一些不使用索引的情况 
1，NOT  IN
2，NOT  BETWEEN
3，LIKE（第一个字符非%号除外，如name like '李%'） 
4，<>
5，IS  NULL  /  IS  NOT  NULL
6，查询的字段加函数 
7，多字段的组合索引（A，B，C），select * from ** where  B='33',则索引也不会用。（按前缀式规则使用索引除外，如 A='33'  and  B='33' || A='33' || A='33'  and  C='33' ） 

 

18、查询语句比较优化的写法： 
1，用db2expln 评估其执行路径
2，可能使用exist 的地方就尽量不用 IN,可以使用not  exist的地方，尽量不要用not in
3，两个表进行JION时，大表放在前面，JION字段建索引 
4，尽量用其它写法，取代NOT  IN， 如 a, b表同结构，数据量很大，则代替select * from  a  where  a.c  not  in  (select  c  from b )
的语句有 
a)select  a.*  from  a, b  where  a.c  = b.c + and  b.c  is  null（据说速度比原写法提高30倍 ） 
b)select * from a minus select a.* from a,b where a.c=b.c (速度其次)
c)select * from a where not exist(select a.* from a,b where a.c=b.c) (也不错)


5，动态 SQL 中，尽量多用 execute  immediate,

6，对于很复杂的查询语句，可以建立临时表进行缓冲（关于临时表的解释与使用，还希望同行告诉我在哪里有……） 

7，COUNT（*） 与 COUNT（某列）一样进行全表扫描 Fast  Full  Index  Scan，速度差不多 

8，经常同时存取多列，或经常使用 GROUP  BY 的SQL语句，最好对表的GROUP字段建立组合索引。组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 

9，对于字段取值单一（如性别字段只有男与女），而经常在性别上做查询，建索引应该注意

 

假设你设置了一个非常好的索引，任何傻瓜都知道应该使用它，但是DB2 却偏偏不用，那么，需要做的第一件事情，是审视你的 sql 语句。 

DB2 要使用一个索引，有一些最基本的条件： 
1， where 子句中的这个字段，必须是复合索引的第一个字段； 
2， where 子句中的这个字段，不应该参与任何形式的计算 

具体来讲，假设一个索引是按 f1, f2, f3的次序建立的，现在有一个 sql语句, where子句是 f2 = : var2,则因为 f2不是索引的第1个字段，无法使用该索引。 

第2个问题，则在我们之中非常严重。以下是从实际系统上面抓到的几个例子： 

Select jobid  from  mytabs where isReq='0' and updatedate >= 2001-7-18', 'YYYY-MM-DD'； 

………

以上的例子能很容易地进行改进。请注意这样的语句每天都在我们的系统中运行，消耗我们有限的cpu和 内存资源。 

除了1，2这两个我们必须牢记于心的原则外，还应尽量熟悉各种操作符对 DB2是否使用索引的影响。这里我只讲哪些操作或者操作符会显式（explicitly）地阻止 DB2使用索引。以下是一些基本规则： 

1， 如果 f1和 f2 是同一个表的两个字段，则 f1>f2, f1>=f2, f1
2， f1 is null,  f1  is  not  null,  f1  not  in,

f1  !=,  f1  like  ‘%pattern%’;
3， Not  exist
4， 某些情况下，f1  in  也会不用索引 

对于这些操作，别无办法，只有尽量避免。比如，如果发现你的 sql 中的 in 操作没有使用索引，也许可以将  in  操作改成比较操作 +  union  all。笔者在实践中发现很多时候这很有效。