一文读懂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": [

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;
  }

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 为最小值
[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
+-----------------------+-------+

# 第一次查询
[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 |
+-----------+-------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+

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