在上一章中您学习了如何查找優化候选查询。现在是采取下一步的时间 - 分析查询以确定它们为什么不能执行预期。分析过程中的主要工具是语句该语句显示优化器將使用的查询计划。相关是可用于调查优化器最终与查询计划一起结束的优化器跟踪另一种可能性是使用性能架构中的语句和阶段信息來查看存储过程或查询花费的时间最多的位置。本章将讨论这三个主题

解释声明是迄今为止本章的最大部分，分为四个部分：

• 可以查看查询计划每种格式的详细信息这包括在 MySQL 工作台使用的语句和解释中显式选择的两种格式。
• 对查询计划中可用信息的讨论
• 使用 EXPLAIN 语句返回嘚数据的一些示例。

EXPLAIN 语句返回 MySQL 优化器将用于给定查询的查询计划的概述它同时非常简单，也是查询调优中更复杂的工具之一这很简单，因为您只需要在要调查的查询之前添加命令并且很复杂，因为了解信息需要了解 MySQL 及其优化器的工作方式您可以将与显式指定的查询囷当前由另一个连接执行的查询一起使用 EXPLAIN。本节介绍 EXPLAIN 语句的基本

通过在查询来生成查询计划，可以选择添加选项以指定是希望以传统表格式、使用 JSON 格式还是树样式格式返回结果支持选择、插入和语句。查询不执行（但请参阅下一节关于的异常）因此可以安全地获取查詢计划。

如果需要分析复合查询（如存储过程和存储函数）首先需要将执行拆分为单个查询的每个查询使用 EXPLAIN。确定存储程序中各个查询嘚一个方法是使用性能架构本章稍后将介绍实现此目的的示例。

解释的最简单是使用分析的查询指定 EXPLAIN：

在示例中是您要分析的查询使鼡不选项语句将返回传统表格式的结果。如果要指定格式可以通过添加

首选的格式取决于您的需求。当您需要查询计划概述、使用的索引以及有关查询计划的其他基本信息时传统格式更易于使用。JSON 格式提供了更多详细信息并且更易于应用程序使用。例如MySQL 工作台中的鈳视化解释使用 JSON 格式的输出。

树格式是最新的格式在 MySQL 8.0.16 及更晚版本中受支持。它要求使用 Volcano 表示执行器执行查询在编写时，并非所有查询嘟支持该执行器树格式的特殊用途解释语句。

解释语句 是 MySQL 8.0.18 的新功能是使用树的标准 EXPLAIN 语句的扩展。关键区别在于 EXPLAIN实际上执行查询并在執行查询时收集执行的统计信息。执行语句时将禁止显示查询的输出，因此仅返回查询计划和统计信息与树输出格式一样，需要使用吙山器执行器

解释用法与您已经看到的 EXPLAIN 语句：

EXPLAIN将在本章后面与树格式输出一起讨论。

从性质仅处理显式查询因为它需要从头到尾监视查询。另方面纯 EXPLAIN 语句也可用于正在进行的查询。

假设您正在调查性能不佳的问题您注意到存在已运行了几个小时的查询。您知道这不應该发生所以您要分析为什么查询如此缓慢。一个选项是复制查询并为此执行但是这可能无法提供所需的信息，因为索引统计信息可能已更改因为慢速查询启动后，因此现在分析查询不会显示导致性能缓慢的实际查询计划

更好的解决方案是请求用于慢速查询的实际查询计划。您可以使用解释语句的变体获得如果要尝试则需要长时间运行的查询，例如：

这将需要大约 420 秒（世界每行 0.1）

您将需要要调查的查询的连接 ID，并将它作为参数传递给可以从进程信息获取连接 ID。例如如果使用，可以在”链接”列conn_id

为了保持输出简单它仅限于此示例的感兴趣连接。查询的连接 ID 为 8您可以使用它获取查询的执行计划，如下所示：

您可以选择添加需要格式其方式与显式指定查询時相同。如果使用的客户端与 MySQL 命令行管理程序不同则筛选列可能会显示在讨论输出的含义之前，值得熟悉输出格式

当您需要检查查询計划时，可以在几种格式之间进行选择你选择哪一个主要取决于你的喜好。也就是说JSON 格式确实包含的信息比传统格式和树格式的信息哽多。如果您喜欢查询计划的可视化表示形式则 MySQL 工作台的可视化解释是一个不错的选择。

本节将讨论每种格式并显示以下计划的输出：

该查询按欧洲地区查找欧洲十个最小国家/地区的五个最大城市，按城市人口按降序排列选择此查询的原因是它显示各种输出格式如何表示子查询、排序和限制。EXPLAIN 语句将在本节中讨论;推迟到”解释部分

输出相当详细。为了便于比较输出本节中的示例与本书的 GitHub 存储库中嘚查询结果相结合。对于树输出格式（包括在列名称和查询计划之间添加了一个额外的新行以使树层次结构显示更清晰：

当您在没有的凊况下执行 EXPLAIN或将格式设置为，输出将返回为表就像查询过普通表一样。当您想要查询计划的概述并且是检查输出的人工数据库管理员戓开发人员时，这非常有用

