关系型数据库性能体系

3万字|关系型数据库性能体系!

1前百

1.1目的

本文档详细定义了关系型数据库的数据库模型设计规范、表的设计规范、分区

表的设计规范、索引的设计规范、其他数据库对象的设计规范、SQL的访问规范、

编码和注释规范，并介绍了SQL调优的关注点和常用方法，作为数据库规划、设

计、开发及维护人员的技术参考资料，用以指导关系型数据库的设计和开发，性能是

设计出来的，质量也是可以设计出来的，理解这篇，关系型数据库性能提升30%不

是问题。

我希望通过该规范的分享，能够实现以系统、体系的工程化思维模式去规范关

系型数据库设计和开发，使数据库结构和编码风格标准化，提高模型的前瞻性、高

效性，以尽早提前避免由于数据库设计不当而产生的麻烦，同时好的规范，在执行

的时候可以培养出好的习惯，好的习惯是软件质量的很好的保证。

目录

1前言1

1.1目的1

1.2预期的读者和阅读建议6

2数据库模型设计规范6

2.1数据库建模原则性规范6

2.2实体型之间关系认定规范7

2.3范式化1NF的规范7

2.4范式化2NF的规范8

2.5范式化3NF的规范9

2.5反范式化冗余字段使用规范9

2.6数据库对象命名基本规范10

2.6.1遵循行业规范10

2.6.2简单命名原则10

2.6.3字符范围原则11

2.6.4字母全部大写或小写原则11

2.6.5勿用保留词原则11

2.6.5同义性原则11

2.6.6富有含义原则11

2.6.7扩展性原则11

3表的设计规范12

3.1命名规范12

3.1.1表的命名规范12

3.1.2字段的命名规范12

32表的设计规范13

3.2.1指定表空间规范13

3.2.2表的主键规范13

3.2.3表的外键规范13

3.2.4字段类型及宽度的规范14

3.2.5一个表所含字段总长度的规范14

3.2.6一个表所含字段访问频繁度的规范15

3.2.7大对象字段(BLOB,CLOB)使用规范15

3.2.8关于字段能否为NULL值15

3.2.9关于冗余列的规范16

3.2.10使用注释的规范16

3.2.11一个表所含数据量的规范16

3.2.12增量同步表的设计规范17

3.3字段类型规范17

3.3.1不使用会发生隐式转换：INTEGER,FLOAT17

3.3.2不使用过时老类型：RAW,LONG,LONGRAW17

3.33国家字符集相关17

3.3.4不能使用大对象：BLOB,CLOB,NCLOB18

3.35不能使用高精度：TIMESTAMP18

3.3.6关于CHAR字段18

4分区表的设计规范18

41表空间及分区表的概念18

4.1.1表空间18

4.1.2分区表18

4.2表分区的具体作用19

4.3表分区的优缺点19

4.4分区表设计规范19

4.2.1不使用全局索引19

4.2.2RANGE分区的规范20

4.2.3LIST分区的规范20

4.2.4HASH分区的规范21

4.2.5RANGE-LIST分区的规范22

42.6RANGE-HASH分区的规范22

5索引的设计规范23

5.1索引分类23

5.1.1单列索引与复合索引23

5.1.2唯一索引与非唯一索引23

5.1.3B树索引、位图索引与函数索引23

52命名规范24

5.3索引设计规范24

5.31指定表空间规范25

5.3.2主键索引的规范25

5.3.3唯一约束索引的规范26

5.3.4外键列索引的规范26

5.3.5复合索引的规范26

5.3.6函数索引的规范27

5.3.7位图索引的规范27

5.3.8反向索引的规范27

5.3.9分区索引的规范27

5.3.10索引重建的规范27

6其他数据库对象设计规范28

6.1命名规范28

6.2视图设计规范28

6.2.1尽量使用简单的视图，避免使用复杂的视图28

6.2.2按照必要性原则建立视图29

6.3存储过程、函数、触发器的设计规范29

6.3.1关于触发器的设计29

7SQL访问规范30

7.1尽量不要写复杂的SQL30

7.2避免使用SELECT*30

7.3INSERT时需写全列名30

7.4进行DML操作时使用CTAS进行数据备份31

7.5大数据量DML操作分多次执行31

7.6使用绑定变量，降低高硬解析31

7.7选择最有效率的表名顺序32

7.8关注WHERE子句中的连接顺序32

7.9用EXISTS替代IN33

7.10用表连接替换EXISTS34

7.11用EXISTS替换DISTINCT35

7.12尽量用unionall替换union35

7.13使用DECODE函数来减少处理时间35

7.14尽量避免用orderby36

7.15用Where子句替换HAVING子句36

7.16减少多表关联37

7.17避免重复访问37

7.17.1使用groupby37

7.17.2用表更新表38

7.17.3竖向显示变横向显示38

7.18完成事务及时commil39

7.19数据库连接及时关闭39

7.20索引的使用39

7.20.1避免在索引列上使用函数或运算39

7.20.2避免改变索引列的类型40

7.20.3避免在索引列上使用N0T40

7.204用＞=替代＞41

7.20.5避免在索引列上使用ISNULL和ISNOTNULL41

7.20.6带通配符（%）的like语句41

7.20.7总是使用索引的第一个列42

8编码及注释规范42

8.1编码规范42

