一文读懂MySQL 8.0直方图(2)-阿里云开发者社区

无索引时，走直方图

"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进行直方图数据统计时，大致是这样的步骤：

估算要统计的列数据类型长度，记为 row_size_bytes。
可用内存除以每条记录长度，得到预计可以采样的数据量 rows_in_memory = histogram_generation_max_mem_size / row_size_bytes。
计算得到采样比例 sample_percentage = rows_in_memory / rows_in_table。其中 rows_in_table 是表预估总记录数。
依照采样比例，扫描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 不够大，则采样比例比较低，就会影响准确度，那么应该设置多少合理呢，可以按照下面这个方法：

先设置最小值。
执行一次采样。
查看采样比例。
反推出要全部采样所需的内存。
当然了，如果表数据量特别大，也没必要全部采样，毕竟消耗的内存比较多，而且也需要更多的物理读。
来个实例演示。

# 设置 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 |
+--------------------------------+

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