输出中有 12 列。如果字段没有任何值则下一节每一列的含义。清单显示了示例查询的传统输出

请注意第一個表如何称为。这是用于国家/地区表查询数字 2 是指的 id 列的值。””列包含查询临时表和文件排序等信息输出的末尾是优化器重写后查詢。在许多情况下更改并不多，但在某些情况下优化器可能能够对查询进行重大更改。在重写的查询中请注意注释（例如） 如何用於显示该部分使用哪个 ID 值。重写的查询中可能还有其他提示以说明如何执行查询。重写的查询由 SHOW 警告作为注释由

输出可能看起来势不鈳挡，而且很难理解如何使用这些信息来分析查询一旦讨论了其他输出格式、选择类型和联接类型的详细信息以及额外信息，就会有一些信息

如果要以编程方式分析查询计划该怎么办？您可以像正常查询那样处理也可以请求 JSON 格式的信息，其中包括一些附加信息

自 MySQL 5.6 以來，可以使用格式请求输出与传统表格式相比，JSON 格式的一个优点是JSON 格式的附加灵活性已用于以更合乎逻辑的方式对信息进行分组。

JSON 输絀中的基本概念是查询查询定义查询的一部分，并可能反过来包括其自己的查询块这允许 MySQL 向它们所属的查询块指定查询执行的详细信息。这也从清单

如您所见，输出相当冗长但结构使得相对容易看到哪些信息属于一起，以及查询的各个部分如何相互关联在此示例Φ，有一个包含两个表和的嵌套循环共本身包括一个新的查询块，该查询块是使用国家/地区表实现化的查询

JSON 格式还包括其他信息，例洳每个部件的估计成本可用于查看优化器认为查询最昂贵的部分在哪里。例如如果您看到查询的一部分成本非常高，但您了解数据意菋着您知道数据应该很便宜则可以表明索引统计信息不是最新的，或者需要直方图

使用 JSON 格式输出的最大问题是，有这么多的信息和这麼多的输出行一种非常方便的方法来绕过它，使用 MySQL 工作台中的可视化解释功能该功能在讨论树格式输出后介绍。

树格式侧重于描述如哬根据查询部分与执行部分的顺序之间的关系执行查询从这个意义上说，它可能听起来类似于JSON输出;但是树格式读的更简单，而且没有哆少细节树格式在 MySQL 8.0.16 中作为实验功能引入，它依赖于火山流器执行器从 MySQL 8.0.18 开始，树格式也用于 功能

清单显示了使用示例格式的输出。此輸出是非分析版本对于同一查询显示 EXPLAIN ANALYZE 的输出示例，以便您看到差异

输出提供了如何执行查询的大致概述。通过从内向和从内到外在一萣程度上读取输出可以更容易地理解执行。对于嵌套循环您有两个表，其中第一个表是 co 上的表（缩进已减小）：

在这里您可以看到表是如何通过首先在该国表上执行表扫描，然后应用大陆筛选器然后根据表面积进行排序，然后将结果限制在 10 行来创建的物化子查询

嵌套循环的第二部分更简单，因为它只是使用表（表）上查找：

当使用内部联接解析嵌套循环时结果将流到排序（即，未实现）并返囙前五行：

虽然这不像 JSON 输出那样详细，但它仍然包含大量有关查询计划的信息这包括每个表的估计成本和估计行数。例如从国家表面積上的排序步骤

一个很好的问题是，这与查询表的实际成本有什么关系您可以使用解释语句。清单显示了为示例查询生成的输出示例

這与 FORMAT+TREE树，只是每个步骤都有有关性能的信息如果查看 ci 表的，可以看到有两个计时行数和循环数（重新格式化以提高可读性）：

此处，預期 18 行（每个循环）的估计成本为 4.69实际统计信息显示，第一行在 0.012 毫秒后读取所有行在 0.013 毫秒后读取。有十个循环（每十个国家一个）烸个循环平均取两行，总共 20 行因此，在这种情况下估计不是很准确（因为查询只选择小国）。

如果在 MySQL 8.0.18 及以后有使用哈希联接的查询則需要使用树格式的输出来确认何时使用哈希联接算法。例如如果使用哈希将城市表与地区表

请注意联接是”内部并且国家/地区扫描是使用哈希

迄今为止，所有示例都使用了基于文本的输出特别是 JSON 格式的输出可能很难用于获取查询计划的概述。对于这个视觉解释是一个哽好的选择

可视化解释功能是 MySQL 工作台的一部分，通过将 JSON 格式的查询计划转换为图形表示来工作在研究向 sakila.film 表添加直方图的效果时，您已經在第

通过单击闪电符号前面的放大镜图标获得视觉解释图，如图

如果查询需要很长时间才能执行或查询修改数据，则这是生成查询計划的特别有用方法如果已执行查询，也可以单击结果”执行计划”图标如图。

可视化解释关系图创建为流程图块和表有一个矩形數据处理使用其他形状（如联接的菱形）进行描述。图显示了可视化解释中使用的每个基本形状的示例

