数据库开发规范

[b]转载自其它网站[/b]

数据库开发规范

命名规范

表:同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义,长度以不超过15个字符为宜(避免超过20)

字段名:表达其实际含义的英文单词或简写,不要使用类似VALUE1这种无意义的字段名

索引:表名称_字段名_IND

约束:表名称_PK(或者PK_表名称),表名称_UK(或者UK_表名称),务必保持一致

触发器:表名称_A(After)B(Before)I(Insert)U(Update)D(Delete)_TRG,如果是同步触发器以 sync作为前缀:sync_表名_trg,函数过程:采用动词+名词的形式表达其含义
常用数据类型

NUMBER(p,s):固定精度数字类型。

NUMBER:不固定精度数字类型,当不确定数字的精度时使用,PK通常使用此类型。

DATE:当仅需要精确到秒时,选择DATE而不是TIMESTAMP类型。

TIMESTAMP:扩展日期类型,不建议使用。

VARCHAR2:变长字符串,最长4000个字节,

CHAR:定长字符串,除非是CHAR(1),否则不要使用。

CLOB:当超过4000字节时使用,但是要求这个字段必须单独创建到一张表中,然后有PK与主表关联。此类型应该尽量控制使用。
表:

a)如果有必要,总是包含两个日期字段:gmt_create(创建日期),gmt_modified(修改日期)。

b)尽可能使用简单数据类型,不要使用类似数组或者嵌套表这种复杂类型。

c)必须要有主键,且尽量不要使用存在实际意义的字段做主键。

d)需要join的字段,数据类型保持绝对一致。

e)允许适当冗余,以提高性能,但是必须考虑数据同步的情况。

f)当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表(主要是为了性能考虑)。
PK约束:

a)PK最好是无意义的,由Sequence产生的ID字段,不建议使用组合PK。如果除了PK以外,还存在其他的唯一约束,可以创建UK。

b)不要试图通过创建唯一索引来达到唯一约束的效果,不管是pk还是unique约束(实际上我们严格控制建立unique index,因为一旦建立,这个索引的的唯一约束性将永远无法在不影响索引的情况下被摘除),必须显式创建普通索引和约束,而不是仅仅创建一个NOT NULL+UNIQUE INDEX的组合(即先create一个以约束名命名的index(普通索引,而不是带约束的索引),然后创建一个约束,并 using index ...,这样做的好处就是当需要摘掉约束的时候可以做到不影响索引)。

c)当摘除约束的时候,为了确保不影响到index,最好加上keep index参数。

d)PK字段不能被更新。

e)外键约束一般不在数据库上创建,只表达一个逻辑的概念,由程序控制。

f)当万不得已必须使用外健的话,必须在外健列创建INDEX。
索引

a)Bitmap索引通常不适合我们的环境。

b)索引根据实际SQL,由DBA创建。

c)索引不是万能的,很多情况下索引并不能解决问题。

d)严格控制创建带约束的索引,所有的约束效果都通过显示创建约束然后再using 一个已经创建好的普通索引来实现
sql编写规范

1.避免在where子句中对字段施加函数,这样将导致索引失效,比如:

select * from member where to_char(gmt_create,'yyyymmdd')='20070101';

原因:在建立index的时候是根据字段来建立的,也就是说oracle在inidex的时候是索引的字段的值,如果提供给oracle的是一个需要经过函数处理的比较,oracle就没办法通过索引中的索引键值来进行相应的比较,所以就不会走到索引上

2.避免在SQL中发生隐式类型转换

如:

select * from table where id='123'; -- # 这里ID是NUMBER型,会造成oracle将id先转换成varchar类型再比较,造成索引失效

select * from table where gmt_create = to_char('2000-01-01','yyyy-mm-dd');

-- # 会造成oracle先将gmt_create转换成varchar类型,造成无法走index,这里还有一个需要注意的,就是当和oracle中date 类型的字段比较的时候,最好先通过to_date('2000-01-01','yyyy-mm-dd')函数将传入的值转换一下,即使传入的是java 的date类型也这样做,因为这样也很可能让oracle出现隐式转换

