sql语句调优及技巧

一、sql语言介绍

1.sql语言简介

结构化查询语言(StructuredQuery Language）最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。

2.sql语言三个组成部分

数据定义语言(DDL）：CREATE、DROP、ALTER等语句。

数据操作语言(DML）：数据查询语言（DQL），如SELECT语句；数据更新语言，如，INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。

数据控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

二、sql技巧

1.ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名, FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。

例如: 表 TAB1 16,384条记录   表 TAB2  1条记录

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

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

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

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

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

3.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(*) FROMEMP
             WHERE MGR=E.EMPNO)
AND    SAL > 50000
AND    JOB = ‘MANAGER’;

4. SELECT子句中避免使用 ‘ * ‘ 。

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

5. 使用oracle自带函数提高效率。

如使用DECODE函数来减少处理时间，可以避免重复扫描相同记录或重复连接相同的表。

6. 用TRUNCATE替代DELETE。

当删除表中的记录时,在通常情况下, 回滚段(rollbacksegments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。

7. 尽量多使用COMMIT。

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。注意：在使用COMMIT时必须要注意到事务的完整性（不能再循环中使用commit）。

8. 用Where子句替换HAVING子句。

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

HAVING 中的条件一般用于对一些集合函数的比较,如COUNT()等等. 除此而外,一般的条件应该写在WHERE子句中。

9. 使用表的别名(Alias)。

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

Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属。

10.用表连接替换EXISTS。

11.用EXISTS替换DISTINCT。

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

12.用索引提高效率。

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证。

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

13.用>=替代>。两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录（>=4和>3的比较）。

14.用UNION替换OR (适用于索引列)。

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低 。

15.用IN来替换OR。这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的。

16.避免在索引列上使用IS NULL和IS NOT NULL。

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中。

空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

17.和一般的观点相反, count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)。

18.对于复合索引，查询列必须使用前导列。

create unique index PK_ENAME on DEPT(DNAME,LOC)

select * from dept t where t.loc = 'xxx'; （没有使用索引）。

select * from dept t where t.dname = ''xxx; （使用索引）。

19.比较值与索引列的数据类型一致。（若不一致，存在隐士转换）

20.查询列与索引列次序一致。

create unique index PK_ENAME on DEPT(DNAME,LOC)

select * from dept where loc = 'BOSTON' and dname ='SALES' 隐式转换 --> select * from dept where dname ='SALES' and loc = 'BOSTON' 

21.避免对索引列进行计算。

create index IND_EMPNO on EMP (EMPNO)

select * from emp t where to_char(t.empno) = '12345'; 未使用索引。

22.比较值避免使用NULL。全表扫描。

23.用多表连接代替EXISTS子句。

24.用NOT EXISTS代替NOT IN。

25.尽量少用子查询。

26.把过滤记录数最多的条件放在最后面；

三、索引原理。