新特性解读 | MySQL 8.0.31 导入直方图存量数据
作者:杨涛涛
资深数据库专家,专研 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秒多。
<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 列值即为直方图的详细元数据。
<mysql:8.0.31:ytt>select * from information_schema.column_statistics\G
*************************** 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 里
[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多倍。
[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 的结果解读
关于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 |
相关文章
- 精通MySQL操作语句完全指南(mysql操作语句)
- MySQL存储:用图片助力数据记录(mysql存储图片)
- 妙用MySQL:存储中文数据的好办法(mysql数据库存中文)
- MySQL中判断NULL值的操作(mysql判断为null)
- MySQL导入表:从文件到数据库(mysql导入表文件)
- MySQL数据库查询性能优化实战(mysql大数据查询优化)
- MySQL Insert: Getting Started with Data Insertion(mysql的insert)
- 使用JSP连接MySQL数据库(jsp连接池mysql)
- 深入浅出MySQL宽表设计及应用(mysql宽表)
- 解析MySQL数据共享:打破信息孤岛,实现数据高效共享(mysql数据共享)
- 如何在MySQL中精准查询某一天的数据?(mysql查询某一天)
- MySQL数据库的稳定性分析和应用推荐,助力企业数据存储和管理。(mysql稳定)
- MySQL 存储过程与数据管理(mysql存储过程数据)
- 快速高效:csv数据无缝导入mysql数据库(csv数据导入mysql)
- MySQL中方括号的使用方法和注意事项(mysql方括号)
- MySQL开源协议:让世界更美好(mysql开源协议)
- MySQL中的二进制数据存储方式(mysql 二进制数据)
- 如何能快速连接MySQL数据库(如何连接mysql)
- MySQL中len函数的使用详解(mysql中len)
- C语言添加MySQL数据库实现数据操作(c 中添加mysql)
- 如何在MySQL中连接两个表(mysql两表怎么连接)
- MySQL 57 主从复制分离简介(5.7mysql主从分离)
- 简单易用MySQL一键脚本快速安装(mysql一键脚本安装)
- MySQL插入数据操作详解(mysql。insert)
- MySQL索引应对上亿行数据的优化(mysql上亿行索引)
- MySQL记录时间精确至小时,上个小时数据呈现(mysql上个小时)
- 解决MySQL数据不更新的问题(mysql不更新数据)