数据库设计参考标准

数据库设计参考标准

文档控制

文档属性

文档修订历史

[1]

一、

概述

为明确公司项目中数据库逻辑设计及物理设计的内容和流程，特制定本规范，供数据库设计、开发及维护人员参考。

数据库设计方法目前可分为四类：直观设计法、规范设计法、计算机辅助设计法和自动化设计法。新奥尔良法是目前公认的比较完整和权威的一种规范设计法。新奥尔良法将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。目前，常用的规范设计方法大多起源于新奥尔良法，并在设计的每一阶段采用一些辅助方法来具体实现。

以下是两种常用的规范设计方法：

1. 基于E-R 模型的数据库设计方法。该方法是由P .P .S.chen 于1976年提

出的数据库设计方法，其基本思想是在需求分析的基础上，用E-R （实体—联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS 的概念模式。

2. 基于3NF 的数据库设计方法。该方法是由S·Atre 提出的结构化设计方法，

其基本思想是在需求分析的基础上，确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF 的约束条件，将其进行投影分解，规范成若干个3NF 关系模式的集合。其具体设计步骤分为五个阶段：

（1） 设计企业模式，利用规范化得到的3NF 关系模式画出企业模式； （2） 设计数据库的概念模式，把企业模式转换成DBMS 所能接受的

概念模式，并根据概念模式导出各个应用的外模式；

（3） 设计数据库的物理模式（存储模式）； （4） 对物理模式进行评价； （5） 实现数据库。

备注：数据库设计规范、数据编程规范、数据库物理设计规范中以Oracle 数据库为例，其它结构的数据库类似。

二、

[2]

数据库设计流程

以规范性设计为例，把数据库设计流程分为以下几个阶段。 (一) 需求分析阶段

1. 需求收集和分析，得到数据字典描述的数据需求和数据流图描述的处理

需求。

2. 需求分析的重点：调查、收集与分析用户在数据管理中的信息要求、处

理要求、安全性与完整性要求。

3. 需求分析的方法：调查组织机构情况、各部门的业务活动情况、协助用

户明确对新系统的各种要求、确定新系统的边界。

4. 常用的调查方法有：跟班作业、开调查会、请专人介绍、询问、设计调

查表请用户填写、查阅记录。

5. 分析和表达用户需求的方法：主要包括自顶向下和自底向上两类方法。

采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。 6. 数据流图（Data Flow Diagram ，DFD ）表达了数据和处理过程的关系。

系统中的数据则借助数据字典(Data Dictionary，简称DD) 来描述。 (二) 概念结构设计阶段

概念模型用于信息世界的建模。概念模型不依赖于某一个DBMS 支持的数

据模型。通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS 的概念模型，可以用E-R 图表示。概念模型可以转换为计算机上某一DBMS 支持的特定数据模型，其特点为：

1. 具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知

识。

2. 应该简单、清晰、易于用户理解，是用户与数据库设计人员之间进行交

流的语言。 (三) 逻辑设计阶段

将概念结构转换为某个DBMS 所支持的数据模型(例如关系模型) ，并对其进行优化。将E-R 图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式, 这种转换一般遵循如下原则：

1. 一个实体型转换为一个关系模式，实体的属性就是关系的属性； 2. 一个多对多的联系转换为一个关系模式。

数据模型的优化，确定数据依赖，消除冗余的联系，确定各关系模式分别属于第几范式。确定是否要对它们进行合并或分解。一般来说将关系分解为3NF 的标准。

(四) 物理设计阶段

[3]

为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法) 。根据DBMS 特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。

(五) 数据库实施阶段

运用DBMS 提供的数据语言(例如SQL) 及其宿主语言(例如C) ，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

(六) 数据库运行维护阶段

在数据库系统运行过程中必须不断地对其进行评价、调整与修改。内容包括：数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。

为加快数据库设计速度，目前有很多数据库辅助工具(CASE工具) ，如Rational 公司的Rational Rose ，CA 公司的Erwin 和Bpwin ，Sybase 公司的PowerDesigner 以及Oracle 公司的Oracle Designer等。

三、

数据库设计规范

(一) 数据库规范化的总体要求 1. 数据表中避免可空列

虽然表中允许空列，但空字段为一种特殊的数据类型，数据库将对其进行特殊的处理，为此将增加数据库处理记录的复杂性，且当表中存在较多空字段时，在同等条件下，数据库处理的性能将降低许多。因此，在数据库表设计时应尽量避免。若确实需要，可通过一些折中方式处理，让其对数据库性能的影响降低至最少。在此推荐两个方式。

A ． 设置默认值。默认值设置原则为业务启动后不可能达到的值，或者为

原始状态的对应值。如无法从数值上区分是否为原始状态则不推荐设置默认值。以数值型字段为例，假设其用于存放采集终端所获取的子舱位订座，由于订座数默认至少为大于等于0的数值，为此可设置默认值为-1。另一个例子，字符型的航班状态，默认情况下航班为正常执行的正班，当因为天气、机械或其它调控等原因状态才发生改变，此时可设置默认值为“正班”。日期型数据可默认为1900-01-01，在应用层面显示需要根据实际情况进行转换。