3.全模糊查询无法使用INDEX,应当尽可能避免

select * from table where name like '%jacky%';

4.如果使用Oracle数据库,使用Oracle的外连接,而不是标准的外连接语法

正确:select * from table1 a,table2 b where a.id=b.id ;

错误:select * from table1 a left join on test2 b a.id=b.id;

5.分页语句必须使用三层嵌套的写法(不是10g的数据库不要使用分析函数)

select * from (

select rownum rn,a,* from (

select * from table where 条件 order by 条件

) a where rownum<=100

) where rn>80;

6.使用in的绑定问题

a)使用ibatis的in list绑定,但是必须注意不能超过1000项。

b)使用str2numlist_order,str2varlist_order函数,将一个字符串转换为内存表。

7.判断NULL应该使用IS NULL或者IS NOT NULL,而不是=null或者null

8.sql中的/+ ordered use_nl(member offer)/是hint,用来确定SQL的执行计划,请在 DBA确认后使用。

9.在使用ibatis时候,sqlmap文件编写需要严格遵守以下规范:Ibatis中的SqlMap编写规范
Ibatis中的SqlMap编写规范
select语句中*号的问题

1。表连接时,绝不允许写select * ,否则按照编码错误Bug处理。

2。单表查询,一般情况下可以使用select * ,但以下几种情况禁用:

a、表中包含lob字段(BLOB、CLOB、LONG、LONG RAW等)。

b、表中包含长度较大的字段,如varchar2(1000)以上的字段,但该SQL实际上并不需要取出该字段的值。

c、字段数量很多,但实际要用的字段很少,比如表有50个字段,而你实际只用5个,并且该sql目前没有被重用。

d、DBA要求优化调整的。
严格要求使用正确类型的变量,杜绝oracle做隐式类型转换的情况

1、推荐在sqlmap的变量中指定变量的数据类型,如:select * from iw_user where iw_user_id = #userid:VARCHAR#

2、其中,对于时间类型的字段,必须使用TO_DATE进行赋值(当前时间可直接用sysdate表示),不允许下列这些错误用法:

错误的写法(使用date类型的变量):

select * from iw_account_log

where trans_account = #transaccount:varchar#

and trans_dt >= #dateBegin:date# and trans_dt < #dateEnd:date#

错误的写法(将to_date函数和数字进行算术运算):

select * from iw_account_log

where trans_account = #transaccount:varchar#

and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')

and trans_dt < to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss') + 1

正确的写法:

select * from iw_account_log

where trans_account = #transaccount:varchar#

and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')

and trans_dt < to_date(#dateEnd:varchar#, 'yyyy-mm-dd hh24:mi:ss') /*或 trans_dt < sysdate */

3、对于变量数据类型错误导致SQL严重性能问题的,按严重的编码错误Bug处理!
绑定变量和替代变量

在Ibatis中:

绑定变量用 #变量名# 表示

替代变量用 $变量名$ 表示

除了特别情况(经由DBA和架构组安全工程师审批)外,都只能使用绑定变量而不得使用替代变量,对于原来order by传入数组的问题,安全工程师已经要求全部更改掉,架构组会提供相应解决方案。

注意几点:

1) 通常,应使用绑定变量,尤其是具体取值变化范围较大的变量,如iw_user_id = #userid#。

2) 取值范围很小(比如枚举字段),并且通常取值会比较固定,在DBA预先同意的情况下使用替代变量,或者干脆使用常量。

3) 当一个绑定变量在实际使用中实际取值总是为某一固定常量时,应当直接使用常量而不是变量,关于这点,可在具体使用时咨询DBA

4) 在order by子句中,通常使用替代变量而不是绑定变量。

5) IN子句,使用"iterate + 数组类型变量"的方式实现绑定变量,例如:

<isNotEmpty prepend="and" property="userIds">

