首页 > 编程笔记 > MySQL笔记 阅读:15

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;

相关文章