MySQL :: MySQL 8.3 Reference Manual :: 10.9.6 Optimizer Statistics

Documentation Home

version 8.3

8.0 current

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Optimizer Statistics

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：一个桶表示列中的一个单个值。该直方图类型是在生成直方图时列中的distinct 值数量小于或等于指定的桶数量时创建的。
- equi-height：一个桶表示列中的一个值范围。该直方图类型是在生成直方图时列中的distinct 值数量大于指定的桶数量时创建的。
number-of-buckets-specified：生成直方图时指定的桶数量。
数据类型: 这个直方图包含的数据类型。这是在从持久存储中读取和解析直方图到内存时所需的。该值是 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);

直方图统计信息主要用于非索引列。添加索引到适用直方图统计信息的列也可能有助于优化器进行行估算。权衡是：

索引必须在表数据修改时更新。
直方图创建或更新仅在需要时进行，因此在表数据修改时不增加开销。另一方面，统计信息在表修改时变得逐渐过时，直到下一次更新。

优化器优先使用范围优化器行估算，而不是来自直方图统计信息的估算。如果优化器确定范围优化器适用，它将不使用直方图统计信息。

对于索引列，行估算可以通过索引dives（见第 10.2.1.2 节，“范围优化”）获得。在这种情况下，直方图统计信息可能不是必需的，因为索引dives 可以提供更好的估算。

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

或者，要禁用直方图统计信息，可以使用 ANALYZE TABLE 删除它们。禁用直方图统计信息的另一种方法是关闭 condition_fanout_filter 标志的 optimizer_switch 系统变量（尽管这也可能禁用其他优化）：

SET optimizer_switch='condition_fanout_filter=off';

如果使用直方图统计信息，结果将在 EXPLAIN 中可见。考虑以下查询，其中没有可用的索引列 col1：

SELECT * FROM t1 WHERE col1 < 24;

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

PREV HOME UP NEXT