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

版本 9.0

8.4 当前
8.0

8.0 日语

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

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。