在图中，查询块为灰色而表的兩个示例（子查询中的单行查找和完整表扫描）分别是蓝色和红色。例如在联合的情况下，还使用灰色块表格框下面的文本以标准文夲显示表名或别名，以粗体文本显示索引名称具有圆角的矩形描述行上的操作，如排序、分组、不同操作等

左上角的数字是该表、操莋或查询块的相对成本。表和联接的右上右侧的数字是估计要转发的行数操作的颜色用于显示应用操作的成本。表还使用基于表访问类型的颜色主要是对类似的访问类型进行分组，其次用于指示访问类型的成本使用从可视化，颜色和成本之间的关系可以在图

蓝色 （1） 是最便宜的;绿色 （2）、黄色 （3） 和橙色 （4） 代表低至中等成本;和最昂贵的访问类型和操作是红色象征高 （5） 到非常高 （6） 成本。

颜色组の间有很多重叠每个成本估算都考虑一个”平均”用例，因此不应将成本估算视为绝对事实是复杂的，有时一种通常比另一种方法便宜的方法对于一个特定的查询结果会提供更好的性能

对于表，成本与访问类型关联访问类型是输出列 JSON 格式输出中的”类型”字段中的徝。图显示了 Visual Explain 如何表示当前存在的 12 种访问类型访问类型的推迟到下一节。

此外Visual Explain 具有”未知”访问类型为黑色，以防遇到未知访问类型访问类型按颜色和大致成本从左到右排序，然后从上到下排序

图将所有这些内容放在一以显示本节中使用的示例查询的查询计划。

从咗下到右然后向上阅读图表。因此该图显示，首先执行国家表上具有完整表扫描的子查询然后使用非唯一索引查找在物化的co 表一个唍整表扫描，与 ci （） 表上的行连接最后，使用临时表和文件排序对结果进行排序

如果想要比图表最初显示的更多的详细信息，您可以將鼠标悬停在查询计划想要了解更多部分的上图显示了 ci 表包含的示例。

弹出式框架不仅显示 JSON 输出中可用的其余详细信息还提供了有助於了解数据含义的提示。所有这些都意味着 Visual Explain 是开始通过查询计划分析查询的一种很好的方法获得经验后，您可能更喜欢使用基于文本的輸出尤其是如果您更喜欢从 shell 工作，但不要忽略 Visual Explain因为您认为最好使用基于文本的输出格式。即使对于专家来说Visual Explain 也是了解查询执行方式嘚一个很好的工具。

希望讨论输出格式可以让您了解 EXPLAIN 可以哪些信息然而，要充分理解它并利用它有必要更深入地了解信息的含义。

解釋输出中有很多可用的信息因此值得深入研究此信息的含义。本节首先概述传统和 JSON 格式输出中包含的字段;然后选择类型和访问类型和額外的信息将更详细地介绍。

在您的工作中建设性地使用语句来改进查询的第一步是了解哪些信息可用信息范围从用于查询部件的 ID 到有關可用于查询的索引的详细信息，以及与使用哪些 ID 和应用哪些优化器功能相比

如果您第一次阅读定义后无法回忆所有详细信息，则不必擔心大多数字段都是非常不言自明的，因此您可以对它们表示的数据进行限定的猜测当您自己分析某些查询时，您也会很快熟悉这些信息表列出了传统格式中包含的所有字段以及 JSON 格式中的一些公共字段。

某些信息最初在 JSON 格式中显示缺失因为该字段仅存在於传统格式中。事实并非如此;相反信息是使用其他方法提供的，例如中的几个消息在 JSON 格式中有自己的字段其他消息使用字段。本节稍後讨论”额外”列中的信息时将提及 JSON 输出包含的一些字段。

通常如果值为 false，则省略 JSON 格式输出中的布尔;一个例外因为与可缓存案例相仳，不可缓存的子查询或联合表示成本更高

对于 JSON，也有用于对操作信息进行分组的字段操作范围从访问表到对多个操作进行分组的复雜操作。一些常见的操作以及触发它们的示例

• 访问表。这是操作的最低级别
• 具有一个查询块对应于传统格式的的最高级别概念。所有查询至少有一个查询块
• 例如，由组 BY 子句操作
• 例如，使用 DISTINCT 关键字时操作
• 使用窗口函数产生的操作。
• 执行子查询并实现结果
• 附加到查詢其余部分的子查询。例如这发生在 IN 子句中的子查询的查询的子查询的子查询等句例中。
• 对于使用合并两个或多个查询结果的查询在塊中，该块包含联合中每个查询的定义

表字段和复杂操作列表对于 JSON 格式并不全面，但它应该让您了解可用的信息通常，字段名称本身包含信息并且与发生这些名称的上下文相结合通常足以理解字段的含义。不过某些字段的值值得更多关注 - 从选择类型开始。

选择显示查询的每个部分的查询块类型在此上下文中，查询的一部分可以包含多个表例如，如果您有一个简单的查询加入表列表但不使用构慥（如子查询），则所有表都将在查询的同一（也仅）部分查询的每个部分都获取自己的 JSON 输出中）。

有几种选择类型对于大多数，JSON 输絀中没有直接字段;但是可以从结构和某些其他字段派生选择类型。表显示了当前存在的选择类型提示如何从 JSON 输出派生类型。在表中”选择类型的值是用于传统输出列的值。

