常用的SQL优化方法(整理)

1.创建索引

(1)在经常需要进行检索的字段上创建索引，比如要按照表字段username进行检索，那么就应该在姓名字段上创建索引，如果经常要按照员工部门和员工岗位级别进行检索，那么就应该在员工部门和员工岗位级别这两个字段上创建索引。

(2)创建索引给检索带来的性能提升往往是巨大的，因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

(3)一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

2.避免在索引上使用计算

在where字句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表查询,函数属于计算的一种,同时在in和exists中通常情况下使用EXISTS，因为in不走索引效率低：

select * from user where salary*22>11000--(salary是索引列)

效率高：

select * from user where salary>11000/22--(salary是索引列)

如果where语句中有多个字段，那么可以考虑创建组合索引。组合索引中字段的顺序是非常重要的，越是唯一的字段越是要靠前。另外，无论是组合索引还是单个列的索引，尽量不要选择那些唯一性很低的字段。比如说，在只有两个值0和1的字段上建立索引没有多大意义。

3.调整Where字句中的连接顺序

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

4.用union all替换union

当SQL语句需要union两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用union这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序，因此如果可以判断检索结果中不会有重复的记录时候，应该用union all，这样效率就会因此得到提高。

5.考虑使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是也得避免频繁创建和删除临时表，以减少系统表资源的消耗。

6.限制结果集

要尽量减少返回的结果行，包括行数和字段列数，只提取必须要的字段返回的结果越大，意味着相应的SQL语句的logical reads 就越大，对服务器的性能影响就越甚。一个很不好的设计就是返回表的所有数据

7.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

8.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

9.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

10.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10union allselect id from t where num=20

11.下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

12.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

13.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

14.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’-–name以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′)=0-–’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’select id from t where createdate >= ’2005-11-30′ and createdate < ’2005-12-1’

15.不要在 where 子句中的=左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

16.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)