图片 7

MySQL 8.0 中统计信息直方图的尝试

 

1 什么是统计信息

    统计信息 描述了 表格或者索引视图中的某些列的值
的分布情况,属于数据库对象。根据统计信息,查询优化器就能评估查询过程中需要读取的行数及结果集情况,同时也能创建高质量的查询计划。有了统计信息,查询优化器可以使用基数估计来选择合理的索引,而不需要耗费更多的IO资源扫描来评估哪个索引合理,能有效提供查询性能。所以,简单的说,统计信息是用来
反应数据在实体表格或者视图中的分布情况。

一.概述  

  sql
server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql
server就知道了数据的分布情况。索引的统计值信息,还内置策略用来在没有索引的属性列上创建统计值。在有索引和没有索引的属性列上统计值信息会被自动维护。大部分场景下不需要手动去维护统计信息。
  
  作用是 sqlserver
查询优化器使用统计信息来创建可提高查询性能的查询计划。
对于大多数查询,查询优化器已为高质量查询计划生成必要的统计信息。每个索引都会自动建立统计信息,
统计信息的准确性直接影响指令的速度,执行计划的选择是依据统计信息。

  1.1 属性列统计值
  默认情况下,每当在一个查询的where子句中使用非索引属性列时,sqlserver会自动地创建统计值,统计名称以_WA_Sys开头。

-- 查看表中非索引的统计信息
 sp_helpstats PUB_Search_Log

   如下所示:

 图片 1图片 2

  1.2 自动更新统计信息的阀值

  在自动更新统计信息选项 AUTO_UPDATE_STATISTICS 为 ON
时,查询优化器将确定统计信息何时可能过期。查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较,确定统计信息何时可能过期。
  (1)如果在评估时间统计信息时表基数为 500 或更低,则每达到 500
次修改时更新一次。
  (2)如果在评估时间统计信息时表基数大于 500,则改变每达到 500 +
20%的行数更新一次(大表特别要注意更新时间)

直方图是表上某个字段在按照一定百分比和规律采样后的数据分布的一种描述,最重要的作用之一就是根据查询条件,预估符合条件的数据量,为sql执行计划的生成提供重要的依据
在MySQL
8.0之前的版本中,MySQL仅有一个简单的统计信息却没有直方图,没有直方图的统计信息可以说是没有任何意义的。
MySQL
8.0新特性之一就是开始支持统计信息的直方图,这个概念很早就提出来了,抽空具体尝试了一下使用方法。

2 统计信息的内容

    可以通过sys.stats查看到统计信息的名字及基于哪一个表格,然后根据 dbcc
show_statistics(<table_name>,<index_or_statistics_name>)
来查看统计信息内容。

 

图片 3

可以看到,统计信息分为三部分内容,头信息,数据字段选择性及直方图。

二. 统计信息分析

--查询统计信息
DBCC SHOW_STATISTICS(tablename,'indexname')

  下面是一个复杂的统计信息,上一次更新统计信息时间是2018年5月8日,距离现在有二个多月没更新了,也就是说更新条件没有达到(改变达到500次

  • 20%的行数变动)。

  图片 4

  图片 5

  2.1 统计信息三部分:头信息,字段选择性,直方图。
   (1) 头信息

    name:统计信息名称,也是索引的名字。
    updated:上一次统计信息更新时间(重要)。
    rows:上一次统计表中的行数,反映了表里的数据量。
    rows Sampled:
用于统计信息计算的抽样总行数。当表格数据比较大,为了降低消耗,只会取一小部分数据做抽样。 
rows sampled<rows时候统计信息可能不是最精确的。
    steps:把数据分成几组。最多200个组,每个直方图梯级都包含一个列值范围,后跟上限列值。
    density:索引第一列前缀的选择性。查询优化器不使用此 Density,
值此值的目的是为了与 SQL Server
2008 之前的版本实现向后兼容。
    average key length:索引列平均字节数。
    string index: YES 代表字符串索引。

  (2)数据字段选择性

    all density:
反映了索引列的选择度。它反映了数据集里重复的数据量多少,如果数据很少有重复,那么它选择性就比较高。 密度为
1/非重复值。值越小选择性就越高。如果值小于了0.1,那索引的选择性就非常高了(这一点通过查看自增ID主键索引列,非常明显小于了0.1的值)。
    average length: 索引列平均字节长度 例如model
