直方图

在前几章中，您了解了索引和索引统计信息。索引的目的是减少访问查询所需的行和索引统计信息所需的读取，以帮助优化器确定最佳查询计划。这一切都很棒，但索引不是免费的，而且有些情况下索引不是很有效，而且不保证开销，但您仍然需要优化器来了解数据分布。这就是直方图可能有用的地方。

本章开始讨论什么是直方图，以及哪些工作负载直方图很有用。然后介绍使用直方图的更实际的一面，包括添加、维护和检查直方图数据。最后，还有一个查询示例，其中查询计划随着直方图的添加而更改。

什么是直方图？

对直方图的支持是 MySQL 8 中的新功能。它使分析和存储有关数据在表中分布的信息成为可能。虽然直方图与索引有些相似，但它们并不相同，并且您可以为没有任何索引的列具有直方图。

创建直方图时，告诉 MySQL 将数据划分为存储桶。这可以通过在每个存储桶中放入一个值或在每个存储桶中具有大致相等的行数的值来实现。有关数据分布的知识可以帮助优化器更准确地估计表中给定 WHERE 子句或联接条件将筛选出多少数据。如果没有这些知识，优化器可能会假定条件返回表的三分之一，而直方图可能显示只有 5% 的行与条件匹配。这些知识对于优化器选择最佳查询计划至关重要。

同时，必须认识到直方图与索引不一样。MySQL 不能使用直方图来减少使用直方图检查的表的行数，而对于没有直方图执行的同一查询计划相比。但是，通过了解将筛选多少表，优化器可以更好地确定最佳联接顺序。

直方图的一个优点是，它们仅在创建或更新时具有成本。与索引不同，更改数据时直方图没有更改。您可以不时重新创建直方图，以确保统计信息是最新的，但 DML 查询没有开销。通常，直方图应与索引统计信息进行比较，而不是与索引进行比较。

注意 了解索引和直方图之间的根本区别非常重要。索引可用于减少访问所需行所需的工作，直方图不能。当用于查询的直方图时，它不会直接减少检查的行数，但它可以帮助优化器选择更优化的查询计划。

就像索引一样，您应该小心地选择为哪个列添加直方图。因此，让我们讨论哪些列应被视为优秀的候选人。

何时应添加直方图？

添加直方图的好处是将它们添加到正确的列。简而言之，直方图最适用于不是索引中的第一列、具有非统一值分布以及将条件应用于这些列的列。这听起来像是一个非常有限的用例，事实上直方图在 MySQL 中并不像在其他一些数据库中那样有用。这是因为 MySQL 有效地估计索引列范围内的行数，因此直方图不会与同一列上的索引一起使用。另请注意，虽然直方图对于具有不均匀数据分布的列特别有用，但同样可用于统一数据分布，因为不值得添加索引。

提示 不要向索引中第一列的列添加直方图。对于稍后在索引中显示的列，直方图对于由于需要使用索引的左前缀而不能用于列的查询仍具有值值。

也就是说，在某些情况下，直方图可以大大提高查询性能。典型的用例是具有一个或多个联接的查询，以及具有数据未一匀分布的列上的一些次要条件。在这种情况下，直方图可以帮助优化器确定最佳联接顺序，以便尽早筛选出大部分行。

具有不一致数据分布的数据的一些示例包括状态值、类别、一天中的时间、工作日和价格。状态列可能具有终端状态的大量行，如”已完成”或”失败”，以及处于工作状态的一些值。同样，产品表在某些类别中的产品可能比其他类别中的产品多。一天中的时间和工作日值可能不统一，因为某些事件更有可能发生在某些时间或天数中。例如，平日发生的球类比赛（取决于运动）更有可能在周末发生，而不是平日。对于价格，您可能拥有价格相对较窄的多数产品，但最低和最高价格都超出了此范围。选择性低的列示例包括列，即列的数据类型、布尔值和其他只有几个唯一值的列。

与索引相比，直方图的一个好处是直方图比索引潜水便宜，以确定一个范围内的行数，例如，对于长 IN 子句或许多 OR 条件。这样做的原因是，直方图统计信息对于优化器是现成的，而索引潜水以估计一个范围内的行数，在确定查询计划时完成，从而对每个查询重复。

提示 对于索引列，优化器将从进行相对昂贵但非常准确的索引潜水切换到在有 eqrange_index_dive 限制（默认值为 200）或更多相等范围时，使用索引统计信息来估计匹配行数。

