《MySQL排错指南》——1.6　慢查询

本节书摘来自异步社区出版社《MySQL排错指南》一书中的第1章，第1.6节，作者：【美】Sveta Smirnova（斯维特 斯米尔诺娃），更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.6　慢查询

SQL应用程序的一个常见问题就是性能退化。这一节将会介绍当你面对性能问题时的一些基本操作。不必担心细节，仅仅关注本质的思想就好。随着知识的深入，你会发现你自己变得更加训练有素，能够更加高效地使用它们。

当我考虑本章应涵盖哪些问题时，我曾犹豫是否应该涉及性能相关的问题。市面上有很多详细介绍性能问题的资料，如MySQL参考手册中的“优化”章节以及O'Reilly出版的相关书籍。我会在本书的最后简单介绍一些有用的资料。你很容易就会把整个职业生涯都消耗在该问题上面，或者淹没在可用信息之中。

这里将主要介绍SELECT查询。本节最后会简单介绍一下如何处理修改数据的慢查询问题。

处理慢查询有3个主要技巧：调优查询本身、调优表（包括增加索引）和调优服务器。下面逐一详细介绍。

1.6.1　通过EXPLAIN的信息调优查询

最强大的查询调优工具就是我们之前熟知的EXPLAIN。这个工具为用户提供了服务器实际上如何执行查询的详细信息。MySQL 参考手册已经详细介绍了MySQL的EXPLAIN工具，因此这里不再赘述。不过，我将会重点介绍输出信息中我认为是最重要、最有用的部分。

第一行中你要注意的是type，它展示了连接（join）的执行方式；还要注意rows，它展示了在查询执行过程中检查的行数的估计（例如，如果查询必须扫描整个表，那么rows展示的数值和该表中的行数相等）。多表连接需要检查的行数是每个表中检查行数的笛卡儿积。也就是说，如果请求在第一个表中检查20行，另一个表中检查30行，那么连接一共执行了600次检查。EXPLAIN会包含JOIN中每个表中的行。我们将通过下面的示例进行说明。

即使在操作单表时，EXPLAIN也会报告连接。这可能听起来有些奇怪，因为MySQL的内部优化器把每个请求都当成一个连接，哪怕是单表上的连接。

我们来回顾一下前面介绍过的EXPLAIN输出：

检查的行数是10乘以1，因为子查询对于外部查询的每一行执行一次。第一个查询的类型是index，这意味着全部索引都将被读取。第二个查询的类型是index_subquery。这是一个索引查找函数，工作方式类似于ref类型。因此，在这个示例中，优化器将从itmes表中读取全部索引记录，并且对于从itmes表中查询到10行记录中的每一行，对应从items_links表中读取一行记录。

该如何确认这是一个对该查询合理的执行方式呢？首先，重复查询结果并检查查询的实际执行时间：

MySQL服务器检查了10行然后返回结果是4。这有多快？为了回答这个问题，统计每个表中的行数：

itmes表中有10行记录，每行都有一个唯一的ID。items_links表中有6行记录，没有唯一的ID(iid)。对于当前的数据规模来说，这个设计看起来可以，不过与此同时，这也透露出一个潜在的问题。目前，links数小于items数，并且数目的差异不是很大，但是如果数目差距巨大，那就更加值得注意了。

为了验证这个猜想，同时也为了举例说明查询调优的方法，我将向items表中插入一些数据。id字段定义为INT NOT NULL AUTO_INCREMENT PRIMARY KEY，这样可以确保对新插入的行不会存在关联（link）。这样，我就可以模仿现实中用户想从一个大表中查询少量link的场景（在该场景中是6个）。下面的语句展现了一个快速构造数据的小技巧，即重复地从表中选择所有行，然后再插入更多行：

现在，看一下查询的执行计划是否有所变化：

查询的执行计划并没有变化──这次为了6个link检查了5136行！有没有什么方式可以重写一下这个查询，使其运行得更快一些呢？

子查询的类型是index_subquery。这意味着优化器使用索引查询函数完全替代了子查询。SHOW WARNINGS的输出展示了查询是如何重写的：

输出信息看起来令人生畏，不过至少在这里可以看到一些连接。如果我们重写查询，让在其上执行连接的列更加明显，那又会怎么样？我们也将重写子查询，使之变成显式JOIN；在当前版本的MySQL中，这个方法能够显著地提高性能：

结果看起来挺让人振奋的，因为它没有搜索items表中的所有行。不过，这个查询结果正确吗？

我们得到了6行，而不是4行。这是因为我们要求返回所有匹配的行，这里有相同的link被匹配了两次。可以通过添加DISTINCT关键字来修复这个问题：

提示　提示

