数据库开发规范.docx

数据库开发规范.docx

《数据库开发规范.docx》由会员分享，可在线阅读，更多相关《数据库开发规范.docx（16页珍藏版）》请在冰豆网上搜索。

数据库开发规范

项目一部数据库开发规范

版本号

日期

修改者

说明

1.0

2012-03-06

注：

对该文件内容增加、删除或修改均需填写此变更记录，详细记载变更信息，以保证其可追溯性。

1.命名规范

1.1.对象

新建的表，存储过程，包等要遵循以下规则

对象名称

前缀

备注

表

见下表，根据功能分

视图

v_

主键

pk_

索引

idx_

序列

seq_

存储过程

pr_

包

pk_

函数

f_

同义词

s_

数据库连接（dblink）

dl_

1.2.表

表名不得超过30个字母，全部采用大写字母，表的命名可以如下：

模块缩写名_表的名称，如RES_NODE代表资源模块的节点;值班：

DUTY_CALENDAR。

1.3.字段名称

字段名不得超过30个字母，必须以英文单词构成，每个单词之间以下划线隔开，全部采用大写字母。

对复杂的大型应用系统而言，必须建立表名和字段名的数据字典，并附于开发规范附录中，在命名时必须严格遵守数据字典。

2.数据库对象管理

2.1.一般规定

数据库所有对象，包括表、视图、主键、索引、序列、存储过程、包等必须在数据库建模工具中进行管理并保持与数据库完全同步。

2.2.大小写

在数据库模型、数据库脚本中，所有对象，包括表、视图、主键、索引、序列、存储过程、包等名称必须大写。

3.语句书写规范

3.1.尽量不使用某种数据库的特有功能

为了保持可移植性，尽量不使用某种数据库的特有功能，如SQLServer专用的UniqueID,Oracle专用的Sequence的功能；

3.2.查询sql语句尽量使用绑定变量

3.3.尽最大可能不使用通配符

在SQL语句中，LIKE关键字支持通配符匹配，但这种匹配特别耗费时间。

如：

SELECTAFROMABCWHEREALIKE'M%'。

在A字段上建立了索引。

把语句改为SELECTAFROMABCWHEREA>'M'ANDA<'N'，在执行查询时会利用索引以提高响应速度。

使用*通配符必须事先征得项目开发负责人同意。

1

2

3

3.1

3.2

3.3

Distinct

使用distinct会增加查询和I/O的操作次数。

应当避免使用distinct关键字。

嵌套查询

SELECTAFROMCMS_USERWHEREUSER_NAMEIN（SELECTUSER_NAMEFROMCMS_DEPARTMENTWHEREDEPARTMENT=’电子办’）

如果我们用连接来代替，且表关联放在条件语句的最后部。

即：

SELECTAFROMCMS_USER，CMS_DEPARTMENTWHERECMS_DEPARTMENT.DEPARTMENT=’电子办’ANDCMS_DEPARTMENT.USER_NAME=CMS_USER.USER_NAME

将提高一定的效率。

查询嵌套层次越多，效率越低。

应当尽量避免子查询。

如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

排序

利用索引自动以适当的次序输出时，可以避免对表中数据排序，当以下的情况发生时，排序就不能省略：

Ø索引中不包括一个或几个待排序的列；

Øgroupby或orderby子句中列的次序与索引的次序不一样；

Ø排序的列来自不同的表。

正确地增建索引、合理地合并数据库表，可以避免不必要的排序。

如果排序不可避免，那么应当试图简化它，如缩小排序列的范围等。

UNION

如果不过滤多表中的重复数据，请使用UNIONALL；如果过滤多表中的重复数据，请使用UNION。

长语句

避免使用很长、很复杂的查询语句，如果有特殊需求必须书写较长的SQL语句，应该把语句分解成若干部分，每一部分形成一个存储过程或函数。

大表尽可能使用分区

大表分区：

超过1G的表尽可能使用分区，分区的原则和尽可能和维护该表的机制结合起来。

比如：

保留10天数据，每天删除10天前的一天数据，在删除数据的时候，可以采用采用altertabletable_nametruncatepartitionpartition_name,而后altertabletable_namedroppartitionpartition_name;这里不直接使用drop的原因是减小数据库的开销。

Truncate是在秒的级别中完成。

其他注意事项

1）在条件语句中，如Wherea.s=b.c中将记录少的表放在等号的前部。

表关联条件放在语句的最后部。

