SQL脚本优化

1.创建索引
一.要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
  (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=10
     union all
     select 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开头的id
     select 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)


posted @ 2019-06-20 17:35  醉城、  阅读(987)  评论(0编辑  收藏  举报