B ． 第二种方式是建立副表。这是当一张表中允许为空的列较多时，如接

近表全部列数的三分之一，且这些列数据在大多情况下均为空，此时建议另外建立一张副表，以保存这些列；主表与副表之间根据关键字关联，这样将数据分别存储于两个独立的表中使得主表设计更为简单，既保证了数据库性能，又能满足存储空值的应用需要。

如果字段默认值设置导致部分业务需求无法满足或开发代价大量增加，此时不建议设置，保留可空。如数值型字段，如在该字段上需要与其他字段进行组合运算、或基于该字段进行统计汇总时，空值具有不可替代的重要作用。

[4]

2. 数据表中不应存在重复数据值或列

如客户数据与客户经理数据存储问题，如将二者放在同一张表中，为解决多个客户经理问题，表中必须设置首选客户经理、备选客户经理相关信息。在客户经理离职后，必须修改表中所有相关数据，造成很多不便且不利于追踪。

为此，在数据库设计的时候要尽量避免现象发生，建议改变策略，将客户信息存入一张表，客户经理信息存入一张表，而客户与客户经理之间的联系关系存入第三张表。当联系关系发生变化时，仅需设置过期日期或状态即可。

3. 数据表主键设置为无意义数值序列号

在进行数据库表设计的时候，采用一个无意义的、数值型的PKID 对行记录进行唯一的标识，而不是通过航班号、航段、姓名、工号等具有业务意义的字段区分记录。由于人工管理下很难保证PKID 值的不重复，为此建议每个表对应的PKID 列的值由数据库自动管理。建议在原有业务上需要唯一区分的字段或字段组合上，建立唯一性索引。以存储10年的航班计划表为例，业务上唯一区分一个航班的通常包含三个字段，航班号、航段、起飞日期，先前的做法是在这三个上建立复合主键。在此推荐用一个Number （8）的PKID 列作为主键，原因是以每天600个航班、一年365天，10年总计219万个航班，考虑到业务的扩展速度为10年内翻三番，657万，按理可设置7位，考虑到其它可能损耗的ID 值，多设置一位，故为Number(8)；在航班数据的增删改过程中，通常需要判断是否存在重复数据，是则为修改操作，否则为插入操作，此时仅以PKID 列无法区分，为此建议在航班号、航段、起飞日期上建立唯一性索引，由数据库后台执行一致性校验。利用Sequence 生成的PKID 若需要用于其他数据库使用，需要在应用层面记录，以防止数据库出现问题时恢复后sequence 不匹配问题。

4. 数据库对象前缀名统一

一个应用系统对应的数据库表、视图、过程等对象，通常均高达千计，为对其进行规范管理与快速定位，要求遵循数据对象命名规范。具体命名规范详见下文第（二）点。

5. 数据表上避免设置外键

外键的作用在于两方面，一是保证主从表数据一致性，这是数据库提供的、用于保证数据质量的强制性手段，二是定义主表数据删除时从表数据的级联删除方式；二者是外键的优势所在，减少了开发人员不分工作量。

但是，外键的存在同样带来困扰。一是从应用系统的三层架构上考虑，如何设置外键关系到业务逻辑，这是将业务逻辑层部分功能后移至数据库层，导致各层职责不清的紧耦合现象，而且当业务逻辑随着需求发展发生变化将引起数据库层面与应用层面的双重修改；二是在开发过程中，开发人员不得不了解数据库物理设计细节，在项目协作分工上造成不便；三是从后续主数据、各业务方向公共数据建设上，为数据库管理员在执行数据表从“公有区”移入移出操作增加难度。

为此，建议不设置外键；如果设置了外键，则仅保留数据一致性校验功能，不建议设置级联删除的选项。此时要求开发人员通过培养一种良好的编程习惯，从程序逻辑上实现主表、从表数据的增、删、改、查的规范操作。具体可参考如下说明。

A. 从表数据增加操作

首先从主表上查询是否存在所需数据，是则可进行增加操作；否则需先

[5]

增加主表数据后才可进行从表数据增加操作。 B. 从表数据删除操作

直接删除从表数据。当主表数据未被任何从表引用时，根据实际业务需要判断是否级联删除主表数据。 C. 从表数据修改操作

从表数据修改后的内容必须是主表内存在的数据，否则需先增加主表数据后才可进行数据修改操作。 D. 主表数据增加操作

直接数据数据，与从表数据无关。 E. 主表数据删除操作

删除之前判断是否存在引用的从表数据，是则根据业务规则决定是否继续，并根据业务规则判断从表引用数据的处理方式为对应数据项置空或删除从表数据记录。如果未存在引用的从表数据，则可直接删除。注意保持事务的原子性，即主、从表数据同时提交或同时回滚。 F. 主表数据修改操作

判断是否存在引用的从表数据，是则首先修改从表数据，然后进行主表数据修改。注意保持事务的原子性，即主、从表数据同时提交或同时回滚。

