MySQL性能调优

欢迎来到MySQL性能调优领域。 这个世界有时看起来似乎被黑魔法和运气所支配，但是希望这本书可以帮助您以结构化的方式去工作，并有条不紊地以更好的方式去工作。

本章通过讨论整个堆栈以及监视和基于数据执行操作的重要性，向您介绍MySQL性能调优。 由于本书主要是关于查询的，因此在结束本章之前，先回顾一下查询的生命周期。

技巧 如果您需要一个测试实例，无论是阅读本书时，还是为了处理工作中的问题，云都可以是你的朋友。它允许您快速启动测试实例。如果您只需要一个小实例，例如，在本书中探索示例，您甚至可以使用免费实例，例如通过 Oracle 云的免费套餐（仍然需要注册和信用卡）：https://mysql.wisborg.dk/oracle_cloude_free_tier.

考虑整个堆栈

调查性能问题时，必须考虑系统的所有部分，从最终用户到应用程序再到 MySQL。当有人报告应用程序速度很慢，并且您知道 MySQL 是应用程序的核心部分时，很容易得出”MySQL 速度慢”的结论。但是，这将排除造成性能不佳的许多潜在原因。

当应用程序需要查询结果或需要在 MySQL 中存储数据时，它会通过网络将请求发送到 MySQL，为了执行请求，MySQL 与操作系统交互并使用主机资源（如内存和磁盘）。一旦请求的结果准备就绪，它将通过网络将通信回应用程序。图 1-1 说明了这一点。

请求的结果准备就绪后，通过网络将其传递回应用程序。 如图1-1所示。 MySQL性能调优 - 图1

这个金字塔是一个非常简化的图片，它把应用程序以外的一切都删除了，而应用程序又可能与用户通信并使用自己的资源。 网络通信还涉及主机和操作系统。

为了说明图层如何交互，请考虑一个实际示例。一个MySQL用户报告了MySQL遇到临时停顿的问题。在Linux上使用 perf 工具的一项调查显示，之所以出现停滞，是因为内存非常分散，主要是由 I/O 缓存引起的。当您通过网络提交数据时，Linux 会请求连续的内存部分（使用 kmalloc），但由于内存严重碎片化，Linux 必须先对内存进行碎片整理（压缩）。在进行这种压缩的同时，包括MySQL在内的所有内容都停滞了，并且在最坏的情况下，它长达一分钟（服务器具有大量可用于I/O缓存的内存），因此造成了严重的影响。 在这种情况下，更改MySQL配置以使用直接I/O可以解决此问题。尽管这是一个极端的情况，但值得注意的是，交互会导致令人惊讶的拥塞点。

一个更直接的实际示例是使用框架来生成查询的应用程序。框架中存在一个错误，这意味着对大型表的查询省略了WHERE子句。这意味着一系列的问题，包括应用程序重试查询，并在几秒钟内完成50个查询副本（由于数据最终已被读入缓冲池，使得最后一个查询的执行速度比第一个查询快得多），最终导致一系列问题的解决； 将大量数据发送回应用程序，导致网络过载，并且应用程序内存不足。

本书重点介绍 MySQL 和影响查询的各个方面，但不要忘记系统的其余部分。这包括您监视系统的时间。

监控方式

如果你只从阅读这本书中拿出一件事，那么让监控对于维持一个健康的系统至关重要。你所做的一切都应该围绕着监控。在某些情况下，通过专用的监视解决方案进行监视可提供您需要的所有数据，在其他情况下，您需要进行临时观察。

您的监视应使用多种信息来源。 这些包括但不限于

  • Performance Schema，包括从低级互斥量到查询和事务度量的信息。这是用于查询性能调优的最重要的信息源。 sys schema 提供了一个方便的界面，特别是用于即席查询。
  • Information Schema，其中包括模式信息，InnoDB统计信息等。
  • SHOW语句，例如，包含来自InnoDB的信息以及详细的引擎统计信息。
  • 慢查询日志，可以记录符合某些条件的查询，例如花费比预定义阈值更长的时间的查询。
  • EXPLAIN语句返回查询执行计划。这是一个无价的工具，用于调查为什么由于缺少索引，查询以次优方式编写或MySQL选择次优方式执行查询而导致查询无法正常运行的原因。 EXPLAIN语句在调查特定查询时通常以临时方式使用。
  • 操作系统指标，例如磁盘利用率，内存利用率和网络利用率。不要忘记简单的指标（例如可用存储量），因为存储空间不足会导致中断。

这些信息来源在本书中都有讨论和使用。

在整个性能调优过程中使用监视时，您可以验证问题是什么，找到原因，并证明您已经解决了问题。在处理解决方案时，了解查询的生命周期也很有用。

查询的生命周期

执行查询时，它会在查询结果回到应用程序或客户端之前执行几个步骤。每个步骤都需要时间，而且本身可能是由多个子部分组成的复杂操作。

查询生命周期的简化概述见图 1-2。在实践中，涉及更多的步骤，如果您安装了诸如查询重写器之类的插件，它将添加它们自己的步骤。但是，该图确实涵盖了基本步骤，稍后将更详细地介绍几个步骤

MySQL性能调优 - 图2

MySQL 服务器可分为两层。例如，SQL 层处理连接并准备语句以执行。实际数据由存储引擎存储，这些引擎作为插件实现，因此相对容易实现处理数据的不同方法。本书中考虑的主要存储引擎是 InnoDB，它是完全事务性的，并且对高并发工作负载具有很好的支持。另一个存储引擎的示例是 NDBCluster，它也是事务性的，用作MySQL NDB集群的一部分。

当应用程序需要执行查询时，第一件事是创建连接（这不包括在图中，因为连接可以重复使用以执行更多查询）。当查询到达时，MySQL 将分析它。这包括将查询拆分为token，因此查询类型是已知的，并且有查询所需的表和列的列表。在下一步中，需要此列表，检查用户是否具有执行查询所需的权限。

此时，查询已达到确定如何执行查询的重要步骤。这是优化器的工作，涉及重写查询以及确定访问表的顺序以及要使用的索引。

实际执行步骤包括从存储引擎层请求数据。存储引擎本身可能很复杂。对于 InnoDB，它包括用于缓存数据和索引的缓冲池、重做和撤销日志、其他缓冲区以及表空间文件。如果查询返回行，则这些行通过 SQL层 从存储引擎发送回应用程序。

在查询调优中，最重要的步骤是优化器和执行步骤，包括存储引擎。本书中大部分信息直接或间接地与这三部分有关。

总结

本章触及了性能调优的表面，并让你为本书的其余部分的旅程做好准备。关键要点是，您需要考虑从最终用户到主机和操作系统的低级详细信息，监视在性能调优中是绝对必须的。执行查询包括每个步骤，其中优化器和执行步骤是您将在本书中学到的最多步骤。

下一章将仔细研究一种有助于解决性能问题的方法。