你可以争辩说，为什么当你可以添加索引时，麻烦直方图，但请记住，它不是没有成本，以维护索引，因为数据的变化。执行 DML 查询时需要维护它们，并且它们增加了表空间文件的大小。此外，在执行查询的优化阶段，对范围（包括相等范围）中值数的统计信息进行远程计算。也就是说，它们是根据需要计算的每个查询。另一方面，直方图只存储统计信息，并且仅在显式请求时更新。直方图统计信息也始终可用于优化器。

总之，直方图的最佳候选项是符合以下条件的列：

• 数据分布不均匀或值太多，以致优化器的粗略估算（在下一章中讨论）并不是对数据选择性的良好估算。
• 选择性差（否则索引可能是更好的选择）。
• 用于在WHERE子句或联接条件中过滤表中的数据。 如果不对列进行过滤，则优化器将无法使用直方图。
• 随时间推移稳定分布数据。 直方图统计信息不会自动更新，因此，如果在数据分布经常变化的列上添加直方图，则直方图统计信息可能不准确。 直方图选择不佳的一个主要示例是存储事件日期和时间的列。

这些规则的一个例外是，如果可以使用直方图统计信息来替换昂贵的查询。可以查询直方图统计信息，因为它将显示在”检查直方图数据”部分中，因此，如果您只需要数据分布的近似结果，则可以查询直方图统计信息。

提示 如果您有确定给定范围内的值数的查询，并且只需要近似值，则即使您不打算使用直方图来改进查询计划，也可以考虑创建直方图。

由于直方图存储列中的值，因此不允许向加密表添加直方图。否则，加密数据可能会无意中被写入磁盘。此外，不支持临时表上的直方图。

为了以最佳方式应用直方图，您需要了解直方图工作的内部信息，包括支持的直方图类型。

Histogram Internals

直方图周围有几个内部图，为了有效地使用它们，必须了解这些内部图。您应该了解的概念是存储桶、累积频率和直方图类型。本节将介绍每个概念。

Buckets

创建直方图时，值将分发到存储桶。每个存储桶可以包含一个或多个不同的值，对于每个存储桶，MySQL 计算累积频率。因此，存储桶的概念很重要，因为它与直方图统计的准确性密切相关。

MySQL 支持多达 1024 个存储桶。存储桶的量桶越多，每个存储桶中的值就更少，因此存储桶越多，对于每个值的统计信息越准确。在最好的情况下，每个存储桶只有一个值，因此您知道该值的行数”精确”（只要统计信息准确）。如果每个存储桶有多个值，则计算值范围的行数。

在此上下文中了解何谓独特价值非常重要。对于字符串，在比较值时只考虑前 42 个字符，对于二进制值，则考虑前 42 个字节。如果您有具有相同前缀的长字符串或二进制值，直方图可能不太适合您。

注意 仅使用字符串前 42 个字符和二进制对象的前 42 个字节来确定直方图存在的值。

值按顺序添加，因此，如果您从左到右订购存储桶并检查给定的存储桶，则您知道左侧的所有存储桶的值都较小，而右侧的所有存储桶的值都较大。图 16-1 显示了存储桶的概念。

在图中，前面的暗列是每个存储桶中值的频率。频率是具有该值的行的百分比。在后台（颜色更亮的列）是累积频率，其值与存储桶 0 的计数列相同，然后逐渐增加，直到存储桶 7 达到 100。什么是累积频率？这是你应该理解的直方图的第二个概念。

累积频率

存储桶的累积频率是当前存储桶和上一个存储桶中的行的百分比。如果您正在查看存储桶编号 3，并且累积频率为 50%，则 50% 的行适合存储桶 0、1、2 和 3。这使得优化器很容易确定具有直方图的列的选择性。

计算选择性时需要考虑两种情况：相等条件和范围条件。对于相等条件，优化器确定条件值位于哪个存储桶中，然后采用该存储桶的累积频率，并减去上一个存储桶的累积频率（对于存储桶 0，不减去任何值）。如果存储桶中只有一个值，则这就是所需的全部值。否则，优化器假定存储桶中的每个值以相同的频率发生，因此存储桶的频率与存储桶中的值数进行划分。

对于范围条件，它的工作方式非常相似。优化器查找边缘条件位于的铲斗。例如，对于 val = 4，位于值为 4 的存储桶。使用的累积频率取决于存储桶中的值数和条件类型。对于相等条件，对于多值存储桶，累积频率通过假设存储桶中值的相等分布来找到。根据条件类型，累积频率使用如下：

• 小于：使用前一个值的累积频率。
• 小于或等于：使用条件中值的累积频率。
• 大于或等于：从1减去前一个值的累积频率。
• 大于：从1中减去条件中值的累积频率。