列值平均长度是25个字节。
    columns:索引列名称

  (3)直方图(对应steps 组)

      直方图度量数据集中每个非重复值的出现频率。
查询优化器根据统计信息对象第一个键列中的列值来计算直方图,它选择列值的方法是以统计方式对行进行抽样或对表或视图中的所有行执行完全扫描。
    range_hi_key: 列值也称为键值。直方图里每一组(step)数据最大值
。上图值是model字符串类型
    range_rows:每组数据区间估算数目。
    eq_rows:表中值与直方图每组数据库上限相等的数目
    distinct_range_rows:每组中非重复数目,
如果没有重复则range_rows等于distinct_range_rows值。
    avg_range_rows:每组数据区间重复值平均数目, (range_rows)

 

 三. 人工维护的几种情况

1.查询执行时间很长
  如果查询响应时间很长或不可预知,则在执行其他故障排除步骤前,确保查询具有最新的统计信息。
2.在升序或降序键列上发生插入操作。
  与查询优化器执行的统计信息更新相比,升序或降序键列(例如 IDENTITY
或实时时间戳列)上的统计信息可能要求更频繁地更新。插入操作将新值追加到升序或降序键列上
3.在维护操作后。
  考虑在执行维护过程(例如截断表或对很大百分比的行执行大容量插入)后更新统计信息。
这可以避免在将来查询等待自动统计信息更新时在查询处理中出现延迟。

-- 更新统计信息
UPDATE STATISTICS tablename(indexname)

  更新统计信息可确保查询使用最新的统计信息进行编译。
不过,更新统计信息会导致查询重新编译。
我们建议不要太频繁地更新统计信息,因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。

之前写过MSSQL相关统计信息的一点东西,在原理上都是一致的,

2.1 头信息

列名 说明
Name 统计信息的名称。
Updated 上次更新统计信息的日期何时间
Rows 预估表中的行数,不一定是精确的
Rows Sampled 统计信息的抽样行数,如果小于Rows,则说明直方图和密度结果是更加抽样行估计的
Steps 直方图中的梯级数。
Number of steps in the histogram.
每个梯级都跨越一个列值范围,后跟上限列值。 直方图梯级是根据统计信息中的第一个键列定义的。 最大梯级数为 200。
Density 计算公式为 1/统计信息对象第一个键列中的所有值(不包括直方图边界值)的非重复值。 查询优化器不使用此 Density 值,显示此值的目的是为了与 SQL Server 2008 之前的版本实现向后兼容。
Average key length 统计信息对象中所有键列的每个值的平均字节数。
String Index Yes 指示统计信息对象包含字符串摘要统计信息,以改进对使用 LIKE 运算符的查询谓词的基数估计;例如 WHERE ProductName LIKE ‘%Bike’。
Yes indicates the statistics object contains string summary statistics to improve the cardinality estimates for query predicates that use the LIKE operator; for example, WHERE ProductName LIKE ‘%Bike’.
字符串摘要统计信息与直方图分开存储,并当它是类型的统计信息对象第一个键列上创建char, varchar, nchar, nvarchar, varchar (max), nvarchar (max),文本,或ntext。
Filter Expression 包含在统计信息对象中的表行子集的谓词。 NULL = 未筛选的统计信息。 有关筛选的谓词的详细信息,请参阅Create Filtered Indexes。 有关筛选的统计信息的详细信息,请参阅统计信息。
Unfiltered Rows 应用筛选表达式前表中的总行数。 如果筛选表达式为 NULL,则 Unfiltered Rows 等于 Rows。

 

2.2 数据字段选择性

列名 Description
Density 密度为 1/非重复值。 结果显示统计信息对象中各列的每个前缀的密度,每个密度显示一行。 非重复值是每个行前缀和列前缀的列值的非重复列表。 例如,如果统计信息对象包含键列 (A, B, C),结果将报告以下每个列前缀中非重复值列表的密度:(A)、(A,B) 以及 (A, B, C)。 使用前缀 (A, B, C),以下每个列表都是一个非重复值列表:(3, 5, 6)、(4, 4, 6)、(4, 5, 6) 和 (4, 5, 7)。 使用前缀 (A, B),相同列值则具有以下非重复值列表:(3, 5)、(4, 4) 和 (4, 5)
Average Length
存储列前缀的列值列表的平均长度(以字节为单位)。 例如,如果列表 (3, 5, 6) 中的每个值都需要 4 个字节,则长度为 12 个字节。
columns
为其显示 All density 和 Average length 的前缀中的列的名称。