<iterate property="userIds" open="t.creator in (" close=")" conjunction=",">

#userIds[]#

</iterate>

</isNotEmpty>

将生成 t.creator in (:1, :2, :3, :4, :5 ...) 的语句
在字段上加函数的问题

1) 通常,不允许在字段上添加函数或者表达式,如:

错误的写法:

select * from iw_account_log where to_char ( trans_dt, 'yyyy-mm-dd') = '2007-04-04';

select qty from product where p_id + 12 = 168;

正确的写法:

select * from iw_account_log

where trans_dt >= to_date ( '2007-04-04', 'yyyy-mm-dd') and trans_dt < to_date ( '2007-04-05', 'yyyy-mm-dd');

select qty from product where p_id = 168 - 12;

2) 如果是业务要求的除外,但需要在编写时咨询DBA

3) 特别注意,当表连接时,用于连接的两个表的字段如果数据类型不一致,则必须在一边加上类型转换的函数,如

错误的写法(a.id是number类型,而b.operator_number是char类型):

select count from adm_user a, adm_action_log b where a.id = b.operator_number and a.username = '小钗';

正确的写法:

select count from adm_user a, adm_action_log b where to_char(a.id) = b.operator_number and a.username = '小钗';

select count from adm_user a, adm_action_log b where a.id = to_number(b.operator_number) and a.username = '小钗';

上面两种写法哪个正确?遇到这种情况时必须咨询DBA!
表连接

1) 不使用ANSI连接,如inner join、left join、right join、full outer join,而使用(+)来表示外连接

错误的写法:

select a.*, b.goods_title from iw_account_log a left join beyond_trade_base b on a.TRANS_OUT_ORDER_NO = b.trade_no

where a.trans_code = '6003' and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)

正确的写法:

select a.*, b.goods_title from iw_account_log a, beyond_trade_base b

where a.TRANS_OUT_ORDER_NO = b.trade_no and a.trans_code = '6003'

and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)
表连接分页查询的使用

1、包含排序逻辑的分页查询写法,必须是三层select嵌套:

错误的写法:

SELECT t1.* FROM (

SELECT t.*, ROWNUM rnum FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC

) t1 WHERE rnum >= :4 AND rnum < :5

正确的写法:

SELECT t2.* FROM (

SELECT t1.*, ROWNUM rnum FROM (

SELECT t.* FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC

) t1 WHERE ROWNUM <= :4

) t2 WHERE rnum >= :5

2、不包含排序逻辑的分页查询写法,则是两层select嵌套,但对rownum的范围指定仍然必须在不同的查询层次指定:

错误的写法:

SELECT t1.* FROM (

SELECT t.*, ROWNUM rnum FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

) t1 WHERE rnum >= :4 AND rnum <= :5

正确的写法:

SELECT t1.* FROM (

SELECT t.*, ROWNUM rnum FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= :4

) t1 WHERE rnum >= :5

3、注意下面两种写法的逻辑含义是不同的:

按创建时间排序(倒序),然后再取前10条:

SELECT t2.* FROM (

SELECT t1.*, ROWNUM rnum FROM (

SELECT t.* FROM sell_offer t

WHERE owner_member_id = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC

) t1 WHERE ROWNUM <= 10

) t2 WHERE rnum >= 1

随机取10条,然后在这10条中按照交易创建时间排序(倒序):

SELECT t1.* FROM (

SELECT t.*, ROWNUM rnum FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= 10

ORDER BY gmt_create DESC

) t1 WHERE rnum >= 1

4、先连接后分页与先分页后连接

性能较差:

SELECT t2.* FROM (

SELECT t1.*, ROWNUM rnum FROM (

SELECT a.*, b.receive_fee FROM beyond_trade_base a, beyond_trade_process b

WHERE a.trade_no = b.trade_no AND a.seller_account = :1 AND a.gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND a.gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

ORDER BY a.gmt_create DESC

) t1 WHERE ROWNUM <= :4

) t2 WHERE rnum >= :5

性能较好:

