5.2 查询优化器的功能和怪癖

li3p

　　在测试你的查询语句时，记得要永远使用真实的数据。MySQL性能问题的根源之一，就是查询优化器对测试数据的处理方式。它经常做一些奇怪的事情。如果你不知道它是如何工作的以及为什么要这样做（事实上它也的确极少告诉你为什么要这么做），你会花费大量的时间去追踪一个莫须有的问题。或者，更糟糕的情况下，你会因为在MySQL的邮件列表里询问这个问题而窘迫不已，最终你发现是你自己制造了这个问题。
　　通常，只要足够确信使用索引会比不使用索引更加有效，MySQL就会使用索引。在测试的过程中，这会带来出现一些负面的假象，我们会在接下来探讨的两种情形中遇到。
5.2.1过低的数据差异性（ Diversity）
　　即使你有很多的数据（上千条记录或者更多），但在你的数据不具备足够的差异性的情况下，MySQL也可能会选择忽略索引的存在。为什么会发生这种情况？设想一下你有一张表来存储世界多数城市的历史气象信息的表：
CREATE TABLE weather(  city       VARCHAR(100) NOT NULL,  high_temp  TINYINT      NOT NULL,  low_temp   TINYINT      NOT NULL,  the_date   DATE         NOT NULL,  INDEX (city),  INDEX (the_date),)
　　不用加载所有的两百万条记录，就以你记载两年产生的数据（1980年和1981年）来测试。在进行一些测试之后，你发现那些需要存取很多记录的查询语句，是在使用全表扫描，而不是使用the_date 索引。举个例子：查找1980年的最高温度的平均值，你可能会编写如下的查询：
SELECT AVG(high_temp) FROM weatherWHERE the_date BETWEEN '1980-01-01' AND '1980-12-31';
　　假定只是获取1980和1981两年的数据，查询需要检查weather表中50%以上的记录。在这种情况下，MySQL确定对整个表使用简单的表扫描要更为快速。
　　MySQL又是怎么知道这一点的？当超过某个阈值之后，通过索引来定位记录就要比顺序读取的方式来得慢。对MySQL而言，这个阈值点是大概30%。这个值的选择，是基于MySQL开发者们丰富的经验（和对代码的了解），在各版本发布之间确定下来的。这个数值的实际上是特定于每个存储引擎的：InnoDB表的这个阈值与MyISAM表的不同，等等。
　　索引的性能在这种情况下表现差的主要原因，可以一直追溯到硬件：磁盘存取的性能。索引总是被排序的，但是磁盘上的数据却并非如此。使用索引意味着按照索引的排序顺序、而不是依据索引数据在磁盘上的存取数据来访问记录。最终的结果就是更多的时间被耗费在磁盘数据寻道上而非是数据读取上。顺序的读取总是要比随机存取来的快。如果你有幸能够使用一个RAM磁盘，上述绝大多数开销都消弥无形了。
　　从这些知识中你可以得出两个结论：第一，如果一个表的数据量将来永远保持在一个非常小的数量级，那么你可以不使用索引。（唯一索引对这条规则而言是个例外。没有唯一索引你就无法为一个表强制一个唯一约束）。第二个结论仅仅是对之前我们提到的内容的一个强调－在测试过程中永远要使用有代表性的数据集。这种代表性应该体现在数据集的数量和数据的差异性上。
　　一个必须提到的特殊情况是关于纯索引查询(Index-Only Query)。如果你刚好编写一个查询，而这个查询所请求的所有数据正好都在索引中，你会得到一个愉快的惊喜：MySQL足够聪明得意识到所有需要的数据都存在于索引中，所以它甚至不会浪费时间从磁盘中读取任何记录。这一来，显然MySQL能提供给你更为出色的性能。
5.2.2 基于索引的排序
　　MySQL的弱项之一是排序。它通常可以在一次心跳的时间检索出15000条记录，但是当你需要这些记录以某种特定的顺序排列时，它所需要的时间可就不是这么一星半点了。
[2]
　　[2]当然，性能永远是相对的。我们看到在使用ORDER BY 子句加入排序后，原本一个20ms的查询MySQL花费了200ms。对于多数应用程序而言，200ms已经是非常快了。
　　这个问题是具有双重性的。首先，排序简化了很多的工作，而工作是需要时间的。抛开去增加一个更快的CPU，这个事实你是无法回避的。如果你不是基于一个计算字段（Computed Field）进行排序，你的第一个直觉大概就是给要排序的列加个索引。不幸的是，这很少能帮上忙。如果你还记得第四章中提到的，MySQL对每个查询最多使用一个索引。不幸的是你本查询中你已经为该表使用了一个索引，所以MySQL不会理会你新加的索引。
　　第二个问题的解决方案我们在第四章中也有提到。将要排序的列作为现有索引的第二个部分。这样就可以一箭双雕了。你既有了一个可以用来帮助MySQL快速定位记录的索引（像之前一样），同时也用有了一个能够用来对数据进行排序的索引。这避免了MySQL对结果集进行排序的需要。
　　回到上面的关于气象信息的例子，为了给下面的这个查询提速：
SELECT * FROM weather WHERE city = 'Toledo' ORDER BY the_date DESC
　　你可以将city 的索引变为基于(city, the_date)的索引：
ALTER TABLE weather DROP INDEX city, ADD INDEX (city, the_date)
　　记住索引中列出现的顺序也是影响重大，最左前缀规则决定了在本查询中，city列必须首先出现。
　　更进一步，你可能会尝试删除单独的the_date一索引。除非你能够确定没有查询会在WHERE子句中使用the_date列，否则不要这么做。基于the_date的查询，无法从新的基于(city, the_date)的索引中得到好处，因为the_date并非是该索引的最左前缀（leftmost prefix）。
5.2.3 无厘头的查询（Impossible Queries）
　　MySQL会对每个具有WHERE子句的查询进行基础的逻辑分析。这样一来，MySQL经常可以觉察到一些毫无意义的请求：
SELECT * FROM mytable WHERE id  30000
　　如果发现了不可能的WHERE子句，MySQL返回零结果集，跳过运行其他毫无意义并可能代价昂贵的查询操作。
　　如果你对MySQL是否优化掉了那些不可能的WHERE子句心存怀疑，就通过EXPLAIN来验证一下这个查询。如果你看到类似现面的执行结果：
mysql> SELECT * FROM mytable WHERE id  30000+-----------------------------------------------------+| Comment                                             |+-----------------------------------------------------+| Impossible WHERE noticed after reading const tables |+-----------------------------------------------------+1 row in set (0.00 sec)
　　你就会知道MySQL是怎么思考的。
　　除非是因为简单的拼写错误，你不可能会经常执行类似上面的查询。但是如果你是在MySQL之上构建一个应用程序，而且碰巧在代码中犯了一个拼写错误或者一个严重的逻辑错误，你可以在跟踪问题之前，避免去运行一些无意义的查询。知道MySQL不会浪费时间在不合逻辑的查询上是件好事。
5.2.4 使用全文索引（Full-Text）取代LIKE子句
　　在第四章中，已经明确：在你的查询中使用全文索引来检索单词或词组，要远远快于使用LIKE子句。在大多数情况下，你应该使用全文索引来应对此类检索问题。
　　然而，有时这种方式也会带来问题。在确定对某个表使用哪个索引方面，查询优化器看起来并没有认真对待全文索引。事实上，如果存在一个可用的全文索引，优化器将总是使用它，而不是考虑这个索引从结果集中排除了多少无用的记录。希望未来的MySQL版本中会修正这个问题。

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/7114/showart_276522.html