中文字幕在线观看,亚洲а∨天堂久久精品9966,亚洲成a人片在线观看你懂的,亚洲av成人片无码网站,亚洲国产精品无码久久久五月天

MySQL 8.0 中統(tǒng)計(jì)信息直方圖的嘗試

2018-09-17    來源：importnew

容器云強(qiáng)勢上線！快速搭建集群，上萬Linux鏡像隨意使用

直方圖是表上某個(gè)字段在按照一定百分比和規(guī)律采樣后的數(shù)據(jù)分布的一種描述，最重要的作用之一就是根據(jù)查詢條件，預(yù)估符合條件的數(shù)據(jù)量，為sql執(zhí)行計(jì)劃的生成提供重要的依據(jù)。

在MySQL 8.0之前的版本中，MySQL僅有一個(gè)簡單的統(tǒng)計(jì)信息卻沒有直方圖，沒有直方圖的統(tǒng)計(jì)信息可以說是沒有任何意義的。

MySQL 8.0新特性之一就是開始支持統(tǒng)計(jì)信息的直方圖，這個(gè)概念很早就提出來了，抽空具體嘗試了一下使用方法。

之前寫過MSSQL相關(guān)統(tǒng)計(jì)信息的一點(diǎn)東西，在原理上都是一致的，https://www.cnblogs.com/wy123/p/5875237.html

照舊，直接上例子，造數(shù)據(jù)，創(chuàng)建一個(gè)測試環(huán)境

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中統(tǒng)計(jì)信息的創(chuàng)建，不同于MSSQL，MySQL統(tǒng)計(jì)信息不依賴于索引，需要單獨(dú)創(chuàng)建，語法如下

  • 創(chuàng)建字段上的統(tǒng)計(jì)直方圖信息

ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;

  • 刪除字段上的統(tǒng)計(jì)直方圖信息

ANALYZE TABLE test DROP HISTOGRAM ON create_date

1，可以一次性創(chuàng)建多個(gè)字段的統(tǒng)計(jì)信息，系統(tǒng)會(huì)逐個(gè)創(chuàng)建列出的字段上的統(tǒng)計(jì)信息，統(tǒng)計(jì)信息不依賴于索引，這一點(diǎn)與MSSQL不同（當(dāng)然MSSQL也可以拋開索引獨(dú)立創(chuàng)建統(tǒng)計(jì)信息）
2，BUCKETS值是一個(gè)必須提供的參數(shù)，默認(rèn)值為1000，范圍是1-1024，這一點(diǎn)也不同與MSSQL也不一樣，MSSQL是有一個(gè)類似的最大值為200的步長（step）字段
3，一般來說，數(shù)據(jù)量較大的情況下，對(duì)于不重復(fù)或者重復(fù)性不高的數(shù)據(jù)，BUCKETS值越大，描述出來的統(tǒng)計(jì)信息越詳細(xì)
4，統(tǒng)計(jì)信息的具體內(nèi)容在 information_schema.column_statistics中，但是可讀性并不好，可以根據(jù)需求自行解析（出來一種自己喜歡的格式）

與sqlserver中的統(tǒng)計(jì)信息一樣，理論上，在準(zhǔn)確性與取樣百分比（BUCKETS）是成正比的，當(dāng)然生成統(tǒng)計(jì)信息的代價(jià)也就越大，至于BUCKETS與統(tǒng)計(jì)信息的取樣百分比，以及綜合代價(jià)，筆者暫時(shí)沒有找到相關(guān)的資料。

如下是通過ANALYZE TABLE test UPDATE HISTOGRAM ON create_date WITH 4 BUCKETS;創(chuàng)建的統(tǒng)計(jì)信息直方圖
可以發(fā)現(xiàn)直方圖的HISTOGRAM字段是一個(gè)JSON格式的字符串，可讀性并不好。

想到了sqlserver中DBCC SHOW_STATISTICS的直方圖信息，如下的格式，直方圖中的數(shù)據(jù)分布情況看起來非常清晰直觀

于是就做了一個(gè)MySQL直方圖的格式轉(zhuǎn)換，說白了就是解析information_schema.column_statistics表中的HISTOGRAM 字段中的JSON內(nèi)容

如下，一個(gè)簡單的解析直方圖統(tǒng)計(jì)信息json數(shù)據(jù)的存儲(chǔ)過程,參數(shù)分別是庫名，表名，字段名

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 ;

于是，第一個(gè)截圖中的結(jié)果就轉(zhuǎn)換為了如下的格式。這里刻意按照4個(gè)buckets生成的直方圖，應(yīng)該來說足夠簡單了，熟悉MSSQL直方圖同學(xué)，應(yīng)該一眼就可以看明白這個(gè)直方圖的含義（測試數(shù)據(jù)量是400,000）以第一個(gè)bucket為例：["2018-06-15 04:57:48.000000", "2018-07-02 15:13:04.000000", 0.25, 95311]。很明顯，

1，”2018-06-15 04:57:48.000000″和”2018-07-02 15:13:04.000000″是類似于sqlserver中直方圖中的下限值與上限值
2，0.25小于bucket的值的比例（也就小于這個(gè)區(qū)間上限制值的比例）
3，95311是這個(gè)區(qū)間的字段值不重復(fù)的行數(shù)。

到最后一個(gè)bucket，采樣率必然是1，也就是100%