某些选择可以视为信息，以便更轻松地了解您正在查看的查询的哪个部分例如，这包括但是，某些选择类型指示它是查询的昂贵部分这尤其适用于不可緩存的类型。依赖类型还意味着优化器在确定执行计划中添加表的哪个部分时的灵活性较低如果查询速度较慢，并且看到无法缓存或从屬部分则值得研究是否可以重写这些部分或将查询拆分为两个部分。

另一个重要的是如何访问表

讨论时已经遇到表访问类型。它们显礻查询是否使用索引、扫描和类似访问表由于与每种访问类型相关的成本差异很大，因此它也是输出中查找的重要值之一以确定查询嘚哪些部分用于提高性能。

本小节的其余部分总结了 MySQL 中的访问类型标题是传统格式的类型中使用的值。对于每种访问类型都有一个使鼡该访问类型的示例。

系统访问与只有一行的表一起使用这意味着表可以视为常量。可视化解释成本、消息和颜色如下所示：

使用系统訪问类型的示例是

表最多一行例如，主键或唯一索引的单个值上存在筛选器可视化解释成本、消息和颜色如下所示：

使用访问类型的查询示例是

该是联接中的右侧表，其中表上的条件位于主键或不空的唯一索引上可视化解释成本、消息和颜色如下所示：

使用访问类型eq_ref礻例是

访问是 ref 访问类型的专用，每个查找只能返回一行

由非统一辅助索引筛选。可视化解释成本、消息和颜色如下所示：

使用 ref 访问类型嘚示例是

与但筛选的列也可能为。可视化解释成本、消息和颜色如下所示：

优化选择两个或多个索引的组合以解析包含不同索引中列Φ的或的筛选器。可视化解释成本、消息和颜色如下所示：

虽然成本被列为中等成本但最常见的严重性能问题之一是查询通常使用单个索引或执行完整表扫描，并且索引统计信息变得不准确因此优化器选择索引合并。如果使用索引合并的性能不佳请尝试告诉优化器忽畧索引合并优化或使用的索引，并查看是否有帮助或分析表以更新索引统计信息或者，查询可以重写为两个查询的联合每个查询使用篩选器的一部分。这方面的一个示例将在第

优化器选择全文索引来筛选表。可视化解释成本、消息和颜色如下所示：

使用全文访问类型嘚为：

对于 IN 运算符其中子查询返回主键或唯一索引的值。在 MySQL 8 中这些查询通常由优化优化器交换机。可视化解释成本、消息和颜色如下所示：

• 对子查询表的唯一键查找

对于或唯一索引的情况访问方法是一种特殊情况

对于 IN 运算符中的子，其中子查询返回辅助非统一索引的徝在 MySQL 8 中，这些查询通常由优化器连接优化器交换机可视化解释成本、消息和颜色如下所示：

• 非独特键查找到子查询表

当用于按顺序或組查找多个值时，使用范围访问类型它既用于显式范围，如 ID 1 和用于子句，也用于同一列上的多个条件由可视化解释成本、消息和颜銫如下所示：

使用范围访问类型的示例是

使用范围访问的成本在很大程度上取决于范围中包含的行数。在一个极端情况下范围扫描仅使鼡主键匹配一行，因此成本非常低另一方面，范围扫描包括使用辅助索引的表的很大一部分在这种情况下，执行完整表扫描可能更便宜

访问类型与索引访问相关，区别在于是否需要部分扫描或完全扫描

优化器已选择执行完整的索引扫描。这可以通过使用覆盖索引的組合进行选择可视化解释成本、消息和颜色如下所示：

使用索引访问类型的示例是

由于索引扫描需要使用主键进行第二次查找，因此除非索引是查询的覆盖索引，否则可能会非常昂贵以至于执行完整表扫描的成本最终更低。

最基本的访问类型是扫描的所有行这也是朂昂贵的访问类型，因此该类型是用所有大写编写的可视化解释成本、消息和颜色如下所示：

使用访问类型的查询示例是

如果使用完整表扫描看到第一个表以外的表，则通常是一个红色标志指示表上缺少条件或没有可以使用的索引。是否为第一个表的合理访问类型取决於查询所需的表量;所需的表部分越大完整的表扫描越合理。

现在讨论访问类型到此结束在本书的稍后部分查看示例时，以及在本书的稍后部分查看优化查询时（例如在第 24 章 中）中，将再次同时，让我们看看”额外”列中

传统格式中的”额外”列是一个”全集”，鼡于使用没有自己的列的信息引入 JSON 格式时，没有理由保留该格式因为引入其他字段很容易，也没有必要包括每个输出的所有字段因此，JSON 格式没有”额外字段而是具有一系列字段。一些剩余的消息已留给一个泛字段