为了在应用程序上能顺利完成上述六项操作，开发人员除了严格记录主从表调用关系外，还必须在程序上实现数据一致性检查功能，以及时发现可能的数据不一致现象。如果应用程序未能实现这种一致性检查工作，则需将检查规则提交予DBA ，由DBA 在后台进行定期检查。

6. 避免在数据库上编写存储过程、代码包、触发器

通常存储过程、代码包、触发器包含很多业务逻辑，违背了三层架构设计的松耦合原则。

(二) 数据对象命名规范 1. 命名规范总要求

所有数据对象命名必须遵循如下规范要求。

（1） 通常采用“望文知义”的形式命名，部分对安全具有特殊要求的可以

编号命名。

（2） 命名格式是，以字母打头，包含具有特定含义的一个或多个英文单词，

[6]

单词之间以下划线“_”间隔，长度不超过30个字符；

（3） 大小写不敏感。 （4） 禁止使用关键字命名。

（5） 不同数据对象命名的前缀要求如下表所示。

2. 表命名规范

对于同一应用，在命名上区分不同功能模块的所使用的表。假设一个系统包含三个模块，基础维护、查询、系统管理，则基础维护表名前缀为“TB_BASE_”，查询模块表名前缀为“TB_QURY_”，后面加上具体的表名。

对于一些特定作用的表，增加特定后缀表示。如历史航班表可命名为TB_FLIGHT_HISTORY，复杂计算中间过程所产生的临时数据表可命名为TB_MIDTERM_DATA_TEMP。

3. 字段命名规范

字段命名规范符合命名规范总要求。 字段类型选择满足

（1） 固定长度的字符串类型采用CHAR ，非固定长度的字串类型采用

VARCHAR2。

（2） 日期型字段采用日期类型。

（3） 数字型字段采用NUMBER 类型，并标明长度与小数位数。 （4） 如无特殊需要，避免使用大字段(BLOB，CLOB ，LONG 等) 。 （5） 相同业务字段出现在不同表中，使用相同命名，且保证类型和长度

[7]

一致。

4. 索引命名规范

A. 复合索引至少包含前两个字段的缩写或全名。

B. 在表上创建主键时，必须显式指定主键唯一性约束名称。

举例：为表TB_FLIGHT创建主键，正确格式为“alter table TB_FLIGHT add constraint PK_FLIGHT primary key (FLIGHT_ID)”，语句中指定了主键的唯一性约束名为PK_FLIGHT。而错误的格式：“alter table TB_FLIGHT add primary key (FLIGHT_ID)”

此中，主键唯一性约束将被系统随机分配一个名字。 5. 分区命名规范

采用可容易判断各分区内所存数据内容的名字，如对表TB_FLIGHT按季度进行分区，则各分区命名格式为“PT_ XXXXQN”，其中XXXX 为年份，N 为1至4的季度编号。

6. 触发器命名规范

命名规则：trg_表名__。如表TB_FLIGHT行插入后的触发器名字为,TRG_TB_FLIGHT_AFTER_INS。 (三) 数据库设计说明书格式

数据库设计说明书的格式至少包含如下部分。

1. 概述：从整体上说明数据库设计思想、采用的方法、作用范围、参考的

文献、文档结构组成等。 2. 数据表清单

3. ER 关系描述

描述表之间存在的主外键联系等。 4. 数据表定义

1) 表1

[8]

2) 表2

5. 各系统相关的其它信息。

要求在“数据表清单”每一行的表名上定义“超链接”指向“数据表定义”中的对应内容，以方便地在文档中浏览跳转等。 四、

数据库编程规范

(一) 书写规范

1. 关键字大写，其他代码统一使用小写。

2. 确保变量和参数在类型和长度上与表数据列类型和长度相匹配。建议采

用如下格式：

vs_flightno tb_flight.flight_no%TYPE

这样当TB_FLIGHT表的FLIGHT_NO字段定义发生变化时程序无需调整。

3. 参数和变量命名前缀遵循如下规范：

4. 程序块中的begin 、end 独立成行。

5. 程序块采用缩进风格书写，保证代码清晰易读，缩进格数统一。 6. 一行仅写一条语句。

7. 同一语句占用多行时，每行的第一个关键字左对齐。

8. 对于 Insert „ values 和 update 语句，一行写一个字段，字段后面紧

跟注释（注释语句左对齐），values 和 insert 左对齐，左括号和右括号

[9]

与 insert 、values 左对齐

例：

insert into table_name

(

user_id, --用户 ID ，主键

user_name, --用户名

login_name --登录名

)

values

