性能优化简介

问 10 个人关于性能的问题，可能会得到 10 个不同的回答，比如每秒查询次数、CPU 利用率、可扩展性之类。这其实也没有问题，每个人在不同场景下对性能有不同的理解，单本章将给性能一个正式的定义。

将性能定义为：完成某件任务所需要的时间度量，换句话说，性能即响应时间。这是个非常重要的原则。我们通过任务和时间而不是资源来衡量性能。数据库服务器的目的是执行 SQL 语句，所以关注他的任务是查询或则语句，如 SELECT、UPDATE、DELETE 等（本书不会严格的区分 DDL 和 DML，只关心执行命令的速度，本书将以「查询」一词泛指所有发送给服务器的命令）。

数据库服务器的性能用 查询的响应时间来度量，单位是每个查询花费的时间

另外一个问题：什么是优化？暂时不讨论它，而是假设性能优化就是在一定的工作负载下尽可能的降低响应时间。

很多人对此很迷茫。假如你认为性能优化时降低 CPU 利用率，那么可以减少对资源的使用。但这是一个陷阱，资源是用来消耗并用来工作的，所以有时候消耗更多的资源能加快查询速度。很多时候将使用老版本 InnoDB 引擎的 MySQL 升级到新版本后，CPU 利用率会上升的很厉害，这并不达标性能出现了问题，反而说明新版本的 InnoDB 对资源的利用率上升了。查询的响应时间则更能体现升级后的性能是不是变得更好。版本升级有时候会带来一些 bug，比如不能利用某些索引从而导致 CPU 利用率上升。 CPU 利用率只是一种现象，而不是很好的可度量目标。

同样，如果把性能优化仅仅看成是 提升每秒查询量，这其实 只是吞吐量优化。吞吐量的提升可以看做性能优化的副产品。对查询的优化可以让服务器每秒执行更多的查询，因为每条查询执行的时间更短了（吞吐量的定义是单位时间内的查询数量，这正好是我们对性能的定义的倒数）

如果 目标是降低响应时间，那么就需要理解为什么服务器执行查询需要这么多时间，然后减少或则消除那些对获得查询结果来说不必要的工作。也就是说，先要搞清楚时间花在哪里。这就引申出优化的第二个原则：无法测量就无法有效的优化。所以第一步应该测量时间花在什么地方。

我们观察到，很多人在优化时，都将精力放在修改一些东西上，很少去进行精确的测量。我们的做法完全相反，将花费非常多，甚至 90% 的时间来测量响应时间花在哪里。如果通过测量没有找到答案，那要么是测量的方法错了，要么是测量得不够完美。如果测量了系统中完整而且正确的数据，性能问题一般都能暴露出来，对症下药的解决方案也就比较明了。测量是一项很有挑战性的工作，并且分析结果也同样有挑战性，测出时间花在哪里，和知道为什么花在哪里，是两码事。

前面到需要合适的测量范围，是说 只测量需要优化的活动。有两种比较常见的情况会导致不合适的测量：

• 在错误的时间启动和停止测量
• 测量的是聚合后的信息，而不是目标活动本身

例如，一个常见的错误是先查看慢查询，然后又去排查整个服务器的情况来判断问题在哪里。如果确认有慢查询，那么就应该测量慢查询，而不是测量整个服务器。测量的应该是从慢查询的开始到结束的时间，而不是查询之前或查询之后的时间。

完成一项任务所需要的时间可以分成两部分：执行时间和等待时间。

• 如果要优化任务的执行时间

  最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或则提升子任务的效率。

• 优化任务的等待时间

  则相对要复杂一些，因为等待有可能是由其他系统间接影响导致的，任务之间也可能由于争用磁盘或则 CPU 负载而相互影响。

根据时间是花在执行还是等待上的不同，诊断也需要不同的工具和技术。

那么如何确认哪些子任务是优化的目标呢？这个时候性能剖析就可以派上用场了。

如何判断测量是正确的？

