MySQL统计信息以及预估方式初探,mysql统计信息初探澳门新葡亰赌995577

澳门新葡亰赌995577 6

 

MySQL统计信息以及预估方式初探,mysql统计信息初探

 

数据库中的统计信息在不同(精确)程度上描述了表中数据的分布情况,执行计划通过统计信息获取符合查询条件的数据大小(行数),来指导执行计划的生成。
在以Oracle和SQLServer为代表的商业数据库,和以开源的PostgreSQL为代表的数据库中,直方图是统计信息的一个重要组成部分。
在生成执行计划的时候,通过统计信息以及统计信息的直方图来预估符合条件的数据行数,从而影响执行计划的生成。
统计信息对执行计划的影响,具体体现在:索引的查找与扫描,多表连接时表之间的驱动顺序,表之间的JOIN方式,以及对sql查询语句的资源分配等等。
但是在MySQL数据库中,执行计划的方式相对简单,表之间的JOIN只有LOOPJOIN一种方式,且没有并行执行计划等,也就说通过预估结果集的行数对执行计划的影响有限。
但是对于某些情况,依旧需要预估的方式来指导执行计划的生成,
比如常见的多表连接时驱动顺序,多数情况下是小表驱动大表(不完全一定)的方式来实现查询的,因此MySQL中一样需要预估来指导执行计划的生成。

不过MySQL中的统计信息只有一个cardinality信息来预估索引的选择性(show
index from
table),并不包含直方图的信息,也就是无法通过直方图来预估查询数据的大小,mysql是通过其他方式来实现预估的。
对于有直方图的数据来说,直方图为预估提供了重要的依据,对于没有直方图的MySQL,执行计划是如何预估的?预估的准确性有如何?
笔者在研究这个问题的时候,一开始也遇到不少疑惑的地方,还是看了博客园大神的问题才得以释惑,后面会给出链接。

 

首先通过例子,通过一个非常简单的查询来观察一个有意思的现象。

新建测试表,测试表如下:

create table test_statistics
(
    id int auto_increment primary key,
    col2 varchar(200),
    col3 varchar(200),
    create_date datetime,
    index idx_create_date(create_date)
)ENGINE=InnoDB;

