Oracle-sql查询优化（一）

一、一些sql书写规范
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建 立索引。

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

select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

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

用>=替代> 　　
//高效: 　　 SELECT * FROM EMP WHERE DEPTNO >=4 　　
//低效: SELECT * FROM EMP WHERE DEPTNO >3 　　
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定 位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

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

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

如： select id from t where num/2=100
–应改为:
select id from t where num=100*2

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

如：
select id from t where substring(name,1,3)=’abc’ - -name以‘abc’开头的id
select id from t where datediff(day,createdate,’2015-11-30’)=0 - -‘2005-1130’生成的id 
- -应改为：
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30’ and createdate<’2005-12-1’

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

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

**in和exists的区别在于：
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in，反之，如果外层的主查询记录较少，子查询的表较大，又没有索引时，使用exists；
如果查询语句使用了not in ,那么内外表都将进行全表扫描，没有用到索引；而not exists的子查询依然可以用到表上的索引，所以无论哪个表大，用not exists 都比用 not in要快。**

9.并不是所有索引对查询都有效，SQL时根据表中数据来进行查询优化的，当索引列有大量的数据重复时，SQL查询可能不太会去利用索引，如一表中有字段sex,male.female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

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

11.尽量使用数字型字段，若只含数值型信息的字段尽量不要设置为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符中每一个字符，而对于数字型而言，只需要比对一次就可以了。

12.尽量使用varchar/nvarchar代替char/nchar，因为首先，变长字段存储空间小，可以节省存储空间，其次，对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

13.任何地方都不要使用select * from t,用具体的字段列表代替“*”，不要返回用不到的任何字段。

14.选择最有效率的表名顺序
（只在基于规则的优化器中有效）
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表（基础表 driving table）将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有三个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。
15.WHERE子句中的连接顺序
ORACLE采用自上而下的顺序解析WEHRE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

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

17.用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平 衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update 语句的佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效 率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或 LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,
你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高, 但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中 增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE ,  UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要 的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.： 　　ALTER INDEX REBUILD