ORACLE数据库设计与优化

最新推荐文章于 2021-04-05 04:11:58 发布

cnham

最新推荐文章于 2021-04-05 04:11:58 发布

阅读量889

点赞数

分类专栏： Oracle基础 oracle性能优化文章标签：数据库 oracle list 服务器存储磁盘

Oracle基础同时被 2 个专栏收录

89 篇文章 0 订阅

订阅专栏

oracle性能优化

34 篇文章 0 订阅

订阅专栏

ORACLE 数据库设计与优化

1 Oracle 数据库设计策略及规范

1.1 基本策略

u 分类拆分数据量大的表。

对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。

u 分区策略

在拥有数500 行以上的表时，采用分区策略。

u 索引设计。

在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。

u 减少表的关联，恰当的数据冗余是允许的。

u 为表和索引建立不同的表空间，禁止在系统表空间中放入非核心oracle 系统成分的对象，确保数据表空间和索引表空间位于不同的磁盘磁盘驱动器上。

u 对于经常发生同时查询或频繁查询的表，最好把他放到不同的磁盘空间上。

1.2 逻辑设计规范

1.2.1 表设计

u 对于数据量比较大的表，根据表数据的属性进行分区。如果表按某些字段进行增长，则采用按字段值范围进行范围分区；如果表按某个字段的几个关键值进行分布，则采用列表分区；对于静态表，则采用Hash 分区或列表分区；在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。

u 每个表均创建类型为Sequence 的主键字段。

每个表中需含有如下几个基本字段：一个表的SEQ 号，4 个创建信息字段，5-8 个备用字段，一个删除标记字段，最好还有一个行版本字段

字段名	类型	备注
业务SEQ 号	整数型	作为表主键
OBLIGATE1	字符型	备用字段
OBLIGATE2
OBLIGATE3
OBLIGATE4
OBLIGATE5
CREATE_USER_ID	VARCHAR	创建用户ID
CREATE_DATETIME	DATE	创建时间
LAST_UPDATE_USER_ID	VARCHAR	更新用户ID
LAST_UPDATE_DATETIME	TIMESTAMP	更新时间

u 不要用Identify 字段作为表的主键与其它表关联。

u 把允许 NULL 的列放在表的后面

1.2.2 索引设计

u 常规OLTP 应用，创建B-TREE 索引, 不创建位图索引 。

u 不需要为记录数小于5000 的表，创建索引。

u 给单个表创建的索引不超过5 个，特别是海量交易类表。

u 索引条件查询结果记录，不超总记录的20% 。

u 不要给固定选项的字段创建独立索引。

u 对于复合索引，把查询频率比较高的字段排在索引组合的最前面。

u 索引放到独立的表空间，该表空间不需要REDO LOG 。

u 含有外键约束的表的字段，必须有单独索引。如订单明细的表头外键。

u 表的字段很少，不适合建索引

2 SQL 语句优化

u 尽量使 SQL 语句能够使用索引

u 当 SQL 语句中包含 not in,<>,is null,is not null,like ‘%%’ 的时候不会用索引。

解决方法：

IN ： in 会拆成一堆 or 的，可以使用表的索引

NOT IN ：强列推荐不使用。可以用 NOT EXISTS 或（外连接 + 判断为空）代替

<> ：永远不会用到索引的，用其它相同功能的操作运算代替。例如 a<>0 改为 a>0 or a<0 ； a<>’’ 改为 a>’’

IS NULL 或 IS NOT NULL ：用其它相同功能的操作运算代替。如 a is not null 改为 a>0 或 a>’’ 等。

LIKE ：如 LIKE ‘%001%’ 改 LIKE ‘001%’

u 多表查询时，一般一个大表关联几个小表。不要几个大表关联

u 经常查询的 SQL 可以写成存储过程

3 超大型数据库应用系统的设计方法

3.1 概论

超大型系统的特点为：

1. 处理的用户数一般都超过百万，有的还超过千万，数据库的数据量一般超过1TB ；

2. 系统必须提供实时响应功能，系统需不停机运行，要求系统有很高的可用性及可扩展性。

为了能达到以上要求，除了需要性能优越的计算机和海量存储设备外，还需要先进的数据库结构设计和优化的应用系统。

一般的超大型系统采用双机或多机集群系统。

3.2 Oracle 并行服务器应用划分策略

Oracle 并行服务器允许不同节点上的多个INSTANCE 实例同时访问一个数据库，以提高系统的可用性、可扩展性及性能。Oracle 并行服务器中的每个INSTANCE 实例都可将共享数据库中的表或索引的数据块读入本地的缓冲区中，这就意味着一个数据块可存在于多个INSTANCE 实例的SGA 区中。那么保持这些缓冲区的数据的一致性就很重要。Oracle 使用 PCM （ Parallel Cache Management ）锁维护缓冲区的一致性，Oracle 同时通过I DLM （集成的分布式锁管理器）实现PCM 锁, 并通过专门的LCK 进程实现INSTANCE 实例间的数据一致。