可以通过查询重写技巧来确认是否需要添加 DISTINCT 关键字。只要将count(*)替换成items.id，就可以看到重复的值。
加上DISTINCT后，该查询一样高效吗？我们再次通过EXPLAIN来验证一下：

它仍然检查了6行记录。因此，我们可以认为对于这个特定的数据集，该查询得到了优化。本章后面将会解释为何数据结构及其容量会有影响。

在该示例中，数据集是小规模的，所以即使在我的笔记本电脑上，我也无法让其真正执行得特别缓慢。不过，原始的和优化过的查询的执行时间的确有很大不同。下面是原始查询的时间：

下面是优化过的查询的时间：

对于如此小的数据集，查询的时间仍然降低了一半！在测试中，虽然仅提高了0.11秒，不过如果对于上百万行，那么效率提升效果就会更好。

你刚刚学习了一个基本的使用EXPLAIN命令的查询调试技巧：阅读当前查询的信息并与你预期的信息进行比较。该过程可以用来调优从最简单到最复杂的任何查询。

1.6.2　表调优和索引

上一节介绍了调优查询的过程。在全部示例中，EXPLAIN的输出都包括索引信息。那么，如果表没有索引会如何？或者如果没有使用到索引呢？你该如何选择在什么时候、什么地方、添加哪种索引呢？

当结果有限的时候，MySQL服务器会使用索引。因此，在与WHERE、JOIN和GROUP BY语句相关的列上添加索引可以加速查询。在与ORDER BY语句相关的列上添加索引也会有效果，因为它将使服务器更高效地排序。

在掌握这些规则的前提下，添加索引就成为了一个很简单的工作。考察之前示例中的表，但没有任何索引：

如你所见，没有指定任何索引。我们在这些表上试验一个没有优化过的查询，然后再优化它：

类型变成了 ALL，这是最耗时的类型，因为这表示会读取所有行。该查询这次检查了6 * 5137 = 30 822行。这甚至比之前示例中我们认为的慢查询还要糟。

来仔细检查一下这个查询：

这个查询返回结果集中唯一非空值的数目。应该在items.id列上添加索引，以使该查询使用索引。

该查询的另一部分是：

这里有items表中id字段和items_links表中iid字段的连接。因此，应该在这两列上添加索引。

现在看一下添加索引对查询计划的影响：

这看起来比之前好了很多，只有一点不好： items表这次的类型是ref，比之前的eq_ref要差。这是因为我们添加的是一个简单索引，而原始表在该列已经有唯一索引了。我们也可以简单地修改该临时表，因为ID是唯一的并且也应该如此：

现在，当已经使用了执行更快的eq_ref类型的时候，可以删除items.id字段上多余的索引。这在你关心数据修改的查询速度的时候尤为重要，因为每次更新索引都会消耗时间。下一节会讨论何时该停止优化。

你刚刚学习了索引是如何影响查询执行的以及何时应该添加索引。

选择你自己的执行计划

索引实际上也有减慢查询的时候。在这种情况下，应该删除索引或者使用会忽略索引（IGNORE INDEX）的语句（如果其他的查询还需要用到该索引）。也可以使用强制索引（FORCE INDEX）使优化器知道你想要使用的索引。这些语句对于查询调优也非常有用，比如当你想要了解特定索引对性能会有怎样影响的时候。只需要通过EXPLAIN命令执行语句，然后分析输出。

尽管使用IGNORE INDEX和FORCE INDEX 可能听起来不错，但是你应该避免在生产环境中使用，除非你已经准备好在今后的每个升级版本中都逐一检查使用了该语句查询。

因为优化器总是试图选择最佳的执行计划，随着版本的升级，可能对于同一个JOIN会使用不同的执行计划，所以这种检查是必要的。当你没有强制使用或者忽略索引的时候，优化器会按照它认为最佳的计划执行。但是，如果你明确指定优化器在多表JOIN的某个表中应该如何使用索引，那么这个规则可能会造成其他影响，并且这个最终的执行计划在新版本中可能会比之前要差。

在对单一表的查询中使用IGNORE INDEX和FORCE INDEX相对安全。对所有其他的情况，必须在更新后检查确保查询的执行计划没有改变。

在产品中使用IGNORE INDEX和FORCE INDEX的另一个问题是对于指定表的最佳执行计划依赖于其存储的数据。一般的步骤是，优化器检查表的统计数据然后依此调整计划，当然在你使用了IGNORE INDEX和FORCE INDEX的时候不会这样做。如果你使用这些语句，你就必须定期检查在应用程序的生命周期它们是否还有效。

1.6.3　何时停止调优