(

v_user_id,

v_user_name,

v_login_name

）

9. 相对独立的程序块之间增加一空行。

10. 超过110列的语句要分行书写，长表达式应在低优先级操作符处换行，

操作符或关键字放在新行之首。

(二) 注释规范

1. 以统一格式在头部对代码进行注释，以说明代码所要完成功能、传入参

数、传出参数、作者、创建时间，并在其后从修改日期、修改者、修改

内容角度描述各次修改历史。

/*

功能描述：-----------------

传入参数：-----------------

传出参数：-----------------

返回结果：-----------------

作者：-----------------

创建日期：-----------------

修改历史：-----------------

（1）YYYY-MM-DD ：作者1，修改内容1；

（2）YYYY-MM-DD ：作者2，修改内容2；

*/

2. 在所有变量定义的右侧或上方，进行注释以说明变量的用途与含义。

3. 注释内容清晰、明了，并确保无二义性。

[10]

4. 对每一程序分支书写注释。

5. 在代码的功能分支、子层次上注释，以帮助维护人员理解代码。

例：

case vs_type1

when 1 then --里程奖励

... ...

when 2 then --里程扣除

... ...

when 3 then --里程兑换

... ...

end case;

6. 代码注释应放在描述的代码上方或右方相近位置，不可放在下面。

7. 注释与所描述的内容进行字数的缩进排列。

8. 在函数中，对所返回的代码进行详细描述。

9. 在程序块的结束行右方加注释，以表示程序块结束。

(三) 语法规范

1. 存储过程的In 、out 参数应按类别分开书写，不要交叉。

2. 存储过程中变量的声明应集中在is 和begin 关键字之间申明

3. 尽可能使用相关表字段类型来定义相关的变量，如%type，%rowtype。

4. 删除无用的变量与参数。

5. 存储过程中存在多分支时，若有事务控制，需确保各分支均有事务结束

处理，且发生异常时，必须Exception 中进行异常捕获。

(四) SQL 性能规范

1. 避免在批量操作时频繁使用commit

频繁的COMMIT 会导致物理I/O增大，同时会产生log file sync等待，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT 时必须保证事务的完整性。

2. 使用动态 sql 时，需绑定变量，而非实际值。

为了不重复解析相同的SQL 语句，在第一次解析之后，ORACLE 将SQL 语句缓存在内存中，当你执行一个SQL 语句时，如果它和之前的执行过的语句完全相同，ORACLE 就能很快获得内存中相同语句的执行计划，然后就直接执行该语句，而不需要进行解析。

如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，ORACLE 还是需要对每条结构相同的语句重新解析一遍，同时在内存中缓存该语句，导致共享池中存放了N 条结构相同的语句，既达不到共享的

[11]

目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。

3. 避免不必要的排序

对查询结果进行排序会大大的降低系统的性能

4. 用WHERE 子句替换HAVING 子句

例如：

SELECT NAME ，SUM(AGE)

FROM EMPLOYEE

GROUP BY NAME HAVING NAME ！=„ABC‟

修改为以下语句效果更好

SELECT NAME ，SUM(AGE)

FROM EMPLOYEE

WHERE NAME ！=„ABC‟GROUP BY NAME

5. 用“>=”替代“>”

如：在ID 列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS 将直接跳到第一个ID 等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT 大于8的记录。

6. 删除表中所有记录时用TRUNCATE 替代DELETE

当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息，如果你没有COMMIT 事务，ORACLE 可以将数据恢复到删除之前的状态；而当运用TRUNCATE 时，回滚段不存放任何可被用于恢复的信息，当命令运行后，数据不能被恢复, 因此很少的资源被调用，执行时间也会很短, 空间立即释放，detele 操作后的空间可以被重新利用，但不会释放。

7. 用UNION-ALL 代替UNION

说明：UNION-ALL 不过虑重复记录，UNION 过滤重复记录，所以需要先排序。如果不需要过滤重复的记录，UNION-ALL 比UNION 性能更好。

8. 用(NOT) EXISTS替代(NOT) IN。

在许多基于驱动表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

例如：

SELECT *

FROM EMP WHERE EMPNO > 0

AND DEPTNO IN

(SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')

没有以下语句高效

SELECT *

FROM EMP WHERE EMPNO > 0

AND EXISTS (SELECT 'X' FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = 'MELB')

[12]

9. 使用DECODE 函数来减少处理时间。

使用DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。

例：

SELECT NAME||‘UNDER AGE’FROM EMPLOYEE WHERE AGE

SELECT NAME||‘OVER AGE’FROM EMPLOYEE WHERE AGE>=18 可以改成：

SELECT

DECODE(SIGN(AGE-18),-1,NAME||‘UNDERAGE ’，1，NAME||‘OVERAGE ’)

FROM EMPLOYEE

10. 使用表的别名(Alias)。

当在SQL 语句中连接多个表时，应使用表的别名并把别名前缀于每个列上，这样可以减少解析的时间，并减少那些由Column 歧义引起的语法错误。

11. 尽量减少对表的查询次数。

说明：在含有子查询的SQL 语句中，要特别注意减少对表的查询。

例：

UPDATE EMP SET

EMP_CAT =(SELECT MAX(CTEGORY) FROM EMP_CATEGORIES), SAL_RANGE=(SELECT

MAX(SAL_RANGE)FROMEMP_CATEGORIES)

WHERE EMP_DEPT=‟0020‟

改写成下面这样会更高效:

UPDATE EMP SET EMP_CAT, SAL_RANGE =

(SELECT MAX(CATEGORY),MAX(SAL_RANGE)

FROM EMP_CATEGORIES) WHERE EMP_DEPT = „0020‟

12. 用表连接替换EXISTS 。

例：

SELECT ENAME

FROM EMP E

WHERE EXISTS (SELECT 'X'

FROM DEPT

WHERE DEPT_NO = E.DEPT_NO

AND DEPT_CAT = 'A');

改写成下面这样会更高效:

SELECT ENAME

FROM DEPT D, EMP E

WHERE E.DEPT_NO = D.DEPT_NO

AND DEPT_CAT = 'A';

13. 避免使用DISTINCT ，可以用EXISTS 替换DISTINCT 。

当提交一个包含一对多表信息(比如部门表和雇员表) 的查询时, 避免在SELECT 子句中使用DISTINCT. 一般可以考虑用EXIST 替换

[13]

例:

SELECT DISTINCT DEPT_NO, DEPT_NAME

FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO

改写成下面这样会更高效:

SELECT DEPT_NO, DEPT_NAME

FROM DEPT D

WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO =

D.DEPT_NO);

EXISTS 使查询更为迅速，因为RDBMS 核心模块将在子查询的条件一旦满足后，立刻返回结果。

14. 避免使用耗费资源的操作

带有DISTINCT ，UNION ，MINUS ，INTERSECT ，ORDER BY，GROUP BY的SQL 语句会启动SQL 引擎执行耗费资源的排序(SORT)功能, 如果可能，应尽量避免使用或改写。

15. 避免对索引列使用数据库函数、计算表达式等等

对查询索引列使用数据库函数、计算表达式会造成对索引失效

例：

select t.reserver1, count(*) cout

from custcare.password_login_fail t

where t.operating_source = 1

and to_char(t.login_time,'yyyymmdd') between 20080904 and 20080918 group by t.reserver1

having count(*) > 30

custcare.password_login_fail表的login_time列上建了索引，上面语句to_char函数会造成索引失效，把

and to_char(t.login_time,'yyyymmdd') between 20080904 and 20080918 修改成：

and t.login_time between to_date('20080904','yyyymmdd') and to_date('20080918','yyyymmdd')可以正确索引。

查询时尽可能将操作移至等式的右边，甚至去掉函数。

16. 在查询条件中，避免不必要的类型转换。

字符类型列赋予数值型数据时会使用隐含类型转换，而数值型列赋予字符型数据时不会隐含类型转换，而是将右边值转换为数值类型。

例：

select * from atp_para_cfg where cfg_type=35

cfg_type是字符型列，上面语句会造成隐含类型转换to_number(cfg_type)=35，即Oracle 内部会做一次类型转换。

17. 尽量避免字段与“NULL ”比较

索引并不存储null 值，使用NULL 作为条件将不会使用索引。

例：

select * from staff_member where address is null ；（或not null）

[14]

该语句将不会使用索引

18. 在索引列上使用(!=)和 like 将不会使用索引。

例：

select * from staff_member where dept_no2001;

select * from staff_member where first_name like ‘%DON’;

以上语句都不会用到索引

注:like语句改为 select * from staff_member where first_name like ‘DON%’;

当前面没有%时，将使用索引，ORACLE 内部转换为范围操作

19. 用Case 语句合并多重扫描

例：

select count(*) from emp where sal

select count(*) from emp where sal between 1000 and 5000;

select count(*) from emp where sal>5000;

这样我们需要进行三次全表查询，但是如果我们使用case 语句：

select

count (sale when sal

then 1 else null end) count_poor,

count (sale when between 1000 and 5000

then 1 else null end) count_blue_collar,

count (sale when sal >5000

then 1 else null end) count_poor

from emp;

这样查询的结果一样，但是执行计划只进行了一次全表查询。

(五) JOB 使用规范

1. Job 的执行时间尽量避开业务高峰；

2. 如果有多个占用资源较多的job 任务，考虑将它们执行的时间错开；

3. 禁止在job 运行期间对其调用的存储过程进行修改和编译。

4. 在Oracle10g 之后，利用dbms_schedule建立Job 。

(六) 索引使用规范

1. 索引创建原则

（1） 表的主键、外键必须有索引；

（2） 数据量超过300条的表应该有索引；

（3） 经常与其他表进行连接的表，在连接字段上应该建立索引；

（4） 经常出现在Where 子句中的字段，特别是大表的字段，应该建立索

[15]

引；

（5） 索引应该建在选择性高的字段上；

（6） 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建

索引；

（7） 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

正确选择复合索引中的主列字段，一般是选择性较好的字段；

复合索引的几个字段是否经常同时以AND 方式出现在Where 子

句中？单字段查询是否极少甚至没有？如果是，则可以建立复合

索引；否则考虑单字段索引；

如果复合索引中包含的字段经常单独出现在Where 子句中，则分

解为多个单字段索引；

如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，

考虑减少复合的字段；

（8） 频繁进行数据增删改操作的表，不要建立太多的索引；

（9） 删除无用的索引，避免对执行计划造成负面影响；

2. 索引使用建议

（1） 避免对条件字段进行操作

对条件字段的操作会导致该列的索引失效，这里所谓的操作包括数据库函数、计算表达式等。

如以下3个sql ，其条件列上都建有索引，但不会被使用：

select * from record where substrb(CardNo,1,4)='5378' --(13秒)

select * from record where amount/30

select * from record where to_char(ActionTime,'yyyymmdd')='19991201' --（10秒）

可改为如下形式：

select * from record where CardNo like '5378%' --（

select * from record where amount

select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd') --（

（2） 避免隐式转换

对于条件值书写不规范的，oracle 会自动对条件列进行隐式转换，以完成比较，但同时也会造成索引失效。

[16]

如将字符型数据与数值型数据比较，ORACLE 会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

例：表tab1中的列col1是字符型（char) ，则以下语句存在类型转换： select col1,col2 from tab1 where col1>10，

