坐标深圳,测试经理,干了十年测试,公号"简尚" ,博客isTester.com ,关注「软件测试从业者综合能力提升 & 职场人每日进阶」,个人微信957863300

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

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

51Testing软件测试网^J-NOZ!E+H
51Testing软件测试网 `H V5B"b _+B b9]*`E

写在前面的话:51Testing软件测试网K3L }R&q3JwR.@7qV

51Testing软件测试网f ogS$g_

总是在灾难发生后,才想起容灾的重要性;

^)H4pi x:J CMk^8r0

)H7CL?0Q0总是在吃过亏后,才记得曾经有人提醒过。

? N$y`&i051Testing软件测试网uB.?j'g-J!b{


s0NX&D7o0

o&CKx6_f4u+[J0
51Testing软件测试网M$pj2m c,j2P6s

核心军规

1q#pYh3P5PX7vU/o"~0

t\XE z ]W3T0T01、不在数据库做运算51Testing软件测试网)| h$f%k B
cpu计算务必移至业务层
51Testing软件测试网q$e;H+Y,p)K

51Testing软件测试网T!l5Y0S0S/Ed6V

2、控制单表数据量
9syo:u'D3} ek H0int型不超过1000w,含char则不超过500w;
8K@ e Eu'b.b+L9R8}X0合理分表;
!jQ2?'_,`|%k#G{C0限制单库表数量在300以内;

j1^SeE'p0Cf0

)|"NE|KAeqG03、控制列数量51Testing软件测试网VI!ZA'YIx
字段少而精,字段数建议在20以内;

;aOp0{5|051Testing软件测试网-Ky5z(H$yMU Ba

4、平衡范式与冗余51Testing软件测试网 uEQu9u2D|9qa
效率优先;
5p'I$T&^ O}'p0往往牺牲范式;
51Testing软件测试网rA3X9|7n5u }~

TU%j;I(`3d6E4u05、拒绝3B51Testing软件测试网,z]b p}f*})` }

51Testing软件测试网xjV6K IxH

拒绝大sql语句:big sql
0H$~-z%DZ+?&^&J0拒绝大事务:big transaction51Testing软件测试网CR%lm-l;WYQ
拒绝大批量:big batch
51Testing软件测试网/ak"k~!i~.P

Z"z!lI*p9tH0
df.I/mb EDnF4d0

8n#mL{M HD9s3i$]"S y0

fZ D|+wj0
.kqO7\Ys0

4b-|b9X[Ow0

'UV4fCD2b m0字段类军规51Testing软件测试网 y7STy,ao!z^8?

51Testing软件测试网3}0Kt"Mq L8r

6、用好数值类型

h)qb:_0q+^Cd'\H0

TqM]S1y0tinyint(1Byte)
Of@$c7qX#G5E0smallint(2Byte)51Testing软件测试网X2xX?.a]M L
mediumint(3Byte)
_ uZ W A4E,d0int(4Byte)
!^.I;HuiYv i:Q:h+_0bigint(8Byte)51Testing软件测试网*Rf6[eR(]8W }}
bad case:int(1)/int(11)
51Testing软件测试网6w7Jh2s-HFLY

)L y6`[9IT07、字符转化为数字51Testing软件测试网4gVh'j D8I4z Gu

Tz,I L)Wknt0用int而不是char(15)存储ip

/O m%eT],l8rx2Bx0

ZkWNT9ZI T08、优先使用enum或set51Testing软件测试网 k X`2yg/Hf

51Testing软件测试网)GW ?)m-T