前面讨论了简单查询。即使是简单查询，我们仍找到了优化的方向，有时经过一步一步的调优我们获得了更好的结果。当你处理包含很多JOIN条件，或者WHERE子句和GROUP BY字句中包含很多字段的复杂查询时，你就会拥有更多的选择。可以认为你总会找到使性能更好的方法，并且这种优化永无止境。因此，现在问题是，什么时候可以认为查询优化合理并可以停止进一步优化。

深入了解性能优化的技术自然可以帮助你选择合适的解决方案。不过，哪怕你自认不是专家，我们也有一些基本原则可以帮你决定停止优化。

首先，你应该了解查询都做了什么。例如，下面的查询：

始终会返回表中的所有列，该语句没有什么可优化的空间。

不过，即使你查询所有列，添加JOIN语句也会使情况改变：

这会产生优化效果，因为ON条件限制了结果集。当然，同样的分析也适用于WHERE和GROUP BY条件。

其次，你应该通过EXPLAIN输出查看连接类型。尽管你想要获得可能的最佳的JOIN类型，但是你应时刻意识到你的数据的约束。例如，非唯一的行永远不会产生eq_ref或者更好的类型。

当你优化查询的时候，你的数据是非常重要的。对于同样的执行计划，不同的数据可能会产生完全不同的结果。最简单的示例就是比较表中只有一行记录和表中超过50%的行都有相同值的结果。在这样情况下，使用索引会降低性能而不是提升性能。

另一个规则：不要只依赖于EXPLAIN的输出，要衡量实际的查询执行时间。
你需要牢记的另一件事情是索引在修改表时的影响。尽管索引通常会提高SELECT查询的性能，但是它会略微降低修改数据的查询的性能，尤其是INSERT语句。因此，有些时候为了加快插入的速度，允许SELECT查询略慢是明智的。要时刻牢记考察你整个应用程序的性能，而不仅仅是某一个查询的性能。

1.6.4　配置选项的影响

假如你已经对查询进行了完全的优化，找不到进一步优化的方法，但是它仍然很慢，那么还有没有办法可以提高它的性能呢？有的。有很多服务器选项可以让你调节对查询有影响的因素，比如内存中临时表的大小、排序缓冲区等。有些针对特定存储引擎（如InnoDB）的选项，也会对查询优化很有用。

第3章将详细介绍这些选项。这里仅对如何使用它们进行性能优化做一个概述。

调整服务器的配置从某种程度来说是一个影响全局的行为，因为每个修改都可能对该服务器上的每个查询造成影响（对于指定引擎的选项，会影响每个使用该引擎的表上的查询）。不过有些选项是针对特定类型的优化的，如果你的请求没有满足条件，它将没有任何作用。

第一个需要检查的选项是缓冲区大小（buffer size）。每个缓冲区都有其存在的特定原因。一般的规律是大缓冲区意味着高性能──不过仅当请求可以针对该缓冲区扮演的特定角色使用大容量缓存的时候。

当然，增加缓冲区大小是有代价的。下面是一些大缓冲区可能带来的影响。我不是想要阻止你使用大缓冲区，因为在合理的环境下它是提高性能非常有效的手段。你仅需要牢记下面的要素然后合理地调整大小。

交换区（Swapping）
大容量缓冲区可能会导致使用到操作系统级别的交换区从而造成性能缓慢，这取决于系统内存大小。通常情况下，MySQL服务器在它所需的所有内存都来自物理内存的时候运行最快。当它使用到交换区的时候，性能显著下降。

当为缓冲区分配的内存大小超过服务器的物理内存大小的时候就会使用到交换区。请注意，有一些缓冲区是针对每个用户线程的。要确定这些缓冲区究竟需要多少内存，可以用公式最大连接数 缓冲区大小（max_connections buffer_size）来计算。计算出所有缓冲区的内存和，并确保和小于mysqld服务器可以使用的内存大小。这个计算的值不是决定性的，因为mysqld实际上可以分配多于你明确指定大小的内存。

启动时间
mysqld需要分配的内存越多，其启动时间就越长。

过期数据
我们还会有伸缩性问题，大部分时候是来自线程间的缓存共享。在这些场景中，扩充缓冲区做缓存会产生内存碎片。你通常会在服务器运行数小时后发现内存碎片问题，该问题发生在旧的数据需要从缓冲区中移除以给新数据腾出空间的时候。这会导致高速运转的服务器突然变慢。第3章会给出这样的示例。

讨论完缓冲区，第3章讨论其他选项。届时，我们不仅关注性能优化选项（如优化器选项），还会关注一些控制高可用性的选项。事务运行得越安全，就需要更多的检查和更慢地执行性能。不过，要注意这些选项；只有在你可以为了性能牺牲安全的时候才调优它们。