8.L1避免隐式的数据类型转换42

8.1.2不要将空的变量值直接与比较运算符（符号）比较43

8.1.3跨行语句，第一关键字应当左对齐43

8.1.4Insert…values和update语句书写规范43

8.1.5Insert…select语句书写规范44

8.1.6避免使用嵌套的IF语句44

8.1.7减少控制语句的检查次数45

8.1.8语句涉及多个表时，使用别名来限定字段名46

8.1.9其他编码规范47

8.2注释规范50

8.2.1注释语法50

8.2.2每个块和过程开发放置注释50

8.2.3代码注释应放在其上方或右方50

8.2.4其他注释规范51

9PLSQL优化51

9.1性能问题分析51

9.2PLSQL优化的核心思想52

9.3PLSQL优化示例53

9.3.1减少对表的查询53

9.3.2避免循环（游标）里面嵌查询55

9.3.3groupby优化56

9.3.4删除重复记录57

9.3.5COMMIT使用57

93.6批量数据插入58

9.3.7索引使用优化59

9.38使用提示（Hints）60

9.39表上存在过旧的分析61

9.310表上存在并行61

9.311关于索引建立62

9.3.12ExpainPlan分析索引使用62

1.2预期的读者和阅读建议

本文档预期的读者为项目经理、开发经理、DBA、数据结构管理师、系统设计

师、开发师、测试师等相关岗位的人员。

读者可以通篇阅读该文档，以整体熟悉和掌握Oracle数据库设计规范，也可以

重点关注跟自身相关的内容章节，如数据库模型设计、表的设计，或SQL访问规

范、编码和注释规范等。

2数据库模型设计规范

2.1数据库建模原则性规范

对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑模型图是项目组成

员之间在数据库层面沟通交互的依据，必须规范画图(表，主键，外键，关系)。

对于表的个数在20个以上的模型，需要DBA参与设计，并作最终审核。

对于0LTP系统，采用范式化思想进行模型设计，对于OLAP系统，采用面向问

题及多级颗粒度的思想进行模型设计。

需采用主流的模型设计软件工具PowerDesigner,ERWin。

2.2实体型之间关系认定规范

所有实体间的业务逻辑关系，除了语义上保留其原有的业务关系外，本质上都

要转化成关系数据库的三种关系(1:1)(1:N)(N:M),对于3个及以上实体型

之间的“多元关系”，需要DBA参与设计。

比如，实体型A和实体型B之间的关系，可以通过问两个问题来确定他们之间

的关系：一个A可以对应几个B?一个B可以对应几个A?

(1)一个A对应一个B,相反一个B对应一个A,那么A对B就是1:1关系；

(2)一个A对应多个B,相反一个B对应一个A,那么A对B就是1:N关

系；

(3)一个A对应多个B,相反一个B对应对个A,那么A对B就是N:M关

系；

具体实施的时候，掌握如下原则：

■•1:1关系选取任何一个表的主键到另一个表中，作为外键来体现。

■•1：N关系将1表的主键在N表中，以外键形式来体现。

■♦N:M关系采用“关系表”来体现，该关系表的主键是由相关实体表的主键

组成的复合主键；各实体表主键不但组成了该关系表的主键，同时也被看作

外键在该关系表中存在。

■•对于三个以上表之间的“多元关系”常需要和反范式化冗余字段结合起

来设计，以保证查询速度。

2.3范式化1NF的规范

OLTP系统的模型，需要符合第三范式，对于表在20个以上的模型，需要DBA

参与设计。

范式化要求(1NF)：列是访问的最小单位，具有原子性，不可再被分割。

在具体实施的时候，需要依据情况对相应属性进行拆分或者合并：

■•同一个属性值的不同细度把握

比如,常见的“姓名”这个属性,设计一:“姓名”是一个列,设计二:

“姓”是一个列,“名”是一个列,两个列的值组合起来才表达一个“姓名"语

乂。两种设计方法,在不同的系统中都有应用,这主要是依据需求的细度来确定,

灵活把握。

■•把多个属性值错误的作为一个属性值存储

比如:常见的0A系统要存储员工的各种属性,包括技能信息,技能范围:

Oracle,JAVA,.NET,C#,Perl,UNIX等等,一种常见的错误设计是:设计一张员工

表，其中有一个技熊属性字段，然后某员工所掌握的多种技能用逗号(,)间隔,

然后将这个字符串存储到这个员工表的技能属性字段中。

这里的错误在于将多个属性值作为一个属性值存储在一个字段中,不能满足直

接遍历员工对某个技能掌握情况,而且如果再要求说明员工对个技能的掌握程度

(精通,熟悉,一般等等),则再增加字段,里面的对应关系将很容易错乱,这是

严重违反1NF的情况。

正确的设计应该是:两个实体表:一张是员工表,一张是技能字典表,一个员

工可以掌握多个技能,也就是(1:N)关系，相反一个技能可以被多个员工掌握,也

是(l:N)关系,双向都是(I:N)关系,那么综合起来员工和技能之间就是“多对

多关系(N:M)”，依据前述规范,应该设计一张“关系表"来存储''多对多关

系”，主键为复合主键(员工主键+技能主键)，该关系有一个属性“技能掌握程

2.4范式化2NF的规范

OLTP系统的模型，需要符合第三范式。对于表在20个以上的模型，需要DBA

