SQL优化总结

http://www.cnblogs.com/baiyu/archive/2011/09/21/2183809.html

http://www.cnblogs.com/zengxiangzhan/archive/2009/12/04/1617186.html

 

/*常见优化建议*/

 

1、UNION会对结果进行排序，尽量用UNION ALL 替换（比如在不可能有重复记录时）

2、用>=代替>，高效: WHERE DEPTNO >=4，低效: WHERE DEPTNO >3，=号可以直接定位

3、用WHERE子句替换HAVING子句，将不需要的记录在GROUP BY之前过滤掉

4、复杂的SQL往往牺牲了执行效率，尽量通过内部函数提高SQL效率

5、连接多个表时, 使用表的别名并把别名前缀于每个列上，可以减少解析时间和理解歧义

6、视情况，可用EXISTS替代IN、用NOT EXISTS替代NOT IN
　　低效：SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')
　　高效：SELECT * FROM EMP WHERE EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

 

7、视情况，NOT IN 可以用 LEFT JOIN ... WHERE ... IS NULL 代替

 

8、考虑使用临时表或表变量存放中间结果，把连接拆成较小的几个部分逐个顺序执行，优先执行那些能够大量减少结果的连接。好处不仅仅是减少SQL SERVER优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。比如连接的where条件都是针对TableA的，可以这样拆分：
select * into #tmp from TableA where...
select * from #tmp left join TableB on #tmp.BID = TableB.BID

 a、局部临时表（#tmp）只对当前连接有效，当前连接断开时自动删除。
 b、全局临时表（##tmp）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。 
 c、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop table #tmp(或者drop table ##tmp)来显式删除

 d、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table，然后 drop table，这样可以避免系统表的较长时间锁定。

 

9、若只含数值信息的字段尽量使用数字型字段，不要设计为字符型

/*对于索引*/

1、!=将不使用索引， 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中

2、避免对索引计算，低效： WHERE SAL * 12 > 25000，高效： WHERE SAL > 25000/12

3、索引列间的OR会造成全表扫描，用UNION代替OR，如 SELECT ID FROM T WHERE NUM=10 OR NUM=20 可以这样查询：
     SELECT ID FROM T WHERE NUM=10 UNION ALL SELECT ID FROM T WHERE NUM=20

4、避免在索引列上使用IS NULL和IS NOT NULL，低效： WHERE CODE IS NOT NULL，高效： WHERE CODE >=0;

5、总是使用联合索引的第一个列， 只有在联合索引的第一个列被WHERE子句引用时，索引才生效

 

6、用<和>代替<>，<和>允许使用索引，而<>不可以

　　低效：SELECT * FROM EMP WHERE SALARY<>3000

　　高效：SELECT * FROM EMP WHERE SALARY<3000 OR SALARY>3000

 

7、尽量避免把通配符%放到词首，使用WHERE NAME LIKE 'JACK%'，这样可以使用索引

 

8、对于连续的数值，能用 between 就不要用 in：select id from t where num between 1 and 3

 

9、一定要将函数和列名分开，如变通写法：SUBSTRING(name,1,1)='m' 改为 name like 'm%'（索引扫描）

 

/*关于视图*/

 

无法在视图 'view_xxxx' 上创建索引，因为该视图未绑定到架构
解决办法：重新设计视图，在视图设计语句之前加上：ALTER VIEW View_LotteryIssue WITH SCHEMABINDING as 原视图设计语句
无法在视图 'view_xxxx' 上创建索引，因为其中包含表提示
解决办法：去掉表提示，如wiht nolock

 

一般视图（没有索引）： 聚焦特定的数据，防止敏感的列被选中；简化频繁书写操作，一些复杂的逻辑操作放在视图中完成，简化了外部查询语句；
索引视图：提高查询的效率

 

视图中不要使用ORDER BY，在查询视图时再ORDER BY。
视图性能较低，可以用存储过程代替。
不要用视图嵌套，嵌套视图增加了寻找原始资料的难度。

 

/*临时表#/##与表变量@/@@*/

 

http://www.cnblogs.com/vs2020/archive/2012/02/06/2339532.html
临时表存放在磁盘；用于较大的数据集；需要日志，可以创建索引，可以使用统计数据，可以在多会话中访问，需要锁机制。
表变量存放在内存；用于较小的数据集；不需要日志，不可以创建索引，不可以使用统计数据，不可以在多会话中访问，不需要锁机制。

 

创建临时表时用显示申明语句，而不是SELECT INTO。
当用SELECT INTO或CREATE TABLE时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取，所以千万不要在事务内使用！

如果是经常要用的临时表请使用实表，或者临时表变量。

 

/*对于ORACLE*/

1、ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表（即基础表）将被最先处理

2、在FROM子句中包含多个表的情况下，选择记录条数最少的表作为基础表

 3、如果有3个以上的表连接查询，那就需要选择交叉表（被其他表所引用的表）作为基础表

4、ORACLE采用自下而上的顺序解析WHERE子句，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

5、明确列名，避免使用SELECT *，ORACLE在解析的过程中, 会将*依次转换成所有的列名, 这个工作是通过查询数据字典完成的

 

/*查询慢的原因*/

 

1、没有索引或者没有用到索引或没有用到预想中的索引：可以指定索引 (index =索引名)

 

2、查询出的数据量过大：使用纵/横向分割表、多次查询、需求分解，降低数据量 

 

3、锁或者死锁：nolock、增加冗余列减少计算
 
4、返回了不必要的行或列：补充条件，指定返回列名

 

5、查询字段值很长：不用like，而是建全文索引。

 

6、插入大的二进制值到Image列，使用存储过程（或带参的SQL），不要用内嵌Insert来插入（需要二进制转字符串再转二进制）。
CREATE PROCEDURE p_insert AS INSERT INTO TABLE(t) VALUES (@image)

 

7、在IN后面值的列表中，将出现最多的放前面，出现最少的放后面（in多少） 

 

8、如果GROUP BY的目的不包括计算，只是分组，那么用DISTINCT更快 

 

9、批处理。一次更新多条记录比分多次更新每次一条快 

 

10、存储过程。尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。反复执行的动态SQL,可以使用临时存储过程（CREATE PROCEDURE #p_tmp）。 

 

11、锁表顺序。按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。
如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁，死锁很难被发现。

 

12、明确类型。分析WHERE SALARY > 3000，若SALARY是FLOAT类型的，则DBMS将对其进行优化为Convert(float,3000)。应在编程时直接使用3000.0。字符和整型的转换同理。