MySQL 8.0 中統計資訊長條圖的嘗試

標籤：roo   fine   怎麼   zab   重複   spec   tps   根據   RoCE   

 

長條圖是表上某個欄位在按照一定百分比和規律採樣後的資料分布的一種描述，最重要的作用之一就是根據查詢條件，預估合格資料量，為sql執行計畫的產生提供重要的依據
在MySQL 8.0之前的版本中，MySQL僅有一個簡單的統計資訊卻沒有長條圖，沒有長條圖的統計資訊可以說是沒有任何意義的。
MySQL 8.0新特性之一就是開始支援統計資訊的長條圖，這個概念很早就提出來了，抽空具體嘗試了一下使用方法。

之前寫過MSSQL相關統計資訊的一點東西，在原理上都是一致的，https://www.cnblogs.com/wy123/p/5875237.html

 

照舊，直接上例子，造資料，建立一個測試環境

create table test(    id int auto_increment primary key,    name varchar(100),    create_date datetime ,    index (create_date desc));USE `db01`$$DROP PROCEDURE IF EXISTS `insert_test_data`$$CREATE DEFINER=`root`@`%` PROCEDURE `insert_test_data`()BEGIN    DECLARE v_loop INT;    SET v_loop = 100000;    WHILE v_loop>0 DO        INSERT INTO test(NAME,create_date)VALUES (UUID(),DATE_ADD(NOW(),INTERVAL -RAND()*100000 MINUTE) );        SET v_loop = v_loop - 1;    END WHILE;END$$DELIMITER ;

MySQL中統計資訊的建立，不同於MSSQL，MySQL統計資訊不依賴於索引，需要單獨建立，文法如下

--建立欄位上的統計長條圖資訊
ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;
--刪除欄位上的統計長條圖資訊
ANALYZE TABLE test DROP HISTOGRAM ON create_date

1，可以一次性建立多個欄位的統計資訊，系統會逐個建立列出的欄位上的統計資訊，統計資訊不依賴於索引，這一點與MSSQL不同（當然MSSQL也可以拋開索引獨立建立統計資訊）
2，BUCKETS值是一個必須提供的參數，預設值為1000，範圍是1-1024，這一點也不同與MSSQL也不一樣，MSSQL是有一個類似的最大值為200的步長（step）欄位
3，一般來說，資料量較大的情況下，對於不重複或者重複性不高的資料，BUCKETS值越大，描述出來的統計資訊越詳細
4，統計資訊的具體內容在 information_schema.column_statistics中，但是可讀性並不好，可以根據需求自行解析（出來一種自己喜歡的格式）

與sqlserver中的統計資訊一樣，理論上，在準確性與取樣百分比（BUCKETS）是成正比的，當然產生統計資訊的代價也就越大，
至於BUCKETS與統計資訊的取樣百分比，以及綜合代價，筆者暫時沒有找到相關的資料。

如下是通過ANALYZE TABLE test UPDATE HISTOGRAM ON create_date WITH 4 BUCKETS;建立的統計資訊長條圖
可以發現長條圖的HISTOGRAM欄位是一個JSON格式的字串，可讀性並不好。

想到了sqlserver中DBCC SHOW_STATISTICS的長條圖資訊，如下的格式，長條圖中的資料分布情況看起來非常清晰直觀

於是就做了一個MySQL長條圖的格式轉換，說白了就是解析information_schema.column_statistics表中的HISTOGRAM 欄位中的JSON內容
如下，一個簡單的解析長條圖統計資訊json資料的預存程序

DELIMITER $$USE `db01`$$DROP PROCEDURE IF EXISTS `parse_column_statistics`$$CREATE DEFINER=`root`@`%` PROCEDURE `parse_column_statistics`(    IN `p_schema_name` VARCHAR(200),    IN `p_table_name` VARCHAR(200),    IN `p_column_name` VARCHAR(200))BEGIN        DECLARE v_histogram TEXT;    -- get the special HISTOGRAM    SELECT HISTOGRAM->>‘$."buckets"‘ INTO v_HISTOGRAM     FROM   information_schema.column_statistics    WHERE schema_name =  p_schema_name     AND table_name = p_table_name     AND column_name = p_column_name;         -- remove the first and last [ and ] char    SET v_histogram = SUBSTRING(v_HISTOGRAM,2,LENGTH(v_HISTOGRAM)-2);
    DROP TABLE IF EXISTS t_buckets ;    CREATE TEMPORARY TABLE t_buckets    (        id INT AUTO_INCREMENT PRIMARY KEY,        buckets_content VARCHAR(500)    );        -- split by "]," and get single bucket content        WHILE (INSTR(v_histogram,‘],‘)>0) DO        INSERT INTO t_buckets(buckets_content)        SELECT SUBSTRING(v_histogram,1,INSTR(v_histogram,‘],‘));        SET v_HISTOGRAM = SUBSTRING(v_histogram,INSTR(v_histogram,‘],‘)+2,LENGTH(v_histogram));        END WHILE;
