目录
一,直方图简介
直方图,histogram,直译就是柱状图,国内多翻译为直方图。
直方图不是图,是对表的某个字段数据分布的统计,这种数据统计是基于某字段的实际存储数据分布得出的。
这种分布统计可以帮助查询优化器掌握字段真实的情况,从而得出更高效而切合实际的执行计划。
在有直方图之前,优化器对某字段的数据分布预估有可能是不准确的,甚至可能和实际情况差距比较大,错误的预估会导致不高效的执行计划。
从Explain sql的角度看,建立直方图后会影响Explain结果的filter字段,使之成为较为真实的比例,从而影响执行计划的选择。
直方图分为单值直方图(singleton histograms)和等高直方图(equi-height histograms)。
不知道为什么国内多把singleton histograms翻译成等宽直方图。
二,直方图的生成
生成直方图的sql语句是这样的:
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
可以看到,可以同时对多个字段生成直方图。
BUCKETS是统计结果的分组个数,如果不写则默认100,可选值从1到1024。数字越大统计结果越精确,官方建议从小值慢慢增加到可以满足预期的大小。
直方图生成的时候,MySQL会把所有相关数据读入内存中,然后进行分组统计。
8.0新增的参数histogram_generation_max_mem_size定义了直方图能用多大的内存。
如果字段值超过限定内存,则会取样,样本随机,也就是说两次生成的直方图内容可能不一样。
官方例子:
mysql> SELECT histogram->>'$."sampling-rate"'
-> FROM information_schema.column_statistics
-> WHERE table_name = "customer"
-> AND column_name = "c_birth_country";
+---------------------------------+
| histogram->>'$."sampling-rate"' |
+---------------------------------+
| 0.048743243211626014 |
+---------------------------------+
1 row in set (0.00 sec)
sampling-rate是查询直方图取样比例用的,这次的查询结果代表此字段的直方图是大概取样4.8%得到的。
一个字段只能生成一个直方图,重复生成会把前者覆盖。
生成直方图的sql中如果有不存在的字段,MySQL依然会为sql中存在的字段生成直方图。
生成直方图时,如果字段的distinct值小于sql指定的bucket,MySQL会选择生成单值直方图(singleton histograms),否则生成等高直方图(equi-height histograms)。
三,直方图的删除
sql如下:
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];
随时可以删,不影响数据库其他操作。
四,直方图的存储
生成的直方图保存在information_schema.COLUMN_STATISTICS表中,内容是JSON格式。
下面说一下JSON的格式,单值直方图和等高直方图的JSON格式略有不同。
以下是一个单值直方图的JSON例子:
{
"buckets": [
[
1,
0.3333333333333333
],
[
2,
0.6666666666666666
],
[
3,
1
]
],
"null-values": 0,
"last-updated": "2017-03-24 13:32:40.000000",
"sampling-rate": 1,
"histogram-type": "singleton",
"number-of-buckets-specified": 128,
"data-type": "int",
"collation-id": 8
}
以下是一个等高直方图的例子:
{
"buckets": [
[
38671,
99756,
0.249795,
17002
],
[
99757,
100248,
0.500035,
492
],
[
100249,
100743,
0.749945,
495
],
[
100744,
172775,
1.0,
16630
]
],
"data-type": "int",
"null-values": 0.0,
"collation-id": 8,
"last-updated": "2018-09-22 09:59:30.857797",
"sampling-rate": 1.0,
"histogram-type": "equi-height",
"number-of-buckets-specified": 4
}
二者在buckets参数中存储的内容不一样,其他部分的格式是一样的。
在单值直方图的buckets中,有2个值:
- 第1个值:bucket代表的值。类型由字段类型决定。
- 第2个值:自己的bucket和前面的bucket中的值的占比之和,是一个逐渐增加的值。取值在0.0到1.0之间。显然最后一个bucket中比例一定是1。
在等高直方图的buckets中,有4个值:
- 第1个值:bucket包括的值的范围的下限。
- 第2个值:bucket包括的值的范围的上限。
- 第3个值:自己bucket取值范围和前面bucket取值范围中的值的占比之和,是一个逐渐增加的值。取值在0.0到1.0之间。同单值直方图的第2个值,最后一个bucket的比例是1。
- 第4个值:bucket包括的值的distinct数。
在buckets之外的参数:
- null-values:null值比例。取值在0.0到1.0之间。0代表没有null值。
- last-updated:直方图更新时间。格式:YYYY-MM-DD hh:mm:ss.uuuuuu。
- sampling-rate:取样比例。取值在0.0到1.0之间。1.0代表bucket值完全覆盖字段值,没有取样。
- histogram-type:直方图类型。singleton代表单值直方图,每个bucket都代表一个值,字段distinct值小于sql指定bucket数时使用该类型。equi-height代表等高直方图,每个bucket都代表一个值的范围,字段distinct值大于sql指定bucket数时使用该类型。
- number-of-buckets-specified:生成直方图的sql语句中指定的bucket数量。
- data-type:直方图中的数据类型。从磁盘中读取并存入内存中时会用到。可能的值有:int,uint (unsigned integer),double,decimal,datetime,string (包括character and binary strings)。
- collation-id:直方图数据中的字符集id。和INFORMATION_SCHEMA.COLLATIONS表中id对应。直方图中的数据是String类型时会有用。
直方图中的值可以用MySQL8.0加入的JSON语法获得。
五,举例说明直方图的作用
官方举了这么一个例子:
mysql> SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) am_pm_ratio
-> FROM (SELECT COUNT(*) amc
-> FROM web_sales,
-> household_demographics,
-> time_dim,
-> web_page
-> WHERE ws_sold_time_sk = time_dim.t_time_sk
-> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
-> AND ws_web_page_sk = web_page.wp_web_page_sk
-> AND time_dim.t_hour BETWEEN 9 AND 9 + 1
-> AND household_demographics.hd_dep_count = 2
-> AND web_page.wp_char_count BETWEEN 5000 AND 5200) at,
-> (SELECT COUNT(*) pmc
-> FROM web_sales,
-> household_demographics,
-> time_dim,
-> web_page
-> WHERE ws_sold_time_sk = time_dim.t_time_sk
-> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
-> AND ws_web_page_sk = web_page.wp_web_page_sk
-> AND time_dim.t_hour BETWEEN 15 AND 15 + 1
-> AND household_demographics.hd_dep_count = 2
-> AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt
-> ORDER BY am_pm_ratio
-> LIMIT 100;
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048 |
+-------------+
1 row in set (1.48 sec)
这个sql是用来比较上午9点和下午15点的销售情况,可见执行时间是1.48秒。
然后给wp_char_count建一个直方图再查一遍:
mysql> ANALYZE TABLE web_page UPDATE HISTOGRAM ON wp_char_count WITH 8 BUCKETS;
+----------------+-----------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+----------------------------------------------------------+
| tpcds.web_page | histogram | status | Histogram statistics created for column 'wp_char_count'. |
+----------------+-----------+----------+----------------------------------------------------------+
1 row in set (0.06 sec)
mysql> SELECT ...
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048 |
+-------------+
1 row in set (0.50 sec)
可见查询只用了0.5秒,效率提高非常多,原因是这样的:
建直方图的字段就是sql中取值5000到5200的字段,在没有直方图提供统计信息的情况下,MySQL预估该字段值在5000到5200的大概占总比例的11.11%,但实际上此范围内的数据只占总比例的1.6%:
mysql> SELECT
-> (SELECT COUNT(*) FROM web_page WHERE web_page.wp_char_count BETWEEN 5000 AND 5200)
-> /
-> (SELECT COUNT(*) FROM web_page) AS ratio;
+--------+
| ratio |
+--------+
| 0.0167 |
+--------+
1 row in set (0.00 sec)
所以,有了直方图提供的准确统计信息,优化器会调整优化策略,提前对该表进行join操作,从而提高了查询效率。
六,直方图和索引
使用索引也可以把查询效率提高不少,效果不比直方图差,二者的区别有:
1,维护索引是有成本的。新增,删除,修改字段时同时需要维护索引,影响效率。对比之下直方图一经建立就不再修改,不会影响操作效率(显然是把双刃剑,会存在数据不准确的情况)。
2,使用索引时,优化器使用index dives的方式预估大概范围,这个操作也是有成本的。特别是in语句且列表有大量值时,直方图的效果更强一点。
七,获得直方图的信息
1,获取更新时间
官方提供了这样的例子,可以获取直方图的生成时间或更新时间:
mysql> SELECT
-> HISTOGRAM->>'$."last-updated"' AS last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE
-> SCHEMA_NAME = "sakila"
-> AND TABLE_NAME = "payment"
-> AND COLUMN_NAME = "amount";
+----------------------------+
| last_updated |
+----------------------------+
| 2017-09-15 11:54:25.000000 |
+----------------------------+
2,查询直方图bucket
官方提供了这样的例子:
mysql> SELECT
-> TABLE_NAME,
-> COLUMN_NAME,
-> HISTOGRAM->>'$."number-of-buckets-specified"' AS num_buckets_specified,
-> JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE
-> SCHEMA_NAME = "sakila";
+------------+--------------+-----------------------+---------------------+
| TABLE_NAME | COLUMN_NAME | num_buckets_specified | num_buckets_created |
+------------+--------------+-----------------------+---------------------+
| payment | amount | 32 | 19 |
| payment | payment_date | 32 | 32 |
+------------+--------------+-----------------------+---------------------+
其中,'$."number-of-buckets-specified"'是生成直方图的sql中指定的bucket数量,
JSON_LENGTH(HISTOGRAM, '$.buckets')是生成的直方图中实际的bucket数量。
实际bucket数不会超过sql中指定的bucket上限。
使用直方图时要注意的点
通过前面直方图和索引的比较可以看到,直方图自生成之后就不再随数据变化而改变,除非手动重做直方图。
这样带来的数据不准确的问题是需要开发者自己考虑的。比如什么时候需要刷新直方图,以频率刷新等。
因为生成直方图消耗的cpu和内存资源都挺大,使用的时候要注意。
完
MySQL8.0全部学习笔记:
MySQL8.0新特性学习笔记(一):binlog复制策略优化