SQL調(diào)優(yōu)-表統(tǒng)計(jì)信息未及時(shí)更新導(dǎo)致查詢超級(jí)慢

某日同事丟給我一個(gè)看上去復(fù)雜的查詢（實(shí)際就涉及兩張表，套來(lái)套去）說(shuō)只是換了日期條件，但一個(gè)查詢5秒出數(shù)據(jù)，一個(gè)根本查不出來(lái)。現(xiàn)在整理下解決過(guò)程，及涉及的知識(shí)點(diǎn)。

若有不正之處，請(qǐng)多多諒解并歡迎批評(píng)指正，不甚感激。

一.問題描述

環(huán)境：sqlserver 2008r2

現(xiàn)象：查詢涉及到兩張表

ODS_TABLE_A   每日數(shù)據(jù)700萬(wàn)現(xiàn)在總計(jì)60多億。已建立索引+分區(qū)

MID_TABLE_B     每日數(shù)據(jù)20萬(wàn) 總計(jì)3000萬(wàn)。已建立索引未分區(qū)

當(dāng)etldate為 ‘2016-08-12’ 及以前的時(shí)間時(shí)，本查詢5秒出數(shù)據(jù)，

當(dāng)etldate為 ‘2016-08-16’ 及以后的時(shí)間時(shí)，本查詢出不來(lái)數(shù)據(jù)。

貼上問題sql：做過(guò)數(shù)據(jù)字段處理，針對(duì)本篇主題注意點(diǎn)放在查詢因?yàn)槿掌诘倪x擇不同導(dǎo)致查詢時(shí)間變的超級(jí)慢，而不是改變sql寫法比如用臨時(shí)表，強(qiáng)制索引上。

———-《代碼開始》

select
COUNT(distinct(case when COL_USERID3 is null then COL_USERID6 end)) as 'aa',
COUNT(distinct(case when COL_USERID3 is null and COL_USERID7 is not null then COL_USERID6 end)) as 'bb',
COUNT(distinct(case when COL_USERID3 is not null then COL_USERID6 end)) as 'cc',
COUNT(distinct(case when COL_USERID3 is not null and COL_USERID7 is not null then COL_USERID6 end)) as 'dd',
SUM(case when COL_USERID3 IS not null then ee end) as 'ee'
from
(
    select c.COL_USERID3,c.ee,g.COL_USERID6
    from
    (
        select  b.COL_USERID2 as COL_USERID3,COUNT(b.COL_USERID2) as ee
        from
        (
            select COL_USERID as COL_USERID1,min(EventTime) as time1
                from ODS_TABLE_A   
                where  EtlDate = '2016-08-12'
                    and colid LIKE 'heihei%'
                    group by COL_USERID
        )as a


         join
        (
            select COL_USERID as COL_USERID2,eventtime as time2
                from ODS_TABLE_A
                where EtlDate = '2016-08-12'
                    and ItemId = '1111111111101'
                    and colid like 'haha-%'
                    and colid not like 'haha-skill%'
                    and colid not like 'haha-fine%'
        )as b
        on a.COL_USERID1 = b.COL_USERID2 and  a.time1 > b.time2
        group by b.COL_USERID2
    )as c


    right join
    (
        select  DISTINCT d.COL_USERID4 as COL_USERID6
        from
        (        
            select distinct COL_USERID as COL_USERID4
            from MID_TABLE_B    
            where etldate = '2016-08-12'
        )as d
        join
        (
            select COL_USERID AS COL_USERID5
            from ODS_TABLE_A
            where  EtlDate = '2016-08-12'
                and colid LIKE 'heihei%'
        )as f
        on d.COL_USERID4 = f.COL_USERID5
    )as g
    on c.COL_USERID3 = g.COL_USERID6
)as i

left join
(
    select COL_USERID as COL_USERID7
    from MID_TABLE_B
    where EtlDate = '2016-08-12'
        and IsTodayPay = '1'
)as h
on i.COL_USERID6 = h.COL_USERID7

———-《代碼結(jié)束》

二。解決過(guò)程

1.先看了下上述代碼的執(zhí)行計(jì)劃如下圖初看上去需要用索引的地方都用到了。應(yīng)該沒啥大問題。

可能你注意到系統(tǒng)提示的缺少索引信息，加上去一樣效果，不能解決‘2016-08-16’ 查詢慢的問題。

2.在修改下日期 ，就是把 【所有】etldate=‘2016-08-12’  的改成  etldate=‘2016-08-16’

看下執(zhí)行計(jì)劃：對(duì)不起跑了半個(gè)小時(shí)沒出來(lái)，查看估計(jì)的執(zhí)行執(zhí)行和上面的圖類似。

減少涉及到數(shù)據(jù)集的量 加top 1 我再看執(zhí)行計(jì)劃：不貼圖了 結(jié)果就是比上面的圖少了個(gè)【并行度】

初步以為是優(yōu)化器因?yàn)楣烙?jì)行數(shù)等不準(zhǔn)的原因沒選擇并行度，趕緊找代碼讓它強(qiáng)行這樣走。

二話不說(shuō)加關(guān)鍵字OPTION(querytraceon 8649)，可是應(yīng)用到實(shí)際發(fā)現(xiàn)查詢效率無(wú)任何改善，久久不出結(jié)果。后來(lái)問宋大師（感謝宋大神）。他說(shuō)有些操作是沒法并行的，更新統(tǒng)計(jì)信息試試先。

執(zhí)行如下代碼：

update STATISTICS ODS_TABLE_A  –（把ODS_TABLE_A 這個(gè)大表統(tǒng)計(jì)信息更新）

默認(rèn)情況下，查詢優(yōu)化器已根據(jù)需要更新統(tǒng)計(jì)信息以改進(jìn)查詢計(jì)劃；但在某些情況下，你可以通過(guò)使用 UPDATE STATISTICS 或存儲(chǔ)過(guò)程 sp_updatestats 來(lái)比默認(rèn)更新更頻繁地更新統(tǒng)計(jì)信息，提高查詢性能。針對(duì)文中此種情況新插入的數(shù)據(jù)沒統(tǒng)計(jì)信息，大表自動(dòng)更新統(tǒng)計(jì)信息觸發(fā)自動(dòng)更新機(jī)制頻率不夠，最好定期更新。

至此問題解決。

三、總結(jié)

對(duì)于大表新插入的數(shù)據(jù)沒及時(shí)更新統(tǒng)計(jì)信息，導(dǎo)致出現(xiàn)上面文中的現(xiàn)象，一個(gè)日期導(dǎo)致查詢效率天壤之別的分水嶺（查12號(hào)前5秒出數(shù)據(jù)，查12號(hào)后死活不出來(lái)。）

解決辦法是大表自動(dòng)更新統(tǒng)計(jì)信息觸發(fā)自動(dòng)更新機(jī)制頻率不夠，定期更新。