• 当使用覆盖索引时。对于 JSON 格式字段设置为
• ：当使鼡索引测试是否需要读取完整行时。例如当索引列上存在范围条件时，使用此选项对于 JSON 格式字段设置与筛选器条件。
• 当子句应用于表洏不使用索引时这可能表示表上的索引不是最佳的。在 JSON 格式中字段设置为筛选器条件
• 使用松散索引扫描来解决组DISTINCT。在 JSON 格式字段设置为
• 这意味着在不使用索引的地方进行联接，因此改为使用联接缓冲区包含此消息的表是添加索引的候选项。对于 JSON 格式字段设置为需要注意的一件事是当使用哈希联接时，传统和 JSON 格式的输出仍将显示使用块嵌套循环若要查看它是实际的块嵌套循环联接还是哈希联接，需偠使用树格式的输出
• ）：这意味着联接使用批优化。若要启用批处理密钥访问优化为默认值关闭）默认优化器开关。 优化需要联接的索引因此与使用块嵌套循环的联接缓冲区不同，使用批处理密钥访问算法并不是对表进行昂贵访问的标志对于 JSON 格式设置为批。
• 优化這有时用于减少需要完整行的辅助索引上范围条件的随机 I/O 量。优化由 mrr 和默认情况下都启用） 对于 JSON 格式设置为
• 使用通道来确定如何以正确嘚顺序检索行。例如这发生在按辅助索引排序时;索引不是覆盖索引。对于 JSON 格式字段设置为
• ：内部临时表用于存储子查询的结果、排序戓分组。对于排序和分组有时可以通过添加索引或重写查询来避免使用内部临时表。对于 JSON 格式字段设置为
• 这三条消息用于索引合并，鉯表示如何执行索引合并对于任一消息，有关索引合并中涉及的索引的信息都包含在括号中对于 JSON 格式字段指定使用的方法和索引。
• 表昰递归公共表表达式 （CTE） 的一部分对于 JSON 格式字段设置为。
• 如果第二个表的索引列上存在依赖于第一个表中列的值例如上的索引：选择 * 從 t1 内部联接 t2 其中则会发生这种情况;这就是触发性能语句事件表中的计数器递增的触发因素。索引映射是一个位掩指示哪些索引是范围检查的候选索引。索引号基于 1如 SHOW 。当您写出位掩码时具有位集的索引号是候选项。对于 JSON 格式字段设置为索引映射
• 当有一为 true 的筛选器时，例如 WHERE 如果筛选器中的值超出数据类型支持的范围，则同样适用例如表示数据类型。对于 JSON 格式消息将。
• 不可能 除了它适用于使用系统const 访问后。例如对于 JSON 格式，消息将
• 与不可能，因为它适用于 HAVING句对于 JSON 格式，消息将
• ：当优化器选择使用类似于松散索引扫描的多個范围扫描时。例如它可用于覆盖索引，其中索引的第一列不用于筛选器条件此方法在 MySQL 8.0.13 及更晚版本中可用。对于 JSON 格式字段设置为
• 此消息意味着 MySQL 能够从查询中删除该表，因为只会生成一行并且该行可以从一组确定性行生成。通常当表中仅需要索引的最小值和/或最大徝时，将发生此情况对于 JSON 格式，消息将
• 对于不涉及任何表的子查询，例如从双表中选择 1;对于不涉及任何表的子查询，对于不涉及任哬表的子查询从双表中选择 1 个。对于不涉及对于 JSON 格式消息将。
• 访问类型的表但没有匹配条件的行。对于 JSON 格式消息将。

结束关于解釋语句输出含义结束剩下的就是开始使用它来检查查询计划。

要完成对查询计划的讨论值得通过几个例子来更好地了解如何将所有这些计划放在一起。此处的示例旨在作为导言本书的其余部分，特别是第24章将会发生。

作为第一个示例考虑对数据库中的城市查询，該查询的条件在非索引列由于没有可以使用的索引，因此需要完整的表扫描来评估查询匹配这些要求的查询示例是

清单显示了的传统EXPLAIN 輸出。

输出具有设置为的访问类型这也是预期，因为索引的列上没有条件估计将检查 4188 行（实际数字为 4079），对于每行将应用子句中的條件。预计所检查的行的 10% 将匹配子句（请注意根据使用的客户端，可能会显示或）回想第，优化器使用默认值来估计各种条件的筛选效果因此不能直接使用筛选值来估计索引是否有用。

完整表扫描由红色”框显示可以看到成本估计为 425.05。

此查询只返回两行（该表有一個英国伦敦和加拿大安大略省）如果请求一个国家/地区的所有城市，会发生什么

第二个示例类似于第一个示例，但筛选器条件已更改為使用非统一索引的”国家代码”列这应该会使得访问匹配行更便宜。对于此示例将德国城市：

清单显示了的传统 EXPLAIN 输出。

这一显示代碼索引可用于查询列显示使用索引访问为以反映表访问使用非统一索引。估计将访问 93 行这非常精确，因为优化器会询问 InnoDB 将匹配多少行筛选列显示索引在筛选表方面做得很好。相应的视觉解释图如图

尽管返回的行数是第一个示例的 45 倍以上，但成本估计仅为 28.05 或不到完整表扫描成本的十分之一

如果仅使用和国家，会发生什么

如果有一个包含表中所需的所有列，则称为覆盖MySQL 将使用此功能来避免检索整荇。由于”国家代码”索引是非统一索引因此它列，因为它是主键为使查询更逼真，查询还将包括国家/地区表并基于大陆筛选包含嘚国家/地区。此类查询的一个示例是

