isTester.com发起人,某互联网测试经理,十年测试经验,公众号“简尚”; 分享实战测试技能、答疑解惑(测试职业探索经验、以及一些书本上学不到的各种职业干货);致力于软件测试从业者综合能力提升。 微信isTester 》》博客文章未标注转载均属「原创」,转载找老徐授权;

用尽洪荒之力整理的Mysql数据库32条军规

上一篇 / 下一篇  2016-10-29 14:40:14 / 个人分类:数据库DB

51Testing软件测试网 L(C?8rLW

'p?D+@W;j0写在前面的话:

+BVw!xC_0

}y6T;T;xsd0总是在灾难发生后,才想起容灾的重要性;51Testing软件测试网/C9xg8dGQ4i2K

51Testing软件测试网%kA @8p/RkRt!m:`

总是在吃过亏后,才记得曾经有人提醒过。

,rybq y*b`d051Testing软件测试网4G3x%}Y z#_m


ZU,~@`;Tm5L0
51Testing软件测试网3YXV.rX0vG{~

3U_;cq&cX#E#x0核心军规51Testing软件测试网B.@W sf^K]

2c5fVo4V5GS@01、不在数据库做运算51Testing软件测试网AV9p,R+te4v
cpu计算务必移至业务层
51Testing软件测试网zC8MeB}zh

t e+v.w7~'iY02、控制单表数据量
1CQ@C y4m0int型不超过1000w,含char则不超过500w;
[ hW_9q u&O }!u0合理分表;
&E`\BIiF7w:K Q0限制单库表数量在300以内;
51Testing软件测试网)o4pN5J0m

-F _9s:v0{^Qu03、控制列数量
%O6E J)r|m0字段少而精,字段数建议在20以内;
51Testing软件测试网fsd0xr f ^

%ORKoB6|4n:H04、平衡范式与冗余
v3DbI\ I*?0效率优先;51Testing软件测试网3],e ck0s ? _]
往往牺牲范式;
51Testing软件测试网"ua+@jWA2Jp

51Testing软件测试网/}J_9NE:L/U`

5、拒绝3B51Testing软件测试网0Nwm({ l%mJM

51Testing软件测试网-[ wx'm*ja L8QO

拒绝大sql语句:big sql
.f x\MH"q0拒绝大事务:big transaction
]5[&Wl*Ko0z#qoS0拒绝大批量:big batch

3Rv D/gz~em051Testing软件测试网]_#l,KlRA*~F


t4N9C}9a/e0
51Testing软件测试网JJd c'|+q

51Testing软件测试网'y'i5wvdQ


8W3{^O` TU0
51Testing软件测试网OA.z|2k/b&} m`

\z-B8m ^4j#NF/yX0字段类军规51Testing软件测试网{r7AM%m

51Testing软件测试网i5o1k#FR

6、用好数值类型

?0bd?iD;[051Testing软件测试网1r K E8mN6j5cI

tinyint(1Byte)
x){'Y]u!f(a,~]0smallint(2Byte)
t;?+{1{ Q$eX0mediumint(3Byte)51Testing软件测试网 l%p K&Fj&T
int(4Byte)
(b"H2h5r#W:P0bigint(8Byte)51Testing软件测试网M#P@;`/UO
bad case:int(1)/int(11)
51Testing软件测试网8sM1M5a0~;["kn

51Testing软件测试网x6Z[yx d

7、字符转化为数字51Testing软件测试网U~[l.C,b

51Testing软件测试网jsg ^vL)l

用int而不是char(15)存储ip

Jbs y?O051Testing软件测试网0r;Qo!F0R)v@5q

8、优先使用enum或set51Testing软件测试网4jT},zf-aJ \

