MySQL查询优化(数据类型与效率)

在大多数情况下，你所关注的是SELECT查询的优化，因为SELECT查询是最常见的查询类型，而且如何优化它们又不是太简单。与此形成对比，把数据载入数据库的操作就相对直接了。然而，你仍然可以利用某些策略来改善数据载入操作的效率。

　在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的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()可以看到数据表中列的情况：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> 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）数据表，接着使用转储的文件删除并重新建立那些数据表：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> % 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，那么试着使用在一个语句中指定多个数据行的形式：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> 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语句可以实现这样的功能：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> START TRANSACTION;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
COMMIT;
　　对于非事务性的存储引擎，获取数据表上的写入锁，它被锁定的时候提交INSERT语句：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> 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形式：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> 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表的索引的方法是，首先你要确保已经告诉了服务器让该数据表独立出来，接着把它移动到适当的数据库目录中，并运行下面的命令：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> % myisamchk --keys-used=0 tbl_name
　　载入数据之后，重新激活索引：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> % myisamchk --recover --quick --keys-used=n tbl_name
　　其中的n是位掩码（bitmask），它指明了要激活的索引。Bit 0（第一个位）与索引1对应。例如，如果某张表拥有三个索引，那么n的值应该是7（二进制的111）。你也可以使用--description选项来检测索引的数量：

<table bordercolor=\"#cccccc\" width=\"90%\" align=\"center\" bgcolor=\"#e3e3e3\" border=\"1\"> % myisamchk --description tbl_name
　　前面的数据载入原则也可以应用于混合查询环境（客户端执行多种不同的操作）。例如，你应该避免在那些频繁被修改（写入）的数据表上运行长时间的SELECT查询。这会引发大量的争用（contention），导致写入操作的性能较差。一个可能的解决办法是，如果你的写入操作主要是INSERT操作，那么把新记录添加到辅助表中，接着周期性地把这些记录添加到主表中。如果你必须立即访问这些新记录，那么这个策略是不行的，但是如果你能够承担得起短期内不访问这些数据的代价，那么使用辅助表可以在两个方面带来好处。首先，它减少了主表上的SELECT查询争用的问题，因此它们执行得更快。其次，把辅助表中的批量数据载入主表中所花费的时间总和也比单独载入记录花费的时间总和要小一些；键缓存只需要在每次批量载入结束后刷新一次，而不用每个数据行载入后都刷新一次。

　　使用这种策略的一个应用是把Web服务器的Web页面访问日志载入MySQL数据库的时候。在这种情况下，保证实体立即进入主表的优先级并不高（没有这个必要性）。

　　如果你在MyISAM表上使用了混合的INSERT和SELECT语句，你就可以利用并发性插入操作的优点了。这个特性允许插入和检索操作同时进行，而不需要使用辅助表。你可以查看"使用并发性插入操作"部分。
[点击查看详细]