MySQL查询优化系列讲座之数据类型与效率

    但是或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的速度要快。