mysql直方图

直方图，histogram，直译就是柱状图，国内多翻译为直方图。

直方图不是图，是对表的某个字段数据分布的统计，这种数据统计是基于某字段的实际存储数据分布得出的。

这种分布统计可以帮助查询优化器掌握字段真实的情况，从而得出更高效而切合实际的执行计划。

在有直方图之前，优化器对某字段的数据分布预估有可能是不准确的，甚至可能和实际情况差距比较大，错误的预估会导致不高效的执行计划。

从Explain sql的角度看，建立直方图后会影响Explain结果的filter字段，使之成为较为真实的比例，从而影响执行计划的选择。

直方图分为单值直方图（singleton histograms）和等高直方图（equi-height histograms）。
 

直方图的生成
生成直方图的sql语句是这样的：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
可以看到，可以同时对多个字段生成直方图。

BUCKETS是统计结果的分组个数，如果不写则默认100，可选值从1到1024。数字越大统计结果越精确，官方建议从小值慢慢增加到可以满足预期的大小。

直方图生成的时候，MySQL会把所有相关数据读入内存中，然后进行分组统计。

8.0新增的参数histogram_generation_max_mem_size定义了直方图能用多大的内存。

如果字段值超过限定内存，则会取样，样本随机，也就是说两次生成的直方图内容可能不一样。

直方图的删除

sql如下：

ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

随时可以删，不影响数据库其他操作。

直方图的存储

生成的直方图保存在information_schema.COLUMN_STATISTICS表中，内容是JSON格式。

在单值直方图的buckets中，有2个值：

第1个值：bucket代表的值。类型由字段类型决定。
第2个值：自己的bucket和前面的bucket中的值的占比之和，是一个逐渐增加的值。取值在0.0到1.0之间。显然最后一个bucket中比例一定是1。
 

在等高直方图的buckets中，有4个值：

第1个值：bucket包括的值的范围的下限。
第2个值：bucket包括的值的范围的上限。
第3个值：自己bucket取值范围和前面bucket取值范围中的值的占比之和，是一个逐渐增加的值。取值在0.0到1.0之间。同单值直方图的第2个值，最后一个bucket的比例是1。
第4个值：bucket包括的值的distinct数。
 

在buckets之外的参数：

null-values：null值比例。取值在0.0到1.0之间。0代表没有null值。
last-updated：直方图更新时间。格式：YYYY-MM-DD hh:mm:ss.uuuuuu。
sampling-rate：取样比例。取值在0.0到1.0之间。1.0代表bucket值完全覆盖字段值，没有取样。
histogram-type：直方图类型。singleton代表单值直方图，每个bucket都代表一个值，字段distinct值小于sql指定bucket数时使用该类型。equi-height代表等高直方图，每个bucket都代表一个值的范围，字段distinct值大于sql指定bucket数时使用该类型。
number-of-buckets-specified：生成直方图的sql语句中指定的bucket数量。
data-type：直方图中的数据类型。从磁盘中读取并存入内存中时会用到。可能的值有：int，uint (unsigned integer)，double，decimal，datetime，string (包括character and binary strings)。
collation-id：直方图数据中的字符集id。和INFORMATION_SCHEMA.COLLATIONS表中id对应。直方图中的数据是String类型时会有用。
直方图中的值可以用MySQL8.0加入的JSON语法获得。
 

直方图和索引
使用索引也可以把查询效率提高不少，效果不比直方图差，二者的区别有：

1，维护索引是有成本的。新增，删除，修改字段时同时需要维护索引，影响效率。对比之下直方图一经建立就不再修改，不会影响操作效率（显然是把双刃剑，会存在数据不准确的情况）。

2，使用索引时，优化器使用index dives的方式预估大概范围，这个操作也是有成本的。特别是in语句且列表有大量值时，直方图的效果更强一点。

 

使用直方图时要注意的点

通过前面直方图和索引的比较可以看到，直方图自生成之后就不再随数据变化而改变，除非手动重做直方图。

这样带来的数据不准确的问题是需要开发者自己考虑的。比如什么时候需要刷新直方图，以频率刷新等。

因为生成直方图消耗的cpu和内存资源都挺大，使用的时候要注意。