Oracle数据库优化

1、数据库讲座说明：各种数据库之间存在一些差异，本文，部分内容针对ORACLE数据库。2 数据库优化2.1 外部的性能问题Oracle并不是单独运行的。Oracle数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：l CPU - CPU 资源的不足令查询变慢。当查询超过了Oracle服务器的CPU性能时，你的数据库性能就受到CPU的限制。对于大型系统，尽量利用多个CPU处理器。CPU的快速发展使得ORACLE越来越重视对多CPU的并行技术的应用，一个数据库的访问工作可以用多个CPU相互配合来完成，加上分布式计算已经相当普遍，只要可能，应该将数据库服务器和应用程序的CPU请求分开，或将CP

2、U请求从一个服务器移到另一个服务器。对于多CPU系统尽量采用Parallel Query Option(PQO,并行查询选项)方式进行数据库操作。使用PQO方式不仅可以在多个CPU间分配SQL语句的请求处理，当所查询的数据处于不同的磁盘时，一个个独立的进程可以同时进行数据读取。l 内存 - 可用于Oralce的内存数量也会影响SQL的性能，特别是在数据缓冲和内存排序方面。l 网络 - 大量的Net8通信令SQL的性能变慢。许多新手都错误的认为应该首先调整Oracle数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的Oracle调整都是没有帮助的。2.2 利用表分区分区将

3、数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。比如：可将一张数据量非常大的表，按某一字段（如：类别）进行表分区。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行FTS(Full Table Scan，全表扫描)，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。2.3 避免出现行连接和行迁移在建立表时，由于参数pctfree和pctused不正确的设置，数据块中的数据会出现行链接和行迁移，也就是同一行的数据不保存在同一的数据块中。如果在进行数据查询时

4、遇到了这些数据，那么为了读出这些数据，磁头必须重新定位，这样势必会大大降低数据库执行的速度。因此，在创建表时，就应该充分估计到将来可能出现的数据变化，正确地设置这两个参数，尽量减少数据库中出现行链接和行迁移。2.4 控制碎片碎片(fragmentation)是对一组非邻接的数据库对象的描述。碎片意味着在执行数据库的功能时要耗费额外的资源（磁盘I/O，磁盘驱动的循环延迟，动态扩展，链接的块等），并浪费大量磁盘空间。当两个或多个数据对象在相同的表空间中，会发生区间交叉。在动态增长中，对象的区间之间不再相互邻接。为了消除区间交叉将静态的或只有小增长的表放置在一个表空间中，而把动态增长的对象分别放在各

5、自的表空间中。在create table、create index、create tablespace、create cluster时，在storage子句中的参数的合理设置，可以减少碎片的产生。2.5 回滚段的交替使用由于数据库配置对应用表具有相对静止的数据字典和极高的事务率特点。而且数据库的系统索引段、数据段也具有相对静止，并发现在应用中最高的负荷是回滚段表空间。交替使用回滚段，可达到了循环分配事务对应的回滚段，可以使磁盘负荷很均匀地分布。2.6 非规范化设计对于频繁查询但是不频繁修改的数据，内部设计应当物理不规范化。比如：在不影响业务需求的情况下，可以建一些冗余字段，以减少连接表的查询。

6、建一些临时统计数据表，帮助我们提高统计及业务查询的效率。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。2.7 管理组织索引l 索引优点索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。创建索引时，应尽量保证该索引最可能地被用于where、order by、group by 子句中。l 不建或少建索引情形n 表记录太少如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE

7、会将所有的数据一次读出，处理速度显然会比用索引快。n 经常插入、删除、修改，但对查询效率要求不高表n 数据重复且分布平均的字段假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。n 经常和主字段一块查询但主字段索引值比较多的表字段如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引

8、查询将对性能不产生太大的影响。l 特大型表的索引问题但是有些DBA发现，对一个大型表（字段多、数据多）建立的索引，并不能改善数据查询速度，反而会影响整个数据库的性能。这主要是和SGA（内存分配系统全局区）的数据管理方式有关。ORACLE在进行数据块高速缓存管理时，索引数据比普通数据具有更高的驻留权限，在进行空间竞争时，ORACLE会先移出普通数据。对一个建有索引的大型表的查询时，索引数据可能会用完所有的数据块缓存空间，ORACLE不得不频繁地进行磁盘读写来获取数据。解决办法：因此在对一个大型表进行分区之后，可以根据相应的分区建立分区索引。如果对这样大型表的数据查询比较频繁，或者干脆不建索引。2

