Oracle数据库设计规范

最新推荐文章于 2021-04-04 09:18:58 发布

litter_m

最新推荐文章于 2021-04-04 09:18:58 发布

阅读量2.3k

点赞数 7

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/litter_m/article/details/111049828

版权

Oracle数据库设计规范

参考网上一些其他的数据库设计规范,结合Oracle官网设计文档做出的设计规范.

一、命名规范

【强制】不允许使用数据库关键字和保留字，为了避免不必要的冲突和麻烦。Oracle的关键字、保留字，详情见《关于Oracle数据库中的关键字和保留字》文件的说明。
【强制】严禁使用带空格的名称来给字段和表命名，会出错误而终止：

反例：TAB_START CROSS_TIME
【强制】用户自定义数据库对象：表、视图、主外键、索引、触发器、函数、存储过程、序列，总体风格要保持一致。使用特定开头进行区分：

数据库对象	特定开头
表	TAB_
视图	V_
序列	SEQ_
触发器	TRG_
存储过程	SP_
函数	F_
主键	PK_
外键	FK_
唯一索引	UK_
普通索引	IDX_

【强制】不允许使用中文和特殊字符，名称只能含有大写字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段，禁止两个下划线中间只出现数字，禁止使用拼音首字母的形式。

正例：TAB_OBJ , HOST_TYPE , CLASS2_NAME

反例：aliyun—1, rdcCofig , level_1=
【强制】数据库对象名称长度均不能超过30个字符。

【强制】同一个字段名在一个数据库中只能代表一个意思。

比如phone在一个表中代表“座机号码”的意思，在另外一个表中就不能代表其他意思（比如手机名称、品牌等，否则在A表中phone存的是座机号码，在B表中存的是手机品牌，那就混乱了）

【强制】代表同一个意思的字段，在各个表中都用相同单词表示。

例如电话号码字段，在A表中叫telephone，在B表中叫phone，在C表中叫mobile，这样就很混乱。

二、建表规范

【强制】表名、字段名不要使用复数名词，务必使用相关性最强的英语词汇，杜绝完全不规范的缩写，专有名词保持英文原文即可。

【强制】遵守数据的设计规范 3NF 规定：

Ⅰ.表内的每一个记录都只能被表达一次。

Ⅱ.表内的每一个记录都应该被唯一的标识（有唯一键）。

Ⅲ.表内不应该存储依赖于其他键的非键信息。

【强制】数据表名、字段名长度需要保持在30个字节以内，违反则会出现：
```
ORA-00972: identifier is too long
```

【强制】表必备三字段： id, create_time, update_time

id：VARCHAR2/NUMBER 主键
create_time：DATE NOT NULL
update_time：DATE NOT NULL

-- generated as identity 主键递增，19c 不需要序列加触发器了进行递增了
-- [constraint TAB_TIME_PK] 可以给约束信息起个名字，主键会自动生成索引，也会以这个名字创建
CREATE TABLE TAB_USER(
	ID NUMBER GENERATED AS IDENTITY,
    USERNAME VARCHAR2(50), 
	CREATE_TIME DATE NOT NULL,
	UPDATE_TIME TIMESTAMP NOT NULL
    [CONSTRAINT TAB_TIME_PK] PRIMARY KEY(ID)
)

【强制】定长字符型列使用CHAR类型，不定长字符型列使用VARCHAR2 类型。

CREATE TABLE TAB_USER(
	ID INT GENERATED AS IDENTITY,
    USER_NAME VARCHAR2(50), --VARCHAR
    USER_CODE CHAR(18),  	--CHAR
	CREATE_TIME DATE NOT NULL,
	UPDATE_TIME TIMESTAMP NOT NULL
    [CONSTRAINT TAB_TIME_PK] PRIMARY KEY(ID)
)

【强制】对于数字和浮点型数据，不得使用VACHAR等字符串类型来保存，应该使用相应精度的数字、小数类型。
【强制】字段类型禁止使用 enum，对于boolean类型或者表示简单状态的字段，使用NUMBER(1)

Ⅰ.建议状态类型字段 not null，根据业务要求来设置默认值(例如默认为0)。

Ⅱ.对于 boolean 类型，以1代表是(true)， 0 代表否(false)。

Ⅲ.对于状态类型，注释中应该注明每一种状态的含义，例如“0：编辑中，1：审核中，2：已完成”。

【强制】日期字段只能选择DATE和TIMESTAMP类型。如果定义为varchar2或者char时需要进行转换，影响效率。需要数据精确到微秒的字段需定义为 TIMESTAMP类型。
【强制】禁止使用LOB类型保存大文本、图片和文件，建议使用其他方式存储（例如文件系统，数据库只保存其地址信息）。
【强制】不要通过 Navicat 创建表，尽量使用 SQL 语句，也可以使用 IDEA，因为 Navicat 大小写敏感，当表名或者字段名使用小写单词时，会变成 “word”，当查询时会导致：
```
ORA-00942: table or view does not exist
ORA-00904: "**": invalid identifier
```
【强制】表和字段必须写注释表明其含义，将表注释、字段注释写到 Excel 表中。
【强制】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【强制】列字段对象名称请勿使用以SYS_或ORA_开头。
【强制】对于一一对应的关系，在相关表中应添加相对应的字段。多对多关系应该建立对应的关系表。
【强制】对于读写数据量较大的表格中禁止使用外键，使用后会严重影响性能。
【强制】严禁数据量大的表使用UUID/MD5作为主键，建议主键为数字类型，且为递增顺序，主键不表示任何业务含义。
【推荐】对于所有的列而言，为null时，最好定义default值，避免因为null而造成索引不能被用到的情况。

