MySQL8.0新特性之直方图

什么是直方图

MySQL8.0 引入了直方图的统计信息，用于统计字段值的分布情况。典型的场景是估算where条件中过滤的字段的选择率，以便选择更好的执行计划。例如性别、状态等字段。 直方图和索引区别：

索引可以减少扫描行数，而直方图不能。当使用直方图进行查询时，它不能直接减少扫描行数，但是可以帮助优化器选择最好的查询计划。 直方图维护成本远低于索引，对索引字段DML字段需要修改对应的索引，但是直方图只需要在创建和修改是消耗资源。 索引需要大量的存储空间，而直方图需要的存储空间几乎为零。 在判断某个范围内的行数时，索引的成本要高的多，因为索引需要使用索引试探进行收集和计算，而直方图这方面的信息是现成的。 接下来，我们来学习一下直方图怎么使用。 创建测试表

测试表一和表二数据量一样（100万），并且表结构一样。 测试表一：

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.26 sec)

mysql> show create table sbtest1 \G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `status` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

测试表二：

mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.06 sec)

mysql> show create table sbtest2 \G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `status` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

测试表一查看执行计划

mysql> explain format=tree select * from sbtest1 where status >2 and status<5 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((sbtest1.`status` > 2) and (sbtest1.`status` < 5))  (cost=102075.25 rows=109578)
    -> Table scan on sbtest1  (cost=102075.25 rows=986400)

可以看到，Filter:cost=102075.25 rows=109578

测试表二创建直方图

mysql> analyze table sbtest2 update histogram on status;
+----------------+-----------+----------+---------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                          |
+----------------+-----------+----------+---------------------------------------------------+
| sbtest.sbtest2 | histogram | status   | Histogram statistics created for column 'status'. |
+----------------+-----------+----------+---------------------------------------------------+

测试表二查看执行计划

mysql> explain format=tree select * from sbtest2 where status >2 and status<5 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((sbtest2.`status` > 2) and (sbtest2.`status` < 5))  (cost=102133.75 rows=4641)
    -> Table scan on sbtest2  (cost=102133.75 rows=986020)

可以看到，Filter的cost=102133.75 rows=4641，扫描行数降低不少。

查看表二status字段直方图

mysql> select json_pretty(histogram) from information_schema.column_statistics where table_name='sbtest2' and column_name='status' \G
*************************** 1. row ***************************
json_pretty(histogram): {
  "buckets": [
    [
      1,
      0.0013733675901630839
    ],
    [
      2,
      0.9952819499089427
    ],
    [
      3,
      0.9970430276339033
    ],
    [
      4,
      0.9976873909565246
    ],
    [
      5,
      0.9986566662935185
    ],
    [
      6,
      1.0
    ]
  ],
  "data-type": "int",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2022-09-06 03:03:39.789888",
  "sampling-rate": 0.3622065040697523,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 100
}