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

(sO;]GNj:U3l0写在前面的话：51Testing软件测试网]q.NY0Z+G5E
?7{Z

总是在灾难发生后，才想起容灾的重要性；51Testing软件测试网#dL"W6kc!CtJ(c

总是在吃过亏后，才记得曾经有人提醒过。51Testing软件测试网}#S~R[V

7Of \Gn(H0
+J3\.n])~+PV#I0

51Testing软件测试网6w&B/KzL%Rimvx

核心军规51Testing软件测试网5qj
_;IAt

0]]8tUH,i)P(v3ge01、不在数据库做运算51Testing软件测试网"|+d ~
a'wlT
cpu计算务必移至业务层51Testing软件测试网h }j:Mf J

;yrV uW FwY02、控制单表数据量51Testing软件测试网,Kg0aG'm+]%L1wsL
b
int型不超过1000w，含char则不超过500w；
Z8iB3{jw0合理分表；
ML7F`1},S%aPGy0限制单库表数量在300以内；

3、控制列数量
0Jh7f!Mk7KK7IFWT0字段少而精，字段数建议在20以内；

4、平衡范式与冗余51Testing软件测试网\9M;{)x#q \F
效率优先；51Testing软件测试网]mAt-RM,y$[
往往牺牲范式；

5、拒绝3B51Testing软件测试网J:w!VQ!EQ

拒绝大sql语句：big sql
%r+of,Y%O} yb0拒绝大事务：big transaction
S){sB#DPU(o e'CV0拒绝大批量：big batch

B6roR1B*iL l)B.s051Testing软件测试网S_T-J*? t

G}8zIlKYTf051Testing软件测试网u/IKn[q

51Testing软件测试网CN.HnD0m

字段类军规

&TC&G-Yp wY%@+\0

.{H;}:xSK!Z_Ul5I6J06、用好数值类型

tinyint(1Byte)
BZF O4} i0smallint(2Byte)51Testing软件测试网9f S-G KF;j#n]6G
mediumint(3Byte)
"^8f$[{a5Fw"M*N myN0int(4Byte)51Testing软件测试网2D*n9vq x&h(b
bigint(8Byte)51Testing软件测试网9t#C3n7M6~5}Eb
bad case：int(1)/int(11)

`h`'@,yMj
H;[U07、字符转化为数字51Testing软件测试网f}kC @}1VNq!N

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

8、优先使用enum或set

例如：sexenum (‘F’, ‘M’)

;H8scY(y#f${xv1l.C0

al9|)CLgc8b09、避免使用NULL字段

NULL字段很难查询优化；51Testing软件测试网'L?*Wl*rw*u
NULL字段的索引需要额外空间；
/omfUb`8_0NULL字段的复合索引无效；

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

k'Z0?
C&EH+E
]
V010、少用text/blob51Testing软件测试网t1l"b Dd5k
] ^

4| uz.ieL'K(YX0varchar的性能会比text高很多；51Testing软件测试网6JJ.x9]'y.s(j
实在避免不了blob，请拆表；

8u
M.u
C&kC Le011、不在数据库里存图片

u `$Am.Bp3@051Testing软件测试网1?Q%O} a,J;u
51Testing软件测试网 N&[2Yh-Mf

3t J7\0f#V9a051Testing软件测试网It&mb9t!{

51Testing软件测试网.c5i0AC4gk7Bq

索引类军规

5W%Fla4cnG:oWs0

8M2ss4d
w3m2vE012、谨慎合理使用索引

改善查询、减慢更新；
gp2Z$w w0索引一定不是越多越好(能不加就不加，要加的一定得加)；
q8y9Tr9\0覆盖记录条数过多不适合建索引，例如“性别”；51Testing软件测试网K$t;J"K d^'tT7L

13、字符字段必须建前缀索引51Testing软件测试网-GJ+GQEQdq

6Noi5Ho!q5sg014、不在索引做列运算

badcase：selectidwhereage +1=10;

15、innodb主键推荐使用自增列；51Testing软件测试网e?,C5E.|!?n}

kd+Fy@6L(X0主键建立聚簇索引；51Testing软件测试网!a#W.m7JLYDm
主键不应该被修改；51Testing软件测试网5IVU'UTJXH
字符串不应该做主键；51Testing软件测试网4Y%c `0K'\mm
如果不指定主键，innodb会使用唯一且非空值索引代替；51Testing软件测试网N*h"zlt8S A

16、不用外键

请由程序保证约束；51Testing软件测试网e;_2XvS

51Testing软件测试网'S&\+y5b%]%l8A

;Z&k5w[6?N)W8n0

51Testing软件测试网b3JX5E#z:b

sql类军规

6i#lVd-x!F"H0

17、sql语句尽可能简单

一条sql只能在一个cpu运算；51Testing软件测试网t-zd7M1U%oV
大语句拆小语句，减少锁时间；
8S1Ey2C'MIOs_ t0一条大sql可以堵死整个库；

K`Cm0CSs?xkv018、简单的事务

事务时间尽可能短；51Testing软件测试网/f-y,_ DT"rFQ

;N8g0atPi-|L019、避免使用trig/func51Testing软件测试网O`[/Gtyu#y!k.Gk

触发器、函数不用；51Testing软件测试网-{2jCCzx'a!o0c
客户端程序取而代之；

g#M;r)l:k+ac020、不用select *51Testing软件测试网`;}7w$}+M)X

yw j:^{wv0消耗cpu，io，内存，带宽；
tu:Z+cSM_K0这种程序不具有扩展性；51Testing软件测试网8Q1P:a7EHKr,a%K)U,H

21、OR改写为IN()

8g,QqQg-G+`'@M0or的效率是n级别；
Li7f1zOKJ0in的消息时log(n)级别；
{)n)^2UI)Nn0in的个数建议控制在200以内；

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

 =>selectidfromtwherephonein(’159′, ’136′);

22、OR改写为UNION51Testing软件测试网-@enj:o

mysql的索引合并很弱智

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

 =>selectidfromtwherephone=’159′

 unionselectidfromtwherename=’jonh’

o0We&V:]ij023、避免负向%51Testing软件测试网9Q:J-lN;M+C?

24、慎用count(*)51Testing软件测试网cT"E,fEz.D

25、limit高效分页

limit越大，效率越低

selectidfromt limit10000,10;

=>selectidfromtwhereid >10000limit10;

(W&n4|
A)Zm0

26、使用union all替代union

$i:r ~L!_4Db;i z-rL0union有去重开销

T&Q5}~:ouk#Ju7I051Testing软件测试网uWR!y0](x0DR
51Testing软件测试网aCy Yj+r v\]#C

27、少用连接join51Testing软件测试网v D)j/d7w&?dE

.e [2my._[%s['m0

3x%V9zbQ028、少用group by51Testing软件测试网~L8ME5Ci#F

0E8[8N-YN0分组；
|kA&u%r.{*X1V0V0自动排序；

6u}8E%a%BS|0

V1m[5H w"J029、请使用同类型比较

+?e2PN\X]s1t0

30、使用load data导数据51Testing软件测试网@iqS
D]5tJs`'f

load data比insert快约20倍；51Testing软件测试网[!^4o6W.k

wR'P mZd0
(o5d*gYc1z051Testing软件测试网Nc_YcI qf}

31、打散批量更新51Testing软件测试网 n n5Z} U G

$u6{ql2]0

32、新能分析工具

showprofile;mysqlsla;

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