应该写为：

select col1,col2 from tab1 where col1>'10'。

（3） 尽量避免“”

尽量去掉 ""，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR" 方式。

例：UPDATE SERVICEINFO SET STATE=0 WHERE STATE0;

以上语句由于其中包含了""，执行计划中用了全表扫描（TABLE ACCESS FULL ），没有用到state 字段上的索引。实际应用中，由于业务逻辑的限制，字段state 为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉""，利用索引来提高效率。

修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。

（4） 避免Where 子句中的IS NULL和IS NOT NULL

Where 字句中的IS NULL 和IS NOT NULL 将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where 子句中的IS NULL和IS NOT NULL。

（5） 数据分布不均匀列的索引使用

当列值选择性不高，但查询返回值恰好是返回较少的数值，则应创建索引。 例：表ServiceInfo 中数据量很大，有一百万行，其中有一个字段DisposalCourseFlag ，取值范围为枚举值：[0，1，2，3，4，5，6，7]。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低。

然而，由于该字段上数据值的分布情况非常特殊，具体如下表：

取值范围 1~5 6 7

占总数据量的百分比 1% 98% 1%

而且，常用的查询中，查询DisposalCourseFlag

（6） 需要时利用HINT 强制指定索引

[17]

对于ORACLE 优化器无法用上合理索引的情况下，一方面可以单独对该字段或该表用analyze 语句进行分析，对该列搜集足够的统计数据，使ORACLE 在查询选择性较高的值时能用上索引；另一方面，可以利用HINT 提示，在SELECT 关键字后面，加上“/*+ INDEX(表名称，索引名称）*/”的方式，强制ORACLE 优化器用上该索引。

