一周工作總結–一次SQL最佳化記錄

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

     首先說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,
        ... --省略部分都是類似上面的運算，很多，為了節省篇幅都取消了        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的執行計畫：

      

      這個時候可以初步判斷是因為product_flag_m表太大造成的查詢效率低下。既然只需要12月的資料，那麼我自然而然的想到了將12月的分區壓縮一下，利用壓縮表的特點進行查詢效率的提高。但是這是張生產表，不能隨便操作，於是我就將12月份的type_id='02'的資料單獨抽取出來形成一張新的表，當然這張表是壓縮過的，而且我抽取的時候只抽取自己需要的欄位，這樣做的好處是盡量減少資料量，減輕資料庫的負擔。

     下面就是使用了壓縮表之後的執行計畫：

     

       可以看到COST是有所降低，但是這個和沒有降低沒什麼區別。還是面臨執行不出來的問題。

       這個時候我注意到了ID=2的這一部執行計畫。在id=3的hash join right outer之後，不管是COST還是BYTES都是在一個比較正常的水平之內的，那麼問題就應該出在TRADE_LIST這個表上。

       這個表是一張編碼錶，本身並不大，但是注意這裡：

       

       所示應該就是罪魁了。於是我想到了，既然最後需要過濾一下trade_id，那麼為什麼不直接就用一張只有trade_id為1008601和1008602的表呢？

       於是我鬼使神差的建立了一張視圖，這個視圖就是只取了上面說的那麼多資料，然後替換掉原來的SQL中的TRADE_LIST，刪除了其中的

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

       

        這個效果就非常好了。

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

       這個結果有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;

      結果還是48行。也就是說這個方法是可行的。

      這樣的話，如果在原來的SQL上加上並行提示，效果會更好。經過我的實際測試，3分鐘以內就跑出了所有的結果。

      或許會有人問我，為什麼不加上索引？我並不是反對加索引，我不習慣使用索引的習慣是因為我們的現實環境所限，我們的磁碟空間基本上每隔一段時間就會滿，所以我沒辦法隨心所欲的添加會佔用空間的索引，而是更傾向於使用壓縮表，節省資料表空間。而且，id2欄位進行關聯的時候有一個隱式類型轉換，這個欄位起碼沒有辦法加索引。至於其他欄位，我沒辦法實驗，如果有機會，可以做個實驗試試。