绝无经由的MySQL查询优化系列讲座之数据范例与效力

MySQL在业界的流行所带来的另一个好处是，人们总可以很轻松地发现本行业的MySQL学习教程。厂商都希望他们的开发工具和应用程序框架可以与MySQL数据库兼容。　　这一部分供应了怎样选择数据范例来匡助进步查询运转速率的一些引导：
　　在可使用短数据列的时分就不要用长的。假如你有一个流动长度的CHAR数据列，那末就不要让它的长度超越实践必要。假如你在数据列中存储的最长的值有40个字符，就不要界说成CHAR(255)，而应当界说成CHAR(40)。假如你可以用MEDIUMINT取代BIGINT，那末你的数据表就小一些（磁盘I/O少一些），在盘算过程当中，值的处置速率也快一些。假如数据列被索引了，那末利用较短的值带来的功能进步加倍明显。不但索引能够进步查询速率，并且短的索引值也比长的索引值处置起来要快一些。
　　假如你能够选择数据行的存储格局，那末应当利用最合适存储引擎的那种。关于MyISAM数据表，最好利用流动长度的数据列取代可变长度的数据列。比方，让一切的字符列用CHAR范例取代VARCHAR范例。衡量得掉，我们会发明数据表利用了更多的磁盘空间，可是假如你可以供应分外的空间，那末流动长度的数据行被处置的速率比可变长度的数据行要快一些。关于那些被频仍修正的表来讲，这一点特别凸起，由于在那些情形下，功能更简单遭到磁盘碎片的影响。
　　・在利用可变长度的数据行的时分，因为纪录长度分歧，在屡次实行删除和更新操纵以后，数据表的碎片要多一些。你必需利用OPTIMIZETABLE来按期保护其功能。流动长度的数据行没有这个成绩。
　　・假如呈现数据表溃散的情形，那末数据行长度流动的表更简单从头机关。利用流动长度数据行的时分，每一个纪录的入手下手地位都能够被检测到，由于这些地位都是流动纪录长度的倍数，可是利用可变长度数据行的时分就纷歧定了。这不是与查询处置的功能相干的成绩，可是它必定可以加速数据表的修复速率。
　　只管把MyISAM数据表转换成利用流动长度的数据列能够进步功能，可是你起首必要思索上面一些成绩：
　　・流动长度的数据列速率较快，可是占用的空间也较年夜。CHAR(n)列的每一个值（即便是空值）一般占n个字符，这是由于把它存储到数据表中的时分，会在值的前面增加空格。VARCHAR(n)列占据的空间较小，由于只必要分派需要的字符个数用于存储值，加上一两个字节来存储值的长度。因而，在CHAR和VARCHAR列之间举行选择的时分，实践上是工夫与空间的对照。假如速率是次要的思索要素，那末就利用CHAR数据列猎取流动长度列的功能上风。假如空间很主要，那末就利用VARCHAR数据列。总而言之，你能够以为流动长度的数据行能够进步功能，固然它占用了更年夜的空间。可是关于某些特别的使用程序，你大概但愿利用两种体例来完成某个数据表，然后运转测试来决意哪一种情形切合使用程序的需求。
　　・即便乐意利用流动长度范例，偶然候你也没有举措利用。比方，善于255个字符的字符串就没法利用流动长度范例。
　　MEMORY数据表今朝都利用流动长度的数据行存储，因而不管利用CHAR或VARCHAR列都没有干系。二者都是作为CHAR范例处置的。
　　关于InnoDB数据表，外部的行存储格局没有辨别流动长度和可变长度列（一切数据行都利用指向数据列值的头指针），因而在实质上，利用流动长度的CHAR列纷歧定比利用可变长度VARCHAR列复杂。因此，次要的功能要素是数据利用用的存储总量。因为CHAR均匀占用的空间多于VARCHAR，因而利用VARCHAR来最小化必要处置的数据行的存储总量和磁盘I/O是对照好的。
　　关于BDB数据表，不管利用流动长度或可变长度的数据列，不同都不年夜。两种办法你都可用试一下，运转一些实行测试来检测是不是存在分明的不同。
　　把数据列界说成不克不及为空（NOTNULL）。这会使处置速率更快，必要的存储更少。它偶然候还简化了查询，由于在某些情形下你不必要反省值的NULL属性。
　　思索利用ENUM数据列。假如你具有的某个数据列的基数很低（包括的分歧的值数目无限），那末能够思索把它转换为ENUM列。ENUM值能够被更快地处置，由于它们在外部体现为数值。
　　利用PROCEDUREANALYSE()。运转PROCEDUREANALYSE()能够看到数据表中列的情形：
SELECT*FROMtbl_namePROCEDUREANALYSE();
SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);
　　输入的每列信息城市对数据表中的列的数据范例提出优化倡议。第二个例子告知PROCEDUREANALYSE()不要为那些包括的值多于16个大概256字节的ENUM范例提出倡议。假如没有如许的限定，输入信息大概很长；ENUM界说一般很难浏览。
　　依据的PROCEDUREANALYSE()输入信息，你大概发明，能够修正本人的数据表来使用那些效力更高的数据范例。假如你决意改动某个数据列的范例，必要利用ALTERTABLE语句。
　　利用OPTIMIZETABLE来优化那些遭到碎片影响的数据表。被大批修正的数据表，出格是那些包括可变长度数据列的表，简单蒙受碎片的影响。碎片很糟，由于它会招致用于存储数据表的磁盘块构成无用空间（朴陋）。跟着工夫的推移，为了失掉无效的数据行，你必需读取更多的块，功能就会下降。这会呈现在任何可变长度的数据行上，可是关于BLOB或TEXT数据列特别凸起，由于它们的长度差别太年夜了。在一般情形下利用OPTIMIZETABLE会避免数据表的功能下降。OPTIMIZETABLE能够用于MyISAM和BDB数据表，可是defragments只能用于MyISAM数据表。任何存储引擎中的碎片收拾办法都是用MySQLdump来转储（dump）数据表，接着利用转储的文件删除偏重新创建那些数据表：
%mysqldump--optdb_nametbl_name>dump.sql
%mysqldb_name<dump.sql
　　把数据打包放进BLOB或TEXT数据列。利用BLOB或TEXT数据列存储打包（pack）的数据，并在使用程序中举行解包（unpack），使你可以在一次检索操纵中失掉必要的任何信息，而不必要举行屡次检索。它对那些很难用尺度的数据表布局体现的数据值和频仍变更的数据值也是有匡助的。
　　办理这个成绩的另外一种办法是让那些处置Web窗体的使用程序把数据打包成某种数据布局，然后把它拔出到单个BLOB或TEXT数据列中。比方，你可使用xml暗示查询拜访表复兴，把那些XML字符串存储在TEXT数据列中。因为要对数据举行编码（从数据表中检索数据的时分还必要解码），它会增添客户真个开支，可是能够简化数据布局，并且它还打消了那些由于改动了查询拜访表的内容而必需改动数据表布局的需求。
　　另外一方面，BLOB和TEXT值也会引发本人的一些成绩，出格是实行了大批的删除或更新操纵的时分。删除这类值会在数据表中留下很年夜的"朴陋"，今后填进这些"朴陋"的纪录大概长度分歧（后面会商的OPTIMIZETABLE提出办理这个成绩的一些倡议）。
　　利用分解的（synthetic）索引。分解的索引列在某些时分是有效的。一种举措是依据别的的列的内容创建一个散列值，并把这个值存储在独自的数据列中。接上去你就能够经由过程检索散列值找到数据行了。可是，我们要注重这类手艺只能用于准确婚配的查询（散列值关于相似<或>=等局限搜刮操纵符是没有效处的）。我们可使用md5()函数天生散列值，也能够利用SHA1()或CRC32()，大概利用本人的使用程序逻辑来盘算散列值。请记着数值型散列值能够很高效力地存储。一样，假如散列算法天生的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会遭到尾部空格往除的影响。
　　分解的散列索引关于那些BLOB或TEXT数据列出格有效。用散列标识符值查找的速率比搜刮BLOB列自己的速率快良多。
　　在不用要的时分制止检索年夜型的BLOB或TEXT值。比方，SELECT*查询就不是很好的设法，除非你可以断定作为束缚前提的WHERE子句只会找到所必要的数据行。不然，你大概毫无目标地在收集上传输大批的值。这也是BLOB或TEXT标识符信息存储在分解的索引列中对我们有所匡助的例子。你能够搜刮索引列，决意那些必要的数据行，然后从及格的数据行中检索BLOB或TEXT值。
　　把BLOB或TEXT列分别到独自的表中。在某些情况中，假如把这些数据列挪动到第二张数据表中，可让你把原数据表中的数据列转换为流动长度的数据行格局，那末它就是成心义的。这会削减主表中的碎片，使你失掉流动长度数据行的功能上风。它还使你在主数据表上运转SELECT*查询的时分不会经由过程收集传输大批的BLOB或TEXT值。
　　高效力地载进数据
　　在年夜多半情形下，你所存眷的是SELECT查询的优化，由于SELECT查询是最多见的查询范例，并且怎样优化它们又不是太复杂。与此构成对照，把数据载进数据库的操纵就绝对间接了。但是，你仍旧能够使用某些战略来改良数据载进操纵的效力。基础的道理以下所示：
　　・批量载进比单行载进的效力高，由于在每笔记录被载进后，键缓存（keycache）不必革新（flush）；能够在这批纪录的开端革新键缓存。键缓存革新的频次削减得越多，数据载进的速率就越快。
　　・没有索引的数据表的载进速率比有索引的要快一些。假如存在索引，不仅要把纪录增加到数据文件中，还必需修正索引来反应新增的纪录。
　　・较短的SQL语句对照长的SQL语句快，由于它们所触及到服务器端剖析历程较少，同时经由过程收集把它们从客户端发送到服务器上的速率也更快。
　　个中有些要素看起来是主要的（特别是最初一个），可是假如你载进的数据良多，那末即便很小的效力差别也会招致必定的功能不同。我们能够夙昔面的一样平常道理得出几条怎样疾速载进数据的理论结论：
　　・LOADDATA（一切情势的）比INSERT效力高，由于它是批量载进数据行的。服务器只必要剖析息争释一条语句，而不是多条语句。一样，索引只必要在一切的数据行被处置过以后才革新，而不是每行革新一次。
　　・不带LOCAL的LOADDATA比带有LOCAL的LOADDATA的速率要快。不带LOCAL的时分，文件必需位于服务器上，并且你必需具有FILE权限，可是服务器却能够间接从磁盘上读取文件。利用LOADDATALOCAL的时分，客户端读取文件并经由过程收集把它发送给服务器，速率慢一些。
　　・假如你必需利用INSERT，那末试着利用在一个语句中指定多个数据行的情势：
INSERTINTOtbl_nameVALUES(...),(...),...;
　　在这个语句中指定的数据行越多，效果就越好。这会削减需要的语句数目，并最小化索引革新的次数。这一条结论看起来与后面所会商的"语句越短，实行速率越快"相冲突，可是实践上其实不冲突。这儿所会商的是同时拔出多个数据行的一个INSERT语句所消费的开支比功效不异的多个单行INSERT语句的消费的开支要小一些，而且多行语句损耗的索引革新开支也少一些。
　　假如你利用mysqldump天生数据库备份文件，那末MySQL4.1会默许地天生多行INSERT语句：它会激活--opt(优化)选项，而这个选项会激活--extended-insert选项，该选项天生多行INSERT语句，还存在别的一些选项也能够使数据被载进的时分，转储文件被处置的效力更高。关于MySQL4.1之前的版本，你能够明白地指定--opt或--extended-insert选项。
　　利用mysqldump的时分要制止利用--complete-insert选项；它天生的INSERT语句是每一个数据行一条语句的，语句统共会很长，比多行语句必要的剖析操纵更多。
　　・假如你必需利用INSERT语句，那末在大概的情形下，对它们举行分组以削减索引的革新。关于事件性的存储引擎，在单个事件中提交，而不是在主动提交（autocommit）形式下提交INSERT语句能够完成如许的功效：
STARTTRANSACTION;
INSERTINTOtbl_name...;
INSERTINTOtbl_name...;
INSERTINTOtbl_name...;
COMMIT;
　　关于非事件性的存储引擎，猎取数据表上的写进锁，它被锁定的时分提交INSERT语句：
LOCKTABLEStbl_nameWRITE;
INSERTINTOtbl_name...;
INSERTINTOtbl_name...;
INSERTINTOtbl_name...;
UNLOCKTABLES;