照旧,直接上例子,造数据,创建一个测试环境

2.3 直方图

列名 Description
RANGE_HI_KEY 直方图梯级的上限列值。 列值也称为键值。
RANGE_ROWS 其列值位于直方图梯级内(不包括上限)的行的估算数目。
EQ_ROWS 其列值等于直方图梯级的上限的行的估算数目。
DISTINCT_RANGE_ROWS 非重复列值位于直方图梯级内(不包括上限)的行的估算数目。
AVG_RANGE_ROWS
重复列值位于直 方图梯级内(不包括上限)的平均行数(如果 DISTINCT_RANGE_ROWS > 0,则为 RANGE_ROWS / DISTINCT_RANGE_ROWS)。

   

   
直方图,用于计算数据中每个非重复值出现的频率。使用统计信息对象的第一个键列中的列值来计算直方图,可以通过抽样行或者全表扫描的形式。如果是抽样创建,那么,这里边的
存储总行数何非重复值总数则为估计值。

   
创建直方图的时候,查询优化器对列值进行排序,同时计算每个非重复列值匹配的个数,然后将这列非重复列值
分为
1-200个连续的直方图梯级中,每个梯级包含一个列值范围,该范围介于两个边界值之间的所有可能列值,不包含边界值本身,最小的排序列值是第一个直方图梯级的上限值。

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 ;

3 影响统计信息的选项

    每个表格或者索引视图
何时创建统计信息、基于哪些列创建统计信息及何时更新统计信息,需要根据 
AUTO_CREATE_STATISTICS 、 AUTO_UPDATE_STATISTICS、
AUTO_UPDATE_STATISTICS_ASYNC 的设定值 来确定,这三个属于
数据库级别的选项,可以通过系统视图查看,也可以通过
图形界面选择数据库的“属性”,查看“选项”。

1 --查看数据库统计信息选项设定值
2 SELECT
3       name dbname,
4       is_auto_create_stats_on,
5          is_auto_update_stats_on,
6          is_auto_update_stats_async_on
7 FROM sys.databases

MySQL中统计信息的创建,不同于MSSQL,MySQL统计信息不依赖于索引,需要单独创建,语法如下

3.1 AUTO_CREATE_STATISTICS

    默认为ON。自动创建统计信息选项,仅应用于 表格单列统计信息!!!

   
查询优化器根据查询谓词的使用情况,在表格上单独给某一列创建统计信息(这些单列暂时未创建直方图),协助查询计划的基数估计。

    该选项不决定是否为索引创建统计信息,也不生产筛选统计信息。

    通过该选项创建的统计信息,名称以 _WA
开头。可以通过sys.stats视图查看。

1 SELECT OBJECT_NAME(s.object_id) AS object_name,
2     COL_NAME(sc.object_id, sc.column_id) AS column_name,
3     s.name AS statistics_name
4 FROM sys.stats AS s JOIN sys.stats_columns AS sc
5     ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
6 WHERE s.name like '_WA%'
7 ORDER BY s.name;

–创建字段上的统计直方图信息
ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16
BUCKETS;
–删除字段上的统计直方图信息
ANALYZE TABLE test DROP HISTOGRAM ON create_date

3.2 AUTO_UPDATE_STATISTICS

   
默认为ON。自动更新统计信息选项,查询优化器自动确定统计信息何时过期何时需要更新。

通常情况,从上次自动更新至今,如果期间积累了较大数量的数据变更,包括插入、删除及修改,或表结构变更等,均会造成统计信息过期。

    该选项适用于为索引创建统计信息对象、查询谓词中的单列以及使用 create
statistics 语句创建的统计信息。