这意味着，通过使用累积频率，最多需要考虑两个存储桶，以确定条件对表中的行进行筛选的身体状况。查看示例以更好地了解累积频率的工作方式可能很有用。表 16-1 显示了一个直方图示例，每个存储桶有一个值，每个存储桶的累积频率。

在此示例中，这些值与存储桶编号相同，但通常情况并非如此。累积频率从 0.1 （10） 开始并增加每个存储桶中的行百分比，直到最后一个存储桶达到 100%。此分布与图 16-1 中所示的分布相同。

如果查看与值 4 相比的五种条件类型，则每种类型估计的行数如下：

• val = 4：从存储区4的累积频率中减去存储区3的累积频率：估计= 0.63 – 0.55 = 0.08。 因此，估计将包含8％的行。
• val <4：使用存储区3的累积频率，因此估计将包含55％的行。
• val <= 4：使用存储区4的累积频率，因此估计将包含63％的行。
• val> = 4：从1中减去存储区3的累积频率，因此估计将包含45％的行。
• val> 4：从1中减去存储区4的累积频率，因此估计将包含37％的行。

当每个存储桶中包含多个值时，它变得有点复杂。表 16-2 显示了相同的表和值分布，但这次直方图只有四个存储桶，因此每个存储桶平均有两个值。

在这种情况下，每个存储桶中正好有两个值，但通常情况并非如此（在讨论直方图类型时将对此进行更多讨论）。现在，评估相同的五个条件时，需要考虑到每个存储桶都包含多个值的行数的估计值：

• val = 4：从存储区2的累积频率中减去存储区1的累积频率；然后将结果除以存储区2中值的数量：估计=（0.83 – 0.55）/ 2 = 0.14。因此，估计将包含14％的行。这比每个桶中有一个值的更准确的估计要高，因为值4和5的频率是一起考虑的。
• val <4：存储桶1的累积频率是唯一需要的频率，因为存储桶0和1的所有值都小于4。因此，估计将包含55％的行（这与对于前一个示例，因为在两种情况下，估算都只需要考虑完整的存储桶）。
• val <= 4：这更加复杂，因为存储区2中一半的值包含在过滤中，而另一半则不包含。因此，估算值将是存储桶1的累积频率加上存储桶2的频率除以存储桶中值的数量：估算= 0.55 +（0.83 – 0.55）/ 2 = 0.69或69％。这比每个存储桶使用一个值的估计值更高且更不准确。该估计的准确性较差的原因是，假定值4和5具有相同的频率。
• val> = 4：此条件要求存储区2和3中的所有值，因此估算值应包括1减去存储区1的累积频率；这是45％–与每个存储桶一个值的情况下的估算值相同。
• val> 4：这种情况类似于val <= 4，只是要包含的值相反，因此您可以取0.69并从1中减去，得出0.31或31％。同样，由于涉及两个存储桶，因此估算的准确性不如每个存储桶的单个值准确。

如您所见，在将值分发到存储桶中时有两种不同的方案：要么存储桶至少与值一样多，也可以为每个值分配自己的存储桶，或者多个值必须共享一个存储桶。这是两种不同类型的直方图，接下来将讨论这些直方图的具体细节。

Histogram Types

MySQL 8 中有两种类型的直方图。创建或更新直方图时，根据值是否大于存储桶自动选择直方图类型。两种直方图类型是

• 单位：对于单位直方图，每个存储桶只有一个值。这些是最精确的直方图，因为创建直方图时存在的每个值都有估计值。
• 等高度：当列的值大于存储桶时，MySQL 将分发这些值，因此每个存储桶的行数大致相同，也就是说，每个存储桶的高度大致相同。由于具有相同值的所有行都分布到同一个存储桶，因此存储桶的高度不会完全相同。对于等高直方图，每个存储桶都表示的值数不同。

在探索累积频率时，您已经遇到两种直方图类型。单位直方图是最简单、最准确的，但等高直方图是最灵活的，因为它们可以处理任何数据集。

为了演示单层和等高直方图，可以从 world.city 表创建 city_ 直方图表，其中包含基于八个国家/地区代码的城市子集。可以使用以下查询创建表：

use world
CREATE TABLE city_histogram LIKE city;
INSERT INTO city_histogram
SELECT *
 FROM city
 WHERE CountryCode IN
 ('AUS', 'BRA', 'CHN', 'DEU',
 'FRA', 'GBR', 'IND', 'USA');

图 16-2 显示了”国家代码”列上的单例直方图示例。由于有八个值，因此直方图有八个存储桶。（您将在章节的稍后部分学习如何创建和检索直方图统计信息。

