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()可以看到数据表中列的情况：
根据的PROCEDURE ANALYSE()输出信息，你可能发现，可以修改自己的数据表来利用那些效率更高的数据类型。如果你决定改变某个数据列的类型，需要使用ALTER TABLE语句。
把数据打包放入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值。 高效率地载入数据
在这个语句中指定的数据行越多，效果就越好。这会减少必要的语句数量，并最小化索引刷新的次数。这一条结论看起来与前面所讨论的"语句越短，执行速度越快"相矛盾，但是实际上并不矛盾。这儿所讨论的是同时插入多个数据行的一个INSERT语句所花费的开销比功能相同的多个单行INSERT语句的花费的开销要小一些，并且多行语句消耗的索引刷新开销也少一些。 如果你使用mysqldump生成数据库备份文件，那么MySQL 4.1会默认地生成多行INSERT语句：它会激活--opt (优化)选项，而这个选项会激活--extended-insert选项，该选项生成多行INSERT语句，还存在其它一些选项也可以使数据被载入的时候，转储文件被处理的效率更高。对于MySQL 4.1以前的版本，你可以明确地指定--opt或--extended-insert选项。 使用mysqldump的时候要避免使用--complete-insert选项；它生成的INSERT语句是每个数据行一条语句的，语句总共会很长，比多行语句需要的分析操作更多。 ・ 如果你必须使用INSERT语句，那么在可能的情况下，对它们进行分组以减少索引的刷新。对于事务性的存储引擎，在单个事务中提交，而不是在自动提交（autocommit）模式下提交INSERT语句可以实现这样的功能：
对于非事务性的存储引擎，获取数据表上的写入锁，它被锁定的时候提交INSERT语句：
无论采用哪种方法，你得到的好处都是相同的：索引在所有的语句都被执行之后才刷新一次，而不是每个INSERT语句刷新一次索引。后面介绍了在自动提交模式下或数据表没有被锁定的时候发生的情况。
这些语句关闭或打开表中非唯一（non-unique）索引的更新过程。 ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推荐方法，因为服务器也是这样操作的（如果你使用LOAD DATA语句把数据载入空的MyISAM表中，服务器会自动地执行这样的优化操作）。 ・ Myisamchk工具可以执行索引维护。它直接在数据表文件上进行操作，因此使用它的时候，你必须拥有数据表文件的写入权限。 使用myisamchk禁止MyISAM表的索引的方法是，首先你要确保已经告诉了服务器让该数据表独立出来，接着把它移动到适当的数据库目录中，并运行下面的命令：
载入数据之后，重新激活索引：
其中的n是位掩码（bitmask），它指明了要激活的索引。Bit 0（第一个位）与索引1对应。例如，如果某张表拥有三个索引，那么n的值应该是7（二进制的111）。你也可以使用--description选项来检测索引的数量：
前面的数据载入原则也可以应用于混合查询环境（客户端执行多种不同的操作）。例如，你应该避免在那些频繁被修改（写入）的数据表上运行长时间的SELECT查询。这会引发大量的争用（contention），导致写入操作的性能较差。一个可能的解决办法是，如果你的写入操作主要是INSERT操作，那么把新记录添加到辅助表中，接着周期性地把这些记录添加到主表中。如果你必须立即访问这些新记录，那么这个策略是不行的，但是如果你能够承担得起短期内不访问这些数据的代价，那么使用辅助表可以在两个方面带来好处。首先，它减少了主表上的SELECT查询争用的问题，因此它们执行得更快。其次，把辅助表中的批量数据载入主表中所花费的时间总和也比单独载入记录花费的时间总和要小一些；键缓存只需要在每次批量载入结束后刷新一次，而不用每个数据行载入后都刷新一次。 使用这种策略的一个应用是把Web服务器的Web页面访问日志载入MySQL数据库的时候。在这种情况下，保证实体立即进入主表的优先级并不高（没有这个必要性）。 如果你在MyISAM表上使用了混合的INSERT和SELECT语句，你就可以利用并发性插入操作的优点了。这个特性允许插入和检索操作同时进行，而不需要使用辅助表。你可以查看"使用并发性插入操作"部分。 |