您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > MySQL只有叁个总结的总结音信却并没有直方图

MySQL只有叁个总结的总结音信却并没有直方图

发布时间:2019-12-31 20:25编辑:威尼斯官方网站登录浏览(174)

     

    直方图是表上有个别字段在安份守己一定比例和法规采样后的数据遍布的风流罗曼蒂克种描述,最关键的机能之意气风发正是基于查询条件,预估切合条件的数据量,为sql施行安顿的变动提供关键的基于
    在MySQL 8.0事前的版本中,MySQL只有多个轻巧易行的计算音讯却未有直方图,未有直方图的计算消息方可说是未有别的意义的。
    MySQL 8.0新特点之生龙活虎就是开端帮忙总括音信的直方图,那些概念很已经提议来了,抽空具体尝试了弹指间施用办法。

    前边写过MSSQL相关总结音讯的一点东西,在常理上都是千篇风姿浪漫律的,

     

    反之亦然,直接上例子,造数据,成立二个测量试验景况

    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格式的字符串,可读性并不佳。

    图片 1

    想到了sqlserver中DBCC SHOW_STATISTICS的直方图消息,如下的格式,直方图中的数据布满情状看起来十一分清晰直观

    图片 2

    于是就做了三个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]

    很明显,
    1,"2018-06-15 04:57:48.000000"和"2018-07-02 15:13:04.000000"是周围于sqlserver中央市直机关方图中的下限值与上限值
    2,0.25小于bucket的值的百分比(也就低于这些间距上节制值的比例)
    3,95311是那个间距的字段值不重复的行数。
    到结尾一个bucket,采集样板率必然是1,也正是百分百

    图片 3

    亟需小心的是,直方图的换代时间是正规时间(UTC value),并不是服务器当前岁月。
    MySQL 8.0中的直方图基本上与sqlserver的直方图生机勃勃律,皆以依照单列的取样预估,可是MySQL直方图中从未看似于sqlserver中的字段选拔性,
    但是那一个字段选取性自身意义也非常的小,sqlserver中对于复合索引,多个字段合计在一块总计,除非多个字段的同时布满的都很均匀,不然多字段索引的字段选拔性参照他事他说加以调查意义非常小。
    这也是复合索引不或者做到较为正确预估的案由。

     

    存在的疑问?

    事情未发生前写过一些MySQL总计音讯的,不过是在MySQL5.7上边,还尚无直方图的概念
    接触总括音信更新的变量依旧set global innodb_stats_on_metadata = 1;不过经测验,总计信息的直方图并不曾就此而立异。
    innodb_stats_on_metadata在MySQL5.7中国电影响到的是MySQL的目录上的总计音信,而那边纯粹是总括新闻的直方图(MySQL 8.0中直方图跟索引未有必然的关系)。
    除此以外,这里透过反复测量试验发掘,buckets的数据量,与调换直方图的效能并不曾极度醒指标涉及,如下截图,也并不知晓,buckets数量跟取样百分比有如何关系。

    图片 4

    又留意看了须臾间参谋链接的原委,发掘那样意气风发段话:

    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.

       它本人是认证索引与直方图之间的涉及的,提到直方图创设之后并不会自动更新,除非主动立异。

    唯其如此耻笑的正是,如果自个儿在有个别字段上制造了一个索引,还必要顺便在成立叁个总计消息直方图?而且这么些直方图并不会随着数据的退换自动更新,还索要手动更新。
    MySQL 8.0中会不会把总计消息和目录关联起来,可能依靠要求活动创制总括信息,假如总结消息做不到自动更新,基本上能够感觉是残缺的总计新闻了。

     

    至于变越来越直方图中时的能源的成本

    直方图的浮动是三个相比较消功耗源的历程的,如下是在三番两次测量检验成立直方图的进程中,zabbix监察和控制到的服务器的CPU使用情形,当然,这里只有观看了一下CPU使用率的主题材料。
    故而,直方图再好,真要大范围利用的使用,照旧要综合考虑衡量的,在怎么时候履行更新,以致怎么去接触它的换代。

    图片 5

    此间仅仅是通俗尝试,难免有繁多认识不足之处。

     

    一些有趣的东西

    正文最终交给的参照他事他说加以调查链接中窥见有的妙趣横生的东西
    MySQL 8.0中某个美不可言的预估量法,看来看去,跟sqlserver中的相差相当的小,都是看似大约那三种算法,算是没有主意的法子了。
    对于四个谓词结合在一齐时候的预估,可能是平素不总计消息覆盖的预估,基本上能够感到是瞎蒙的,由此上文中也关系,多个谓词结合起来的接收性,未有怎么意义。

    ------------------------------------
    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中的预测度法:


     

     

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

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:MySQL只有叁个总结的总结音信却并没有直方图

    关键词: