概念描述

  从MySQL8.0.19版本开始引入了一种统计信息叫直方图（Histogram）属于MySQL8.0版本的新特性，用于描述InnoDB表中某一列的数据分布情况，通过统计列值的分布情况，向查询优化器提供统计信息，帮助优化器更准确地估算查询过程中的行数，从而选择更高效的查询计划。它通过将数据分布情况以直方图的形式存储，使得优化器能够根据实际数据分布来生成执行计划，而不是仅依赖于简单的统计信息。
 

工作原理

  1. 数据采样： MySQL通过ANALYZE TABLE命令收集直方图信息。直方图基于表中某一列的实际数据分布生成的，也可以针对表中的多列生成，用于描述这些列的数据分布情况。在创建直方图时，MySQL会对指定列的数据进行采样，采样率可以通过 histogram_generation_max_mem_size 参数控制，该参数限制生成直方图时所允许使用的最大内存大小。
  2. 桶的分布： 根据采样数据，将数据分布划分为多个桶（bucket）每个桶记录该范围内数据的频率或密度。桶的数量可以通过WITH N BUCKETS指定，若未指定则默认为100个桶。当桶的数量不小于列中distinct值的数量时，创建单例直方图（Singleton Histogram）；否则创建等高直方图（Equi-height Histogram）。
  3. 直方图存储： 直方图信息存储在information_schema.column_statistics表中，以JSON格式存储，主要包括桶的分布、数据类型、NULL值比例、采样率等。

  4. 优化器使用： 当执行查询语句时，在生成执行计划时候，优化器会参考直方图信息，优化器会使用直方图信息来估算查询的选择性。例如，对于一个WHERE条件，优化器可以通过直方图估算满足条件的行数，从而选择更优的执行计划。
 

使用示例

  1. 创建直方图

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;

-- 对表t的c1字段创建直方图，如果不指定 WITH N BUCKETS 默认会生成100个桶
mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+


ANALYZE TABLE sales UPDATE HISTOGRAM ON amount WITH 10 BUCKETS;
--说明：在这个例子中，WITH 10 BUCKETS 表示我们将 amount 列的值分成 10 个桶（即 10 个区间）。可以根据需要调整桶的数量。

复制

  2. 删除直方图

ANALYZE TABLE table_name DROP HISTOGRAM ON column_name;

-- 删除直方图示例
ANALYZE TABLE sales DROP HISTOGRAM ON amount;
复制

  3. 查看直方图信息
  通过查询 information_schema 数据库中的 column_statistics 表来查看直方图信息。

-- 查看直方图信息
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'your_table' AND COLUMN_NAME = 'your_column';
复制

  4. 解读直方图信息

mysql >select * from COLUMN_STATISTICS where table_name='my_table'\G
*************************** 1. row ***************************
SCHEMA_NAME: testdb
 TABLE_NAME: my_table
