数据库设计规范(共17页)

1、密级：数据库设计规范（oracle版本(bnbn)）版 本 号V1.0发布日期2015-06-19修 改 人尤盟修改日期2015-06-19审 核 人审核日期审 批 人审批日期修订(xidng)记录版本号发布日期修改人/修改日期审核人/审核日期审批人/审批日期备注目录(ml) TOC o 1-3 h z u HYPERLINK l _Toc286908835 1、目的(md) PAGEREF _Toc286908835 h 4 HYPERLINK l _Toc286908836 2、概述(i sh) PAGEREF _Toc286908836 h 4 HYPERLINK l _Toc28690

2、8837 3、数据库物理(wl)设计原则 PAGEREF _Toc286908837 h 4 HYPERLINK l _Toc286908838 3.1、数据库环境配置原则 PAGEREF _Toc286908838 h 4 HYPERLINK l _Toc286908839 3.2、数据库设计原则 PAGEREF _Toc286908839 h 5 HYPERLINK l _Toc286908840 3.3、数据库表空间设计原则 PAGEREF _Toc286908840 h 9 HYPERLINK l _Toc286908841 4、数据库逻辑设计原则 PAGEREF _Toc286908

3、841 h 9 HYPERLINK l _Toc286908842 4.1、命名规范 PAGEREF _Toc286908842 h 9 HYPERLINK l _Toc286908843 4.2、命名 PAGEREF _Toc286908843 h 11 HYPERLINK l _Toc286908844 4.3、数据类型 PAGEREF _Toc286908844 h 12 HYPERLINK l _Toc286908845 4.4、设计 PAGEREF _Toc286908845 h 13 HYPERLINK l _Toc286908846 4.5、SQL编写 PAGEREF _Toc2

4、86908846 h 15 HYPERLINK l _Toc286908847 5、设计工具 PAGEREF _Toc286908847 h 17 HYPERLINK l _Toc286908848 附录 PAGEREF _Toc286908848 h 17 HYPERLINK l _Toc286908849 SGA PAGEREF _Toc286908849 h 17 HYPERLINK l _Toc286908850 3NF PAGEREF _Toc286908850 h 191、目的(md)定义(dngy)数据库设计(shj)设计规范，作为数据库设计、规划、开发以及维护人员的技术参考资料

5、。2、概述本文主要根据oracle9i以上数据库性能特点，描述数据库环境配置、数据库物理设计、数据库逻辑设计、视图、存储过程、应用程序设计等方面的设计规范。3、数据库物理设计原则3.1、数据库环境配置原则操作系统环境：对于中小型数据库系统，采用linux操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统，可以采用Oracle9i RAC的集群数据库的方法，集群节点数范围在264个。对于大型数据库系统，可以采用Sun Solaris SPARC 64位小型机系统或HP 9000 系列小型机系统。内存要求对于linux操作系统下的数据库,由于在正常情况下Oracle对SGA的管理能力不

6、超过1.7G。所以总的物理内存在4G以下。SGA的大小为物理内存的50%75%。对于64位的小型系统，Oracle数据库对SGA的管理超过2G的限制，SGA设计在一个合适的范围内：物理内存的50%70%，当SGA过大的时候会导致内存分页，影响系统性能。交换区设计当物理内存在2G以下的情况下，交换分区swap为物理内存的3倍，当物理内存2G的情况下，swap大小为物理内存的12倍。其他环境变量参考Oracle相关的安装文档和随机文档。3.2、数据库设计原则数据库SID数据库SID是唯一(wi y)标志数据库的符号，命名长度不能超过30个字符。对于单节点数据库，以字符(z f)开头的30个长度以内

