MySQL :: MySQL 8.4 参考手册 :: 10.9.6 优化器统计信息

版本 8.4

9.0
8.0

8.0 日语

MySQL 8.4 参考手册 / ... / 优化器统计信息

10.9.6 优化器统计信息

column_statistics 数据字典表存储关于列值的直方图统计信息，供优化器在构建查询执行计划时使用。要执行直方图管理，请使用 ANALYZE TABLE 语句。

column_statistics 表具有以下特征：

该表包含除几何类型（空间数据）和 JSON 之外的所有数据类型的列的统计信息。
该表是持久的，因此不需要在每次服务器启动时都创建列统计信息。
服务器执行对表的更新；用户不执行。

用户无法直接访问 column_statistics 表，因为它是数据字典的一部分。可以使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 获取直方图信息，该视图是作为数据字典表上的视图实现的。 COLUMN_STATISTICS 具有以下列：

SCHEMA_NAME、TABLE_NAME、COLUMN_NAME：应用统计信息的模式、表和列的名称。
HISTOGRAM：描述列统计信息的 JSON 值，存储为直方图。

列直方图包含列中存储的值范围部分的桶。直方图是 JSON 对象，以便在表示列统计信息时具有灵活性。下面是一个示例直方图对象：

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

直方图对象具有以下键：

buckets：直方图桶。桶结构取决于直方图类型。
对于 singleton 直方图，桶包含两个值：
- 值 1：桶的值。类型取决于列数据类型。
- 值 2：表示该值的累积频率的双精度值。例如，.25 和 .75 表示列中 25% 和 75% 的值小于或等于桶值。
对于 equi-height 直方图，桶包含四个值：
- 值 1、2：桶的下限和上限（含）。类型取决于列数据类型。
- 值 3：表示该值的累积频率的双精度值。例如，.25 和 .75 表示列中 25% 和 75% 的值小于或等于桶的上限值。
- 值 4：从桶下限值到其上限值范围内的不同值的数量。
null-values：介于 0.0 和 1.0 之间的数字，表示为 SQL NULL 值的列值的分数。如果为 0，则该列不包含 NULL 值。
last-updated：生成直方图的时间，以 UTC 值表示，格式为 YYYY-MM-DD hh:mm:ss.uuuuuu。
sampling-rate：介于 0.0 和 1.0 之间的数字，表示用于创建直方图的数据样本的分数。值为 1 表示读取了所有数据（无采样）。
histogram-type：直方图类型：
- singleton：一个桶表示列中的一个值。当列中不同值的数目小于或等于生成直方图的 ANALYZE TABLE 语句中指定的桶数时，将创建此直方图类型。
- equi-height：一个桶表示一个值范围。当列中不同值的数目大于生成直方图的 ANALYZE TABLE 语句中指定的桶数时，将创建此直方图类型。
number-of-buckets-specified：生成直方图的 ANALYZE TABLE 语句中指定的桶数。
data-type：此直方图包含的数据类型。从持久存储中读取直方图并将其解析到内存中时，需要使用此类型。该值可以是 int、uint（无符号整数）、double、decimal、datetime 或 string（包括字符和二进制字符串）。
collation-id：直方图数据的排序规则 ID。当 data-type 值为 string 时，此属性非常重要。这些值对应于信息架构 COLLATIONS 表中的 ID 列值。

要从直方图对象中提取特定值，可以使用 JSON 操作。例如

mysql> SELECT
         TABLE_NAME, COLUMN_NAME,
         HISTOGRAM->>'$."data-type"' AS 'data-type',
         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+-----------------+-------------+-----------+--------------+
| TABLE_NAME      | COLUMN_NAME | data-type | bucket-count |
+-----------------+-------------+-----------+--------------+
| country         | Population  | int       |          226 |
| city            | Population  | int       |         1024 |
| countrylanguage | Language    | string    |          457 |
+-----------------+-------------+-----------+--------------+

如果适用，优化器会对收集了统计信息的任何数据类型的列使用直方图统计信息。优化器应用直方图统计信息，根据列值与常量值比较的选择性（过滤效果）来确定行估计值。以下形式的谓词符合直方图使用条件

col_name = constant
col_name <> constant
col_name != constant
col_name > constant
col_name < constant
col_name >= constant
col_name <= constant
col_name IS NULL
col_name IS NOT NULL
col_name BETWEEN constant AND constant
col_name NOT BETWEEN constant AND constant
col_name IN (constant[, constant] ...)
col_name NOT IN (constant[, constant] ...)

例如，以下语句包含符合直方图使用条件的谓词

SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;
SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

与常量值进行比较的要求包括常量函数，例如 ABS() 和 FLOOR()

SELECT * FROM tbl WHERE col1 < ABS(-34);

直方图统计信息主要对未编制索引的列有用。为应用了直方图统计信息的列添加索引也可能有助于优化器进行行估计。折衷方案如下

修改表数据时，必须更新索引。
直方图仅在需要时才创建或更新，因此在修改表数据时不会增加开销。另一方面，当表发生修改时，统计信息会变得越来越过时，直到下次更新为止。

优化器更倾向于使用范围优化器行估计值，而不是从直方图统计信息中获取的行估计值。如果优化器确定范围优化器适用，则不会使用直方图统计信息。

对于已编制索引的列，可以使用索引查找来获取相等比较的行估计值（请参阅第 10.2.1.2 节“范围优化”）。在这种情况下，直方图统计信息不一定有用，因为索引查找可以产生更好的估计值。

在某些情况下，使用直方图统计信息可能无法改善查询执行（例如，如果统计信息已过时）。要检查是否属于这种情况，请使用 ANALYZE TABLE 重新生成直方图统计信息，然后再次运行查询。

或者，要禁用直方图统计信息，请使用 ANALYZE TABLE 将其删除。另一种禁用直方图统计信息的方法是关闭 optimizer_switch 系统变量的 condition_fanout_filter 标志（但这可能还会禁用其他优化）

SET optimizer_switch='condition_fanout_filter=off';

如果使用了直方图统计信息，则可以使用 EXPLAIN 查看产生的效果。请考虑以下查询，其中 col1 列没有可用索引

SELECT * FROM t1 WHERE col1 < 24;

如果直方图统计信息表明 t1 中 57% 的行满足 col1 < 24 谓词，则即使没有索引，也可以进行过滤，并且 EXPLAIN 会在 filtered 列中显示 57.00。