MySQL :: MySQL 8.4 Reference Manual :: 10.9.6 Optimizer Statistics

Documentation Home

version 8.4

8.0 current

8.0 Japanese

MySQL 8.4 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：表示列中SQL NULL值比例的数值，介于0.0和1.0之间。如果为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 时最有意义。值对应于信息_schema 中 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);

直方图统计信息对非索引列最有用。为具有直方图统计信息的列添加索引可能还会帮助优化器进行行估计。权衡如下：

索引必须在表数据被修改时更新。
直方图仅在需要时创建或更新，因此它不会在表数据被修改时产生额外开销。但是，随着表修改的进行，统计信息会变得越来越过时，直到下一次更新为止。

优化器更喜欢基于范围的行估计，而不是从直方图统计信息中获得的估计。如果优化器确定范围优化适用，它将不使用直方图统计信息。

对于具有索引的列，通过索引分割（参见 Section 10.2.1.2, “Range Optimization”）可以获得等值比较的行估计。在这种情况下，直方图统计信息可能不太有用，因为索引分割可以提供更好的估计。

在某些情况下，使用直方图统计信息可能不会改善查询执行（例如，如果统计信息过时）。要检查是否为此情况，请使用 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 显示 57.00 在 filtered 列中。

PREV HOME UP NEXT