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
}
https://blog.51cto.com/u_12592884/5654875