2）不使用容易与系统关键字重复的单词来命名，如ID，DATE等，但可以使用如NODEID，BUYDATE等可以来命名；

3）SQL语句用大写字母（字段中的内容除外）；

4）编写ddl和dml时，每个语句后面必须加上分号；

5）对于定期增加性的性能数据表，必须建立有效的索引；

6）查询语句的Where语句必须落在索引上。

4高效的sql语句

Sql优化方法

RBO（rule-basedoptimizer）

CBO

驱动表

1）2张行数不一致的表连接

表TAB1行数：

16,384行

表TAB2行数：

1行

×SELECTCOUNT（*）FROMTAB2,TAB1;

○SELECTCOUNT（*）FROMTAB1,TAB2;

2）3张表连接

×SELECT*

FROMEMPE,

LOCL,

CATC

WHEREE.emp_noBETWEEN1000AND2000

ANDE.cat_no=C.cat_no

ANDE.locn=L.locn;

○SELECT*

FROMLOCL,

CATC,

EMPE

WHEREE.emp_noBETWEEN1000AND2000

ANDE.cat_no=C.cat_no

ANDE.locn=L.locn;

Where语句顺序的效率

1）使用索引引起的where语句效率

使用AND语句时行数多的放在前面

×SELECT*

FROMempE

WHEREemp_sal>50000

ANDemp_type='MANAGER'

AND25<（SELECTCOUNT（*）FROMEMPWHEREemp_mgr=E.emp_no）;

○SELECT*

FROMempE

WHERE25<（SELECTCOUNT（*）FROMEMPWHERE

emp_mgr=E.emp_no）

ANDemp_sal>50000

ANDemp_type='MANAGER';

使用OR语句时，行数多的放在后面

×SELECT*

FROMempE

WHERE25<（SELECTCOUNT（*）FROMEMPWHEREemp_mgr=E.emp_no）

OR（emp_sal>50000

ANDemp_type='MANAGER'）;

○SELECT*

FROMempE

WHERE（emp_sal>50000

ANDemp_type='MANAGER'）

OR25<（SELECTCOUNT（*）FROMEMPWHEREemp_mgr=E.emp_no）;

2）ROWID的使用

使用ROWID的WHERE语句效率最高。

SELECTROWID,...

INTO:

emp_rowid,...

FROMemp

WHEREemp.emp_no=56722

FORUPDATE;

UPDATEemp

SETemp.name=...,

WHEREROWID=:

emp_rowid;

3）减少访问次数

×SELECTemp_name,sal,grade

FROMemp

WHEREemp_no=0342;

SELECTemp_name,sal,grade

FROMemp

WHEREemp_no=0291;

○SELECTA.emp_name,A.sal,A.grade,

　B.emp_name,B.sal,B.grade

FROMempA,empB

WHEREA.emp_no=0342

ANDB.emp_no=0291;

4）Where语句的索引的使用

（1）SUBSTR

×SELECTacc_name,trans_date,amount

FROMtransaction

WHERESUBSTR（account_name,1,7）='CAPITAL';

○SELECTacc_name,trans_date,amount

FROMtransaction

WHEREaccount_nameLIKE'CAPITAL%';

（2）！

=

×SELECTacc_name,trans_date,amount

FROMtransaction

WHEREamount!

=0;

　　○SELECTacc_name,trans_date,amount

FROMtransaction

WHEREamount>0;

（3）TRUNC

×SELECTacc_name,trans_date,amount

FROMtransaction

WHERETRUNC（trans_date）=TRUNC（SYSDATE）;

　○SELECTacc_name,trans_date,amount

FROMtransaction

WHEREtrans_date

BETWEENTRUNC（SYSDATE）

ANDTRUNC（SYSDATE）+.99999;

（4）||

×SELECTacc_name,trans_date,amount

FROMtransaction

WHEREacc_name||acc_type='AMEXA';

○SELECTacc_name,trans_date,amount

FROMtransaction

WHEREacc_name='AMEX'

ANDacc_type='A';

（5）运算

×SELECTacc_name,trans_date,amount

FROMtransaction

WHEREamount+3000<5000;

○SELECTacc_name,trans_date,amount

FROMtransaction

WHEREamount<5000+3000;

SQL命令的使用秘诀

DECODE的使用

×SELECTCOUNT（*）,SUM（salary）

FROMemp

WHEREdept_no=0020

ANDemp_nameLIKE'SMITH%';

SELECTCOUN