数据库开发规范文档格式.docx
《数据库开发规范文档格式.docx》由会员分享,可在线阅读,更多相关《数据库开发规范文档格式.docx(16页珍藏版)》请在冰豆网上搜索。
索引
idx_
序列
seq_
存储过程
pr_
包
函数
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语句时行数多的放在前面
FROMempE
WHEREemp_sal>
50000
ANDemp_type='
MANAGER'
AND25<
(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no);
WHERE25<
(SELECTCOUNT(*)FROMEMPWHERE
emp_mgr=E.emp_no)
ANDemp_sal>
ANDemp_type='
;
使用OR语句时,行数多的放在后面
WHERE25<
(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no)
OR(emp_sal>
);
WHERE(emp_sal>
)
OR25<
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
WHEREemp_no=0342;
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
WHEREaccount_nameLIKE'
CAPITAL%'
(2)!
=
WHEREamount!
=0;
○SELECTacc_name,trans_date,amount
FROMtransaction
WHEREamount>
0;
(3)TRUNC
WHERETRUNC(trans_date)=TRUNC(SYSDATE);
○SELECTacc_name,trans_date,amount
WHEREtrans_date
BETWEENTRUNC(SYSDATE)
ANDTRUNC(SYSDATE)+.99999;
(4)||
WHEREacc_name||acc_type='
AMEXA'
WHEREacc_name='
AMEX'
ANDacc_type='
A'
(5)运算
WHEREamount+3000<
5000;
WHEREamount<
5000+3000;
SQL命令的使用秘诀
DECODE的使用
SELECTCOUNT(*),SUM(salary)
WHEREdept_no=0020
ANDemp_nameLIKE'
SMITH%'
SELECTCOUN