SELECT /+ ordered use_nl(a,b) */ a., b.receive_fee FROM (

SELECT t2.* FROM (

SELECT t1.*, ROWNUM rnum FROM (

SELECT t.* FROM beyond_trade_base t

WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')

ORDER BY gmt_create DESC

) t1 WHERE ROWNUM <= :4

) t2 WHERE rnum >= :5

) a, beyond_trade_process b

WHERE a.trade_no = b.trade_no

后面这种写法的适用情况:

a、where子句中的查询条件都是针对beyond_trade_base表的(否则得到的结果将不相同)

b、关联beyond_trade_process表时,用的是该表的主键或者唯一键字段(否则将改变结果集的条数)
"<>"、"!="、"not in"、"exsits"和"not exists"的使用规范

1、原则上一般禁止使用"<>"、"!="和"not in",而应该转换成相应的"="和"in"查询条件

错误的写法:

select a.id,a.subject,a.create_type from product

where status <> 'new' and owner_member_id = :1

正确的写法:

select a.id,a.subject,a.create_type from product

where status in ('auditing','modified','service-delete','tbd','user-delete','wait-for-audit') and owner_member_id = :1

错误的写法:

select a.id,a.subject,a.create_type from product

where create_type not in ('new_order','vip_add') and owner_member_id = :1

正确的写法:

select a.id,a.subject,a.create_type from product

where create_type = 'cust_add' and owner_member_id = :1

2、原则上不允许使用"exsits"和"not exists"查询,应转换成相应的"等连接"和外连接来查询

错误的写法:

select a.id from company a

where not exsits (select 1 from av_info_new b where a.id = b.company_id)

正确的写法:

select a.id from company a,av_info_draft b

where a.id = b.company_id and b.company_id is null

错误的写法:

select count from company a

where exsits (select 1 from av_info_new b where a.id = b.company_id)

正确的写法:

select count from company a,av_info_draft b

where a.id = b.company_id

注:在通过等连接替换exsits的时候有一点需要注意,只有在一对一的时候两者才能较容易替换,如果是一对多的关系,直接替换后两者的结果会出现不一致情况。因为exsits是实现是否存在,他不care存在一条还是多条,而等连接时返回所关联上的所有数据。

3、如有特殊需要无法完成相应的转换,必须在DBA允许的情况下使用"<>"、"!="、"not in"、"exsits"和"not exists"
SQLMAP的其它编写规范

1、对表的记录进行更新的时候,必须包含对gmt_modified字段的更新,并且不要使用dynamic标记,如:

错误的写法:

update BD_CONTACTINFO

<dynamic prepend="set">

......

<isNotNull prepend="," property="gmtModified">

GMT_MODIFIED = #gmtModified:TIMESTAMP#

</isNotNull>

</dynamic>

where ID = #id#

正确的写法(当然,这里更推荐直接更新为sysdate):

update BD_CONTACTINFO

set GMT_MODIFIED = #gmtModified:TIMESTAMP#

<dynamic>

......

</dynamic>

where ID = #id#

2、不允许在where后添加1=1这样的无用条件,where可以写在prepend属性里,如:

错误的写法:

select count from BD_CONTRACT t where 1=1

<dynamic>

......

</dynamic>

正确的写法:

select count from BD_CONTRACT t

<dynamic prepend="where">

......

</dynamic>

3、对大表进行查询时,在SQLMAP中需要加上对空条件的判断语句,具体可在遇到时咨询DBA,如:

性能上不保险的写法:

select count from iw_user usr

<dynamic prepend="where">

<isNotEmpty prepend="AND" property="userId">

usr.iw_user_id = #userId:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="email">

usr.email = #email:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="certType">

usr.cert_type = #certType:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="certNo">

usr.cert_no = #certNo:varchar#

</isNotEmpty>

</dynamic>

性能上较保险的写法(防止那些能保证查询性能的关键条件都为空):

select count from iw_user usr

<dynamic prepend="where">