直方图每个存储桶只有一个值。澳大利亚（澳大利亚）的频率范围从1.0%到24.9%到中国（CHN）。例如，如果”国家代码”列上没有索引，直方图可以极大地帮助提供更准确的筛选估计值。原始 world. city 表有 232 个不同的国家代码值，因此单元直方图效果良好。

图 16-3 显示了相同数据的等高直方图，但统计时只有四个存储桶。

对于等高度直方图，MySQL 的目标是具有每个存储桶的相同频率（高度）。但是，由于列值将完全位于一个存储桶中，并且值按顺序分布，因此通常不可能获得完全相同的高度。本示例中的情况也是，存储桶 0 和 3 的频率比存储桶 1 和 2 稍小。

该图还显示了等高直方图的缺点。巴西 （BRA）、中国 （CHN） 和印度 （IND） 城市的高频率被它们共享水桶的国家/地区的低频率所掩盖。因此，等高直方图的精度没有单位直方图高。当值的频率变化很大时，尤其如此。精度降低通常比范围条件更公平，因此等高直方图最适合主要用于范围条件的列。

在使用直方图统计信息之前，您需要创建它们，一旦创建，就需要维护统计信息。如何做到这一点是下一节的主题。

Adding and Maintaining Histograms

直方图仅作为统计信息存在，与在表空间中具有物理状态的索引不同。因此，使用也用于更新索引统计信息的 ANALYZE TABLE 语句创建、更新和删除直方图并不令人感到意外。语句有两个变体：更新和删除统计信息。创建和更新直方图时，还需要了解采样率。本节将介绍每个主题。

创建和更新直方图

通过将 UPDATE HISTOGRAM 子句添加到 ANALYZE TABLE语句或更新图。如果没有统计信息并发出更新请求，则创建直方图;如果存在统计信息并请求更新，则创建直方图。否则，将替换现有的直方图。您需要指定要将统计信息划分到的存储桶数。

若要使用最多 256个存储桶（长度以分钟表示，因此 256 个存储桶应足以确保单例直方图）将直方图添加到表的长度列中，可以使用如下示例所示的语句：

mysql> ANALYZE TABLE sakila.film
 UPDATE HISTOGRAM ON length
 WITH 256 BUCKETS\G
**************************** 1. row *****************************
 Table: sakila.film
 Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'length'.
1 row in set (0.0057 sec)

或者，您可以在 ANALYZE 和关键字将语句写入二进制日志。 这与更新索引统计信息时的工作方式相同。

当完成无错误的直方图创建将等于状态显示已创建直方图统计信息以及哪个列。如果发生错误，等于解释问题。 例如，如果尝试为不存在的列创建直方图，则错误将类似于此示例：

mysql> ANALYZE TABLE sakila.film
 UPDATE HISTOGRAM ON len
 WITH 256 BUCKETS\G
**************************** 1. row ***************************
 Table: sakila.film
 Op: histogram
Msg_type: Error
Msg_text: The column 'len' does not exist.
1 row in set (0.0004 sec)

还可以使用相同的语句更新同一表中多个列的直方图。例如，如果要更新表的长度评级列上的直方图，可以使用像清单。

Listing 16-1. Updating histograms for multiple columns
mysql> ANALYZE TABLE sakila.film
 UPDATE HISTOGRAM ON length, rating
 WITH 256 BUCKETS\G
*************************** 1. row ***************************
 Table: sakila.film
 Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'length'.
**************************** 2. row ***************************
 Table: sakila.film
 Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'rating'.
2 rows in set (0.0119 sec)

您应该选择多少桶？如果唯一值少于 1024 个，建议有足够的存储桶来创建单位直方图（即，至少与唯一值一样多的存储桶）。如果选择比值更多的存储桶，MySQL 将只使用存储每个值的频率所需的存储桶。从这个意义上说，存储桶的数量应视为要使用的最大存储桶数。

如果超过 1024 个不同的值，则需要足够的存储桶来获取数据的不同表示形式。25 到 100 个铲斗通常是一个很好的起点。对于 100 个存储桶，等高度直方图将平均具有每个存储桶中 1% 的行。行的分布越统一，需要的存储桶就更少，分布差异越大，需要的存储桶就越大。目标是在自己的存储桶中具有最常发生的值。例如，对于上一节中使用的表的子集，五个存储桶将中国 （CHN）、印度 （IND） 和美国放在自己的存储桶中。

直方图通过对值进行采样创建。如何完成取决于可用的内存量。

采样

