MySQL8 查询性能调优教程（一）
一、MySQL 性能调优
欢迎来到 MySQL 性能调优的世界。这是一个有时似乎被黑魔法和运气主宰的世界，但希望这本书可以帮助你以一种结构化的方式有条不紊地工作，以达到更好的表现。
本章通过讨论整个堆栈以及监控和基于数据采取行动的重要性，向您介绍了 MySQL 性能调优。因为这本书主要是关于使用查询，所以在结束本章之前回顾一下查询的生命周期。
Tip
如果您需要一个测试实例，无论是在阅读本书时还是在工作中解决问题时，云都可以成为您的朋友。它允许您快速启动一个测试实例。如果你只是需要一个小的实例，例如，探索本书中的例子，你甚至可以使用免费的实例，例如通过甲骨文云的免费层(仍然需要注册和信用卡): https://mysql.wisborg.dk/oracle_cloude_free_tier 。
考虑整个堆栈
当您研究性能问题时，重要的是您要考虑系统的所有部分，从最终用户到应用再到 MySQL。当有人报告说应用很慢，而你知道 MySQL 是应用的核心部分，那么就很容易得出“MySQL 很慢”的结论。然而，这将排除性能不佳的大量潜在原因。
当应用需要查询的结果或者需要在 MySQL 中存储数据时，它通过网络向 MySQL 发送请求，为了执行请求，MySQL 与操作系统进行交互，并使用内存和磁盘等主机资源。一旦请求的结果准备好了，就会通过网络传回给应用。如图 1-1 所示。
图 1-1
围绕 MySQL 的堆栈
金字塔是一个非常简化的图形，它忽略了应用之外的一切，而应用可能会与用户通信并使用自己的资源。通过网络通信还涉及主机和操作系统。
为了说明这些层如何交互，考虑一个真实世界的例子。一个 MySQL 用户报告了 MySQL 遇到暂时停止的问题。使用 Linux 上的perf工具进行的一项调查显示，出现停顿是因为内存碎片过多，这主要是由 I/O 缓存引起的。当您通过网络提交数据时，Linux 请求一块连续的内存(使用kmalloc)，但是由于严重的内存碎片，Linux 必须首先对内存进行碎片整理(压缩)。当这种压缩发生时，包括 MySQL 在内的所有东西都停止了，而且在最糟糕的情况下，这需要一分钟(服务器有大量内存可用于 I/O 缓存)，这导致了严重的影响。在这种情况下，将 MySQL 配置更改为使用直接 I/O 解决了这个问题。虽然这是一个极端的情况，但是值得记住的是，交互可能会导致令人惊讶的拥塞点。
一个更简单的真实例子是一个使用框架生成查询的应用。框架中有一个 bug，这意味着针对大型表的查询省略了一个WHERE子句。这意味着一连串的问题，包括应用重试查询，并在几秒钟内完成 50 个查询副本(因为数据最终被读入缓冲池，使得最后一个查询的执行速度比第一个查询快得多)，以及将大量数据发送回应用，导致网络过载和应用耗尽内存。
这本书主要关注 MySQL 和影响查询的方面，但是不要忘记系统的其他部分。这包括当你监控你的系统。
监控
如果你从这本书里只带走了一样东西，那么就让它成为监控对于保持一个健康的系统是至关重要的。你所做的一切都应该围绕着监控。在某些情况下，通过专用监控解决方案进行监控可以提供您需要的所有数据，而在其他情况下，您需要进行特别的观察。
您的监控应该使用几个信息源。这些包括但不限于
-
性能模式包括从低级互斥到查询和事务度量的信息。这是查询性能调优的最重要的信息源。
sys模式提供了一个方便的接口，特别是对于特定的查询。 -
信息模式，包括模式信息、InnoDB 统计信息等等。
-
SHOW语句，例如，包括来自 InnoDB 的信息以及详细的引擎统计数据。 -
慢速查询日志，可以记录符合特定条件的查询，例如耗时超过预定义的阈值。
-
返回查询执行计划的
EXPLAIN语句。这是一个非常有价值的工具，可以用来研究为什么一个查询由于缺少索引、查询以次优的方式编写或者 MySQL 选择了次优的方式来执行查询而表现不佳。在调查特定的查询时，EXPLAIN语句通常以特别的方式使用。 -
操作系统指标，如磁盘利用率、内存利用率和网络利用率。不要忘记一些简单的指标，如可用存储量，因为存储空间不足会导致停机。
这些信息源在本书中都有讨论和使用。
当您在整个性能调优过程中使用监控时，您可以验证问题是什么，找到原因，并证明您已经解决了问题。在处理解决方案时，了解查询的生命周期也很有用。
查询的生命周期
当您执行一个查询时，在查询结果返回到应用或客户端之前，它会经历几个步骤。每一步都需要时间，并且本身可能是由几个子部分组成的复杂操作。
在图 1-2 中可以看到查询生命周期的简化概述。在实践中，涉及到更多的步骤，如果你安装插件，如查询重写器，它会添加自己的步骤。然而，该图确实涵盖了基本步骤，其中几个步骤将在后面更详细地介绍。
图 1-2
基本查询生命周期
MySQL 服务器可以分为两层。例如，SQL 层处理连接并为执行准备语句。实际数据是由存储引擎存储的，这些存储引擎是作为插件实现的，这使得实现不同的数据处理方式变得相对容易。主要的存储引擎——也是本书将考虑的唯一一个——是 InnoDB，它是完全事务性的，对高并发性工作负载有很好的支持。另一个存储引擎的例子是 NDBCluster，它也是事务性的，是 MySQL NDB 集群的一部分。
当应用需要执行一个查询时，首先要做的是创建一个连接(图中没有包括这个连接，因为这个连接可以被重用来执行更多的查询)。当查询到达时，MySQL 解析它。这包括将查询拆分成标记，因此查询类型是已知的，并且存在查询所需的表和列的列表。在下一步检查用户是否有执行查询的必要权限时，需要这个列表。
此时，查询已经到了决定如何执行查询的重要步骤。这是优化器的工作，包括重写查询以及确定访问表的顺序和使用哪些索引。
实际的执行步骤包括从存储引擎层请求数据。存储引擎本身可能很复杂。对于 InnoDB，它包括一个缓冲池，用于缓存数据和索引、重做和撤消日志、其他缓冲区以及表空间文件。如果查询返回行，这些行将通过 SQL 层从存储引擎发送回应用。
在查询调优中，最重要的步骤是优化器和执行步骤，包括存储引擎。本书中的大部分信息都与这三个部分直接或间接相关。
摘要
本章只触及了性能调优的皮毛，并为您准备了本书其余部分的旅程。关键要点是，您需要考虑从最终用户到主机和操作系统的底层细节的整个体系，并且在性能调优中，监控是绝对必要的。执行查询包括几个步骤，其中优化器和执行步骤是您将在本书中学到最多的。
下一章将更仔细地研究一种对解决性能问题有用的方法。
二、查询调优方法
解决问题有几种方法。在极端情况下，你可以一头扎进去，尝试做一些改变。虽然这看起来像是节省时间，但更多的时候，它只会导致沮丧，即使当改变看起来起作用时，您也不能确定您是否真正解决了根本问题，或者问题只是暂时变好了。
相反，建议从方法上进行工作，通过分析和使用监控来确认变更的效果。本章将向您介绍一种在解决 MySQL 问题时非常有用的方法，重点是性能调优。首先介绍该方法中的步骤。然后，本章的其余部分将更详细地讨论每个步骤，以及为什么花尽可能多的时间积极工作是重要的。
Note
此处描述的方法基于 Oracle 技术支持中用于解决客户报告的问题的方法。
概观
MySQL 性能调优可以被视为一个永无止境的过程，在这个过程中，随着时间的推移，使用迭代方法来逐步提高性能。显然，有时会出现像查询这样的特定问题，需要半个小时才能完成，但是一定要记住，性能不是二元状态，因此有必要知道什么是足够好的性能。否则，你将永远无法完成哪怕一项任务。
图 2-1 显示了如何描述性能调整生命周期的示例。循环从左上角开始，由四个阶段组成，其中第一个阶段是验证问题。
图 2-1
性能调整生命周期
当您遇到性能问题时，第一阶段是验证问题是什么，包括收集问题的证据，并定义认为问题得到解决的要求是什么。
第二阶段是确定性能问题的原因，第三阶段是确定解决方案。最后，在第四阶段，您实施解决方案。解决方案的实施应包括验证更改的效果。
Tip
这种循环既适用于危机期间的救火工作，也适用于积极主动的工作。
然后，您就可以从头开始了，要么进行第二次迭代来进一步提高您刚才看到的问题的性能，要么您可能需要处理第二个问题。也有可能在两个周期之间会有一段很长的时间。
验证问题
在你试图确定问题的原因和解决方案之前，重要的是你要清楚你要解决的是什么问题。仅仅说“MySQL 很慢”是不够的——这是什么意思？一个具体的问题可能是“前端网页的第二部分中使用的查询需要 5 秒钟”或者“MySQL 每秒只能支持 5000 个事务”你越具体，解决问题的机会就越大。
问题的定义还应该包括验证问题是什么。问题最初看起来是什么和真正的问题是什么是有区别的。验证问题可能很简单，只需执行一个查询并观察查询是否真的像声称的那样耗时，或者可能涉及到检查您的监控。
准备工作还应该包括从您的监控中收集基线，或者运行说明问题的数据收集。没有基线，您可能无法在故障诊断结束时证明您已经解决了问题。
最后，您需要决定性能调优的目标是什么。引用斯蒂芬·R·科维
的高效人士的 7 个习惯从心里的目的开始。
慢速查询运行速度的最低可接受目标是什么，或者所需的最小事务吞吐量是多少？这将确保你在做出改变时知道目标是否已经达到。
当问题得到明确定义和验证后，您可以开始分析问题并确定原因。
确定原因
第二阶段是确定性能不佳的原因。确保你思想开放，考虑整个问题，这样你就不会在某个方面看不到自己，而这个方面与问题没有任何关系。
当你认为你知道原因时，你还需要论证为什么那是原因。您可能有一个EXPLAIN语句的输出，清楚地显示查询执行了全表扫描，因此这可能是原因，或者您可能有一个图形显示 InnoDB 重做日志已满 75%,因此您可能有一个异步刷新，导致临时性能问题。
找到原因通常是调查中最困难的部分。一旦知道了原因，你就可以决定一个解决方案。
确定解决方案
为您调查的问题确定解决方案需要两个步骤。第一步是找到可能的解决方案；第二，你必须选择实现哪一个。
当你寻找可能的解决方案时，做一次头脑风暴会很有用，在头脑风暴中你可以写下你能想到的所有想法。重要的是，你不要强迫自己只考虑一个狭窄的领域，这个领域的根本原因可能经常在不同的领域找到解决方案。一个例子是上一章提到的由于内存碎片造成的延迟，解决方案是改变 MySQL 的配置，使用直接 I/O 来减少操作系统 I/O 缓存的使用。您还应该记住短期解决方案和长期解决方案，因为如果需要重新启动或升级 MySQL、更换硬件或类似的事情，并不总是能够立即实现完整的解决方案。
Tip
一个有时不受重视的解决方案是升级 MySQL 或操作系统以获得新功能。但是，您当然需要进行仔细的测试，以验证您的应用在新版本中运行良好，特别要注意优化器是否有任何导致查询性能下降的更改。
确定解决方案的第二部分是选择最有效的候选解决方案。为了做到这一点，你必须为每一个解决方案辩护，为什么它有效，利弊是什么。在这一步中，重要的是对自己诚实，并仔细考虑可能的副作用。
一旦你对所有可能的解决方案有了很好的理解，你就可以选择哪一个来进行。您也可以选择一个解决方案作为临时缓解措施，同时开发一个更可靠的解决方案。无论哪种情况，下一阶段都是实现解决方案。
实施解决方案
您通过一系列步骤来实现解决方案，在这些步骤中，您定义行动计划、测试行动计划、优化行动计划等等，直到您最终将解决方案应用到您的生产系统。重要的是不要仓促行事，因为这是发现解决方案问题的最后机会。在某些情况下，测试还可能表明您需要放弃该解决方案，并返回到前一阶段，选择一个不同的解决方案。图 2-2 说明了实施解决方案的工作流程。
图 2-2
实施解决方案的工作流程
你选择了解决方案，并为其制定了行动计划。在这里，非常具体是很重要的，这样您就可以确保您测试的行动计划也是您最终在生产系统上应用的计划。写下将要使用的确切命令和语句会很有用，这样您就可以复制和粘贴它们，或者将它们收集到脚本中，这样它们就可以自动应用。
然后，您需要在测试系统上测试行动计划。重要的是，它尽可能地反映生产情况。测试系统上的数据必须代表您的生产数据。实现这一点的一种方法是复制生产数据，还可以选择使用数据屏蔽来避免将个人详细信息和信用卡信息等敏感信息复制到生产系统之外。
Tip
MySQL 企业版订阅(付费订阅)包含一个数据屏蔽特性: www.mysql.com/products/enterprise/masking.html 。
测试应该验证解决方案解决了问题，并且没有意外的副作用。需要什么样的测试取决于您试图解决的问题和建议的解决方案。如果您有一个缓慢的查询，它涉及到在实现解决方案之后测试查询的性能。如果修改一个或多个表上的索引，还必须验证这会如何影响其他查询。在实现解决方案之后，您可能还需要对系统进行基准测试。在所有情况下，您都需要与您在问题验证期间收集的基线进行比较。
第一次尝试可能不像预期的那样成功。通常，只需要对行动计划进行一些改进，其他时候，您可能必须完全放弃提议的解决方案，并返回到上一阶段，选择另一个解决方案。如果建议的解决方案部分解决了问题，您也可以选择将它应用到生产系统中，并从头开始评估如何继续改进性能。
当您对测试显示解决方案有效感到满意时，您可以将它应用到试运行系统，如果一切仍然有效，还可以应用到生产系统。一旦你这样做了，你需要再次验证它的工作。无论您在建立一个代表生产系统的测试系统时有多小心，由于这样或那样的原因，解决方案都有可能在生产中不能完全按照预期工作。本书作者遇到的一种可能性是，本质上随机的索引统计是不同的，因此在生产系统上应用解决方案时，更新索引统计的ANALYZE TABLE语句是必要的。
如果该解决方案有效，您应该收集一个新的基线，用于将来的监控和优化。如果该解决方案证明不起作用，您需要决定如何继续，要么回滚更改并寻找新的解决方案，要么进行新一轮的故障排除并确定该解决方案不起作用的原因并应用第二个解决方案。
主动工作
性能调优是一个永无止境的过程。如果你有一个基本上健康的系统，大部分的工作将是积极主动的，在你工作的地方预防紧急情况，在那里紧急程度相对较低。这不会给你的工作带来很多关注，但会让你的日常生活压力更小，用户会更开心。
Note
这个讨论在某种程度上是基于斯蒂芬·R·科维的《高效能人士的 7 个习惯》中的习惯 3“把重要的事情放在第一位”。
图 2-3 展示了如何将你的任务按照紧急程度和重要性进行分类。紧急任务通常会引起其他人的注意，而其他任务可能很重要，但只有在没有及时完成的情况下才会显现出来，所以它们会突然变得紧急。
图 2-3
根据紧急程度和重要性对任务进行分类
最容易分类的任务是那些与危机相关的任务，如生产系统停机，公司收入损失，因为客户无法使用产品或进行购买。这些任务既紧迫又重要。在这些任务上花费大量时间可能会让你觉得自己很重要，但这也是一种非常有压力的工作方式。
处理性能问题最有效的方法是处理重要但不紧急的问题。这是预防危机发生的主动工作，包括监控、在问题变得明显之前进行改进等等。这一类的一个重要任务也是准备，所以你准备好处理危机。例如，这可能是建立一个备用系统，在发生危机时可以故障转移到该系统，或者建立快速启动替换实例的过程。这有助于缩短危机的持续时间，使其回到重要但不那么紧急的类别。你花在这一类任务上的时间越多，通常你就越成功。
最后两类包括不太重要的任务。紧急但不重要的任务包括你无法重新安排的会议、其他人推掉的任务以及感觉到的(但不是真实的)危机。不紧急和不重要的任务包括行政任务和检查电子邮件。当然，这些任务中的一些对于你保住工作可能是必需的，也是重要的，但是对于保持 MySQL 的良好运行却并不重要。虽然这些类别中总会有必须处理的任务，但是最大限度地减少在这里花费的时间是很重要的。
避免处理不重要的任务的一部分包括您理解任务的重要性，例如，通过定义何时性能足够好，这样您就不会过度优化查询或吞吐量。在实践中，如果不重要的任务引起了组织中其他人的注意(这些往往是紧急的任务)，当然很难将它们推后，但重要的是，你要尽可能地将工作转移到重要但不紧急的任务上，以避免危机任务在以后接手。
摘要
本章讨论了一种可以用来解决 MySQL 性能问题(以及其他类型的问题)的方法。)以及积极工作的重要性。
当报告一个问题时，您开始验证问题是什么，并确定什么被认为已经解决了它。对于本质上开放式的绩效问题，知道什么是足够好是很重要的，否则你将冒着永远无法停止执行危机管理并回到主动工作的风险。
一旦你有了一个清晰的问题描述，你就可以着手确定原因；而一旦明确了原因，你就可以确定你想做什么来解决问题。最后一个阶段是实施解决方案，如果您最初选择的解决方案不起作用或者有不可接受的副作用，您可能需要重新考虑潜在的解决方案。因此，在尽可能真实的环境中测试解决方案非常重要。
本章的最后一部分讨论了花尽可能多的时间做积极主动的工作的重要性，这可以防止危机的发生，并帮助你在危机发生时做好准备。这将帮助你有一份压力较小的工作，并以更健康的状态管理数据库。
正如本章所讨论的，在将您的解决方案部署到您的生产系统之前，测试它的影响是非常重要的。下一章将介绍基准测试，重点是 Sysbench 基准测试。
三、Sysbench 基准测试
在将更改应用到生产系统之前，验证更改的影响是非常重要的。这既适用于修改查询这样的小变化，也适用于重构应用和模式以及 MySQL 升级这样的大变化。您可能认为最佳性能测试是基于您的生产模式和使用应用执行的相同查询的数据。然而，重新创建合适的工作负载并不总是像听起来那么简单，因此有时有必要使用标准的基准测试套件。
本章首先介绍了执行基准测试时的一些最佳实践，并概述了 MySQL 使用的一些最常见的基准测试和工具。然后将更详细地考虑最常用的基准 Sysbench。
最佳实践
安装一个基准程序并执行它是很容易的。难的是正确使用它。执行 MySQL 基准测试分享了一些性能调优的概念，第一点也是最重要的一点是，您需要以“知情的方式”工作。这意味着您必须很好地了解您的工具，并且清楚地定义测试的目标和成功标准。对于您的工具，您需要知道如何正确地使用它们，因为使用默认参数执行它们可能不会产生您想要的测试。
这与基准的目标是联系在一起的。你需要确定什么？例如，您可能想要验证更改某些配置变量的效果，在这种情况下，您必须确保您的测试已经设置好，以便对该区域进行测试。考虑一个选项，比如影响 InnoDB 写速度的innodb_io_capacity。如果您的基准是只读测试，那么改变innodb_io_capacity不会有任何影响。在这种情况下，您还需要确保一次只更改一件事情，并且只进行相对较小的更改——就像您在对生产系统进行更改时应该做的那样。否则，如果您同时更改几个设置，那么一些设置可能会对结果产生积极影响，而另一些则会产生消极影响，但是您无法确定哪些更改要保留，哪些要恢复。如果您进行大的更改，您可能会超过最佳值，因此您最终会放弃该更改，即使还有改进的空间。
在测试结束读取结果时，需要了解基准测试的是什么；否则，结果只是一个没有意义的数字。这还包括定义在测试过程中要调整哪些变量，至于一般的性能调优，限制变量的数量是很重要的，这样您就可以很容易地识别每个变量的影响。为了使结果有效，您还必须确保测试是可重复的，也就是说，如果您两次执行相同的测试，那么您会得到相同的结果。测试可重复的一个要求是您有一个定义良好的系统起始状态。
Tip
不要假设一个客户端就足以生成您想要的负载。需要多少客户机取决于并发查询的数量和您正在执行的基准。
这就引出了下一个重点。您的基准应该反映应用的工作负载。如果您的应用具有在线分析处理(OLAP)工作负载，那么使用在线事务处理(OLTP)基准测试来证明您的配置更改效果很好，或者如果您的应用是写入密集型的，那么证明您的只读性能很好，这些都没有帮助。
您可能认为设计基准的最佳方式是捕获生产中执行的所有查询，并将其作为基准重放。这肯定有一些优点，但也有挑战。收集所有执行的查询是很昂贵的，但是如果您已经启用了 MySQL 企业审计日志来进行审计，那么可以使用它。将生产数据复制到测试系统还可能存在数据隐私问题。最后，与当前的生产负载相比，很难扩展测试来改变数据集的大小(无论是为了使其更易于管理还是为了测试的增长)或增加测试工作负载。由于这些原因，通常有必要使用人工基准。
Tip
您可以使用 MySQL 企业审计日志(需要订阅)或通用查询日志(开销非常大)来捕获一段时间内的所有查询。这包括执行查询时的时间戳，因此您可以使用日志以相同的顺序和相同的并发性重放查询。但是，它要求您自己创建一个脚本来提取查询并执行它们。
下一点是关于基准测试结果，它也与前面的几点相关。当您有了基准测试的结果时，理解结果的含义是很重要的，并且不要因为结果看起来不正确就丢弃它们。因此，基准结果是“永远不会错的”；这是一些工作的结果。如果结果出乎意料，理解为什么会这样是很重要的。也许，您没有使用预期的参数，或者使用了与预期不同的表大小，但也可能是其他因素干扰了基准测试，或者是第三个因素。如果有什么东西干扰了基准测试，那么它是否也可能发生在生产中？如果可以，那么基准测试就非常重要，您需要决定如何在生产中处理这种情况。
为了了解基准测试期间发生了什么，监控 MySQL 和主机系统也很重要。一种选择是使用与生产系统相同的监控解决方案。然而，测试或开发系统上的基准测试与生产系统稍有不同，因为您通常对较高频率的采样感兴趣，但在基准测试期间持续时间较短，因此使用专门针对基准测试的专用监控解决方案会很有用。dim_STAT ( http://dimitrik.free.fr/ )就是这样一个选项，它是由 Dimitri Kravtchuk 开发的，他是 MySQL 的性能架构师，也是许多 MySQL 服务器基准测试的幕后推手。
总的来说，理解结果并不是一件简单的事情。您还需要注意的一件事是，如果出现临时停顿，在基准测试期间会发生什么。基准测试是阻止后续查询，还是继续提交查询？如果它停滞不前，那么随后的查询将会比实际情况下更快，因为用户不会仅仅因为积压而停止提交请求。
最后，基准测试通常会产生几个指标，所以您需要分析结果，因为它与您的系统最相关。例如，延迟或吞吐量是最重要的？还是对两者都有要求？或者你对第三种度量更感兴趣？
标准 TPC 基准
有一个几乎无止境的基准列表，但最终常用的都归结为少数几个测试。这并不意味着您不应该考虑其他基准；最后，重要的是基准测试能够满足您的需求。
最常用的标准基准由 TPC ( www.tpc.org/ )定义，随着硬件和软件的变化，旧的基准变得过于简单，新的基准也被设计出来。TPC 网站包含了对基准的详细描述和规范。表 3-1 总结了当前的企业 TPC 基准。
表 3-1
通用 TPC 基准
|名字
|
类型
|
描述
| | --- | --- | --- | | TPC-C 战式攻击机 | 联机事务处理 | 这可能是最经典的 TPC 基准测试，可以追溯到 1992 年。它模拟了一个批发供应商的查询，并使用了九个表。 | | TPC-DI(消歧义) | 数据集成 | 测试提取、转换和加载(ETL)工作负载。 | | TPC-DS(卢旺达问题国际法庭) | 决策支持 | 该基准测试包括数据仓库(星型模式)的复杂查询。 | | TPC-E 游戏 | 联机事务处理 | 这意味着用一个更复杂的模式和查询来代替 TPC-C，所以它对现代数据库更现实。它包括 33 个表。 | | TPC-H 导弹 | 决策支持 | 这是另一个常用于测试优化器特性的经典基准。它由 22 个复杂的查询组成，旨在模拟 OLTP 数据库的报告端。 | | TPC-VMS | 虚拟化 | 它使用 TPC-C、TPC-DS、TPS-E 和 TPC-H 基准来确定虚拟化数据库的性能指标。 |
这些标准基准的优点是，您更有可能找到实现它们的工具，并且可以与其他人获得的结果进行比较。
Tip
如果您想了解更多关于 TPC 基准测试以及如何以最佳方式执行数据库基准测试的信息，请考虑 Bert Scalzo 的书:数据库基准测试和压力测试 (Apress)、 www.apress.com/gp/book/9781484240076 。
与标准基准测试一样，也有一些通用的基准测试工具。
通用基准工具
实现一个基准绝非易事，所以在大多数情况下，最好使用预先存在的可以为您执行基准的基准工具。一些工具是跨平台的和/或可以使用几个不同的数据库系统，而另一些工具则更加具体。您应该选择一个实现您需要的基准并在您的生产系统上工作的平台。
表 3-2 总结了一些最常用的测试 MySQL 性能的基准工具。
表 3-2
与 MySQL 一起使用的通用基准
|基准
|
描述
|
| --- | --- |
| 二 | 这是最常用的基准，也是本章将重点介绍的。它具有针对 OLTP 工作负载的内置测试、非数据库测试(例如纯 I/O、CPU 和内存测试)等等。此外，最新版本支持定制工作负载。它是开源的，主要在 Linux 上使用。可以从 https://github.com/akopytov/sysbench 下载。 |
| DBT2 | DBT2 可用于使用订单系统(TPC-C)模拟 OLTP 工作负载。DBT2 也可用于自动化 Sysbench，可从 https://dev.mysql.com/downloads/benchmarks.html 获得。 |
| DBT3 | DBT3 实现了 TPC-H 基准，用于测试复杂查询的性能。这是 MySQL 优化器开发者最喜欢使用的测试之一，用来验证实现新的优化器特性后的性能。从 https://sourceforge.net/projects/osdldbt/ 可以得到 DBT3 的副本。 |
| HammerDB | HammerDB 工具是一个免费的跨数据库工具，支持 Microsoft Windows 和 Linux。它支持 TPC-C 和 TPC-H 基准，可从 https://hammerdb.com/ 获得。 |
| 数据库工厂 | Database Factory 是一个强大的 Microsoft Windows 基准测试工具，支持多种数据库和基准测试。它支持 TPC-H、TPC-C、TPC-D 和 TPC-E 基准测试等。是商用产品(可免费试用): www.quest.com/products/benchmark-factory/ 。 |
| ii 长凳 | iiBench 测试将数据插入数据库的速度，因此如果您经常需要接收大量数据，它会非常有用。可以从 https://github.com/tmcallaghan/iibench-mysql 下载。 |
| DVD 商店版本 3 | DVD 商店将样本 DVD 商店的数据与基准相结合。它可以生成任何给定大小的数据，标准大小为 10 MB、1 GB 和 100 GB。它也可用作一般测试数据，可从 https://github.com/dvdstore/ds3 下载。它基于旧的戴尔 DVD 商店数据库测试套件。 |
| mysqlslap | mysqlslap工具很特别，因为它包含在 MySQL 安装中。它可用于根据您选择的表生成并发工作负载。它是一个非常简单的工具，所以不能有太多的用途，但是很好用。mysqlslap的手册页可以在 https://dev.mysql.com/doc/refman/en/mysqlslap.html 找到。 |
MySQL 最常用的工具是 Sysbench，本章的其余部分将介绍它的安装和使用示例。
Sysbench 安装
因为 Sysbench 是一个开源工具，所以有几个可用的分支。MySQL 维护其中一个分支；但是，要获得最新功能的版本，建议使用 Alexey Kopytov 的 fork。(这也是 MySQL 性能架构师 Dimitri Kravtchuk 推荐的 fork。)本章中的例子都使用 Kopytov 的 fork 版本 1.0.17(但是注意输出中列出的版本是 1.1.0)，但是对于其他 Sysbench forks 来说例子是相似的，只要 fork 足够新，能够包含所演示的特性。
支持使用原生 Linux 包安装 Sysbench，在 macOS 上从 Homebrew 安装，或者自己编译。虽然使用原生包安装更简单，但通常自己编译会更好，因为这样可以确保针对 MySQL 8 开发库进行编译，并且可以在比可用包更多的平台上编译 Sysbench。
Tip
有关所有安装说明的详细信息，包括所需的依赖项和使用本机包，请参见 https://github.com/akopytov/sysbench 。Sysbench 1.0 中已不再支持 Microsoft Windows。目前还不知道是否会重新引入支持。如果您使用的是 Microsoft Windows，建议您通过 Windows Subsystem for Linux(WSL)(https://msdn.microsoft.com/en-us/commandline/wsl/about)安装 Sysbench，在这种情况下，本章中的说明只需稍加修改(取决于您选择的 Linux 发行版)。另一种方法是使用虚拟机，例如在 VirtualBox 中。
编译软件可能不再很常见，但幸运的是，编译 Sysbench 很简单。您需要下载源代码，然后配置构建，编译它，最后安装它。
在编译 Sysbench 之前，您需要安装一些工具。所需的具体工具取决于您的操作系统。详见项目 GitHub 页面的安装说明。例如，在 Oracle Linux 7 上:
shell$ sudo yum install make automake libtool \
pkgconfig libaio-devel \
openssl-devel
您还需要安装 MySQL 8 开发库。在 Linux 上，最简单的方法是从 https://dev.mysql.com/downloads/ 为您的 Linux 发行版安装 MySQL 存储库。清单 3-1 展示了在 Oracle Linux 7 上安装 MySQL 8 开发库的例子。
shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
...
Saving to: 'mysql80-community-release-el7-3.noarch.rpm'
100%[=================>] 26,024 --.-K/s in 0.006s
2019-10-12 14:21:18 (4.37 MB/s) - 'mysql80-community-release-el7-3.noarch.rpm' saved [26024/26024]
shell$ sudo yum install mysql80-community-release-el7-3.noarch.rpm
Loaded plugins: langpacks, ulninfo
Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================
Package
Arch Version
Repository Size
===========================================================
Installing:
mysql80-community-release
noarch el7-3
/mysql80-community-release-el7-3.noarch 31 k
Transaction Summary
===========================================================
Install 1 Package
Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql80-community-release-el7-3.noarc 1/1
Verifying : mysql80-community-release-el7-3.noarc 1/1
Installed:
mysql80-community-release.noarch 0:el7-3
Complete!
shell$ sudo yum install mysql-devel
...
Dependencies Resolved
===========================================================
Package Arch Version Repository Size
===========================================================
Installing:
mysql-community-client
x86_64 8.0.17-1.el7 mysql80-community 32 M
replacing mariadb.x86_64 1:5.5.64-1.el7
mysql-community-devel
x86_64 8.0.17-1.el7 mysql80-community 5.5 M
mysql-community-libs
x86_64 8.0.17-1.el7 mysql80-community 3.0 M
replacing mariadb-libs.x86_64 1:5.5.64-1.el7
mysql-community-libs-compat
x86_64 8.0.17-1.el7 mysql80-community 2.1 M
replacing mariadb-libs.x86_64 1:5.5.64-1.el7
mysql-community-server
x86_64 8.0.17-1.el7 mysql80-community 415 M
replacing mariadb-server.x86_64 1:5.5.64-1.el7
Installing for dependencies:
mysql-community-common
x86_64 8.0.17-1.el7 mysql80-community 589 k
Transaction Summary
===========================================================
Install 5 Packages (+1 Dependent package)
Total download size: 459 M
...
Complete!
Listing 3-1Installing the MySQL 8 development libraries
输出取决于您已经安装的内容。注意其他几个 MySQL 包，包括mysql-community-server，是如何作为依赖项被拉进来的。这是因为在这种情况下,mysql-community-devel包替换了另一个预先存在的包，从而触发了一系列的依赖关系更新。
Note
如果你安装了一个旧版本的 MySQL 或者 fork，所有相关的包都会被升级。为此，最好在可以自由替换包或者已经安装了正确的 MySQL 8 开发库的主机上编译 Sysbench。
您现在可以考虑 Sysbench 本身了。您可以选择克隆 GitHub 存储库或者下载 ZIP 文件形式的源代码。要克隆存储库，您需要安装git，然后使用git clone命令:
shell$ git clone https://github.com/akopytov/sysbench.git
Cloning into 'sysbench'...
remote: Enumerating objects: 14, done.
remote: Counting objects: 100% (14/14), done.
remote: Compressing objects: 100% (12/12), done.
remote: Total 9740 (delta 4), reused 5 (delta 2), pack-reused 9726
Receiving objects: 100% (9740/9740), 4.12 MiB | 2.12 MiB/s, done.
Resolving deltas: 100% (6958/6958), done.
带有源代码的 ZIP 文件可以从 GitHub 存储库中下载，例如，使用wget:
shell$ wget https://github.com/akopytov/sysbench/archive/master.zip
...
Connecting to codeload.github.com (codeload.github.com)|52.63.100.255|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: 'master.zip'
[ <=> ] 2,282,636 3.48MB/s in 0.6s
2019-10-12 16:01:33 (3.48 MB/s) - 'master.zip' saved [2282636]
或者，您可以使用浏览器下载 ZIP 文件，如图 3-1 所示。
图 3-1
在浏览器中从 GitHub 下载 Sysbench 源代码
点击下载 ZIP 文件，文件将被下载。下载源代码后，将其解压缩。
现在，您可以配置编译了。输入带有源代码的顶级目录。目录列表应类似于以下输出:
shell$ ls
autogen.sh COPYING Makefile.am rpm tests
ChangeLog debian missing scripts third_party
config install-sh mkinstalldirs snap
configure.ac m4 README.md src
使用清单 3-2 中所示的autogen.sh脚本后跟configure命令来完成配置。
shell$ ./autogen.sh
autoreconf: Entering directory `.'
...
parallel-tests: installing 'config/test-driver'
autoreconf: Leaving directory `.'
shell$ ./configure
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
...
===========================================================================
sysbench version : 1.1.0-74f3b6b
CC : gcc -std=gnu99
CFLAGS : -O3 -funroll-loops -ggdb3 -march=core2 -Wall -Wextra -Wpointer-arith -Wbad-function-cast -Wstrict-prototypes -Wnested-externs -Wno-format-zero-length -Wundef -Wstrict-prototypes -Wmissing-prototypes -Wmissing-declarations -Wredundant-decls -Wcast-align -Wvla -pthread
CPPFLAGS : -D_GNU_SOURCE -I$(top_srcdir)/src -I$(abs_top_builddir)/third_party/luajit/inc -I$(abs_top_builddir)/third_party/concurrency_kit/include
LDFLAGS : -L/usr/local/lib
LIBS : -laio -lm
prefix : /usr/local
bindir : ${prefix}/bin
libexecdir : ${prefix}/libexec
mandir : ${prefix}/share/man
datadir : ${prefix}/share
MySQL support : yes
PostgreSQL support : no
LuaJIT : bundled
LUAJIT_CFLAGS : -I$(abs_top_builddir)/third_party/luajit/inc
LUAJIT_LIBS : $(abs_top_builddir)/third_party/luajit/lib/libluajit-5.1.a -ldl
LUAJIT_LDFLAGS : -rdynamic
Concurrency Kit : bundled
CK_CFLAGS : -I$(abs_top_builddir)/third_party/concurrency_kit/include
CK_LIBS : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.a
configure flags :
===========================================================================
Listing 3-2Configuring Sysbench for compilation and installation
配置的末尾显示了将用于编译的选项。确保MySQL support同意。默认安装在/usr/local中。您可以在执行配置时使用--prefix选项进行更改，例如./configure --prefix=/home/myuser/sysbench。
下一步是使用make命令编译代码:
shell$ make -j
Making all in third_party/luajit
...
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/home/myuser/git/sysbench'
-j选项告诉make并行编译源代码，这样可以减少编译时间。然而，Sysbench 在所有情况下都可以快速编译，因此在这种情况下并不重要。
最后一步是安装 Sysbench 的编译版本:
shell$ sudo make install
Making install in third_party/luajit
...
make[2]: Leaving directory `/home/myuser/git/sysbench'
make[1]: Leaving directory `/home/myuser/git/sysbench'
就是这样。您现在已经准备好使用 Sysbench 来执行基准测试了。
执行基准
Sysbench 包括几个现成可用的基准。这包括从非数据库内置测试到各种数据库测试。非数据库测试被认为是内置的，因为它们是在 Sysbench 源代码本身中定义的。其他测试在 Lua 脚本中定义，并安装在/usr/local/share/sysbench/目录中(假设您安装在默认位置)。
Note
这一节和下一节假设您在安装 Sysbench 的同一台主机上有一个可用于测试的 MySQL 实例。如果不是这样，您需要根据需要调整主机名。
您可以通过使用--help参数调用sysbench来获得理解 Sysbench 参数的一般帮助:
shell$ sysbench –help
...
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
See 'sysbench <testname> help' for a list of options for each test.
在输出的底部是一个内置测试的列表和一个关于如何获得给定测试的更多信息的提示。您可以通过列出共享目录中的文件来获得附加测试的列表:
shell$ ls /usr/local/share/sysbench/
bulk_insert.lua oltp_update_index.lua
oltp_common.lua oltp_update_non_index.lua
oltp_delete.lua oltp_write_only.lua
oltp_insert.lua select_random_points.lua
oltp_point_select.lua select_random_ranges.lua
oltp_read_only.lua tests
oltp_read_write.lua
除了oltp_common.lua(OLTP 测试的共享代码)之外，带有.lua扩展名的文件是可用的测试。Lua 语言 1 是一种轻量级编程语言，常用于将代码嵌入到程序中。使用 Lua 程序类似于使用脚本语言，比如 Python，除了您的代码是通过另一个程序执行的(在这种情况下是 Sysbench)。
如上所述，您可以通过提供测试名称和help命令来获得关于测试的额外帮助。例如，要获得关于在oltp_read_only.lua中定义的测试的附加信息，您可以使用清单 3-3 中所示的help命令。
shell$ sysbench oltp_read_only help
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
oltp_read_only options:
--auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
--create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on]
--create_table_options=STRING Extra CREATE TABLE options []
--delete_inserts=N Number of DELETE/INSERT combinations per transaction [1]
--distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
--index_updates=N Number of UPDATE index queries per transaction [1]
--mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
--non_index_updates=N Number of UPDATE non-index queries per transaction [1]
--order_ranges=N Number of SELECT ORDER BY queries per transaction [1]
--pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
--point_selects=N Number of point SELECT queries per transaction [10]
--range_selects[=on|off] Enable/disable all range SELECT queries [on]
--range_size=N Range size for range SELECT queries [100]
--reconnect=N Reconnect after every N events. The default (0) is to not reconnect [0]
--secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off]
--simple_ranges=N Number of simple range SELECT queries per transaction [1]
--skip_trx[=on|off] Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
--sum_ranges=N Number of SELECT SUM() queries per transaction [1]
--table_size=N Number of rows per table [10000]
--tables=N Number of tables [1]
Listing 3-3Obtaining help for the oltp_read_only test
方括号中的值是默认值。
help命令只是几个可用命令中的一个(一些测试可能不会实现所有的命令)。其他命令涵盖了基准测试的各个阶段:
-
prepare: 执行设置测试所需的步骤，例如，通过创建和填充测试所需的表格。 -
warmup: 确保缓冲区和缓存是热的，例如，表数据和索引已经加载到 InnoDB 缓冲池中。这对于 OLTP 基准来说是特殊的。 -
run: 执行测试本身。该命令由所有测试提供。 -
cleanup: 删除测试使用的任何表格。
作为一个例子，考虑您以前检索帮助的只读 OLTP 测试。首先，创建一个可以执行所需查询的 MySQL 用户。默认情况下使用sbtest模式作为基准，因此一个简单的解决方案是创建一个拥有该模式所有特权的用户:
mysql> CREATE USER sbtest@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON sbtest.* TO sbtest@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE SCHEMA sbtest;
Query OK, 1 row affected (0.01 sec)
在这种情况下，用户需要从localhost开始连接。一般来说，情况并非如此，因此您需要更改帐户的主机名部分，以反映 Sysbench 用户的连接位置。用户名选择为sbtest,因为这是 Sysbench 使用的默认用户名。当 Sysbench 测试要求模式在第一次连接时存在时，也会创建sbtest模式。
Note
强烈建议为帐户选择强密码。
如果您想执行一个使用四个各有 20000 行的表的基准测试，那么您可以像清单 3-4 所示那样准备测试。
shell$ sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--table_size=20000 \
--tables=4 \
--threads=4 \
prepare
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...
Creating table 'sbtest2'...
Inserting 20000 records into 'sbtest2'
Inserting 20000 records into 'sbtest3'
Inserting 20000 records into 'sbtest1'
Inserting 20000 records into 'sbtest4'
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest1'...
Listing 3-4Preparing the test
这使用四个线程创建了四个表sbtest1、sbtest2、sbtest3和sbtest4。在这种情况下，准备步骤会很快，因为表很小；但是，如果您使用大型表来执行基准测试，那么设置测试会花费大量的时间。由于基准测试通常涉及执行一系列测试，因此您可以通过创建二进制备份(复制表，关闭 MySQL 或使用 MySQL Enterprise Backup 等工具)或文件系统快照来加速测试。对于每个后续测试，您可以恢复备份，而不是重新创建表。
可选地，作为下一步，您可以经历清单 3-5 中所示的预热阶段。
shell$ sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--table_size=20000 \
--tables=4 \
--threads=4 \
warmup
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
Initializing worker threads...
Preloading table sbtest3
Preloading table sbtest1
Preloading table sbtest2
Preloading table sbtest4
Listing 3-5Warming MySQL up for the test
在这里，包含--tables和--table-size选项很重要，否则只会预加载sbtest1表的默认行数(10，000)。预加载包括平均化id列和一个简单的SELECT COUNT(*)查询，在子查询中提取行(查询已经被重新格式化):
SELECT AVG(id)
FROM (SELECT *
FROM sbtest1 FORCE KEY (PRIMARY)
LIMIT 20000
) t
SELECT COUNT(*)
FROM (SELECT *
FROM sbtest1
WHERE k LIKE '%0%'
LIMIT 20000
) t
因此预热阶段可能并不等同于暂时运行实际的基准测试。
Tip
在执行基准测试时，您还可以使用--warmup-time=N选项来禁用第一个N秒的统计。
基准本身正在使用run命令执行。有两个选项可以指定测试的持续时间:
-
--events=N: 要执行的最大事件数。默认值为 0。 -
--time=N: 以秒为单位的最大持续时间。默认值为 10。
当其中一个选项的值为 0 时，表示无穷大。因此，如果您将--events和--time都设置为 0，测试将永远运行。例如，如果您对基准统计数据本身不感兴趣，但希望收集监控指标或希望在执行其他任务时创建工作负载，这可能会很有用。
Tip
本书的作者使用 Sysbench，将事件数量和时间限制都设置为 0，为创建备份的测试生成并发工作负载。
例如，如果您想执行一个一分钟(60 秒)的测试，您可以使用清单 3-6 中的命令。
shell$ sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--table_size=20000 \
--tables=4 \
--time=60 \
--threads=8 \
run
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 766682
write: 0
other: 109526
total: 876208
transactions: 54763 (912.52 per sec.)
queries: 876208 (14600.36 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 912.5224
time elapsed: 60.0128s
total number of events: 54763
Latency (ms):
min: 3.26
avg: 8.76
max: 122.43
95th percentile: 11.24
sum: 479591.29
Threads fairness:
events (avg/stddev): 6845.3750/70.14
execution time (avg/stddev): 59.9489/0.00
Listing 3-6Executing a Sysbench test for one minute
注意，与准备和预热阶段不同，run命令是用八个线程运行的。在一系列测试中，线程数量通常是变化的因素之一，以确定系统可以支持的并发工作负载。有必要指定run命令应该使用的表和行数，否则将使用默认值(Sysbench 命令之间没有共享状态)。
一旦您完成了测试，您就可以告诉 Sysbench 使用 clean up 命令进行自我清理，如清单 3-7 所示。
shell$ sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--tables=4 \
cleanup
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Listing 3-7Cleaning up after a test
请注意，有必要指定表的数量；否则，只会删除第一个表。
内置测试很棒，但让 Sysbench 成为真正强大的工具的是，您还可以定义自己的基准。
创建自定义基准
正如您在上一节中看到的，Sysbench 包含的数据库测试是在 Lua 脚本中定义的( www.lua.org/ )。这意味着定义自己的测试所需要做的就是用测试的定义创建一个 Lua 脚本，并将它保存在 Sysbench 的共享目录中。一个有用的例子是，如果您想要基于应用的特定需求创建一个测试，以测试索引的效果、重构应用或类似的事情。
这一节将整理一个小的测试脚本示例，这样您就可以看到创建您自己的测试的原则。该测试也可以在本书的 GitHub 资源库的sequence.lua中找到。
Tip
学习如何编写自己的 Sysbench Lua 脚本的一个好方法是研究现有的脚本。除了本章中的例子，你可以看看 Sysbench 附带的 Lua 脚本和 https://gist.github.com/utdrmac/92d00a34149565bc155cdef80b6cba12 中另一个相对简单的例子。
自定义脚本概述
示例基准测试将测试一个序列的性能，该序列是通过在一个表中为每个序列指定一行来实现的。这种构造有时用于在应用中实现自定义序列。列表 3-8 中显示了表格定义和表格使用示例。
mysql> SHOW CREATE TABLE sbtest.sbtest1\G
*************************** 1\. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` varchar(10) NOT NULL,
`val` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SELECT * FROM sbtest.sbtest1;
+--------+-----+
| id | val |
+--------+-----+
| sbkey1 | 0 |
+--------+-----+
1 row in set (0.00 sec)
mysql> UPDATE sbtest1
SET val = LAST_INSERT_ID(val+1)
WHERE id = 'sbkey1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT 6047878;
+------------------+
| 6047878 |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM sbtest.sbtest1;
+--------+-----+
| id | val |
+--------+-----+
| sbkey1 | 1 |
+--------+-----+
1 row in set (0.00 sec)
Listing 3-8Using a custom sequence table
在UPDATE语句中使用了6047878函数来为最后插入的 id 分配会话值，因此可以在之后的SELECT语句中获取该值。
示例测试将具有以下特征:
-
支持
prepare、run、cleanup和help命令。 -
prepare和run命令可以并行执行。 -
支持指定表的数量、表的大小以及是否使用显式事务。
-
验证每个表的行数是否在 1–99999 的范围内。表格的
id列被创建为varchar(10)，关键字以sbkey为前缀，因此最多可以有五位数字。
图 3-2 总结了将要实施的功能。
图 3-2
顺序测试中的功能概述
“准备”、“运行”和“清理”组代表命令，而“助手”组包含将在多个命令中使用的两个助手函数。run和help命令是特殊的，因为它们总是存在。帮助是根据脚本添加的选项自动生成的，因此不需要特别考虑。还有一些函数之外的代码，首先是健全性检查和脚本将支持的选项。
定义选项
脚本支持的选项是通过向sysbench.cmdline.options散列添加元素来配置的。这是 Sysbench 的内置特性之一，您可以在脚本中使用。另一个是sysbench.cmdline.command，它是为执行提供的命令的名称。
清单 3-9 展示了如何验证命令已经设置，然后添加该脚本支持的三个选项。
-- Validate that a command was provided
if sysbench.cmdline.command == nil then
error("Command is required. Supported commands: " ..
"prepare, run, cleanup, help")
end
-- Specify the supported options for this test
sysbench.cmdline.options = {
skip_trx = {"Don't start explicit transactions and " ..
"execute all queries in the AUTOCOMMIT mode",
false},
table_size = {"The number of rows per table. Supported " ..
"values: 1-99999", 1},
tables = {"The number of tables", 1}
}
Listing 3-9Verifying a command is specified and adding the options
如果没有设置命令，内置的error()函数用于发出错误消息，并列出支持的命令。没有必要验证该命令是否是受支持的命令之一，因为 Sysbench 会自动进行验证。
这些选项是通过由帮助文本和默认值组成的数组添加的。使用此脚本中的定义，生成的帮助文本将变成:
shell$ sysbench sequence help
sysbench 1.1.0-74f3b6b (using bundled LuaJIT 2.1.0-beta3)
sequence options
--skip_trx[=on|off] Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
--table_size=N The number of rows per table. Supported values: 1-99999 [1]
--tables=N The number of tables [1]
选项值在sysbench.opt散列中可用，例如，要获得测试中的表数，可以使用sysbench.opt.tables。散列是全局可用的，因此在使用它之前您不需要做任何事情。
现在，您已经准备好实现脚本支持的三个命令。因为run命令是强制性的，所以这是将要讨论的第一个命令。
运行命令
run命令是特殊的，因为它是强制性的，并且总是支持并行执行。与在单个函数中实现的其他命令不同(可选地调用其他函数)，Sysbench 为run命令使用了三个函数。必须始终存在的三个功能是
-
thread_init(): 这是 Sysbench 初始化脚本时调用的。 -
thread_done(): 当 Sysbench 执行完脚本时调用。 -
event(): 这是实现实际测试的地方，并且在每次迭代中调用一次。
对于这个例子，thread_init()函数可以保持非常简单:
-- Initialize the script
-- Initialize the global variables used in the rest of the script
function thread_init()
-- Initialize the database driver and connections
db = sysbench.sql.driver()
cnx = db:connect()
end
对于这个简单的测试，所有需要的初始化就是创建到 MySQL 的连接，包括初始化数据库驱动程序，并使用它来创建连接。驱动程序可从sysbench对象获得。通过在thread_init()函数中创建连接，Sysbench 可以重用这些连接，而不是为每次迭代创建一个新的连接。如果您想模拟为每组查询创建一个新的连接，您也可以选择通过在event()函数中添加代码来实现，并使连接对象成为本地的，就像稍后对prepare和cleanup命令所做的一样。
类似地，thread_done()函数在执行后进行清理:
-- Clean up after the test
function thread_done()
-- Close the connection to the database
cnx:disconnect()
end
在这种情况下，只需要关闭连接，这是使用连接的disconnect()方法完成的。
三个必需函数中最有趣的是event()函数，它定义了执行测试时要做什么。示例脚本的代码可以在清单 3-10 中看到。
-- Called for each iteration
function event()
-- Check the --skip_trx option which determines
-- whether explicit transactions are required.
if not sysbench.opt.skip_trx then
cnx:query("BEGIN")
end
-- Execute the customer test
do_increment()
-- If necessary, commit the transaction
if not sysbench.opt.skip_trx then
cnx:query("COMMIT")
end
end
Listing 3-10The event() function
这段代码使用了一个选项，即--skip_trx选项。如果--skip_trx被禁用，那么测试依赖于自动提交特性；否则，执行显式的BEGIN和COMMIT。
Note
在 Sysbench Lua 脚本中，您不能使用START TRANSACTION来开始一个事务。
在这种情况下，event()函数本身实际上并不执行任何工作。这被委托给do_increment()函数来展示如何添加额外的函数来像在其他程序中一样分离工作。清单 3-11 中显示了do_increment()函数和几个助手函数。
-- Generate the table name from the table number
function gen_table_name(table_num)
return string.format("sbtest%d", table_num)
end
-- Generate the key from an id
function gen_key(id)
return string.format("sbkey%d", id)
end
-- Increment the counter and fetch the new value
function do_increment()
-- Choose a random table and id
-- among the tables and rows available
table_num = math.random(sysbench.opt.tables)
table_name = gen_table_name(table_num)
id = math.random(sysbench.opt.table_size)
key = gen_key(id)
query = string.format([[
UPDATE %s
SET val = LAST_INSERT_ID(val+1)
WHERE id = '%s']], table_name, key)
cnx:query(query)
cnx:query("SELECT 6047878")
end
Listing 3-11The do_increment() and helper functions
gen_table_name()函数基于整数生成表名，gen_key()函数同样基于整数 id 生成键值。表名和键值用在脚本中的其他一些地方，所以通过将逻辑分成助手函数，可以确保它们在整个脚本中以相同的方式生成。
do_increment()函数本身根据测试中的表数和每个表中的行数，基于随机值生成表名和键。在实际的应用中，您可能没有这样统一的序列访问，在这种情况下，您可以修改脚本中的逻辑。最后，执行UPDATE和SELECT语句。该脚本的一个可能的扩展是在其他查询中使用生成的序列号，但是要小心，不要最终做了与您试图进行基准测试无关的工作。
这就是run命令所需的全部内容。注意，没有做任何事情来实现并行执行；这由 Sysbench 自动处理，除非您不想对所有线程一视同仁。线程不应该执行相同工作的一个例子是prepare命令，其中每个线程负责自己的表。
准备命令
prepare命令是支持并行执行的定制命令的一个例子。该命令的顶层代码在do_prepare()函数中实现，该函数又使用create_table()函数根据传递给该函数的表编号创建一个特定的表。这两个函数可以在清单 3-12 中看到。
-- Prepare the table
-- Can be parallelized up to the number of tables
function do_prepare()
-- The script only supports up to 99999 rows
-- as the id column is a varchar(10) and five
-- characters is used by 'sbkey'
assert(sysbench.opt.table_size > 0 and
sysbench.opt.table_size < 100000,
"Only 1-99999 rows per table is supported.")
-- Initialize the database driver and connection
local db = sysbench.sql.driver()
local cnx = db:connect()
-- Create table based on thread id
for i = sysbench.tid % sysbench.opt.threads + 1,
sysbench.opt.tables,
sysbench.opt.threads do
create_table(cnx, i)
end
-- Disconnect
cnx:disconnect()
end
-- Create the Nth table
function create_table(cnx, table_num)
table_name = gen_table_name(table_num)
print(string.format(
"Creating table '%s'...", table_name))
-- Drop the table if it exists
query = string.format(
"DROP TABLE IF EXISTS %s", table_name)
cnx:query(query)
-- Create the new table
query = string.format([[
CREATE TABLE %s (
id varchar(10) NOT NULL,
val bigint unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (id)
)]], table_name)
cnx:query(query)
-- Insert the rows inside a transaction
cnx:query("BEGIN")
for i = 1, sysbench.opt.table_size, 1 do
query = string.format([[
INSERT INTO %s (id)
VALUES ('%s')]], table_name, gen_key(i))
cnx:query(query)
end
cnx:query("COMMIT")
end
Listing 3-12The do_prepare() and create_table() functions
在do_prepare()函数中做的第一件事是验证行数在 1–99999 的范围内。这是通过使用assert()函数完成的，其中第一个参数的值必须为 true 否则，将打印作为第二个输出给出的错误消息，并且脚本存在。
每个线程调用一次do_prepare()函数，因此并行化是为您处理的(在示例的最后会有更多相关内容)，但是您需要确保每个表只创建一次。这是通过for循环完成的，其中sysbench.tid(Sysbench 线程 id)与线程数量的模数用于确定每个线程处理的表号。
实际的表创建是在create_table()中执行的，以将任务分离出来，从而更容易维护脚本。如果该表已经存在，则删除它，然后创建它，最后用请求的行数填充该表。所有行都被插入到一个事务中，以提高性能。如果您需要填充更大的表，每隔几千行就提交一次是值得的，但是因为这个表中的最大行数是 99999，而且行非常小，所以为了简单起见，每个表只使用一个事务就可以了。
清理命令
最后一个必须执行的命令是cleanup，这是单线程命令的一个例子。该命令的工作在cleanup()函数中完成，如清单 3-13 所示。
-- Cleanup after the test
function cleanup()
-- Initialize the database driver and connection
local db = sysbench.sql.driver()
local cnx = db:connect()
-- Drop each table
for i = 1, sysbench.opt.tables, 1 do
table_name = gen_table_name(i)
print(string.format(
"Dropping table '%s' ...", table_name))
query = string.format(
"DROP TABLE IF EXISTS %s", table_name)
cnx:query(query)
end
-- Disconnect
cnx:disconnect()
end
Listing 3-13The cleanup() function
cleanup()函数只支持串行执行，所以它可以遍历这些表并一个接一个地删除它们。
这就留下了一个问题:Sysbench 怎么知道prepare命令可以并行运行，而cleanup命令却不能？
注册命令
默认情况下，除了run以外的所有命令都是串行执行的，执行命令的功能与命令同名。因此，对于prepare命令，有必要在脚本中设置prepare对象指向do_prepare()函数，并附加一个参数，即每个线程应该调用一次do_prepare():
-- Specify the actions other than run that support
-- execution in parallel.
-- (Other supported actions are found based on the
-- function name except 'help' that is built-in.)
sysbench.cmdline.commands = {
prepare = {do_prepare, sysbench.cmdline.PARALLEL_COMMAND}
}
sysbench.cmdline.PARALLEL_COMMAND常量是内置的，指定命令应该并行执行。重要的是，该代码位于do_prepare()的定义之后，否则将分配一个零值。实际上，将代码添加到脚本的末尾是很方便的。
剧本到此结束。如果您已经将它复制到共享的 Sysbench 目录中(当您自己编译 Sysbench 时，使用默认的安装目录)，那么您现在可以像使用 Sysbench 附带的测试一样使用它。假设您已经将脚本保存为sequence.lua，在清单 3-14 中显示了该脚本的使用示例——没有输出。
shell$ sysbench sequence \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--table_size=10 \
--tables=4 \
--threads=4 \
prepare
shell$ sysbench sequence \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--table_size=10 \
--tables=4 \
--time=60 \
--threads=8 \
run
shell$ sysbench sequence \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-ssl=REQUIRED \
--mysql-db=sbtest \
--tables=4 \
cleanup
Listing 3-14Example commands for the sequence test
注意，对于oltp_read_only测试，sbtest模式必须在执行prepare命令之前存在。留给读者一个练习，用不同的--threads、--tables、--table_size和--skip_trx值来尝试这个脚本。
摘要
本章讨论了如何在 MySQL 中使用基准测试。首先，讨论了一些使用基准的一般最佳实践。最重要的事情是你已经确定了什么是基准，什么是成功的标准。这与一般的性能调优没有什么不同。理解基准执行的测试以及结果意味着什么也很重要。通常，您需要通过正常的监控解决方案或专门的脚本来收集额外的指标，以确定基准测试是否成功。
接下来，介绍了标准的 TPC 基准。TPC-C 和 TPC-E 基准很适合测试 OLTP 工作负载，其中 TPC-C 使用最多，因为它是最老的，但是 TPC-E 对于现代应用来说是最现实的。TPC-H 和 TPC-DS 使用复杂的查询，例如，探索可能影响查询计划的变化。
虽然您可以选择自己从头实现一个基准，但更有可能的是您将使用一个预先存在的基准工具。MySQL 最常用的工具是 Sysbench，我们已经详细介绍过了。首先，Sysbench 是通过编译安装的。然后展示了如何执行标准的 Sysbench 基准测试。然而，Sysbench 的真正优势在于您可以定义自己的定制测试。上一节给出了一个简单的例子。
同样，不可能总是使用真实世界的基准，也不可能总是使用真实世界的数据进行一般测试。下一章探讨了 MySQL 中经常使用的一些通用数据集，其中一些也在本书中使用。
Footnotes [1](#Fn1_source)www. lua. org/ 和 https:// en。维基百科。org/ wiki/ Lua_(编程语言)
四、测试数据
测试是性能调优工作中非常重要的一部分，因为在将更改应用到生产系统之前，验证这些更改是否有效非常重要。验证您的更改的最佳数据与您的生产数据密切相关；然而，为了探索 MySQL 是如何工作的，使用一些通用的测试数据会更好。本章介绍了四个带有安装说明的标准数据集，以及其他一些可用的数据集。
Tip
在本书的剩余部分中，world、world_x和sakila数据库被用作测试数据。
但是，首先，您需要知道如何下载数据库。
下载示例数据库
本章详细讨论的示例数据库的共同点是，它们可以从 https://dev.mysql.com/doc/index-other.html 下载，或者有一个可以下载它们的链接。对于几个数据库，也有在线文档和 PDF 文件从这个页面链接。页面相关部分如图 4-1 所示。
图 4-1
包含示例数据库链接的表
员工数据(employees数据库)是从朱塞佩·马霞(也被称为 Data Charmer)的 GitHub 存储库下载的，而其他数据库是从甲骨文的 MySQL 网站下载的。与雇员数据一起下载的还包括一个sakila数据库的副本。对于雇员数据、world数据库和sakila数据库，也有可用的文档。
Note
如果您没有使用最新版本的数据，则在安装测试数据库时，您可能会看到关于不推荐使用的功能的警告。您可以忽略这些警告，但是，建议您获取最新版本的数据。
menagerie数据库是一个很小的两表数据库，总共不到 20 行，是为 MySQL 手册中的教程部分创建的。不再赘述。
世界数据库
world样本数据库是简单测试中最常用的数据库之一。它由三个有几百到几千行的表组成。这使它成为一个小数据集，这意味着它甚至可以很容易地用于小的测试实例。
计划
数据库由city、country和countrylanguage表组成。表格之间的关系如图 4-2 所示。
图 4-2
world数据库
country表包含关于 239 个国家的信息，并作为来自city和countrylanguage表的外键的父表。数据库中总共有 4079 个城市和 984 种国家和语言组合。
装置
下载的文件由一个名为world.sql.gz或world.sql.zip的文件组成，这取决于您选择的是 Gzip 还是 zip 链接。在这两种情况下，下载的档案包含一个文件world.sql。数据的安装非常简单，只需执行脚本即可。
如果您将 MySQL Shell 与 2020 年 1 月左右或之前的世界数据库副本一起使用，您将需要使用传统协议，因为 X 协议(默认)要求 UTF-8，而世界数据库使用拉丁 1。您使用\source命令从 MySQL Shell 加载数据:
MySQL [localhost ssl] SQL> \source world.sql
如果您使用传统的mysql命令行客户端，请使用SOURCE命令:
mysql> SOURCE world.sql
在这两种情况下，如果world.sql文件不在您启动 MySQL Shell 或mysql的目录中，请添加该文件的路径。
一个相关的数据库是world_x，它包含与world相同的数据，但是它的组织方式不同。
世界 x 数据库
MySQL 8 增加了对 MySQL Document Store 的支持，它支持以 JavaScript Object Notation (JSON)文档的形式存储和检索数据。world_x数据库将一些数据存储在 JSON 文档中，为您提供一个测试数据库，可以很容易地用于包含使用 JSON 的测试。
计划
world_x数据库包括与world数据库相同的三个表，尽管列略有不同，例如，city表包括 JSON 列Info和人口，而不是Population列，并且country表省略了几个列。取而代之的是countryinfo表，这是一个纯文档存储类型的表，其中的信息是从country表中删除的。模式图如图 4-3 所示。
图 4-3
world_x数据库
虽然city和countryinfo表中没有外键，但是可以分别使用CountryCode列和doc->>'$.Code'值将它们连接到country表。countryinfo表的_id列是一个存储生成列的例子，其中的值是从 JSON 文档的doc列中提取的。
装置
world_x数据库的安装与world数据库非常相似。你可以下载world_x-db.tar.gz或者world_x-db.zip文件并解压。提取的文件包括一个名为world_x.sql的文件和一个README文件。world_x.sql文件包括创建模式所需的所有语句。
由于world_x模式使用 UTF-8，您可以使用任何一种 MySQL 协议来安装它。例如，使用 MySQL Shell:
MySQL [localhost+ ssl] SQL> \source world_x.sql
如果world_x.sql文件不在当前目录中，则添加其路径。
world和world_x数据库非常简单，易于使用；然而，有时你会需要一些稍微复杂一点的东西，而sakila数据库可以提供这些东西。
萨基拉数据库
sakila数据库是一个真实的数据库，它包含一个电影租赁业务的模式，其中包含关于电影、库存、商店、员工和客户的信息。它添加了一个全文索引、一个空间索引、视图和存储程序，以提供一个使用 MySQL 特性的更完整的示例。数据库大小仍然非常适中，适合小型实例。
计划
sakila数据库由 16 个表、7 个视图、3 个存储过程、3 个存储函数和 6 个触发器组成。这些表可以分为三组，客户数据、业务和库存。为了简洁起见，图中没有包括所有的列，大多数索引也没有显示。图 4-4 显示了表格、视图和存储程序的完整概览。
图 4-4
sakila数据库概述
包含客户相关数据的表格(加上员工和商店的地址)位于左上角的区域。左下角的区域包含与业务相关的数据，右上角的区域包含关于电影和库存的信息。右下角用于视图和存储的程序。
Tip
您可以通过在 MySQL Workbench 中打开安装中包含的sakila.mwb文件来查看整个图表(尽管格式不同)。这也是一个很好的例子，说明如何在 MySQL Workbench 中使用增强的实体关系(EER)图来记录您的模式。
由于对象的数量相对较多，所以在讨论模式时，将它们分成五组(每个表组、视图和存储例程)。第一组是客户相关数据，表格如图 4-5 所示。
图 4-5
sakila数据库中包含客户数据的表格
有四个表包含与客户相关的数据。customer表是主表，地址信息存储在address、city和country表中。
客户和业务组之间存在外键，外键从业务组的customer表指向store表。业务组中的表还有四个外键指向address和customer表。业务群如图 4-6 所示。
图 4-6
sakila数据库中包含业务数据的表
业务表包含关于商店、员工、租金和付款的信息。store和staff表有两个方向的外键，员工属于一个商店，而商店的经理是员工的一部分。租金和付款由员工处理，因此与商店间接相关，付款是为了租金。
表的业务组是与其它组关系最密切的组。staff和store表有address表的外键，而rental和payment表引用客户。最后，rental表有一个外键指向库存组中的inventory表。库存组的示意图如图 4-7 所示。
图 4-7
sakila数据库中包含库存数据的表格
库存组中的主表是film表，它包含关于商店提供的电影的元数据。此外，还有一个带有标题和描述的film_text表，带有全文索引。
在film和category以及actor表之间存在多对多的关系。最后，在业务组中有一个从inventory表到store表的外键。
这涵盖了sakila数据库中的所有表格，但也有一些如图 4-8 所示的视图。
图 4-8
sakila数据库中的视图
这些视图可以像报告一样使用，并且可以分为两类。film_list、nicer_but_slower_film_list和actor_info视图与存储在数据库中的电影相关。第二类包含与sales_by_store、sales_by_film_category、staff_list和customer_list视图中的商店相关的信息。
为了完善数据库，还有如图 4-9 所示的存储函数和过程。
图 4-9
存储在sakila数据库中的程序
film_in_stock()和film_not_in_stock()过程返回一个结果集，该结果集由给定电影和商店的库存 id 组成，基于电影是否有库存。找到的库存条目总数作为 out 参数返回。rewards_report()程序根据上个月的最低花费生成一份报告。
get_customer_balance()函数返回给定客户在给定数据上的余额。剩下的两个函数检查一个库存 id 的状态，其中inventory_held_by_customer()返回当前租赁该商品的客户的客户 id(如果没有客户租赁该商品，则返回NULL)，如果您想检查给定的库存 id 是否有库存，可以使用inventory_in_stock()函数。
装置
下载的文件展开到一个包含三个文件的目录中，其中两个文件创建模式和数据，最后一个文件包含 MySQL Workbench 使用的格式的 ETL 图。
Note
本节和本书后面的例子使用了从 MySQL 主页下载的sakila数据库的副本。
这些文件是
-
sakila-data.sql: 填充表格所需的INSERT语句以及触发器定义。 -
sakila-schema.sql: 模式定义语句。 -
sakila.mwb**:**MySQL 工作台 ETL 图。这类似于图 4-4 所示，细节如图 4-5 至 4-9 所示。
通过首先获取sakila-schema.sql文件，然后获取sakila-data.sql文件来安装sakila数据库。例如，下面是使用 MySQL Shell:
MySQL [localhost+ ssl] SQL> \source sakila-schema.sql
MySQL [localhost+ ssl] SQL> \source sakila-data.sql
如果文件不在当前目录中，请添加文件的路径。
到目前为止，这三个数据集的共同点是它们包含的数据很少。虽然在许多情况下这是一个很好的特性，因为它使工作变得更容易，但是在某些情况下，您需要更多的数据来研究查询计划中的差异。employees数据库是一个具有更多数据的选项。
雇员数据库
employees数据库(在 MySQL 文档下载页面上称为雇员数据；GitHub 知识库的名字是test_db)最初是由王辅生和卡洛·扎尼奥洛创建的，是 MySQL 主页上链接的最大的测试数据集。对于非分区版本，数据文件的总大小约为 180 MiB，对于分区版本，约为 440 MiB。
计划
employees数据库由六个表和两个视图组成。您可以选择再安装两个视图、五个存储函数和两个存储过程。表格如图 4-10 所示。
图 4-10
employees数据库中的表格、视图和例程
可以选择让salaries和titles表按照from_date列的年份进行分区，如清单 4-1 所示。
PARTITION BY RANGE COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB,
PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB,
PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB,
PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB,
PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB,
PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
Listing 4-1The optional partitioning of the salaries and titles tables
表 4-1 显示了employees数据库中表的行数和表空间文件的大小(请注意，当您加载数据时，大小可能会稍有变化)。该大小假定您加载了未分区的数据；分区表有点大。
表 4-1
employees数据库中每个表的大小
桌子
|
行数
|
表空间大小
|
| --- | --- | --- |
| departments | nine | 128 kiB |
| dept_emp | Three hundred and thirty-one thousand six hundred and three | 25600 千桶 |
| dept_manager | Twenty-four | 128 kiB |
| employees | Three hundred thousand and twenty-four | 22528 kiB |
| salaries | Two million eight hundred and forty-four thousand and forty-seven | 106496 kiB |
| titles | Four hundred and forty-three thousand three hundred and eight | 27648 kiB |
按照今天的标准，它仍然是一个相对较小的数据量，但是它足够大，您可以开始看到不同查询计划的一些性能差异。
图 4-11 总结了视图和程序。
图 4-11
employees数据库中的视图和例程
dept_emp_latest_date和current_dept_emp视图与表格一起安装，而其余的对象分别安装在objects.sql文件中。存储程序自带内置帮助，可通过使用employees_usage()功能或employees_help()程序获得。后者如清单 4-2 所示。
mysql> CALL employees_help()\G
*************************** 1\. row ***************************
info:
== USAGE ==
====================
PROCEDURE show_departments()
shows the departments with the manager and
number of employees per department
FUNCTION current_manager (dept_id)
Shows who is the manager of a given departmennt
FUNCTION emp_name (emp_id)
Shows name and surname of a given employee
FUNCTION emp_dept_id (emp_id)
Shows the current department of given employee
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Listing 4-2The built-in help for the stored routines in the employees database
装置
您可以下载一个包含安装所需文件的 ZIP 文件，也可以在 https://github.com/datacharmer/test_db 克隆 GitHub 库。在撰写本文时，只有一个名为master的分支。如果你已经下载了 ZIP 文件，它会解压到一个名为test_db-master的目录中。
有几个文件。在 MySQL 8 中与安装employees数据库相关的两个是employees.sql和employees_partitioned.sql。区别在于salaries和titles表是否被分区。(还有针对 MySQL 5.1 的employees_partitioned_5.1.sql，其中不支持employees_partitioned.sql中使用的分区方案。)
通过使用SOURCE命令获取.dump文件来加载数据。在撰写本文时，MySQL Shell 不支持SOURCE命令，因此您需要使用遗留的mysql命令行客户端来导入数据。转到源文件所在的目录，根据您是否想要使用分区，选择employees.sql或employees_partitioned.sql文件，例如:
mysql> SOURCE employees.sql
导入需要一点时间，并通过显示花费的时间来完成:
+---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:51 |
+---------------------+
1 row in set (0.44 sec)
或者，您可以通过获取objects.sql文件来加载一些额外的视图和存储的例程:
mysql> SOURCE objects.sql
除了这里讨论的数据集之外，还有其他一些选择来获取示例数据。
其他数据库
可能会发生这样的情况，您需要执行测试，这些测试需要的数据有一些需求是到目前为止所讨论的标准示例数据库无法满足的。幸运的是，还有其他选择。
Tip
不要忽视创建您自己的定制示例数据库的可能性，例如，通过对您的生产数据使用数据屏蔽。
如果你正在寻找一个非常大的真实世界的例子，那么你可以在 https://en.wikipedia.org/wiki/Wikipedia:Database_download 下载维基百科数据库。2019 年 9 月 20 日起的英文维基百科转储，bzip2 压缩 XML 格式，16.3 GiB。
如果您正在寻找 JSON 数据，那么一个选项是来自美国地质调查局(USGS)的地震信息，该信息以 GeoJSON 格式提供，可以下载过去一小时、一天、一周或一个月的地震信息，可以根据地震强度进行筛选。可以在 https://earthquake.usgs.gov/earthquakes/feed/v1.0/geojson.php 找到格式描述和提要链接。由于数据包含 GeoJSON 格式的地理信息，因此对于需要空间索引的测试非常有用。
前一章描述的基准工具也包括测试数据或者支持创建测试数据。这些数据也可能对您自己的测试有用。
如果您搜索互联网，还可以找到其他示例数据库。最后，要考虑的重要事情是数据对于您的测试是否有合适的大小，以及它是否使用了您需要的特性。
摘要
本章介绍了四个标准示例数据库和一些其他的测试数据示例。讨论的四个标准数据库是world、world_x、sakila和employees。这些都可以在 https://dev.mysql.com/doc/index-other.html 通过 MySQL 手册找到。除employees外，这些数据库用于本书中的示例，除非另有说明。
world和world_x数据库是最简单的，区别在于world_x使用 JSON 来存储一些信息，而world数据库是纯关系型的。这些数据库不包含太多的数据，但是由于它们的小尺寸和简单性，它们对于简单的测试和例子是有用的。特别是world数据库在本书中被广泛使用。
sakila数据库有一个更复杂的模式，包括不同的索引类型、视图和存储例程。这使得它更现实，并允许更复杂的测试。然而，数据的大小仍然足够小，甚至可以在小型 MySQL 实例上使用。它在本书中也被广泛使用。
employees数据库的模式在复杂性上介于world和sakila数据库之间，但是有更多的数据，这使得它更适合测试各种查询计划之间的差异。如果您需要在实例上生成一些负载，例如使用表扫描，这也很有用。本书中没有直接使用employees数据库，但是如果您想要重现一些需要加载的示例，那么这是四个标准测试数据库中最好的一个。
您不应该限制自己去考虑标准的测试数据库。您可以创建自己的数据库，使用基准工具创建一个，或者在互联网上查找可用的数据。维基百科的数据库和美国地质调查局(USGS)的地震数据都是可以下载的数据。
这就完成了 MySQL 查询性能调优的介绍。第二部分从性能模式开始，介绍了与诊断性能问题相关的常见信息源。
五、性能模式
性能模式是 MySQL 中与性能相关的诊断信息的主要来源。它最初是在 MySQL 5.5 版本中引入的，然后在 5.6 版本中被大量修改为当前的结构，此后在 5.7 和 8 版本中逐渐得到改进。
本章介绍并概述了性能模式，因此在本书的其余部分使用性能模式时，它是如何工作的就很清楚了。与性能模式密切相关的是将在下一章讨论的 sys 模式和第 7 章的主题信息模式。
本章讨论了性能模式特有的概念，特别关注线程、工具、使用者、事件、摘要和动态配置。但是，首先有必要熟悉性能模式中使用的术语。
术语
在学习一门新学科时，术语是很难的事情之一，性能模式也不例外。由于术语之间几乎是循环关系，所以没有明确的顺序来描述它们。相反，本节将提供本章中使用的最重要术语的简要概述，以便您了解这些术语的含义。到本章结束时，你应该能更好地理解这些概念的含义以及它们之间的关系。
表 5-1 总结了性能模式中最重要的术语。
表 5-1
MySQL 性能模式术语
|学期
|
描述
|
| --- | --- |
| 行动者 | 用户名和主机名的组合(帐户)。 |
| 消费者 | 收集由仪器产生的数据的过程。 |
| 摘要 | 规范化查询的校验和。摘要用于聚集相似查询的统计数据。 |
| 动态配置 | 性能模式可以在运行时配置，这称为动态配置。这是通过设置表完成的，而不是通过改变系统变量。 |
| 事件 | 事件是由消费者从仪器收集数据而产生的。因此，一个事件包含度量和关于度量在何时何地被收集的信息。 |
| 工具 | 进行测量的代码点。 |
| 目标 | 表、事件、函数、过程或触发器。 |
| 设置表 | 性能模式有几个用于动态配置的表。这些被称为设置表，表名以setup_开头。 |
| 一览表 | 包含汇总数据的表。表名包括单词 summary，名称的其余部分表示数据的类型和分组依据。 |
| 线 | 线程对应于连接或后台线程。性能模式线程和操作系统线程之间是一一对应的。 |
当你阅读这一章时，如果你遇到不确定其含义的术语，参考这个表会很有用。
线
线程是性能模式中的一个基本概念。当在 MySQL 中做任何事情时，无论是处理连接还是执行后台工作，工作都是由线程完成的。MySQL 在任何时候都有几个线程，因为它允许 MySQL 并行执行工作。对于连接，只有一个线程。
Note
InnoDB 中引入了对执行聚集索引和分区的并行读取的支持，这在一定程度上混淆了一个连接一个线程的画面。但是，由于执行并行扫描的线程被认为是后台线程，因此对于本讨论，您可以将连接视为单线程。
每个线程都有一个唯一标识该线程的 id，在性能模式表中存储该 id 的列称为THREAD_ID。检查线程的主表是清单 5-1 中的threads表，展示了 MySQL 8 中存在的线程类型的典型示例。可用的线程数量和确切的线程类型取决于查询threads表时实例的配置和使用情况。
mysql> SELECT THREAD_ID AS TID,
SUBSTRING_INDEX(NAME, '/', -2) AS THREAD_NAME,
IF(TYPE = 'BACKGROUND', '*', ") AS B,
IFNULL(PROCESSLIST_ID, ") AS PID
FROM performance_schema.threads;
+-----+--------------------------------------+---+-----+
| TID | THREAD_NAME | B | PID |
+-----+--------------------------------------+---+-----+
| 1 | sql/main | * | |
| 2 | mysys/thread_timer_notifier | * | |
| 4 | innodb/io_ibuf_thread | * | |
| 5 | innodb/io_log_thread | * | |
| 6 | innodb/io_read_thread | * | |
| 7 | innodb/io_read_thread | * | |
| 8 | innodb/io_read_thread | * | |
| 9 | innodb/io_read_thread | * | |
| 10 | innodb/io_write_thread | * | |
| 11 | innodb/io_write_thread | * | |
| 12 | innodb/io_write_thread | * | |
| 13 | innodb/io_write_thread | * | |
| 14 | innodb/page_flush_coordinator_thread | * | |
| 15 | innodb/log_checkpointer_thread | * | |
| 16 | innodb/log_closer_thread | * | |
| 17 | innodb/log_flush_notifier_thread | * | |
| 18 | innodb/log_flusher_thread | * | |
| 19 | innodb/log_write_notifier_thread | * | |
| 20 | innodb/log_writer_thread | * | |
| 21 | innodb/srv_lock_timeout_thread | * | |
| 22 | innodb/srv_error_monitor_thread | * | |
| 23 | innodb/srv_monitor_thread | * | |
| 24 | innodb/buf_resize_thread | * | |
| 25 | innodb/srv_master_thread | * | |
| 26 | innodb/dict_stats_thread | * | |
| 27 | innodb/fts_optimize_thread | * | |
| 28 | mysqlx/worker | | 9 |
| 29 | mysqlx/acceptor_network | * | |
| 30 | mysqlx/acceptor_network | * | |
| 31 | mysqlx/worker | * | |
| 34 | innodb/buf_dump_thread | * | |
| 35 | innodb/clone_gtid_thread | * | |
| 36 | innodb/srv_purge_thread | * | |
| 37 | innodb/srv_purge_thread | * | |
| 38 | innodb/srv_worker_thread | * | |
| 39 | innodb/srv_worker_thread | * | |
| 40 | innodb/srv_worker_thread | * | |
| 41 | innodb/srv_worker_thread | * | |
| 42 | innodb/srv_worker_thread | * | |
| 43 | innodb/srv_worker_thread | * | |
| 44 | sql/event_scheduler | | 4 |
| 45 | sql/compress_gtid_table | | 6 |
| 46 | sql/con_sockets | * | |
| 47 | sql/one_connection | | 7 |
| 48 | mysqlx/acceptor_network | * | |
| 49 | innodb/parallel_read_thread | * | |
| 50 | innodb/parallel_read_thread | * | |
| 51 | innodb/parallel_read_thread | * | |
| 52 | innodb/parallel_read_thread | * | |
+-----+--------------------------------------+---+-----+
49 rows in set (0.0615 sec)
Listing 5-1Threads in MySQL 8
TID列是每个线程的THREAD_ID,THREAD_NAME列包括线程名称的最后两个部分(第一个部分是所有线程的thread),B列有一个星号表示后台线程，PID列有前台线程的进程列表 id。
Note
不幸的是，术语 thread 在 MySQL 中被重载了，在某些地方被用作连接的同义词。在本书中，连接是指用户连接，线程是指性能模式线程，也就是说，它可以是后台或前台(包括连接)线程。例外情况是当讨论一个明显违反该约定的表时。
线程列表显示了线程的几个重要概念。进程列表 id 和线程 id 不相关。事实上，线程 id = 28 的线程的进程列表 id (9)比线程 id 为 44 的线程的进程列表 id(4)高。因此，甚至不能保证顺序是相同的(尽管对于非mysqlx线程来说，通常是这样)。
对于mysqlx/worker线程，一个是前台线程，另一个是后台线程。这反映了 MySQL 如何使用 X 协议处理连接，这与传统的连接处理方式有很大的不同。
还有一些“混合”线程既不是完全的后台线程，也不是完全的前台线程。压缩mysql.gtid_executed表的sql/compress_gtid_table线程就是一个例子。它是一个前台线程，但是如果你执行SHOW PROCESSLIST，它将不会被包含。
Tip
performance_schema.threads表非常有用，也包含了SHOW PROCESSLIST显示的所有信息。因为与执行SHOW PROCESSLIST或查询information_schema.PROCESSLIST表相比，查询该表的开销更小，所以推荐使用线程表以及sys.processlist和sys.session视图来获得连接列表。
获取连接的线程 id 有时会很有用。这有两个功能:
-
PS_THREAD_ID(): 获取作为参数提供的连接 id 的性能模式线程 id。 -
PS_CURRENT_THREAD_ID(): 获取当前连接的性能模式线程 id。
在 MySQL 8.0.15 和更早的版本中，使用sys.ps_thread_id()并给出一个参数NULL来获取当前连接的线程 id。使用这些函数的一个例子是
mysql> SELECT CONNECTION_ID(),
PS_THREAD_ID(13),
PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
CONNECTION_ID(): 13
PS_THREAD_ID(13): 54
PS_CURRENT_THREAD_ID(): 54
1 row in set (0.0003 sec)
使用这些函数相当于查询performance_schema.threads表中的PROCESSLIST_ID和THREAD_ID列来链接一个连接 id 和一个线程 id。清单 5-2 展示了一个使用PS_CURRENT_THREAD_ID()函数查询当前连接的threads表的例子。
mysql> SELECT *
FROM performance_schema.threads
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1\. row ***************************
THREAD_ID: 54
NAME: thread/mysqlx/worker
TYPE: FOREGROUND
PROCESSLIST_ID: 13
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: performance_schema
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: statistics
PROCESSLIST_INFO: SELECT *
FROM threads
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: SSL/TLS
THREAD_OS_ID: 31516
RESOURCE_GROUP: SYS_default
1 row in set (0.0005 sec)
Listing 5-2Querying the threads table for the current connection
有几栏提供了有关性能调优的有用信息，将在后面的章节中使用。这里值得注意的是名称以PROCESSLIST_开头的列。这些等同于由SHOW PROCESSLIST返回的信息，但是查询threads表对连接的影响较小。INSTRUMENTED和HISTORY列指定是否为线程收集指令数据，以及是否为线程保存事件历史。您可以更新这两列来改变线程的行为，或者您可以基于setup_threads表中的线程类型或者基于使用setup_actors表的帐户来定义线程的默认行为。这就回避了乐器和事件是什么的问题。接下来的三个部分将讨论这一点以及如何使用这些工具。
工具
仪器是进行测量的代码点。有两种类型的工具:可以定时的和不能定时的。定时工具是事件和idle工具(当线程空闲时测量)，而非定时工具计算错误和内存使用。
仪器按其名称分组，形成一个层次结构，组件之间用/分隔。一个名字有多少个组成部分没有规则，有些只有一个组成部分，而另一些有多达五个组成部分。
工具名的一个例子是statement/sql/select，它代表直接执行的SELECT语句(即，不是从存储过程中执行的)。另一个工具是statement/sp/stmt,它是一个在存储过程中执行的语句。
随着新特性的增加，以及更多的检测点被插入到现有代码中，检测的数量也在不断增加。在 MySQL 8.0.18 中，没有安装额外的插件或组件时，大约有 1229 个工具(工具的确切数量也取决于平台)。这些仪器在表 5-2 中列出的顶级组件中进行拆分。定时栏显示仪器是否可以定时，计数栏显示该顶层组件的仪器总数以及 8.0.18 中默认启用的仪器数量。
表 5-2
MySQL 8.0.18 中的顶级仪器组件
|成分
|
定时的
|
数数
|
描述
|
| --- | --- | --- | --- |
| error | 不 | 总计:1 已启用:1 | 是否收集有关遇到的错误和警告的信息。没有子组件。 |
| idle | 是 | 总计:1 已启用:1 | 用于检测线程何时空闲。没有子组件。 |
| memory | 不 | 总数:511 启用:511 | 收集内存分配和释放的数量和大小。名称由三部分组成:memory、代码区和仪器名称。 |
| stage | 是 | 总数:119 已启用:16 | 收集有关查询阶段事件的信息。这些名称有三个组成部分:stage、代码区和阶段名。 |
| statement | 是 | 总数:212 已启用:212 | 收集关于语句事件的信息。有一到两个子组件。 |
| transaction | 是 | 总计:1 已启用:1 | 收集有关事务事件的信息。没有子组件。 |
| wait | 是 | 总数:384 已启用:52 | 收集有关最低级别事件的等待事件的信息。例如，这包括获取锁和互斥锁以及执行 I/O 操作 |
命名方案使得确定仪器测量的内容相对容易。您可以在setup_instruments表中找到所有可用的仪器，该表还允许您配置仪器是否启用和定时。对于某些仪器，还有一个简短的文档，记录了该仪器收集的数据。
如果您想在 MySQL 启动时启用或禁用工具，您可以使用performance-schema-instrument选项。它的工作方式与大多数选项不同，因为您可以多次指定它来更改几个仪器的设置，并且您可以使用%通配符来匹配模式。如何使用该选项的示例如下
[mysqld]
performance-schema-instrument = "stage/sql/altering table=ON"
performance-schema-instrument = "memory/%=COUNTED"
第一个选项启用stage/sql/altering table乐器的计数和计时，而第二个选项启用所有记忆乐器的计数(这也是默认设置)。
Caution
启用所有工具(以及接下来讨论的消费者)似乎很诱人。但是，检测和使用的越多，开销就越大。启用所有功能实际上会导致停机(本书的作者已经看到了这种情况)。特别是，wait/synch/%仪器和events_waits_%消费者增加了开销。根据经验，监控的粒度越细，增加的开销就越多。在大多数情况下，MySQL 8 中的默认设置在可观察性和开销之间提供了一个很好的折衷。
仪器生成的数据必须被使用，以便这些数据在性能模式表中可用。这是消费者做的。
顾客
消费者处理由仪器生成的数据，并使其在性能模式表中可用。消费者在setup_consumers表中定义，除了消费者名称之外，该表还有一列指定消费者是否被启用。
消费者形成如图 5-1 所示的层次结构。该图分为两部分，虚线上方为高级消费者，虚线下方为事件消费者。默认情况下，绿色(浅色)用户处于启用状态，红色(深色)用户处于禁用状态。
图 5-1
消费者等级制度
消费者形成一个层次结构意味着消费者只有在它自己和层次结构中所有更高的消费者都被启用的情况下才消费事件。因此，禁用global_instrumentation消费者实际上禁用了所有消费者。您可以使用sys模式函数ps_is_consumer_enabled()来确定消费者及其依赖的消费者是否被启用，例如:
mysql> SELECT sys.ps_is_consumer_enabled(
'events_statements_history'
) AS IsEnabled;
+-----------+
| IsEnabled |
+-----------+
| YES |
+-----------+
1 row in set (0.0005 sec)
statements_digest消费者负责收集按语句摘要分组的数据，例如，通过events_statements_summary_by_digest表提供的数据。对于查询性能调优，这可能是最重要的消费者。它只取决于全球消费者。thread_instrumentation使用者确定线程是否正在收集特定于线程的测量数据。它还控制是否有任何事件消费者收集数据。
对于消费者，每个消费者有一个配置选项，选项名称由前缀performance-schema-consumer-和消费者名称组成，例如:
[mysqld]
performance-schema-consumer-events-statements-history-long = ON
这将使events_statements_history_long消费者。
您很少需要考虑禁用三个高级消费者中的任何一个。事件消费者通常是专门配置的，并且将与事件的概念一起讨论。
事件
事件是消费者记录仪器收集的数据的结果，您可以用它来观察 MySQL 中正在发生的事情。有几种事件类型，并且事件是相互关联的，因此通常一个事件既有一个父事件，又有一个或多个子事件。本节介绍事件是如何工作的。
事件类型
有四种事件类型，涵盖了从事务到等待的各种级别的细节。事件类型还将相似类型的事件分组，为事件收集的信息取决于其类型。例如，表示语句执行的事件包括查询和检查了多少行，而事务的事件包含诸如请求的事务隔离级别之类的信息。事件类型如图 5-2 所示。
图 5-2
四种事件类型
事件对应于不同级别的详细信息，事务是最高级别(最低级别的详细信息)，等待事件是最低级别(最高级别的详细信息):
-
**事务:**事件描述事务，包括诸如请求的事务隔离级别(但不一定使用)、事务状态等细节。默认情况下，收集每个线程的当前和最后十个事务。
-
**语句:**这是最常用的事件类型，包含有关所执行查询的信息。它还包括有关在存储过程中执行的语句的信息。这包括检查的行数、返回的行数、是否使用了索引以及执行时间等信息。默认情况下，收集每个线程的当前和最后十条语句。
-
**阶段:**这大致对应于
SHOW PROCESSLIST报告的状态。这些在默认情况下是不启用的(InnoDB 进度信息是部分例外)。 -
**等待:**这些是低级事件，包括 I/O 和等待互斥。这些非常具体，对于低级性能调优非常有用，但也是最昂贵的。默认情况下，不会启用任何等待事件使用者。
还有一个问题是记录的事件要保存多久。
事件范围
对于每种事件类型，都有三个使用者，它们指定了被使用事件的生存期。范围是
-
current: 当前正在进行的事件，以及空闲线程最后完成的事件。在某些情况下，同一级别的事件可能不止一个。例如，当执行存储过程时，过程本身既有语句事件，又有当前正在过程中执行的语句。
-
**历史:**每个线程的最后十个(默认)事件。当线程关闭时，事件被丢弃。
-
history_long: 最后 10，000 个(默认)事件，不考虑生成事件的线程。即使在线程关闭后，事件仍会保留。
事件类型和范围共同构成了 12 个事件消费者。每个事件消费者都对应一个性能模式表，表名与消费者名相同，如清单 5-3 所示。
mysql> SELECT TABLE_NAME
FROM performance_schema.setup_consumers c
INNER JOIN information_schema.TABLES t
ON t.TABLE_NAME = c.NAME
WHERE t.TABLE_SCHEMA = 'performance_schema'
AND c.NAME LIKE 'events%'
ORDER BY c.NAME;
+----------------------------------+
| TABLE_NAME |
+----------------------------------+
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
+----------------------------------+
12 rows in set (0.0323 sec)
Listing 5-3The correspondence between consumer and table names
如图 5-2 中事件类型之间的箭头所暗示的，类型之间的关系超出了它们所代表的细节层次。这种关系不是层次结构，而是由事件嵌套组成。
事件嵌套
一般来说，事件是由其他事件生成的，所以事件形成一个树，每个事件有一个父事件，可能还有许多子事件。虽然看起来事件类型形成了一个层次结构，例如，事务是语句的父级，但关系要比这复杂得多，而且是双向的。以开始一个事务的START TRANSACTION语句为例，该语句成为该事务的父级，而该事务又是其他语句的父级。另一个例子是调用存储过程的CALL语句，该存储过程成为在该过程中执行的语句的父级。
嵌套会变得非常复杂。图 5-3 显示了包括所有四种事件类型的事件链示例。
图 5-3
一连串事件的例子
对于语句事件，显示实际的查询，而对于其他事件类型，显示事件名称或事件名称的一部分。这个链从启动一个事务的START TRANSACTION语句开始。在事务内部，调用了myproc()过程，这使得它成为了SELECT语句的父语句，该语句经历了包括stage/sql/statistics在内的几个阶段，该阶段又包括请求 InnoDB 中的trx_mutex。
事件表有两列用于跟踪事件之间的关系:
-
NESTING_EVENT_ID: 父事件 id -
NESTING_EVENT_TYPE: 父事件的事件类型(TRANSACTION、STATEMENT、STAGE或WAIT)
语句事件表有一些与嵌套语句事件相关的附加列:
-
OBJECT_TYPE: 父语句事件的对象类型。 -
OBJECT_SCHEMA: 存储父语句对象的模式。 -
OBJECT_NAME: 父报表对象的名称。 -
NESTING_EVENT_LEVEL: 语句嵌套有多深。最顶层语句的级别为 0，每创建一个子级别，NESTING_EVENT_LEVEL就加 1。
sys.ps_trace_thread()过程是如何自动生成事件树的一个很好的例子。在第 20 章中有一个使用ps_trace_thread()的例子。
事件属性
无论事件的类型如何，它们都有一些共同的属性。这些属性包括主键、事件 id 和事件的计时方式。
事件的当前和历史(但不是长期历史)表的主键由THREAD_ID和EVENT_ID列组成。随着线程创建更多的事件，EVENT_ID列会增加，所以如果您想让事件按顺序排列，您必须按EVENT_ID排序。每个线程都有自己的事件 id 序列。每个事件表中都有两个事件 id 列:
-
EVENT_ID: 这是事件的主事件 id，在事件开始时设置。 -
END_EVENT_ID: 事件结束时设置该 id。这意味着您可以通过检查END_EVENT_ID列是否为NULL来确定事件是否正在进行中。
此外，EVENT_NAME列包含负责该事件的仪器的名称，而用于语句、阶段和等待的SOURCE列包含仪器触发的源代码中的文件名和行号。
有三列与记录事件开始、结束和持续时间的事件计时相关:
-
TIMER_START: 当 MySQL 启动时，内部定时器计数器设置为 0，每皮秒递增一次。当一个事件开始时，计数器的值被取并赋给 TIMER_START。但是，由于单位是皮秒，计数器可能会达到支持的最大值(大约 30.5 周后发生)，在这种情况下，计数器会再次从 0 开始计数。 -
TIMER_END: 对于正在进行的事件，这是当前时间，对于已完成的事件，这是事件完成的时间。 -
TIMER_WAIT: 这是事件的持续时间。对于仍在进行中的事件，它是自事件开始以来的时间量。
不包括计时的事务除外。
Note
不同的事件类型使用不同的计时器，因此您不能使用TIMER_START和TIMER_END列来对不同类型的事件进行排序。
计时以皮秒为单位(10 -12 秒)。选择这个单位是出于性能原因，因为它允许 MySQL 在尽可能多的情况下使用乘法(最便宜的数学运算和加法)。计时列是 64 位无符号整数，这意味着它们将在大约 30.5 周后溢出，此时值又从 0 开始。
虽然从计算的角度来看，使用皮秒是好的，但对人类来说不太实际。因此，函数FORMAT_PICO_TIME()的作用是将皮秒转换成人类可读的格式，例如:
SELECT FORMAT_PICO_TIME(111577500000);
+--------------------------------+
| FORMAT_PICO_TIME(111577500000) |
+--------------------------------+
| 111.58 ms |
+--------------------------------+
1 row in set (0.0004 sec)
MySQL 8.0.16 中增加了该功能。在早期版本中，您需要使用sys.format_time()函数来代替。
演员和对象
性能模式允许您配置默认情况下应该检测哪些用户帐户和模式对象。账户通过setup_actors表配置，对象通过setup_objects表配置。默认情况下，除了mysql、information_schema和performance_schema系统模式中的对象之外，所有帐户和所有模式对象都会被检测。
摘要
性能模式为基于语句摘要执行的语句生成统计信息。这是基于规范化查询的阿沙-256 哈希。具有相同摘要的语句被视为相同的查询。
规范化包括删除注释(但不包括优化器提示)，将空格改为单空格字符，用问号替换WHERE子句中的值，等等。您可以使用函数STATEMENT_DIGEST_TEXT()来获得规范化的查询，例如:
mysql> SELECT STATEMENT_DIGEST_TEXT(
'SELECT *
FROM city
WHERE ID = 130'
) AS DigestText\G
*************************** 1\. row ***************************
DigestText: SELECT * FROM `city` WHERE `ID` = ?
1 row in set (0.0004 sec)
类似地，您可以使用STATEMENT_DIGEST()函数来获取查询的 SHA-256 散列:
mysql> SELECT STATEMENT_DIGEST(
'SELECT *
FROM city
WHERE ID = 130'
) AS Digest\G
*************************** 1\. row ***************************
Digest: 26b06a0b2f651e04e61751c55f84d0d721d31041ea57cef5998bc475ab9ef773
1 row in set (0.0004 sec)
例如，如果您想要查询一个语句事件表，即events_statements_histogram_by_digest表或events_statements_summary_by_digest表，以找到关于具有相同摘要的查询的信息，那么STATEMENT_DIGEST()函数会很有用。
Note
升级 MySQL 时，不能保证给定查询的摘要保持不变。这意味着您不应该比较不同 MySQL 版本的摘要。
当 MySQL 计算摘要时，查询被标记化，为了避免过多的内存使用，这个过程允许的每个连接的内存量是有上限的。这意味着，如果您有大型查询(就查询文本而言)，规范化查询(称为摘要文本)将被截断。您可以使用max_digest_length变量配置在规范化过程中允许连接为令牌使用多少内存(默认为 1024，需要重启 MySQL)。如果您有大型查询，您可能需要增加这个值，以避免长度超过max_digest_length字节的查询之间的冲突。如果您增加了max_digest_length，您可能还想增加performance_schema_max_digest_length选项，它指定了存储在性能模式中的摘要文本的最大长度。但是，要小心，因为这将增加存储在性能模式中的所有摘要文本值的大小，并且由于性能模式表存储在内存中，这可能会导致内存使用的显著增加。作者已经看到了几个支持票，其中 MySQL 无法启动，因为摘要长度设置得太高，所以 MySQL 耗尽了内存。
Caution
不要盲目地增加摘要长度选项，否则可能会耗尽内存。
表格类型
您已经遇到了性能模式中可用的一些表。这些表可以根据它们包含的信息类型进行分组，本章前面提到的设置表和事件表构成了其中的两个组。表 5-3 总结了从 MySQL 8.0.18 开始可用的表的类型。
表 5-3
性能模式表类型
|表格类型
|
描述
|
| --- | --- |
| 设置 | 具有动态配置的表。这包括setup_consumers和setup_instruments。所有设置表的名称都以setup_.开头 |
| 事件 | 存储当前正在进行的或历史的单个事件的表。这包括events_statements_current。所有表都与其中一个事件使用者同名。常见的还有表名以events_开头，但不包括summary或histogram。 |
| 情况 | 实例表包含从互斥到预准备语句的实例信息。最常用的实例表是prepared_statements_instances，它包含服务器端准备语句的统计信息。除了table_handles之外，所有实例表都有以_instances结尾的表名。 |
| 摘要 | 汇总表可以被认为是一种报告。它们汇总事件表中的事件，因此您可以获得更长期的概述。最常用的汇总表是events_statements_summary_by_digest，它按照默认模式和语句摘要对语句事件数据进行分组。汇总表的另一个例子是file_summary_by_instance,它根据文件实例对与文件相关的统计数据进行分组。所有表名都包含_summary_或以status_开头。表名还包括_by_，后跟数据分组依据的描述。从 8.0.18 开始，有 45 个汇总表，这是最大的一组表。 |
| 柱状图 | 直方图表是类似于汇总表的报告表，但提供了语句延迟的直方图统计。目前有两种直方图表格:events_statements_histogram_by_digest和events_statements_histogram_global。 |
| 连接和螺纹 | 各种包含连接和线程信息的表格。这包括threads、session_account_connect_attrs、session_connect_attrs、accounts、host_cache、hosts和users表格。 |
| 复制 | 关于传统异步复制和组复制的复制配置和状态的信息。除了log_status以外的所有表名都以replication_开头。 |
| 锁 | 这个组包括三个表，其中包含关于数据和元数据锁的信息:data_locks、data_lock_waits和metadata_locks。 |
| 可变的 | 变量表包含关于系统和状态变量(全局和会话范围)以及用户变量的信息。所有的表名都包含单词variables或status。 |
| 克隆 | 使用克隆插件时有关状态和进度的信息。表格包括clone_progress和clone_status。 |
| 多方面的 | keyring_keys和performance_timers表。 |
最常用的表格是汇总表，因为它们提供了对数据的简单访问，这些数据本身可以用作报告，类似于您将在下一章的sys模式视图中看到的内容。
动态配置
除了可以使用SET PERSIST_ONLY或在配置文件中设置的传统 MySQL 配置选项之外，性能模式还通过设置表提供了自己独特的动态配置。本节解释了动态配置的工作原理。
表 5-4 列出了 MySQL 8 中可用的设置表。对于允许插入和删除的表，所有列都可以更改，但对于可设置的列，只列出非键列。
表 5-4
绩效模式设置表
|设置表
|
关键列
|
可设置列
|
描述
|
| --- | --- | --- | --- |
| setup_actors | HOST``USER``ROLE | ENABLED``HISTORY | 此表用于确定前台线程是否被检测，以及是否根据帐户默认收集了历史记录。ROLE列目前未被使用。您可以在该表中插入和删除行。 |
| setup_consumers | NAME | ENABLED | 此表定义了启用哪些使用者。 |
| setup_instruments | NAME | ENABLED``TIMED | 此表定义了启用和定时的仪器。 |
| setup_objects | OBJECT_TYPE``OBJECT_SCHEMA``OBJECT_NAME`` | ENABLEDTIMED` | 此表定义了启用和计时的模式对象。您可以在该表中插入和删除行。 | | `setup_threads` | `NAME` | `ENABLEDHISTORY` | 此表定义了缺省情况下检测哪些线程类型并收集历史记录。 |
对于带有HISTORY列的表格，只有在仪器也启用的情况下才能记录历史。同样，对于TIMED栏，仅当仪器或对象被启用时才相关。对于setup_instruments，注意不是所有的仪器都支持计时，在这种情况下TIMED栏总是NULL。
在设置表中，setup_actors和setup_objects表是特殊的，因为您可以为它们插入和删除行。这包括使用TRUNCATE TABLE语句删除所有行。因为表存储在内存中，所以不能随意插入任意多的行。相反，最大行数由performance_schema_setup_actors_size和performance_schema_setup_objects_size配置选项定义。默认情况下，两个选项都是自动调整大小的。需要重启 MySQL 来使对表大小的更改生效。
您使用常规的UPDATE语句来操作配置。对于setup_actors和setup_objects表，您也可以使用INSERT、DELETE和TRUNCATE TABLE。启用events_statements_history_long消费者的一个例子是
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_statements_history_long';
Query OK, 1 row affected (0.2674 sec)
Rows matched: 1 Changed: 1 Warnings: 0
当重新启动 MySQL 时，这种配置不是持久的，因此如果您想在没有配置选项的情况下更改这些表的配置，可以将所需的 SQL 语句添加到一个 init 文件中，并通过init_file选项执行它。
对性能模式的介绍到此结束，但是您将在本书的剩余部分看到许多使用这些表的例子。
摘要
本章涵盖了性能模式中最重要的概念。MySQL 是一个多线程进程，性能模式包括所有线程的信息，包括前台线程(连接)和后台线程。
工具对应于源代码中被检测的代码点，从而决定收集哪些数据。启用仪器时，除了记忆和错误仪器外，还可以选择对其进行计时。
消费者获取仪器收集的数据，对其进行处理，并通过性能模式表使其可用。十二个使用者代表四种事件类型，每种类型有三个作用域。
这四种事件类型是事务、语句、阶段和等待，涵盖不同的详细级别。这三个事件范围是当前或最后完成的事件的当前范围、仍然存在的每个线程的最后十个事件的历史范围以及最后 10，000 个事件的历史范围，而不考虑生成它们的线程。事件可以触发其他事件，所以它们形成一棵树。
一个重要的概念是摘要，它允许 MySQL 通过规范化查询来聚合数据分组。当您要寻找查询调优的候选对象时，这个特性将被证明是特别有用的。
最后，总结了性能模式中的各种类型的表。最常用的一组表是汇总表，它本质上是报告，使从性能模式中访问聚合数据变得容易。基于性能模式的报告的另一个例子——在一些汇总表的情况下——是在sys模式中可用的信息，这是下一章的主题。