9、.8 聚簇的使用Oracle提供了另一种方法来提高查询速度，就是聚簇（Cluster）。所谓聚簇，简单地说就是把几个表放在一起，按一定公共属性混合存放。聚簇根据共同码值将多个表的数据存储在同一个Oracle块中，这时检索一组Oracle块就同时得到两个表的数据，这样就可以减少需要存储的Oracle块，从而提高应用程序的性能。适用于对查询效率很高，经常一同使用的多个表，且这此表之间有连接关系（外键关系）。2.9 使用Connection Pool机制在数据库处理中，资源花销最大的是建立数据库连接，而且用户还会有一个较长的连接等待时间。解决的办法就是复用现有的Connection，也就是使用Con

10、nection Pool对象机制。Connection Pool的原理是：应用服务器中维持了一个连接缓冲池，这样，当下一个用户访问时，直接在连接缓冲池中取得一个数据库连接，而不需重新连接数据库，因此可以大大地提高系统的响应速度。2.10 数据分片设计在实际业务中，可能某个业务表中的数据量很大,急速膨胀,在这样的情况下,我们为了保持高的数据响应速度,根据数据的时间局部性和空间局部性原则,可以通过对数据表进行分片设计。一般有两种分片方法，横向分片和竖向分片。l 横向分片将业务表按使用部门（或者机构）拆分为多个表,各个部门之间的数据相对独立,相互之间互不影响,这种方法适用于各部门相互独立的情况,不过

11、带来的问题就是部门不便扩展,统计及交换数据不便,举个例子,同样的医嘱管理系统,几个病区,一个病区一张表,虽然带来了查询数据效率的提高,但是病区之间病人转科,数据交换不方便。l 竖向分片将业务表按流程拆分成多个表，将业务表中不常用的数据转移到另外的表中，如在院和出院病人数据分别存于不同的表中，病人出院后就将在院病人数据转到出院病人表中，因为在院病人数据是经常要操作的，所以这种分片保证了在院病人数据表始终保持一定的量,从而提高查询的效率。l 分片原则在数据量较小，部门之间数据频繁交换的情况下使用竖向分片，这样会保证程序设计中逻辑相对保持简单，提高了程序的可靠性，也降低了程序员的负担.。竖向分片仍然

12、不足以解决问题的情况下，我们再引入横向分片，将数据经常交换的部门分为一组，放在同一张业务表中,横向分片数要尽量少,减少统计程序的复杂性。2.11 操作型数据存储 (Operational Data Store，ODS)它可对业务数据自动抽取汇总，大大提高数据统计速度。而且减少了对业务数据库的访问。在ODS方式下，数据被从业务数据库中复制到一个中心位置，再从这里被抽取到多个数据集市中。ODS是从客户、产品和其他商业角度来组织的，被称为商业状况的“实时快照”。它不包含历史信息，但可以很容易地满足一个历史数据库或一组面向主题的数据集市的需要。2.12 数据复制、快照l 数据复制，即将数据一次复制到本

13、地，这样以后的查询就使用本地数据，但是只适合那些变化不大的数据。l 快照也可以在分布式数据库之间动态复制数据，定义快照的自动刷新时间或手工刷新，以保证数据的引用参照完整性。适合范围也比较狭窄。2.13 其它l 存储过程，通过调用它可以大大减少因频繁的SQL语句调用而带来的网络拥挤。l 少写触发器，影响效率，而且相比之下更容易锁表。3 高效SQL编写3.1 选择最有效率的表名顺序 大表放在FORM字句的最前面，小表放在FORM字句的最后面。ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子

14、句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询， 那就需要选择交叉表(intersECtion table)作为基础表， 交叉表是指那个被其他表所引用的表。3.2 WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大