如果测量是如此重要，那么测量错了会有什么后果？实际，测量经常都是错误的。对数量的测量并不等于数量本身。测量的错误可能很小，跟实际情况区别不大，但错的就是错的。所以这个问题应该是：测量到底有多么不准确？这个问题不是本书的主题，但是要意识到使用的是测量数据，而不是其所代表的的实际数据。通常来说，测量的结果也可能有多种模糊的表现，这可能导致推断出错误的结论。

性能优化简介-小结

性能定义为：完成某件任务所需要的时间度量，

数据库服务器的性能用 查询的响应时间来度量，单位是每个查询花费的时间

要降低响应时间，就要搞清楚时间花在哪里了，因为：无法测量就无法有效的优化

在优化时，要将精力放在 测量时间花在哪里，而不是一上来就去调整各种参数，同时找到合适的测量范围：只测量需要优化的活动，而不是胡乱的测。

完成一项任务所需要的时间可以分成两部分：执行时间和等待时间。往往在优化等待时间时很复杂，因为这个等待情况多种多样，有可能是依赖第三方导致的。根据时间是花在执行还是等待上的不同，诊断也需要不同的工具和技术。

那么如何确认哪些子任务是优化的目标呢？这就需要性能剖析。

通过性能剖析进行优化

一旦掌握并实践面向响应时间的优化方法，就会发现需要不断的对系统进行性能剖析（profiling）

性能剖析是 测量和分析时间花费在哪里 的主要方法。一般有两个步骤：

• 测量任务所花费的时间
• 对结果进行统计和排序，将重要的任务排到前面

性能剖析工具的工作方式基本相同。在任务开始时启动计时器，在任务结束时停止计时器，然后用结束时间去减去启动时间得到响应时间。也有些工具会记录任务的父任务。这些结果数据可以用来绘制调用关系图，但对于我们来说更重要的是，可以将相似的任务分组并进行汇总。可以帮助对那些分到一组的任务做更复杂的统计分析，但至少需要知道每一组有多少任务，并计算出总的响应时间。通过性能剖析报告（profile report）可以获得需要的结果。性能剖析报告会累出所有任务列表。每行记录一个任务，包括任务名、任务的执行时间、任务的消耗时间、任务的平均执行时间，以及该任务执行时间占全部时间的百分比。性能剖析报告会按照任务的消耗时间进行降序排序。

为了更好的说明，这里举一个对整个数据库服务器工作负载的性能剖析的例子，主要输出的是各种类型查询和执行查询的时间。这是从整体的角度来分析响应时间，后面会演示其他角度的分析结果。下面的输出使用 Percona Toolkit 中的 pt-query-digest 分析得到的结果。

只显示了一部分，根据总响应时间进行排名，只包括剖析所需要的最小列组合。每一行都包括了查询的响应时间和占总时间比、查询的执行次数、单词执行的平均响应时间，以及该查询的摘要。

通过个性能剖析可以很清楚的看到 每个查询相互之间的成本比较，以及每个查询占总成本的比较。在这个例子中，任务指的就是查询，实际在分析 MySQL 的时候经常都指查询

我们将实际的讨论两种类型的性能剖析：

• 基于执行时间的分析

  研究的是什么任务的执行时间最长？

• 基于等待的分析

  分析的是判断任务在什么地方被阻塞的时间最长？

如果任务执行时间长是因为消耗了太多的资源且大部分时间花费在执行上，等待的时间不多，这种情况下基于等待分析作用就不大。反之亦然。如果不能确认问题是出在执行还是等待上，那么两种方式都要试试。

事实上，当基于执行时间的分析发现一个任务需要花费太多时间的时候，应该深入去分析一下，可能会发现某些执行时间实际上是在等待。例如，上面简单的性能剖析的输出显示表 invitesNew 上的 SELECT 查询花费了大量时间，如果深入研究，则可能发现时间都花费在等待 I/O 完成上。

