《Oracle性能优化求生指南》第四章：数据库逻辑设计和物理设计学习小结1

1、建立逻辑数据模型为第一阶段&＃xff0c;包括对应用程序需要处理和存储的信息进行建模&＃xff0c;并确保所有必要的数据都能够正确、完整且无歧义地表示。在关系数据库的实现中&＃xff0c;这通常是指构造一个标准化的实体-关系(E-R)模型。

2、将逻辑数据模型映射为物理数据模型为第二阶段。对关系数据库来讲&＃xff0c;物理数据模型描述的是表、索引、视图、键和其他一些数据库特性。

3、第三范式&＃xff1a;

实体(表)的所有数据完全依赖于主键。

不能有重复的属性(列)或属性组。

不存在仅依赖部分主键的实体数据。

不存在依赖于其他非主键的实体数据。

用一条格言描述&＃xff1a;”键&＃xff0c;完整的键&＃xff0c;除了键没有其他东西。“

4、无论从文档或定义角度看&＃xff0c;逻辑模型中精确定义属性的数据类型、长度、精度都有优势。由于Oracle在数据类型的物理存储上采用的是通用且灵活性很强的内部实现方式&＃xff0c;因此从存储或性能角度看&＃xff0c;使用限制性很强的数据类型或精度并没有优势。

5、不管如何限制数据类型&＃xff0c;大部分情况下&＃xff0c;Oracle内部都会使用大范围高精度的浮点方式进行存储。

6、固定长度类型的字符串都会占用一个固定长度的存储空间&＃xff0c;而不管字符串的真实长度是多少。采用固定的航长度可以降低碎片&＃xff0c;但是它会导致较大的平均行长&＃xff0c;这样就会增加全表扫描的开销。因此&＃xff0c;除非数据的长度确实是固定的&＃xff0c;否则就应该优先选择变长字符串类型(VARCHAR)。

7、人造键&＃xff1a;

是由Oracle sequence产生的一个数字类型的列。

没有任何含义&＃xff0c;只是为了唯一地标识实体中的记录。

从来不会被更新。

自然键&＃xff1a;

可由多列组成并可包括任何数据类型。是由实体中具有唯一性的自然属性构成的。

如果自然键被更新&＃xff0c;则引用它的外键也需要更新&＃xff0c;这将显著增加IO开销和锁争用。

显然&＃xff0c;必须基于自然键的列进行索引查询的需求也是很常见的&＃xff0c;为满足这种需求&＃xff0c;可以在这些列上创建普通索引或唯一约束。

8、逻辑阶段的主要目的是确保设计能够满足应用的功能需求。物理设计阶段的目的才是确保数据库能够满足应用的性能需求。将逻辑自雷转化为表的时候&＃xff0c;要避免采用分开的父类表和子类表的方法&＃xff0c;而要选择将所有子类映射为一个表&＃xff0c;或采用单独的子类表而没有上一级表的方式。

9、表的类型有堆表、索引组织表、聚簇等。

散列聚簇(Hash Cluster)&＃xff1a;能够有效优化大小相对固定的表的主键查询&＃xff0c;与B*树索引相比&＃xff0c;散列聚簇还能降低热点块上的闩锁争用。

索引聚簇(Index Cluster)&＃xff1a;共享聚簇键值的多个表中的记录存储在一起&＃xff0c;这样可以优化多表联结。虽然多表联结性增强了&＃xff0c;但仅针对聚簇中某个表的全表扫描的性能却降低了。

嵌套表(Nested Table)&＃xff1a;能够针对主表中的某一行优化对明细行的检索。但是&＃xff0c;如果要跳过主表行检索明细行的话&＃xff0c;性能通常会大打折扣。

索引组织表&＃xff1a;如果大部分表访问都是通过主键进行查询&＃xff0c;并且表数据量的变动幅度较大而不适合使用散列聚簇&＃xff0c;使用索引组织表将会更高效。

对象表(Object Table)&＃xff1a;行都被定义为一个Oracle对象数据类型&＃xff0c;需要使用对象表的场景非常少。

