數(shù)據(jù)庫(kù)表太大造成的查詢效率低下、SQL優(yōu)化壓縮表提高查詢效率

今天收到一個(gè)同事的問(wèn)題，有一段SQL跑了很久很久，根本沒(méi)有結(jié)果，根據(jù)同事的反映，這個(gè)SQL一個(gè)月比一個(gè)月要慢。這是不被允許的事情，我們要做的就是對(duì)這個(gè)SQL進(jìn)行一次優(yōu)化。下面就是這次優(yōu)化的記錄。

首先說(shuō)SQL：

select t.month_id,
       t1.area_id,
       t1.local_id,
       count(distinct case
               when t.type_id = '02' and t.valid_flag = 1 and
                    t3.trade_id = '1008601' then
                t.user_id
               else
                null
             end),
       count(distinct case
               when t.type_id = '02' and t.valid_flag = 1 and
                    t3.trade_id = '1008602' then
                t.user_id
               else
                null
             end)
        from product_flag_m t,
        ... --省略部分都是類似上面的運(yùn)算，很多，為了節(jié)省篇幅都取消了
        left join VW_CODE_LOCALNET t1
          on t.local_id = t1.root_local_id
        LEFT JOIN TRADE_LIST T3
      ON T.id2 = T3.id2
     AND T3.trade_id IN ('1008601', '1008602')
       where t.month_id = '201212'
       group by t.month_id, t1.area_id, t1.local_id;

這段代碼隱藏了敏感信息，可能會(huì)有一些修改的時(shí)候錯(cuò)漏的問(wèn)題。

接下來(lái)就是比較老的套路了，查看這段SQL的執(zhí)行計(jì)劃：

這個(gè)時(shí)候可以初步判斷是因?yàn)?strong>product_flag_m表太大造成的查詢效率低下。既然只需要12月的數(shù)據(jù)，那么我自然而然的想到了將12月的分區(qū)壓縮一下，利用壓縮表的特點(diǎn)進(jìn)行查詢效率的提高。但是這是張生產(chǎn)表，不能隨便操作，于是我就將12月份的type_id='02'的數(shù)據(jù)單獨(dú)抽取出來(lái)形成一張新的表，當(dāng)然這張表是壓縮過(guò)的，而且我抽取的時(shí)候只抽取自己需要的字段，這樣做的好處是盡量減少數(shù)據(jù)量，減輕數(shù)據(jù)庫(kù)的負(fù)擔(dān)。

下面就是使用了壓縮表之后的執(zhí)行計(jì)劃：

可以看到COST是有所降低，但是這個(gè)和沒(méi)有降低沒(méi)什么區(qū)別。還是面臨執(zhí)行不出來(lái)的問(wèn)題。

這個(gè)時(shí)候我注意到了ID=2的這一部執(zhí)行計(jì)劃。在id=3的hash join right outer之后，不管是COST還是BYTES都是在一個(gè)比較正常的水平之內(nèi)的，那么問(wèn)題就應(yīng)該出在TRADE_LIST這個(gè)表上。

這個(gè)表是一張編碼表，本身并不大，但是注意這里：

上圖所示應(yīng)該就是罪魁了。于是我想到了，既然最后需要過(guò)濾一下trade_id，那么為什么不直接就用一張只有trade_id為1008601和1008602的表呢？

于是我鬼使神差的建立了一張視圖，這個(gè)視圖就是只取了上面說(shuō)的那么多數(shù)據(jù)，然后替換掉原來(lái)的SQL中的TRADE_LIST，刪除了其中的

AND T3.trade_id IN ('1008601', '1008602') 語(yǔ)句，再看執(zhí)行計(jì)劃：

這個(gè)效果就非常好了。

我本身很擔(dān)心這個(gè)視圖用了以后會(huì)影響查詢結(jié)果集。于是我自己造了一張表做了一個(gè)小測(cè)試。test3中有object_id為2， 3， 4， 5， 6， 7的記錄，編碼表中只有id為2， 3, 4， 5， 6的編碼記錄，SQL如下：

select t1.object_id, t2.id, t2.name
  from test3 t1
  left join test4 t2
  on t1.object_id = t2.id
  and t2.id in (2, 3);

這個(gè)結(jié)果有48行。制造一個(gè)視圖：

create view test5 as select * from test4 where id in (2, 3)

 然后替換成視圖：

select t1.object_id, t2.id, t2.name
  from test3 t1
  left join test5 t2
  on t1.object_id = t2.id;

結(jié)果還是48行。也就是說(shuō)這個(gè)方法是可行的。

這樣的話，如果在原來(lái)的SQL上加上并行提示，效果會(huì)更好。經(jīng)過(guò)我的實(shí)際測(cè)試，3分鐘以內(nèi)就跑出了所有的結(jié)果。

或許會(huì)有人問(wèn)我，為什么不加上索引？我并不是反對(duì)加索引，我不習(xí)慣使用索引的習(xí)慣是因?yàn)槲覀兊默F(xiàn)實(shí)環(huán)境所限，我們的磁盤空間基本上每隔一段時(shí)間就會(huì)滿，所以我沒(méi)辦法隨心所欲的添加會(huì)占用空間的索引，而是更傾向于使用壓縮表，節(jié)省表空間。而且，id2字段進(jìn)行關(guān)聯(lián)的時(shí)候有一個(gè)隱式類型轉(zhuǎn)換，這個(gè)字段起碼沒(méi)有辦法加索引。至于其他字段，我沒(méi)辦法實(shí)驗(yàn)，如果有機(jī)會(huì)，可以做個(gè)實(shí)驗(yàn)試試。