需要注意的是，直方圖的更新時(shí)間是標(biāo)準(zhǔn)時(shí)間（UTC value），而不是服務(wù)器當(dāng)前時(shí)間。

MySQL 8.0中的直方圖基本上與sqlserver的直方圖一致，都是基于單列的抽樣預(yù)估，但是MySQL直方圖中沒有類似于sqlserver中的字段選擇性，不過這個(gè)字段選擇性本身意義也不大 ，sqlserver中對(duì)于復(fù)合索引，兩個(gè)字段合計(jì)在一塊統(tǒng)計(jì)，除非兩個(gè)字段的同時(shí)分布的都很均勻，否則多字段索引的字段選擇性參考意義不大。

這也是復(fù)合索引無法做到較為精確預(yù)估的原因。

存在的疑問？

之前寫過一點(diǎn)MySQL統(tǒng)計(jì)信息的，不過是在MySQL5.7下面，還沒有直方圖的概念https://www.cnblogs.com/wy123/p/6561517.html
觸發(fā)統(tǒng)計(jì)信息更新的變量還是set global innodb_stats_on_metadata = 1;但是經(jīng)測試，統(tǒng)計(jì)信息的直方圖并沒有因此而更新。
innodb_stats_on_metadata在MySQL5.7中影響到的是MySQL的索引上的統(tǒng)計(jì)信息，而這里純粹是統(tǒng)計(jì)信息的直方圖（MySQL 8.0中直方圖跟索引沒有必然的關(guān)系）。

另外，這里經(jīng)過反復(fù)測試發(fā)現(xiàn)，buckets的數(shù)據(jù)量，與生成直方圖的效率并沒有非常明顯的關(guān)系，如下截圖，也并不清楚，buckets數(shù)量跟取樣百分比有什么關(guān)系。

又仔細(xì)看了一下參考鏈接的內(nèi)容，發(fā)現(xiàn)這么一段話：

  1. Maintaining an index has a cost. If you have an index, every INSERT/UPDATE/DELETE causes the index to be updated. This is not free, and will have an impact on your performance.?A histogram on the other hand is created once and never updated unless you explicitly ask for it.It will thus not hurt your INSERT/UPDATE/DELETE-performance.

它本身是說明索引與直方圖之間的關(guān)系的，提到直方圖創(chuàng)建之后并不會(huì)自動(dòng)更新，除非主動(dòng)更新。

不得不吐槽的就是，如果我在某個(gè)字段上創(chuàng)建了一個(gè)索引，還需要順便在創(chuàng)建一個(gè)統(tǒng)計(jì)信息直方圖？并且這個(gè)直方圖并不會(huì)隨著數(shù)據(jù)的變化自動(dòng)更新，還需要手動(dòng)更新。

MySQL 8.0中會(huì)不會(huì)把統(tǒng)計(jì)信息和索引關(guān)聯(lián)起來，或者根據(jù)需要自動(dòng)創(chuàng)建統(tǒng)計(jì)信息，如果統(tǒng)計(jì)信息做不到自動(dòng)更新，基本上可以認(rèn)為是殘廢的統(tǒng)計(jì)信息了。

關(guān)于生成直方圖中時(shí)的資源的消耗

直方圖的生成是一個(gè)比較消耗資源的過程的，如下是在反復(fù)測試創(chuàng)建直方圖的過程中，zabbix監(jiān)控到的服務(wù)器的CPU使用情況，當(dāng)然，這里僅僅觀察了一下CPU使用率的問題。

因此，直方圖再好，真要大規(guī)模應(yīng)用的使用，還是要綜合考量的，在什么時(shí)候執(zhí)行更新，以及怎么去觸發(fā)它的更新。

這里僅僅是粗淺嘗試，難免有很多認(rèn)識(shí)不足的地方。?

一些有意思的東西

本文最后給出的參考鏈接中發(fā)現(xiàn)一些有意思的東西。

MySQL 8.0中一些有意思的預(yù)估算法，看來看去，跟sqlserver中的差別不大，都是類似大概這幾種算法，算是沒有辦法的辦法了。

對(duì)于兩個(gè)謂詞結(jié)合在一起時(shí)候的預(yù)估，或者是沒有統(tǒng)計(jì)信息覆蓋的預(yù)估，基本上可以認(rèn)為是瞎蒙的，因此上文中也提到，多個(gè)謂詞結(jié)合起來的選擇性，沒有什么意義。

------------------------------------
AND       : P(A and B) = P(A) * P(B)
OR        : P(A or B)  = P(A) + P(B) - P(A and B)
=         : 1/10
<,>       : 1/3
BETWEEN   : 1/4
IN (list) : MIN(#items_in_list * SEL(=), 1/2)
IN subq   : [1]
NOT OP    : 1-SEL(OP)

與此類似的，sqlserver中的預(yù)估算法：

  • https://www.cnblogs.com/wy123/p/5790855.html
  • https://www.cnblogs.com/wy123/p/6770258.html
  • https://www.cnblogs.com/wy123/p/6008477.html

參考：

  • https://mysqlserverteam.com/histogram-statistics-in-mysql/
  • https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html
  • https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis

標(biāo)簽： Mysql 服務(wù)器

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。

上一篇:JDK 源碼閱讀 Reference

下一篇:十年程序員用眼告訴你 2018 PHP 不一樣