SQL优化：索引、游标索引的使用

SQL优化：索引、游标索引的使用：

（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。

（2）.避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：

低效：select * from dept where sal*12 >2500;

高效：select * from dept where sal>2500/12;

（3）避免在索引列上使用not和“!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not和“!=”时，就会停止使用索引而去执行全表扫描。

（4）索引列上>=代替>

低效：select * from emp where deptno > 3

高效：select * from emp where deptno >=4

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

（5）尽量使用和数据列一样的值进行操作。如果col1是数值型，那么例如where col1 = 2和where col1= '2'，则前者效率更高，因为比较字符和数值型的时候，引擎需要把两者都转化成双精度然后进行比较，这样col1上的索引就失去作用了。

（6）尽量不要用OR。一般对于OR的条件，优化器一般会使用全表扫描。

（7）减少函数的使用。

例如where col1 >= '2009-10-26' and col1 <= '2009-10-27'

和where datediff(day,col1,getdate())=0

后者因为用到函数处理.所以col1上的索引又无法使用了。

2.游标的使用

（1）少用游标：

任何一种游标都会降低SQLServer性能。有些情况不能避免，大多数情况可以避免。所以如果你的应用程序目前正在使用TSQL游标，看看这些代码是否能够重写以避免它们。如果你需要一行一行的执行操作，考虑下边这些选项中的一个或多个来代替游标的使用：

使用临时表

使用WHILE循环

使用派生表

使用相关子查询

使用CASE语句

使用多个查询

上面每一个都能取代游标并且执行更快。如果你不能避免使用游标，至少试着提高它们的速度，找出加速游标的方法。

（2）当在海量的数据表中进行数据的删除、更新、插入操作时，用游标处理的效率是最慢的，但是游标又是必不可少的，所以正确使用游标十分重要：

①.在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。

②.在insert和update维表时都加上一个条件来过滤维表中已经存在的记录，例如：

insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

ods_customer为数据源表。dim_customer为

维表。



③.使用显式的游标，因为隐式的游标将会执行两次操作，第一次检索记录，第二次检查too many rows这个exception,而显式游标不执行第二次操作。

3.常用sql优化方法：

（1）SELECT子句中避免使用‘*‘：

SqlServer在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。绝大多数情况下，不要用*来代替查询返回的字段列表，用*的好处是代码量少、就算是表结构或视图的列发生变化，编写的查询SQL语句也不用变，都返回所有的字段。但数据库服务器在解析时，如果碰到*，则会先分析表的结构，然后把表的所有字段名再罗列出来。这就增加了分析的时间。

（2）Where子句中的连接顺序：

SqlServer采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where子句的末尾。如：

低效：select * from emp e where sal>5000 and job =‘manager’and 25<(select count (*) from emp where mgr=e.empno);

高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;

（3）删除全表时，用truncate替代delete。同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml，操作立即生效，不能回滚，不触发触发器trigger。小心使用，注意备份。

注：DML(Data Manipulation Language)数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。
如insert,delete,update,select等都是DML.
DDL（data definition language）语句用语定义和管理数据库中的对象，如Create,Alter和Drop，DDL操作是隐性提交的！不能rollback。

（4）用exists替代in，可以提高查询的效率。

（5）用not exists替代not in

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用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 * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC =‘MELB')

（6）优化group by

提高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 having job=’president’or job=’manager’group by job;

（7）使用表的别名(Alias)：

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

（8）慎用SELECT DISTINCT：

DISTINCT子句仅在特定功能的时候使用，即从记录集中排除重复记录的时候。这是因为DISTINCT子句先获取结果集然后去重，这样增加SQLServer有用资源的使用。当然，如果你需要去做，那就只有去做了。

当如果你知道SELECT语句将从不返回重复记录，那么使用DISTINCT语句对SQLServer资源不必要的浪费。

（9）用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换,
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.例子：

低效:

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);

（10）sql语句用大写的；因为解析sql语句，把小写的字母转换成大写的再执行

（11）用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．　

低效：

SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

高效：

SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

（12）正确使用UNION和UNION ALL：

当使用UNION时，它相当于在结果集上执行SELECT DISTINCT。换句话说，UNION将联合两个相类似的记录集，然后搜索重复的记录并排除。如果这是你的目的，那么使用UNION是正确的。但如果你使用UNION联合的两个记录集没有重复记录，那么使用UNION会浪费资源，因为它要寻找重复记录，即使你确定它们不存在。

所以如果你知道你要联合的记录集里没有重复，那么你要使用UNION ALL，而不是UNION。UNION ALL联合记录集，但不搜索重复记录，这样减少SQLServer资源的使用，从而提升性能。

（13）多表查询时，尽量用联接语句。