【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

Ⅰ.不是频繁修改的字段

Ⅱ.不是唯一索引的字段

Ⅲ.不是 varchar 超长字段，更不能是 text 字段

【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

三、索引规范

【强制】小表（数据量小于5000条记录为标准）不需要创建索引。
【强制】将记录差别数最大的列放在索引顺序的最前面。
【强制】单个表上索引的个数不要超过5个。

【强制】在 varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：
    索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/ count(*)的区分度来确定。

【强制】避免建立冗余的索引和重复索引

重复索引 例如： primary key(ID) 、index(ID) 、 unique index(ID)

冗余索引 例如： index(a,b,c) 、 index(a,b) 、index(a)

【推荐】不建议在低基数列上创建索引，例如“性别”列或“状态”列；

【推荐】索引列的顺序，数据库是从左到右的顺序来使用的，联合索引又叫复合索引。当需要创建复合索引时，需要慎重考虑列在索引中的顺序。

Ⅰ.建议区分度最高的列放在联合索引的最左侧

Ⅱ.建议使用最频繁的列放在联合索引的左侧。

Ⅲ.尽量把字段长度最小的列放在联合索引的最左侧

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

Ⅴ.不管是单列索引还是复合索引，当列允许为空，且实际有空值时，在查询中可能会不走索引扫描，因为 null 值不在索引条目中。因此尽可能选择 not null的列做为索引列，如果不能避免时，可以采用列为空时去一个默认值的方法解决。

【推荐】不要沉迷于联合索引所带来的可多选字段便利性，可以使用单个字段建立索引完成的功能，各方面性能都会优于联合索引。
【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：WHERE a=? AND b=? ORDER BY c; 索引：a_b_c

反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b 无法排序。
【推荐】设计索引的时候要注意字段类型，防止因字段类型不同造成的隐式转换，导致索引失效。
【推荐】对索引的使用进行监控，可以发现那些索引有用，那些索引没有用到。没有用到的索引可以直接删除。

【参考】创建索引时避免有如下极端误解：

Ⅰ.索引宁滥勿缺。认为一个查询就需要建一个索引。

Ⅱ.吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。

Ⅲ.抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

四、SQL开发规范

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明，即使字段非常多，也要比使用*要消耗更少的CPU和IO以及网络宽带资源。

反例：SELECT * FROM TAB_USER_ROLE
【强制】避免频繁commit，尤其是把commit 写在循环体中，导致每次循环都进行 commit。避免在一个事务中出现2次commit的现象。
【强制】进行统计数量的时候，不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非 NULL无关。
```
 说明： COUNT(*)会统计值为 NULL 的行，而 COUNT(列名)不会统计此列为 NULL 值的行。 
```
【强制】count(distinct col) 计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
【强制】代码中写分页查询逻辑时，若 count为0应直接返回，避免执行后面的分页语句。
【强制】数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。

【强制】exist 和 in 的选择遵循以下原则：

Ⅰ.当查询的数据较少或者字段值比较时用 IN 比较好，比如： SELECT * FROM TAB_TREE T WHERE T.ID IN (…); 

Ⅱ.当查询的值比较多或者是子查询时用 EXIST 比较好，比如： SELECT * FROM TAB_TREE WHERE EXIST(SELECT …) ;

Ⅲ.EXIST中的子查询用常量是速度比 SELECT * 快,比如： SELECT * FROM TAB_TREE WHERE EXIST (SELECT 1 FROM …);

【强制】在可以使用关联查询的时候，不要用select嵌套写法，如：SELECT…FROM tb1,(SELECT…tb2)…。尽可能把select的嵌套转化为关联查询的方式，如：SELECT … FROM tb1 JOIN tb2 ON …；
【强制】请勿在算术表达式中使用两个连续的减号( - )表示双重否定或减去负值。字符 - 用于在SQL语句中开始注释。若必须使用两个连续的减号,则应使用空格或括号分隔连续的减号。
【强制】查询中使用分层查询运算符时，要注意CONNECT_BY_ROOT不能直接在START WITH condition 或 CONNECT BY condition中使用。
【强制】带引号的标识符以双引号（ " ）开头和结尾。如果使用带引号的标识符命名创建对象，则在引用该对象时必须使用双引号。

【强制】进行查询融合时，在明显不会有重复值时使用union all 而不是union。

UNION ：会把所有数据放到临时表表中后再进行去重操作

UNION ALL ：不会再对结果集进行去重操作

【推荐】 like 子句尽量前端匹配，如写成 like string%，不要写成 %string%。
【推荐】尽量少用not exist/not in等否则写法。如果一定要用时，尽量选择 not exist，not in可能用不到index,not exist效率更高，速度更快。
【推荐】在SQL开发的过程中，SQL的字母大小写采用一致规范，避免因为大小写的问题造成SQL的多次解析浪费资源。

如以下三个名称在Oracle中编译结果相同：
```
employees
EMPLOYEES
"EMPLOYEES"     --此处双引号中对象，Oracle在编译时会区分大小写，但不加双引号一律按照大写处理。
```

【推荐】在SQL语句的书写中尽量避免数据类型的隐式转换，隐式转换会导致索引失效。

SELECT * FROM TABLE1 WHERE ID = '146313'  --此时表TABLE1中ID的类型为数字时，索引直接失效。

【参考】如果想要更深每一步去了解关于SQL开发的相关语法知识，请参考当前19c版本所对应的Oracle官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html