参与设计。

范式化要求(2NF)：满足1NF,不存在非主键属性对主键属性的部分依赖。

实体表中一般不会出现违反2NF的情况，因为都是“一个”主键列，而关系表

是两个以上列的“复合”主键，故而关系表容易出现违反2NF的情况。主要是该关

系表非主键外的属性，本该属于相关的某个实体表的，却放到了该关系表中。这使

得该属性不能通过该关系表的复合主键唯一确定，DML操作会发生错误。如果违反

了2NF,那么应该把这个属性从关系表中拆分，也许会单独形成一个表，绝大部分

情况下是将该属性归并到某个相关的实体表中。

违反2NF的例子:

学生考试情况中,有两个实体表:学生表和学科表,学生与学科之间的考试关

系就是的关系,就要创建一张关系表存储该多对多的考试关系,表的主键为学

生编号和学科编号,属性为考试分数;那么“任课老师”该放在那里呢？如果放到

考试关系表中,那么安排任课老师,必须先进行考试,这显然不符合实际,也就是

任课老师不该依赖于学生编号和学科编号,只是依赖于学科编号,也就是说任课教

师信息应该放在学科表中.

2.5范式化3NF的规范

OLTP系统的模型，需要符合第三范式。对于表在20个以上的模型，需要DBA

参与设计。

范式化要求(3NF)：满足2NF,不存在非主键属性对主键属性的传递依赖；

违反3NF的情况，绝大多数是在含有外键的表中。比如A表中的外键字段Bkey

是B的主键，那么依赖于Bkey的属性应当属于B表的属性，而不是A表，如果放

入A表，则这些对A表的主键Akey的依赖，首先是依赖于A(BKey),而后通过

A(BKey)对A(AKey)的依赖，传递依赖于A(Akey)；三种关系N:M)

都含有外键，都很可能发生违反3NF的情况。违反3NF的后果，会导致那些问题属

性插入异常，或者被误删。

违反3NF的例子:

教师和学科之间,存在着上课关系,假设一个教师上一门课而且一门课只有一

个教师上,那么该关系为1:1关系,将教师表的主键教师编号在学科表中以外键形

式存在就表达了该1:1关系,那么教师的“联系电话”属性该放哪里呢？如果看到

“教师编号”出现在了学科表中,就将联系电话放入学科表中,那么联系电话首先

是对表中的教师编号依赖,再依据教师编号对学科的依赖,达到了学科编号的依

赖,那么联系电话对学科编号的依赖就是传递依赖,违反了3NF。应该将其从学科

表中拆出来放入教师表中,不然的话,会发生操作异常,比如,假设一个教师已经

存在但是还没有为其分配科目,那么他的电话就无法存入库中。

2.5反范式化冗余字段使用规范

OLTP系统中在完成范式化工作之后，对某些表，可以适当反范式化增加冗余字

段以提高数据访问性能；在OLAP中采用的是面向问题的设计思想，应该大量使用

反范式化冗余信息。

当SQL关连查询涉及到4张表时可考虑采用冗余字段。

常用在两个地方：(1)关系表中的冗余：在关系表中增加相关实体表的相关属

性，以达到关连查询时减少表的关联数量的目的(2)层次关系中的冗余：在多层次

的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”

中。

反范式化冗余字段实例:

（1）关系表中的冗余:比如在考试关系中,原本在学科表中的学分信息,可以

冗余添加到考试关系表中,这样,每个学生得了多少学分,就可以直接从考试表得

到,而无需关联学科表来得到。

（2）多层关系中的冗余:假设为之范畴从大到小有国家表，省份表,城市表,

城区表,社区表,它们之间的层次关系是通过上一级的主键在下一级中以外键形式

存在来体现的,但是,如果需要问:某个设计属于哪个国家？这样就要关连查询所

有的5张表,性能会很差。这时可以将国家编号以外键形式放入到社区表中做冗

余,这样直接关联国家表和社区表即可得到答案。一般的,每间隔一级增加一个冗

余外键,比如将国家编号放入城市表中,将城市编号放入社区表中。

如何保证冗余字段数据的正确性（一致性）是反范式化的关键，需要对冗余字

段详细添加注释，说明冗余了什么，以及该字段的维护方法，常用维护方法如下：

■•如果在程序开发前设计的冗余字段，可以在正常的业务逻辑程序中一并

处理；

■•如果是程序完成之后增加的冗余字段，可以使用触发器维护；

■•对于OLAP中大量存在冗余字段，可能需要使用单独的处理任务进行维

护。

2.6数据库对象命名基本规范

2.6.1遵循行业规范

当有相关国家/行业强制性数据结构标准规范存在时，用于存储某业务数据的业

务表在表名命名上原则上应该遵从标准规定，其表中相关字段的中文名称（即数据

项名称）若标准规范上有规定的应遵循规定。此外，若标准规范上对数据项的类

型、长度有规定的，原则上也应当遵循或保证能直接兼容保存和访问。

2.6.2简单命名原则

命名尽可能简单，避免太长的命名，尽量使用缩写形式，但是缩写也要能够表

达命名的含义。凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle

中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超

过30个字符，以免超过数据库命名长度限制（Oracle有30的限制）。建议每个单

词分段长度不要超过6位。

2.6.3字符范围原则

数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，

数字和下划线三类字符，用于间隔名称中的各语义字段，以便阅读同时