考虑这种情况：INSTANCE1 对BLOCK X 块修改，这时INSTANCE2 对BLOCK X 块也需要修改。Oracle 并行服务器利用PCM 锁机制，使BLOCK X 从INSTANCE 1 的SGA 区写入数据库数据文件中，又从数据文件中把BLOCK X 块读入INSTANCE2 的SGA 区中。发生这种情况即为一个PING 。PING 使原来1 个MEMORY IO 可以完成的工作, 变成2 个DISK IO 和1 个 MEMORY IO 才能够完成, 如果系统中有过多的PING ，将大大降低系统的性能。

Oracle 并行服务器中的每个PCM 锁可管理多个数据块。PCM 锁管理的数据块的个数与分配给一个数据文件的PCM 锁的个数及该数据文件的大小有关。当INSTANCE 1 和INSTANCE 2 要操作不同的BLOCK ，如果这些BLOCK 是由同一个PCM 锁管理的, 仍然会发生PING 。这些PING 称为FALSE PING 。当多个INSTANCE 访问相同的BLOCK 而产生的PING 是TRUE PING 。

合理的应用划分使不同的应用访问不同的数据，可避免或减少TRUE PING ；通过给FALSE PING 较多的数据文件分配更多的PCM 锁可减少 FALSE PING 的次数，增加PCM 锁不能减少TRUE PING 。

所以, Oracle 并行服务器设计的目的是使系统交易处理合理的分布在INSTANCE 实例间，以最小化PING ，同时合理的分配PCM 锁，减少FALSE PING 。设计的关键是找出可能产生的冲突，从而决定应用划分的策略。应用划分有如下四种方法:

1. 根据功能模块划分，不同的节点运行不同的应用

2. 根据用户划分，不同类型的用户运行在不同的节点上

3. 根据数据划分，不同的节点访问不同的数据或索引

4. 根据时间划分，不同的应用在不同的时间段运行

应用划分的两个重要原则是使PING 最小化及使各节点的负载大致均衡。

3.3 数据库物理结构的设计

数据库物理结构设计包括确定表及索引的物理存储参数，确定及分配数据库表空间，确定初始的回滚段，临时表空间，redo log files 等，并确定主要的初始化参数。物理设计的目的是提高系统的性能。整个物理设计的参数可以根据实际运行情况作调整。

● 表及索引数据量估算及物理存储参数的设置

表及索引的存储容量估算是根据其记录长度及估算的最大记录数确定的。在容量计算中考虑了数据块的头开销及记录和字段的头开销等等。表及索引的initial 和next 存储参数一般设为相等，pctincrease 设为0 。

● 表空间的设计

Oracle 数据库的表和索引是透过表空间tablespace 存储在数据库中的。在tablespace 设计时一般作以下考虑：

1 、一般较大的表或索引单独分配一个tablespace 。

2 、Read only 对象或Read mostly 对象分成一组，存在对应的tablespace 中。

3 、若tablespace 中的对象皆是read only 对象，可将tablespace 设置成read only 模式，在备份时，read only tablespace 只需备份一次。

4 、高频率insert 的对象分成一组，存在对应的tablespace 中。

5 、增、删、改的对象分成一组，存在对应的tablespace 中。

6 、表和索引分别存于不同的tablespace 。

7 、存于同一个 tablespace 中的表（或索引）的extent 大小最好成倍数关系，有利于空间的重利用和减少碎片。

● DB BLOCK SIZE

超大型数据库DB BLOCK SIZE 一般在4KB 至 64KB ，而最常用的是8KB 、 16KB 或32KB 。选用较大的DB BLOCK SIZE 可使INDEX 的高度降低，也会提高IO 效率。

● Redo Log Files

Oracle 进程redo log writer (LGWR) 将日志写入日志文件。一般日志文件最好建在专用的镜像盘上。日志文件组的个数及文件的大小的设定与系统交易量的大小有关。ORACLE 并行服务器中每个INSTANCE 使用各自的一组rego log files 。一般的每组日志文件的个数为3-7 个，每个的大小为200MB －500MB 。

● 数据文件大小

建议用标准的文件大小，如200M 、１GB 、２GB 、4GB 、8GB 等，可简化空间的维护工作。

● 回滚段

回滚段一般建在专用的表空间中。每一个INSTANCE 实例拥有各自的回滚段。设置回滚段的一般原则是： initial 及 next 存储参数的值是相等的，同时还是DB BLOCK SIZE 的倍数。每个回滚段的minextents 设为20 ，optimal 参数的值保证回滚段缩小时不低于20 个extents 。

● 临时表空间

临时表空间一般建在专用的表空间中。每一个INSTANCE 实例拥有各自的临时表空间。这样使用临时表空间时不会有PING 。设置临时表空间的initial=next 。

3.4 系统硬盘的划分及分

在多机集群环境下，Oracle 并行服务器通过操作系统提供的DRD 服务来共享同一个数据库。每一个INSTANCE 对数据库的数据文件的访问都是通过该数据文件所在的DRD 服务进行的。

考虑以下情况：主机1 上有DRD 服务1 ，该服务对应的数据文件有1 、2 、13 、35 、67 等，这时如果主机2 上的INSTANCE2 需要读取数据文件13 ，通过DRD 服务调度，主机1 通过DRD 服务访问磁盘阵列上的数据文件13 ，把INSTANCE2 需要的数据读到内存，然后通过MEMORY IO 把数据传到主机2 的INSTANCE2 。写操作是读操作的逆过程。