例如:sexenum (‘F’, ‘M’)51Testing软件测试网J\ QY `PLh

51Testing软件测试网!x@%Xzk!S9\6C6OY1H

51Testing软件测试网!bIapv+P!ZnTC
51Testing软件测试网\!_GUp\zUG

i;oB,J?5}Uo09、避免使用NULL字段51Testing软件测试网i8\f R RT,J

51Testing软件测试网?B[[]

NULL字段很难查询优化;51Testing软件测试网QLm5J%S/Zp1}
NULL字段的索引需要额外空间;51Testing软件测试网nH t2Cx!~x1r
NULL字段的复合索引无效;
51Testing软件测试网)y{ b$A8v[I;A

badcase'name'char(32)defaultnull'age'intnotnullgoodcase'age'intnotnulldefault0

'o){,]Z vlU010、少用text/blob51Testing软件测试网Rtfsu W

~"k Yd9Uh1@0varchar的性能会比text高很多;
nX4TG,q:M0实在避免不了blob,请拆表;
51Testing软件测试网)h~ZO-mu

f!^ yu}\&S9C'`#D011、不在数据库里存图片51Testing软件测试网"s zr o5Bu0R

51Testing软件测试网&t/]Q9A0L:O B


6G4s*D;}T0
51Testing软件测试网-\"l-H'Zj~#|:yYS m P

51Testing软件测试网1v!o1U v!v


d+o~a$\ |'S(|-E/I0

Sd)?:`j*R9? KJ0
51Testing软件测试网G'H;v&t6O

索引类军规

A E-PN8yZ,G0

x ? @.d+t1w%z,|012、谨慎合理使用索引51Testing软件测试网+N g$T] D W D {

t%Fe yt]0改善查询、减慢更新;51Testing软件测试网I5Br?EMd
索引一定不是越多越好(能不加就不加,要加的一定得加);
[R7|_2r2sM0覆盖记录条数过多不适合建索引,例如“性别”;

&aV2j7FieX/u3Z051Testing软件测试网yDfh C*qz z9W

13、字符字段必须建前缀索引

:]8X(BL8Kw*L4X$H-c0

#V!D,r8M!a\+g014、不在索引做列运算

\YaRyTn3Im0
badcaseselectidwhereage +1=10;

bV9S3VBk:_:B015、innodb主键推荐使用自增列;

4[8g(X^#ZI"~4]w051Testing软件测试网#h|*J0TM1~'q

主键建立聚簇索引;51Testing软件测试网T;isBc rf
主键不应该被修改;
.iubb"QB0]0字符串不应该做主键;51Testing软件测试网NUZ SZ k8D
如果不指定主键,innodb会使用唯一且非空值索引代替;
51Testing软件测试网nr#?:uH-z!N

e!VoIm3s ~ VC016、不用外键51Testing软件测试网N;]-tN ]G3@/YB

51Testing软件测试网 _+@2N+B)r"O O o

请由程序保证约束;51Testing软件测试网km!{$lN?'IS

b _0S0U8YH+|.|051Testing软件测试网@]QQcD$B
51Testing软件测试网_ Q N mU#Y {

51Testing软件测试网zM4M)FDL9w

51Testing软件测试网}#~L:T Uzh

;P6ey8m \0
51Testing软件测试网3L;xKPP/N;L8^'EL#bG&M

sql类军规

9h|0eF+?7{ }'@s0

0bEq;^Hm@017、sql语句尽可能简单51Testing软件测试网 D$H,?,AT

o}(Po/Ju^A0一条sql只能在一个cpu运算;
;ph@@B+f%q!^0大语句拆小语句,减少锁时间;
^-@}3P{zV$^0一条大sql可以堵死整个库;

z5^ G7PPg051Testing软件测试网;A Oe]3a'T#K7kw

18、简单的事务51Testing软件测试网{q]:@1R#W;Vz'Z

F4e7Oi(j]?5O3l!w0事务时间尽可能短;

&n~BHnp~*v051Testing软件测试网pj7`9_cr6ZC

19、避免使用trig/func

M\1af(C0

tP^CM tG4b0触发器、函数不用;
JTc GJ+Z ]0客户端程序取而代之;
51Testing软件测试网$u#hQt,L)MT }

