MySQL之查询性能优化(十一)

查询性能优化

查询优化器的提示(hint)

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划。下面将列举一些常见的提示，并简单地给出什么时候使用该提示。通过在查询中加入相应的提示，就可以抗旨该查询的执行计划。关于每个提示的具体用法，建议直接阅读MySQL官方手册。有些提示和版本有直接关系。可以使用的一些提示如下:

• 1.HIGH_PRIORITY和LOW_PRIORITY
  这个提示告诉MySQL，当多个语句同时访问某一个表的时候，那些语句的优先级相对高些、哪些语句的优先级相对低些。HIGH_PRIORITY用于SELECT语句的时候，MySQL会将此SELECT语句重新调度到所有正在等待表锁以便修改的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于INSERT语句，其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。
  LOW_PRIORITY则正好相反:它会让该语句一直处于等待状态，只要队列中还有需要访问同一个表的语句——即使是哪些比该语句还晚提交到服务器的语句。这就像一个过于礼貌地人站在餐厅门口，只要还有其他顾客在等待就一直不进去，很明显这容易把自己饿坏。LOW_PRIORITY提示在SELECT、INSERT、UPDATE和DELETE语句中都可以使用。这两个提示只对使用表锁地存储引擎有效，千万不要在InnoDB或者其他有细粒度地锁机制和并发控制的引擎中使用。即使是在MyISAM中使用也要注意，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。HIGH_PRIORITY和LOW_PRIORITY经常让人感到困惑。这两个提示并不会获取更多资源让查询"积极"工作，也不会让少获取资源让查询"消极"工作。它们只是简单地控制了MySQL访问某个数据表的队列顺序。
• 2.DELAYED
  这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成IO的应用。这个用法有一些限制:并不是所有的存储引擎都支持这样的做法；并且该提示会导致函数LAST_INSERT_ID()无法正常工作
• 3.STRAIGHT_JOIN
  这个提示可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。当MySQL没能选择正确的关联顺序的时候，或者由于可能太多的顺序导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用。在后面这种情况,MySQL可能会花费大量时间在"statistics"状态，加上这个提示则会大大减少优化器的搜索空间。可以先使用EXPLAIN语句来查看优化器选择的关联顺序，然后使用该提示来重写查询，再看看它的关联顺序。当你确定无论怎样的where条件，某个固定的关联顺序始终是最佳的时候，使用这个提示可以大大提高优化器的效率。但是在升级MySQL版本的时候，需要重新审视下这类查询，某些新的优化特性可能会因为该提示而失效
• 4.SQL_SAMLL_RESULT和SQL_BIG_RESULT
  这两个提示只对SELECT语句有效。它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序工作。
• 5.SQL_BUFFER_RESULT
  这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能地释放表锁。这和前面提到的由客户端缓存结果不同。当你没法使用客户端缓存的时候，使用服务器端的缓存通常很有效。带来的好处是无须在客户端上消耗太多的内存，还可以尽可能地释放对应的表锁。代价是，服务器端将需要更多的内存
• 6.SQL_CACHE和SQL_NO_CACHE
  这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中
• 7.SQL_CALC_FOUND_ROWS
  严格来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西，它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集。可以通过函数FOUND_ROW()获得这个值
• 8.FOR UPDATE和LOCK IN SHARE MODE
  这也不是真正的优化器提示。这两个提示主要控制SELECT语句的所机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于INSERT…SELECT语句是不需要这两个提示的，因为对于MySQL5.0和更细那版本会默认给这些记录加上读锁(可以禁用该默认行为，但不是个好主意)。唯一内置的支持这两个提示的引擎就是InnoDB。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能再不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。糟糕的是，这两个提示经常被滥用，很容易造成服务器的锁争用问题应该尽可能地避免使用这两个提示，通常都有其他更好的方式可以实现同样的目的。
• 9.USE INDEX、IGNORE INDEX和FORCE INDEX
  这几个提示会告诉优化器使用或者不适用哪些索引来查询记录(例如，在决定关联顺序的时候使用哪个索引)。在MySQL5.0和更早的版本，这些提示并不会影响到优化器选择哪个索引进行排序和分组，在MySQL5.1和之后的版本可以通过新增选项FOR ORDER BY和FOR GROUP BY来指定是否对排序和分组有效。FORCE INDEX和USE INDEX基本相同，除了一点:FORCE INDEX会告诉优化器全表扫描的成本会远远高于索引扫描，哪怕实际上该索引用处不大。当发现优化器选择了错误的索引，或者因为某些原因(比如在不适用ORDER BY的时候希望结果有序)要使用另一个索引时，可以使用该提示。