1,可以一次性创建多个字段的统计信息,系统会逐个创建列出的字段上的统计信息,统计信息不依赖于索引,这一点与MSSQL不同(当然MSSQL也可以抛开索引独立创建统计信息)
2,BUCKETS值是一个必须提供的参数,默认值为1000,范围是1-1024,这一点也不同与MSSQL也不一样,MSSQL是有一个类似的最大值为200的步长(step)字段
3,一般来说,数据量较大的情况下,对于不重复或者重复性不高的数据,BUCKETS值越大,描述出来的统计信息越详细
4,统计信息的具体内容在
information_schema.column_statistics中,但是可读性并不好,可以根据需求自行解析(出来一种自己喜欢的格式)

3.3 AUTO_UPDATE_STATISTICS_ASYNC

    默认为OFF。异步自动更新统计信息选项,确定查询优化器是使用
同步统计信息更新还是异步统计信息更新。OFF则代表使用同步自动更新统计信息,这样,查询计划始终使用最新的统计信息进行编译执行,如果遇到统计信息过期,则会在查询编译前等待更新统计信息,若是异步自动更新统计信息,则在遇到统计信息过期时,直接使用现有统计信息编译然后执行,即使可能由于统计信息过期造成编译不佳,执行计划非最优,但仍按照编译结果运行。

    该选项使用于适用于
为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS
语句创建的统计信息。

通常情况下,使用
同步自动更新统计信息,则设置该选项为OFF,而在以下两种情况下,则可开启为ON(来自官网):

  • 应用程序贫富执行相同查询或者类似查询,与同步统计信息更新相比,使用异步统计信息更新查询的响应时间可以不受影响,避免出现等待最新统计信息的情况;
  • 应用程序遇到了客户端请求超时,这些超时是由于一个或多个查询正在等待更新后的统计信息所导致的。
    在某些情况下,等待同步统计信息可能会导致应用程序因过长超时而失败。

与sqlserver中的统计信息一样,理论上,在准确性与取样百分比(BUCKETS)是成正比的,当然生成统计信息的代价也就越大,
至于BUCKETS与统计信息的取样百分比,以及综合代价,笔者暂时没有找到相关的资料。

4  何时创建与更新

如下是通过ANALYZE TABLE test UPDATE HISTOGRAM ON create_date WITH 4
BUCKETS;创建的统计信息直方图
可以发现直方图的HISTOGRAM字段是一个JSON格式的字符串,可读性并不好。

4.1 创建

  • 查询优化器自动创建
    • 创建索引时,查询优化器自动为表格或者视图上的索引创建统计信息

    • 在 AUTO_CREATE_STATISTICS 为 ON
      时,查询优化器为查询谓词中的单列创建统计信息
  • 手动执行创建

    • CREATE STATISTICS 创建

常规情况下,查询优化器创建的统计信息就可以满足我们的大多数需求,但是如果出现以下情况,可以考虑手动创建:

  • 数据库引擎优化顾问建议创建
  • 查询谓词包含尚不位于相同索引中的多个相关列
  • 查询从数据的子集中选择数据
  • 查询缺少统计信息

图片 6

4.2 更新

   
统计信息定义在普通的表格上,当发生以下任一变化时,统计信息就会被认为是过时的,下次使用到的时候,会自动触发更新动作:

  • – 表格从没有数据变成大于等于1条数据;

  • 对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后;

  • 对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500+(20%*表格数据总量)以后。

   
这三种情况下,第三种情况最容易出现更新不及时的情况,比如一张100万的表格,它最近一个月的数据增长是15万左右,由于小于20%,统计信息没有更新,这就导致了有关最近一个月数据sql执行有不是很正确的信息提供,那么就需要定期去检查并及时更新统计信息!

 

   
临时表上可以有统计信息,其维护策略基本和普通表格一样,但是表变量上不能建立统计信息。

 1 --更新指定统计信息
 2 UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
 3 GO
 4 
 5 --更新表格上的所有统计信息
 6 UPDATE STATISTICS Sales.SalesOrderDetail;
 7 GO
 8 
 9 --更新整个数据库上的所有统计信息
10 EXEC sp_updatestats;
11 
12 --删除统计信息
13 DROP STATISTICS Purchasing.Vendor.VendorCredit, Sales.SalesOrderHeader.CustomerTotal;
14 GO
15 
16 --查看统计信息上一次更新时间
17 
18 SELECT
19        OBJECT_NAME(OBJECT_ID)
20 FROM sys.stats
21 WHERE STATS_DATE(object_id, stats_id) is not null

 

参考资料:

 

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

图片 7