MySQL8.0新特性学习笔记（三）：直方图

一，直方图简介

二，直方图的生成

三，直方图的删除

四，直方图的存储

五，举例说明直方图的作用

六，直方图和索引

七，获得直方图的信息

一，直方图简介

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

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

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

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

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

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

不知道为什么国内多把singleton 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定义了直方图能用多大的内存。

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

官方例子：

mysql> SELECT histogram->>'$."sampling-rate"'
    -> FROM information_schema.column_statistics
    -> WHERE table_name = "customer"
    -> AND column_name = "c_birth_country";
+---------------------------------+
| histogram->>'$."sampling-rate"' |
+---------------------------------+
| 0.048743243211626014            |
+---------------------------------+
1 row in set (0.00 sec)

sampling-rate是查询直方图取样比例用的，这次的查询结果代表此字段的直方图是大概取样4.8%得到的。

一个字段只能生成一个直方图，重复生成会把前者覆盖。

生成直方图的sql中如果有不存在的字段，MySQL依然会为sql中存在的字段生成直方图。

生成直方图时，如果字段的distinct值小于sql指定的bucket，MySQL会选择生成单值直方图（singleton histograms），否则生成等高直方图（equi-height histograms）。

三，直方图的删除

sql如下：

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

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

四，直方图的存储

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

下面说一下JSON的格式，单值直方图和等高直方图的JSON格式略有不同。

以下是一个单值直方图的JSON例子：

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

以下是一个等高直方图的例子：

{
  "buckets": [

    [
      38671,
      99756,
      0.249795,
      17002
    ],
    [
      99757,
      100248,
      0.500035,
      492
    ],
    [
      100249,
      100743,
      0.749945,
      495
    ],
    [
      100744,
      172775,
      1.0,
      16630
    ]
  ],
  "data-type": "int",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2018-09-22 09:59:30.857797",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 4
}

二者在buckets参数中存储的内容不一样，其他部分的格式是一样的。

在单值直方图的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语法获得。

五，举例说明直方图的作用

官方举了这么一个例子：

mysql> SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) am_pm_ratio
    -> FROM (SELECT COUNT(*) amc
    ->              FROM web_sales,
    ->                          household_demographics,
    ->                          time_dim,
    ->                          web_page
    ->             WHERE ws_sold_time_sk = time_dim.t_time_sk
    ->                          AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
    ->                          AND ws_web_page_sk = web_page.wp_web_page_sk
    ->                          AND time_dim.t_hour BETWEEN 9 AND 9 + 1
    ->                          AND household_demographics.hd_dep_count = 2
    ->                          AND web_page.wp_char_count BETWEEN 5000 AND 5200) at,
    ->              (SELECT COUNT(*) pmc
    ->               FROM web_sales,
    ->                          household_demographics,
    ->                          time_dim,
    ->                          web_page
    ->              WHERE ws_sold_time_sk = time_dim.t_time_sk
    ->                            AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
    ->                            AND ws_web_page_sk = web_page.wp_web_page_sk
    ->                            AND time_dim.t_hour BETWEEN 15 AND 15 + 1
    ->                            AND household_demographics.hd_dep_count = 2
    ->                            AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt
    -> ORDER BY am_pm_ratio
    -> LIMIT 100;
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048  |
+-------------+
1 row in set (1.48 sec)

这个sql是用来比较上午9点和下午15点的销售情况，可见执行时间是1.48秒。

然后给wp_char_count建一个直方图再查一遍：

mysql> ANALYZE TABLE web_page UPDATE HISTOGRAM ON wp_char_count WITH 8 BUCKETS;
+----------------+-----------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+----------------------------------------------------------+
| tpcds.web_page | histogram | status | Histogram statistics created for column 'wp_char_count'. |
+----------------+-----------+----------+----------------------------------------------------------+
1 row in set (0.06 sec)
mysql> SELECT ...
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048 |
+-------------+
1 row in set (0.50 sec)

可见查询只用了0.5秒，效率提高非常多，原因是这样的：

建直方图的字段就是sql中取值5000到5200的字段，在没有直方图提供统计信息的情况下，MySQL预估该字段值在5000到5200的大概占总比例的11.11%，但实际上此范围内的数据只占总比例的1.6%：

mysql> SELECT
    ->   (SELECT COUNT(*) FROM web_page WHERE web_page.wp_char_count BETWEEN 5000 AND 5200)
    ->   /
    ->   (SELECT COUNT(*) FROM web_page) AS ratio;
+--------+
| ratio  |
+--------+
| 0.0167 |
+--------+
1 row in set (0.00 sec)

所以，有了直方图提供的准确统计信息，优化器会调整优化策略，提前对该表进行join操作，从而提高了查询效率。

六，直方图和索引

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

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

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

七，获得直方图的信息

1，获取更新时间

官方提供了这样的例子，可以获取直方图的生成时间或更新时间：

mysql> SELECT
    ->   HISTOGRAM->>'$."last-updated"' AS last_updated
    -> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
    -> WHERE
    ->   SCHEMA_NAME = "sakila"
    ->   AND TABLE_NAME = "payment"
    ->   AND COLUMN_NAME = "amount";
+----------------------------+
| last_updated               |
+----------------------------+
| 2017-09-15 11:54:25.000000 |
+----------------------------+

2，查询直方图bucket

官方提供了这样的例子：

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME,
    ->   HISTOGRAM->>'$."number-of-buckets-specified"' AS num_buckets_specified,
    ->   JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created
    -> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
    -> WHERE
    ->   SCHEMA_NAME = "sakila";
+------------+--------------+-----------------------+---------------------+
| TABLE_NAME | COLUMN_NAME  | num_buckets_specified | num_buckets_created |
+------------+--------------+-----------------------+---------------------+
| payment    | amount       | 32                    |                  19 |
| payment    | payment_date | 32                    |                  32 |
+------------+--------------+-----------------------+---------------------+

其中，'$."number-of-buckets-specified"'是生成直方图的sql中指定的bucket数量，

JSON_LENGTH(HISTOGRAM, '$.buckets')是生成的直方图中实际的bucket数量。

实际bucket数不会超过sql中指定的bucket上限。

使用直方图时要注意的点

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

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

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

完

MySQL8.0全部学习笔记：

MySQL8.0新特性学习笔记（一）：binlog复制策略优化

MySQL8.0新特性学习笔记（二）：窗口函数

MySQL8.0新特性学习笔记（三）：直方图

MySQL8.0新特性学习笔记（四）：Hash Join

MySQL8.0新特性学习笔记（五）：JSON格式简介和JSON函数详解

MySQL8.0新特性学习笔记（六）：新特性介绍

MySQL8.0新特性学习笔记（三）：直方图

一，直方图简介

二，直方图的生成

三，直方图的删除

四，直方图的存储

五，举例说明直方图的作用

六，直方图和索引

七，获得直方图的信息

继续阅读

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

opencv——图像的灰度处理（线性变换/拉伸/直方图/均衡化）

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件