【MySQL】一文读懂MySQL 8.0直方图

直方图科普。

0. 什么是直方图

1. 直方图怎么工作

2. 同时有索引和直方图会怎样

3. 如何提高直方图的统计精确度

0. 什么是直方图

  直方图（Histogram）是数据库提供的一种（索引之外的）基础统计信息，用于描述列上的数据分布情况。它最典型的场景是估算查询谓词的选择率，以便选择合适的执行计划。也就是说，某个列可以不创建索引但创建直方图，也可以帮助提升查询效率。
  MySQL 8.0开始支持直方图，这是个很大的进步。直方图可以针对某个列记录其数据分布统计信息，例如有个列的值是从1到1万，那么可以利用直方图分成100个桶（bucket），每个桶中统计这1万个值是怎么分布的，以及每个桶中的最大值、最小值、占比等信息。虽然可以利用索引优化SQL效率，但索引维护的代价更高，索引要保持更新，而直方图可以按需手动更新。索引统计信息也有不可靠的时候，例如存在数据倾斜，或者统计延迟等问题。另外，在有需要的时候，可以在每个有需要的列上创建直方图，但却不太可能同时创建多个单列索引，那样代价太高了。例如下面这个执行计划：

[root@yejr.run]> explain select * from t1 where seq = 1234;
+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | NULL          | NULL | NULL    | NULL | 299876 |    10.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

[root@yejr.run]> select * from t1 where seq = 1234;
Empty set (0.097 sec)

在还没创建直方图之前，seq列上同时也没有索引，这时是全表扫描，注意到 filtered 列的值是10%。

创建完直方图之后，再看这个执行计划：

[root@yejr.run]> explain select * from t1 where seq = 1234;
+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | NULL          | NULL | NULL    | NULL | 299876 |     0.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

[root@yejr.run]> select * from t1 where seq = 1234;
Empty set (0.079 sec)

  注意到filtered列值变成了0%，并且实际耗时是原来的81%，虽然绝对值也不算小，但相对于原来的全表扫描也还是要节省了将近20%耗时。

  所以说，直方图还是很有意义的，当然了，直方图还是无法代替索引，只在一些特定的场景里比较有用。

1. 直方图怎么工作

  MySQL支持两种直方图模式：等宽、等高。等宽直方图是每个桶保存一个值以及这个值累积频率，等高直方图每个桶需要保存不同值的个数，上下限以及累积频率等。MySQL会自动选用哪种类型的直方图，无需也不能指定。一般来说，数据数据分布范围比较大的话就采用等高，反之，如果数据分布比较小就采用等宽。直方图的统计信息物理表 column_statistics 存储在mysql表空间中，无法直接读写，但可以访问 information_schema.COLUMN_STATISTICS 视图来查看统计结果。

[root@yejr.run]> show create view column_statisticsG
*************************** 1. row ***************************
View: COLUMN_STATISTICS
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS ...
character_set_client: utf8
collation_connection: utf8_general_ci

每条记录对应一个直方图统计结果，用JSON格式保存。

此外，还有个参数 histogram_generation_max_mem_size 用来控制创建直方图时可用的内存，该参数很重要，后面会讲到。

截止MySQL 8.0.19版本，直方图支持多种数据类型和场景，甚至包括虚拟列。但不支持以下几种情况：

• 加密表、临时表。

• JSON数据类型、空间（spatial）数据类型。

• 已创建唯一索引的单列。

来个一个创建失败的例子：

[root@yejr.run]> analyze table t2 update histogram on nu;
+---------+-----------+----------+-----------------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                                  |
+---------+-----------+----------+-----------------------------------------------------------+
| yejr.t2 | histogram | Error    | The column 'nu' is covered by a single-part unique index. |
+---------+-----------+----------+-----------------------------------------------------------+

MySQL干脆利落地拒绝了这种反智行为。

当然了，如果是一个列创建了非唯一辅助索引，就可以创建直方图，不会冲突。

来创建个正式直方图：

+---------+-----------+----------+------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                       |
+---------+-----------+----------+------------------------------------------------+
| yejr.t1 | histogram | status   | Histogram statistics created for column 'seq'. |
+---------+-----------+----------+------------------------------------------------+