7、(y ni)字串作为SID的命名。对于集群数据库，当命名SID后，各节点SID自动命名为SIDnn，其中nn为节点号：1，2，,64。例如rac1、rac2、rac24。数据库全局名数据库全局名称：.domain数据库类型选择对于海量数据库系统，采用data warehouse的类型。对于小型数据库或OLTP类型的数据库，采用Transaction Processing类型。数据库连接类型选择Oracle数据库有专用服务器连接类型和多线程服务器MTS连接类型。对于批处理服务，需要专用服务器连接方式，而对于OLTP服务则MTS的连接方式比较合适。由于采用MTS后，可以通过配置网络服务实现某些特定

8、批处理服务采用专用服务器连接方式，所以数据库设计时一般采用MTS类型。数据库SGA配置(pizh)数据库SGA可以(ky)采用手工配置或按物理内存比例配置，在数据库初始(ch sh)设计阶段采用按比例配置方式，在实际应用中按系统调优方式修改SGA。数据库字符集选择为了使数据库能够正确支持多国语言，必须配置合适的数据库字符集，采用UTF8字符集。数据(shj)库其他参数(cnsh)配置DB_FILES Db_files是数据库能够同时打开的文件(wnjin)数量，默认值是200个。当数据库规划时文件数量FILES接近或超过200个时候，按以下估计值配置:DB_FILES = FILES * 1.

9、5 Db_block_size Db_block_size是数据库最小物理单元，一旦数据库创建完成，该参数无法修改，db_block_size按以下规则调整：数据仓库类型： db_block_size尽可能大，采用8192 或 16384OLTP类型： db_block_size 用比较小的取值范围： 2048 或 4096数据库控制文件配置控制文件镜象多个控制文件存放在不同的物理位置。控制文件配置控制文件中参数设置，最大的数据文件数量不能小于数据库参数db_files。数据库日志(rzh)文件配置日志(rzh)文件大小日志(rzh)文件的大小由数据库事务处理量决定，在设计过程中，确保每20分

10、钟切换一个日志文件。所以对于批处理系统，日志文件大小为几百M 到几G的大小。对于OLTP系统，日志文件大小为几百M以内。日志文件组数量对于批处理系统：日志文件组为510组。对于OLTP系统：日志文件组为 35组。每组日志大小保持一致。对于集群数据库系统，每节点有各自独立的日志组。日志成员数量为了确保日志能够镜象作用，每日志组的成员为2个。数据库回滚段配置在Oracle9i数据库中，设计Undo表空间取代以前版本的回滚段表空间。Undo 表空间大小的设计规范由以下公司计算：Undospace = UR * UPS *db_block_size+ 冗余量UR： 表示在undo中保持的最长时间数（秒

11、），由数据库参数UNDO_RETENTION值决定。UPS：表示在undo中，每秒产生的数据库块数量。例如：在数据库中保留2小时的回退数据，假定每小时产生200个数据库块。则Undospace = 2 * 3600 * 200 * 4K = 5.8G数据库临时段表空间配置数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。数据库系统表空间(kngjin)配置系统表空间(kngjin)大小1G左右，除了(ch le)存放数据库数据字典的数据外，其他数据不得存储在系统表空间。3.3、数据库表空间设计原则表空间大小定义原则当表空间大小小于操作系统对最大文件限制时，表空间由一个文件

12、组成。如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：Tablespace + sum (数据段+索引段)*150%表空间扩展性设计原则表空间采用自动扩展的方式、表空间采用local管理方式。4、数据库逻辑设计原则4.1、命名规范表T_ 。数据表必须以有特征含义的单词或缩写组成，中间可以用“_”分割，例如：t_pstn_detail。表分区p 。分区名必须有特定含义的单词或字串。例如 :t_pstn_detail 的分区p2004100101表示该分区存储 2004100101时段的数据。字段必须以有特征含义的单词或缩写组成，中间可以用“_”分割

