直方图科普。
0. 什么是直方图1. 直方图怎么工作2. 同时有索引和直方图会怎样3. 如何提高直方图的统计精确度
0. 什么是直方图
直方图(Histogram)是数据库提供的一种(索引之外的)基础统计信息,用于描述列上的数据分布情况。它最典型的场景是估算查询谓词的选择率,以便选择合适的执行计划。
也就是说,某个列可以不创建索引但创建直方图,也可以帮助提升查询效率。
MySQL 8.0开始支持直方图,这是个很大的进步。
直方图可以针对某个列记录其数据分布统计信息,例如有个列的值是从1到1万,那么可以利用直方图分成100个桶(bucket),每个桶中统计这1万个值是怎么分布的,以及每个桶中的最大值、最小值、占比等信息。
虽然可以利用索引优化SQL效率,但索引维护的代价更高,索引要保持更新,而直方图可以按需手动更新。
索引统计信息也有不可靠的时候,例如存在数据倾斜,或者统计延迟等问题。
另外,在有需要的时候,可以在每个有需要的列上创建直方图,但却不太可能同时创建多个单列索引,那样代价太高了。
例如下面这个执行计划:
[root@yejr.run]> explain select * from t1 where seq = 1234;
+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL | NULL | NULL | NULL | NULL | 299876 | 10.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+
[root@yejr.run]> select * from t1 where seq = 1234;
Empty set (0.097 sec)
在还没创建直方图之前,seq列上同时也没有索引,这时是全表扫描,注意到 filtered 列的值是10%。
创建完直方图之后,再看这个执行计划:
[root@yejr.run]> explain select * from t1 where seq = 1234;
+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL | NULL | NULL | NULL | NULL | 299876 | 0.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+
[root@yejr.run]> select * from t1 where seq = 1234;
Empty set (0.079 sec)
注意到 filtered 列值变成了 0%,并且实际耗时是原来的81%,虽然绝对值也不算小,但相对于原来的全表扫描也还是要节省了将近20%耗时。
所以说,直方图还是很有意义的,当然了,直方图还是无法代替索引,只在一些特定的场景里比较有用。
1. 直方图怎么工作
MySQL支持两种直方图模式:等宽、等高。等宽直方图是每个桶保存一个值以及这个值累积频率,等高直方图每个桶需要保存不同值的个数,上下限以及累积频率等。MySQL会自动选用哪种类型的直方图,无需也不能指定。一般来说,数据数据分布范围比较大的话就采用等高,反之,如果数据分布比较小就采用等宽。
直方图的统计信息物理表 column_statistics 存储在mysql表空间中,无法直接读写,但可以访问 information_schema.COLUMN_STATISTICS 视图来查看统计结果。
[root@yejr.run]> show create view column_statistics\G
*************************** 1. row ***************************
View: COLUMN_STATISTICS
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS ...
character_set_client: utf8
collation_connection: utf8_general_ci
每条记录对应一个直方图统计结果,用JSON格式保存。
此外,还有个参数 histogram_generation_max_mem_size 用来控制创建直方图时可用的内存,该参数很重要,后面会讲到。
截止MySQL 8.0.19版本,直方图支持多种数据类型和场景,甚至包括虚拟列。但不支持以下几种情况:
加密表、临时表。
JSON数据类型、空间(spatial)数据类型。
已创建唯一索引的单列。
来个一