方便某些工具对数据库对象的映射。如xxx_xxx_xxx,但不限于三段式。

2.6.4字母全部大写或小写原则

所有数据库对象命名字母全部大写或小写。Oracle对大小写不敏感，但是有些

数据库对大小写敏感，统一大小写有助于在多个数据库间移植。

2.6.5勿用保留词原则

数据库对象命名不能直接使用数据库保留关键字，但分段中可以使用。如USER

不能用于表名、列名等，但是USER_NAME可以用于列名，USER」NFO也可以用

于表名。

2.6.5同义性原则

对于同一含义尽量使用相同的单词命名，不管使用英文单词还是英文缩写，以

免引起误解。如TELEPNHOE的A表中表示固定电话号码，在B表中就不应该用于表

示移动电话号码。尽量避免同一单词表示多种含义的情况。

2.6.6富有含义原则

命名尽量采用富有意义的英文词汇，不准采用汉语拼音。

2.6.7扩展性原则

各系统或者项目在遵循本规范的基础上可以根据需要制定更明确的规范细则，

以满足项目管理需要。如对模块进行统一命名，然后用于表名的前缀。建议每个系

统在启动开发时建立数据字典，管理命名中使用的英文单词、英文单词缩写等，对

用于命名的单词进行统一管理。

3表的设计规范

3.1命名规范

3.1.1表的命名规范

命名规则：3位类别码一模块名一表名一附加码，采用大写字符。

类别码：一般表TBL、临时表TMP、中间表CVT、删除表DEL、历史表

HIS、配置表CFG,接口表INT,一般表的3位类别码可以省略，其他类型表的类

别码必填。

模块名：模块名代表子系统（或者子模块）的名称，如：保单相关表PLC；订

单相关SLS；基础数据：TYP。

表名：表名应该简洁明了，尽量使用完整的单词，如果导致拼上表名后，长度

超过30个字符，则从最后一个单词开始，依次向前采用该单词的缩写。（如果没

有约定的缩写，则采用该单词前4个字母来表示）。另外，表名中的名词单词都应

使用单数形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加码：为可选项，各系统根据实际情况自行编码，如：可以用以标记临时表

的生成及数据存放日期YYMMDD。

3.1.2字段的命名规范

命名规则：英文单词之间用下划线连结，且每个单词皆为单数.例：

user_name,采用小写字符。

■•字段用来存储sequence序列,命名以字结尾。例：bar_code_ido

■•字段用来存储号码,命名以no结尾。例：policy_noo

■•字段用来存储日期,命名以date结尾。例：create_dateo

■•字段用来存储数量，命名以num结尾。例：insured_num。

■,字段用来存储金额，命名以amt结尾。例：prem_amto

■,字段用来存储名称，命名以name结尾。例：client_nameo

■,字段用来存储描述信息，命名以desc结尾。例：bank_desco

■•字段用来存储基础表的code信息，命名以code结尾。例：region_code＜）

■,字段用来存储标志信息，命名以flag结尾。例：underwrit_flago

■,字段用来存储英文名称和英文描述，命名以en结尾。例：address.eno

3.2表的设计规范

3.2.1指定表空间规范

每个表在创建时候，必须指定所在的表空间，不要采用默认表空间，以防止表

建立在system空间上，导致性能问题。对于事务比较繁忙的数据表，必须存放在

在该表专用空间中。

3.2.2表的主键规范

表的主键设计，应该遵循如下三点原则：

■•有无原则

除临时表和外部表，以及流水表，日志表外，其他表都要建立主键。主键是每

行数据的唯一标识，保证主键不可随意更新修改，在不知道是否需要主键的时候，

请加上主键，它会为你的程序以及将来查找数据中的错误等等，提供一定的帮助。

■•构成原则

主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为

number,取值来自序列sequence；

■•创建原则

对于500万以上的表，采用先建唯一索引再添加主键约束的方式来创建主键。

对于实体表，主键就是一列，就是没有任何语义的自增的NUMBER列；对于关系

表，主键就是相关实体表主键形成的复合主键，是多列。

3.2.3表的外键规范

一个表的某列与另一表有关联关系的时候，如果加得上的话，请加上外键约

束。外键是很重要的，所以要特别强调。

■•适量建立外键

为了保证外键的一致性，数据库会增加一些开销，如果有确凿的并且是对性能

影响到无法满足用户需求的证据，可以考虑不建外键。否则，还是应该建外键。

■•不要以数据操作不方便为理由而不建外键

是的，加上外键以后，一些数据操作变得有些麻烦，但是这正是对数据一致性

的保护。正是因为这种保护很有效，所以最好不要拒绝它。

■•以缺省的方式建立外键

以缺省的方式建立外键（即用deleterestrict方式），以达到保护数据一致性的

目的；外键在保护数据一致方面非常有效。如果不建外键，数据库中容易出现垃圾

数据，并且无人知晓。当数据量很大的时候，查找这些垃圾数据也是相当困难的。

而应用程序在设计时，往往没有考虑或者也无法照顾到垃圾数据。因此垃圾数据很

可能造成应用程序工作不正常，并且表现出来的现象会很奇怪，让人摸不着头脑。

3.2.4字段类型及宽度的规范

字段的宽度要在一定时间内足够用，但也不要过宽，占用过多的存储空间，对于

