MySQL 8.0新特性之统计直方图

概览

MySQL8.0实现了统计直方图。利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。统计直方图的主要使用场景是用来计算字段选择性，即过滤效率。

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

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]; 

buckets默认是100。统计直方图的信息存储在数据字典表"column_statistcs"中,可以通过视图information_schema.COLUMN_STATISTICS访问。直方图以灵活的JSON的格式存储。ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）。

什么是直方图

数据库中，查询优化器负责将SQL转换成最有效的执行计划。有时候，查询优化器会走不到最优的执行计划，导致花费了更多不必要的时间。造成这种情况的主要原因是，查询优化器有时无法准确的知道以下几个问题的答案：

•  每个表有多少行？
•  每一列有多少不同的值？
•  每一列的数据分布情况？

举例说明：一张简单的表，两个字段，一个字段是person_id，另一个字段是time_of_day，表示睡觉时间

CREATE TABLE bedtime (  
person_id INT,  
time_of_day TIME); 

对于time_of_day列，大部分人上床时间会在晚上11:00左右。所以下面第一个查询会比第二个查询返回更多的行数：

1) SELECT * FROM bedtime WHERE time_of_day BETWEEN "22:00:00" AND "23:59:00"  
2) SELECT * FROM bedtime WHERE time_of_day BETWEEN "12:00:00" AND "14:00:00" 

如果没有统计数据，优化器会假设time_of_day的值是均匀分配的，即一个人的上床时间在下午3点和晚上11点的概率差不多。如何才能使查询优化器知道数据的分布情况？一个解决方法就是在列上建立统计直方图。

直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据。直方图有多种形式，MySQL支持了两种：等宽直方图（singleton）、等高直方图（equi-height）。直方图的共同点是，它们都将数据分到了一系列的buckets中去。MySQL会自动将数据划到不同的buckets中，也会自动决定创建哪种类型的直方图。

如何创建和删除统计直方图

为了管理统计直方图，ANALYZE TABLE命令新增了两个子句：

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> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount WITH 32 BUCKETS;  
+----------------+-----------+----------+---------------------------------------------------+  
| Table          | Op        | Msg_type | Msg_text                                         |  
+----------------+-----------+----------+---------------------------------------------------+  
| sakila.payment | histogram | status   | Histogram statistics created for column 'amount'. |  
+----------------+-----------+----------+---------------------------------------------------+  
1 row in set (0.27 sec)  
mysql> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount, payment_date WITH 32 BUCKETS;  
+----------------+-----------+----------+---------------------------------------------------------+  
| Table          | Op        | Msg_type | Msg_text                                                |  
+----------------+-----------+----------+---------------------------------------------------------+  
| sakila.payment | histogram | status   | Histogram statistics created for column 'amount'.       |  
| sakila.payment | histogram | status   | Histogram statistics created for column 'payment_date'. |  
+----------------+-----------+----------+---------------------------------------------------------+ 

buckets的值必须指定，可以设置为1到1024，默认值是100。

对于不同的数据集合，buckets的值取决于以下几个因素：

•  这列有多少不同的值
•  数据的分布情况
•  需要多高的准确性

但是，某些buckets的值能提升的关于数据分布情况的准确性相当低。所以，建议的做法是，开始的时候将buckets的值设的低一点，比如32，然后如果没有满足期望，再往上增大。

上面这个例子中，我们对于amount列建立了两次直方图。第一个语句，建立了一个新的直方图；第二个语句，amount列的直方图被重写了。

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!