当你调优分配的时候，把性能作为整体来考虑尤为重要，因为每个选项都会影响整个服务器。例如，如果你没有使用特定引擎，针对该存储引擎的选项调优不会有任何作用。这是显而易见的，不过我确实见过很多安装环境下有大量关于MyISAM引擎的选项，然而却使用的InnoDB存储引擎，或者相反的情况。如果你用一些通用配置作为模板，这些注意点就尤为重要。

1.6.5　修改数据的查询

我们讨论了影响SELECT性能的因素，在本节我们开始优化修改数据的查询。UPDATE和DELETE查询可以使用与SELECT语句一样的条件去限制受影响的行数。因此，可以使用相同的优化规则。

我们在1.3节中学习了如何把UPDATE和DELETE查询转换成SELECT查询，然后使用EXPLAIN进行调试。可以在5.6.3以下版本的系统上使用该技巧解决性能问题，从5.6.3版本开始增加了EXPLAIN对INSERT、UPDATE和DELETE查询的支持，不过，请牢记UPDATE和DELETE查询有时候与相应的SELECT查询的执行方式略有不同。

通过在查询计划前后查询Handler_%的状态可以检查是否使用了索引：

1 这里使用了SHOW STATUS命令，这是SHOW SEESION STATUS的同义命令，作用是查看当前会话的变量状态。

提示　提示

在测试前使用FLUSH STATUS查询可以方便地重置这些变量。
我们将继续介绍之前列表中的特定变量。你需要注意的是，因为这些是累积的值，所以它们会随着你的每次查询增长。现在我们开始优化1.3节中的查询示例，使其更新可以为空的列：

这条语句没有修改任何行，因为数据在之前损坏了：现在每个字段中的值是0而不是NULL。但是这个请求执行非常缓慢。我们来看一下处理程序变量：

可以看到Handler_read_rnd_next的值非常高，该值代表从datafile中读取下一个值的频繁程度。过高的值一般代表使用了全表扫描，这对性能是有影响的。Handler_read_key也是一个相关的变量，表示读取索引的请求数目。正常情况下该值相对于Handler_read_rnd_next来说不应该这么低，因为这意味着大部分行的读取都没有使用的索引。此外，Handler_commit和Handler_read_first的值也增长缓慢。它们分别代表事务提交的次数和读索引中第一项的次数。最后，Handler_read_first的值是1，表明我们请求服务器读取索引中第一条记录，这可以当作全索引扫描的标志。

希望对这些Handler_%状态变量的简介可以告诉你如何利用它们去检查查询是怎样执行的。对于该查询是否有提升空间这个问题将作为作业留给读者自己去解答。

我仅将对INSERT查询做些说明。它们没有条件去约束受影响的行数，因此表中的索引只会降低插入效率，因为每次插入都需要更新索引。插入的性能需要通过服务器选项调优。特别地，影响InnoDB存储引擎的选项会很有作用。

一种加速插入的方式是把多个插入合并成一条语句，这也叫做“批量插入”（bulk insert）：

不过，请注意插入会阻塞行甚至是整张表，因此其他查询会在插入的过程中被拒绝访问。我将给出一个通用规则来结束本节内容：

在优化任何单个查询的时候，请时刻注意整个应用程序的性能。

1.6.6　没有高招

我们刚刚学习了如何优化服务器选项才能显著提升性能。在本章我们也学习了如何优化特定查询以提升其运行速度。优化查询和优化服务器一般是解决性能问题的两种选择。那么，有没有通用的规则告诉我们该从哪个方向开始优化呢？

我认为恐怕没有。优化服务器选项看起来特别有效以至于许多人认为合理地的改变选项将会使mysqld运行得如火箭般高效。如果你也是那么想的，我不得不让你失望了：不好的查询写法仍会耗尽服务器资源。并且你可能在重启服务器后仅仅感受到几个小时的服务器高性能，然后它就又变慢了，因为每个查询都需要消耗很多的资源并且你的缓存将会充满。有时候，服务器会被数以百万计的查询淹没，需要更多的资源。

然后，优化每个查询也不是一个好选择。有些请求很少调用，所以没有必要在这些查询上浪费人力。还有的查询可能查询表中的所有行，这些查询就没有必要去尝试优化了。

我一般推荐“混合”的优化模式。先优化服务器选项，特别注意你使用的存储引擎相关的选项，然后优化查询。当优化完重要的查询后，回头再检查服务器选项，考虑是否有进一步的优化空间，然后再继续优化剩下的查询，反复如此，直到你对性能满意。

你也可以从自己的应用程序中最慢的查询开始或者找到那些可以通过简单的优化获得显著提升的查询，然后优化服务器选项。参考之前展示的状态变量，第6章将详细介绍它们。

最后同样重要的是：在性能优化中参考大量的信息以形成你自己的策略。