墨墨导读: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(基数)字段,是非常重要的: 因为统计信息不对,cardinality大大少于数据的实际散列程度,那么索引就有可能失效。 下面看看基数变化的情况: 1. 第一次创建表导入数 2.第二次把表drop掉,导入数据 在这种情况下就会使用analyze table 命令2次 上面是2次,ANALYZE TABLE命令进行修复索引。在不停的浮动。 分析表通过对每个索引树执行随机潜水并相应地更新索引基数估计值来确定索引基数,所以这个值也不是100%准确。 ANALYZE TABLE的作用:
ANALYZE TABLE风险:
直方图 通过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)查询命令 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)相关参数 备注:用于生成直方图统计信息的最大可用内存量。 5)注意事项 直方图限制:
直方图受DDL语句的影响:
其他: MySQL 8.0.19中,InnoDB存储引擎为存储在InnoDB表中的数据提供了自己的采样实现。当存储引擎不提供自己的存储引擎时,MySQL使用的默认采样实现需要全表扫描,这对于大型表来说代价很高。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; 备注:mysql服务重新启动之后执行计划不选择直方图,走的是全表扫描,需要官方优化。 总结 按照简单测试用例,大概评估下来,直方图的性能可能提升2倍~3倍。但这对于大数量的访问而言。还有直方图因为需要额外的内存消耗,需要对环境和数据量有效的评估。MySQL 8.0 目前实现的直方图还只是提供了一些基础的功能,执行计划不准确的时候,可以大胆的尝试。 希望官方继续完善。 墨天轮原文链接:https://www./db/27403(复制到浏览器中打开或者点击“阅读原文”) |
|