数据库优化的一些技巧

1、设置字段时，默认为NULL，比NOT NULL所占用空间大，所以字段设置为NOT NULL效率会高；

 

2、关键字要统一大小写，解析时可以复用，减少解析时间；oracle中用大写，解析SQL语句时，会把小写转化成大写；

 

3、WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响；

 

4、用where代替having子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序、总计等操作，如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了；

 

5、使用表的别名(Alias)：当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误；

 

6、 用EXISTS替代IN、用NOT EXISTS替代NOT IN：在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS；

 

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

 

8、避免在索引列上使用计算，WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描；

　　低效： SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 

　　高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

 

9、用>=替代>：

　　高效: SELECT * FROM  EMP  WHERE  DEPTNO >=4 

　　低效: SELECT * FROM EMP WHERE DEPTNO >3

　　两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

 

10、用UNION替换OR (适用于索引列)：

　　通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引。

　　高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 

　　低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

 

11、 用IN来替换OR：

　　低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 

　　高效: SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);

 

12、OLTP系统SQL语句必须采用绑定变量

　　select*from orderheader where changetime >'2010-10-20 00:00:01'

　　select*from orderheader where changetime >'2010-09-22 00:00:01'

　　以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量

　　select*from orderheader where changetime >@chgtime

　　@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。