当 MySQL 创建时，它需要读取行来确定可能的值及其频率。这是以类似但不同的方式对索引统计信息进行采样。计算索引统计信息时，将确定唯一值的数量，这是一个简单的任务，因为它只需要计数。因此，您需要指定的只是要采样的页面数。

对于直方图，MySQL 不仅必须确定不同值的数量，还要确定它们的频率以及如何将值分发到存储桶中。因此，采样值被读取到内存中，然后用于创建存储桶并计算直方图统计信息。这意味着更自然地指定可用于采样的内存量，而不是页数。根据可用内存量，MySQL 将确定可以采样多少页。

分析表期间语句使用选项指定。默认值为 20，000，000 字节。检查直方图数据”部分中讨论的视图包括有关结果采样率的信息。如果没有得到筛选的预期准确性，可以检查采样率，如果采样率低，可以增加histogram_generation_max_mem_size 采样的页数随可用内存量线性缩放，而存储桶数对采样速率没有任何影响。

删除直方图

如果您确定不再需要直方图，可以再次删除它。与更新直方图统计信息一样， 统计信息。您可以在一个语句中删除一个或多个直方图。清单中列出了和评级列上删除直方图的示例。 本章中后的示例部分包括一个查询，可用于查找所有现有直方图。

Listing 16-2. Dropping histograms
mysql> ANALYZE TABLE sakila.film
 DROP HISTOGRAM ON length, rating\G
*************************** 1. row ***************************
 Table: sakila.film
 Op: histogram
Msg_type: status
Msg_text: Histogram statistics removed for column 'length'.
*************************** 2. row ***************************
 Table: sakila.film
 Op: histogram
Msg_type: status
Msg_text: Histogram statistics removed for column 'rating'.
2 rows in set (0.0120 sec)

ANALYZE TABLE 语句类似于创建统计信息。您还可以在 ANALYZE 和，以避免将语句写入二进制日志。

一旦您拥有直方图，如何检查统计数据及其元数据？您可以使用信息架构，如下文讨论。

检查直方图数据

当查询计划不是您所期望的时，了解优化器可用的信息非常重要。就像索引统计信息具有各种视图一样，信息架构还包含一个视图，因此您可以查看直方图统计信息。数据可通过数据视图。下一节包括使用此视图检索有关直方图的信息的示例。

COLUMN_STATISTICS是包含直方图信息的数据字典部分的视图。表总结了四列。

前三列（SCHEMA_NAME、TABLE_NAME、COLUMN_NAME）构成主键，并允许您查询您感兴趣的直方图。列是最有趣的，因为它存储直方图的元数据以及直方图统计信息。

直方图信息作为，其中包含多个对象，其中包括创建统计信息时、采样率和统计信息本身等信息。表显示了文档中包含的字段。当您在查询视图时，字段按字母顺序列出，可能与包含

该视图不仅可用于确定直方图统计信息，还可以使用它来检查元数据，例如，确定统计信息上次更新以来的一段时间，并用它来确保定期更新统计信息。

字段应得到更多的关注，因为它存储了统计信息。它是一个数组，每个存储桶有一个元素。每个存储桶元素本身就是 JSON 数组。对于单位直方图，每个存储桶有两个元素，而对于等高度直方图有四个元素。

单位直方图包含的元素是

• 存储桶的列值。
• 累积频率。

等高度，但总共有四个元素可以说明每个存储桶可能包含多个列值的信息。元素是

• 存储桶中包含的列值的下限。
• 存储桶中包含的列值的上限。
• 累积频率。
• 存储桶中包含的值数。

如果返回并考虑计算各种条件的预期筛选效果的示例，可以看到存储桶信息包括所有必要的内容，但也不包含任何额外的信息。

由于直方图数据存储为 JSON 文档，因此值得查看检索各种信息的一些示例查询。

直方图报告示例

此视图对于查询直方图数据非常有用。由于元数据和统计信息存储在 JSON 文档中，因此考虑一些可用的 JSON 操作函数非常有用，因此可以检索直方图报表。本节将介绍为系统中的直方图生成报表的几个示例。所有示例也可从本书的 GitHub 存储库中获取，例如，清单中的查询可在文件。

列出所有直方图

基本报告是列出中的所有直方图。要包括的一些相关信息是直方图的架构信息、直方图类型、直方图上次更新的时间、采样速率、存储桶数量等。清单显示了一个直方图的查询和输出（您可能会看到不同的直方图列表，具体取决于您创建的直方图）。

