1、什么是直方图

MySQL8.0开始支持索引之外的数据分布统计信息可选项。它就是直方图（Histogram）。

直方图通过估算查询谓词的选择率，以便选择合适的执行计划，也让SQL优化有了更多手段。

在DB中，优化器负责将SQL转换为很多个不同的执行计划，然后从中选择一个最优的来实际执行。但是有时候优化器选择的最终计划有可能随着DB环境的变化不是最优的，这就导致了查询性能不是很好。

比如，优化器无法准确的知道每张表的实际行数以及参与过滤条件的列有多少个不同的值。当然不同类型的索引也可以解决这个问题，但是你不能每个列都建索引吧？

如果一张表有1000个字段，那全字段索引将会拖死对这张表的写入。而且但索引维护的代价更高，索引统计信息也有不可靠的时候，例如存在数据倾斜，或者统计延迟等问题，索引要保持更新，而直方图可以按需手动更新。此时，直方图就是相对来说，开销较小的方法。

当然了，直方图还是无法代替索引，只在一些特定的场景里比较有用。

可以通过以下方式来创建或者删除直方图：

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];
  • 1.
  • 2.

buckets默认是100，范围是1-1024。统计直方图的信息存储在数据字典表 column_statistcs 中,可以通过视图information_schema.column_statistcs访问。

直方图以灵活的JSON的格式存储。ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。

ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）。

2、直方图工作原理

MySQL支持两种直方图模式：等宽、等高。等宽直方图是每个桶保存一个值以及这个值累积频率，等高直方图每个桶需要保存不同值的个数，上下限以及累积频率等。

MySQL会自动选用哪种类型的直方图，无需也不能指定。一般来说，数据数据分布范围比较大的话就采用等高，反之，如果数据分布比较小就采用等宽。

截止MySQL 8.0.19版本，直方图支持多种数据类型和场景，甚至包括虚拟列。但不支持以下几种情况：

  • 加密表、临时表。
  • JSON数据类型、空间（spatial）数据类型。
  • 已创建唯一索引的单列。

来看一个直方图 COLUMN_STATISTICS 中存储的统计信息：

MySQL8.0直方图详解_直方图

 

 

上面这个等高直方图，共100个桶，每个桶的数据量从2571 ~ 2620不等，总数据量259550，占比99.9667%。

此外，还有大约0.033%为NULL的记录。再来个等宽的直方图

MySQL8.0直方图详解_直方图_02

 

 

等宽直方图里，每个桶里记录是各个值的分布信息。

3、直方图做了什么

如果你想要知道直方图做了什么，最简单的方式就是看一下执行计划。举个例子：

MySQL8.0直方图详解_MySQL_03

 

 

可以看到filtered列，从默认的11.11%变成了更精确的32.12%。但是，如果有多个条件，有些有直方图，有些没有，就比较难判断优化器做了什么改进：

如果想要知道更多关于直方图统计的细节，可以使用trace:

MySQL8.0直方图详解_直方图_04

 

 

这里用了JSON_EXTRACT从trace里取出相关的部分。对于每个条件，直方图被使用的话，就会看到估算过的字段的选择性。

在这个例子里，通过直方图，对“c_birth_day <= 20”条件，估算出63.76%的数据满足条件。事实上，与实际的数据分布情况基本一致。