再看下 COLUMN_STATISTICS 中存储的统计信息：

[root@yejr.run]> select SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM) from COLUMN_STATISTICSG
*************************** 1. row ***************************
           SCHEMA_NAME: yejr   --库
            TABLE_NAME: t1     --表
           COLUMN_NAME: seq    --列
JSON_PRETTY(HISTOGRAM): {
  "buckets": [
    [       --第一个桶(bucket)中的统计信息
      1,    --最小值
      9710, --最大值
      0.009996666666666666, --累积占比，0.99%
      2571  --第一个桶中累积几个值
    ],
...
    [       --第100个桶中的统计信息
      989875,
      999994,
      0.9996666666666667,  --因为该列包含部分NULL值，所以这里不是1.0（100%）
      2580
    ]
  ],
  "data-type": "int",   --该列数据类型，是INT
  "null-values": 0.0003333333333333333, --是否包含NULL值，或者NULL值的占比
  "collation-id": 8,
  "last-updated": "2020-04-21 07:21:53.084054",  --直方图最后更新时间
  "sampling-rate": 1.0,  --采样比例 100%
  "histogram-type": "equi-height",  --等高直方图
  "number-of-buckets-specified": 100  --共有100个桶
}

  上面这个等高直方图，共100个桶，每个桶的数据量从2571 ~ 2620不等，总数据量259550，占比99.9667%。此外，还有大约0.033%为NULL的记录。

再来个等宽的直方图

  "buckets": [
    [
      5,   --存储每个值
      0.07692307692307693 ---该值频率
    ],
    [
      25,
      0.15384615384615385
    ],
......
  ],
  "data-type": “int",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2019-07-02 06:44:15.134130”,
  "sampling-rate": 1.0,   --数据采样比例
  "histogram-type": “singleton", --等宽
  "number-of-buckets-specified": 100
}

等宽直方图里，每个桶里记录是各个值的分布信息。

2. 同时有索引和直方图会怎样

  某天，群里有同学在问，假如有个列同时创建了直方图和索引，优化器会怎么选择呢？

  在回答之前，可以先开通脑筋想几秒钟…

  事实上，真是这样的话，优化器会选择索引而非直方图。因为索引的统计信息相对"更及时"，也可能"更精确"，因为直方图是需要手动更新的，没办法保证"及时性"。当然了，我没去看源码，仅是我猜的，并通过试验确认的。

假设上面创建直方图的列 seq，同时也创建了索引，在开启 optimizer_trace 之后，可以看到两个执行计划之间的区别（我只选取了部分内容）

• 无索引时，走直方图