长度不确定的列，采用可变长度的数据类型如varchar类型；

字段的类型及宽度在设计以及后面进行开发时，往往要与应用的设计、开发人

员商讨，以得到双方认可的类型及宽度；

3.2.5一个表所含字段总长度的规范

一个表中的所有字段，应当能存储在一个数据块中（BLOCK）,也即：表的单行

字段总长度＜db_bk）ck（减去pctfree）。对不含有大对象数据类型字段的表，字段数大

于5（）个的，请DBA团队参与设计。

查询字典表USERTABCOLUMNS中的字段DATA_LENGTH得到表中所有字段的

总长度，再依据db_block和表的pctfree参数可以判断是否一个数据行可以存储在一

个数据块(BLOCK)中。

对表添如果所有字段的总长度超出了一个数据块，那么需要将该表拆分成两个

(甚至多个)表，拆分的依据是字段的频繁使用程度，也就是频繁使用的字段在一

个表中，很少被使用的字段放在另一个表中，他们之间使用相同的主键值，用主外

键关联。这点就是“一个表所含字段访问频繁度的规范”。

3.2.6一个表所含字段访问频繁度的规范

一个表中的各字段的访问频繁度应该基本一致，如果一个表的字段数超过50

个，请DBA参与审核。

如果一个表的字段数过多超过50个，并且依据业务逻辑确定该表中一些字段频

繁被访问，另一些字段则很少被访问，则该表需耍做拆分处理，这样可以避免读取

频繁信息时多读取很少被访问的信息，可以提高10性能，减少内存耗费，这在

OLAP系统中比较常见。

将访问频繁度相差太远的字段拆分到两个表中，一个表存频繁访问的字段，另

一个表存很少被访问的字段。

3.2.7大对象字段(BLOB,CLOB)使用规范

存储图片，视频，音频，文件，500字节以上文本等占用太多空间的字段(大对

象字段)，不能和其他字段存储在一个表中。含有大对象(BLOB,CLOB)字段的

表设计和存储请DBA参与设计。一般有两种方法：

■•数据库存储

可以重新建一个表专门存储该大对象字段，该表基本为两个字段，一个为大对

象编号ID为主键，一个为大对象内容本身，并将该主键在原表中作外键关联，该大

对象表存储在单独的表空间中。

■•操作系统存储

将这些文件存储在操作系统空间中，大对象字段存储该文件的全路径名。

如果该大对象字段常被修改，那么采用方法一；如果该大对象信息为静态，加

载后基本不变，那么可以采用方法二，它有一个致命缺点就是信息存储在数据库外

部，不安全，容易丢失。

3.2.8关于字段能否为NULL值

对于字段能否为null,应该在sql建表脚本中明确指定，不应该使用缺省。由于

null值在参加任何计算时，结果均为null,所以在程序中必须用nvl()函数把可能为

null值的字段或变量转换非null的默认值。

3.2.9关于冗余列的规范

除非必要，否则尽量不加冗余列。

所谓冗余列，是指能通过其他列计算出来的列，或者是与某列表达同一含义的

列，或者是从其他表复制过来的列等等。冗余列需要应用程序来维护一致性，相关

列的值改变的时候，冗余列也需要随之修改，而这一规则未必所有人都知道，就有

可能因此发生不一致的情况。

如果是应用的特殊需要，或者是为了优化某些逻辑很复杂的查询等操作，可以

加冗余列。

3.2.10使用注释的规范

每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维

护方法，外键字段说明参照与那个表。原则上谁设计谁注释。

查询字典表user_tab_comments和user_col_comments可知道表和字段的注释

信息。

对表添加注释:

SQL>commentontable<tablename>is'xx';

对字段添加注释:

SQL>commentoncolumn<tablename>.<colname>is'xx

3.2.11一个表所含数据量的规范

一个非分区表中的数据量不要超过500万。当一个非分区表中的数据量超过500

万时，需设计成分区表；如果该表数据量超过5000万，请DBA参与设计。

在系统上线前，通过对业务分析，判断一个表的数据量；在系统上线后，可以

通过exp的日志，Top性能SQL,count(l)来发现数据量大的表。

将这些表进行分区,具体方法请参看分区表的设计规范。

I

记录数超过两亿条的表一定要考虑信息生命周期，必须考虑历史数据的剥离，

并在应用设计中完成对历史数据的相应处理功能(历史数据的剥离规则须经业务使

用部门的确认)。

3.2.12增量同步表的设计规范

字典信息表和需要使用增量同步的表必须增加如下属性。

属性名类型取值说明

StatusChar(1)Y/N：Y为激活N标识该行是否使用。用于软删除，

为作废，默认为Y软删除需将主键和唯一约束列添加

随机数后缀。

CreatetimeDate默认为sysdate创建时间

Update_timeDate默认为sysdate最后修改时间

3.3字段类型规范

3.3.1不使用会发生隐式转换：INTEGER,FLOAT

INTEGER改为NUMBER(n)

FLOAT改为NUMBER(p,s)

3.3.2不使用过时老类型：RAW,LONG,LONGRAW

1非标准:VARCHAR2(nCHAR)、CHAR(nCHAR)

VARCHAR2(nCHAR)改为VARCHAR2(n)

CHAR(nCHAR)改为CHAR(n)

3.3.3国家字符集相关

