MySQL 8.0.31新特性之导入直方图存量数据
👉 本文共约791个字，系统预计阅读时间或需3分钟。
MySQL 8.0 已经发布了好几年，对于直方图这个老概念想必大家已经熟知，今天介绍一个 MySQL 最新小版本8.0.31带来的新特性：存量直方图数据导入！
存量直方图数据导入的新语法为：analyze table 表名 update histogram on 列名1（，列名N） using data '存量数据'。
MySQL 直方图的更新需要耗费大量时间，一般由具体列的数据分布状态而定。比如下面对表t1（数据量1000W条）的c1列建立直方图：用时5秒多。
1 2 3 4 5 6 7 | <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 列值即为直方图的详细元数据。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 | <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 里
1 2 | [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多倍。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [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 |