"considered_execution_plans": [
  {
    "table": "`t1`",
    "chosen": true,
    "plan_prefix": [],
    "cost_for_plan": 35080,
    "rows_for_plan": 1,
    "best_access_path": {
      "considered_access_paths": [
        {
          "cost": 35080,   --cost看起来好高
          "chosen": true,  --但还是被选中了
          "access_type": "scan",   --全表扫描
          "rows_to_scan": 347352,  --预计需要扫描行数
          "resulting_rows": 1,   --预计返回行数
          "filtering_effect": [   
            {
              "condition": "(`t1`.`seq` = 1234)",
              "histogram_selectivity": 0.000001   --直方图过滤效果
            }
          ],
          "final_filtering_effect": 0.0000029
        }
      ]
    },
    "condition_filtering_pct": 100
  }

虽然看起来是要走全表扫描，但因为有了直方图，实际上还是很快就能返回结果的。

• 有索引时，优先走索引

"considered_execution_plans": [
 {
   "table": "`t1`",
   "chosen": true,
   "plan_prefix": [],
   "cost_for_plan": 0.35,
   "rows_for_plan": 1,
   "best_access_path": {
     "considered_access_paths": [
       {
         "cost": 0.35,   --相当上面直方图的cost，这个就非常低了，所以毫无悬念的选择索引
         "rows": 1,
         "index": "k1",
         "chosen": true,
         "access_type": "ref"
       },
       {
         "cause": "heuristic_index_cheaper",
         "chosen": false,
         "access_type": "range",
         "range_details": {
           "used_index": "k1"
         }
       }
     ]
   },
   "condition_filtering_pct": 100
}

如果有源码大佬，也请帮忙确认下是不是这样吧。

3. 如何提高直方图的统计精确度

  前文我们提到过参数 histogram_generation_max_mem_size，其作用是控制在创建/更新直方图时所需的内存大小。

The maximum amount of memory available for generating histogram statistics.

  该参数默认值是 20000000（不到20MB），最小值 1000000（约976KB），这是个会话级（session）分配的内存，而且是每次创建/更新直方图都需要分配，执行结束后就释放。

  介绍完前置信息，该说重点了，在直方图里如何提高统计精确度。

  在扫描InnoDB data page进行直方图数据统计时，大致是这样的步骤：

1. 估算要统计的列数据类型长度，记为 row_size_bytes。

2. 可用内存除以每条记录长度，得到预计可以采样的数据量 rows_in_memory = histogram_generation_max_mem_size / row_size_bytes。

3. 计算得到采样比例 sample_percentage = rows_in_memory / rows_in_table。其中 rows_in_table 是表预估总记录数。

4. 依照采样比例，扫描data page，得到采样结果。例如，采样比例是10%，那么就是扫描1个page后，跳过9个page，然后继续采样。
   这几个步骤是以我的三脚猫源码阅读水平得到的结果，若有出入，还请留言指正。
   上述步骤所对应的代码是 sql/histograms/histogram.cc，约868行附近的 update_histogram 函数。

  MySQL目前对数据长度处理的非常粗粒度，只区分了下面几种情况，这就导致了直方图列实际所需要的内存可能要比它定义的类型长度要更大，也可以看下源码中的定义：

vim sql/histograms/histogram.cc +113

/**
  Convert from enum_field_types to Value_map_type.
  @param field_type the field type
  @param is_unsigned whether the field type is unsigned or not. This is only
                     considered if the field type is LONGLONG
  @return A Value_map_type. May be INVALID if the Value_map does not support
          the field type.
*/
static Value_map_type field_type_to_value_map_type(
    const enum_field_types field_type, const bool is_unsigned) {
  switch (field_type) {
    case MYSQL_TYPE_DECIMAL:
    case MYSQL_TYPE_NEWDECIMAL:
      return Value_map_type::DECIMAL;
    case MYSQL_TYPE_TINY:
    case MYSQL_TYPE_SHORT:
    case MYSQL_TYPE_LONG:
    case MYSQL_TYPE_INT24:
    case MYSQL_TYPE_YEAR:
    case MYSQL_TYPE_BIT:
      return Value_map_type::INT;
    case MYSQL_TYPE_ENUM:
      return Value_map_type::ENUM;
    case MYSQL_TYPE_SET:
      return Value_map_type::SET;
    case MYSQL_TYPE_LONGLONG:
      return is_unsigned ? Value_map_type::UINT : Value_map_type::INT;
    case MYSQL_TYPE_FLOAT:
    case MYSQL_TYPE_DOUBLE:
      return Value_map_type::DOUBLE;
    case MYSQL_TYPE_TIME:
    case MYSQL_TYPE_TIME2:
      return Value_map_type::TIME;
    case MYSQL_TYPE_DATE:
    case MYSQL_TYPE_NEWDATE:
      return Value_map_type::DATE;
    case MYSQL_TYPE_DATETIME:
    case MYSQL_TYPE_TIMESTAMP:
    case MYSQL_TYPE_TIMESTAMP2:
    case MYSQL_TYPE_DATETIME2:
      return Value_map_type::DATETIME;
    case MYSQL_TYPE_TINY_BLOB:
    case MYSQL_TYPE_MEDIUM_BLOB:
    case MYSQL_TYPE_LONG_BLOB:
    case MYSQL_TYPE_BLOB:
    case MYSQL_TYPE_VAR_STRING:
    case MYSQL_TYPE_STRING:
    case MYSQL_TYPE_VARCHAR:
      return Value_map_type::STRING;
    case MYSQL_TYPE_JSON:
    case MYSQL_TYPE_GEOMETRY:
    case MYSQL_TYPE_NULL:
    default:
      return Value_map_type::INVALID;
  }

  可以看到，把TINYINT（1字节）、SMALLINT（2字节）等都统统按 Value_map_type::INT 来处理了，而这个类型实际上是 longlong 的，也就是 16字节。

  另外，每条记录还需要约42字节的额外开销（比大多数据类型长度还要大，尴尬）。

vim sql/histograms/value_map.h +262

  /// @return the overhead in bytes for each distinct value stored in the
  ///         Value_map. The value 32 is obtained from both GCC 8.2 and
  ///         Clang 8.0 (same as sizeof(value_map_type::node_type) in C++17).
  size_t element_overhead() const override {
    // TODO: Replace this with sizeof(value_map_type::node_type) when we have
    // full C++17 support.
    return sizeof(typename value_map_type::value_type) +
           sizeof(typename value_map_type::key_type) + 32;
  }

  其他的几个数据类型也是很粗犷的处理了，在以后的版本应该会改进吧。

  如果 histogram_generation_max_mem_size 不够大，则采样比例比较低，就会影响准确度，那么应该设置多少合理呢，可以按照下面这个方法：

1. 先设置最小值。

2. 执行一次采样。

3. 查看采样比例。

4. 反推出要全部采样所需的内存。
   当然了，如果表数据量特别大，也没必要全部采样，毕竟消耗的内存比较多，而且也需要更多的物理读。
   来个实例演示。

# 设置 histogram_generation_max_mem_size 为最小值
[root@yejr.run]> set session histogram_generation_max_mem_size = 1000000;

# 重置InnoDB Metric计数器
[root@yejr.run]> SET GLOBAL innodb_monitor_enable = 'sampled%';

# 来一发，创建个直方图
[root@yejr.run]> analyze table t1 update histogram on seq;
1 row in set (0.16 sec)   --耗时0.16秒

# 查看采样统计比例
[root@yejr.run]> SELECT HISTOGRAM->>'$."sampling-rate"' FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.059548422871929935            |
+---------------------------------+

# 查看innodb data page扫描统计
[root@yejr.run]> SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'sampled%';
+-----------------------+-------+
| NAME                  | COUNT |
+-----------------------+-------+
| sampled_pages_read    |    51 |  --扫描了51个page
| sampled_pages_skipped |   760 |  --跳过了760个page
+-----------------------+-------+

与此同时，我在另一个session中，分别在之前和之后查询上面创建直方图的线程内存消耗情况。

# 第一次查询
[root@yejr.run]> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 286;
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                          | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|       286 | thread_pool/tp_one_connection |                163 |            345036 |         2116.7853 |            260280 |       212710814 |
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
1 row in set (0.07 sec)

#第二次查询
[root@yejr.run]> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 286;
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                          | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|       286 | thread_pool/tp_one_connection |                165 |            346502 |         2100.0121 |            261712 |       213946226 |
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+

两次查询 total_allocated 的差值 1235412（1.17MB） 主要就是由于创建直方图所需的内存。

现在，可以推算所需要的内存大约是

[root@yejr.run]> select 1235412 / 0.059548422871929935;
+--------------------------------+
| 1235412 / 0.059548422871929935 |
+--------------------------------+
|                  20746342.8991 |
+--------------------------------+

现在加大内存设置后，再做一次看看

[root@yejr.run]> set session histogram_generation_max_mem_size = 1000000;
[root@yejr.run]> SET GLOBAL innodb_monitor_enable = 'sampled%';
[root@yejr.run]> analyze table t1 update histogram on seq;
1 row in set (3.78 sec)   --耗时3.78秒，是上面的23.6倍

[root@yejr.run]> SELECT HISTOGRAM->>'$."sampling-rate"' FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 1.0                             |
+---------------------------------+

[root@yejr.run]> SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'sampled%';
+-----------------------+-------+
| NAME                  | COUNT |
+-----------------------+-------+
| sampled_pages_read    |   811 |
| sampled_pages_skipped |     0 |   --的确，没有被跳过的page了
+-----------------------+-------+

再次提醒，并不是非得所有page都要被采集到，否则代价可能无法承受。。。

MySQL性能优化的核心要素：合理利用索引，降低锁影响，提高事务并发度。