13、。例如：USER_NAME。主键PK_。主键名称应是 前缀+表名+构成的字段名。如果复合主键的构成字段较多，则只包含第一个字段。表名可以去掉前缀。索引IDX_。索引名称应是 前缀+表名+构成的字段名。如果复合索引的构成字段较多，则只包含第一个字段，并添加(tin ji)序号。表名可以去掉前缀。外键FK_。外键名称应是 前缀(qinzhu)+ 外键表名 + 主键表名 + 外键表构成的字段名。表名可以去掉前缀。索引(suyn)分区无。由系统自动产生视图V_。按业务操作命名视图。实体化视图MV_。按业务操作命名实体化视图。存储过程Proc_ 。按业务操作命名存储过程触发器Trg_ 。触发器名应是 前

14、缀 + 表名 + 触发器名。函数Func_ 。按业务操作命名函数数据包Pkg_ 。按业务操作集合命名数据包。序列Seq_ 。按业务属性命名。表空间公用表空间Tbs_ 。 根据存储的特性命名，例如： tbs_parameter 。专用表空间Tbs_表名称_nn。该表空间存储特定表，或表分区的数据数据文件表空间nn.dbf 。nn =1，2，3，4，普通变量Var_ 。 游标(yubio)变量Cur_ 。存放(cnfng)游标记录集。记录(jl)型变量Rec_ 。 存放记录型数据。表类型变量Tab_ 。 存放表类型数据。4.2、命名语言命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命

15、名不允许使用中文或者特殊字符。英文单词使用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。大小写名称一律大写，以方便不同数据库移植，以及避免程序调用问题。单词分隔命名的各单词之间可以使用下划线进行分隔。保留字命名不允许使用SQL保留字。命名长度表名、字段名、视图名长度应限制在30个字符内(含前缀)。字段名称同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号

16、码”的意思，在另外一个表中就不能代表“手机号码”的意思。不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。4.3、数据类型字符(z f)型固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用(shyng)trim()函数截去字串后的空格。数字型数字型字段尽量(jnling)采用number类型。日期和时间系统时间由数据库产生的系统时间首选数据库的日期型，如DATE类型。外部时间由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：YYYYMMDDHH24MI

17、SS。大字段如无特别需要，避免使用大字段(blob，clob，long，text，image等)。唯一键对于数字型唯一键值，尽可能用系列sequence产生。4.4、设计范式如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3NF（要求一个数据库表中不包含已在其它表中已包含的非主关键字信息）。如非确实必要，避免一个字段中存储多个标志的做法。如11101表示5个标志的一种取值。这往往是增加复杂度，降低性能的地方。表设计每个表必须包含以下属性参数: TABLESPACE，PCTUS

18、ED，STORAGE。根据表的不同属性进行表类型设计：分区表对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能：如果表按某些字段进行增长，则采用按字段值范围进行范围分区。如果表按某个字段的几个关键值进行分布，则采用列表分区。对于静态表，则采用hash分区或列表分区。在范围分区中，如果数据(shj)按某关键字段均衡分布，则采用子分区的复合分区方法。聚蔟表如果某几个静态表关系比较密切，则可以(ky)采用聚蔟表的方法。索引(suyn)对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引

19、组合的最前面。在分区表中，尽量采用local分区索引以方便分区维护。索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引。避免对大表查询时进行table scan，必要时考虑新建索引。在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 要注意索引的维护，周期性重建索引，重新编译存储过程。主键关联表的父表要求有主健。外键对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键

20、，根据对数据完整性的要求决定。为了提高性能，要求对外健建立索引。tempdb的使用规范尽量避免使用distinct、order by、group by、having、join，因为这些语句会加重tempdb的负担。 避免频繁创建和删除临时表，减少系统表资源的消耗。 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统

21、能够很好的使用到该临时表的索引。 如果使用到了临时表，最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。NULL值对于(duy)字段能否null，应该在sql建表脚本中明确指明，不应使用缺省。由于NULL值在参加任何运算中，结果均为NULL。所以在应用程序中必须利用nvl()函数把可能为NULL值得(zh d)字段或变量转换为非NULL的默认值。例如：NVL（sale,0）。注释(zhsh)表、字段等应该有