存储过程通过循环插入数据,调用存储过程生成100W行数据(100W行的数据,在实际应用中已经是一个非常小的数据量了),create_date字段上生成一个范围之内的随机时间。

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_test_data`(
    IN `loop_count` INT
)
BEGIN
    declare i int;
    while (loop_count>0) 
    do    
        insert into test_statistics(col2,col3,create_date) values (uuid(),uuid(), DATE_ADD(sysdate(), INTERVAL  -rand()*2400  hour));
        set loop_count = loop_count -1;
    end while;
END

写入测试数据完成之后,进行如下两个查询做测试。

简单地使用select count(1)的来做测试
首先看第一个查询:查询的时间范围是: where create_date>’2017-11-01
12:00:00′ and create_date<‘2017-11-01 16:00:00’
可以发现:explain预估的行数,与实际行数完全一致。

澳门新葡亰赌995577 1

继续第二个查询,扩大查询的时间范围,查询的时间范围是:where
create_date>’2017-11-01 12:00:00′ and create_date<‘2017-11-03
16:00:00’
可以发现,此时的explain执行计划的预估,与实际行数出现了严重的偏差

澳门新葡亰赌995577 2

为什么第一个查询做到了精确的预估,而第二个查询的预估出现严重的偏差?

这一点要从预估的计算方式入手来说。

首先,第一个查询和第二个查询,唯一的不同是,第二个查询的时间范围放宽了,为什么时间放宽之后,执行计划的预估的准确性就大大下降?
其他数据库的预估是通过直方图获取的,统计信息中包含的直方图中的准确性,就决定了预估的准确性。
既然是“预估”,就一定是存在误差,只不过是误差大与小的问题,误差的大下与预估的方式有关。
任何预估的实现,都是以一种在不同程度上“以偏概全”的方式进行的,比如SQL
Server是以对相关数据page的通过某种百分比来取样,然后存储在直方图中做预估依据的。
当然,这种“以偏概全”的预估方式,是在性能与精确度之间权衡折中的结果,在考虑收集统计信息对性能和资源影响的前提下,预估策略各种方式或者代价尽可能减少对预估产生误差的因素。
而MySQL是在查询的时候,直接是以查询条件范围内的数据页做统计之后预估的,但是取样的数据页面有一定的限制,不会无限制取样做统计预估。
如果符合条件的数据页超出了预定的范围,则会取部分页进行预估,而不是全部页(为什么不是全部样做统计预估,原因就不用说了吧)。

 

比如下图中,不管是聚集索引还是二级索引(非聚集索引),理论上说都是一颗平衡树,暂不探究其细节。
假如符合条件的数据是一个范围,位于两个矩形框之间。矩形框分别是范围的左右节点,中间可以想象成多个叶子节点
参考zhanlijun大神的文章,
上述参考链接中得知,MySQL在5.5之后的预估原理如下:
其预估扫描的数据页分别是前后两个数据页,以及从左边开始连续8个数据页,得到平均每个page的行数,根据总的page个数预估出这个范围的数据行数。
具体说,也就是取左右两个叶子节点,以及从左叶子节点开始连续8个页的数据做统计,中间可能有多个数据页,但也会被忽略,这就是上面提到的“以偏概全”的方式。
这里面就存在一个最明显的问题,也就是符合条件的数据页面与预估时候采集的页面的大小关系。
如果符合条件的数据页的分布少于10个,当然在预估的时候,会全部扫描这些page,当然预估是完全精确的,这也是第一个查询执行计划预估的实际行数完全不一致的原因。
如果符合条件的数据页的分布大于10个,当然在预估的时候,会部分扫描这些page,预估的误差情况就此产生,这也是第二个查询执行计划预估的实际行数差异较大的原因。

澳门新葡亰赌995577 3

 当然MySQL的每个版本可能都有所改进或者差异,笔者并没有从源码中找到具体的算法,当前测试的是5.7.20版本。

 

但目前仍不清楚,
1,在create_date字段上,时间是按照DATE_ADD(sysdate(), INTERVAL
-rand()*2400 hour)生成的,从整体分布看,基本按照时间均匀分布的.
 
理论上根据这种方式推到,得到的预估结果偏差应该不会很大,但尚不清楚为什么预估与实际存在如此大的差异。
2,尝试找到预估值从精确到产生差异的临界点,通过查询实际行数,根据key_len的值以及B树索引的存储原理(二级索引叶子节点存储的二级索引的key值+聚集索引的key值).
 
理论上计算出来当前查询一个大概的取样的page个数,发现这个值预报理论上的10个page差异较大,可能是推到方式有问题,或者是MySQL预估本身有一些不知道的细节问题。
3,没有详细翻MySQL的源码,尚未找到具体的实现细节。

 

对于有直方图的数据库来说,直方图的信息也不是没有代价,或者是万能的,直方图也有直方图的局限性,这里暂不表述。
对于尚没有直方图的MySQL数据库来说,其预估原理是每次查询的时候进行对相关的数据页面进行采样预估的,而不是从直方图中获取到预估信息的,这是一个很消耗性能的操作。
详情参考:http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/
这可能会导致MySQL不适合做较大数据量或者较为复杂的JOIN操作,当然这也取决于具体的业务设计方案以及对数据的依赖程度,或者主观上的查询提示操作。
说这句话是冒着被MySQL的大神以及粉丝们怒喷的风险的。
关于MySQL的预估的知识点,搜索到的文章并不是很多,也拘泥于个人的认识有限,也希望对这方面有关注的大神多多指点。
据说MySQL在8.0之后的版本中会加入直方图信息,以及其他JOIN方式(除了LOOP
JOIN),这可能对性能上有比较大的帮助。

 

参考链接:
https://www.cnblogs.com/LBSer/p/3333881.html
http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/

 

http://www.bkjia.com/Mysql/1301919.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1301919.htmlTechArticleMySQL统计信息以及预估方式初探,mysql统计信息初探
数据库中的统计信息在不同(精确)程度上描述了表中数据的分布情况,执行计划通过…

直方图是表上某个字段在按照一定百分比和规律采样后的数据分布的一种描述,最重要的作用之一就是根据查询条件,预估符合条件的数据量,为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格式的字符串,可读性并不好。

澳门新葡亰赌995577 4

想到了sqlserver中DBCC
SHOW_STATISTICS的直方图信息,如下的格式,直方图中的数据分布情况看起来非常清晰直观

澳门新葡亰赌995577 5

于是就做了一个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,也就是100%

澳门新葡亰赌995577 6

需要注意的是,直方图的更新时间是标准时间(UTC
value),而不是服务器当前时间。
MySQL
8.0中的直方图基本上与sqlserver的直方图一致,都是基于单列的抽样预估,但是MySQL直方图中没有类似于sqlserver中的字段选择性,
不过这个字段选择性本身意义也不大
,sqlserver中对于复合索引,两个字段合计在一块统计,除非两个字段的同时分布的都很均匀,否则多字段索引的字段选择性参考意义不大。
这也是复合索引无法做到较为精确预估的原因。