在系统进行性能剖析前，必须先要能进行测量，这需要系统可测量化的支持。可测量的系统一般会有多个测量点可以捕获并收集数据，但实际系统很少可以做到可测量化。大部分系统都没有多少可测量点，即使有也只提供一些活动的计数，而没有活动花费的时间统计。MySQL 就是一个典型的例子，知道 5.5 版本才第一次提供了 Performance Schema ，其中有一些是基于时间的测量点（5.5 的 也没有提供查询级别的细节数据，5.6 才提供），而版本 5.1 及之前没有任何基于时间的测量点。能够从 MySQL 收集到的服务器操作的数据大多是 show status 计数器的形式，这些计数器统计是某种活动发生的次数。这也是我们最终决定创建 Percona Server 的主要原因，Percona Server 从版本 5.0 开始提供更详细的查询级别的测量点。

虽然理想的性能优化技术依赖于更多的测量点，但幸运的是，即使系统没有提供测量点，也还有其他办法可以展开优化工作。因为可以从外部去测量系统，如果测量失败，也可以根据对系统的了解做出一些靠谱的猜测。但是这么做的时候，一定要记住，不管是外部测量还是猜测，数据都不是百分之百的准确，这是系统不透明所带来的风险。

举个例子：在 Percona Server 5.0 中，慢查询日志揭露了一些性能低下的原因，如磁盘 I/O 等待或则行级锁等地。如果日志中显示一条查询花费 10 秒，其中 9.6 秒在等待磁盘 I/O ，那么追究其他 4% 的时间花费在哪里就没有意义，磁盘 I/O 才是最重要的原因。

小结

性能剖析是 测量和分析时间花费在哪里 的主要方法。一般有两个步骤：

• 测量任务所花费的时间
• 对结果进行统计和排序，将重要的任务排到前面

性能剖析最直观的就是下面这个慢查询日志的统计分析数据，每行记录一个任务，包括任务名、任务的执行时间、任务的消耗时间、任务的平均执行时间，以及该任务执行时间占全部时间的百分比。

通过个性能剖析可以很清楚的看到 每个查询相互之间的成本比较，以及每个查询占总成本的比较。在这个例子中，任务指的就是查询，实际在分析 MySQL 的时候经常都指查询

要性能剖析，最理想的情况就是系统支持基于时间的测量化，如上面的慢查询分析。如果没有提供，则只能通过系统外部去测量，和猜测数据。

理解性能剖析

MySQL 的性能剖析（profile）将最重要的任务展示在前面，但有时候没显示出来的信息也很重要。不幸的是，尽管性能剖析输出了排名、总计和平均值，但还是有很多需要的信息是缺失的。如：

• 值的优化的查询（worthwhile query）

  性能剖析不会自动给出哪些查询值的花时间去优化。这里强调两点：

  • 一些只占总响应时间比重很小的查询是不值的优化的

    根据阿姆达尔定律，对一个占总响应时间不超过 5% 的查询进行优化，无论如何努力，收益也不会超过 5%

  • 如果花费了 1000 美元去优化一个任务，单业务的收入么有任何增加，那么可以说反而导致业务被逆优化了 1000 美元

  如果优化的成本大于收益，就应该停止优化。

• 异常情况

  某些任务即使没有出现性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验。因为执行频率低，所以总的响应时间占比并不突出

• 未知的未知

  一款好的性能剖析工具会显示可能的「丢失的时间」。

  丢失的时间：指任务的总时间和实际测量的时间之间的差。

  例如：处理器 CPU 时间是 10 秒，而剖析到的任务总时间是 9.7 秒，那么有 300 毫秒的丢失时间。这可能是有些任务没有被测量到，也有可能是测量的误差和精度问题的缘故。所以如果发现了类似的问题，要引起重视。

• 被掩藏的细节

  性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的，它会隐藏很多信息，而且无法表达全部的情况。

  在前面其实已经说到过的，包括现实中的人均工资一样，隐藏了很多信息。

好的工具可以自动获得这些信息。实际上 pt-query-digest 就在剖析的结果里面包含了很多这类细节信息，并且输出在剖析报告中。

在前面的性能剖析的例子中，有一个重要的缺失，无法在更高层次的堆栈中进行交互式的分析。也就是只显示了单独的一个查询语句，无法获得它的上下文有哪些语句。可以通过一些办法解决，比如给查询加上特殊的注释作为标签，可以标明其来源并据此做聚合，也可以在应用层面增加更多的测量点，这是下一节的主题。