Listing 16-3. Listing all histograms
mysql> SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
 HISTOGRAM->>'$."histogram-type"' AS Histogram_Type,
 CAST(HISTOGRAM->>'$."last-updated"'
 AS DATETIME(6)) AS Last_Updated,
 CAST(HISTOGRAM->>'$."sampling-rate"'
 AS DECIMAL(4,2)) AS Sampling_Rate,
 JSON_LENGTH(HISTOGRAM->'$.buckets')
 AS Number_of_Buckets,
 CAST(HISTOGRAM->'$."number-of-buckets-specified"'AS UNSIGNED)
AS Number_of_Buckets_Specified
 FROM information_schema.COLUMN_STATISTICS\G
**************************** 1. row ****************************
 SCHEMA_NAME: sakila
 TABLE_NAME: film
 COLUMN_NAME: length
 Histogram_Type: singleton
 Last_Updated: 2019-06-02 08:49:18.261357
 Sampling_Rate: 1.00
 Number_of_Buckets: 140
Number_of_Buckets_Specified: 256
1 row in set (0.0006 sec)

该查询提供直方图的高级别视图。运算符从 JSON 文档中提取一个运算符另外取消引用提取的值，这些值在提取字符串时非常有用。例如，从示例输出中，您可以看到表中长度列直方图具有 140 个存储桶，但请求了 256 个存储桶。您还可以看到它是一个单位直这并不奇怪，因为并非所有请求的存储桶都使用。

列出单个直方图的所有信息

查看直方图的整个输出非常有用。例如，请考虑本创建并填充了八个国家/地区的数据的表。您可以在”国家代码”列上创建具有四个存储桶的等，如

ANALYZE TABLE world.city_histogram
 UPDATE HISTOGRAM ON CountryCode
 WITH 4 BUCKETS;

清单查询此直方图的数据。这与图讨论等直方图时使用的直方图相同。

Listing 16-4. Retrieving all data for a histogram
mysql> SELECT JSON_PRETTY(HISTOGRAM) AS Histogram
 FROM information_schema.COLUMN_STATISTICS
 WHERE SCHEMA_NAME = 'world'
 AND TABLE_NAME = 'city_histogram'
 AND COLUMN_NAME = 'CountryCode'\G
**************************** 1. row ****************************
Histogram: {
 "buckets": [
 [
 "base64:type254:QVVT",
 "base64:type254:QlJB",
 0.1813186813186813,
 2
 ],
 [
 "base64:type254:Q0hO",
 "base64:type254:REVV",
 0.4945054945054945,
 2
 ],
 [
 "base64:type254:RlJB",
 "base64:type254:SU5E",
 0.8118131868131868,
 3
 ],
 [
 "base64:type254:VVNB",
 "base64:type254:VVNB",
 1.0,
 1
 ]
 ],
 "data-type": "string",
 "null-values": 0.0,
 "collation-id": 8,
 "last-updated": "2019-06-03 10:35:42.102590",
 "sampling-rate": 1.0,
 "histogram-type": "equi-height",
 "number-of-buckets-specified": 4
}
1 row in set (0.0006 sec)

此查询有几个有趣的事情。函数用于便于读取直方图信息。如果没有函数，整个文档将作为一行返回。

另请注意，每个字符串的下上限作为 base64 编码字符串返回。这是为了确保字符串和二进制列中的任何值都可以由直方图处理。其他数据类型直接存储其值。

列出单位直方图的存储桶信息

在上一个示例中，查询直方图的原始数据。通过使用可以更好地处理存储桶信息。该示例中使用的是个国家/地区（用于避免产出过多）的城市表的副本。”国家代码”列上存在单位图：

ANALYZE TABLE world.city_histogram
 UPDATE HISTOGRAM ON CountryCode
 WITH 8 BUCKETS;

这与图中讨论单例直方图时用于示例的直方图相同。清单显示了对单例直方图进行此项工作的示例。

Listing 16-5. Listing the bucket information for a singleton histogram
mysql> SELECT (Row_ID - 1) AS Bucket_Number,
 SUBSTRING_INDEX(Bucket_Value, ':', -1) AS
 Bucket_Value,
 ROUND(Cumulative_Frequency * 100, 2) AS
 Cumulative_Frequency,
 ROUND((Cumulative_Frequency - LAG(Cumulative_Frequency, 1, 0)
OVER()) * 100, 2) AS Frequency
 FROM information_schema.COLUMN_STATISTICS
 INNER JOIN JSON_TABLE(
 histogram->'$.buckets',
 '$[*]' COLUMNS(
 Row_ID FOR ORDINALITY,
 Bucket_Value varchar(42) PATH '$[0]',
 Cumulative_Frequency double PATH '$[1]'
 )
 ) buckets
 WHERE SCHEMA_NAME = 'world'
 AND TABLE_NAME = 'city_histogram'
 AND COLUMN_NAME = 'CountryCode'
 ORDER BY Row_ID\G