15、数量记录的条件必须写在WHERE子句的末尾。3.3 取别名在联合表的查询中，表名、列名以一个字母为别名，查询速度要快1.5倍。3.4 字段是否允许为空l NOT NULL字段能用到索引，但允许为NULL的字段，有时却不能。比如：在表T，字段username建了索引。select username from t where username=EYGLE ，能用到索引select username from t ，不能用到索引l LEFT JOIN 比 INNER JOIN 消耗资源更多解决方法：比如，有个学生表，含有民族字段，可不填，不填时存储值为(长度为0的字符串)。另外，在民族代码表有条记录

16、为（代码：，值：无）。3.5 操作运算符l NOTIN操作符、（不等于） 、!不能到索引的，因此对它的处理只会产生全表扫描。效率低。解决办法：not in 可改为表连接或not existsa0改为a0ora0a改为a!=! 可改为 运算比=效率高l 运算比=效率高3.6 语句效率大比拼l DISTINCT 子句，其效率比GROUP BY 子句高。l WHERE 子句，其效率比HAVING 子句高。l union语句（2至3个）在通常情况下比用or或IN的效率要高l select 字段名 语句比 select * 语句 的效率要高l EXISTS比IN效率高 在许多基于基础表的查询中，为了满足

17、一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。l EXISTS比DISTINCT效率高 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换l 表连接比EXISTS效率高通常来说，采用表连接的方式比EXISTS更有效率3.7 排序order by后面按索引列排序，比按非索引列或者表达式 排序效率更高3.8 联接列对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于

18、一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。下面是一个采用联接查询的SQL语句， select * from employss where first_name|last_name =Beill Cliton; 上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。 Select * from employee where firs

19、t_name =Beill and last_name =Cliton;3.9 预编译SQL大多数情况下这个语句已经被预编译过，因而当其执行时，只需DBMS运行SQL语句，而不必先编译。而且大大降低运行时间，当然也加快了访问数据库的速度。动态SQL，每次执行都需要编译。而且即使在SQL共享池中有一个完全相同的查询值，动态SQL也会重新进行语法分析，而预编译SQL不需要。4 复杂SQL编写4.1 将行变成列比如：数据表：课程信息表（kcxx），字段：课程ID（chvkcid）、课程名称（chvkcmc）学生信息表（xsxx），字段：学生ID（chvxsid）、学号、姓名（chvxsxm）、班级（

20、chvbj）、学生成绩表（xscj），字段：成绩ID、学生ID（chvxsid）、课程ID（chvkcid）、成绩（fltcj）视图：学生成绩(xscj_view)，字段：学生ID（chvxsid）、学生姓名、课程ID、学生成绩、班级样式数据：张三、语文、78 张三、数学、98 张三、英语、88 李四、语文、85 要求：获得班级为XX的成绩数据，格式为以下表格课程姓名语文数学英语张三789888李四首先，先写个非常简单的查询，获得表头，课程名称供表头用，课程ID供下面SQL使用。方法一，SQL1，将同一个表，转化成3个表相连，然后查出3列select tmp1.chvxsxm, tmp1.fl

21、tcj,tmp2.fltcj,tmp3.fltcj from (select chvxsxm, fltcj from xscj_view where cjvbj=参数班级 and chvkcid=参数课程ID1) tmp1 left join (select fltcj from xscj_view where cjvbj=参数班级 and chvkcid=参数课程ID2) tmp2 on tmp1. chvxsid =tmp2. chvxsid left join (select fltcj from xscj_view where cjvbj=参数班级 and chvkcid=参数课程ID

22、3) tmp3 on tmp1. chvxsid =tmp3. chvxsidorder by tmp1.chvxsid方法二，SQL2，将一个表，3个值同时查出来，据不同段值，转化成3个值select chvxsxm,max(decode(chvkcid, 参数课程ID1 , fltcj ,null) as fltcj1,max(decode(chvkcid, 参数课程ID2 , fltcj , null) as fltcj2,max(decode(chvkcid, 参数课程ID3 , fltcj , null) as fltcj3from xscj_view where cjvbj=参数班级group chvxsid,chvxsxmorder by chvxsid备注：decode(chvkcid, 参数课程ID1 , fltcj ,null) 是个函数，即当chvkcid=参数课程ID1时，返回fltcj，否则返回null；两种方法，相比之下，