column_statistics 数据字典表存储关于列值的直方图统计信息，供优化器在构建查询执行计划时使用。要执行直方图管理，请使用 ANALYZE TABLE 语句。The column_statistics data dictionary table stores histogram statistics about column values, for use by the optimizer in constructing query execution plans. To perform histogram management, use the ANALYZE TABLE statement.

column_statistics 表具有以下特性：The column_statistics table has these characteristics:

column_statistics 表用户无法直接访问，因为它是数据字典的一部分。可以使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看直方图信息，该视图是基于数据字典表实现的。COLUMN_STATISTICS 具有以下列：The column_statistics table is not directly accessible by users because it is part of the data dictionary. Histogram information is available using INFORMATION_SCHEMA.COLUMN_STATISTICS, which is implemented as a view on the data dictionary table. COLUMN_STATISTICS has these columns:

列直方图包含列中存储值范围的各个部分的桶。直方图是 JSON 对象，以允许在列统计信息表示上具有灵活性。以下是一个示例直方图对象：Column histograms contain buckets for parts of the range of values stored in the column. Histograms are JSON objects to permit flexibility in the representation of column statistics. Here is a sample histogram object:

{
  "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
}

直方图对象具有以下键：Histogram objects have these keys:

要从直方图对象中提取特定值，可以使用 JSON 操作。例如：To extract particular values from the histogram objects, you can use JSON operations. For example:

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 |
+-----------------+-------------+-----------+--------------+

优化器使用直方图统计（如果适用），适用于收集了统计信息的任何数据类型的列。优化器应用直方图统计来根据列值与常量值比较的选择性（过滤效果）确定行估算。以下形式的谓词适用于直方图使用：The optimizer uses histogram statistics, if applicable, for columns of any data type for which statistics are collected. The optimizer applies histogram statistics to determine row estimates based on the selectivity (filtering effect) of column value comparisons against constant values. Predicates of these forms qualify for histogram use:

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] ...)

例如，这些语句包含适用于直方图使用的谓词：For example, these statements contain predicates that qualify for histogram use:

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

与常数值进行比较的要求包括常数函数，例如 ABS() 和 FLOOR()：The requirement for comparison against a constant value includes functions that are constant, such as ABS() and FLOOR():

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

直方图统计数据主要对非索引列有用。为可使用直方图统计数据的列添加索引也可能帮助优化器进行行数估计。权衡如下：Histogram statistics are useful primarily for nonindexed columns. Adding an index to a column for which histogram statistics are applicable might also help the optimizer make row estimates. The tradeoffs are:

优化器更倾向于使用范围优化器的行数估计，而不是从直方图统计数据获得的估计。如果优化器确定可以应用范围优化器，它就不会使用直方图统计数据。The optimizer prefers range optimizer row estimates to those obtained from histogram statistics. If the optimizer determines that the range optimizer applies, it does not use histogram statistics.

对于已建立索引的列，可以使用索引深入操作获得等值比较的行估计（参见第 10.2.1.2 节，“范围优化”）。在这种情况下，直方图统计信息不一定有用，因为索引深入操作可以产生更好的估计。For columns that are indexed, row estimates can be obtained for equality comparisons using index dives (see Section 10.2.1.2, “Range Optimization”). In this case, histogram statistics are not necessarily useful because index dives can yield better estimates.

在某些情况下，使用直方图统计信息可能不会改善查询执行（例如，如果统计信息过时）。要检查是否存在这种情况，请使用ANALYZE TABLE 重新生成直方图统计信息，然后再次运行查询。In some cases, use of histogram statistics may not improve query execution (for example, if the statistics are out of date). To check whether this is the case, use ANALYZE TABLE to regenerate the histogram statistics, then run the query again.

或者，要禁用直方图统计信息，可以使用 ANALYZE TABLE 来删除它们。禁用直方图统计信息的另一种方法是关闭 condition_fanout_filter 标志，该标志属于 optimizer_switch 系统变量（尽管这可能也会禁用其他优化功能）：Alternatively, to disable histogram statistics, use ANALYZE TABLE to drop them. A different method of disabling histogram statistics is to turn off the condition_fanout_filter flag of the optimizer_switch system variable (although this may disable other optimizations as well):

SET optimizer_switch='condition_fanout_filter=off';

如果使用了直方图统计信息，通过 EXPLAIN 可以看到其效果。考虑以下查询，其中列 col1 没有可用索引：If histogram statistics are used, the resulting effect is visible using EXPLAIN. Consider the following query, where no index is available for column col1:

SELECT * FROM t1 WHERE col1 < 24;

如果直方图统计显示 t1 中有 57% 的行满足 col1 < 24 这一条件，即使没有索引，也可以进行过滤，而且 EXPLAIN 在 filtered 列中显示 57.00。If histogram statistics indicate that 57% of the rows in t1 satisfy the col1 < 24 predicate, filtering can occur even in the absence of an index, and EXPLAIN shows 57.00 in the filtered column.