1国家字符集相关：NCHAR,NVARCHAR2,NCLOB

NCHAR改为CHAR

NVARCHAR2改为VARCHAR2

NCLOB改为CLOB

3.3.4不能使用大对象：BLOB,CLOB,NCLOB

1不能使用大对象：BLOB,CLOB,NCLOB

CLOB和NCLOB改为VARCHAR2

3.3.5不能使用高精度：TIMESTAMP

1不能使用高精度：TIMESTAMP

TIMESTAMP改为DATE

3.3.6关于CHAR字段

CHAR字段类型长度小于10(),长度大于1()()的字符型信息应该使用VARCHAR2

字段类型来存储。

4分区表的设计规范

4.1表空间及分区表的概念

4.1.1表空间

是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但

主要存放的是表，所以称作表空间。

4.1.2分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下

降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的

表，只是将表中的数据在物理上存放到多个“表空间”（物理文件上），这样查询数据

时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大提高了数据查

询的速度。

4.2表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序

带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此

外，分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用

性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些

数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。

从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体

管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活

性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用SQL

DML命令访问分区后的表时，无需任何修改。

什么时候使用分区表：

1、表的大小超过2GB,数据量超过500万；

2、表中包含历史数据，新的数据被增加都新的分区中。

4.3表分区的优缺点

表分区有以下优点：

■•改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检

索速度。

■•增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可

用；

・•维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区

即可；

■•均衡I/O：可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性

能。

缺点：

■•己经存在的表没有方法可以直接转化为分区表。不过，Oracle提供了

在线重定义表的功能。

4.4分区表设计规范

4.2.1不使用全局索引

在分区表中不建议使用全局索引，因为trunc分区时会导致全局索引失效，造

成难以维护。

4.2.2RANGE分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当

表的数据量超过500（）万，请DBA参与设计。

SQL常依据某列的范围访问表，则对表使用RNAGE分区。常见情况是SQL根

据时间范围进行查新，则使用时间字段作为分区关键字进行RANGE分区；

将对表的多种访问结合考虑来确定分区的细度：

■•大多数SQL操作的分区关键字值的范围；

■•数据维护的需要，比如以月为单位删除历史数据；

■•数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于500

万，则还需要进行细分；

■•一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来

看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很

多，所以需要灵活掌握。

/.当各个分区中的数据能均等划分时性能最好,如果相差太大,则考虑采用

其它分区,或者将大数据量的分区再进行HASH子分区;

2.各分区采用各自的表空间存储,使用usertabpartitions字典来查看-

确定每个分区的表空间位置;

3.分区表的索引采用本地索引,因为常会根据分区关键字（比如时间）进行

分区维护（比如删除1年前的数据,也就是删除/年前的分区），分区维护

时全局索引会失效,而本地索引不会失效,这能保证访问表时索引正常可

4.2.3LIST分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当

表的数据量超过5000万，请DBA参与设计。

SQL常居于某列的散列值访问表，则对表使用LIST分区，LIST分区不支持多列

分区关键字；常见情况针对某个地区或者某个业务进行数据访问，那么就使用地区

编号或者业务编号作为分区关键字。

将对表的多种访问结合考虑来确定分区的细度：

■•一般使用一个分区关键字的值来划定一个分区；

■•可以把分区关键字的值相对应数据比较少的几个分区合并作一个分区；

■•如果一个分区关键字值所对应的数据量过大，比如大于500万，则应该

对表采用RANGE分区，对该值的分区再采用HASH子分区；也就是说，

一个可以采用LIST分区的表，肯定可以转化成RANGE分区（可带子分

区），反之不然；

■•一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来

看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要

快很多，所以需要灵活掌握。

1.各分区采用各自的表空间存储,usertabpartitions字典来确定

每个分区的表空间;

2.分区表的索引采用本地索引。

4.2.4HASH分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当

表的数据量超过5000万，请DBA参与设计。

SQL访问表不按照某列的范围进行，也不按某列离散值进行，而且对该表的数据

不会依据某列的值范围或者离散值进行定期维护，那么使用HASH分区；HASH分

区是不知道应该选择何种分区时的选择；HASH分区的各分区都可能存有各种情况

的数据，故而不能用于依据分区清理数据的情况。

对确定分区细度的考虑：

■,依据分区的数据量规划和表的最大数据量来确定分区数；

■•一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来

看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要

快很多，所以需要灵活掌握。

1.各分区采用各自的表空间存储,使用usertabpartitions字典来确定

每个分区的表空间;

2.对于HASH分区表,大多数情况下依然要求采用本地索引,但是如果分区

过细，也可以采用全局索引,因为根据HASH分区表的特征（各分区无业务

区分,都有数据），该表很少会发生分区维护的工作。

4.2.5RANGE-LIST分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当

表的数据量超过5000万，请DBA参与设计。

SQL访问表时，既依据某列值的范围，又依据其他列的离散值或者范围，这种情

况下采用RANGE-LIST复合分区，常用于语表中的数据需要依据一个时间字段做周

期性删除等维护，并且正常业务SQL访问既依据时间字段，又依据其他字段的散列

值进行访问的情况。

比如：电信增值业务计费表，既有时间又有业务属性列，统计的时候，会选择

时间范围和业务属性，所以可以以时间列为分区关键字建立RANGE分区，以业务属

