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

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

首先說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,
        ... --省略部分都是類似上面的運算，很多，為了節(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;

這段代碼隱藏了敏感信息，可能會有一些修改的時候錯漏的問題。

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

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

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

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

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

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

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

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

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

這個效果就非常好了。

我本身很擔(dān)心這個視圖用了以后會影響查詢結(jié)果集。于是我自己造了一張表做了一個小測試。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);

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

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行。也就是說這個方法是可行的。

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

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