cgL{ {5J020、不用select *51Testing软件测试网'{Rq.F;A{l#E)P(L0g

51Testing软件测试网 h']X)Z8m\q

消耗cpu,io,内存,带宽;
G{W5D7h/X8p0这种程序不具有扩展性;
51Testing软件测试网/Czbei4c]

51Testing软件测试网,KM!ghzj.U$cm

21、OR改写为IN()51Testing软件测试网#]a3R r:sz"k.iZl

*n I6e,oH0or的效率是n级别;51Testing软件测试网v v*E@yR3Sjz
in的消息时log(n)级别;51Testing软件测试网&up my,c-H&l
in的个数建议控制在200以内;

D4a?Ip-e C7J `-w6c0
selectidfromtwherephone=’159′ or phone=’136′;

 =>selectidfromtwherephonein(159′, ’136);
51Testing软件测试网uLEE6T$]j fPj

22、OR改写为UNION51Testing软件测试网,v.G#r(w5cF

Q!mV6M(h|.@ go(M0mysql的索引合并很弱智51Testing软件测试网(i*q^2]7RkHjb

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

 =>selectidfromtwherephone=’159′

 unionselectidfromtwherename=’jonh’

kuB}6T2|#M"v[ q023、避免负向%

$J,ja2L HR0

'l.~ av)b(M024、慎用count(*)51Testing软件测试网 G Y+_`.l3DV

51Testing软件测试网? V q2Kv9H RM

25、limit高效分页

hn)to-a5j1U8@9a0

MSub'pj`_:y}0limit越大,效率越低51Testing软件测试网e!VW.B,T ot%Xe g

selectidfromt limit10000,10;

=>selectidfromtwhereid >10000limit10;

B*`.},|Xl$dn:B3~0

AV'fy.O?G026、使用union all替代union

1D mr f`?0

6n EJ`V }%n0union有去重开销51Testing软件测试网 ^0p0X2a6mx

51Testing软件测试网L]H8G!FfZ,X


"fc{J3P1L y0

a[+x}_"e/t u0

O%Y5U0zp%E,LR027、少用连接join51Testing软件测试网:rM{$\:b8x W)L

3C8sB}H-t0
;goF!IS0
51Testing软件测试网5A qp8ZQ4G \M

51Testing软件测试网&j8\ j^z@z

28、少用group by51Testing软件测试网1ING0p'h]

9R S+y{5Q-LN'm0分组;
K2p;h'K7k0自动排序;

vm9GOggj0

~ PS G"^:H9F0
#V%D}_'k N6b!F6Q0

'F-b+pBuM-v&KT/w,v1Z051Testing软件测试网*F%o"nh4k W|"|;d

29、请使用同类型比较

;@%H-H:A1UpH&a0

+t(c B[h5D0
^ v;s!T X"XBO%I;B?0
51Testing软件测试网0V8@DQ.r%U$F:J0Tb

$b,BH;| K aK9\R Fx030、使用load data导数据

X9C+Dw{ ^+M0

;M#ka#D6^&kh.PP0load data比insert快约20倍;

n]2m6r:W AXR0yU0

EmBIe8?%Pu?051Testing软件测试网_ezGq |A
51Testing软件测试网|w*b"{X6H8\MfW

51Testing软件测试网!uT|Skc

31、打散批量更新51Testing软件测试网5m%j5B ubY

(Jw UR {a `051Testing软件测试网J]$F^ Q5S&R$R

cC`l3o(Y"B1P;A051Testing软件测试网*l` E~$G:|-a$ux]1a7i

32、新能分析工具51Testing软件测试网;ns/M.Ik

showprofile;mysqlsla;

mysqldumpslow;explain;showslowlog;showprocesslist;showquery_response_time(percona);

(OO1X;@@D1Jc0

L8T:t%kS o5dq0
51Testing软件测试网8T5b1IS2Aq
此文转载自网络;
51Testing软件测试网`W(z$r p2?Y _
文/小程故事多(简书作者)51Testing软件测试网5Q&KUW*C v ajp
原文链接:http://www.jianshu.com/p/b7849d3f8698
9J"S)XqtU&_0

GO`:l@0

TAG: 数据库 MySQL mysql

 

评分:0

我来说两句

显示全部

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

Open Toolbar