10、精度的意义更多在于约束数据或定义文档&＃xff0c;而不是为了优化性能。但是&＃xff0c;如果一个高精度的数值被偶然指定给一个精度限制较弱的数字字段&＃xff0c;这时候设定一个精度就可能对性能有所帮助。例如NUMBER(*,2)那么不必要的精度将会被截断&＃xff0c;行的长度也将相应地减少。设置该数值列的精度有时可以减少行的长度。

11、DATE可以精确到秒&＃xff0c;TIMESTAMP可以亚秒级&＃xff0c;可以配置到纳秒级的精度&＃xff0c;其默认精度则为微秒。

12、字符串若小于4000字节&＃xff0c;优先选择VARCHAR2类型。

字符串若大于4000字节&＃xff0c;推荐使用LOB类型&＃xff0c;CLOB存储字符数据&＃xff0c;BLOB存储二进制数据。

11g提供高性能的LOB存储类型&＃xff0c;称为SecureFiles。

每个表只能有一个LONG数据类型的列&＃xff0c;和早期的Oracle版本兼容。

优先选择VARCHAR2不是VARCHAR&＃xff0c;VARCHAR将来会受到修改&＃xff0c;以符合ANSI的标准。

13、B*树索引不能存储NULL&＃xff0c;因此需要全表扫描来查找NULL值。位图索引和(部分列为NULL的)多列组合索引就能存储NULL值。

采用NULL可以降低行的平均长度&＃xff0c;从而一定程度上提高全表扫描的性能。

如果该列的数值大部分是NULL&＃xff0c;并且查询仅需检索非NULL的值&＃xff0c;则该列上的索引会比较紧凑并很高效。

因此&＃xff0c;决定一列是否可以为NULL的时候&＃xff0c;要考虑在该列上是否有使用B*树索引查询NULL的需求。如果有&＃xff0c;则不能指定该列为NULL&＃xff0c;取而代之的是定义该列为NOT NULL(非空)&＃xff0c;并指定一个默认值。

如果要查询那些未知的值&＃xff0c;则不能定义列为空&＃xff0c;相反&＃xff0c;要定义列为非空并指定一个默认值。

对于字符类型的数据&＃xff0c;默认值可以是一个字符串&＃xff0c;例如UNKNOWN或N/A。

对于数字类型&＃xff0c;选择合适的默认值会有难度&＃xff0c;例如&＃xff0c;统计包含AGE字段&＃xff0c;基于索引扫描或查询AGE列时&＃xff0c;可能需要找出年龄不确定(UNKNOWN)的记录。如果给AGE指定一个默认值&＃xff0c;当查询平均年龄、最小年龄和最大年龄的时候&＃xff0c;可能会得到不正确的结果。这种情况&＃xff0c;使用NULL时必要的&＃xff0c;但查询不能快速返回那些AGE不确定的记录&＃xff0c;要么进行反规范化&＃xff0c;增加一个标记列来标记年龄是否已知&＃xff0c;并在该标记列上建立索引&＃xff0c;以便于查询AGE不确定(AGEKNOWN&＃61;N)的记录。NULL值可以存储在位图BITMAP索引中&＃xff0c;上述中或许不会仅因为这个理由选择位图索引&＃xff0c;如果该AGE列上存在位图索引&＃xff0c;可以高效地检索NULL值。

14、除非表中每一个字段都是固定长度的字符串&＃xff0c;否则Oracle无法知道某列在行物理存储结构中的具体位置。

访问表中靠后的列相比访问靠前的列&＃xff0c;需要额外消耗少量的CPU资源。

因为Oracle必须顺序扫描行结构以获得某个特定的列的位置&＃xff0c;基于这个原因&＃xff0c;将经常需要访问的列存储在表的前面&＃xff0c;会带来一些正面的性能影响。

那些值为NULL的字段通常需要一个字节的存储空间&＃xff0c;但如果该行中随后的列的数值都为NULL&＃xff0c;则Oracle不需要为这些NULL分配任何空间。如果将那些大部分为NULL的列存储在表的末尾&＃xff0c;则行的实际长度会变小&＃xff0c;这样有助于提高表扫描的性能。

这些调整带来的性能改进比较小&＃xff0c;因此列的逻辑顺序应使数据模型更易于理解和维护&＃xff0c;而不是为了这些微小的优化而使逻辑模型中列的顺序变得混乱。