性列为关键字建立LIST子分区；分区划分的方法：

■•就按照大多数范围访问的范围值来划定RANGE分区的范围，依据单个

LIST子分区关键字的值来划分子分区；

■•如果LIST子分区中数据量较小而且又常被一起访问的子分区可以合并成

一个子分区；

■•如果LIST子分区中一个子分区关键字值对应的子分区数据量还是很大，

超过500,影响性能，那么可以通过细分RANGE分区来达到减少LIST子

分区数据量的目的，这点和LIST分区在该情况下的处理方法（转化成

RANGE-HASH）不同。

/.各子分区应该尽量分散到不同的表空间中存储,使用

usertabsubpartitions字典来确定每个子分区的表空间;

2.RANGE-1.1ST大多数情况采用本地索引,因为常根据RANGE分区关键字的

来进行分区维护。

4.2.6RANGE-HASH分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当

表的数据量超过5000万，请DBA参与设计。

SQL访问表时，主要依据某个列的范围进行访问，即访问特征符合RANGE分区

的要求，或者数据维护特征符合RANGE分区的要求，但是以SQL或者维护的数据

范围来划定分区，分区数据量又很大，对性能有影响，需再进行子分区，由于分区

中的数据都会被访问到，所以子分区采用HASH方法，整个表就是RANGE-HASH

分区；

划定分区的方法：先按照大多数范围访问的范围值来划定RANGE分区的范围，

再依据性能情况来确定HASH子分区的数据量。

/.各子分区应该尽量分散到不同的表空间中存储,使用

usertabsubpartitions字典来确定每个子分区的表空间；

2.RANGE-HASH大多数情况采用本地索弓I，因为常根据RANGE分区关键字的

来进行分区维护。

5索引的设计规范

5.1索引分类

Oracle中可以创建多种类型的索引，以适应各种表的特点和各种查询条件的特

点。可以按列的多少、索引列是否唯一、索引数据的组织形式对索引进行分类。

5.1.1单列索引与复合索引

一个索引可以由一个或多个列组成，用来创建索引的列被称为“索引列”。

单列索引是基于单列所创建的索引，复合索引是基于两列或者多列所创建的索

引。

5.1.2唯一索引与非唯一索引

唯一索引是索引列值不能重复的索引，非唯一索引是索引列可以重复的索引。

无论是唯一索引还是非唯一索引，索引列都允许取NULL值。默认情况下，

Oracle创建的索引是不唯一索引。

5.1.3B树索引、位图索引与函数索引

B树索引是按B树算法组织并存放索引数据的，所以B树索引主要依赖其组织

并存放索引数据的算法来实现快速检索功能。

Oracle中不仅能够直接对表中的列创建索引，还可以对包含列的函数或表达式

创建索引，这种索引称为“函数索引”。

位图索引在多列查询时，可以对两个列上的位图进行AND和OR操作，达到更好

的查询效果。

5.2命名规范

命名规则：类别码一表名一附加码，采用大写字符。

类别码：一般索引IDX、位图索引BIDX、唯一索引UK、主键PK、外键

FK,类别码根据索引的性质填写。

表名：表名应该简洁明了，尽量使用完整的单词，如果导致拼上表名后，长度

超过30个字符，则从最后一个单词开始，依次向前采用该单词的缩写。（如果没

有约定的缩写，则采用该单词前4个字母来表示）。另外，表名中的名词单词都应

使用单数形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加码：可以是序号，也可以是字段名，根据实际的使用情况进行填写。

5.3索引设计规范

索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采

用索引技术得到解决。但大量的DML操作会增加系统对索引的维护成本，对性能会

有一定影响，对于插入相当频繁的表要慎重建索引，索引也会占相当的存储空间，

所以要根据硬件环境和应用需求在空间和时间上达到最好的平衡点。主要原则：

■•适当利用索引提高查询速度：当数据量比较大，了解应用程序的会有哪

些查询，依据这些查询需求建相应的索引；最好亲自试验一下，模拟一下

生产环境的数据量，在此数据量下，比较一下建索引前后的查询速度；索

引对性能会有一定影响，对于DML频繁列的索引要定期维护（重建）。但

是，索引的结构对于索引的更新（比如在插入数据的时候）是有一定优化

的，所以不要在没有试验以前过分夸大它对性能的影响，最终还是以试验

为准。

■•不要建实际用不上的索引，与上条相关，如果建的索引并不提高任何一

应用中的查询速度，则要把它删除；有些数据库有相关工具可以发现实际

未被使用的索引，可以利用一下。

・•索引列的选择：如果检索条件有可能包含多列，创建联合主键或者联合

索引，把最常用于检索条件的列放在最前端，其他的列排在后面；不要索

引使用频繁的小型表，假如这些小表有频繁的DML就更不要建立索引，维

护索引的代价远远高于扫描表的代价；

■•主键索引在建立的时候一定要明确的指定名称，不能让系统默认建立主

键索引（可能有些数据库无法指定主键名，则例外）；

■•当有联合主键或者联合索引时，注意不要建重复的索引。

举例说明:

表EMPLOYEES,它的主键是建立在列DEPARTID和EMPLOYEE1D上的联合

主键,并且创建主键的语句中DEPART1D在前,EMPLOYEES在后。在这样一个表

里,通常就没有必要再为DEPART1D建一个索引了,联合索引的情况也一样。

