数据库优化

数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，减少IO分为物理优化的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化.

逻辑优化：

1.避免在索引列上使用NOT ， 
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

 

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

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

 

3.避免在索引列上使用IS NULL和IS NOT NULL 
避免在索引中使用任何可以为空的列，ORACLE性能上将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．

低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 
高效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

 

4.注意通配符%的影响 
使用通配符的情况下Oracle可能会停用该索引

SELECT…FROM DEPARTMENT WHERE DEPT_CODE like ‘%123456%'（无效）。 
SELECT…FROM DEPARTMENT WHERE DEPT_CODE = ‘123456'（有效） 

 

5.避免改变索引列的类型.: 
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.

假设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')

 

6.索引

如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

 

7.Order By语句加在索引列，最好是主键PK上。 

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE（低效） 
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE （高效） 

 

8.使用Where替代Having（如果可以的话） 
优化GROUP BY: 
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多. 

低效: 
SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX 
高效: 
SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX 

 

9.通常来说，如果语句能够避免子查询的使用，就尽量不用子查询。

 

10. SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

 

11.sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

 

12.表设计优化 
1）分区 
2）压缩 
3）建立合适的索引 
4）设置合适的pctfree，减少行连接和行迁移 
5）设置合适的storage，控制表碎片 
6）其他等

 

13.优化sql语句 
1）减少对数据库的查询次数，即减少对系统资源的请求，使用快照和显形图等分布式数据库对象可以减少对数据库的查询次数。 
2）尽量使用相同的或非常类似的SQL语句进行查询，这样不仅充分利用SQL共享池中的已经分析的语法树，要查询的数据在SGA中命中的可能性也会大大增加。 
3）限制动态SQL的使用，虽然动态SQL很好用，但是即使在SQL共享池中有一个完全相同的查询值，动态SQL也会重新进行语法分析。 
4）避免不带任何条件的SQL语句的执行。没有任何条件的SQL语句在执行时，通常要进行FTS，数据库先定位一个数据块，然后按顺序依次查找其它数据，对于大型表这将 是一个漫长的过程。 
5）如果对有些表中的数据有约束，最好在建表的SQL语句用描述完整性来实现，而不是用SQL程序中实现。
6）可以通过取消自动提交模式，将SQL语句汇集一组执行后集中提交，程序还可以通过显式地用COMMIT和ROLLBACL进行提交和回滚该事务。 
7）检索大量数据时费时很长，设置行预取数则能改善系统的工作表现，设置一个最大值，当SQL语句返回行超过该值，数值库暂时停止执行，除非用户发出新的指令，开始组织并显示数据，而不是让用户继续等待。

 

14.合理使用事务，合理设置事务隔离性，数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数

 

上面的只是一部分的SQL语句优化，还有许多sql语句的优化，在这里就不一一列举了，SQL语句的优化无疑就是在查询时，把查询的次数尽量减少，在逻辑上把它变得更简单，这样才能提高查询的效率。

物理优化:

1.内存足够大，将临时表空间建立在内存盘上，对于统计、大数据量查询、大数据量sql运算（比如：group by、sum等）的操作，需要大量临时表空间存储临时数据，这些临时数据就直接存储到内存上了，消除了oracle的大读取和大运算产生的临时IO

 

2.由于索引具有可以根据数据文件重建，不怕丢失的特性，把索引文件建在临时文件系统上，oracle每次启动时重建索引，这样平时的读写操作就不会产生索引IO，索引的更新和读取都在内存上进行，系统由于各种原因出现重启时，仅在重启时重建一次索引

3.减少物理读写的优化

1) 增加内存data buffe的大小，尽可能让数据库操作的数据都能在内存里找到不需要进行物理读写操作。
2) 通过使用索引，避免不必要的全表扫描,将索引数据和表数据分开在不同的表空间上（降低IO冲突）。
3) 大表物理分区，Oracle具有很好的分区识别功能，减少数据扫描范围（以空间换取时间，减少IO）。

 

4.Oracle的运行环境（网络，硬件等）,合理配置oracle实例参数

 

5.实施系统资源管理分配计划 
ORACLE提供了Database Resource Manager（DRM,数据库资源管理器）来控制用户的资源分配，DBA可以用它分配用户类和作业类的系统资源百分比。在一个OLDP系统中，可给联机用户分配75%的CPU资源，剩下的25%留给批用户。另外，还可以进行CPU的多级分配。除了进行CPU资源分配外，DRM还可以对资源用户组执行并行操作的限制。

 

6.充分利用系统cpu资源，使用Parallel Query Option(PQO,并行查询选择)方式进行数据查询，使用PQO方式不仅可以在多个CPU间分配SQL语句的请求处理，当所查询的数据处于不同的磁盘时，一个个独立的进程可以同时进行数据读取。

 

上面就是物理优化的方案，对于数据库，想从物理方面来提升效率的方法是有限的，所以我们最好还是在SQL语句方面来优化数据库。

最后放上一个数据库优化的分支图