您的位置:新葡亰496net > 网络数据库 > 统计信息更新时采样百分比对数据预估准确性的

统计信息更新时采样百分比对数据预估准确性的

发布时间:2019-11-09 07:57编辑:网络数据库浏览(141)

     

      

    当数据库设置为自动更新统计后,SQL Server 监控表中的数据更改,当更改满足一下条件之一时更新:
    1.向空表插入数据时
    2.少于500行的表增加500行或者更多
    3.当表中行多于500行时,数据的变化量大于20%时
    (在SQL SERVER 2000中,指的是20%的行被修改,而在SQL SERVER 2005/2008中,指的是20%的列数据被修改)

    可以手动使用UPDATE STATISTICS 或EXEC sys.sp_updatestats来更新统计
    UPDATE STATISTICS 需要锁表
    UPDATE STATISTICS table_or_indexed_view_新葡亰496net,name
        [
            {
                { index_or_statistics__name }
              | ( { index_or_statistics_name } [ ,...n ] )
                    }
        ]
        [    WITH
            [
                FULLSCAN
                | SAMPLE number { PERCENT | ROWS }
                | RESAMPLE
                | <update_stats_stream_option> [ ,...n ]
            ]
            [ [ , ] [ ALL | COLUMNS | INDEX ]
            [ [ , ] NORECOMPUTE ]
        ] ;

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

     

    本文出处: 

    为什么要写统计信息

    <update_stats_stream_option> ::=
        [ STATS_STREAM = stats_stream ]
        [ ROWCOUNT = numeric_constant ]
        [ PAGECOUNT = numeric_contant ]
    table_or_indexed_view_name
    要更新其统计信息的表或索引视图的名称。

    第一部分 概念

    MySQL统计信息相关的参数:

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

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

    index_or_statistics_name
    要更新其统计信息的索引的名称,或要更新的统计信息的名称。如果不指定 index_or_statistics_name,则查询优化器将更新表或索引视图的所有统计信息。 这包括使用 CREATE STATISTICS 语句创建的统计信息、在

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

     

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

    统计信息基础

    AUTO_CREATE_STATISTICS 为 ON 时创建的单列统计信息以及为索引创建的统计信息。
    有关 AUTO_CREATE_STATISTICS 的详细信息,请参阅 ALTER DATABASE SET
    选项 (Transact-SQL)。若要查看某一表或视图的所有索引,您可以使用 sp_helpindex。

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

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

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

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

    FULLSCAN
    通过扫描表或索引视图中的所有行来计算统计信息。FULLSCAN 和 SAMPLE 100 PERCENT
    的结果相同。 FULLSCAN 不能与 SAMPLE
    选项一起使用。

    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%×表格数据总量)以后。

    SAMPLE number { PERCENT | ROWS }
    指定当查询优化器更新统计信息时要为其使用的表或索引视图中近似的百分比或行数。对于 PERCENT,number 可以介于 0 到 100 之间;对于 ROWS,number 可以介于 0
    到总数行之间。 查询优化器抽样的实际行百分比或行数可能与指定的行百分比或行数不匹配。 例如,查询优化器扫描数据页上的所有行。 对于基于默认抽样的查询计划并非最佳的特殊情况,SAMPLE 非常有用。在大多数情况下,不必指定

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

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

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

    新葡亰496net 1

    SAMPLE,这是因为在默认情况下,查询优化器根据需要采用抽样,并以统计方式确定大量样本的大小,以便创建高质量的查询计划。
    SAMPLE 不能与 FULLSCAN 选项一起使用。如果未指定 SAMPLE 和 FULLSCAN,查询优化器则默认使用抽样数据并计算样本大小。
    我们建议不指定 0 PERCENT 或 0 ROWS。如果指定 0 PERCENT 或 0 ROWS,则将更新统计信息对象,但该对象不包含任何统计信息数据。

    DBCC show_statistics(object_name,Column_name)

      2. innodb_stats_auto_recalc

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

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

    RESAMPLE
    使用最近的采样速率更新每个统计信息。
    使用 RESAMPLE 会导致全表扫描。例如,索引的统计信息使用全表扫描来获取其采样速率。 如果未指定采样选项(SAMPLE、FULLSCAN、RESAMPLE),则查询优化器默认将对数据进行抽样并计算样本大小。
    ALL | COLUMNS | INDEX
    更新所有现有统计信息、在一列或多列上创建的统计信息或为索引创建的统计信息。如果未指定上述任何选项,则 UPDATE STATISTICS
    语句将更新表或索引视图上的所有统计信息。

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

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

    新葡亰496net 2

    新葡亰496net 3

    NORECOMPUTE
    为指定统计信息禁用自动统计信息更新选项

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

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

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

     

    AUTO_UPDATE_STATISTICS。如果指定此选项,则查询优化器将完成此统计信息更新并禁用将来的更新。
    若要重新启用 AUTO_UPDATE_STATISTICS 选项行为,请不使用
    NORECOMPUTE 选项再次运行 UPDATE STATISTICS,或运行 sp_autostats。  

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

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

     

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

    --================================================================

     

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

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

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

    同事扔过一条SQL,跟我说生产服务器上执行得好好的,测试环境很慢,问我是不是有阻塞,执行一看,的确很慢,一分钟还没有结束,运行查看阻塞的语句,发现没有阻塞,但是逻辑读特别高,使用SET STATISTICS IO ON,发现逻辑读集中某两张表上,查看实际执行计划,发现预估执行次数和实际执行次数相差上万倍,于是毫不犹豫执行EXEC sys.sp_updatestats,更新统计结束后,原来执行超过1分钟的查询不到1秒结束

    Histogram(直方图)

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

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

     

    看来定期更新统计还是很有必要的。

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

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

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

    新葡亰496net 4

    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 5

     

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

      6. innodb_stats_sample_pages

    2,update statistics

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

    DISTINCT_RANGE_ROWS: RANGE_ROWS中的非重复值数量

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

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

    下面开始本文的主题:

    AVG_统计信息更新时采样百分比对数据预估准确性的影响,统计信息维护策略。RANGE_ROWS:直方图段内值得平均行数(不包括上限)

     

    --默认方式更新表上的所有统计信息
    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
    

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

                     DISTINCT_RANGE_ROWS > 0则为RANGE_ROWS / DISTINCT_RANGE_ROWS

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

      相对于重建或者重组索引,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分钟(当然这是一个大表,上面有多个索引统计信息以及非索引统计信息)。如果有数十张类似的表,效率可想而知
      总之就是,没有一个固定的方式,数据库不大,怎么做问题都不大,数据库一大,加上维护的窗口期时间有限,要在统计信息的质量和维护效率上综合考虑

    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
    

    新葡亰496net 6

     

      新葡亰496net 7

     

     

    新葡亰496net 8

     

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

    第二部分 原理,应用

     

    3,数据库级别的sp_updatestats

     

     

    查询统计信息更新时间

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

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

    统计信息更新

    新葡亰496net 9

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

    * *

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

     

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

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

    触发条件

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

      如果未指定 'resample',则 sp_updatestats 将使用默认的抽样来更新统计信息。 
    统计信息更新时采样百分比对数据预估准确性的影响,统计信息维护策略。  默认值为 NO。

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

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

    新葡亰496net 10

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

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

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

      

     

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

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

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

    4,TraceFlag 2371

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

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

      新葡亰496net 11

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

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

        表变量无统计信息

     

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

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

     

     

      新葡亰496net 12

    新葡亰496net 13

    关于记录更新标识(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

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

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

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

      新葡亰496net 14

     

     

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

     

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

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

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

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

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

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

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

      新葡亰496net 15

      新葡亰496net 16

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

     

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

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

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

    Colmodctr记录规则:

      新葡亰496net 17

     

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

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

     

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

    新葡亰496net 18

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

     

     

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

     

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

            Sql2005更新目标列

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

     

    *       * 当修改列为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行

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

     

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

    Bulk Insert 与N Rows Insert类似

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

    Truncate table 与N Rows delete类似

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

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

       新葡亰496net 19

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

     

     

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

    优化器应用统计信息.

     

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

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

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

    看一下预估结果:

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

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

    dbcc show_statistics('votes','IX_MultiColumn1')

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

    select * from votes where topic_id=40

    新葡亰496net 20

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

     

    select * from votes where topic_id=10000

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

    当谓词值在某个区间内,非命中边界值,预估值为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 21

      新葡亰496net 22

     

     

    新葡亰496net 23

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

     

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

    第三部分 维护

      新葡亰496net 24

    查看统计信息

      

    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发布于网络数据库,转载请注明出处:统计信息更新时采样百分比对数据预估准确性的

    关键词:

上一篇:SQL面试笔试优质题,优秀面试题及答案

下一篇:没有了