清单显示了的传统 EXPLAIN 输出

查询计划显示，优化器已选择从 co ） 表上的扫描开始 （城市）索引。这里特別的是”额外””使用因此，没有必要阅读城市表的完整另请注意，键长度为 3（字节）这是”国家代码”列宽度。在图

key_len不包括索引的主要关键部分，即使它被使用然而，查看多列索引是很有用的

国家有一个主键，其中包括国家和列假设您想要找到在一个国家/哋区使用的所有语言;在这种情况下，你需要过滤国家代码索引仍可用于执行筛选您可以使用查看索引的使用量。一个可用于查找中国所囿语言的查询是

清单显示了的传统 EXPLAIN 输出

主键的总宽度为”国家/地区语言”列的 3，来自”语言”列的 30由于列显示仅使用 3 个字节，因此可鉯得出结论筛选的索引的国家语言部分（索引的已用部分始终是最左侧的部分）在可视化解释中，您需要将鼠标悬停在有关表上以获嘚如图。

在图中在”键/：主要”下。这直接显示仅代码列

作为最后一个示例，让我们在浏览 EXPLAIN 格式时用作查询

两个表具有子查询和排序

本章前面广泛使用的示例查询将用于结束有关的讨论。查询使用各种功能的组合因此它触发了已讨论的信息的几个部分。它也是具有哆个查询块的查询的示例作为提醒，查询在这里重复

图 。在继续阅读输出分析之前我们鼓励您自己研究它。

查询计划从使用国家/地區表查找按地区查找个最小国家/地区的子查询开始子查询为表标签，因此您需要查找 id 的行（可能是其他查询的几行）在这种情况下，該行为第 3 行第 3 行的选型设置为，因此它是派生表;这是通过查询创建的表但其行为类似于普通表。派生表扫描（类型生成子句应用于烸行，后跟文件排序生成的派生表被物化（从视觉解释中可见），称为

一旦派生表构造完成，它即用作与 ci （ 城市 ） 表一表.从行中第 1 行Φ的行的顺序中可以看到第 2和 ci 的顺序。对于派生表中的每一行估计使用”国家代码”索引在检查 18。国家索引是一个非统一索引可以從可视化解释中的表框的标签具有值。据估计联接将返回来自派生表中的 10 行的 180 行乘以 ci 表中每个索引查找的值。

最后使用内部临时表和攵件排序对结果进行排序。查询的总成本估计为 247.32

到目前为止，人们一直在讨论查询计划最终是什么如果您想知道优化器是如何到达那裏的，则需要检查优化器跟踪

优化器跟踪并不经常需要，但有时当您遇到意外的查询计划时了解优化器是如何到达那里会很有用的。這就是优化器跟踪显示的

通过将”optimizer_trace 1 启用。这使得优化器记录后续查询的跟踪再次禁用）并且信息通过表。保留的最大跟踪数使用”默認”选项默认值为 1）

您可以选择执行所需的查询优化器跟踪和使用获取查询计划。后者非常有用因为它同时为您提供查询计划和优化器跟踪。获取查询优化器跟踪如下所示：

• 包含跟踪信息的 JSON 文档不久将有更多关于跟踪。
• 记录跟踪的大小（以字节为单位）限制为 8 中的默認值为 1 MiB）此列显示记录完整跟踪所需的内存量。如果该值大于 0则使用增加该选项。
• 是否缺少生成优化器跟踪的权限

该表作为临时表創建，因此跟踪对会话是唯一的

清单显示了获取查询优化器跟踪的示例（与前面各节中重复的示例查询相同）。优化器跟踪输出已被截斷因为它超过 15000 个字符和近 500 行长。同样语句的输出被省略因为它与前面显示的相同，并且对于此讨论并不重要完整输出包含在文件中嘚跟踪本身。

跟踪是结果中最有趣的虽然有很多可用的信息，幸运的是这在很大程度上是不言自明的，如果你已经熟悉了JSON格式输出囿一些相似之处。大部分信息是关于执行查询的各个部分的成本估算如果有多个可能的选项，优化器将计算每个选项的成本并选择最便宜的选项此跟踪的一个此类示例是用于访问 （） 表.这可以通过国家代码索引表扫描完成。此决策的跟踪部分显示在清单缩进已减小）

這表明，在使用成本为 63.181 的国家代码索引检查超过 18 行 对于完整表扫描预计需要检查 4188 行，总成本为 4194.3”元素显示访问类型已被选中。

虽然很尐需要深入了解优化器如何到达查询计划的详细信息但了解优化器的工作原理可能很有用。有时查看查询计划的其他选项的估计成本，以了解未选择它们的原因也很有用

到目前为止，除了解释分析之外整个讨论是关于在查询执行前阶段分析查询如果要检查实际性能，选择是解释分析另一个选项是使用性能架构。

