Sql语句的优化查询

基本sql语句的用法

1.多表查询时必须指定别名 防止新加字段有同名字段 导致报错

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

3.调整Where字句中的连接顺序
ORACLE 采用自下而上的顺序解析WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾.
select ww from dcs_accumulationgold t where t.accountingdate1=123456

4.选择最有效率的表名顺序(只在基于规则的优化器中有效) oracle的解析器按照从右到左的顺序处理from子句中的表名,因此from子句中写在最后的表(基础表 driving table)将被最先处理。在from子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表。
当oracle处理多个表时, 会运用排序及合并的方式连接它们

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

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

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

8.很多时候用 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)

9.用not exists替代not in：
select * from dept1 where not exists (select 1 from emp1 where deptno=dept1.deptno); --0.015s
select * from temp_tab where table_name not in (select table_name from temp_col); --0.031s

10.用表连接替换exists：
select emp1. from emp1 where exists (select 1 from dept1 where deptno=emp1.deptno);–18.9s
select emp1. from emp1,dept1 where emp1.deptno=dept1.deptno; --18.002s**

11.使用decode函数替代case when then
用decode替换case when then 用法
case 列名
when '原来的值 ’ then ‘需要改变的值’
when ‘原来的值’ then ‘需要改变的值’
else ‘需要改变的值’ end
select no,name,sex,college,case sex
when ‘男’ then ‘1’
when ‘女’ then ‘2’
else ‘3’ end
as 性别
from
student
decode理解如: if(){
}else if(){
}esle if(){
}else{
}
只比较一个参数时
select id,decode(传入参,比较值,equal值,not equal值)name from table;
比较多个参数时
select id,decode(传入值,比较值1,equal值1,比较值2,equal值2,比较值3,equal值3,比较值4,equal值4)name from table;

优化Sql语句

1.在索引上使用计算
在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的字段上建立索引没有多大意义。

2.在 where 子句中使用!=或<>操作符，将引擎放弃使用索引而进行全表扫描。

3.在 where 子句中对字段进行 null 值判断，将导致引擎放弃使用索引而进行全表扫描
如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

4.在 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

5.下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘%abc%’
若要提高效率，可以考虑全文检索。

6.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

7.在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

8.在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
datediff用法
如：
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′

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