<isNotEmpty prepend="AND" property="userId">

usr.iw_user_id = #userId:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="email">

usr.email = #email:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="certType">

usr.cert_type = #certType:varchar#

</isNotEmpty>

<isNotEmpty prepend="AND" property="certNo">

usr.cert_no = #certNo:varchar#

</isNotEmpty>

<isEmpty property="userId">

<isEmpty property="email">

<isEmpty property="certNo">

query not allowed

</isEmpty>

</isEmpty>

</isEmpty>

</dynamic>

另外,对查询表单的查询控制建议使用web层进行控制而不是客户端脚本(JAVASCRIPT/VBSCRIPT)
4、聚合函数常见问题

1) 不要使用count(1)代替count(*)

2) count(column_name)计算该列不为NULL的记录条数

3) count(distinct column_name)计算该列不为NULL的不重复值数量

4) count()函数不会返回NULL,但sum()函数可能返回NULL,可以使用nvl(sum(qty),0)来避免返回NULL

5、NULL的使用

1) 理解NULL的含义,是"不确定",而不是"空"

2) 查询时,使用is null或者is not null

3) 更新时,使用等于号,如:update tablename set column_name = null

6、STR2NUMLIST、STR2VARLIST函数的使用:

1) 适用情况:使用唯一值(或者接近唯一值)批量取数据时

2) 编写规范:a表必须放在from list的第一位,并且必须在select后加上下面的hint

正确的写法:

select /+ ordered use_nl(a,b) */ b.

from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b

where a.column_value = b.trade_no;
大批量数据更新分批提交的规范

假设:

源: create table test_A(id number,name varchar2(40),tel varchar2(40),sex char(1));

目标: create table test_B(id number,name varchar2(40),tel varchar2(40),sex char(1));

逻辑: 从test_A找出符合的记录,然后update test_B;

实现过程(这个也可以作为批量提交的模板,方便以后大家使用):

declare

cnt number := 0;

begin--找出满足条件的记录,循环更新

for i in (select id,name,tel from test_A where name = 'wt') loop

--更新记录,然后记数加1

update test_B set test_B.name = i.name,test_B.Tel = i.tel

where test_B.id = i.id;

cnt := cnt + 1;

--累计到1000条提交一次,也可以是其他参数

if mod(cnt,1000) = 0 then

commit;

dbms_application_info.SET_CLIENT_INFO('have update ' || cnt || ' rows');

end if;

end loop;--最后外面COMMIT一次,防止最后一次未达到1000条,漏掉一批数据

commit;

dbms_application_info.SET_CLIENT_INFO( cnt || ' rows finished!');

end;
产品库ddl变更流程
执行变更时间的约定

变更之前要分析操作对象的访问频繁程度,按照等级考虑变更的时间和方式,非紧急变更,都安排在下班后、晚上空闲时执行。

提交人和审批人,都要严格遵守该约定。
ddl变更的范畴

1.表结构变更(非新增表)

变更之前要列出表的依赖关系,准备重新编译的脚本,变更后马上编译invalid对象。

对于访问程度很高的表,如果有依赖的trigger,按照以下步骤执行:

准备好数据订正的sql->disable trigger->变更->compile trigger->enable trigger->补足、订正这段时间内变化的数据

2.分析统计信息

包括分析表、索引、columns

分析之前一定要把统计信息做好备份(export),并把回滚的sql写好(delete,import,尤其是列的柱状图,需要使用 dbms_stats包才能删除),

一旦出现问题,马上执行回滚方案。

3.调整index

对现有的index作调整、或者新建index之前,尽量获得和这个表相关的sql(可以查询v$sqlarea),在测试库上充分测试新 index对sql的影响。

4.可能导致执行计划突变的变更

比如对表做grant操作,move table,rebuild index等(还有哪些情况?)

都会引起相关sql重新parse,可能导致执行计划突变,操作前后要特别注意影响。

5.系统级别的变更

比如修改系统参数,打开某 event等(还有哪些情况?)。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值