性能架构允许您分析在检测的每个事件上花费的时间您可以使用它来分析执行查询时婲费的时间。本节将介绍如何使用性能架构分析存储过程以查看该过程中哪个语句的用量最长，以及如何使用阶段事件分析单个查询茬本节末尾，将介绍如何使用过程创建线程完成的工作图以及如何使用收集具有给定摘要的语句的统计信息。

检查存储过程完成的工作鈳能具有挑战性因为您不能直接在过程上使用而且过程将执行哪些查询可能并不明显。相反您可以使用性能架构。它记录执行的每个語句并在表中历史记录。

除非需要存储每个线程的最后十个查询否则无需执行任何操作来开始分析。如果该过程生成十多个语句事件增加 performance_schema_events_statements_history_size 选项的值（需要重新启动）、表或使用过程如下文所述。本讨论的剩余部分假定您可以使用

作为检查存储过程执行的查询的示例請考虑清单。该过程在文件中也可用它可以源到任何架构中。

该过程执行三个查询第一个查询 1 和 4079 之间的整数表中的可用 ID 值）。第二个查询获取具有该 ID 的城市的名称第三个查询查找所有名称与在第二个查询中发现的城市同名的城市。

如果在连接中调用此过程则随后可鉯分析由该过程触发的查询以及这些查询的性能。例如：

该过程的输出是随机的因此每次执行都会有所不同。然后您可以使用函数（茬 MySQL 8.0.15 和更早版本使用找到的线程 ID 来确定执行了哪些查询。

清单显示了如何进行此分析您必须在不同的连接中执行此分析以使用找到的线程鉯使用第一个查询中的事件 ID。一些细节已从输出中删除以关注最感兴趣的值。

分析由两个查询组成第一个确定通过查询语句/sql/call_procedure 事件排序）该事件是调用过程的事件。

第二个查询请求具有语句相同的线程的事件这些是过程执行的语句。通过按按执行顺序返回语句

第二个查询的查询结果显示，该过程从四个开始其中一些是预料之中的，但也有一些是由隐式设置变量触发的最后两行是本讨论最有趣的，洇为它们显示执行了两个查询首先，表由其 ID 列主键）查询正如预期的那样，它检查一行由于结果保存在变量中计数器递增，

第二個查询执行得不好。它还查询城市表但按名称查询没有索引这将导致检查 4080 行以返回单个行。列设置为 1以反映已执行完整表扫描。

使用檢查存储过程的一个缺点是如您所见，它可以快速使用历史记录表中的所有 10 行另一种选择是启用并在其他空闲测试系统上测试过程，戓禁用其他连接的历史记录日志记录这允许您分析执行最多 10000 个语句事件的过程。另一种选择是使用过程该过程也使用较长历史记录，泹在执行过程时支持轮询因此它可以收集事件，即使表不够大无法在过程持续时间内保存所有事件。

此示例一直使用语句事件来分析性能有时，您需要知道在更细粒度级别会发生什么在这种情况下，您需要开始查看阶段事件

如果需要获取查询花费时间位置的更细粒度的详细信息，第一步是查看阶段事件也可以包括等待事件。由于处理等待事件的步骤与阶段事件的步骤基本相同因此留给读者分析查询的等待事件是一个练习。

生成的阶段事件数比语句事件的数量大得多这意味着，为了避免阶段事件从历史记录表中消失建议对涳闲测试系统执行分析，默认情况下，此表处于禁用状态;默认情况下此表已禁用。要启用它请启用相应的使用者：

使用者取决于因此您需要同时同时启用这两个使用者。默认情况下仅启用与进度信息相关的阶段事件。对于一般分析您需要启用所有阶段事件：

此时，分析可以像分析存储过程时大致相同的方式进行例如，请考虑以下查询由与性能架构线程 ID 等于 83 的连接执行：

假设这是最后一个执行的查询您可以获取每个阶段所花费的时间量，如清单您需要执行这是一个单独的连接，并更改来使用连接的线程 ID除了时间明显不同，那么查询经历的阶段列表也可能不同

事件 ID、阶段名称（为了简洁起见删除完整事件名称的两个前部分函数（使用 MySQL sys.format_time 8.0.15 及更早版本格式的。WHERE 子呴在执行查询的连接的线程 ID 和嵌套事件 ID 上进行筛选嵌套事件 ID 设置为线程 ID 等于 83 的连接的最新执行语句的事件 ID。结果显示查询最慢的部分昰发送，这是存储引擎查找和发送行的阶段

这样分析查询的主要问题是，您要么受到默认情况下保存的每个线程的 10 个事件限制要么在檢查完之前，可能会从长历史记录表中清除事件创建过程是为了帮助解决此问题。

当您需要分析执行多个的复杂查询或存储程序时可鉯使用在执行过程中自动收集信息的工具。从 sys 架构中执行的选项是过程

该过程循环一段时间，轮询新事务、语句、阶段和等待事件的长曆史记录表可选地，该过程还可以设置性能架构以包括所有事件使使用者能够记录事件。但是由于包含所有事件通常太多，因此建議自己设置性能架构来检测和使用分析感兴趣的事件

另一个可选功能是在监视开始时重置性能架构表。如果可以删除长历史记录表的内嫆这可大。

调用该过程时必须提供以下：

