Oracle Performance Survival Guide读取笔记–第4章 数据库逻辑设计和物理设计
一、逻辑设计
- 采用符合第三范式的标准化模型
- 数据类型选择尽量使用VARCHAR变长字符,否则会浪费空间,全表扫描的时候性能变差超过4000字符的时候采用LOB类型,LONG属于过时的类型,不建议使用
- 主键选择
- artificial key:人造键,无业务含义,大部分情况通过是通过oracle的序列产生的数字。
- natrual key:自然键,一般为字符类型,有明确的业务含义,可以是单列或多列构成。
从性能角度建议使用artificial key,有三个原因:
- artificial key为数字类型,键的程度更短,因此生成索引树的深度会更低
- artificial key不会更新,因此不会带来因外键更新带来的性能问题
二、从逻辑设计到物理设计的映射
实体 –> 表
实体继承,也就是OO开发中的父类和子类,如下面的例子:
class Person {
String firstName;
String lastName;
}
class Customer extends Person {
int customerCategory;
}
class Employee extends Person {
double salary;
}
在映射到数据库的表设计时,有三种方案(个人偏向于方案二):
- 方案一:父类person一张表,子类customer和employee各一张表
- 方案二:子类customer和employee各一张表,在表里面包含父类person的属性
- 方案三:仅一张表,包含所有person,customer,employee的字段,使用额外的
person_type字段区分子类
不建议使用方案一,主要原因是仅查询父类person表的情况较少,大部分时候需要表连接,性能较差。
表类型选择
默认的是堆表,其他可选的表有:
- hash cluster,散列聚簇
- index cluster,索引聚簇
- nested table,嵌套表
- index organized table,索引组织表
- object table,对象表
数据精度选择
货币计算时,一般保留小数点后2为即可,这种情况下最好指定字段为NUMBER(*,2),避免不能整除的数字在oracle中保存不必要的位数
null的设定
考虑字段是否为null时,从性能方面出发,要考虑是否存在查询字段为null的需求,如:
select * from customer where category_id is null
如果存在,category_id字段不应该定义为允许null,应该定义为not null,并设定默认值。
对于数字类型,默认值比较难以设定,会影响计算最大、最小、平均值的结果。可以增加一个辅助字段category_known并建立索引。
列的顺序
- 常用的字段放在前面
- 可以为空的字段全部放在最后,可以节省一点空间
但列顺序对性能的影响很小,因此定义列顺序是应该优先考虑模型的可理解和维护性
oracle对象模型
不建议使用,属于oracle专有技术,bi和报表工具无法使用,优化的资源难找,模型设计通用性不好。
三、Denormalization,反规范化
主要是为了优化性能,但存在风险:
- 带来了冗余信息,可以会造成信息的不一致
- 软件开发和维护的复杂性和成本更高
- 插入和更新的开销更大
- 可能无意中造成其他操作效率低,甚至无法完成(作者举的例子没看懂)
主要的做法有:
- 复制列值,避免表连接
- 使用概要表进行预计算
实时数据有应用系统更新,非实时数据可以使用oracle的物化视图进行更新 - 垂直分区
将全表扫描中经常使用的列保留在主表,其他列放到另外一张表,以提高查询性能。 - 实施denormalization,建议使用触发器以保证数据的一致性。
四、星形模型设计
- 尽量不要采用雪花模型
- 使用oracle的create dimension来表示维度层次
- 使用oracle的物化视图来计算聚集数据
五、物理存储方案
- 推荐使用ASSM(数据存储段自动管理)方案
- PCTFREE和PCTUSED设置时,如果PCTFREE设置较低,为满足并发更新应该考虑增加INITRANS参数的值
- 对于经常全表扫描、且字符字段较多的表,采用压缩可以大大降低物理IO
- LOB字段
- LOB字段可以存储在单独的表空间,以提高IO性能
- 小于4000字节的LOB为inline存储,out-of-line存储时应该设置合适的chunk大小(过小导致访问LOB时访问chunk次数太多,过大则浪费空间)
- 对于新的应用推荐使用oracle 11g的SecureFile LOB类型
- 分区
- partition elimination可以大大降低全表扫描的成本
- 分区可以提升并行处理能力
- 分区可以高效的删除大批量过期数据
- hash分区可以降低锁争用