例：

select /*+ index(mms_cdr IDX_MMS_USERID)*/ *

FROM mms_cdr

WHERE (month_number >= 6 and month_number

AND user_id = to_number('[**************]1')

AND send_time >= '[1**********]000'

AND send_time

ORDER BY send_time;

当user_id和send_time列上均有索引的情况下使用user_id列上的索引更高效，因为user_id列上的索引选择性更高，但是由于统计信息不全，优化器可能选择使用全表扫描或者走send_time列上的索引，导致执行计划不是最优，此时可以使用强制索引来解决。

（7） 屏蔽无用索引

如果了解到条件字段建有索引，但返回值较多，如果走索引的话效率更低，则可通过特殊方法屏蔽掉索引的使用。如字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：“||""”

例：select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36'

（8） like 子句尽量前端匹配

当like 字句前段为通配符，会导致索引失效。

如：select * from city where name like ‘%S%’; --该语句执行全表扫描 修改为：

select * from city where name like „S%‟ ; --会正确使用索引

（9） 使用基于函数的索引

对条件列的操作可以使索引失效，但如果根据操作创建相应索引，则可以被使用。 如：select * from emp where substr(ename,1,2)=‟SM‟;

在ename 列上的索引将不会被使用，如果创建基于函数的索引，即：

create index emp_ename_substr on eemp ( substr(ename,1,2) );则在查询的时候可正确使用到索引。

使用函数索引有如下限制：

[18]

无法索引LOB 字段

无法索引嵌套表字段

表达式不允许有分组函数，如SUM ，AVG 等

无法使用基于规则的优化器

无法储存空值

对于一个输入，要求函数返回同一值，即确定性

当函数改变时，要重建函数索引

（10） 使用分区索引

在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle 的数据字典中。Oracle 可以利用这个信息来提取出那些只与SQL 查询相关的数据分区。

例如，假设你已经定义了一个分区索引，并且某个SQL 语句需要在一个索引分区中进行一次索引扫描。Oracle 会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

但是当查询跨多个分区，涉及到比较大小时，考虑使用全局索引。

（11） 使用位图索引

当列值的选择性非常低时，比如性别字段只有男与女两个值，可以考虑使用位图索引。位图索引占用空间非常小，效率较高。位图索引适合于不会变化的表，如果dml 操作频繁，则极易产生大面积锁。

（12） 使用复合索引

当几个字段经常同时以 and 方式出现在 where 子句中，可以建立复合索引；将最常用的列放在索引最前面；复合索引字段不宜过多，尽量在3个以内。

（13） 少数情况下用全表扫描替代索引扫描

在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总

[19]

体原则是：

对于原始排序的表：仅读取少于表记录数40％的查询应该使用索引范围

扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。

对于未排序的表：仅读取少于表记录数7％的查询应该使用索引范围扫

描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

(七) 分区表使用规范

当表的数据量达到1G 或者1000万行时，考虑将表分区；

分区键的选择参考业务查询要求，通常是时间字段，以range 方式来划分。分区间隔也要看业务需要，比如单次查询总是以天为单位，则按天分区，如果总是以小时为单位，则按小时分区，但并非分得越细越好；

如果插入数据所需要的分区不存在，则插入失败，所以要制定分区维护策略。通常有两种方式，一种是提前划分，比如把一年的分区一次性划分出来，用完时再划一年，另一种是编写分区维护存储过程自动添加或删除分区。

五、 数据库物理设计规范

(一) 环境配置

数据库环境包含生产、开发、测试三种，各种环境之间的配置原则为：

1. 生产、开发环境分离；

2. 生产、开发职责分离；

3. 由于一个业务方向配置一套生产库集群，不同业务方向数据交互通

过中间件平台实现，不直接使用DBLlink 访问。

4. 一个生产应用对应一个开发环境。

5. 测试环境根据实际需要部署。

各种环境的硬件需求（CPU 频率，内存容量，磁盘容量等）及软件需求（操作系统版本及内核参数，交换分区大小，用户权限等）详见可参考业务实际需要及Oracle 在各操作系统下的官方安装文档。

[20]

(二) 相关命名要求

1. 服务名命名要求：以字母开头、不超过8个字符；如为集群数据库，各

节点实例名以‘集群服务名’后加序号组成。

2. 表空间命名要求：以用户名、主要存储对象类型组成。

3. 数据文件命名要求：以表空间名后加序号组成，扩展名为“*.DBF”。

4. 控制文件命名要求：以“CTL ”为前缀，后加序号组成，扩展名为“*.CTL”。

5. 日志文件命名要求：以“LOG ”为前缀，后加日志组号、文件编号组成，

扩展名为“*.RDO”。

6. 临时文件命名要求：以“TMP ”为前缀，后加文件编号组成，扩展名为

“*.TMP”。

为保证OEM(Oracle Enterprise Manager ，企业管理器) 的安装，主机名不得包含大写字母，以及“_”。对于RAC 的安装，要求主机名长度尽量小，在6位以内，同时不得为大写，不要包含“-”和“_”。

(三) 数据库配置

1. 数据库连接类型

通常使用专用数据库连接。

2. SGA 配置

初次设置为系统内存60%，在数据库运行期间根据运行性能调整。

3. PGA 配置

初次设置为系统内存20%或大于1G ，在数据库运行期间根据运行性能调整。

4. 数据库字符集

统一使用“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”字符集。

5. 数据块大小

为操作系统块大小（裸设备块大小512字节，NTFS 块大小 4K ）整数倍，默认取值为8K 。

6. DB_FILES

Db_files是数据库能够同时打开的文件数量，默认值是200个。当数据库规划时文件数量FILES 接近或超过200个时候，按以下估计值配置:

[21]

DB_FILES = FILES * 1.5

7. SGA_TARGET

设置该项即打开SGA 自动管理，建议使用。

8. PROCESSES ：操作系统层所允许连接的最大进程数（包含会话数），

建议其值至少设置为500。

9. SESSIONS ：数据库最大会话数，其值为（系统最大并发连接数＋后

台进程数）*1.1，如果未指定，则系统根据PROCESSES 参数值计算其默认值。

10. OPEN_CURSORS：由用户可能同时打开的游标数、会话数决定，建

议其值至少设置为1000；

11. OPTIMIZE_MODE：默认值为EXACT ，为提高执行计划复用度可设置

为CHOOSE 。

12. USER_DUMP_DEST、BACKGROUDD_DUMP_DEST、

CORE_DUMP_DEST：此三个参数用于定义系统跟踪文件的存放位置，建议指向数据库系统主目录下的三个分目录。

13. GOLBAL_NAMES：取值为真时，客户端TNS 配置中的服务名须为“数

据库服务名. 域名”方可正常连接；取值为假时，用“数据库服务名”即可。

(四) 数据库文件管理

1. 控制文件

（1） 为保证安全及性能，使用多个控制文件，并放置在不同物理位置。

（2） 控制文件中参数设置，最大的数据文件数量不能小于初始化参数

db_files。

2. 日志文件

（1） 日志文件大小

日志文件的大小由数据库事务处理量决定，在设计过程中，确保每20分钟切换一个日志文件。所以对于业务繁忙的系统，日志文件大小为几百M 到几G 的大小。

（2） 日志文件组数量

[22]

对于批处理系统，日志文件组为5—10组；对于OLTP 系统，日志文件组为 3—5组，每组日志大小保持一致；对于集群数据库系统，每节点有各自独立的日志组。

（3） 日志成员数量

为了确保日志冗余，每日志组的成员最少为2个

(五) 表空间设计

1. 永久表空间设计

（1）

（2）

（3）

（4）

（5）

（6）

（7）

（8） 不同模式（schema ）使用不同表空间 使用本地管理表空间 使用ASSM 自动段空间管理 使用uniform extents size选项，大小选择1m 尽量不使用数据文件自动扩展，以免占满文件系统 两个IO 很大的段尽量不放在同一表空间 写操作频繁的数据文件尽量不放在raid5磁盘上 索引与数据存于不同表空间

2. UNDO 表空间

Undo 表空间大小的设计规范由以下公式计算：

Undospace = UR * UPS *db_block_size+ 冗余量

UR ：表示在undo 中保持的最长时间数（秒），由数据库参数UNDO_RETENTION值决定。

UPS ：表示在undo 中，每秒产生的数据库块数量。

例如：在数据库中保留2小时的回退数据，假定每小时产生200个数据库块。则Undospace = 2 * 3600 * 200 * 4K = 5.8G

3. 临时表空间

数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。

4. 系统表空间

系统表空间初始大小1G 左右，除了存放数据库数据字典的数据外，其他数据不得存储在系统表空间。

六、 数据库安全规范

(一) 用户密码规范

1. 用户名与密码不可一致

2. 密码最小长度为6

3. 密码不能与字典相同，默认字典包含'welcome', 'database', 'account',

'user', 'password', 'oracle', 'computer', 'abcd'

[23]

4. 密码至少需要包含一个字母，一个数字和一个标点符号

(二) 用户权限使用规范

1. 同一个业务方向建立一个方向内公共的所有者用户，用于创建方向内共

用的数据表或其它数据对象。

2. 同一应用分配两个用户，一个为所有者用户，另一个为使用者用户。

a) 所有者用户为可用于创建表等数据对象的用户，用户名后缀默认为