MySQL5.0和更新版本中，新增了一些参数用来控制优化器的行为：

• 1.optimizer_search_depth
  这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于"Statistics"状态，那么可以考虑调低此参数。
• 2.optimizer_prune_level
  该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划
• 3.optimizer_switch
  这个变量包含了一些开启/关闭优化器特性的标志位。例如在MySQL5.1中可以通过这个参数来控制禁用索引合并的特性

前两个参数是用来控制优化器可以走的一些"结晶"。这些结晶可以让优化器在处理非常复杂的SQL语句时，仍然可以很搞笑，但这也可能让优化器错过一些真正最优的执行计划。所以应该根据实际需要来修改这些参数。

MySQL升级后的验证

在优化器面前耍一些"小聪明"是不好的。这样做收效甚小，但是却给维护带来了很多额外的工作量。在MySQL版本升级的时候，这个问题就很突出了，你设置的"优化器提示"很可能会让新版的优化策略失效。MySQL5.0版本引入了大量优化策略，在还没有正式发布的5.6版本中，优化器的改进也是近些年来最大的一次改进。如果要更新到这些版本，当然希望能够从这些改进中受益。新版MySQL基本上在各个方面都有非常大的改进，5.5和5.6这两个版本尤为突出。升级操作一般来说都很顺利，但仍然建议仔细检查各个细节，以防止一些边界情况影响你的应用程序。不过还好，要避免这些，你不需要符出太多的经历，使用Percona ToolKit中的pt-upgrade工具，就可以检查在新版本中运行的SQL是否和老版本一样，返回相同的结果

优化特定类型的查询

接下来将介绍如何优化特定类型的查询，对于未来MySQL的版本未必适用。毫无疑问，某一天优化器自己也会实现这里列出的部分或者全部优化技巧。

优化COUNT()查询

COUNT()聚合函数，以及如何优化使用了该函数的查询，很可能是MySQL中最容易被误解的10个话题之一。在网上随便一搜就能看到很多错误的理解，可能比我们想象的多得多。在做优化之前，先来看看COUNT()函数真正的作用是什么

• 1.COUNT()的作用
  COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的(不统计NULL).如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数(而不是NULL).因为很多人对NULL理解有问题，所以这里很容易产生误解。COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。
  我们发现一个最常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*)，这样些意义清晰，性能也会很好
• 2.关于MyISAM的神话
  一个容易产生的误解就是:MyISAM的COUNT()函数总是非常快，不够这是有前提条件的，即只有没有任何WHERE条件的COUNT()才非常快，因为此时无须实际地去计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将COUNT(col)表达式优化为COUNT()。当统计带WHERE子句的结果集行数，可以是统计某个列值得数量时，MyISAM得COUTN()和其他存储引擎没有任何不同，就不再有神话般得速度了，所以在MyISAM引擎表上执行COUNT()有时候比别得引擎块，有时候比别的引擎慢，这受很多因素影响，要视具体情况而定
• 3.简单的优化
  有时候可以使用MyISAM在COUNT(*)全表非常快得这个特性，来加速一些特定条件的COUNT()查询。在下面的例子中，我们使用标准数据库world来看看如何快速查找到所有ID大于5的城市。可以像下面这样来写这个查询:

mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

通过SHOW STATUS的结果可以看到该擦汗寻需要扫描4097行数据。如果将条件反转一下，先找到ID小于等于5的城市数，然后用总城市数一减就能得到同样的结果，却可以将扫描的行数减少到5行以内:

mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)-> FROM world.City WHERE ID <= 5;

这样做可以大大减少需要扫描的行数，是因为在查询优化阶段会将其中的子查询当作一个常数来处理。在邮件组和IRC聊天频道中，通常会看到这样的问题:如何在同一个查询中统计同一个列的不同值的数量，以减少查询的语句量。例如，假设可能需要通过一个查询返回各种不同颜色的商品数量，此时不能使用OR语句(比如SELECT COUNT(color=‘blur’ OR color=‘red’) FROM items;)因为这样做就无法区分不同颜色的商品数量；也不能在WHERE条件中指定颜色(比如SELECT COUNT(*) FROM items WHERE color=‘blue’ AND color = ‘RED’)因为颜色的条件是互斥的。下面的查询可以在一定程度上解决这个问题。

mysql> SELECT SUM(IF(color='blue', 1,0)) AS blue, SUM(IF(color='red',1,0)) AS red FROM items;

也可以使用COUNT()而不是SUM()实现同样的目的，只需要将满足条件设置为真，不满足条件设置为NULL即可

mysql> SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color= 'read' OR NULL) AS red FROM items;