22、中文名称注释，以及需要说明的内容。4.5、SQL编写字符类型数据SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用trim(),lower()等函数格式化匹配条件。复杂SQL对于非常复杂的SQL(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。高效性避免In子句使用In 或 not In子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。避免嵌套的Select子句这个实际上是In子句的特例。避免

23、使用Select * 语句如果不是必要取出所有数据，不要用*来代替，应给出字段列表。避免不必要的排序不必要的数据排序大大的降低系统性能。健壮性Insert语句使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。安全性Where 条件无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失.如果不确定，最好先用Select语句带上相同条件来搜一下结果集，来检验条件是否正确.其他(qt)尽量避免大事务操作，慎用holdlock子句(z j)，提高系统并发能力

24、。 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时(ln sh)表中，然后再做连接。 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 尽量使用exists代替select count(1)来判断是否存在记录，co

25、unt函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 尽量使用“=”，不要使用“”。注意一些or子句和union子句之间的替换 注意表之间连接的数据类型，避免不同类型数据之间的连接。 注意存储过程中参数和数据类型的关系。 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。5、设计工具统一使用sybase power designer设计工具，在该工具上完成物理模型的设计，并且由该工具产生数据库脚本程序。附录SGA系统全局区又称SGA （System Glo

26、bal Area）是Oracle Instance的 基本组成部分，在实例启动时分配。是一组包含一个Oracle实例的数据和控制信息的共享内存结构。主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。SGA主要由数据高速缓冲区（Database Buffer Cache）、共享池（Shared Pool）、重做日志缓冲区（Redo Log Cache）、大型池（Large Pool）、Java池（Java Pool）、流池（Str

27、eams Pool）和其他结构（如固定SGA、锁管理等）组成.SGA几个很重要的特性1、SGA的构成(guchng)数据和控制信息，我们(w men)下面会详细介绍； 2、SGA是共享的，即当有多个用户(yngh)同时登录了这个实例，SGA中的信息可以被它们同时访问（当涉及到互斥的问题时，由latch和enquence控制）； 3、一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区。SGA主要包括：1.数据库高速缓存(the database buffer cache)， 2.重

28、演日志缓存（the redo log buffer） 3.共享池（the shared pool） 4.数据字典缓存（the data dictionary cache）以及其它各方面的信息。 1.数据高速缓冲区（Data Buffer Cache） 在数据高速缓冲区中存放着Oracle系统最近使用过的数据块（即用户的高速缓冲区），当把数据写入数据库时，它以数据块为单位进行读写，当数据高速缓冲区填满时，则系统自动去掉一些不常被用户访问的数据。如果用户要查的数据不在数据高速缓冲区时，Oracle自动从磁盘中去读取。数据高速缓冲区包括三个类型的区： 1） 脏的区（Dirty Buffers）：包含

29、有已经改变过并需要写回数据文件的数据块。 2） 自由区（Free Buffers）：没有包含任何数据并可以再写入的区，Oracle可以从数据文件读数据块该区。 3） 保留区（Pinned Buffers）：此区包含有正在处理的或者明确保留用作将来用的区。 2.Redo Log Buffer Cache缓存对于数据块的所有修改。主要用于恢复其中的每一项修改记录都被称为redo 条目。利用Redo条目的信息可以重做修改。 3. Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义。它主要由两个内存结构构成：Library cache和Data dictionary cache

30、 修改共享池的大小：ALTER SYSTEM SET SHARED_POOL_SIZE = 64M; Library Cache缓存最近被执行的SQL和PL/SQL的相关信息。实现常用语句的共享，使用LRU算法进行管理，由以下两个结构构成：Shared SQL area、Shared PL/SQL area、Data Dictionary Cache、Data dictionary cache缓存最近被使用的数据库定义。它包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。在语法分析阶段，Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。数据字典信息缓存在内存中有助于缩短响应时间。3NForacle范式(fn sh)构造数据库必须遵循一定的规则，在关系(gun x)数据库中这种规则就是范式（NF即Normal Form）。范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求，即满足不同的范式。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。满足最低要求的范式是第一范式1NF，在第一范式