数据库优化策略

数据库优化策略

 

 

SQL语句执行步骤：

解析---->优化---->生成执行计划---->保存到缓冲中---->执行

 

优化1：

对每一条sql语句严格按照规定格式书写，尽量语句格式相同。不要大小混写，以达到代码重用。

select * from emp;

select *   from emp;  两条不同

 

优化2：

在使用变量时，变量名应相同，达到共用。

:BIN.PP = 20

Select * from emp where empno =: BIN.PP;

:BIN.XX = 20

Select * from emp where empno = :BIN.XX;

 

优化3：

在查找中，根据索引来查找。Rownum  查找有列表查询

索引分类：B*聚集索引，按照树结构存储数据；位图索引；反索引；

索引使用原则：在大表(多字段)上面建立索引；在where子句或者链接查询条件中建立索引；在高基数列上建立B*索引，在低基数列上建立位图索引。

 

优化4：

索引列不要做任何运算，否则会失效。

Select * from emp where empno > 200/10;  优化之后如下：

Select * from emp where empno > 20;

Select * from emp where empno*10 > 200;  有运算，索引会失效

 

优化5：

不要将NULL值包含在索引中，否则会失效；

Select * from emp where empname like “XXXX”  优化之后变成下面语句：

Select * from emp where empname = “XXX”;

In(‘A’, ‘B’, ‘C’)优化成select * from emp where empname = ‘A’ or empname = ‘B’ or

empname = ‘C’;

 

优化6：

In和exists在子查询时使用exists效率高于in

 

优化7：

尽量避免使用子查询(嵌套查询)，相当于for循环，如果实在避免不了，应当过滤掉多余的列，使表的结构尽可能的少。

 

优化8：

尽量少使用困难的正则表达式；采用链接查询时，应当有充分的链接条件。

 

优化9：

能使用存储过程的，应尽量使用。

批处理并不能提高效率，只是操作方便，在存储过程使用中，尽量使用临时表，不使用本体表。

尽量避免全表查询(如使用rowID)

 

优化10：

使用基础表进行单表查询；对于from子句，是从右向左解析，所以应该将行数少的放在最右边，最右边的表成为基础表

eg：table  A  1100条数据    table  B  100条数据

select count(*) from B,A;  低效

select count(*) from A, B;  高效

在基础表查询中，应该以交叉表作为基础表。

 

优化11：

对where子句，从下到上进行解析

eg：select count(*) from emp e where sal > 5000 and job = “manager” and 25 < (select count(*) from emp where e.age = e.empno);  把可以过滤掉的应该写在where的后端select count(*) from emp where e.mgr = e.empno) and sql > 5000 and job = “manager”;

 

优化12：

尽可能避免使用*，以免到数据字典查找；

Select count(empno);   高效

Select count(*);   

Select count(1);    低效

 

优化13：

尽量减少DB访问次数；hql语句的优化

 

优化14：

使用decode()减少处理时间

Select empno, decode(empname, null, ‘ok’, ‘no’) as XX from emp;也可以用于group by；order by 子句；高效删除重复记录：

Delete from emp e where e.rowid > (select min(X..rowid) from emp X where X.empno = e.empno);

Truncate代替delete；用于全面查询，不能回滚；更加高效。

 

优化15：

尽量使用commit，清空缓存；

 

优化16：

将having子句写到where里面，多表时最好不要使用having子句。

On比where块，where比having块；

查询过程尽可能短，结果尽可能少；

 

优化17：

多用表的别名，更加高效；

No exists代替not in, exists代替in, >=代替>

Not in 会进行全表查询，导致索引无效。

eg：>=5代替>4

用union并集代替or，or会使索引列无效，会进行全表查询；如无法替换应该用in来代替or；避免在索引列使用is  null和is  not  null

 

优化18：

组合索引时，使用组合索引的第一列；

用union _all来代替union

用where来代替order  by

 

优化19：

避免改变索引列的类型

eg：select * from emp where empno = ‘123’;

select * from emp where empno = 6000;

!=不能引发索引；  + ||也会使索引无效

相同的索引列，不能进行+ - * / % 操作，会使索引无效；

 

优化20：

如果检索数目不超过30%，索引无效；

 

优化21：

避免使用消耗资源的操作：distinct  untion  minus  intersect  order by

 

优化22：

在分组group  by之前，把相关记录过滤掉；干掉having子句。