更复杂的情况,比如表EMPLOYEES,有一个索引建立在列CORPID,

DEPARTID,EMPLOYEES三列上,在创建语句中也依据上述顺序,就没有必要再

为C0RP1D建立索引;也没有必要再建立以C0RP1D在前,DEPARTID在后的联合

索引:如果EMPL0YEE1D需要索引,那么为EMPLOYEEID建立一个索引是不与上

而的索引重复的;DEPARTID歹U也类f以

■•控制一个表的索引数量，尽量使得一个表的索引数量小于五个。

5.3.1指定表空间规范

每个索引在创建时，必须指定表空间，不要采用默认表空间，以防止索引建立

在system空间和非索引专用空间，以减少IO冲突，提高性能。

5.3.2主键索引的规范

对数据量表应该先在主键列建唯一索引，再建主键约束。分区表的主键必须采

用该方法设计。原则上所有的数据表都要有主键。

主键上隐含索引，drop或disable主键时，索引会丢失，为保证性能不变，为了

对主键约束和相应索引有更多的控制，对大表(分区表)的索引采用如下方式建立：

1.在准备建主键的列上建立唯一索引(UNIQUEINDEX):CREATEUNIQUE

INDEXIndex_NameONTable_Name(Column_Name)TABLESPACETBS_INDEX;

2.再加上主键约束：

ALTERTABLETable_NameADD(PRIMARYKEY(Column_Name)USING

INDEXTABLESPACETBSJNDEX);

Oracle会在指定的列上加上主键约束，并且使用该索引。

分区表的主键默认索引是全局索引,所以主键索引的分区方法:先建立分区化

的唯一索引,再建主键约束。

5.3.3唯一约束索引的规范

针对大数据量表应该先在唯一约束列上建立普通索引，再添加唯一性约束。分

区表的唯一约束必须采用该方法。

删除或禁用唯一性约束通常同时使相关联的唯一索引失效，因而降低了数据库

性能。要避免这样问题，可以采取下面的步骤：(a)在唯一性约束的列上创建非唯

一性索引(普通索引)；(b)添加唯一性约束。

5.3.4外键列索引的规范

对于关联两个表字段，一般应该分别建立主键、外键。实际是否建立外键，根

据对数据完整性的要求决定。为了提高性能，无论表的大小，外键都要建立索引，

一是为了子父表关联查询的性能考虑，二是为了避免父子表修改而发生死锁。对于

有要求级联删除属性的外键，必须指定ondeletecascade,

普通表的外键列建立普通索引即可，如果表是分区表，则依据表的情况建立本

地索引或者全局索引。

5.3.5复合索引的规范

复合索引只有在该种复合常被和该表相关的大多数SQL使用时才建立。复合索

引的列数不能超过5个，否则该索引很少会被使用。

■•复合索引的第一列，可以通过不使用该种复合的SQL来确定。假设一些

SQL的WHERE中复合使用列为ABC,而其他一些SQL的WHERE中常使

用的是C列，那么该复合索引可以按照CAB的顺序建立，这样上述两种

SQL都能使用该索引；

■•对于不能把握好的复合索引，请在选择性大的列上分别建立单列索引：

■•切忌不能将表相关的所有SQL中WHERE涉及到的列复合起来建立复合

索引。

5.3.6函数索引的规范

由于使用形式需和创建形式一致，尽量避免使用函数索引。如果想要使用函数

索引，请尽量进行转化。

由于函数索引在使用时，使用形式必须和创建形式一致，故应该尽量避免使用

函数索引，尽量采用如下方法转化SQL以避免函数索引的使用：

原本在WHERE中列上添加函数的，取函数的反意义函数添加到“=”另一侧的常

数项上，这样只需要在列上建立普通索引即可，比如常见的日期转化函数：

TO_CHAR（CREATE_TIME）=（2010-07-07'采用TO_DATE（）转化为

CREATE_TIME=TO_DATE（12010-07-07'yyyy-mm-dd'）。

5.3.7位图索引的规范

静态表中的低基数列可以使用位图索引。在事务型数据库（OLTP）中禁止使用

位图（bitmap）索引，在报表型数据库（OLAP）中的静态表，可以适当使用。

5.3.8反向索引的规范

列值顺序增加的列，其上的WHERE运算是＜＞或者=而不是范围（betweenand或

者＜and＞）检索时，可以采用反向函数。一般创建反向索引的列为NUMBER类

型，值由SEQUENCE生成。

5.3.9分区索引的规范

对分区表的索引，需要做分区维护的，必须使用局部索引。一般情况下，HASH

分区表可以采用全局索引，其他分区，包括RANGE-HASH也应该采用本地索引，

主要是由于HASH分区表不常进行分区维护。

5.3.10索引重建的规范

重建索引使用ALTERINDEXREBUILD方式，禁止采用DROPINDEX&

CREATEINDEX方式。分区表等大数据量表的索引必须采用ALTERINDEX

REBUILD方式重建。

方法：

ALTERINDEXIDX_NAMEREBUILD[TABLESPACETBSP_NAME]。

6其他数据库对象设计规范

6.1命名规范

・•视图：v归相关表名，或者根据需要另取名字；

■•存储过程：SP存储过程名，用英文表达存储过程意义；

■•函数：FUN一函数名称，用英文表达