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设置常常很难观赏。　　按照的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值。　　高功效地载入数据　　在大普遍情景下，你所关心的是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; 　　不管沿用哪种本领，你获得的长处都是沟通的：索引在一切的语句都被实行之后才革新一次，而不是每个insert语句革新一次索引。反面引见了在机动提交形式下或数据表没有被锁定的功夫爆发的情景。　　· 对于myisam数据表，缩小索引革新的其余一个战略是运用delayed_key_write表选项。运用这个选项的功夫，数据行会像凡是一律登时写入数据文献中，然而键缓存不过偶然革新一次，而不是在历次插入操纵之后都须要革新。即使要在效劳器上所有地运用推迟索引革新，那么就须要运用--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语句，你就不妨运用并发性插入操纵的便宜了。这个个性承诺插入和检索操纵同声举行，而不须要运用扶助表。你不妨察看"运用并发性插入操纵"局部。