“_adm”，通常拥有connect,create table,create view,create

trigger,create sequence,create procedure 权限以及给定表空间的

使用配额。应用系统一经上线，开发人员无权使用所有者用户。

b) 使用者用户为应用程序连接使用的用户，用户名后缀默认为“_app”，

通常拥有connect,create synonym 权限，在开发期间还须拥有

debug connect session权限。

c) 所有者用户创建数据对象后，将其所有表的

select,update,insert,delete 权限、视图的select 权限、过程与函数

的execute 权限、序列的select 权限授予使用者用户，可通过如下

sql 语句一次性提取授权信息，并执行。

SELECT 'grant select,update,insert,delete on '||table_name||' to **_app;'

FROM user_tables;

SELECT 'grant select on '||object_name||' to **_app;' FROM user_objects

WHERE object_type IN ( 'VIEW');

SELECT 'grant execute on '||object_name||' to **_app;' FROM user_objects

WHERE object_type IN ( 'PROCEDURE','FUNCTION');

SELECT 'grant select on '||object_name||' to **_app;' FROM user_objects

WHERE object_type IN ( 'SEQUENCE');

d) 使用者用户在被授权之后，需要执行同名创建操作，其目的在于_app

用户使用_adm 用户的表时，不用加前缀“_adm."，也为了后续改

