您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:MySQL索引总计新闻更新相关的参数,

新葡亰496net:MySQL索引总计新闻更新相关的参数,

发布时间:2019-07-05 13:01编辑:网络数据库浏览(198)

      

     

     

    前言:统计信息作为sql server优化器生成执行计划的重要参考,需要数据库开发人员,数据库管理员对其有一定的理解,从而合理高效的应用,管理.

     

    为什么要写统计信息

    本文出处: 

    本文出处: 

    第一部分 概念

    MySQL统计信息相关的参数:

      最近看到园子里有人写统计信息,楼主也来凑热闹。
      话说经常做数据库的,尤其是做开发的或者优化的,统计信息造成的性能问题应该说是司空见惯。
      当然解决办法也并非一成不变,“一招鲜吃遍天”的做法已经行不通了(题外话:整个时代不都是这样子吗)
      当然,还是那句话,既然写了就不能太俗套,写点不一样的,本文通过分析一个类似实际案例来解读统计信息的更新的相关问题。
      对于实际问题,不但要解决问题,更重要的是要从理论上深入分析,才能更好地驾驭数据库。  

    首先解释一个概念,统计信息是什么:
      简单说就是对某些字段数据分布的一种描述,让SQL Server大概知道预期的数据大小,从而指导生成合理执行计划的一种数据库对象

     

    统计信息(statistics):描述某个(些)栏位,索引的数据分布情况.优化器用其评估生成高效执行计划.

     

    统计信息基础

    默认情况下统计信息的更新策略:
      1,表数据从0行变为1行
      2,少于500行的表增加500行或者更多
      3,当表中行多于500行时,数据的变化量大于500 20%*表中数据行数

    统计信息写过几篇了相关的文章了,感觉还是不过瘾,关于统计信息的问题,最近又踩坑了,该问题虽然不算很常见,但也比较有意思。
    相对SQL Server 2012,发现在新的SQL Server版本(2014,2016)中都有一些明显的变化,下文将对此进行粗浅的分析。

    密度(density):一个比率值,显示在一个(组)列中有多少唯一值.(实际应用中值越小越好)

      1. innodb_stats_on_metadata(是否自动更新统计信息),MySQL 5.7中默认为关闭状态

    首先说一个老掉牙的话题,统计信息的更新阈值:
    1,表格从没有数据变成有大于等于1条数据。
    2,对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后。
    3,对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500

    非默认情况下,促使已有统计信息更新的因素(包括但不限于下面三种,别的我也没想起来):
      1,rebulidReorg index
      2,主动update statistics
      3,数据库级别的sp_updatestats

    SQL Server 2012中(包括之前的版本),因表中数据变化,但统计信息尚未更新的情况下,对于直方图中没有覆盖到的谓词过滤时,sqlserver总是预估为1行
    SQL Server 2014和 Server 2016中这种估算方式都有所变化,从表现看,对于对于没有覆盖到的谓词过滤的预估,每个版本都是不同的。
    本文简单测试一下此种情况在SQL Server 2012,SQL Server 2014,SQL Server 2016的不同表现,以及该问题可能造成的潜在影响。

    Density = 1 / Number of distinct values for column(s)

        仅在统计信息配置为非持久化的时候生效。
        也就是说在innodb_stats_persistent 配置为OFF的时候,非持久化存储统计信息的手,innodb_stats_on_metadata的设置才生效。
        当innodb_stats_on_metadata设置为ON的时候,
        InnoDB在执show table status 或者访问INFORMATION_SCHEMA.TABLES
        或者INFORMATION_SCHEMA.STATISTICS 系统表的时候,
        更新费持久化统计信息(类似于ANALYZE TABLE),innodb_stats_on_metadata不管打开还是关闭,都不影响持久化存储统计信息的索引
        某个索引的统计信息更新时间参考mysql.innodb_index_stats这个系统表

    • (20%×表格数据总量)以后。

    开始问题:

    下面涉及到的测试环境的数据库版本如下

    直方图(histogram):将数据分割成不同的段(steps),用于描述,记录每段数据分布的具体情况(抽样创建).最多分为200 steps

    某个索引的统计信息更新时间参考mysql.innodb_index_stats这个系统表
    select *
    from mysql.innodb_index_stats
    where table_name = 'teststatistics';

    新葡亰496net 1

    对于大表的更新策略是:数据的变化量大于500 20%*表中数据行数
    比如对于1000W数据量的表,数据变化要超过500 1000W*20%=2,000,500之后才能触发统计信息的更新,
    这一点大多数情况下是无法接受的,为什么?因为该规则下触发统计信息更新的阈值太大,会导致某些统计信息长期无法更新,
    由于统计信息导致的执行计划不合理的情况已经在实际业务中屡见不鲜,对于统计信息的更新已经显得非常必要

    新葡亰496net 2新葡亰496net 3新葡亰496net 4

    DBCC show_statistics(object_name,Column_name)

      2. innodb_stats_auto_recalc

    做个查询,触发统计信息更新,rowmodct归0(继续累积直到下一个触发的阈值,触发更新之后再次归0)

    同时,仅仅靠sqlserver自己更新统计信息,也不一定可靠,因为统计信息中还要一个取样行数的问题,这个也非常重要
    因为SQL Server默认的取样行数是有上限的(默认取样,未指定取样百分比或者SQL Server自动更新统计信息时候的取样百分比),
    这个上限值在100W行左右(当然也不肯定,只是观察),对于超过千万行的表,这个取样比例还是非常低的
    比如下图超过3亿行的表,更新统计信息时候未指定取样百分比,默认取样才去了84万行)
    据楼主的观察看,对于小表,不超过500W行的表,默认的取样比例是没有问题的,对于较大的表,比如超过500W行的表(当然这个500W行也是一个参考值,不是绝对值)
    因此说默认取样比例是根本无法准确描述数据分布的。

     

    Header(信息头)包含统计信息一系列元数据

        是否自动触发更新统计信息,仅影响持久化存储的统计信息的表,阈值是变化的数据超过表行数的10%。
        也就是说,一个表索引统计信息是持久化存储的,并且表中数据变化了超过10%,
        如果innodb_stats_auto_recalc为ON,就会自动更新统计信息,否则不会自动更新

    新葡亰496net 5

    新葡亰496net 6

     

    Density(密度)包含列(列组)的密度信息及平均列(组)长度

      3. innodb_stats_persistent(非持久化统计信息开关),MySQL 5.7中默认为打开,持久化存储统计信息

     

    由此看来,人工介入统计信息的更新是非常有必要的。那么如何更新索引的统计信息,有没有一种固定的方式?答案是否定的。

    测试环境准备

    Histogram(直方图)包含直方图描述信息.

        该选项设置为ON时候,统计信息会持久化存储到磁盘中,而不是存在在内存中,
        相反,如果是非持久化存储的(存在内存中),相应的统计信息会随着服务器的关闭而丢失。

    关于统计信息“过期”的问题

     

    首先利用如下脚本,建一张测试表,写入测试数据,下面会解释测试数据的分布

     

      4. innodb_stats_persistent_sample_pages (持久化更新统计信息时候索引页的取样页数)

    下面开始正文,网络上很多关于统计信息的文章,提到统计信息,很多都是统计信息过期的问题,然后跟新之后怎么怎么样
    尤其在触发统计信息自动更新阈值的第三个区间:也就是说数据累计变化超过20%之后才能自动触发统计信息的更新
    这一点对于大表来说通常影响是比较大的,比如1000W的表,变化超过20%也 500也就是200W 500行之后才触发统计信息更新,
    这个阈值区间的自动触发阈值,绝大多数情况是不能接受的,于是对于统计信息的诊断就变成了是否“过期”

    首先来看能够触发统计信息更新的方式 

    create table A
    (
        IdentifierId    int identity(1,1),
        Id1                int,
        Id2                int,
        OtherCol        CHAR(500)
    )
    GO
    
    begin tran
    declare @i int = 1
    while @i<=1000000
    begin
        insert into A values ((@i/50000) 1,@i,NEWID())
        set @i = @i 1
        if (@iP0000)=0
        begin
            if @@TRANCOUNT>0
            begin
                commit
                begin tran
            end
        end
    end
    if @@TRANCOUNT>0
    begin
        commit
    end
    GO
    

    Histogram(直方图)

        默认是20个page,如果设置的过高,那么在更新统计信息的时候,会增加ANALYZE TABLE的执行时间。

     

    1,RebulidReorg index
      当然RebulidReorg索引只是附带更新了索引的统计信息,主要是为了整理了索引碎片,
      对于大表,代价相当大,数据库的维护策略,没有一概而论的方法,
    新葡亰496net:MySQL索引总计新闻更新相关的参数,中执会考查总计局计消息直方图中对此从未掩饰到谓词预估以及预估战略的更改。  对于较小的数据库或者是较小的表,比如几十万几百万的表,每天一个rebuild index都可以,
      但是这种经验移植到大一点的数据库上恐怕就不好使了(正如名人的成功经验不可复印一样,每个人生活的环境不一样,不能一概而论)。
      这种RebulidReorg index对资源的消耗以及时间代价上都会相当大,甚至有些情况下是不会给你机会这么做的。
      比如下面rebuild一个复合索引的耗时情况,仅仅是一个表上的一个索引,就花费了5分钟的时间
    新葡亰496net:MySQL索引总计新闻更新相关的参数,中执会考查总计局计消息直方图中对此从未掩饰到谓词预估以及预估战略的更改。  一个业务复杂的表上有类似这么三五个索引也是正常的,
      照这么算下去,如果全库或者是整个实例下的十几个库,每个库数百张表全部这么做,要多长时间,代价可想而知
      说不定整都没整完,维护窗口期的时间就到了,除非数据库不大(究竟大小的临界值为多少?个人觉得可以粗略地认为100GB吧),否则是不可以这么做的。

    插入的测试数据的分布如下,Id1是从1~20,每一个Id1对应50000个不同的Id2

    RANGE_HI_KEY:直方图列(多列情况为首列记录)段的上限值.列值就是键值

      5. innodb_stats_transient_sample_pages(临时性更新统计信息时候索引页的取样页数)

    新葡亰496net 7

      因此可以认为:通过重建或者重组索引来更新索引统计信息,代价太大了,基本上是不现实的。

    新葡亰496net 8

    RANGE_ROWS:其相应列值位于此段(不包含上限)的行得数量(估计值)

        默认值是8,innodb_stats_persistent设置为disable的情况下,也即非持久化明确关闭的时候,innodb_stats_transient_sample_pages才生效,
        也就是非持久化存储过索引统计信息的时候,innodb_stats_transient_sample_pages为更新统计信息的采样页数
        这个值是否生效,要依赖于innodb_stats_on_metadata,而innodb_stats_on_metadata又依赖于innodb_stats_persistent
        总而言之:如果配置为持久化存储统计信息,非持久化相关的配置选项就不起作用

     

      新葡亰496net 9

      

    EQ_ROWS:等于其列值上限值的行数

      6. innodb_stats_sample_pages

    判断统计信息是否过期,然后通过更新统计信息来促使执行计划更加准确地预估行数,这一点本无可厚非
    但是,问题也就出在这里了:那么怎么更新统计信息?一成不变的做法是否可行,这才是问题的重点。
    当然肯定有人说,我就是按照默认方式更新的,更新完之后SQL也变得更加优化了什么的
    通过update statistics TableName StatisticName更新某一个索引的统计信息,
    或者update statistics TableName更新全表的统计信息
    这种情况下往往是小表上可以这么做,当然对于大表或者小表没有一个标准值,一切要结合事实来说明问题  

    2,update statistics

    统计信息直方图中覆盖到的谓词的预估

    DISTINCT_RANGE_ROWS: RANGE_ROWS中的非重复值数量

        已弃用. 已用innodb_stats_transient_sample_pages 替代。
        为啥要用innodb_stats_transient_sample_pages替代?
        个人猜测是一开始参数命名不规范,既然是临时行统计信息,却没有做到见名知意,与innodb_stats_persistent_sample_pages区分开来,
        或许是一开始MySQL中只有临时行统计信息,没有持久化统计信息。

    下面开始本文的主题:

      正是我想重点说的,因为我这里不具体说语法了,具体语法就不做详细说明了,
      简单来说,大概有如下几种选择:
      一种默认方式,另外还可以是全表扫描的方式更新,还有就是是指定一个取样百分比,如下:

      测试:根据直方图中的任何一个Id来做查询,查询之前先创建相关列上的统计信息,发现预估行数是绝对准确的。

    AVG_RANGE_ROWS:直方图段内值得平均行数(不包括上限)

     

    抽象并简化出业务中的一个实际案例,创建这么一张表,类似于订单和订单明细表(主子表),
    这里你可以想象成是一个订单表的子表,Id字段是唯一的,有一个ParentID字段,是非唯一的,
    ParentID类似于主表的Id,测试数据按照一个主表Id对应50条子表明细的规律插入数据

    --默认方式更新表上的所有统计信息
    update statistics TableName
    --对指定的统计信息,采用全表扫描的方式取样
    update statistics TableName(index_or_statistics__name) with FullScan 
    --对指定的统计信息,采用指定取样百分比的方式取样
    update statistics TableName(index_or_statistics__name1,index_or_statistics__name2) with sample 70 percent
    

      新葡亰496net 10

                     DISTINCT_RANGE_ROWS > 0则为RANGE_ROWS / DISTINCT_RANGE_ROWS

    统计信息更新测试1:打开innodb_stats_auto_recalc的情况下,统计信息会在触发其更新阈值后自动更新

    CREATE TABLE [dbo].[TestStaitisticsSample](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [OtherColumn] [varchar](50) NULL
    ) 
    
    
    declare @i int=0
    while(@i<100000000)
    begin
    
        insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())
        /*
        中间插入50条,也即一个主表Id对应50条子表明细
        */
        insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())
    
        set @i=@i 1
    end
    go
    
    create nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]
    (
        [ParentId] 
    )
    go
    

      相对于重建或者重组索引,update statistics 也是通过扫描数据页(索引页)的方式来获取数据分布,但是不会移动数据(索引)页,
      这是Update Statistics代价相对于Rebuild索引小的地方(即便是Update Statistics的时候100%取样)
      关键在于第三种方式:人为指定取样百分比,如果取样百分比为100,那跟FullScan一样
      如果不用100,比如80,60,50,30,又如何选择?取样百分比越高,得到的统计信息越准确,但是代价越大,取样越小效率越高,但是误差的可能性会变大,怎么办,这就需要找一个平衡点。
      那么究竟要取样多少,既能在更新统计信息的效率上可以接受,又能够使得统计信息达到相对准确地描述数据分布的目的,
      这是还是一个需要慎重选择的问题,为什么?参考:http://www.cnblogs.com/wy123/p/5875237.html
      如果统计信息取样百分比过低,会影响到统计信息的准确性,
      如果过于暴力,比如fullscan的方式扫描,
      参考下图,一个表就Update了50分钟(当然这是一个大表,上面有多个索引统计信息以及非索引统计信息)。如果有数十张类似的表,效率可想而知
      总之就是,没有一个固定的方式,数据库不大,怎么做问题都不大,数据库一大,加上维护的窗口期时间有限,要在统计信息的质量和维护效率上综合考虑

      查看idx_1上的统计信息,上面预估的绝对准确就归结于统计信息100%的取样统计以及Rang_Hi_key的EQ_Rows,直方图中的Id1的分布是1~21

    新葡亰496net 11

     

     

      新葡亰496net 12

      新葡亰496net 13

     

    新葡亰496net 14

    本来打算插入1亿条的,中间我让他执行我睡午觉去了,醒来之后发现SSMS挂掉了,挂掉了算了,数据也接近1亿了,能说明问题就够了
    现在数据分布的非常明确,就是一个ParentId有50条数据,这一点首先要澄清。

     

     

    第二部分 原理,应用

     

     

    3,数据库级别的sp_updatestats

    统计信息直方图中未覆盖到的谓词的预估

     

    查询统计信息更新时间

    测试数据写入,以及所创建完成之后来更新 idx_ParentId 索引上的统计信息,就按照默认的方式来更新,然后来观察统计信息

      用法:
      exec sp_updatestats
      或者
      exec sp_updatestats @resample = 'resample'

      继续插入一个与上面Id2都不一样的数据,这里为50,因为此时插入的是50000行数据,同时又不足以触发统计信息更新,因此发生如下写入数据之后,统计信息并不会更新。
      因此这个插入完成之后,统计信息并没有更新。

    统计信息更新

    新葡亰496net 15

    * *

      指定 sp_updatestats 使用 UPDATE STATISTICS 语句的 RESAMPLE 选项。

        新葡亰496net 16

    统计信息可以人工维护更新或是由优化器在确认执行计划有效时依据之一:重编译阈值(recompilation threshold/RT)来决定统计信息是否过期而执行更新.

     

    默认方式更新统计信息(未指定采样密度)

      对于基于默认抽样的查询计划并非最佳的特殊情况,SAMPLE 非常有用。
      在大多数情况下,不必指定 SAMPLE,
      这是因为在默认情况下,查询优化器根据需要采用抽样,并以统计方式确定大量样本的大小,以便创建高质量的查询计划。

      因为统计信息没有更新,在idx_1的直方图中,是没有Id1=50的信息的,也就说Id1=50不存在于统计信息的直方图中,
      在SQL Server 2012中预估的结果:预估为1行,实际为50000行

    触发条件

    收集统计信息之后再次查询,innodb_index_stats表,统计信息更新

    表里现在是8000W多一点记录,默认更新统计信息时取样行数是462239行,那么这个统计信息靠谱吗?

      如果未指定 'resample',则 sp_updatestats 将使用默认的抽样来更新统计信息。 
      默认值为 NO。

      新葡亰496net 17

    当创建的表为空表时,添加一条数据则更新

    新葡亰496net 18

    上面说了,造数据的时候,我一个ParentId对应的是50行记录,这一点非常明确,他这里统计出来的多少?

      直接执行exec sp_updatestats更新统计信息,取样密度是默认的,
      究竟这默认值是多少,MSDN上说默认情况下是“查询优化器根据需要采用抽样”,我想着采样算法应该没那么简单粗暴
      目前也不知道具体是怎么一个算法或者采样方式,如果有知道园友的话请不惜赐教,谢谢

      重复以上测试代码,分别在SQL Server 2014和SQL Server 2016中测试,不重复截图了

    当表数据小于500行时,记录更新标识(Modification Counters)大于500更新

      

    1,对于取样的RANG_HI_Key值,比如51632,预估了862.212行

     

      SQL Server 2014中测试如下:行预估为1024.7,实际为50000,
      这个值是通过什么方式计算出来的?暂时还没查到资料。

    当表数据大于500行时,记录更新标识大于500且20%行数变化(rowcnt)

      通过改变表的变化行数,使得统计信息自动收集

    2,对于AVG_RANG_ROW,比如45189到51632之间的每个Id的数据对应的数据行,预估是6682.490行

    4,TraceFlag 2371

     

    注:临时表表很小(0行或者小于6行).6次变更触发更新.

      新葡亰496net 19

    之前造数据的时候每个Id都是50行,这里的预估靠谱吗,这个误差是无法接受的,

    开启TraceFlag 2371之后,统计信息的变化是根据表做动态变化的,
    打破了触发大表统计信息更新的当表中行多于500行时,数据的变化量大于500 20%*表中数据行数 阈值
    参考:

      新葡亰496net 20

        表变量无统计信息

     

    很多时候,对于大表,采用默认(未指定采样密度)的情况下,默认的采样密度并不足以准确地描述数据分布情况

      在下图中,你可以看到新公式的工作方式,对于小表,阈值仍旧是在20%左右,
      只有超过25000行之后,此动态规则才会被触发生效
      随着表中数据行数的增加,(触发统计信息变更)的百分比会变的越来越低,
      比如,对于100,00行的表,触发统计信息更新的阈值已经降低为10%,
      对于1,000,000行的表,触发统计信息更新的阈值已经降低为3.2%。

      可以确定的是,对于类似情况的预估算法,也就是谓词没有包含在统计信息直方图中的情况下(one specifies a value which is out of range of the current statistics)
      在sqlserver 2014中,经测试,不同情况下预估是不一样的,不是固定的预估为1行,也不是固定预估为的0.1%,也不是简单的Rows Sampled*All density

     

     

    新葡亰496net 21

      新葡亰496net 22

     

    关于记录更新标识(Modification Counters)

    统计信息更新测试2:关闭innodb_stats_auto_recalc的情况下,统计信息会在触发其更新阈值后自动更新

     

      对于10,000,000或者是50,000,000行的表,触发统计信息更新的阈值为少于1%或者0.5%,
      而对于他100,000,000行的表,仅仅要求变化在0.31%左右,就可以出发统计信息的更新。

     

    Rowmodctr  sql2000及之前使用.记录在sys.sysindexes中.

        关闭自动收集统计信息选项innodb_stats_auto_recalc

    指定一个采样密度的方式更新统计信息(20%采样)

      但是个人认为,这种方式也不一定靠谱,虽然开启TraceFlag 2371之后触发更新索引统计信息的阈值降低了,但是取样百分比还是一个问题,
      之前我自己就有一个误区,看统计信息的时候只关注统计信息的更新时间(跟自己之前遇到的数据库或者表太小有关)
      对于统计信息,及时更新了(更新时间比较新)不等于这个统计信息是准确的,一定要看取样的行数所占总行数的百分比

      SQL Server 2016中测试如下: 预估为49880.8,实际为50000,基本上接近于真实值。
      相对于SQL Server 2012和2014的预估结果,这个预估的准确性看起来还是比较吊的。

    注:此参数虽然高版本依赖其使用,但微软目前仍维护此参数变化可作为参考

      新葡亰496net 23

     

     

      新葡亰496net 24

    colmodctr  sql2005及以后使用记录在sys.sysrscols. rcmodified中(需DAC访问)

     

    这一次用20%的采样密度,可以看到取样的行数是15898626行

    如何有效维护索引统计信息?

      为什么SQL Server 2016中预估的如此准确?
      因为在SQL Server 2016中,对于直方图中不存在的过滤谓词,在用这个谓词进行查询的时候,会自动更新相关的统计信息,然后再执行查询,
      这个特性,相对于SQL Server 2012和2014来说,是全新的,也是非常实用的。
      SQL Server 2014这个预估策略虽然在2012的基础上做出了一些改进,但是还是没有解决本质问题,以至于人仍旧要人为地干预统计信息的更新。
      在SQL Server 2016中,即便是当前表中改变的数据行还没有达到触统计信息更新阈值的条件(传统上所谓的阈值,500 rowcount*20%),
      统计信息依然会在查询的驱动下更新,通过索引上的统计信息可以看到,参考下图,直方图中生成了一个50的统计。

    此外DMV sys.system_internals_partition_columns的modified_count同样记录

        往表中插入超过原表10%的数据(已经达到触发统计信息更新阈值的情况),统计信息依旧没有更新

    1,对于取样的RANG_HI_Key值,比如216305,他给我预估了24.9295行

      上面说了,要使获取相对准确的统计信息,就要在更新统计信息时候的取样百分比,
      对于小表,即便按照其默认的变化阈值触发统计信息更新,或者是按照100%取样更新统计信息,都是没有问题,
      对于大表,一定要考虑在其达到默认触发统计信息更新的阈值之前人为更新这个统计信息,但是大表的100%取样统计是不太现实的(性能考虑)
      取样百分比越高,得到的统计信息越准确,但是代价越大,这就需要找一个平衡点,那么如果更新大表上的统计信息呢?
      如果是认为干预统计信息的生成,就要考虑两个因素:一是数据变化了多少之后更新?二是更新的时候,以什么样的取样来更新?
      我们知道,一个表的数据变化信息(增删改)记录在sys.sysindexes这个系统表的rowmodctr字段中,
      该表的统计信息更新之后,该字段清零,然后再次累积记录表上的数据变化。

      新葡亰496net 25

    Colmodctr(无需DAC)但不提供向上兼容.目前sql2012依然支持!

      新葡亰496net 26

    2,对于AVG_RANG_ROW,比如186302到216305之间的每个Id的行数,预估是197.4439行

      新葡亰496net 27

      下面就是所谓触发统计信息更新阈值的条件(严格说是该规则仅对SQL Server 2016之前的版本有效,不适应于SQL Server 2016)
        1,表格从没有数据变成有大于等于1条数据。
        2,对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后。
        3,对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500

     

       此时(关闭innodb_stats_auto_recalc的情况下),只有通过手动收集才能完成统计信息的更新

    观察比如上面默认的取样密度,这一次不管是RANG_HI_Key还是AVG_RANG_ROW得预估,都有不一个非常高的下降,开始趋于接近于真实的数据分布(每个Id有50行数据)

      这个信息非常好使,为人工更新统计信息提供了重要的依据,
      比如,对于1000W行的表,可以指定变化超过20W行(根据业务情况自定义)之后,手动更新统计信息,
      对于5000W行的表,可以指定变化超过60W行(根据业务情况自定义)之后,手动更新统计信息,
      同时根据不同的表,在相对较小的表上,指定相对较高的取样百分比,在相对较大的表上,指定相对较低的取样百分比
      比如对于1000W行的表,更新统计信息的时候取样百分比定位60%,对于5000W行的表,更新统计信息的时候取样百分比定位30%
      这样,可以自行决定数据变化了多少之后更新统计信息,以及动态地决定不同表的不同取样百分比,达到一个合理的目的。
      当然,最后强调一下,我说的每一个数据都是相对的,而不是绝对的,都是仅做参考,
      具体还要你自己结合自己的服务器软硬件以环境及维护窗口时间去尝试,一切没有死的标准。

    • (20%×表格数据总量)以后。
        这个说法,对于SQL Server 2016之前的版本是有效的,对于SQL Server 2016之后的版本是不成立的,我想这个还是值得注意的。

    Colmodctr记录规则:

      新葡亰496net 28

    整体上看,但是这个误差还是比较大的,如果继续提高采样密度,看看有什么变化?

     

    SQL Server 2016中统计信息更新策略相当于之前版本中开启了TraceFlag 2371,参考
    也即决定统计信息的变化值为动态的,不再拘泥于“数据累计变化量大于500 (20%×表格数据总量)”这一限制。
    除此之外,应该还要其他机制,比如这里的查询所触发的。

    Insert    每添加一行所有列 Colmodctr 1=Colmodctr

     

    新葡亰496net 29

    总结:统计信息的准确性对执行计划的生成有着至关重要的影响,本文粗略分析了统计信息的跟新规律以及要更新统计信息时候要注意的问题,
       在人为干预统计信息更新的时候,需要根据具体的情况(表数据流量,服务器软硬件环境,维护窗口期等)在效率与准确性之间作出合理的选择。

     

    Delete   每删除一行所有列Colmodctr 1=Colmodctr

     

     

    造成的问题

    Update  每修改一行 更新目标列Colmodctr 1=Colmodctr(sql2008 )

     

    指定一个采样密度的方式更新统计信息(70%采样) 

    为什么微软会在SQL Server 2016中将统计信息的更新策略做出如此的改变,以及为什么笔者会来探究这个问题?
    当然在实际业务中被这个问题坑的蛋疼。
    问题很明显,类似于测试的场景,在SQL Server 2012(包括之前的版本),这种预估策略存在的严重的缺陷。
    比如示例中:
    因为没有当前过滤谓词的统计信息(或者说没有收集到当前谓词的统计信息),实际为5000行的情况下,预估为1行。
    这种预估策略非常离谱,某种情况下会造成严重的性能问题,估计也很容易猜到,只是遇到的比较少罢了.
    下面就简单具体说明,会造成什么问题,以及原因。

            Sql2005更新目标列

    MySQL可以在表上指定一个统计信息取样的page个数,并且可以修改表上的统计取样page个数

     

      上述问题在什么情况下会造成性能问题,以及影响又多严重,这里仅简单举例说明。下面这个测试是在SQL Server 2012下进行的。
      为演示这个问题,先来做另外一张测试表B,并写入测试数据。

    *       * 当修改列为NOKEY columns时Colmodctr 1=Colmodctr

    -- 创建表的时候指定一个统计取样page数据
    create table testSamplePages
    (
        id int,
        name varchar(50)
    )ENGINE=InnoDB,
    STATS_PERSISTENT=1,
    STATS_AUTO_RECALC=1,
    STATS_SAMPLE_PAGES=25;
    
    --修改表的统计取样page数据
    ALTER TABLE testSamplePages
    STATS_SAMPLE_PAGES 60
    

    这一次用70%的采样密度,可以看到取样行数是55962290行

    create table B
    (
        IdentifierId    int identity(1,1),
        Id2                int,
        OtherCol        char(500)
    )
    GO
    
    begin tran
    declare @i int = 1
    while @i<=1000000
    begin
        insert into B values (@i,NEWID())
        set @i = @i 1
        if (@i0000)=0
        begin
            if @@TRANCOUNT>0
            begin
                commit
                begin tran
            end
        end
    end
    if @@TRANCOUNT>0
    begin
        commit
    end
    GO
    
    create index idx_2 on B(Id2)
    GO
    

            当修改列为KEY columns时Colmodctr 2=Colmodctr

     

    1,对于取样的RANG_HI_Key值,比如1978668,预估了71.15906行

    借助第二张表做一个测试,从而把错误预估行数造成的缺陷给放大,
    执行下面两个SQL,分别查询A.Id1 = 5和A.Id1 = 50的信息,
    由数据分布可知,查询总的结果总数会完全一样(截图受影响行数),
    虽然A.Id1 = 5和A.Id1 = 50的数据量和分布也完全一样,但是后者的逻辑IO远远超出前者。
    就是因为直方图中没有A.Id1 = 50的统计信息,A.Id1 = 50被错误地预估为1行造成的。

    Bulk Insert 与N Rows Insert类似

    2,对于AVG_RANG_ROW,比如1124024到1978668之间的每个Id,预估为61.89334行

        新葡亰496net 30

    Truncate table 与N Rows delete类似

    可以说,对于绝大多数值得预估(AVG_RANG_ROW),都愈发接近于真实值

      具体原因就很明了的,了解执行计划的同学应该很清楚。
      因为错误地预估了当前谓词过滤的行数,在A表上,采用索引查找的方式来查询数据,
      事实证明,当前情况下,这是比全表扫描更加低效的一种方式(看逻辑IO),这是其一。
      另外A表查询之后驱动B表的过程中,因为预估为一行,采用了Nested Loop的方式来驱动B表做连接,
      事实上当前情况下Nested Loop并非最好的,可以说是很不好的。
      这里也可以归结为统计信息的直方图中没有过滤谓词上的统计信息,在第一个阶段的预估中错误地估算为1行造成的。

    注: Modification Counters非事务(如当插入1000条数据,然后rollback. Colmodctr会加1000)

       新葡亰496net 31

      新葡亰496net 32

       过滤统计信息(filtered statistics)触发更新为整体数据区间而非过滤区间.一旦创建需人工维护

     

      这种问题更蛋疼的地方在于,检查Session或者缓存的执行计划的时候,会发现,表面上看,执行计划挺好的啊,都用到索引了。
      比如第二个SQL的执行计划,看起来似乎没问题,也容易直接忽略这个造成的问题,
      从而把重点转向其他地方,使得问题变得更加难以甄别。其实问题正是出在错误地使用了索引,不该使用索引的地方使用了索引。
      这就是执行计划第一步选择错误,造成后面每一步都错误的情况(一步错,步步错),实际情况中,SQL更加复杂,数据量也更大,造成的影响也更大。
      如果上述示例中在再多几张表join,会出现清一色的Nested Loop方式来驱动表连接,这样的话,SQL执行时间和逻辑IO是非常高的。

     

    指定一个采样密度的方式更新统计信息(100%采样)

      附上一个在SQL Server 2016下的测试截图,可见在默认情况下,执行计划做出了正确的选择。

    优化器应用统计信息.

     

      新葡亰496net 33

    优化器如何应用统计信息是一个比较复杂的方式.Sql Server在各版本之间应用方式甚至不尽相同.这里只做简单介绍.

    可以看到,取样行数等于总行数,也就是所谓的全部(100%)取样

      新葡亰496net 34

    优化器使用Statistics”偏好” 优先考虑最新的,Full Scan的统计信息

    看一下预估结果:

     最后: 

    简单介绍下等式单谓词预估.

    比如Id=3981622,预估是50行,3981622与4131988之间的Id的行数,预估为49.99874行,基本上等于真实数据分布

    1,本文不是说索引的,关于索引的就不多说。
    2,本文也的场景虽然不是太常见,稍显特殊,但也是实际遇到的,另外可以看出,微软也在从这个方面逐步改进SQL Server优化器更新统计信息的策略。
    3,关于此场景下的预估,在不同版本下,还有不少有意思问题没有抛出来,有机会再说。
    4,类似问题只有在数据量相对较大的情况下才能发生,如果是十万以下或者几十万的数据量,对数据库来说算是微小型数据量,类似问题对性能的影响完全体现不出来。
    5,如果有人根据本文的测试验证的话,请注意一个细节:对于过滤谓词的预估,分如下两种情况,这两种情况在2012和2014(2016)中预估的方式也是不同的
      1,表中确实没有这个谓词的数据,并且统计信息没有更新,比如Id1 = 50的数据为0行的情况下的预估
      2,表中有这个谓词的数据,同样是统计信息没有更新,比如Id1 = 50的数据为50000行的情况下的预估

    dbcc show_statistics('votes','IX_MultiColumn1')

    这个就不做过多解释了,基本上跟真实值是一样的,只是AVG_RANG_ROW有一点非常非常小的误差。

     

    select * from votes where topic_id=40

    新葡亰496net 35

     

    当谓词命中边界值时预估行数为EQ_ROWS

     

    总结:

    select * from votes where topic_id=10000

     取样密度高低与统计信息准确性的关系

    SQL Server 的预估对执行计划的生成有着至关重要的影响,而预估又依赖于统计信息,因此统计信息的更新以及准确性就显得尤为重要。鉴于此,SQL Server在每个版本中,对于统计信息的生成以及更新策略都有着比较大的变化,本文仅仅从一个较小的点出发,来验证SQL Server各个版本中统计信息预估以及更新的一些特点,从中发现类似问题可能产生的潜在的影响,以及SQL Server 2016中的一些改进。

    当谓词值在某个区间内,非命中边界值,预估值为AVG_Range_ROWS.即9042至16234间的所有谓词键值预估均为2.482208

     

     

    declare @topic_id int

      至于为什么默认取样密度和较低取样密度情况下,误差很大的情况我简单解释一下,也非常容易理解,
      因为“子表”中存储主表ID的ParentId值允许重复,在存在重复值的情况下,如果采样密度不够,极有可能造成“以偏概全”的情况
      比如对10W行数据取样1W行,原本10W行数剧中有2000个不重复的ParentId值,
      如果是10%的取样,在1W行取样数据中,因为密度不够大,只找到了20个不重复的ParentId值,
      那么就会认为每一行ParentId对应500行数据,这根实际的分布的每个ParentId有一个非常大的误差范围
      如果提高采样密度,那么这个误差就会越来越小。   

    set @topic_id =1000

    更新统计信息的时候,高比例的取样是否可取(可行) 

    select * from votes where topic_id=@topic_id

      因此在观察统计信息是否过期,决定更新统计信息的时候,一定要注意取样的密度,
      就是说表中有多少行数据,统计信息更新的时候取了多少采样行,密度有多高。
      当然,肯定有人质疑,那你说采样密度越高,也就是取样行数越高越准确,那么我就100%取样。
      这样行不行?
      还要分情况看,对于几百万或者十几万的小表来说,当然没有问题,这也是为什么数据库越小,表数据越少越容易掩盖问题的原因。
      对于大表,上亿的,甚至是十几亿的,你按照100%采样试一试?   

    当谓词为变量形式时,优化器不知道参数值.将采用 密度*行数的形式预估.

      举个实际例子:

    即topic_id=@topic_id 为0.000004936151*1943794=9.59486

      我这里对一个稍微大一点的表做个全表统计信息的更新,测试环境,服务器没负载,存储是比普通的机械硬盘要强很多的SAN存储
      采用full scan,也就是100%采样的更新操作,看一下,仅仅这一样表的update statistic操作就花费了51分钟
      试想一下,对一个数百GB甚至数TB的库来说,你敢这么搞一下。

    新葡亰496net 36

      新葡亰496net 37

     

     

    新葡亰496net 38

      扯一句,这个中秋节过的,折腾了大半天,话说做测试过程中电脑有开始有点卡,

     

      做完测试之后停掉SQLServer服务,瞬间内存释放了7个G,可见这些个操作还是比较耗内存的

    第三部分 维护

      新葡亰496net 39

    查看统计信息

      

    sp_autostats 'votesbak'---查看统计信息更新信息

     

    sp_helpstats 'votesbak','all' ---查看统计信息对应列/键值

    总结:

    dbcc show_statistics('votes','IX_MultiColumn1')—查看特定统计信息的详细内容

      本文通过对于某些场景下,在对较大的表的索引统计信息更新时,采样密度的分析,阐述了不同采样密度下,对统计信息预估的准确性的影响。

     

      当然对于小表,一些都好说。

    统计信息相关设置

      随着单表数据量的增加,统计信息的更新策略也要做相应的调整,

    AUTO_CREATE_STATISTICS         ----自动创建统计信息

      不光要看统计信息是否“过期”,更重要的是注意统计信息更新时究竟取样了全表的多少行数据做统计。

    AUTO_UPDATE_STATISTICS         ---自动更新统计信息

      对于大表,采用FULL SCAN或者100%采样往往是不可行的,这时候就需要做出权衡,做到既能准确地预估,又能够以合理的代价执行。

    AUTO_UPDATE_STATISTICS_ASYNC   --自动异步更新统计信息(优化器会用旧的统计信息,而不重编译,立即执行.)

     

    例子:

    ALTER DATABASE [BitautoReview2] SET AUTO_UPDATE_STATISTICS_ASYNC ON

    Trace Flag 2371       弹性根据条件更新统计信息

    统计信息操作

    Create Statistics  --创建统计信息

    Update Statistics  --更新统计信息

    Drop Statistics    --删除统计信息

    维护统计信息时可选择样本量来进行创建,更新

    update statistics [votes](IX_MultiColumn1)  with fullscan--手动更新指定索引的统计信息(一般针对特定统计信息问题时使用全扫描.更新时间较长可能影响性能)

    UPDATE STATISTICS  Votes WITH SAMPLE 10 Percent;-----手动更新全表统计信息,样本采样10%(一般针对更新整个对象时使用,样本量与精确程度依环境而定)

    UPDATE STATISTICS votes WITH ROWCOUNT =1000000,PAGECOUNT=100000---指定更新行数/页数(一般用于模拟当表数据量变得巨大时,优化器将采用何种执行计划)

     

    统计信息最佳实践

    AUTO_CREATE_STATISTICS , AUTO_UPDATE_STATISTICS一般无特殊应用自动打开.

    AUTO_UPDATE_STATISTICS_ASYNC当触发更新的表巨大,统计信息更新会明显影响当前性能,且旧的统计信息对原有更新无明显影响时应设置为ON

    Trace flag 2371根据相应环境设置

    由表变量造成的因无统计信息而使执行计划糟糕的情况用临时表代替

    只读库(Readonly)配置前应创建相应统计信息

    尽量避免变量,使用PROC传参形式,当使用动态SQL时用exec sp_executesql形式

    过滤统计信息应人为维护

    表达式(CTE)再参与Join的操作后,可能因执行逻辑无法获取准确统计信息,当出现此问题时用临时表或重写等方式代替

    当数据倾斜很大,造成参数嗅探等问题时,应创建过滤统计信息(过滤索引),或是用query Hint,重写逻辑处理等方式处理.

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:MySQL索引总计新闻更新相关的参数,

    关键词: