ORICAL性能优化指南

以下均为个人观点，不喜勿喷，欢迎大牛共同学习交流！

  导致系统性能出现问题从系统底层分析也就是如下三个原因：

1. CPU占用率过高，资源争用导致等待

2. 内存使用率过高，内存不足需要磁盘虚拟内存

3. IO占用率过高，磁盘访问需要等待

SQL优化方案：

一：SQL语句层面优化

（1）ORACLE的解析器按照从下到上的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

例如:

表 ac01有 16,384 条记录
表 ab01有1 条记录

选择ab01作为基础表 (好的方法)
select count(*) from ac01,ab01 执行时间0.96秒

选择ac01作为基础表 (不好的方法)
select count(*) from ab01,ac01 执行时间26.09秒

（2）ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前

例如:

(低效)
SELECT ab01.aab001,ab02.aab051
 FROM ab01,ab02
WHERE ab02.aae140=’31’
   AND ab01.aab001=ab02.aab001;

(高效)
SELECT ab01.aab001,ab02.aab051
 FROM ab01,ab02
WHERE ab01.aab001=ab02.aab001
   AND ab02.aae140=’31’;

（3）ELECT子句中避免使用 ‘ * ‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（4）尽量用union all替换union

Union会去掉重复的记录，会有排序的动作，会浪费时间。因此在没有重复记录的情况下或可以允许有重复记录的话，要尽量采用union all来关联。

（5）尽量避免使用order by

Order by需要查询后排序，速度慢影响性能，如果查询数据量大，排序的时间就很长。但我们也不能避免不使用，这样大家一定注意一点的是如果使用order by那么排序的列表必须符合索引，这样在速度上会得到很大的提升。

（6）尽量使用表的别名

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

（7）尽可能的控制夺标关联再三个以下

表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过3个。

二：索引优化

（1）用>=代替>

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

（2）避免改变索引列的类型.
当索引列的条件如果类型与数据库不匹配，则索引将无效。

例如：
不使用索引
Select * from ac01 where aac001=10001000;
使用索引
Select * from ac01 where aac001=’10001000’;

（3）避免在索引列上使用NOT或NULL（IS NULL和IS NOT NULL）
避免在索引列上使用NOT或者NULL, NOT/NULL不会使查询条件使用索引。对于!=这样的判断也是不能使用索引的，索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中
例如:

无效: (这里,不使用索引)

select *
From ac02
Where not aab019=’10’;


有效: (这里,使用了索引)
select *
From ac02
Where aab019 in (’20’,’30’);

（4）带通配符（%）的like语句
%在常量前面索引就不会被使用。

例如：
索引失效
Select * from ac01 where aac002 like ‘%210104’;
Select * from ac01 where aac002 like ‘%210104%’;

索引有效
Select * from ac01 where aac002 like ‘210104%’;

（5）避免在索引列上使用函数或运算
在索引列上使用函数或运算，查询条件都不会使用索引。

三：思路

1.SQL也是一种编程语言，也存在缓冲区，所以尽量将sql语句的查询参数使用变量，数据库中的缓存能存储的sql语句数量有限，当查询参数固定写死为不同的参数时，sql中会当作两条查询语句存储，影响了sql性能；
2.由于硬盘成本的降低，所以sql表结构设计的时候尽量要保留必要的冗余，尽量减少多表关联的查询；但是再设计表的过程中不能够所有数据放在一张表中或者过多冗余，不便于维护，平衡即可；
3.常用的查询可以使用索引以及组合索引，但是不要所有的字段都添加索引，索引也会占用磁盘的存储空间，大量不必要的索引会浪费很多磁盘空间；
4.尽量使用sql通式，防止项目一旦发生的数据库变更（比如：原来采用ORICAL，由于成本原因改用MYSQL）导致的不识别和程序崩溃；
5.尽量简化sql代码，能够拿到业务层处理的代码要处理完毕后再操作数据，避免再sql中执行各种数据类型转换和业务级别的判断；
6.尽量不要使用视图。