动的可兼容。可通过如下sql 一次性提取结果，并执行。

SELECT 'create synonym '||object_name||' for **_adm.'||object_name||';'

[24]

FROM all_objects WHERE owner IN ('**_ADM') ;

3. 删除或锁定无关帐号。

4. 限制SYSDBA 远程登录。

在spfile 中设置REMOTE_LOGIN_PASSWORDFILE=NONE，或者在sql net.ora 中设置SQLNET.AUTHENTICATION_SERVICES=NONE来禁用 SYS DBA 角色的自动登录

(三) 限制业务用户权限

在数据库权限配置能力内，根据用户的业务需要，配置其所需的最小权； 禁止使用应用的使用者用户进行动态创建数据表操作。

(四) 启用数据字典保护

只有SYSDBA 用户才能访问数据字典基础表（通过设置初始化参数O7_DICTIONARY_ACCESSIBILITY = FALSE 实现，此项是默认设置）

(五) 数据库监听规范

1. 在需要时为监听设置密码

方法如下：

$ lsnrctl

LSNRCTL> change_password

Old password: Not displayed

New password: Not displayed

Reenter new password: Not displayed

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)(IP=FIRST)))

Password changed for LISTENER

The command completed successfully

LSNRCTL> save_config

2. 在需要时设置监听的信任IP 集

方法如下：

在文件$ORACLE_HOME/network/admin/sqlnet.ora中加入如下行

tcp.validnode_checking = yes

tcp.invited_nodes = (ip1,ip2…)

[25]

七、 数据库评审

下表为衡量各数据库设计及安装是否符合规范的评审内容参考表。

[26]

序号

18

19

20

21

22

23

24

25

26

27

28

限制SYSDBA 远程登录 评审内容 是否分配给业务用户所需最小权限 用户profile 配置 是否启用数据字典保护 是否设置监听密码 是否设置黑白名单 安装是否符合规范 数据文件设置是否合理 日志文件设置是否合理 控制文件设置是否合理 初始化参数设置是否合理

[27]