<b>MySQL查询优化系列讲座之数据范例与效率</b>[MySQL防范]
本文“<b>MySQL查询优化系列讲座之数据范例与效率</b>[MySQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
在可以利用短数据列的时刻就不要用长的.假如你有一个固定长度的CHAR数据列,那么就不要让它的长度超越实际需求.假如你在数据列中存储的最长的值有40个字符,就不要定义成CHAR(255),而应当定义成CHAR(40).假如你可以用MEDIUMINT替换BIGINT,那么你的数据表就小一些（磁盘I/O少一些）,在计算历程中,值的处理速度也快一些.假如数据列被索引了,那么利用较短的值带来的性能提高越发明显.不但索引可以提高查询速度,并且短的索引值也比长的索引值处理起来要快一些.
假如你可以挑选数据行的存储格局,那么应当利用最合适存储引擎的那种.关于MyISAM数据表,最好利用固定长度的数据列替换可变长度的数据列.比方,让全部的字符列用CHAR范例替换VARCHAR范例.衡量得失,我们会发现数据表利用了更多的磁盘空间,但是假如你可以供应额外的空间,那么固定长度的数据行被处理的速度比可变长度的数据行要快一些.关于那些被频繁改正的表来说,这一点特别突出,因为在那些情形下,性能更简单遭到磁盘碎片的影响.
· 在利用可变长度的数据行的时刻,由于记录长度差别,在多次履行删除和更新操作之后,数据表的碎片要多一些.你必须利用OPTIMIZE TABLE来按期保护其性能.固定长度的数据行没有这个问题.
· 假如呈现数据表崩溃的情形,那么数据行长度固定的表更简单重新构造.利用固定长度数据行的时刻,每个记录的开始位置都可以被检测到,因为这些位置都是固定记录长度的倍数,但是利用可变长度数据行的时刻就不一定了.这不是与查询处理的性能相关的问题,但是它一定可以加快数据表的修复速度.
固然把MyISAM数据表转换成利用固定长度的数据列可以提高性能,但是你首先需求考虑下面一些问题:
· 固定长度的数据列速度较快,但是占用的空间也较大.CHAR(n)列的每个值（即便是空值）普通占n个字符,这是因为把它存储到数据表中的时刻,会在值的背面增添空格.VARCHAR(n)列占据的空间较小,因为只需求分配必要的字符个数用于存储值,加上一两个字节来存储值的长度.因此,在CHAR和VARCHAR列之间举行挑选的时刻,实际上是时间与空间的比较.假如速度是主要的考虑因素,那么就利用CHAR数据列获得固定长度列的性能上风.假如空间很重要,那么就利用VARCHAR数据列.总而言之,你可以认为固定长度的数据行可以提高性能,固然它占用了更大的空间.但是关于某些特别的利用程序,你大概但愿利用两种方法来实现某个数据表,然后运行测试来决意哪类情形符合利用程序的需求.
· 即便乐意利用固定长度范例,有时刻你也没有办法利用.比方,擅长255个字符的字符串就无法利用固定长度范例.
MEMORY数据表目前都利用固定长度的数据行存储,因此无论利用CHAR或VARCHAR列都没有关系.二者都是作为CHAR范例处理的.
关于InnoDB数据表,内部的行存储格局没有辨别固定长度和可变长度列（全部数据行都利用指向数据列值的头指针）,因此在本质上,利用固定长度的CHAR列不一定比利用可变长度VARCHAR列简单.因而,主要的性能因素是数据行利用的存储总量.由于CHAR平均占用的空间多于VARCHAR,因此利用VARCHAR来最小化需求处理的数据行的存储总量和磁盘I/O是对比好的.
关于BDB数据表,无论利用固定长度或可变长度的数据列,差别都不大.两种办法你都可用试一下,运行一些实行测试来检测能否存在明显的差别.
把数据列定义成不能为空（NOT NULL）.这会使处理速度更快,需求的存储更少.它有时刻还简化了查询,因为在某些情形下你不需求查抄值的NULL属性.
考虑利用ENUM数据列.假如你拥有的某个数据列的基数很低（包含的差别的值数目有限）,那么可以考虑把它转换为ENUM列.ENUM值可以被更快地处理,因为它们在内部表现为数值.
利用PROCEDURE ANALYSE().运行PROCEDURE ANALYSE()可以看到数据表中列的情形:
| SELECT * FROM tbl_name PROCEDURE ANALYSE(); SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256); |
输出的每一列信息城市对数据表中的列的数据范例提出优化倡议.第二个例子奉告PROCEDURE ANALYSE()不要为那些包含的值多于16个大概256字节的ENUM范例提出倡议.假如没有这样的限制,输出信息大概很长;ENUM定义普通很难阅读.
[1] [2] [3] [4] 下一页
按照的PROCEDURE ANALYSE()输出信息,你大概发现,可以改正自己的数据表来操纵那些效率更高的数据范例.假如你决意改变某个数据列的范例,需求利用ALTER TABLE语句.
利用OPTIMIZE TABLE来优化那些遭到碎片影响的数据表.被大量改正的数据表,分外是那些包含可变长度数据列的表,简单蒙受碎片的影响.碎片很糟糕,因为它会招致用于存储数据表的磁盘块形成无用空间（空洞）.随着时间的推移,为了得到有效的数据行,你必须读取更多的块,性能就会降低.这会呈目前任何可变长度的数据行上,但是关于BLOB或TEXT数据列特别突出,因为它们的长度差别太大了.在正常情形下利用OPTIMIZE TABLE会避免数据表的性能降低.OPTIMIZE TABLE可以用于MyISAM和BDB数据表,但是defragments只能用于MyISAM数据表.任何存储引擎中的碎片整理办法都是用MySQLdump来转储（dump）数据表,接着利用转储的文件删除并重新成立那些数据表:
| % mysqldump --opt db_name tbl_name > dump.sql % mysql db_name < dump.sql |
把数据打包放入BLOB或TEXT数据列.利用BLOB或TEXT数据列存储打包（pack）的数据,并在利用程序中举行解包（unpack）,使你可以在一次检索操作中得到需求的任何信息,而不需求举行多次检索.它对那些很难用尺度的数据表构造表现的数据值和频繁改变的数据值也是有帮忙的.
办理这个问题的另一种办法是让那些处理Web窗体的利用程序把数据打包成某种数据构造,然后把它插入到单个BLOB或TEXT数据列中.比方,你可以利用XML表示调查表答复,把那些XML字符串存储在TEXT数据列中.由于要对数据举行编码（从数据表中检索数据的时刻还需求解码）,它会增添客户端的开销,但是可以简化数据构造,并且它还消除了那些因为改变了调查表的内容而必须改变数据表构造的需求.
另一方面,BLOB和TEXT值也会惹起自己的一些问题,分外是履行了大量的删除或更新操作的时刻.删除这种值会在数据表中留下很大的"空洞",今后填入这些"空洞"的记录大概长度差别（前面谈论的OPTIMIZE TABLE提出办理这个问题的一些倡议）.
利用合成的（synthetic）索引.合成的索引列在某些时刻是有效的.一种办法是按照别的的列的内容成立一个散列值,并把这个值存储在单独的数据列中.接下来你便可以通过检索散列值找到数据行了.但是,我们要注意这种技术只能用于切确匹配的查询（散列值关于近似<或>=等范围搜索操作符是没有效处的）.我们可以利用MD5()函数生成散列值,也可以利用SHA1()或CRC32(),大概利用自己的利用程序逻辑来计算散列值.请记着数值型散列值可以很高效率地存储.一样,假如散列算法生成的字符串带有尾部空格,就不要把它们存储在CHAR或VARCHAR列中,它们会遭到尾部空格去除的影响.
合成的散列索引关于那些BLOB或TEXT数据列分外有效.用散列标识符值查找的速度比搜索BLOB列本身的速度快很多.
在不必要的时刻避免检索大型的BLOB或TEXT值.比方,SELECT *查询就不是很好的设法,除非你可以肯定作为约束条件的WHERE子句只会找到所需求的数据行.不然,你大概毫无目的地在网络上传输大量的值.这也是BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮忙的例子.你可以搜索索引列,决意那些需求的数据行,然后从合格的数据行中检索BLOB或TEXT值.
把BLOB或TEXT列别离到单独的表中.在某些环境中,假如把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格局,那么它就是有意义的.这会削减主表中的碎片,使你得到固定长度数据行的性能上风.它还使你在主数据表上运行SELECT *查询的时刻不会通过网络传输大量的BLOB或TEXT值.
上一页 [1] [2] [3] [4] 下一页
高效率地载入数据
在大大都情形下,你所关注的是SELECT查询的优化,因为SELECT查询是最常见的查询范例,并且若何优化它们又不是太简单.与此形成比较,把数据载入数据库的操作就相对直接了.但是,你仍旧可以操纵某些战略来改进数据载入操作的效率.基本的原理以下所示:
· 批量载入比单行载入的效率高,因为在每条记录被载入后,键缓存（key cache）不用革新（flush）;可以在这批记录的末尾革新键缓存.键缓存革新的频率削减得越多,数据载入的速度就越快.
· 没有索引的数据表的载入速度比有索引的要快一些.假如存在索引,不但要把记录增添到数据文件中,还必须改正索引来反映新增的记录.
· 较短的SQL语句对比长的SQL语句快,因为它们所触及到服务器端解析历程较少,同时通过网络把它们从客户端发送到服务器上的速度也更快.
此中有些因素看起来是次要的（特别是最后一个）,但是假如你载入的数据很多,那么即便很小的效率差别也会招致一定的性能差别.我们可以早年面的普通原理得出几条若何快速载入数据的实践结论:
· LOAD DATA（全部情势的）比INSERT效率高,因为它是批量载入数据行的.服务器只需求解析和注释一条语句,而不是多条语句.一样,索引只需求在全部的数据行被处理过之后才革新,而不是每行革新一次.
· 不带LOCAL的LOAD DATA比带有LOCAL的LOAD DATA的速度要快.不带LOCAL的时刻,文件必须位于服务器上,并且你必须拥有FILE权限,但是服务器却可以直接从磁盘上读取文件.利用LOAD DATA LOCAL的时刻,客户端读取文件并通过网络把它发送给服务器,速度慢一些.
· 假如你必须利用INSERT,那么试着利用在一个语句中指定多个数据行的情势:
| INSERT INTO tbl_name VALUES(...),(...),... ; |
在这个语句中指定的数据行越多,效果就越好.这会削减必要的语句数目,并最小化索引革新的次数.这一条结论看起来与前面所谈论的"语句越短,履行速度越快"相冲突,但是实际上并不冲突.这儿所谈论的是同时插入多个数据行的一个INSERT语句所耗费的开销比功效相同的多个单行INSERT语句的耗费的开销要小一些,并且多行语句损耗的索引革新开销也少一些.
假如你利用mysqldump生成数据库备份文件,那么MySQL 4.1会默许地生成多行INSERT语句:它会激活--opt (优化)选项,而这个选项会激活--extended-insert选项,该选项生成多行INSERT语句,还存在别的一些选项也可以使数据被载入的时刻,转储文件被处理的效率更高.关于MySQL 4.1从前的版本,你可以明确地指定--opt或--extended-insert选项.
利用mysqldump的时刻要避免利用--complete-insert选项;它生成的INSERT语句是每个数据行一条语句的,语句总共会很长,比多行语句需求的解析操作更多.
· 假如你必须利用INSERT语句,那么在大概的情形下,对它们举行分组以削减索引的革新.关于事件性的存储引擎,在单个事件中提交,而不是在自动提交（autocommit）情势下提交INSERT语句可以实现这样的功效:
| START TRANSACTION; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; COMMIT; |
关于非事件性的存储引擎,获得数据表上的写入锁,它被锁定的时刻提交INSERT语句:
| LOCK TABLES tbl_name WRITE; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; INSERT INTO tbl_name ... ; UNLOCK TABLES; |
上一页 [1] [2] [3] [4] 下一页
项的时刻,数据行会像普通一样当即写入数据文件中,但是键缓存只是无意革新一次,而不是在每次插入操作之后都需求革新.假如要在服务器上全面地利用耽误索引革新,那么就需求利用--delay-key-write选项来启动mysqld.在这种情形下,每个数据表的索引块写入操作城市被耽误,直到这些数据块必须为别的的索引值供应空间、大概履行了FLUSH TABLES号令、大概数据表被关闭的时刻才履行操作.
假如你挑选了对MyISAM数据表利用耽误键写入,那么不正常的服务器关闭大概会惹起索引值的丧失.这不是致命的问题,因为MyISAM索引可以根据数据行来举行修复,但是假如想让修复历程呈现,你就必须利用--myisam-recover=FORCE选项来启动服务器.这个选项会使服务器在翻开MyISAM数据表的时刻查抄它们,假若有必要就自动地修复它们.
关于复制（replication）从属服务器,你大概但愿利用--delay-key-write=ALL来耽误全部的MyISAM数据表索引的革新,不管在主服务器上最初是若何成立它们的.
· 利用紧缩的客户端/服务器协议来削减网络上数据传输的数目.关于大大都MySQL客户端来说,我们都可以利用--compress号令行选项来指定它.普通,这个选项只是在较慢的网络上利用,这是因为紧缩操作会耗费大量的处理器时间.
· 让MySQL替你插入默许值.也就是说,无论若何都不要给INSERT语句中那些可以赋予默许值的列指定值.平均起来,你的语句更短,削减了通过网络发送到服务器的字符数目.此外,由于语句包含的值较少,服务器履行的解析和值转换操作也较少.
· 关于MyISAM数据表,假如你必须把大量的数据载入一个新表,最好成立不带索引的表,载入数据,然后成立索引,这样的工作次序的速度要快一些.一次性地成立索引比每行都更新索引的速度要快一些.关于已经带有索引的表,假如预先删除或禁止索引,后来再重新成立大概激活索引,那么数据载入的速度也要快一些.这些战略不能利用于InnoDB或BDB表,它们没有对别离的索引成立历程举行优化.
假如你考虑利用删除或禁止索引的战略,把数据载入MyISAM数据表,那么在评价得到的上风的时刻,就需求考虑整个环境.假如你把少量的数据载入大型的数据表中,那么在没有任何特别预备工作的情形下,重新成立索引耗费的时间大概比载入数据的时间还要长.
要删除并且重新成立索引,需求利用DROP INDEX和CREATE INDEX,大概利用与索引相关的ALTER TABLE.禁止和激活索引有两种办法:
· 你可用利用ALTER TABLE的DISABLE KEYS和ENABLE KEYS情势:
| ALTER TABLE tbl_name DISABLE KEYS; ALTER TABLE tbl_name ENABLE KEYS; |
这些语句关闭或翻开表中非唯一（non-unique）索引的更新历程.
ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的举荐办法,因为服务器也是这样操作的（假如你利用LOAD DATA语句把数据载入空的MyISAM表中,服务器会自动地履行这样的优化操作）.
· Myisamchk工具可以履行索引保护.它直接在数据表文件上举行操作,因此利用它的时刻,你必须拥有数据表文件的写入权限.
利用myisamchk禁止MyISAM表的索引的办法是,首先你要确保已经奉告了服务器让该数据表独立出来,接着把它移动到得当的数据库目录中,并运行下面的号令:
| % myisamchk --keys-used=0 tbl_name |
载入数据之后,重新激活索引:
| % myisamchk --recover --quick --keys-used=n tbl_name |
此中的n是位掩码（bitmask）,它指明了要激活的索引.Bit 0（第一个位）与索引1对应.比方,假如某张表拥有三个索引,那么n的值应当是7（二进制的111）.你也可以利用--description选项来检测索引的数目:
| % myisamchk --description tbl_name |
前面的数据载入原则也可以利用于混合查询环境（客户端履行多种差别的操作）.比方,你应当避免在那些频繁被改正（写入）的数据表上运行长时间的SELECT查询.这会引发大量的争用（contention）,招致写入操作的性能较差.一个大概的办理办法是,假如你的写入操作主如果INSERT操作,那么把新记录增添到帮助表中,接着周期性地把这些记录增添到主表中.假如你必须当即拜候这些新记录,那么这个战略是不行的,但是假如你可以承当得起短时间内不拜候这些数据的代价,那么利用帮助表可以在两个方面带来好处.首先,它削减了主表上的SELECT查询争用的问题,因此它们履行得更快.其次,把帮助表中的批量数据载入主表中所耗费的时间总和也比单独载入记录耗费的时间总和要小一些;键缓存只需求在每次批量载入完毕后革新一次,而不用每个数据行载入后都革新一次.
利用这种战略的一个利用是把Web服务器的Web页面拜候日记载入MySQL数据库的时刻.在这种情形下,保证实体当即进入主表的优先级并不高（没有这个必要性）.
假如你在MyISAM表上利用了混合的INSERT和SELECT语句,你便可以操纵并发性插入操作的长处了.这个特点答应插入和检索操作同时举行,而不需求利用帮助表.你可以查看"利用并发性插入操作"部份
上一页 [1] [2] [3] [4]
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“<b>MySQL查询优化系列讲座之数据范例与效率</b>”更多相关内容
中查找“<b>MySQL查询优化系列讲座之数据范例与效率</b>”更多相关内容