**************************** 1. row *****************************
 Bucket_Number: 0
 Bucket_Value: AUS
Cumulative_Frequency: 0.96
 Frequency: 0.96
**************************** 2. row ****************************
 Bucket_Number: 1
 Bucket_Value: BRA
Cumulative_Frequency: 18.13
 Frequency: 17.17
**************************** 3. row *****************************
 Bucket_Number: 2
 Bucket_Value: CHN
Cumulative_Frequency: 43.06
 Frequency: 24.93
**************************** 4. row *****************************
 Bucket_Number: 3
 Bucket_Value: DEU
Cumulative_Frequency: 49.45
 Frequency: 6.39
**************************** 5. row *****************************
 Bucket_Number: 4
 Bucket_Value: FRA
Cumulative_Frequency: 52.2
 Frequency: 2.75
**************************** 6. row *****************************
 Bucket_Number: 5
 Bucket_Value: GBR
Cumulative_Frequency: 57.76
 Frequency: 5.56
**************************** 7. row *****************************
 Bucket_Number: 6
 Bucket_Value: IND
Cumulative_Frequency: 81.18
 Frequency: 23.42
**************************** 8. row *****************************
 Bucket_Number: 7
 Bucket_Value: USA
Cumulative_Frequency: 100
 Frequency: 18.82
8 rows in set (0.0008 sec)

查询将 JSON 上的视图，将 JSON 文档转换为 SQL 表。函数采用两个参数，其中第一个参数是 JSON 文档，第二个参数是值的路径，以及生成的表的列定义。列定义包括为每个存储桶创建的三列：

• 此列具有子句，它使它成为一个基于 1 的自动增量计数器，因此可以通过减去 1 来用于存储桶编号。
• 与存储桶一起使用的列值。请注意，该值在其 base64 编码解码后返回，因此相同的查询适用于字符串和数值。
• 存储桶的累积频率为 0.0 和 1.0 之间的小数。

函数的结果可以与派生表相同的方式使用。累积频率位于将查询百分比的 SELECT 部分窗口函数用于计算每个存储桶的频率（也作为百分比）。

列出等高度直方图的存储桶信息

检索等高度直方图的存储桶信息的查询与刚才为单位直方图讨论的查询非常相似。唯一的区别是等高度直方图有两个值（间隔的开始和结束）定义存储桶和存储桶中的值数。

例如，您可以在”国家代码”列上创建一个直个存储桶：

ANALYZE TABLE world.city_histogram
 UPDATE HISTOGRAM ON CountryCode
 WITH 4 BUCKETS;

清单显示了在具有四个存储桶的代码存储桶信息的示例。

Listing 16-6. Listing the bucket information for an equi-height histogram
mysql> SELECT (Row_ID - 1) AS Bucket_Number,
 SUBSTRING_INDEX(Bucket_Value1, ':', -1) AS
 Bucket_Lower_Value,
 SUBSTRING_INDEX(Bucket_Value2, ':', -1) AS
 Bucket_Upper_Value,
 ROUND(Cumulative_Frequency * 100, 2) AS
 Cumulative_Frequency,
 ROUND((Cumulative_Frequency - LAG(Cumulative_Frequency, 1, 0)
OVER()) * 100, 2) AS Frequency,
 Number_of_Values
 FROM information_schema.COLUMN_STATISTICS
 INNER JOIN JSON_TABLE(
 histogram->'$.buckets',
 '$[*]' COLUMNS(
 Row_ID FOR ORDINALITY,
 Bucket_Value1 varchar(42) PATH '$[0]',
 Bucket_Value2 varchar(42) PATH '$[1]',
 Cumulative_Frequency double PATH '$[2]',
 Number_of_Values int unsigned PATH '$[3]'
 )
 ) buckets
 WHERE SCHEMA_NAME = 'world'
 AND TABLE_NAME = 'city_histogram'
 AND COLUMN_NAME = 'CountryCode'
 ORDER BY Row_ID\G
**************************** 1. row *****************************
 Bucket_Number: 0
 Bucket_Lower_Value: AUS
 Bucket_Upper_Value: BRA
Cumulative_Frequency: 18.13
 Frequency: 18.13
 Number_of_Values: 2
**************************** 2. row *****************************
 Bucket_Number: 1
 Bucket_Lower_Value: CHN
 Bucket_Upper_Value: DEU