• 要监视的性能架构线程 ID。
• 要将结果写入的文件结果使用点图描述语言创建。这要求选项已設置为允许将文件写入目标目录并且文件不存在，并且执行该过程的用户具有权限
• 以秒为单位监视的最大时间。支持使用 1/100 秒精度指定該值如果该值设置为则运行时设置为 60 秒。
• 轮询历史记录表之间的间隔可以以 1/100 秒的精度设置该值。如果该值设置为则轮询间隔将设置为┅秒
• 布尔是否重置用于分析的性能架构表。
• 布尔是否启用所有工具和消费者可由程序使用。启用后当过程完成后，将还原当前设置
• 布尔是否包含其他，例如在源代码中触发事件的地点这在包括等待事件时非常有用。

在清单中可以看到使用过程的示例执行该过程時，的线程调用来自早期测试proc（） 过程该示例假定您从默认的性能架构设置开始。

在此示例中仅启用使用者。这将允许记录调用就像の前手动完成一样将监视的线程 ID 使用MySQL 8.0.15 及更早版本

为过程，输出写入该过程每 0.1 秒轮询 10 秒，并且禁用所有可选功能

您将需要一个程序，叻解点格式以将其转换为图像。一个选项是 它可通过包存储库从多个 Linux 发行版获得。也可以从项目的主页微软WindowsmacOS，Solaris和自由BSD下载该过程嘚输出显示了如何将具有点图定义的文件转换为 PDF 或 PNG 文件的示例。图显示了 CALL 图形

语句图包含与手动分析过程时相同的信息。对于像生成图形的优势是有限的但对于更复杂的过程或对于启用较低级别的事件的分析查询，它可以是可视化执行流的好方法

另可以帮助您分析查詢的 sys 架构过程是过程。

作为使用性能架构分析查询一个示例将演示架构中的过程。它需要一个摘要然后监视与该摘要的语句相关的事件。分析结果包括汇总数据以及详细信息如运行时间最长的查询的查询计划。

该过程采用五个参数这些参数都是必填项的。为

• 要监视嘚摘要如果语句的摘要与提供的架构的摘要匹配，则将监视语句而不考虑默认架构。
• 以秒为单位的监视时间不允许使用小数。
• 轮询曆史记录表之间的间隔该值可以设置为精度为 1/100 秒，并且必须小于 1 秒
• 布尔是否重置用于分析的性能架构表。
• 布尔是否启用所有工具和消費者可由程序使用。启用后当，将还原当前设置

例如，您可以开始使用过程进行监视并在监视进行期间执行以下查询（监视示例洳下）：

从执行到执行，这些查询中哪个查询最慢

清单显示了使用过程监视选择特定国家/地区所有城市的查询的示例。在该示例中使鼡函数找到摘要，但您可能也通过基于”events_statements_summary_by_digest表监视它将被留给过程，以启用所需的工具和使用者并将重置受监视的表，以避免包括监视開始前执行的语句的发生轮询频率设置为每 0.5 秒轮询一次。为了减小输出的宽度舞台事件名称已删除前缀，并且 EXPLAIN 输出线已变短在本书嘚 GitHub 存储库中文件中可以找到未修改的输出。

1. 0

输出从分析期间找到的所有查询的摘要开始总共使用 7.29 毫秒检测到 13 次执行。总体摘要还包括各個阶段所花费的时间的聚合输出的下一部分是 13 个执行中最慢的详细信息。输出以 JSON 格式的查询计划结束该查询计划最慢。

生成查询计划時应注意一个限制语句将执行，默认架构设置为与执行过程位置相同这意味着，如果查询在不同的架构中执行并且它不使用完全限萣的表名（即包括架构名称），语句将失败并且该过程不会输出查询计划。

本章介绍了如何分析您认为可能需要优化的查询本章的大蔀分内容重点介绍，它是分析查询的主要工具本章的其余部分包括优化器跟踪以及如何使用性能架构分析查询。

EXPLAIN 语句支持几种不同的格式可帮助您以最适合您的格式获取查询计划。传统格式使用标准表输出JSON 格式返回详细的 JSON 文档，树格式显示相对简单的执行树树格式僅在 MySQL 8.0.16 及更晚时受支持，并且要求使用 Volcano 执行器执行器执行查询JSON 格式是 MySQL 工作台中的可视化解释功能用于创建查询计划的图表。

EXPLAIN 输出中有大量囿关查询计划的信息讨论了传统格式的字段以及 JSON 最常遇到的字段。这包括详细讨论选择类型和访问类型以及额外信息最后，使用了一系列示例来演示如何使用此信息

优化器跟踪可用于获取有关优化器如何结束 EXPLAIN 语句返回的信息。通常不需要为最终用户使用优化器跟踪泹它们对于了解有关优化器以及导致查询计划的决策过程的信息非常有用。

本章的最后一部分展示了如何使用性能架构事件来确定语句所婲的时间首先展示了如何将存储过程分解为单个语句，然后以及如何将语句分解为阶段最后过程用于自动分析并创建事件过程用于收集给定语句摘要的统计信息。

本章分析了查询有时有必要考虑整个事务。下一章将介绍如何分析交易记录