新特性解读 | MySQL 8.0.31 导入直方图存量数据

2022-12-21 21:44:11 浏览数 (1)

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源：原创投稿

* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

MySQL 8.0 已经发布了好几年，对于直方图这个老概念想必大家已经熟知，今天介绍一个 MySQL 最新小版本8.0.31带来的新特性：存量直方图数据导入！

存量直方图数据导入的新语法为：analyze table 表名 update histogram on 列名1（，列名N） using data '存量数据'。

MySQL 直方图的更新需要耗费大量时间，一般由具体列的数据分布状态而定。比如下面对表t1（数据量1000W条）的c1列建立直方图：用时5秒多。

代码语言：javascript复制
<mysql:8.0.31:ytt>analyze table t1 update histogram on c1 with 1000 buckets;
 -------- ----------- ---------- ----------------------------------------------- 
| Table  | Op        | Msg_type | Msg_text                                      |
 -------- ----------- ---------- ----------------------------------------------- 
| ytt.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
 -------- ----------- ---------- ----------------------------------------------- 
1 row in set (5.34 sec)

给列建立好直方图后，MySQL 把直方图元数据保存在表 information_schema.column_statistics 中：这张表的 histogram 列值即为直方图的详细元数据。

代码语言：javascript复制
<mysql:8.0.31:ytt>select * from information_schema.column_statisticsG
*************************** 1. row ***************************
SCHEMA_NAME: ytt
 TABLE_NAME: t1
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [[1, 0.09946735110669537], [2, 0.20023182646133467], [3, 0.2998288899928244], [4, 0.40027598388254126], [5, 0.4996605398244742], [6, 0.5989015841474857], [7, 0.6994176740078379], [8, 0.7998868466081581], [9, 0.8999503229011425], [10, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-12-19 07:37:53.960993", "sampling-rate": 0.0370089475200097, "histogram-type": "singleton", "number-of-buckets-specified": 1000}
1 row in set (0.00 sec)
一般来讲，以下两种场景会再次更新直方图数据：
第一：如果后期对表t1进行过于频繁的 DML 操作，数据会较之前有许多新的变更。特别是对于列c1，原先的数值范围为1-10，大量更新后 ，数据范围变为1-20；或者说大量更新后，列c1的数值范围还是1-10，不过每个数值的分布范围发生变化。对于这种情况，就得按需手动进行直方图的更新，再次执行对应 SQL 语句。
第二：表列c1值没变化，但是 DBA 不小心删除了列c1上的直方图数据，恰好此时数据库并发又很大，不敢随意再次添加列c1的直方图数据。

以上这两种情况，刚好适合 MySQL 8.0.31 最新小版本带来的存量直方图数据导入功能！

为了减少数据库端的计算压力，需要提前在外部预先计算好直方图数据，并且定义好格式。比如新的直方图数据存放在文件 histogram_new.txt 里

代码语言：javascript复制
[root@ytt-pc tmp]# cat histogram_new.txt
{"buckets": [[1, 0.04993815708101423], [2, 0.09973691413972445], [3, 0.14968014031245883], [4, 0.20004410109796528], [5, 0.24956405811206747], [6, 0.2990662733051492], [7, 0.34892585946450116], [8, 0.3988995001875564], [9, 0.44909871549215813], [10, 0.49972373450125207], [11, 0.5504704117116295], [12, 0.5998915214371889], [13, 0.6500425803704493], [14, 0.7008450175897483], [15, 0.7506589819236189], [16, 0.8002727171345438], [17, 0.8503325324168416], [18, 0.9005951113679451], [19, 0.9498666828877602], [20, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-12-19 07:57:02.133738", "sampling-rate": 0.0370089475200097, "histogram-type": "singleton", "number-of-buckets-specified": 1000}

提前计算好直方图数据后，就可以使用最新版本的存量数据导入功能：执行时间只有0.03秒，比在线添加直方图快100多倍。

代码语言：javascript复制
[root@ytt-pc tmp]# mysql -uroot -p -D ytt -vv -e "analyze table t1 update histogram on c1 using data '`cat histogram_new.txt`'";

Enter password:

analyze table t1 update histogram on c1 using data '{"buckets": [[1, 0.04993815708101423], [2, 0.09973691413972445], [3, 0.14968014031245883], [4, 0.20004410109796528], [5, 0.24956405811206747], [6, 0.2990662733051492], [7, 0.34892585946450116], [8, 0.3988995001875564], [9, 0.44909871549215813], [10, 0.49972373450125207], [11, 0.5504704117116295], [12, 0.5998915214371889], [13, 0.6500425803704493], [14, 0.7008450175897483], [15, 0.7506589819236189], [16, 0.8002727171345438], [17, 0.8503325324168416], [18, 0.9005951113679451], [19, 0.9498666828877602], [20, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-12-19 07:57:02.133738", "sampling-rate": 0.0370089475200097, "histogram-type": "singleton", "number-of-buckets-specified": 1000}'
--------------

 -------- ----------- ---------- ----------------------------------------------- 
| Table  | Op        | Msg_type | Msg_text                                      |
 -------- ----------- ---------- ----------------------------------------------- 
| ytt.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
 -------- ----------- ---------- ----------------------------------------------- 
1 row in set (0.03 sec)

Bye

本文关键字：#直方图# #MySQL 8.0.31#

文章推荐：

使用 SQL 语句来简化 show engine innodb status 的结果解读

OceanBase 在 Ubuntu 平台部署

MySQL Shell 运行 SQL 的两种内置方法概述

关于SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

SQLE 获取

类型

地址

版本库

https://github.com/actiontech/sqle

文档

https://actiontech.github.io/sqle-docs-cn/

发布信息

https://github.com/actiontech/sqle/releases

数据审核插件开发文档

https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_development.html

云数据库SQLServer 数据库 sql https github

0 人点赞