浅析MySQL 8.0直方图原理
本文分享自华为云社区《【MySQL技术专栏】MySQL8.0直方图介绍》，作者：GaussDB 数据库。
背景
数据库查询优化器负责将SQL查询转换为尽可能高效的执行计划，但因为数据环境不断变化导致优化器对查询数据了解的不够充足，可能无法生成最优的执行计划进而影响查询效率，因此MySQL8.0推出了直方图(histogram)功能来解决该问题。
直方图用于统计字段值的分布情况，向优化器提供统计信息。利用直方图，可以对一张表的一列数据做分布统计，估算where条件中过滤字段的选择率，从而帮助优化器更准确地估计查询过程中的行数，选择更高效的查询计划。
本文将对直方图概念进行介绍，借助举例描述直方图的使用方式，对创建/删除直方图的原理进行浅析，并通过例子说明其应用场景。
MySQL8.0直方图介绍
数据库中，查询优化器所生成执行计划的好坏关乎执行耗时的多少，优化器若是不清楚表中数据的分布情况，可能会导致无法生成最优的执行计划，造成执行时浪费时间。
假设一条SQL语句要查询相等间隔的两个不同时间段内出行的人数，若不知道每个时间段内的人数，优化器会假设人数在两个不同时间段内是均匀分布的。如果两个时间段内人数相差较大，这样优化器估算的统计数据就出现严重偏差，从而可能选择错误的执行计划。那么，如何使优化器比较清楚地知道数据统计情况进而生成好的执行计划呢？
一种解决方法就是，在列上建立直方图，从而近似地获取一列上的数据分布情况。利用好直方图，将会带来很多方面收益：
(1)查询优化：提供关于数据分布的统计信息，帮助优化查询计划，选择合适的索引和优化查询语句，从而提高查询性能；
(2)索引设计：通过分析数据的分布情况，帮助确定哪些列适合创建索引，以提高查询效率；
(3)数据分析：提供数据的分布情况，帮助用户了解数据的特征和趋势。
直方图分为两类：等宽直方图(singleton)和等高直方图(equi-height)。等宽直方图是每个桶保存一个值以及这个值累积频率：
SCHEMA_NAME: xxx//库名
TABLE_NAME: xxx//表名
COLUMN_NAME: xxx//列名
HISTOGRAM: {
"buckets":[
[
xxx, //桶中数值
xxx //取值频率
],
......
],
"data-type":"xxx", //数据类型
"null-values":xxx, //是否有NULL值
"collation-id":xxx,
"last-updated":"xxxx-xx-xx xx:xx:xx.xxxxxx", //更新时间
"sampling-rate":xxx, //采样率，1表示采集所有数据
"histogram-type":"singleton", //桶类型，等宽
"number-of-buckets-specified":xxx //桶数量
}
等高直方图每个桶需要保存不同值的个数，上下限以及累积频率等：
SCHEMA_NAME: xxx
TABLE_NAME: xxx
COLUMN_NAME: xxx
HISTOGRAM: {
"buckets":[
[
xxx, //最小值
xxx, //最大值
xxx, //桶值出现的频率
xxx //桶值出现的次数
],
......
],
"data-type":"xxx",
"null-values":xxx,
"collation-id":xxx,
"last-updated":"xxxx-xx-xx xx:xx:xx.xxxxxx",
"sampling-rate":xxx,
"histogram-type":"equi-height", //桶类型，等高
"number-of-buckets-specified":xxx
}
MySQL8.0直方图使用方式
创建和删除直方图时涉及analyze语句，常用语法格式为：
创建直方图：
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] ... [WITH N BUCKETS]
删除直方图：
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name] ...
具体示例：
mysql> create table t1(c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int,c12 int,c13 datetime,c14 int,c15 int,c16 int,primary key(c1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,2,3,4,5,6,7,8,9,10,11,12,'0000-01-01',14,15,16),(2,2,3,4,5,6,7,8,9,10,11,12,'0500-01-01',14,15,16),(3,2,3,4,5,6,7,8,9,10,11,12,'1000-01-01',14,15,16),(4,2,3,4,5,6,7,8,9,10,11,12,'1500-01-01',14,15,16),(5,2,3,4,5,6,7,8,9,10,11,12,'1500-01-01',14,15,16);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
创建直方图：
mysql> analyze table t1 update histogram on c13;
+---------+-----------+----------+------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-----------+----------+------------------------------------------------+
| test.t1 | histogram | status | Histogram statistics created for column 'c13'. |
+---------+-----------+----------+------------------------------------------------+
1 row in set (0.01 sec)
查看直方图信息：
mysql> select json_pretty(histogram)result from information_schema.column_statistics where table_name = 't1' and column_name = 'c13'\G
*************************** 1. row ***************************
result: {
"buckets": [
[
"0000-01-01 00:00:00.000000", //统计的列值
0.2 //统计的相对频率，下同
],
[
"0500-01-01 00:00:00.000000",
0.4
],
[
"1000-01-01 00:00:00.000000",
0.6
],
[
"1500-01-01 00:00:00.000000",
1.0
]
],
"data-type": "datetime", //统计的数据类型
"null-values": 0.0, //NULL值的比例
"c