Oracle数据库性能调优实践(六)——五种调优法则

摘要：根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，整理出如下五种基本优化法则。一是减少数据访问（减少磁盘访问）；二是返回更少数据（减少网络传输或磁盘访问）；三是减少交互次数（减少网络传输）；四是减少服务器CPU开销（减少CPU及内存开销）；五是利用更多资源（增加资源）。

每一种优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。

现将有关Oracle的五种基础调优法则相关知识整理出来，供参考。详细内容请看下文。

一、访问Oracle数据库

1、切换到oracle 用户： su - oracle

2、访问oracle 数据库： sqlplus / as sysdba

二、五种调优法则

（一）、减少数据访问

1、创建并使用正确的索引

简介：数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的DBA也不一定能完全做到最优。索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。

2、只通过索引访问数据

简介：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。

3、优化SQL执行计划

简介：SQL执行计划是关系型数据库最核心的技术之一，它表示SQL执行时的数据访问算法。由于业务需求越来越复杂，表数据量也越来越大，程序员越来越懒惰，SQL也需要支持非常复杂的业务逻辑，但SQL的性能还需要提高，因此，优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外，还需要有一套优秀的算法库来提高SQL性能。目前ORACLE有SQL执行计划的算法约300种，而且一直在增加，所以SQL执行计划是一个非常复杂的课题，一个普通DBA能掌握50种就很不错了，就算是资深DBA也不可能把每个执行计划的算法描述清楚。

（二）、返回更少的数据

1、数据分页处理

比如采用rowid分页语法：优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。

create index myindex on product(company_id,status);

select b.* from (

select * from (

select a.*,rownum rn from

(select rowid rid,status from product a where company_id=? order by status) a

where rownum<=20)

where rn>10) a, product b

where a.rid=b.rowid;

数据访问开销=索引IO+索引分页结果对应的表数据IO

举例：假如某公司产品有1000条记录，要分页取其中20个产品，假设访问公司索引需要50个IO，2条记录需要1个表数据IO。

那么按第一种ROWNUM分页写法，需要550(50+1000/2)个IO，按第二种ROWID分页写法，只需要60个IO(50+20/2);

或者

2、只返回需要的字段

举例：通过去除不必要的返回字段可以提高性能，例：

调整前：select * from product where company_id=?;

调整后：select id,name from product where company_id=?;

优点：1、减少数据在网络上传输开销；2、减少服务器数据处理开销；3、减少客户端内存占用；4、字段变更时提前发现问题，减少程序BUG；5、如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。缺点：增加编码工作量。

（三）、减少交互次数

1、batch DML

数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

2、In List

很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：

for ：var in ids[] do begin

select * from mytable where id=:var;

end;

我们也可以做一个小的优化， 如下所示，用ID INLIST的这种方式写SQL：

select * from mytable where id in(:id1,id2,...,idn);

通过这样处理可以大大减少SQL请求的数量，从而提高性能。

3、设置Fetch Size

当我们采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

4、使用存储过程

大型数据库一般都支持存储过程，合理地利用存储过程也可以提高系统性能。普通业务逻辑不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

5、优化业务逻辑

6、使用ResultSet游标处理记录

（四）、减少数据库服务器CPU运算

1、使用绑定变量

Java中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：

1、防止SQL注入

2、提高SQL可读性

3、提高SQL解析性能，不使用绑定变量称为硬解析，使用绑定变量称为软解析。

2、合理使用排序

Oracle的排序算法一直在优化，但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗。以下列出了可能会发生排序操作的SQL语法：Order by；Group by；Distinct；Exists子查询；Not Exists子查询；In子查询；Not In子查询；Union（并集），Union All也是一种并集操作，但是不会发生排序，如果你确认两个数据集不需要执行去除重复数据操作，那请使用Union All 代替Union；Minus（差集）；Intersect（交集）；Create Index和Merge Join。

3、减少比较操作

4、大量复杂运算在客户端处理

（五）、利用更多的资源

1、客户端多进程并行访问

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。

2、数据库并行处理

数据库并行处理是指客户端一条SQL的请求，数据库内部自动分解成多个进程并行处理。并不是所有的SQL都可以使用并行处理，一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问，所以需要指定SQL并行的提示，如下所示：select /*+parallel(a,4)*/ * from employee;

并行的优点：使用多进程处理，充分利用数据库主机资源（CPU,IO），提高性能。

并行的缺点：1、单个会话占用大量资源，影响其它会话，所以只适合在主机负载低时期使用；2、只能采用直接IO访问，不能利用缓存数据，所以执行前会触发将脏缓存数据写入磁盘操作。