新特性解讀 | MySQL 8.0.31 匯入直方圖存量資料

愛可生雲資料庫發表於2023-01-05
MySql直方圖

作者：楊濤濤

資深資料庫專家，專研 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

相關文章