深入理解MySQL8.0直方图

数据和云

发布于 2020-07-10 18:05:54

1.3K0

发布于 2020-07-10 18:05:54

文章被收录于专栏：数据和云数据和云

墨墨导读：MySQL 8.0 新功能直方图，继承于Oracle ，MairaDB的实现方式。本文从MySQL角度解释，直方图是什么。

MySQL 8.0 新功能直方图，继承于Oracle ，MairaDB的实现方式。

那下面从MySQL角度认识下，直方图是什么。先看下官方直方图的实现方式。

从上图上可以看到原来是ANALYZE命令。先了解一下MySQL里 ANALYZE命令到底有什么用。

ANALYZE

在MySQL里提交一条查询SQL语句时，优化器会选在一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着资源资源越少，扫描行数是怎么判断，是通过索引的基数来得到值和row。

mysql>SHOW INDEX FROM table_name

执行计划里出现的Cardinality(基数)字段，是非常重要的：基数计算是采样统计，如果把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高。采样统计的时候InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

基数很容易不准，除了基数优化器还要判断，执行这个语句本身要扫描多少rows预计扫描行数。

因为统计信息不对，cardinality大大少于数据的实际散列程度，那么索引就有可能失效。

下面看看基数变化的情况：

1. 第一次创建表导入数

2.第二次把表drop掉，导入数据

在这种情况下就会使用analyze table 命令2次

上面是2次，ANALYZE TABLE命令进行修复索引。在不停的浮动。

分析表通过对每个索引树执行随机潜水并相应地更新索引基数估计值来确定索引基数,所以这个值也不是100%准确。

ANALYZE TABLE的作用：

统计索引分布信息。
对于 MyISAM 表，相当于执行了一次 myisamchk --analyze
支持 InnoDB.NDB.MyISAM 等存储引擎，但不支持视图（view）
执行时，会对表加上读锁（read lock）
该操作会记录binlog，可以在analyze和table之间添加关键字local取消写入

ANALYZE TABLE风险：

analyze table的需要扫描的page代价粗略估算公式：sample_pages * 索引数 * 表分区数。
因此，索引数量较多，或者表分区数量较多时，可能会比较费时，要评估代价，并默认只在负载低谷时执行。
如果某个表上当前有慢SQL，此时执行analyze table，则该表后续的查询均会处于waiting for table flush的状态，严重的话会影响业务，因此执行前必须先检查有无慢查询。

直方图

通过ANALYZE操作了解到，在数据库中查询优化所需的指标抽取方式。有时候，查询优化器会走不到最优的执行计划，导致花费了更多不必要的时间。直方图就是解决这样的问题.

直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据。将数据分到了一系列的buckets中去。MySQL会自动将数据划到不同的buckets中，也会自动决定创建哪种类型的直方图。

1）从代码实现中，MySQL支持了两种：等宽直方图（singleton）.等高直方图（equi-height）。

2）两个直方图区别在于equi-height 多了下限和上限的指标。

3）选择直方图判断逻辑是：如果指定的桶数大于或等于桶数

对于不同的值，创建一个单例直方图。否则创建一个等高直方图。

直方图命令

1)创建命令

ANALYZE TABLE employees01 UPDATE HISTOGRAM ON emp_no WITH 32 BUCKETS;

2)删除命令

ANALYZE TABLE employees01 DROP HISTOGRAM ON emp_no ;

3)查询命令统计直方图的信息存储在数据字典表"column_statistcs"中,可以通过视图information_schema.COLUMN_STATISTICS访问,直方图以灵活的JSON的格式存储，buckets方式保存，默认是100。

SELECT  * FROM  INFORMATION_SCHEMA.COLUMN_STATISTICS \G

SELECT HISTOGRAM->>'$."sampling-rate"'     FROM INFORMATION_SCHEMA.COLUMN_STATISTICS        WHERE TABLE_NAME = "employees01"        AND COLUMN_NAME = "emp_no";

采样率值为1意味着来自emp_no列的大约100%的数据被读入内存以生成直方图统计信息。

4)相关参数 histogram_generation_max_mem_size：

备注：用于生成直方图统计信息的最大可用内存量。

5)注意事项

直方图限制：

加密表(为了避免在统计数据中暴露数据)或临时表不支持生成直方图。
直方图生成适用于除几何类型(空间数据)和JSON之外的所有数据类型的列。
可以为存储的和虚拟生成的列生成直方图。
不能为单列惟一索引所覆盖的列生成直方图。
分析表可以使用InnoDB.NDB和MyISAM表
innodb_read_only 需要关闭
information_schema_stats_expiry系统变量定义缓存表统计信息过期之前的时间段
分析表支持分区表
分析表从表定义缓存中删除需要刷新锁的表。如果有长时间运行的语句或事务仍然使用该表，则后续的语句和事务必须等待这些操作完成，然后才能释放刷新锁。

直方图受DDL语句的影响：

删除被删除表中列的直方图。
DROP DATABASE删除被删除数据库中任何表的直方图，因为该语句删除了数据库中的所有表。
重命名表不会移除直方图。相反，它将重命名重命名表的直方图，使之与新表名相关联。
删除或修改列的ALTER TABLE语句删除该列的直方图。
ALTER TABLE……转换为字符集将删除字符列的直方图，因为它们会受到字符集更改的影响。非字符列的直方图不受影响。

其他：

MySQL 8.0.19中，InnoDB存储引擎为存储在InnoDB表中的数据提供了自己的采样实现。当存储引擎不提供自己的存储引擎时，MySQL使用的默认采样实现需要全表扫描，这对于大型表来说代价很高。InnoDB抽样实现通过避免全表扫描提高了抽样性能。 sampled_pages_read和sampled_pages_skip INNODB_METRICS计数器可用于监视InnoDB数据页的采样。

计算公式如下：

sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

通过以下方式确认INNODB_METRICS信息：

mysql> SET GLOBAL innodb_monitor_enable = 'sampled%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, COUNT FROM INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
 NAME: sampled_pages_read
COUNT: 342
*************************** 2. row ***************************
 NAME: sampled_pages_skipped
COUNT: 0
2 rows in set (0.00 sec)

基于抽样计数器数据的抽样率大致与INFORMATION_SCHEMA的柱状图列中的抽样率值相同。

测试

1)有无直方图的情况下对比。确实性能提升很多，特别是filtered 和rows 明显有变化。

2)OPTIMIZER_TRACE

mysql> SET OPTIMIZER_TRACE = "enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from employees where birth_date>'1964-02-01';
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_dt_birth  | idx_dt_birth | 3       | NULL | 44288 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
mysql> SELECT *  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;