首页 > 编程笔记 > MySQL笔记 阅读：14

MySQL直方图的用法（非常详细）

MySQL 8.0 实现了统计直方图。利用直方图，用户可以对一张表的某一列做数据分布的统计，特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。

直方图的优点

在数据库中，查询优化器负责将 SQL 转换成最有效的执行计划。

有时候，查询优化器会找不到最优的执行计划，导致花费了更多不必要的时间。造成这种情况的主要原因是，查询优化器有时无法准确地知道以下几个问题的答案：
例如，销售表 production 包括 id、tm、count 三个字段，分别表示编号、销售时间和销售数量。

对比以下两个查询语句： 
SELECT * FROM production WHERE tm BETWEEN "22:00:00" AND "23:59:00"
SELECT * FROM production WHERE tm BETWEEN "08:00:00" AND "12:00:00"
如果销售时间大部分集中在上午 8 点到 12 点，在查询销售情况时，第一个查询语句耗费的时间会远远大于第二个查询语句。

因为没有统计数据，优化器会假设 tm 的值是均匀分配的。如何才能使查询优化器知道数据的分布情况呢？一个解决方法就是在列上建立统计直方图。

直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据。

直方图有多种形式，MySQL 支持了两种：等宽直方图（singleton）和等高直方图（equi-height）。直方图的共同点是，它们都将数据分到了一系列的 buckets 中去。MySQL 会自动将数据划到不同的 buckets 中，也会自动决定创建哪种类型的直方图。

直方图的基本操作

1) 创建直方图

创建直方图的语法格式如下： 
ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name]
buckets 的默认值是 100。统计直方图的信息存储在数据字典表 column_statistcs 中，可以通过视图 information_schema.COLUMN_STATISTICS 访问。

直方图以灵活的 JSON 格式存储。ANALYZE TABLE 会基于表大小自动判断是否要进行取样操作。ANALYZE TABLE 也会基于表中列的数据分布情况以及 bucket 的数量，来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）。

创建用于测试的数据表 production，语句如下： 
mysql> CREATE TABLE production (id int,tm TIME,count int);
在数据表 production 的字段 tm 上创建直方图，执行语句如下： 
mysql> ANALYZE TABLE production UPDATE HISTOGRAM ON tm WITH 60 BUCKETS;
+-----------------+----------+---------+---------------------------------------------+
| Table           | Op       | Msg_type| Msg_text                                    |
+-----------------+----------+---------+---------------------------------------------+
|mytest.production|histogram | status  |Histogram statistics created for column 'tm'.|
+-----------------+----------+---------+---------------------------------------------+
buckets 的值必须指定，可以设置为 1 到 1024，默认值是 100。设置 buckets 值时，可以先设置低一些，如果没有满足需求，可以再往上增大。

对于不同的数据集合，buckets 的值取决于以下几个因素：
在数据表 production 的字段 tm 和字段 count 上创建直方图，执行语句如下： 
mysql>ANALYZE TABLE production UPDATE HISTOGRAM ON tm,count WITH 60 BUCKETS;
+-------------------+----------+----------+------------------------------------------------+
| Table             | Op       | Msg_type | Msg_text                                       |
+-------------------+----------+----------+------------------------------------------------+
|mytest.production  |histogram |status    |Histogram statistics created for column 'count'.|
|mytest.production  |histogram |status    |Histogram statistics created?for column 'tm'.   |
+-------------------+----------+----------+------------------------------------------------+
再次创建直方图时，将会将上一个直方图重写。

2) 删除直方图

如果需要删除已经创建的直方图，用 DROP HISTOGRAM 就可以实现： 
mysql>ANALYZE TABLE production DROP HISTOGRAM ON tm,count;
+------------------+-----------+---------+------------------------------------------------+
| Table            | Op        | Msg_type| Msg_text                                       |
+------------------+-----------+---------+------------------------------------------------+
|mytest.production | histogram | status  |Histogram statistics removed for column 'count'.|
|mytest.production |histogram  | status  |Histogram statistics removed?for column 'tm'.   |
+------------------+-----------+---------+------------------------------------------------+
直方图统计了表中某些字段的数据分布情况，为优化选择高效的执行计划提供参考。直方图与索引有着本质的区别：维护一个索引有代价，每一次的 INSERT、UPDATE、DELETE 都会需要更新索引，会对性能有一定的影响；而直方图一次创建永不更新，除非明确去更新它，所以不会影响 INSERT、UPDATE、DELETE 的性能。

建立直方图的时候，MySQL 服务器会将所有数据读到内存中，然后在内存中进行操作，包括排序。如果对一个很大的表建立直方图，可能会需要将几百兆的数据都读到内存中。为了规避这种风险，MySQL 会根据给定的 histogram_generation_max_mem_size 的值计算该将多少行数据读到内存中。

设置 histogram_generation_max_mem_size 值的方法如下： 
mysql>SET histogram_generation_max_mem_size = 10000;

相关文章