MySQL可以说是程序员应用最多的数据库,下面笔者为大家分享MySQL数据库开发当中的一些常识,存储引擎的选择,索引的设计及使用和大批量插入时SQL语句的优化。希望能对大家有帮助。HR%Jq#H4S0 存储引擎的选择
.@J%gWP051Testing软件测试网ha4Pl~S 声明:本文所针对的数据库版本都是MYSQL 5这里我主要针对两种存储引擎进行简单比较分别是MyISAM和InnoDB,首先比较下区别:
#E'n0x0R;Yh051Testing软件测试网|(Hd;}"wnqV$h!O*z 1、MyISAM不支持事务,不支持外键,优点是访问速度高,批量插入速度快。假设大量的操作是select、insert,建议采用该存储引擎。但是在我的实际应用中,出现过批量插入过于频繁的时候,当数据量到达一定级别,出现表损坏的情况。
2W(]^P:C4I.N0DA7eK+j:\7A n3h F q!R0 2、InnoDB支持事务处理,但是相对于前者,处理效率低一些,并且其索引及数据也更占用磁盘空间。在存储一些关键数据,并需要对其进行事务操作的时候,我们可以选择innodb,当然,我认为他不应该是访问量太大的。
!Q!D$YE7Y7E`Q051Testing软件测试网nci%Q2i^ 索引的设计及使用51Testing软件测试网UoQ2iFJC
#|
iZ e5I0 没有索引的表是恐怖的,除非里头没多少数据,但是怎么设计索引是合理的?恐怕不是所有人都明白,这里简要分析下索引的设计及使用。
U X3wNa^YBF0G0e?(i|j*i
y*I u0 1、索引通常是设置where字句中的列,如果你设置select后的列,这是没有任何意义的。当然你需要对某列进行排序,order by后的列也是可以建成索引的。51Testing软件测试网5I*WG/WIE
O
\
(}Py kN)U&b@0 2、使用唯一索引,主键就是最好的例子,假设你建的索引列,大量都是重复的,例如:性别,那么这样的索引并不会加快搜索速度。至于为什么,请大家自行了解索引的工作原理。
9i{Cr2H o@k0;^1EIL(mKtgX0O/r$v0 3、只要有可能,就要尽量限定索引的长度,例如索引列为 char(100),在其前10个字符大部分都是唯一的,请设置索引的长度为10,使用短索引可以加快查询速度,并节省硬盘空间。
lh'G&pY5k C"c051Testing软件测试网;[&R8?&F KiT
uS7| 4、索引的左前缀特性,联合索引实质上也是建立了多个的索引,那么是建立联合索引好还是分别建多个索引好呢?显然前者更好,利用左前缀特性,只要联合索引的最左的列被用到,那么索引都会被使用。
%m:]KA,O051Testing软件测试网e?t~N0yX1F 5、当然,最后要说的是,不要过度使用索引,索引越多,插入的速度越慢,尤其到数据量庞大时,同时,大量的索引将耗费很多硬盘空间,造成不必要的浪费。
Qf$Wv:KD5y6FJ051Testing软件测试网r(x_8I8L? 下面举几个列子来说明索引的使用:
-QGc_yc!z2K gt
M0Qv0]]z"F0 1、联合索引的左前缀51Testing软件测试网{4^r i+jME+^
6`n.Iba6B d0 先看索引结构:51Testing软件测试网#V5C9m"F!Z8s/l ~
51Testing软件测试网q$N5K~SC`5V'm
user是联合索引的名称,包含3个列,分别是username,order,email。接下来执行以下sql,使用explain命令来分析下运行结果。51Testing软件测试网+O+tLO:j(^(v
@9a
`U4q0 在两句sql中,我们可以发现,第一个sql虽然没用上,全部的索引列,但由于使用到了最左端的列,所以,联合索引还是启用了,第二句没有使用到最左的列,所以索引没有使用。
8gd"L7d_4i/ab4Z!C0 2、关于like关键字
K ZP"w x8yK/NA6b0"v!}b1U,\0 对于使用like的查询,需要注意的是只有列的%不在第一个字符索引才可能被使用。以下分别展示了使用like的查询,第一个是索引被使用的,第二个是索引未被使用的。51Testing软件测试网4F7}'MP,E:u
mDl
U&k%|C8]0 3、查看索引使用情况51Testing软件测试网Yp7LpCh;h
使用以下命令:51Testing软件测试网%ay*R0B:b#R*W9H
51Testing软件测试网)@ @t$h&Y$`3l%H*V|
如果索引正在工作,那么Handler_read_key 会很高,如果查询中出现Handler_read_rnd_next的值很高,则表明查询低效,索引的应用并不合理。51Testing软件测试网
Z~
y
}r
y"l)b
大批量插入时的SQL语句优化51Testing软件测试网r*L Z0l1uo
在大量插入时,尤其是并发插入时,mysql往往要承受更高的负载,使用mysql
administortar的健康检查就可以发现,其avg的值相当高,在这种情况下,首先要做的是sql语句的优化,比较下面两个句子,后者的速度比前
者要快得多。因为减少大量的连接。
$U&XV _ R9z0 复制内容到剪贴板代码:
ODd$mhh }B']!W7a051Testing软件测试网0qx Ku
A$\2~+C;|
在我的一个实际应用中,由于需要经常有数百个并发的插入,我还采用了insert delayed
into来取代insert
into,前者与后者的区别是在执行插入语句时,数据保存在内存队列中,待数据库空闲时执行,但是会立即返回一个插入成功的信息。使用insert
delayed
into时需要注意:此时不能使用mysql_insert_id(),因为此时并没有真正插入。对特别重要的数据不宜采用该语句,避免数据以外丢失。
-z
fxsw0 其他杂谈51Testing软件测试网_f3Jg&e~/lM"V
mysql myisam 表超过4G无法访问的解决
xK(J'u3T;P,s5hR0 myisam引擎默认是支持4GB,innodb理论上可以到6TB,假设单张表容量超过4GB,可能导致表都无法访问了。可以通过以下命令增加表最大数据量:51Testing软件测试网s2Dc.uw:|"bW"c
K(LCW:I*^o1ln&A0