OBp+s)[2I0例如:sexenum (‘F’, ‘M’)

o(A(J#T_8v$l}$B0T051Testing软件测试网#{5?M?K"Z hOAv

51Testing软件测试网3p+d-T5t7j;b0`
51Testing软件测试网9W.h.sm y:ic&_GM

l(PC~5ew09、避免使用NULL字段

'[*o+QR-m-y5N?0

4m\g;];r0NULL字段很难查询优化;51Testing软件测试网8x+fr'[!?!U
NULL字段的索引需要额外空间;
8q#Ik+@;Z%SP(G0NULL字段的复合索引无效;

C-H1@-?{N0
badcase'name'char(32)defaultnull'age'intnotnullgoodcase'age'intnotnulldefault0

6Vg U!G Dj010、少用text/blob51Testing软件测试网h'^I&IY W$w

51Testing软件测试网bFW!~-gF#k

varchar的性能会比text高很多;51Testing软件测试网uj+w-o.F([vE#D
实在避免不了blob,请拆表;

R7Rw7nk\E*W051Testing软件测试网o X3en9@w$qd

11、不在数据库里存图片

^2I5X1a'F^? K p'O051Testing软件测试网%W-Ue?KW0ZU S#\

51Testing软件测试网:A&XuUD2]/Y

^3B6E/l"M,kJ @0

Q1zUp:J o051Testing软件测试网 q u2\5ZMZ![*qI3qR9L
51Testing软件测试网+a7~$z/|8Hi/uf

51Testing软件测试网 Vqmq(E5x9zt(~

索引类军规51Testing软件测试网6z3L*le/C

,@kpu \9b \9U012、谨慎合理使用索引51Testing软件测试网0e)c1?/h[*`2E b

51Testing软件测试网0wl(W;W&hhTo

改善查询、减慢更新;51Testing软件测试网vkhSo:^)KA
索引一定不是越多越好(能不加就不加,要加的一定得加);51Testing软件测试网7iVsD2czJ8C
覆盖记录条数过多不适合建索引,例如“性别”;

)E;[`2b%m uOI0

||5eo0q;Y&J5q013、字符字段必须建前缀索引

2^:`7hP"b&CX I051Testing软件测试网!@,e3d z8lB

14、不在索引做列运算

rgCp&eQ0
badcaseselectidwhereage +1=10;
51Testing软件测试网@inDD2B8qD3@ n

15、innodb主键推荐使用自增列;51Testing软件测试网"e&ImzJm

51Testing软件测试网:b+rZf+j7q ?

主键建立聚簇索引;
r#m2q'T4SA0主键不应该被修改;
!f)Gw,ATo\i/H0字符串不应该做主键;51Testing软件测试网6j F3LH3o3Q
如果不指定主键,innodb会使用唯一且非空值索引代替;

%I,Er2E7^Jz!P051Testing软件测试网[7h_*a9y;{(x#@k

16、不用外键51Testing软件测试网$In8cX[Q]0I

51Testing软件测试网^O3O}*A5?9|q

请由程序保证约束;

Xx7GH9kN&}(D051Testing软件测试网 Y7r,qNx:T


z#K"o~X%c/j.U'jM0
51Testing软件测试网p'L fd._? z)k K

`"_X7k;Z8I8G0
)\ HJ m'I)nBi-@4f!Z0
51Testing软件测试网#E8tlu-^']'fcm

I/j7x;\&z^a&p0sql类军规51Testing软件测试网4o9L%n G5GF;O L"I(v,wp

51Testing软件测试网]0w Q3|R0EI4l7b`

17、sql语句尽可能简单51Testing软件测试网 K3S*{&TG/\ U3dfkm

51Testing软件测试网$I8Z5o;v-c Z7Nj

一条sql只能在一个cpu运算;51Testing软件测试网)Z V X-u%q%LLw h LZ
大语句拆小语句,减少锁时间;
9mfF:ey0一条大sql可以堵死整个库;
51Testing软件测试网 R-DL2T6v,y vI

51Testing软件测试网/} i U;?_[J;h

18、简单的事务

F*nD/bt/TZ1J!W J'ih8x051Testing软件测试网HS[cW"E:[^Y I Z

事务时间尽可能短;51Testing软件测试网*l%SCQC8o-pR'aTF

51Testing软件测试网4N"B^j,Y

19、避免使用trig/func

kb_0p&B#\-ijF051Testing软件测试网ojI2FW ap%Z lp

触发器、函数不用;51Testing软件测试网;Aj)@BdhYL
客户端程序取而代之;

5D hn!\ n J'zA |0

.YvQy Qq4M020、不用select *51Testing软件测试网0z uSBK5pq rt

51Testing软件测试网u[;Y5~kMGD ]'s

消耗cpu,io,内存,带宽;
Q0I,~KU_ {+l!^1_2t0这种程序不具有扩展性;

e9K(`c `0

J)BbuU021、OR改写为IN()

(R` lM0Iy l-G }051Testing软件测试网4W7wMI MycR%?;l Y

or的效率是n级别;
k V|Le|8qeo0in的消息时log(n)级别;51Testing软件测试网y0`eU\r
in的个数建议控制在200以内;
51Testing软件测试网P&?!Z$llH \Y

selectidfromtwherephone=’159′ or phone=’136′;

 =>selectidfromtwherephonein(159′, ’136);

'`'is1TWs*mtZ022、OR改写为UNION

&xs3v0o!S ik;I3A0

9VR'~]'D*K!@0mysql的索引合并很弱智51Testing软件测试网&G1`}'A]+i5z

selectidfromtwherephone = ’159′ or name = ‘john’;

 =>selectidfromtwherephone=’159′

 unionselectidfromtwherename=’jonh’

-Z B!S)]hK4Ri Fv023、避免负向%

D ]R e@!b9N+sp0

fuu1gPTQ4f$jo024、慎用count(*)51Testing软件测试网t,| J(~ P m

6]z)~5{ n4Z7afkuCQ025、limit高效分页51Testing软件测试网w `1L@#]f

51Testing软件测试网 N`Q Qh

limit越大,效率越低51Testing软件测试网(p"|-I:I2D4mT

selectidfromt limit10000,10;

=>selectidfromtwhereid >10000limit10;

0C)rOeE/g7S S0

E\Loh\\ R3e026、使用union all替代union

P%d BH}j(G051Testing软件测试网8og |5]/Bo

union有去重开销51Testing软件测试网TYn#Y B

51Testing软件测试网 UC9F?cmB


*x'?C']|0
51Testing软件测试网3s;NH\5|p N1`

$yuf_KW%z1e$fkg027、少用连接join

&e1~\Z^.S|aO!`!L051Testing软件测试网H7sq`Sba


&}3R ]K-M%yT0
51Testing软件测试网.Ft[3y'l}$o&L

$NyV aBAv028、少用group by

h^1] Re/A'B5b051Testing软件测试网5a q#c5ju-[2f

分组;51Testing软件测试网ru7\I~^:L3Y
自动排序;
51Testing软件测试网#d!HMtd_$z|T3],X

LB|{iX%R S1f;G051Testing软件测试网[ wMw`x^W x

+Pw!DA0Fl0

.I g_)iQ'yWg029、请使用同类型比较

H7S%ve[0

(h*\d.[7x0
a |0r} Z p{7F0
51Testing软件测试网cbY1z;@2h

51Testing软件测试网0T C gqh&g6g'Q^

30、使用load data导数据

l)ygi&V7?s051Testing软件测试网,i0Y.O2R(l

load data比insert快约20倍;

`KU:~._*T051Testing软件测试网}?W,~zehd


5x#Z)lh p:cf0

jnX2q I V{ o?(F0

(i2T?FWl#`:[031、打散批量更新

0g/dtoUJG051Testing软件测试网6W TLR/zY^h


*nBj-~c0

OE8YJ$BWm(i1f-N051Testing软件测试网0B/`*u#B]Yp o

32、新能分析工具

J*k{$S S,s o {0
showprofile;mysqlsla;

mysqldumpslow;explain;showslowlog;showprocesslist;showquery_response_time(percona);
51Testing软件测试网OsP ]+HM'y x|Y

h4f4pfbZ*JJ0

1@JR5_n3i0此文转载自网络;
51Testing软件测试网,?(OcW$jv.o
文/小程故事多(简书作者)
2a4r6E3i[1?9n:iP!c0原文链接:http://www.jianshu.com/p/b7849d3f869851Testing软件测试网Mm |'uQ0bz'[;E

3Bu[;Nd,YL1v3h0

TAG: 数据库 MySQL mysql

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar