isTester.com发起人,某互联网公司测试经理,十年测试经验,公众号“简尚”; 致力于软件测试从业者综合能力提升 & 职场人每日进阶。 微信isTester 》》博客文章未标注转载均属「原创」,转载找老徐授权;

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

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


8R9Jy)g'k0
51Testing软件测试网"i\_4Cq~w

写在前面的话:51Testing软件测试网]P DwS6dw`

DN [f"j1t_]0总是在灾难发生后,才想起容灾的重要性;

3N2r T3]1h8D051Testing软件测试网r i-WT;C+a-a

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

vm1p:es_0

/|4R r ]n0
)P4Is#D!AQw0
51Testing软件测试网!P,e|`]o

51Testing软件测试网7i*VC kd?%V

核心军规51Testing软件测试网Sw!M(G1M7?8y J0L

51Testing软件测试网+pn:oWEHY j

1、不在数据库做运算51Testing软件测试网ta-ApN-O(uI"\+t
cpu计算务必移至业务层
51Testing软件测试网eC{)B9t}!|

51Testing软件测试网HP VI QS

2、控制单表数据量
Q;G!n8`qex0int型不超过1000w,含char则不超过500w;51Testing软件测试网F0FB%a QE
合理分表;
~~5K|7F"G0限制单库表数量在300以内;

6g-C6w9Us4s051Testing软件测试网O8m1m!v0p,G y

3、控制列数量51Testing软件测试网8vT{'r&pY~
字段少而精,字段数建议在20以内;

jiEj}FAl$U0

7O:X N-i#vL D9T8D*L1I _04、平衡范式与冗余51Testing软件测试网 c\u/W7u9Fp([&q+J y$t
效率优先;
CtF o-l4B%zQ w0往往牺牲范式;
51Testing软件测试网 `}q%Z$X1qx!`

#px-\-EaeXd&D"}e[ f05、拒绝3B51Testing软件测试网 n cgT*i.u/kFm

51Testing软件测试网J5r5~Jr,G4NFf

拒绝大sql语句:big sql
H1f9[BUp0拒绝大事务:big transaction
t)Z U'}nZ0拒绝大批量:big batch

^7D6L(jIZ&?+Yb6J0

:F+Km)TV'{k0
%I Y\^}0r+R0
51Testing软件测试网 B5{4hx)Dv'B A9t+t

51Testing软件测试网;b#k-@3B,A1T_Q


S7g4k-{e0
51Testing软件测试网F9|N$`$ONe$a

0C&W3}$APK$m D7w0字段类军规

Kk5]"n:w}!{?5N7g0

'eT H?ee06、用好数值类型51Testing软件测试网x'P@'FBc|