15、反规范化&＃xff1a;是指在物理模型中重新引入冗余、重复或其他非规范化结构的过程&＃xff0c;主要意图是为了提高性能。

16、概要表

如果实时汇总数据是必需的&＃xff0c;则每当源数据被更新时&＃xff0c;必需同时更新汇总数据。通过数据库触发器或物化视图来手动实现这一点&＃xff0c;但若更新十分频繁&＃xff0c;则可能导致锁争用。

如果实时汇总信息不实必需的&＃xff0c;则可以通过定期的作业调度更新概要表-费业务高峰期进行。使用Oracle物化视图机制实现。但会导致不实时的不准确的汇总信息。

17、Oracle 11g的结果集缓存。

18、进行聚合操作的查询通常会占用大量数据库资源&＃xff0c;最好是使用物化视图来维护反规范化的汇总信息。

19、通常&＃xff0c;将逻辑子类型实现为从表会降低常见SQL操作的性能。

垂直分区(Vertical Partitioning)&＃xff1a;经常在全表扫描操作中访问到的列需要保留在主表中&＃xff0c;较少访问的列则存储在第二章表中。

20、视图&＃xff1a;给用户只有一张表的假象。通常在应用程序查询子表的时候用以获得最佳性能&＃xff0c;代价是使SQL应用复杂化。

21、Oracle有时会允许将一张表物理地分割成多个数据段&＃xff0c;同时让用户保留一个单一逻辑表的印象&＃xff0c;例如&＃xff1a;

LOB数据通常会存储在单独的数据段。

索引组织表中&＃xff0c;一些数据将被存储在溢出段中。

如果表很大&＃xff0c;且预计会有频繁表扫描&＃xff0c;可以考虑将字段较长且不常访问的列迁移到一个单独的子表中&＃xff0c;以减少长度和提高表扫描的性能。

22、优先使用数据库触发器来保证反规范化数据的一致性&＃xff0c;避免通过应用代码来维护。数据库触发器降低了数据不一致的风险&＃xff0c;简化了应用代码&＃xff0c;同时更加高效。

23、事实表的外键几乎都是由序列生成的人造数字键。从存储的观点看&＃xff0c;如果事实表的外键都是有意义的字符串的话&＃xff0c;则存储成本比较高&＃xff1a;例如&＃xff0c;一个产品的具体名称占用的空间可能是产品ID占用空间的10倍。保持较短的行长度对事实表来说是重要的&＃xff0c;因为事实表上会经常有全表(或分区)扫描。

24、尽量避免使用雪花模式。当维度表不包括外键的时候&＃xff0c;查询性能通常会得到优化。

25、通过创建层次化的维&＃xff0c;可以提高那些需要使用较高层次聚集数据的查询的性能&＃xff0c;例如&＃xff0c;查询每月总收入将不再需要累加该月份中每天的收入记录&＃xff0c;从而大大提高了性能。

可以考虑针对多层次的聚集数据采用多个事实表的方式&＃xff0c;采用多个事实表是常用的数据仓库解决方案&＃xff0c;但Oracle还提供了一个更复杂的称为物化视图的方法。

物化视图本质是一个物理表&＃xff0c;它包含了将会由视图的定义返回的记录。如果将普通的视图看做存储在数据库中的查询语句&＃xff0c;物化视图就可以被看作是数据库中存储的查询结果。

Oracle可以自动维护物化视图&＃xff0c;保证是最新的或是定期更新。

26、物化视图最佳实践&＃xff1a;

针对物化视图所包括的每一张表&＃xff0c;都要创建物化视图日志。

使用CREATE DIMENSION语句来标识各维度之间的层次关系。

设置参数QUERY_REWRITE_ENABLED&＃xff0c;启动查询重写功能。

选择合适的物化视图刷新策略。推荐使用ON DEMAND刷新策略&＃xff0c;不是ON COMMIT刷新策略&＃xff0c;适用于大量数据更新时刷新。

选择参数QUERY_REWRITE_INTEGRITY。

合理创建物化视图。如果有疑虑&＃xff0c;则需要比较并评估创建与不创建物化视图对查询和DML语句的性能影响&＃xff0c;以衡量物化视图带来的额外开销能否从查询性能的提高中得到足够的补偿。