MySQL8.* 直方图

ANALYZE

MySQL 8.0 新功能直方图，继承于Oracle ，MairaDB的实现方式。
官方文档
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html
语法格式

  1. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  2.     TABLE tbl_name [, tbl_name] ...
  4. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  5.     TABLE tbl_name
  6.     UPDATE HISTOGRAM ON col_name [, col_name] ...
  7.         [WITH N BUCKETS]
  9. ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
  10.     TABLE tbl_name
  11.     DROP HISTOGRAM ON col_name [, col_name] ...

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

  1. mysql>SHOW INDEX FROM table_name

MySQL 8.*直方图 - 图1
执行计划里出现的Cardinality(基数)字段，是非常重要的：
基数计算是采样统计，如果把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高。采样统计的时候InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。MySQL 8.*直方图 - 图2
基数很容易不准，除了基数优化器还要判断，执行这个语句本身要扫描多少rows预计扫描行数。
因为统计信息不对，cardinality大大少于数据的实际散列程度，那么索引就有可能失效。
下面看看基数变化的情况：
1. 第一次创建表导入数MySQL 8.*直方图 - 图3
2.第二次把表drop掉，导入数据MySQL 8.*直方图 - 图4
在这种情况下就会使用analyze table 命令2次MySQL 8.*直方图 - 图5
上面是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）。MySQL 8.*直方图 - 图6
    2）两个直方图区别在于equi-height 多了 下限和上限的指标。MySQL 8.*直方图 - 图7
    3）选择直方图判断逻辑是：如果指定的桶数大于或等于桶数
    对于不同的值，创建一个单例直方图。否则创建一个等高直方图。MySQL 8.*直方图 - 图8

    直方图命令

    1)创建直方图

    1. ANALYZE TABLE employees01 UPDATE HISTOGRAM ON emp_no WITH 32 BUCKETS;

    MySQL 8.*直方图 - 图9

    2)删除直方图

    1. ANALYZE TABLE employees01 DROP HISTOGRAM ON emp_no ;

    MySQL 8.*直方图 - 图10

    3)查询直方图

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

    1. SELECT  * FROM  INFORMATION_SCHEMA.COLUMN_STATISTICS \G

    MySQL 8.*直方图 - 图11

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

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

    4)相关参数

    histogram_generation_max_mem_size：
    MySQL 8.*直方图 - 图13
    备注：用于生成直方图统计信息的最大可用内存量。

    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数据页的采样。
    计算公式如下：
    1. sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)
    通过以下方式确认INNODB_METRICS信息：
    1. mysql> SET GLOBAL innodb_monitor_enable = 'sampled%';Query OK, 0 rows affected (0.00 sec)
    2. mysql> SELECT NAME, COUNT FROM INNODB_METRICS WHERE NAME LIKE 'sampled%'\G*************************** 1. row *************************** NAME: sampled_pages_readCOUNT: 342*************************** 2. row *************************** NAME: sampled_pages_skippedCOUNT: 02 rows in set (0.00 sec)
    基于抽样计数器数据的抽样率大致与INFORMATION_SCHEMA的柱状图列中的抽样率值相同。
    测试
    1)有无直方图的情况下对比。确实性能提升很多，特别是filtered 和rows 明显有变化。MySQL 8.*直方图 - 图14
    2)OPTIMIZER_TRACE
    1. 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)
    2. 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 8.*直方图 - 图15
备注：mysql服务重新启动之后执行计划不选择直方图，走的是全表扫描，需要官方优化。

总结

按照简单测试用例，大概评估下来，直方图的性能可能提升2倍~3倍。但这对于大数量的访问而言。还有直方图因为需要额外的内存消耗，需要对环境和数据量有效的评估。MySQL 8.0 目前实现的直方图还只是提供了一些基础的功能，执行计划不准确的时候，可以大胆的尝试。