F DaOF0tinyint(1Byte)
2U;gU7i[g&c3x:p0smallint(2Byte)51Testing软件测试网G I ]rO9nq mk@p)l
mediumint(3Byte)
4z%D'| s[.I0@ H z0int(4Byte)51Testing软件测试网T._K1t&v uM#~
bigint(8Byte)
Pu(Q a6\:nH0bad case:int(1)/int(11)
51Testing软件测试网*h(q h6NN:` b

yLr {|(|;q!\%vY07、字符转化为数字

{Nd8c#e0

5I3wy+\ CN0用int而不是char(15)存储ip51Testing软件测试网 {!W._`T6L

51Testing软件测试网/]"fdg9\*@d:g"n

8、优先使用enum或set

T:hv"E.F(CC1As9ZP051Testing软件测试网7Q$TB;jF6p

例如:sexenum (‘F’, ‘M’)51Testing软件测试网 i{)O[ A{_

51Testing软件测试网C4d"Lefma"T A


x |9^!opa-vo9u-kH0

u^nkY#{'L8MSn051Testing软件测试网 rz7oKN'UQ$|

9、避免使用NULL字段

p,L_8Rmq&Qt051Testing软件测试网 g$DE*fJy g"JR

NULL字段很难查询优化;
^}7n)_!w#R UC0NULL字段的索引需要额外空间;51Testing软件测试网z+Jc(mp)M%x5[-?
NULL字段的复合索引无效;
51Testing软件测试网-I-~Tq\4_

badcase'name'char(32)defaultnull'age'intnotnullgoodcase'age'intnotnulldefault0
51Testing软件测试网^1L(R7B;y4S(e

10、少用text/blob

8JY4H$n.i`Zm-h051Testing软件测试网n DS-l)Vl&wk

varchar的性能会比text高很多;
niYc1f1tOY0实在避免不了blob,请拆表;
51Testing软件测试网"]1w#ai6kS7{*LoG

51Testing软件测试网is9z%C m

11、不在数据库里存图片

5F]ke4@m O4@0

0VuR$@$aoRfW s4U0
T5q6or-`,u[Wm0

wCnk4Y ei0

/L#? m7rkQDs(@051Testing软件测试网dXp|-I K0p

8bg3g!Nu0

5S os|Q%i`0索引类军规51Testing软件测试网:]j5|8g+D-Hu)u;Xb/Ch

51Testing软件测试网 O B#v%`u^&G

12、谨慎合理使用索引51Testing软件测试网{+dl%O#W|0cj

51Testing软件测试网cc+fJY!U1{

改善查询、减慢更新;
uC4I;Th0c0@S0索引一定不是越多越好(能不加就不加,要加的一定得加);51Testing软件测试网NuK'qly
覆盖记录条数过多不适合建索引,例如“性别”;

l { Om#G0

.^9R+L#\*v%H013、字符字段必须建前缀索引51Testing软件测试网uZ3z"NM3zh

51Testing软件测试网m2N s{9r[U!\

14、不在索引做列运算

;WG ?T*H_H0
badcaseselectidwhereage +1=10;

G*\*Ho d!R015、innodb主键推荐使用自增列;

Yz~c}(hW051Testing软件测试网FO0y I,iJ

主键建立聚簇索引;51Testing软件测试网&Kw8zNE#v*UiX
主键不应该被修改;
$E:x7n4vp0字符串不应该做主键;
H/dIA"K5?O4^0如果不指定主键,innodb会使用唯一且非空值索引代替;
51Testing软件测试网D sI&J$mv;}

'Ld7E#j2Q&i JG016、不用外键

;e[ X Gc||l1f051Testing软件测试网w$C CJ{H

请由程序保证约束;51Testing软件测试网 J4U A0\V$b"wA-R

y$z9a(F3B051Testing软件测试网 i:|pduF#xI

[0Y$_P.S4Tt f0

bh"Xv$k$H b0
:_MHsv6^0
51Testing软件测试网y+~hu1`'c u~

51Testing软件测试网l NsC l:e[

sql类军规51Testing软件测试网.K&Ys7DkBYk

$n:t G6}1K s X7k-G017、sql语句尽可能简单51Testing软件测试网u+wv(M{~4|

8L*qt/zc i RZ [0Q Ic0一条sql只能在一个cpu运算;
TQE1Q1zv0大语句拆小语句,减少锁时间;51Testing软件测试网2Br*tuU5l%r/v(D6s
一条大sql可以堵死整个库;

m_JhH8z3]fC0

G_5ws}n-t$V1p018、简单的事务51Testing软件测试网#FTW {3BTx

'UT!].^oX0事务时间尽可能短;

/C9F,Xl-l6W051Testing软件测试网'U3nYVx:K%Cn

19、避免使用trig/func

O9csYO*K Hv051Testing软件测试网c*x9FV,C

触发器、函数不用;
[:C"AjlQ]b:B0客户端程序取而代之;
51Testing软件测试网Z#a J8h0K5j_ fP;gg

51Testing软件测试网s/Yur`5Xdr:R

20、不用select *51Testing软件测试网yeB:~m4ccp

S$v4R/\!EE#h0消耗cpu,io,内存,带宽;
y,kMnE| ^H0这种程序不具有扩展性;
51Testing软件测试网+`k~KPX

51Testing软件测试网 u4b ddqrU4X

21、OR改写为IN()51Testing软件测试网 L1l3f4[!e

-y Y6XJt:B5u0or的效率是n级别;51Testing软件测试网;f jHu6pzdQ
in的消息时log(n)级别;
UC j5i4e?0U&[ nE N0in的个数建议控制在200以内;
51Testing软件测试网;}:cE$u8J}0Nu

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

 =>selectidfromtwherephonein(159′, ’136);
51Testing软件测试网8k_2fVS+\x+O'}!?

22、OR改写为UNION51Testing软件测试网$C8O#_u-x uH^!m%F-`

Q;M&[rf"s$kMF#L0mysql的索引合并很弱智51Testing软件测试网(M&_IlS;LX

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

 =>selectidfromtwherephone=’159′

 unionselectidfromtwherename=’jonh’

j-dbalX2a023、避免负向%51Testing软件测试网 B.s Rm R0Tu

M)V+E8mA8`NK-n4h024、慎用count(*)

;u {;?G0C WlL3Q CBl0

'LUq*K!AE qDok025、limit高效分页51Testing软件测试网(U2\ t+tL|m

51Testing软件测试网Zs-{!dm%fj d,t0V Q;`

limit越大,效率越低

z/^w3L EWT0
selectidfromt limit10000,10;

=>selectidfromtwhereid >10000limit10;

/z0x VT r$JjXj0

{jXQL026、使用union all替代union

o9PT/K p~0

SJ k Rd,A0union有去重开销51Testing软件测试网8f6W2L,CO6k.o

%c%vQ1O^r~0
0Pu:p oFGB7l3oj0U0

gmX3Qne0

+D&c M[tWc1L027、少用连接join51Testing软件测试网?^ |3{'?a dET)r5HP-O

51Testing软件测试网o Z({Z$Q#f*T y)KK

51Testing软件测试网8t+u {Xc2w
51Testing软件测试网&U @,w,D%x)cf3M

&Pz3A AmO(S{028、少用group by

/Up'kn1_r(z0

6X&F"W3sZ0分组;
.ZC{ yT&U6H`0自动排序;
51Testing软件测试网9h C7?r0B |:h:`_6L1w

D J2B(k5I0
I-|7mw S051Testing软件测试网JSw&X Hu@

51Testing软件测试网G"l;q5UnLF F

29、请使用同类型比较51Testing软件测试网2{7rc7X*}R"X

6b:F9r1NI0OG0K9T;E051Testing软件测试网hp'sZ;` _W
51Testing软件测试网 B0jE(TM

/@w XSbo*mb030、使用load data导数据

"D`3R2[Ck6`4e_._051Testing软件测试网8rb Nlm+Q

load data比insert快约20倍;51Testing软件测试网1{2a L+c(v

5{f%Q,{ a,LY'[051Testing软件测试网`H!Q6kb/h

Y7KhX3E4\9i:VO0

L{jM6Lx;v-[031、打散批量更新51Testing软件测试网u/E{|P;Q:x

:B[)W2}im051Testing软件测试网W"Q{*x$b-u'T

)Wn;B$g$g]w0

;fy%s/x7n1]3M J032、新能分析工具

5\@f!xIU^w,S0
showprofile;mysqlsla;

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

;u)P,^9f O0

5d-h@ m#C+P K3Jm)H:o0

-I c/_4v6Lxu#UZ0此文转载自网络;

7N2u|9y0cR0
文/小程故事多(简书作者)
e+i\qO/aU0TCx0原文链接:http://www.jianshu.com/p/b7849d3f8698
){u%i"WseN&?5w0

@Jsh t?8TI0

TAG: 数据库 MySQL mysql

 

评分:0

我来说两句

显示全部

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

Open Toolbar