COLUMN_NAME: col10
  HISTOGRAM: {"buckets": [["base64:type254:ZGF0YTA=", "base64:type254:ZGF0YTEyNDI=", 0.0273, 273], ["base64:type254:ZGF0YTEyNDM=", "base64:type254:ZGF0YTE0ODk=", 0.0546, 273], ["base64:type254:ZGF0YTE0OQ==", "base64:type254:ZGF0YTE3MzQ=", 0.0819, 273], ["base64:type254:ZGF0YTE3MzU=", "base64:type254:ZGF0YTE5ODA=", 0.1092, 273], ["base64:type254:ZGF0YTE5ODE=", "base64:type254:ZGF0YTIyMjU=", 0.1365, 273], ["base64:type254:ZGF0YTIyMjY=", "base64:type254:ZGF0YTI0NzE=", 0.1638, 273], ["base64:type254:ZGF0YTI0NzI=", "base64:type254:ZGF0YTI3MTc=", 0.1911, 273], ["base64:type254:ZGF0YTI3MTg=", "base64:type254:ZGF0YTI5NjM=", 0.2184, 273], ["base64:type254:ZGF0YTI5NjQ=", "base64:type254:ZGF0YTMyMDg=", 0.2457, 273], ["base64:type254:ZGF0YTMyMDk=", "base64:type254:ZGF0YTM0NTQ=", 0.273, 273], ["base64:type254:ZGF0YTM0NTU=", "base64:type254:ZGF0YTM3MA==", 0.3003, 273], ["base64:type254:ZGF0YTM3MDA=", "base64:type254:ZGF0YTM5NDY=", 0.3276, 273], ["base64:type254:ZGF0YTM5NDc=", "base64:type254:ZGF0YTQxOTE=", 0.3549, 273], ["base64:type254:ZGF0YTQxOTI=", "base64:type254:ZGF0YTQ0Mzc=", 0.3822, 273], ["base64:type254:ZGF0YTQ0Mzg=", "base64:type254:ZGF0YTQ2ODM=", 0.4095, 273], ["base64:type254:ZGF0YTQ2ODQ=", "base64:type254:ZGF0YTQ5Mjk=", 0.4368, 273], ["base64:type254:ZGF0YTQ5Mw==", "base64:type254:ZGF0YTY2Ng==", 0.4631, 263], ["base64:type254:ZGF0YTY2NjY2NjY2NjY2", "base64:type254:ZGF0YTY2NjY2NjY2NjY2", 0.9631, 1], ["base64:type254:ZGF0YTY2Nw==", "base64:type254:ZGF0YTkxMQ==", 0.9904, 273], ["base64:type254:ZGF0YTkxMg==", "base64:type254:ZGF0YTk5OQ==", 1.0, 96]], "data-type": "string", "null-values": 0.0, "collation-id": 45, "last-updated": "2025-03-20 09:03:17.605493", "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 20}
1 row in set (0.00 sec)

--为方便可读，将上面HISTOGRAM列中的json串进行格式化如下显示：

HISTOGRAM: 
{
  "buckets": [
    ["base64:type254:ZGF0YTA=", "base64:type254:ZGF0YTEyNDI=", 0.0273, 273],
    ["base64:type254:ZGF0YTEyNDM=", "base64:type254:ZGF0YTE4ODk=", 0.0546, 273],
    ["base64:type254:ZGF0YTE0OQ==", "base64:type254:ZGF0YTE3MzQ=", 0.0819, 273],
    ["base64:type254:ZGF0YTE3MzU=", "base64:type254:ZGF0YTE5ODA=", 0.1092, 273],
    ["base64:type254:ZGF0YTE5ODE=", "base64:type254:ZGF0YTIyMjU=", 0.1365, 273],
    ["base64:type254:ZGF0YTIyMjY=", "base64:type254:ZGF0YTI0NzE=", 0.1638, 273],
    ["base64:type254:ZGF0YTI0NzI=", "base64:type254:ZGF0YTI3MTc=", 0.1911, 273],
    ["base64:type254:ZGF0YTI3MTg=", "base64:type254:ZGF0YTI5NjM=", 0.2184, 273],
    ["base64:type254:ZGF0YTI5NjQ=", "base64:type254:ZGF0YTMyMDg=", 0.2457, 273],
    ["base64:type254:ZGF0YTMyMDk=", "base64:type254:ZGF0YTM0NTQ=", 0.273, 273],
    ["base64:type254:ZGF0YTM0NTU=", "base64:type254:ZGF0YTM3MA==", 0.3003, 273],
    ["base64:type254:ZGF0YTM3MDA=", "base64:type254:ZGF0YTM5NDY=", 0.3276, 273],
    ["base64:type254:ZGF0YTM5NDc=", "base64:type254:ZGF0YTQxOTE=", 0.3549, 273],
    ["base64:type254:ZGF0YTQxOTI=", "base64:type254:ZGF0YTQ0Mzc=", 0.3822, 273],
    ["base64:type254:ZGF0YTQ0Mzg=", "base64:type254:ZGF0YTQ2ODM=", 0.4095, 273],
    ["base64:type254:ZGF0YTQ2ODQ=", "base64:type254:ZGF0YTQ5Mjk=", 0.4368, 273],
    ["base64:type254:ZGF0YTQ5Mw==", "base64:type254:ZGF0YTY2Ng==", 0.4631, 263],
    ["base64:type254:ZGF0YTY2NjY2NjY2NjY2", "base64:type254:ZGF0YTY2NjY2NjY2NjY2", 0.9631, 1],
    ["base64:type254:ZGF0YTY2Nw==", "base64:type254:ZGF0YTkxMQ==", 0.9904, 273],
    ["base64:type254:ZGF0YTkxMg==", "base64:type254:ZGF0YTk5OQ==", 1.0, 96]
  ],
  "data-type": "string",
  "null-values": 0.0,
  "collation-id": 45,
  "last-updated": "2025-03-20 09:03:17.605493",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 20
}

--上面结果中 HISTOGRAM列是直方图的核心部分，它包含了列 col10 的数据分布信息。它是一个 JSON 对象，包含以下字段：
    1. buckets 表示直方图的桶（bucket）信息。每个桶代表列 col10 中一定范围内的数据分布。每个桶包含以下信息：
      [lower_bound, upper_bound, cumulative_frequency, frequency]
      ​第一个值：桶的下限值（base64:type254:... 格式）经过 Base64 编码的字符串，解码后可以得到实际的数据值。
​      第二个值：桶的上限值（base64:type254:... 格式）经过 Base64 编码的字符串，解码后可以得到实际的数据值。
​      累积频率：表示该桶及其之前所有桶中的数据占总数据的比例。
​      频数：表示该桶内数据的频率，即该桶中有多少行数据。

    例如，第一个桶
    ["base64:type254:ZGF0YTA=", "base64:type254:ZGF0YTEyNDI=", 0.0273, 273],

    - base64:type254:VmFsdWUxMDA= 和 base64:type254:VmFsdWUxMDQ3NTA= 是该桶的下限和上限值。
    - 0.0273 是该桶的累积频率，表示该桶及其之前所有桶中的数据占总数据的 2.73%。
    - 273 是该桶的频数，表示该桶中有273行数据

    2. data-type: "string": 表示 col10 列的数据类型是字符串（string）。
    3. null-values: 0.0：表示该列中没有 NULL 值，或者 NULL 值的比例为 0%。
    4. ​collation-id: 45：表示该列的排序规则（collation）的 ID 是 45。排序规则决定了字符串的比较和排序方式。
    5. last-updated": "2025-03-20 09:03:17.605493：表示直方图最后一次更新的时间戳是2025-03-20 09:03:17.605493。
    6. sampling-rate: 1.0：表示生成直方图时，采样率为 100%。即 MySQL 在生成直方图时，采样了表中 100%的数据。
    7. ​histogram-type: "equi-height"：表示直方图的类型是“等高直方图”（equi-height）。等高直方图将数据分成若干个桶，每个桶中的行数大致相等。
    8. number-of-buckets-specified: 20：表示在生成直方图时，指定了 20个桶。MySQL 会根据这个参数将数据分成 20个桶。
复制

使用场景

直方图功能在以下场景中特别有用：

• ​数据分布不均匀：当表中某一列的数据分布不均匀时，直方图可以帮助优化器更准确地估算查询条件的选择性。
• 多列查询：在涉及多列查询时，直方图可以帮助优化器更好地估算组合条件的选择性。
• ​索引选择：当表中有多个索引时，直方图可以帮助优化器选择更合适的索引。
• ​范围查询：对于范围查询（如 BETWEEN、>、< 等），直方图可以帮助优化器更准确地估算扫描的行数。
   

直方图的优缺点

优点​

• ​提高查询性能：通过更准确的选择性估算，优化器可以生成更高效的执行计划，从而提高查询性能。
• ​减少资源消耗：准确的执行计划可以减少不必要的扫描和排序操作，从而减少CPU和I/O资源的消耗。
• 适应性强：适用于各种数据类型，包括数值型、日期型和字符串型。

缺点：​

• ​存储开销：直方图需要额外的存储空间来保存统计信息，特别是在大表上，这可能增加存储开销。
• ​维护成本：直方图需要定期更新（通过 ANALYZE TABLE），否则可能失效。
• ​复杂性：直方图的维护和使用增加了数据库的复杂性，特别是在多列查询和复杂查询条件下。
• 不适用于所有场景：对于数据分布均匀的列，直方图可能不会带来显著的性能提升。
   

涉及到的参数

histogram_generation_max_mem_size：

• 作用：控制生成直方图时使用的最大内存量。
• 默认值：20000000（20 MB）。
• 说明：如果生成直方图所需的内存超过此值，MySQL 会减少直方图的桶数（buckets）或停止生成直方图。
• 调整建议：如果表的列数据量较大，可以适当增加此值，以确保直方图能够完整生成。

SET GLOBAL histogram_generation_max_mem_size = 50000000; -- 设置为 50 MB
复制

innodb_stats_persistent：

• 作用：控制 InnoDB 表的统计信息是否持久化。
• 默认值：ON。
• 说明：直方图信息依赖于表的统计信息。如果此参数为 ON，统计信息会持久化到磁盘，避免每次重启后重新生成。
• 调整建议：通常保持默认值 ON，以确保统计信息的稳定性。

innodb_stats_persistent_sample_pages

• 作用：控制 InnoDB 表在计算统计信息时采样的页数。
• 默认值：20。
• 说明：采样页数越多，统计信息越准确，但生成统计信息的开销也越大。直方图信息依赖于这些统计信息。
• 调整建议：对于数据分布不均匀的表，可以适当增加此值。

SET GLOBAL innodb_stats_persistent_sample_pages = 50;
复制

注意事项

• 直方图更新频率：直方图信息不会自动更新，需要定期通过 ANALYZE TABLE 手动更新。
• 内存限制：如果列的数据量较大，生成直方图时可能会受到 histogram_generation_max_mem_size 的限制。
• ​数据变化：如果表中的数据发生了显著变化（如大量插入、删除或更新），直方图信息可能会失效，需要重新生成。
   

总结

  MySQL 8.0 的直方图功能通过提供更精确的统计信息，帮助优化器更好地估算查询的选择性，从而提升查询性能。直方图特别适用于处理非均匀分布的数据，但在使用时需要注意定期更新直方图，以确保其准确性。
 

参考文档

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
https://dev.mysql.com/doc/refman/8.0/en/information-schema-column-statistics-table.html