数据库军规
上一篇 / 下一篇 2014-06-23 15:46:03 / 个人分类:编程
`'q"k R-Tq051Testing软件测试网T6XWz-{
51Testing软件测试网R!DP \r]军规一:【恰当控制事务大小,commit不要过于频繁。】51Testing软件测试网y"^ieW(}2n
51Testing软件测试网#WD@gX [军规二:【在OLTP系统中一定要注意使用绑定变量。】51Testing软件测试网WH Q4M'E_NA#S)I
:e Q0i.N+L$zh,}0军规三:【在OLTP系统中一定要注意复杂的多表关联不宜超过4个,关联十分复杂时,需要拆分成多个步骤,防止执行计划不正确。】
f3Q,h;~(`l+EC0QP|F1W0军规四:【合理收集统计信息,固定住SQL的执行计划。】51Testing软件测试网 Q t j2X1KQ%a
51Testing软件测试网 l NFQ-d!y(`"}军规五:【尽量避免使用XA事务,在RAC环境中要避免XA事务跨节点操作。】51Testing软件测试网zd u U-l%Kgx
#C3v@dV0军规六:【不可以对表或索引指定并行度,如果使用Oracle并行处理方式(并行查询、并行DML),只能在具体语句中指定并行度。在并行建表或者建索引之后,需要手工设置其并行度为1。】51Testing软件测试网rL%gsa2nMwC
e;g H/~0P/_0军规七:【避免频繁的检索lob类型及较长字符型的字段,尽量不要在较长字符串的字段上建立索引,如char(1000)、varchar2(1000)等。】
rc.io,Y7D051Testing软件测试网G,iLU)i a_\GV#l军规八:【任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。对于大批量数据的删除,要考虑使用特殊方式处理,不要使用delete方式。】
"@`?u+`6ni kY0D!d^bF0军规九:【合理设计数据库对象】51Testing软件测试网(YI"qY*\,Ar
q2k6Dx` dt0军规十:【合理使用RAC】
z&`!A/r(f#E2s-Z8j9VI0!OC1sNo$x_*w0
i y,R9v C4|0x[ |Rs$B0
)fH`)q pu#}UX0
{kcA%S I0军规说明$a:p~\%?2Y tH.@h^ km051Testing软件测试网&f fsNRN0{\q!LX1P
tNJPRNI"y0军规一:【恰当控制事务大小,commit不要过于频繁。】
ZIl]7YA$w051Testing软件测试网'q8wu5QOZb& 说明
RC?aiT{S04`$s.X`2j`}*\+y01. 要根据具体业务合理控制事务的大小,在需要提交时才提交事务,不要无目的减小事务;51Testing软件测试网hAoD9CZh
O7fWE }5]:@&kNhiy0h02. 事务过小,频繁commit会带来以下影响:
j,GlI;m*M2AX"X0{&C&D;y*o's0Ø 程序性能降低,执行时间长,因为需要花费大量时间来等待log file sync事件;51Testing软件测试网6LBTr|2D
51Testing软件测试网 pf(_3|dYØ 产生的总的redo、undo数量变大;51Testing软件测试网-xa9{g(U+[3w L5?v
51Testing软件测试网8oa\\Cs(`&hØ 系统总的栓锁数量变大,造成并发能力减弱;
z6o1_3Sg051Testing软件测试网%@ q-gN:w9Y2TØ 由于已commit的undo信息可以被覆盖,因此容易造成ORA-01555错误。
p4Um,n4p2Z0%I` ge.Z kC9Dxs2Rm8J03. 事务过大,会带来以下影响:51Testing软件测试网+@&Jb$n#p
51Testing软件测试网~V r}8M"a}"FØ 长时间占有锁,对其他相关事务造成等待;51Testing软件测试网 S o,` g[E:y
$F*CWpt0Ø 产生大量undo,造成undo表空间严重扩张或不足。
1n ]UI0IT/K051Testing软件测试网azHhh)Q#pO军规二:【在OLTP系统中一定要注意使用绑定变量。】
6C\*K1p4?$s/QX.Pc051Testing软件测试网pw8wA9y【示例】51Testing软件测试网$b!i!K-^7W,ldN {p
+R6^ L,_ OS J(bf:BQ%{ Fl0不建议的写法:51Testing软件测试网+u"DS5N-p
51Testing软件测试网l'@2p/m%].WW;t'Bdeclare51Testing软件测试网A5~]W;ax{
51Testing软件测试网j2B-Z1]|-gv/A_type tcur_ref is ref cursor;51Testing软件测试网#m&s&I/IU+D{
51Testing软件测试网MGvUz%C6z/xg9pbcur_obj tcur_ref;
1n2TE}6A051Testing软件测试网l f+}'rOu^i_l_start number default dbms_utility.get_time;51Testing软件测试网yFy7V"r bW
~+ry+?_ cT0str_l_dummy all_objects.object_name%type;51Testing软件测试网t c!r ? F(S-v-c&M
?"N2E vje @ n0begin
j0U{gP03Smw.RC0for i in 1..100051Testing软件测试网 JqF!y$q
51Testing软件测试网ynbUx8e?loop
&o+C;U.a3aqNZ051Testing软件测试网 iw0cF}5r+d)Iopen cur_obj for
Ml9U-o8sl051Testing软件测试网 U P!Jb_s'select object_name51Testing软件测试网 S(g#SZ&P
51Testing软件测试网,SoB {v4r@ [&S)LM,ffrom all_objects
{jH$W'J0|051Testing软件测试网1t(t|%fmXLwhere object_id = ' || i; --未使用绑定变量51Testing软件测试网-bxF8v6eTbf
51Testing软件测试网&p-g~4xetfetch cur_obj51Testing软件测试网/S.OmdM[
51Testing软件测试网 dg:u v7a5x N5pR0Ginto str_l_dummy;
"L%gs-X4a.Y051Testing软件测试网;ar5Cj5f0m'V#M Bclose cur_obj;
2{,F!L8vo@5b4b'M c3}#b05RraJ3z G%}PK0end loop;51Testing软件测试网#_ylq$|^
By3_V"u h0dbms_output.put_line(round((dbms_utility.get_time-i_l_start)/100,2)||'seconds...');
R%mV+bA(y051Testing软件测试网*J%?4R_2nZ*`end;
y!QRE)]fq9e0@W8|XO?d0推荐写法:
&ax"m\mq,f2q051Testing软件测试网T `*wnNa,nN;_declare51Testing软件测试网ik%D6]!]{
;tp LP0uK"D0type tcur_ref is ref cursor;51Testing软件测试网$TKB$z a$c+@)GT&w
5B-}:n9W0|1e/d%D%e0cur_obj tcur_ref;51Testing软件测试网s/q Ke d
51Testing软件测试网lEE,S,hTi_l_start number default dbms_utility.get_time;51Testing软件测试网)C3H fuQHrh,H'xoD
51Testing软件测试网 h:w3^7CGstr_l_dummy all_objects.object_name%type;51Testing软件测试网oVT!p$k
9O'C2nCo._v0begin
^A5K1BSEe0)c)l"Te9J(^sK+pB0for i in 1..1000
mO _u*z(@zDh5v3S V0m5i8H/M"rI{%` ~0loop
"Urd)z.P:n'i0~5oZ6O2?F'eh0open cur_obj for51Testing软件测试网"gF@a5w!ub7Nt9X
51Testing软件测试网&Jm8m#t;N_2m%P/n m'select object_name51Testing软件测试网3BB8rYh4ne
51Testing软件测试网;tcG:O'k xwfrom all_objects
VnjiVb,qQ)h051Testing软件测试网0W%`0G8En$AZtwhere object_id = :x' using i; --使用了绑定变量51Testing软件测试网9{a9b{w4]*tHu
51Testing软件测试网,o9I \9qgWfetch cur_obj
:m Is^C.`K#I0{%TU2nO-~0into str_l_dummy;51Testing软件测试网#_/?;vIr X&~~U;}r
51Testing软件测试网 k J6Uo9Cclose cur_obj;
G9g_?B/C5m X0t&E#qy5B Z8KH0end loop;
H$Q*b:q&L RM051Testing软件测试网qo+a\~Ddbms_output.put_line ( round ( ( dbms_utility.get_time-i_l_start ) / 100,2) || 'seconds...');51Testing软件测试网0xs:o_\ d\X O6FO
51Testing软件测试网e+mU-A/y(S}/]^ oend;51Testing软件测试网%_|1diS'h(ma1I.V
51Testing软件测试网)Y9E8i"A#M& 说明
0Dun;|$]|f051Testing软件测试网 L5m5Ro)_:q&g9R1. OLTP系统的特点是同一个SQL语句的执行频繁度高,因此,减少分析时间和减少消耗在分析上的资源是非常重要的。不使用绑定变量会引起下面的问题:
~[ |G9}/HT5t051Testing软件测试网hE6hG,|(hØ 严重降低系统的并发能力。不绑定变量会造成过多的硬分析,过多的硬分析会导致共享池的栓锁争用,而过多的栓锁争用会严重降低系统的并发能力。51Testing软件测试网5W+p j E0l.p
51Testing软件测试网;f4fh P0R*]'VØ 容易引起ORA-04031错误。51Testing软件测试网N eBsOF[nD
51Testing软件测试网 ?|Q Y6C\xX'g2. 数据仓库系统的特点是同一SQL语句的执行频繁度低,因此,分析时间和分析所消耗的资源可以忽略,使SQL语句根据实际的数据分布获得一个最佳的执行计划才是最重要的。因此,数据仓库系统中是否绑定变量并不重要,而且由于Oracle Peeking机制的问题,绑定变量可能会引起软分析的SQL语句执行计划不合理的问题。
ma*a G3Qu7t051Testing软件测试网A f L7L3JcBks0R51Testing软件测试网y*``1C8G
51Testing软件测试网C;Y6wMZ!_军规三:【在OLTP系统中一定要注意复杂的多表关联不宜超过4个,关联十分复杂时,需要拆分成多个步骤,防止执行计划不正确。】
)VLe.XENc051Testing软件测试网{XF,i,kTxp,fn,{3tJ8G051Testing软件测试网x} h|8R8k#j`S$l军规四:【合理收集统计信息,固定住SQL的执行计划。】51Testing软件测试网 I\7|~t-M
51Testing软件测试网8e}Q5]2j#k& 说明
yR_0HL;|z3NvEm6h0?RAr V"MnM6g0g.e01. 数据库优化器是根据统计信息来判断执行计划的成本,因此,正确的统计信息有利于优化器产生正确的执行计划;
7tFI#K:~ YY0C0(BaXTI02. 统计信息收集策略可以采用如下方式:51Testing软件测试网I`R-w Z y*_9m
]+ru w$Ec@"kc~0Ø 方法1:实验室中构造基准数据,收集统计信息并验证,随业务版本发布。
d1}#Mq${~$F K0F&C1r6n+\[#u4|0Ø 方法2:在现网收集并锁定统计信息;收集新导入对象的统计信息;当某些对象统计信息不对的时候,重新收集这些对象的统计信息。51Testing软件测试网+Ib.z:F @ w zY;M
51Testing软件测试网U3Pn#{(]Ø 方法3:采用“90-9-1”收集原则,90%采用自动收集策略,9%定制收集策略,1%加提示或者profile固定。51Testing软件测试网x4@CJ:^x
51Testing软件测试网*EIN"v&j6l0K3. 无论采取何种策略,必须保证实验室和现网SQL的执行计划一致,每个SQL上线前必须验证好执行计划。51Testing软件测试网@!`t!h:ae-R
rrH [2D$S:a051Testing软件测试网Y`-TzD$@b
U^;V5y~"I;wL0军规五:【尽量避免使用XA事务,在RAC环境中要避免XA事务跨节点操作。】51Testing软件测试网$h {q3ff*\LF8t)a
2Db}0v,b0& 说明51Testing软件测试网RV'@F|[#P if
9c#Yd5U/])eo(|01. 使用分布式数据库的时候,业务尽量隔离开,减少使用XA事务,如果大量出现了XA事务,需要考虑业务分割的是否合理。
&R-vu%^&FE N0u%ko VmY6i02. 在RAC中,禁止将XA事务分布在多个节点上,避免XA事务跨节点操作。51Testing软件测试网u6y,}DnA3I Xn
6[BcK'yr0
1R5K)j9K3Kn051Testing软件测试网5MmU"{S oJ"g.i51Testing软件测试网:F)G`G\'|b
lM1~7_@%k\!n9@0军规六:【不可以对表或索引指定并行度,如果使用Oracle并行处理方式(并行查询、并行DML),只能在具体语句中指定并行度。在并行建表或者建索引之后,需要手工设置其并行度为1。】51Testing软件测试网Iv2s)m-z,N*P
51Testing软件测试网!c$q BaG5R/p"jr){K5Qb6j_051Testing软件测试网v7N0vM|/\军规七:【避免频繁的检索lob类型及较长字符型的字段,尽量不要在较长字符串的字段上建立索引,如char(1000)、varchar2(1000)等。】
s7{-[v$h o051Testing软件测试网)@x~L5I'p7]51Testing软件测试网hT(U)J%lj4C4c-z!@ @
51Testing软件测试网T"ra9D:l3`#m军规八:【任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。对于大批量数据的删除,要考虑使用特殊方式处理,不要使用delete方式。】
:PxE'`)^)FY0z(e/V"S Wl C0& 说明
pU0^5ToX)J%A051Testing软件测试网.\2Azk(I ?;Q*L!Zdelete不会释放高水位(HWM),一来造成空间的浪费,二来会导致效率的下降,另外delete方式删除大批量数据的效率也是极差的。可以考虑使用分区表和truncate、create table as等方法。
b#cS'qJ JQ"Ny0qc5b M"TX0
1M4s7aJfM051Testing软件测试网H7Z9v;Am%[p&SpG6L&p5N~7}i(Q051Testing软件测试网r@(s:?$?E~军规九:【合理设计数据库对象】51Testing软件测试网'j*X\9t5@&Sw
51Testing软件测试网%y m Q+b7aHV+~数据库对象是SQL优化的基础,由于数据库对象一旦建立,后续整改将十分困难,因此在设计过程中就需要考虑扩展性、性能、可维护性等相关因素。51Testing软件测试网,nud,oiB?5{HU
51Testing软件测试网6pid'N tS2qK以下关于数据库对象的设计要点是一定要遵循的:51Testing软件测试网(oy `v+`zSuSH
51Testing软件测试网n,c,G,C"{w*~W1l名称51Testing软件测试网 A+JM5zuk?
51Testing软件测试网~Y:q l'yM$IuP)mZ}要点
[1a8RkJS-z051Testing软件测试网]3g}-eB+[#x?表空间设计
!r[v:fh'sH051Testing软件测试网hAt U;B-y,`Q9VY1.空间根据业务特征或者功能特征进行分离。51Testing软件测试网+`|$zf'ZCp
51Testing软件测试网Z N H`8DD:ojH2.数据文件的数目不宜过多也不宜过少,过多会导致占用较多的资源,过少会导致资源竞争。51Testing软件测试网 uZ/@8cL/hYN l1l
51Testing软件测试网 H@O*Tf@O3. 新建立的表空间必须采用本地管理和自动段空间管理。
-c&B9u1G(I051Testing软件测试网nLRh eno;hz+Gp4. 没有必要频繁的整理表空间中的碎片。51Testing软件测试网Ep3BH@m
51Testing软件测试网P_o7\.E,Pr表设计51Testing软件测试网QcT rc5`.E"k
51Testing软件测试网W Xi'yrs0@1.数据库表和字段命名必须规范。
v3z]-h @}.^'O05s8?M"gNQ8sK02.字段数据类型定义必须规范。51Testing软件测试网\|;\x N)r l(Od
0vqX]l7M03.表的设计要尽量满足第二范式。
!`h:bjrDr051Testing软件测试网8p2Ui9BO A4.任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。51Testing软件测试网:h0n~3}"BU`
%g5q&J7A6j4U4q05.除非基于特殊情况考虑,通常情况下每个表都要有主键。51Testing软件测试网{9x$H.F3i^0t:m
51Testing软件测试网)Y/E*qd m&Mj,uzJp6.尽量避免使用大字段(LOB)或者超长字段(varchar2 > 1000)。51Testing软件测试网9b9r4\ ]M N6f0a5f;[
51Testing软件测试网4v'XW7Dz索引设计51Testing软件测试网!FT v&z!Teo pM
5I%^HB b01.数据库索引命名必须规范。51Testing软件测试网Rork7^r0nm
@;T*i*MB#U F"Y02.表的主键、外键必须有索引。51Testing软件测试网 Ld9y FNm3^k
51Testing软件测试网M/H p B(w3.经常查询且选择率低于5%的列需要建立索引。
%{v0{qH0W%P8@krN2_$\tz04.经常与其他表进行连接的表,在连接字段上应该建立索引。
QSB|)S7v$lZ,P3^`?|4C0l+J#sY N(o05.位图索引适合于DSS或者OLAP,不应该在OLTP的系统中或者DML操作较频繁的列上建立位图索引。
-R8U HtYB o;k'o051Testing软件测试网(PMrX []S1w6.可以适当的使用函数索引来完成特殊的优化。
_&zFkG6c3c&KS D"X051Testing软件测试网9o;ml+L9G7.避免大范围的使用复合索引,复合字段不宜过多。51Testing软件测试网!]2h3c5f.XL
)vm.~vNf+zyC9Y0分区设计51Testing软件测试网2Ug;Xvg
51Testing软件测试网,d#[H |9~Hsvn{1.分区表上尽量使用本地索引。51Testing软件测试网1Y7Q1P:Km*K]$l*d
51Testing软件测试网p }Im#E(|0W#T2.使用分区表可以有效地分割数据,易于管理,提高性能和可用性。
$ckK-k$\7`%S051Testing软件测试网^#h/l [hy6P3.依据业务特性合理的设计与使用分区表。51Testing软件测试网(T,{ C"gpc:i1[
51Testing软件测试网s\i-y2XV4lI#_0F RT;I(u)L3E01.1 表空间设计b~wR&o9` fc01. 用户表空间与系统表空间(system、sysaux)分离。
G!ruP8v#tD M1D0\ O,m#WB2O%p~VJ*`02. 数据表空间与索引表空间分离。
'n1Ea5O}4Aei0.j-}5lrd0O!}T+fop _03. 业务表空间与日志表空间分离。51Testing软件测试网,|l6]/v c~%l
5H"d7K%I(d04. OLTP系统最好不要使用BIG FILE TABLESPACE。
3Cl2P'O&T0&s#BE TM2p.uN(J05. 小型数据库数据文件统一使用8GB;中型数据库数据文件统一使用16GB;大型数据库数据文件统一使用24GB,不允许使用数据文件自动扩展。
P3py gQ%p#^0vR0g!F+A[ l06. 用户新建立的表空间必须采用本地管理和自动段空间管理。51Testing软件测试网X\ np-gU:\
51Testing软件测试网}s\#c"Y:m1~ N+^-U|7. 没有必要频繁的整理表空间中的碎片,除非碎片率达到了80%以上。
0\JG;|w9g051Testing软件测试网xP5aV Q8. 如果表空间因频繁的DDL操作出现碎片,建议设置表空间的UNIFORM. SIZE为合适的数值(例如1MB),来减少碎片的产生。51Testing软件测试网0Zg"FQ B-c
51Testing软件测试网 my*b?:[M| D51Testing软件测试网)_'uXc3zr2U%D
表设计'aq s+V#T0数据库表名和字段命名规范
-[cC(x+G z7N08K.N{`hBv01. 数据库表名必须使用前缀“T_”。
*rn*U5\g_)A*_Y0|1F!PC n02. 命名尽量采用富有意义、易于记忆、描述性强、具有唯一性的英文词汇,不准采用汉语拼音。51Testing软件测试网!J%a!c@%ZU
51Testing软件测试网U{{sv-K3. 命名不允许超过30个字符。51Testing软件测试网+X1I-M[o.a$k9h X
51Testing软件测试网&s+s+a\O2?e4. 命名采用英文单数,不允许使用复数形式。
Sp9M#N+wo+GVe0:~ o+}3i|;?05. 命名尽量简短,最好不要使用英文缩写。51Testing软件测试网+QRN5zi[/?
51Testing软件测试网Nu,x9mkq6j]-{&v*N&}W9A0Mc_`_m3Q+QI.Iz/T0字段数据类型定义规范
B*?5qo2Qp051Testing软件测试网}BH%b,r@,fA+i1. 数字类型一律使用NUMBER[(precision, scale)]定义。
uO4]y8_$\_0loA.Y;A-I02. 字符类型一律使用VARCHAR2 (size byte)定义。51Testing软件测试网0gds^ uxR&dnT+Yq
+J7kj4{_Ao$K8xL+r i03. 时间类型使用DATE定义,如果时间要求精确到毫秒级,可以使用TIMESTAMP[( precision )]定义。51Testing软件测试网cu)_~6JQ
4L4XeiBG"d^04. 除非特殊要求,否则不允许使用LOB数据类型、BOOLEAN数据类型和用户自定义的数据类型。
AMX;iVhR\0M*~3c6gX d;z05. 确保数据类型定义的精度(precision或者size)能够包含字段所有的取值,最好是留一定的冗余。
l n`~`8Asy01_,Os7gx06. 对于不同表的相同字段,必须保证字段名和字段类型完全一致。
G%p~Ab8S}-c00^oy oxC5X0
_^4}O:K;w}#V051Testing软件测试网2Q6\f_"A ]0^设计原则
0a.Z,y7c1l;X+m(}MY051Testing软件测试网+|k?)JL h1K1oS1. 任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。
F)|H x1Kt`(mPL051Testing软件测试网Y"ko9|IqX2. 严禁将业务数据放在系统表空间(system)中,必须有独立的表空间存放业务数据。
+n;`3ahg+O2q9|k051Testing软件测试网fMj0K%~c3. 在不同业务系统共用同一个数据库时,要注意不同业务系统的对象必须存放在不同的用户下面,绝不能混放在同一用户下。