MySQL直方图的用法(非常详细)
MySQL 8.0 实现了统计直方图。利用直方图,用户可以对一张表的某一列做数据分布的统计,特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。
有时候,查询优化器会找不到最优的执行计划,导致花费了更多不必要的时间。造成这种情况的主要原因是,查询优化器有时无法准确地知道以下几个问题的答案:
例如,销售表 production 包括 id、tm、count 三个字段,分别表示编号、销售时间和销售数量。
对比以下两个查询语句:
因为没有统计数据,优化器会假设 tm 的值是均匀分配的。如何才能使查询优化器知道数据的分布情况呢?一个解决方法就是在列上建立统计直方图。
直方图能近似获得一列的数据分布情况,从而让数据库知道它含有哪些数据。
直方图有多种形式,MySQL 支持了两种:等宽直方图(singleton)和等高直方图(equi-height)。直方图的共同点是,它们都将数据分到了一系列的 buckets 中去。MySQL 会自动将数据划到不同的 buckets 中,也会自动决定创建哪种类型的直方图。
直方图以灵活的 JSON 格式存储。ANALYZE TABLE 会基于表大小自动判断是否要进行取样操作。ANALYZE TABLE 也会基于表中列的数据分布情况以及 bucket 的数量,来决定是否要建立等宽直方图(singleton)还是等高直方图(equi-height)。
创建用于测试的数据表 production,语句如下:
对于不同的数据集合,buckets 的值取决于以下几个因素:
在数据表 production 的字段 tm 和字段 count 上创建直方图,执行语句如下:
建立直方图的时候,MySQL 服务器会将所有数据读到内存中,然后在内存中进行操作,包括排序。如果对一个很大的表建立直方图,可能会需要将几百兆的数据都读到内存中。为了规避这种风险,MySQL 会根据给定的 histogram_generation_max_mem_size 的值计算该将多少行数据读到内存中。
设置 histogram_generation_max_mem_size 值的方法如下:
直方图的优点
在数据库中,查询优化器负责将 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;