数据库开发规范.docx
《数据库开发规范.docx》由会员分享，可在线阅读，更多相关《数据库开发规范.docx（19页珍藏版）》请在冰点文库上搜索。
数据库开发规范
项目一部数据库开发规范
版本号
日期
修改者
说明
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%';
SELECTCOUNT（*）,SUM（salary）
FROMemp
WHEREdept_no=0030
ANDemp_nameLIKE'SMITH%';
○SELECTCOUNT（DECODE（dept_no,0020,'X',NULL）） D0020_count,
COUNT（DECODE（dept_no,0030,'X',NULL）） D0030_count,
SUM（DECODE（dept,0020,salary,NULL））D0020_sal,
SUM（DECODE（dept,0030,salary,NULL））D0030_sal
FROMemp
WHEREemp_nameLIKE'SMITH%';
表的别名的使用
×SELECTE.emp_no,name,tax_no,C.comp_code,comp_name
FROMcompanyC,empE
WHEREE.comp_code=C.comp_code;
○SELECTE.emp_no,E.name,E.tax_no,C.comp_code,C.comp_name
FROMcompanyC,empE
WHEREE.comp_code=C.comp_code;
去掉重复行
DELETEFROMempE
WHEREE.rowid>（SELECTMIN（X.rowid）
FROMempX
WHEREX.emp_no=E.emp_no）;
表的行计数
SELECTCOUNT（有索引的列）FROMTRANS;
SELECTCOUNT（*）FROMTRANS;
SELECTCOUNT
（1）FROMTRANS;
用WHERE语句替换HAVING语句的使用
×SELECTregion,AVG（loc_size）
FROMlocation
GROUPBYregion
HAVINGregion!
='SYDNEY'
ANDregion!
='PERTH';
○SELECTregion,AVG（loc_size）
FROMlocation
WHEREregion!
='SYDNEY'
ANDregion!
='PERTH';
GROUPBYregion
使用表连接替代EXISTS使用
×SELECTemp_name
FROMemp
WHERE（emp_cat,sal_range）=
（SELECTMAX（category）,MAX（sal_range）FROMemp_categories）
ANDemp_dept=0020;
○SELECTemp_name
FROMemp
WHEREemp_cat=（SELECTMAX（category）FROMemp_categories）
ANDsal_range=（SELECTMAX（sal_range）FROMemp_categories）
ANDemp_dept=0020;
使用EXISTS替代表连接
×SELECT...
FROMdeptD,empE
WHEREE.dept_no=D.dept_no
ANDE.emp_type='MANAGER'
ANDD.dept_cat='A';
○SELECT...
FROMempE
WHEREEXISTS（SELECT'X'FROMdept
WHEREdept_no=E.dept_no
ANDdept_cat='A'）
ANDE.emp_type='MANAGER';
×SELECT...
FROMdeptD,empE
WHEREE.dept_no=D.dept_no
AND（E.emp_type='MANAGER'
ORD.dept_cat='A'）;
○SELECT...
FROMempE
WHEREE.emp_type='MANAGER';
OREXISTS（SELECT'X'
FROMdept
WHEREdept_no=E.dept_no
ANDdept_cat='A'）;
使用EXISTS代替DISTINCT语句
×SELECTDISTINCTdept_no,dept_name
FROMdeptD,empE
WHERED.dept_no=E.dept_no;
○SELECTdept_no,dept_name
FROMdeptD
WHEREEXISTS（SELECT'X'
FROMempE
WHEREE.dept_no=D.dept_no）;
使用NOTEXISTS代替NOTIN语句
×SELECT...
FROMemp
WHEREdept_noNOTIN（SELECTdept_no
FROMdept
WHEREdept_cat='A'）;
○SELECT...
FROMempE
WHERENOTEXISTS（SELECT'X'
FROMdept
WHEREdept_no=E.dept_no
ANDdept_cat='A'）;
使用unionall代替union语句
Union--------进行排序
UnionAll--------不排序
使用Union和IN代替OR语句
Loc_no,region上有索引
×SELECT...
FROMlocation
WHEREloc_id=10
ORregion='MELBOURNE';
○SELECT...
FROMlocation
WHEREloc_id=10
UNIONALL
SELECT...
FROMlocation
WHEREregion='MELBOURNE';
×SELECT...
FROMlocation
WHEREloc_id=10
ORloc_id=20
ORloc_id=30;
○SELECT...
FROMlocation
WHEREloc_idIN（10,20,30）;
5高效索引
索引的使用
使用索引时，要考虑以下因素：
1）索引列的计算
2）索引列的增加
3）索引列不要用NOT
4）索引中空值的使用
ISNULL,ISNOTNULL
5）索引列的数据类型的变换
EMP_TYPE为varchar2类型，下列语句使用索引
SELECT...
FROMemp
WHEREemp_type=123;
SELECT...
FROMemp
WHERETO_NUMBER（emp_type）=123;
SELECT...
FROMemp
WHEREemp_type='123';
增加索引
增加索引要考虑多种因素，要考虑对更新、插入的影响等。
1）频繁查询不超过大表1%~15%的场合。
2）列的不同值
3）列
4）列
5）同表
6解析表的使用方法
SQLTRACE
参数表的生成
$>sqlplus–s/
SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL>commit
SQL>exit
AUTOTRACE的用法
@afe57gc2/users/home/ino29>sqlplussys/****
SQL>@$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>droproleplustrace;
：
SQL>grantplustracetoops$ino;
SQL>exit
AUTOTRACE功能的使用
@afe57gc2/oracle/product/8.1.737>sqlplus/
SQL>setautotraceon
SQL>select*fromwcattori;
执行计划
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'WCATTORI'
統計
----------------------------------------------------------
0recursivecalls
12dbblockgets
4consistentgets
0physicalreads
0redosize
4779bytessentviaSQL*Nettoclient
314bytesreceivedviaSQL*Netfromclient
1SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
0rowsprocessed
SQL>
TKPROF
初始化
@afe57gc2/oracle/product/8.1.740>sqlplus/
SQL>ALTERSESSIONSETTIMED_STATISTICS=true;
SQL>ALTERSESSIONSETMAX_DUMP_FILE_SIZE=500;
SQLTrace开始
SQL>ALTERSESSIONSETSQL_TRACE=true;
实例
SQL>SELECT*FROMWCATTORI;
SQLTrace结束
SQL>ALTERSESSIONSETSQL_TRACE=false;
SQL>exit
内容变换
@afe57gc2/users/home/ino44>su
Password:
#chmod777/oracle/product/8.1.7/rdbms/log/ora_14186_wca1.trc
#exit
@afe57gc2/users/home/ino46>tkprof$ORACLE_HOME/rdbms/log/ora_14186_wca1.trcoutput.txt
TKPROF:
Release8.1.7.2.0-Productionon木Apr1815:
55:
172002
（c）Copyright2000OracleCorporation.Allrightsreserved.
備考：
ログファイルの出力先
SQL>columnnameformatA20
SQL>columnvalueformatA30
SQL>selectname,valuefromv$parameterwherenamelike'%dump_dest';
NAMEVALUE
---------------
升级会员 