mysql8 翻译系列 五十

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：一个双精度数，表示该值的累积频率。例如，0.25和0.75表示列中25%和75%的值小于或等于桶的值。
  • 对于equi-height直方图，桶包含四个值：
    • 值1、值2：桶的下限和上限（包含）值。类型取决于列的数据类型。
    • 值3：一个双精度数，表示该值的累积频率。例如，0.25和0.75表示列中25%和75%的值小于或等于桶的上限值。
    • 值4：从桶的下限值到上限值范围内的不同值的数量。
• null-values：一个介于0.0到1.0之间的数字，表示列值中SQL NULL值的比例。如果为0，则表示该列不包含NULL值。
• last-updated：直方图生成的时间，采用YYYY-MM-DD hh:mm:ss.uuuuuu格式的UTC值。
• 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。