　　     INSERT INTO t_buckets(buckets_content)     SELECT v_histogram;        -- get the basic statistics data    WITH cte AS    (        SELECT         HISTOGRAM->>‘$."last-updated"‘ AS last_updated,        HISTOGRAM->>‘$."number-of-buckets-specified"‘ AS number_of_buckets_specified        FROM INFORMATION_SCHEMA.COLUMN_STATISTICS        WHERE schema_name =  p_schema_name         AND table_name = p_table_name         AND column_name = p_column_name    )    SELECT         CASE WHEN id = 1 THEN p_schema_name ELSE ‘‘ END AS schema_name,        CASE WHEN id = 1 THEN p_table_name ELSE ‘‘ END AS table_name,        CASE WHEN id = 1 THEN p_column_name ELSE ‘‘ END AS column_name,         CASE WHEN id = 1 THEN last_updated ELSE ‘‘ END AS last_updated,        CASE WHEN id = 1 THEN number_of_buckets_specified ELSE ‘‘ END AS ‘number_of_buckets_specified‘ ,        id AS buckets_specified_index,        buckets_content    FROM    (        SELECT * FROM cte,t_buckets    )t;END$$DELIMITER ;

於是，第一個中的結果就轉換為了如下的格式
這裡刻意按照4個buckets產生的長條圖，應該來說足夠簡單了，熟悉MSSQL長條圖同學，應該一眼就可以看明白這個長條圖的含義（測試資料量是400,000）
以第一個bucket為例，["2018-06-15 04:57:48.000000", "2018-07-02 15:13:04.000000", 0.25, 95311]
很明顯，"2018-06-15 04:57:48.000000"和"2018-07-02 15:13:04.000000"是類似於sqlserver中長條圖中的下限值與上限值，0.25是長條圖的採樣率25%，95311沒猜錯的話應該是這個區間的行數。
到最後一個bucket，採樣率必然是1，也就是100%

需要注意的是，長條圖的更新時間是標準時間，而不是伺服器目前時間。
需要注意的是，MySQL 8.0中的長條圖基本上與sqlserver的長條圖一致，都是基於單列的抽樣預估，但是MySQL長條圖中沒有類似於sqlserver中的欄位選擇性，
不過這個欄位選擇性本身意義也不大 ，sqlserver中對於複合索引，兩個欄位合計在一塊統計，除非兩個欄位的同時分布的都很均勻，否則多欄位索引的欄位選擇性參考意義不大。
這也是符合索引無法做到較為精確預估的原因。

 

存在的疑問？

之前寫過一點MySQL統計資訊的，不過是在MySQL5.7下面，還沒有長條圖的概念https://www.cnblogs.com/wy123/p/6561517.html
觸發統計資訊更新的變數還是set global innodb_stats_on_metadata = 1;但是經測試，統計資訊的長條圖並沒有因此而更新。
innodb_stats_on_metadata在MySQL5.7中影響到的是MySQL的索引上的統計資訊，而這裡純粹是統計資訊的長條圖（MySQL 8.0中長條圖跟索引沒有必然的關係）。
另外，這裡經過反覆測試發現，buckets的資料量，與產生長條圖的效率並沒有非常明顯的關係，如下，也並不清楚，buckets數量跟取樣百分比有什麼關係。

 

 關於產生長條圖中時的資源的消耗

長條圖的產生是一個比較消耗資源的過程的，如下是在反覆測試建立長條圖的過程中，zabbix監控到的伺服器的CPU使用方式，當然，這裡僅僅觀察了一下CPU使用率的問題。
因此，長條圖再好，真要大規模應用的使用，還是要綜合考量的，在什麼時候執行更新，以及怎麼去觸發它的更新。

 

 

這裡僅僅是粗淺嘗試，難免有很多認識不足的地方。

 

參考：https://mysqlserverteam.com/histogram-statistics-in-mysql/

MySQL 8.0 中統計資訊長條圖的嘗試