Cumulative_Frequency: 49.45
 Frequency: 31.32
 Number_of_Values: 2
**************************** 3. row *****************************
 Bucket_Number: 2
 Bucket_Lower_Value: FRA
 Bucket_Upper_Value: IND
Cumulative_Frequency: 81.18
 Frequency: 31.73
 Number_of_Values: 3
**************************** 4. row *****************************
 Bucket_Number: 3
 Bucket_Lower_Value: USA
 Bucket_Upper_Value: USA
Cumulative_Frequency: 100
 Frequency: 18.82
 Number_of_Values: 1
4 rows in set (0.0011 sec)

现在，您有一些工具来检查直方图数据，剩下的就是展示直方图如何更改查询计划的示例。

查询示例

直方图的主要目标是帮助优化器实现执行查询的最佳方法。查看直方图如何影响优化器以更改查询计划的示例非常有用，因此，为了结束本章，将讨论在子句中的列中添加直方图时更改计划的查询。

该查询使用 和查询短于 55 分钟的电影，并具有名称为 Elvis 的演员。这似乎是一个精心策划的示例，但类似的查询很常见，例如，为满足某些条件的客户查找订单。此示例查询可以按如下内容编写：

SELECT film_id, title, length,
 GROUP_CONCAT(
 CONCAT_WS(' ', first_name, last_name)
 ) AS Actors
 FROM sakila.film
 INNER JOIN sakila.film_actor USING (film_id)
 INNER JOIN sakila.actor USING (actor_id)
 WHERE length < 55 AND first_name = 'Elvis'
 GROUP BY film_id;

标题和列来自和 如果GROUP_CONCAT猫王，使用”第三个函数。（此查询的替代方法是使用但这样，查询结果中包含了名称为 Elvis 的演员的全名。

在列的长度和first_name没有，因此优化器无法知道这些列的条件筛选得有多好。 默认情况下，它假定长度上的条件表中大约三分之一的行，并且 first_name 上10% 的行。（下一章包括这些默认筛选器值的来自。

图显示了不存在直方图时查询计划。查询计划显示为可视化解释第。

在查询计划中需要注意的重要一点是器已选择从执行组件表上的完整表，最后加入表。 总查询成本（在图的右上角）计算为 467.20（图中的查询成本编号可能与您获得的成本数字不同，因为它们取决于索引和直方图 （ 统计信息）。

如前所述，默认情况下，优化器估计大约三分之一的电影长度小于 55 分钟。只是考虑到长度的可能值，它表明这是一个糟糕的估计（但优化器对电影一无所知，所以它看不到）。事实上，只有6.6%的电影有这个范围的长度。这使得长度成为直方图的候选列，您可以添加该柱线，就像前面显示的一样：

ANALYZE TABLE sakila.film
 UPDATE HISTOGRAM ON length
 WITH 256 BUCKETS;

现在，查询计划将更改，如图。

直方图意味着现在优化器确切地知道如果首先扫描胶片表返回多少行。这可将查询的总成本降低至 282.26，这是一个很好的改进。（同样，根据索引统计信息，您可能会看到不同的更改。示例中的重要一点是直方图更改查询计划和估计成本。

此示例还有趣的是，如果更改条件以查找时间小于 60 分钟的影片，更改回第一次扫描表。原因是，有了这种情况，将包含足够的电影的基础上的长度，这是更好地开始寻找候选演员。同样，如果在上添加直方图，则优化器将实现名字对于此数据库中的执行组件来说是一个相当不错的筛选器;特别是，只有一个演员叫猫王。读者可以尝试更改 WHERE 子句，并查看查询计划如何更改。

总结

本章展示了在优化器尝试确定最佳查询计划时，如何使用直方图来改进优化器可用的信息。直方图将列值划分为存储桶，每个存储桶一个值称为单位直方图，或者每个存储桶称为等高度直方图的多个值。对于每个存储桶，确定遇到值的频率，并计算每个存储桶的累积频率。

直方图主要适用于不具有索引的候选列，但它们仍用于筛选具有联接的查询。在这种情况下，直方图可以帮助优化器确定最佳联接顺序。在章节末尾提供了一个示例，演示直方图如何更改查询的联接顺序。

可以在数据中检查直方图的元数据和视图。该信息包括优化器使用的每个存储桶的所有数据以及元数据，例如上次更新直方图的时间、直方图类型以及请求的存储桶数。

在查询示例中，有人提到优化器对于各种条件的估计筛选效果有一些默认值。到目前为止，在索引和直方图的讨论中，优化器大多被忽视。是时候改变这一点了：下一章是关于查询优化器的。