　　不管接纳哪一种办法，你失掉的优点都是不异的：索引在一切的语句都被实行以后才革新一次，而不是每一个INSERT语句革新一次索引。前面先容了在主动提交形式下或数据表没有被锁定的时分产生的情形。
　　・关于MyISAM数据表，削减索引革新的别的一个战略是利用DELAYED_KEY_WRITE表选项。利用这个选项的时分，数据行会像寻常一样当即写进数据文件中，可是键缓存只是偶然革新一次，而不是在每次拔出操纵以后都必要革新。假如要在服务器上周全地利用提早索引革新，那末就必要利用--delay-key-write选项来启动mysqld。在这类情形下，每一个数据表的索引块写进操纵城市被提早，直到这些数据块必需为别的的索引值供应空间、大概实行了FLUSHTABLES命令、大概数据表被封闭的时分才实行操纵。
　　假如你选择了对MyISAM数据表利用提早键写进，那末不一般的服务器封闭大概会引发索引值的丧失。这不是致命的成绩，由于MyISAM索引能够根据数据行来举行修复，可是假如想让修复历程呈现，你就必需利用--myisam-recover=FORCE选项来启动服务器。这个选项会使服务器在翻开MyISAM数据表的时分反省它们，假如有需要就主动地修复它们。
　　关于复制（replication）附属服务器，你大概但愿利用--delay-key-write=ALL来提早一切的MyISAM数据表索引的革新，不论在主服务器上最后是怎样创建它们的。
　　・利用紧缩的客户端/服务器协定来削减收集上数据传输的数目。关于年夜多半MySQL客户端来讲，我们都可使用--compress命令行选项来指定它。一般，这个选项只是在较慢的收集上利用，这是由于紧缩操纵会消费大批的处置器工夫。
　　・让MySQL替你拔出默许值。也就是说，不管怎样都不要给INSERT语句中那些能够付与默许值的列指定值。均匀起来，你的语句更短，削减了经由过程收集发送到服务器的字符数目。别的，因为语句包括的值较少，服务器实行的剖析和值转换操纵也较少。
　　・关于MyISAM数据表，假如你必需把大批的数据载进一个新表，最好创建不带索引的表，载进数据，然后创建索引，如许的事情序次的速率要快一些。一次性地创建索引比每行都更新索引的速率要快一些。关于已带有索引的表，假如事后删除或克制索引，厥后再从头创建大概激活索引，那末数据载进的速率也要快一些。这些战略不克不及使用于InnoDB或BDB表，它们没有对分别的索引创建历程举行优化。
　　假如你思索利用删除或克制索引的战略，把数据载进MyISAM数据表，那末在评价取得的上风的时分，就必要思索全部情况。假如你把大批的数据载进年夜型的数据表中，那末在没有任何特别筹办事情的情形下，从头创建索引消费的工夫大概比载进数据的工夫还要长。
　　要删除而且从头创建索引，必要利用DROPINDEX和CREATEINDEX，大概利用与索引相干的ALTERTABLE。克制和激活索引有两种举措：
　　・你可用利用ALTERTABLE的DISABLEKEYS和ENABLEKEYS情势：
ALTERTABLEtbl_nameDISABLEKEYS;
ALTERTABLEtbl_nameENABLEKEYS;
　　这些语句封闭或翻开表中非独一（non-unique）索引的更新历程。
　　ALTERTABLE的DISABLEKEYS和ENABLEKEYS子句是索引克制和激活操纵的保举办法，由于服务器也是如许操纵的（假如你利用LOADDATA语句把数据载进空的MyISAM表中，服务器会主动地实行如许的优化操纵）。
　　・Myisamchk工具能够实行索引保护。它间接在数据表文件长进行操纵，因而利用它的时分，你必需具有数据表文件的写进权限。
利用myisamchk克制MyISAM表的索引的办法是，起首你要确保已告知了服务器让该数据表自力出来，接着把它挪动到得当的数据库目次中，并运转上面的命令：
%myisamchk--keys-used=0tbl_name
　　载进数据以后，从头激活索引：
%myisamchk--recover--quick--keys-used=ntbl_name
　　个中的n是位掩码（bitmask），它指了然要激活的索引。Bit0（第一个位）与索引1对应。比方，假如某张表具有三个索引，那末n的值应当是7（二进制的111）。你也能够利用--description选项来检测索引的数目：
%myisamchk--descriptiontbl_name
　　后面的数据载进准绳也能够使用于夹杂查询情况（客户端实行多种分歧的操纵）。比方，你应当制止在那些频仍被修正（写进）的数据表上运转长工夫的SELECT查询。这会激发大批的争用（contention），招致写进操纵的功能较差。一个大概的办理举措是，假如你的写进操纵次要是INSERT操纵，那末把新纪录增加到帮助表中，接着周期性地把这些纪录增加到主表中。假如你必需当即会见这些新纪录，那末这个战略是不可的，可是假如你可以承当得起短时间内不会见这些数据的价值，那末利用帮助表能够在两个方面带来优点。起首，它削减了主表上的SELECT查询争用的成绩，因而它们实行得更快。其次，把帮助表中的批量数据载进主表中所消费的工夫总和也比独自载进纪录消费的工夫总和要小一些；键缓存只必要在每次批量载进停止后革新一次，而不必每一个数据行载进后都革新一次。
　　利用这类战略的一个使用是把Web服务器的Web页面会见日记载进MySQL数据库的时分。在这类情形下，包管实体当即进进主表的优先级其实不高（没有这个需要性）。
　　假如你在MyISAM表上利用了夹杂的INSERT和SELECT语句，你就能够使用并发性拔出操纵的长处了。这个特征同意拔出和检索操纵同时举行，而不必要利用帮助表。你能够检察"利用并发性拔出操纵"部分。
你可以配置MySQL运行在微小的嵌入式应用程序中，处理的数据可能不足1Mb??而你也可以用它来处理数Tb的数据。MySQL获得这种可扩展性的路径之一是通过一个人们所熟知的存储过程，这是一个运行在程序之外的微型、预编译程序。

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

原来公司用过MYSQL自己也只是建个表写个SQL

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

我个人认为就是孜孜不懈的学习

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。