sql优化策略
主要关注oracle的sql相关优化
1.选择最有效率的表名顺序和WHERE子句中的连接顺序。由于oracle表扫描按有右向左的顺序,因此把关联表的数据比较少的放在右边。加快查询速度。同时where条件查询也是如此,把过虑掉数据量大的查询条件放在最右边。
2.条件查询关键字where和having。用where,不要用having,因为having是对查询结果再执行条件查询。Having主要用在group by的时候。
3.查询的时候尽量查询出有用的字段。把无用的字段摈弃掉。如一个表有name、address、qq、tel等等。而假如我们只需要name这个属性,我们selece name from table 即可。尽量不要select * from table。
4.尽量exists代替in
5.用聚合函数代替自己的sql逻辑
6.不要字段计算。如select * from table where a*2 > 2,要用 where a>1
7.关键字段上加索引。注意时间索引问题。即
8.用 >= 代替 >;select * from tab id >= 4;select * from tab id > 3;第一个效率高,原因是,DBMS 直接跳到 id 等于4的记录,而后者将首先定位到 id=3的记录并且向前扫描到第一个id大于3的记录。
9.减少访问数据库的数量
10.使用表的别名alias。
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
11.UNION的使用
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:select * from gc_dfys union select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。 select * from gc_dfys union all select * from ls_jg_dfys
数据库索引
数据库中有很多的数据,但是在关键字段上加上了索引后,并没有提高查询效率?主要是原因是索引使用不当造成的。这种情况下即时加了索引,数据库仍然后进行全表扫描,从而达不到效果。避免这种情况需要知道如下:
1.索引字段上不能用is null或is not null条件
使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。
解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
2.时间索引的使用不当
时间索引:可以建时间索引,注意事项:因为我们传入的时间是日期+时间的,即:date+time,而sql中只有sql.Date和sql.Time,所以当我们传入时间的时候,数据库会自动转换为含有日期和时间的timeStamp格式,此时在数据库中建的date类型的索引就失却了左右,我们的查询也变成了全表扫描了。而不是按索引进行扫描了。解决办法,在sql语句中转为endDate>to_date(aa,’yyyymmddd ddhh24miss’);而不能直接用endDate>?了。或者是把数据库字段改成timeStamp的日期类型。
3.使用了<>或!这样的不等于符号
下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?
有!
通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。
select * from dept shere staff_num < 1000 or dept_id > 1000;
4.使用了函数。
如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:
select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);