MySQL统计直方图的基本操作（附带实例）

MySQL 9.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）：

• 等宽直方图：指每个 bucket 的宽度相等。可以用来了解数据的范围和分布。
• 等高直方图：指每个 bucket 的高度相等。可以用来了解数据的数量分布。

MySQL 会自动将数据划分到不同的 bucket 中，也会根据数据情况自动决定创建哪种类型的直方图。当需要关注数据的范围分布时，可以使用等宽直方图；当需要关注数据的数量分布时，可以使用等高直方图。

MySQL直方图的基本操作

创建直方图的语法格式如下：

ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON  clo_name]

buckets 的值必须指定，可以设置为 1~1024，默认值是 100。统计直方图的信息存储在数据字典表 column_statistcs 中，可以通过视图 information_schema.COLUMN_STATISTICS 访问。直方图以灵活的 JSON 格式存储。

ANALYZE TABLE 会基于表的大小自动判断是否要进行取样操作，也会基于表中列的数据分布情况以及 bucket 的数量，来决定是建立等宽直方图还是建立等高直方图。

【实例】创建直方图。创建用于测试的数据表 production，SQL 语句如下：

mysql> CREATE TABLE production (id int,tm TIME,count int);

在数据表 production 的字段 tm 上创建直方图，SQL 语句如下：

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 值时，可以先设置得低一些，如果没有满足需求，再增大。

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

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

在数据表 production 的字段 tm 和字段 count 上创建直方图，SQL 语句如下：

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'.   |
+-----------------+---------+---------+------------------------------------------------+

再次创建直方图时，将会将上一个直方图重写。

如果需要删除已经创建的直方图，用 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'.   |
+-----------------+----------+---------+------------------------------------------------+

直方图统计了表中某些字段的数据分布情况，为选择高效的执行计划提供了参考。直方图与索引有着本质的区别：维护一个索引有代价，每一次的插入、更新、删除都需要更新索引，这会对性能有一定的影响；而直方图一次创建无须更新，除非明确去更新它，所以不会影响插入、更新、删除的性能。

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

设置 histogram_generation_max_mem_size 值的方法如下：

mysql>SET histogram_generation_max_mem_size = 10000;