SQL Where 條件中,有很多的 OR 查詢效能問題

我們在取 Workflow 的待辦事項時,會從 Work 的待辦資料中,依這個使用者的代號、部門主管、角色或是部門 + 角色的條件來取得這個人的待辦資料。

所以整個 SQL 中 Where 條件中會有很多的 OR,像這種很多 OR 的查詢效能要如何解決呢?

整個 SQL 類似如下,

SELECT   …..
FROM    WORKLIST_IV WITH ( READPAST )
WHERE   ( ( PTCP_KIND = N'1'
            AND PTCP_COMP_ID = N655AND PTCP_USR = N'S122484051'
          )
          OR ( ( PTCP_KIND = N'3'
                 AND PTCP_DOMAIN_ID = N'GSS'
                 AND PTCP_COMP_ID = N655’
               )
               AND ( (PTCP_OU = N'379132200C2202'
                     AND PTCP_RELKIND = N'1')
                   )
             )
          OR ( ( PTCP_KIND = N'2'
                 AND PTCP_DOMAIN_ID = N'GSS'
                 AND PTCP_COMP_ID = N655’
               )
               AND ( ( PTCP_ROL = N'ODDeskUsr_Rol_4'
                       AND PTCP_RELKIND = N'1'
                     )
                     OR ( PTCP_ROL = N'ODDeskUsr_Rol_4_379132200C2202'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_ROL = N'SWS_OuExamRol'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_ROL = N'SWS_OuExamRol_379132200C22AA'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_ROL = N'SWSProcesser_Rol'
                          AND PTCP_RELKIND = N'1'
                        )
                   )
             )
          OR ( ( PTCP_KIND = N'5'
                 AND PTCP_DOMAIN_ID = N'GSS'
                 AND PTCP_COMP_ID = N655’
               )
               AND ( ( PTCP_OU = N'379132200C2202'
                       AND PTCP_ROL = N'ODDeskUsr_Rol_4'
                       AND PTCP_RELKIND = N'1'
                     )
                     OR ( PTCP_OU = N'379132200C2202'
                          AND PTCP_ROL = N'ODDeskUsr_Rol_4_379132200C2202'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_OU = N'379132200C2202'
                          AND PTCP_ROL = N'SWS_OuExamRol'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_OU = N'379132200C2202'
                          AND PTCP_ROL = N'SWS_OuExamRol_379132200C22AA'
                          AND PTCP_RELKIND = N'1'
                        )
                     OR ( PTCP_OU = N'379132200C2202'
                          AND PTCP_ROL = N'SWSProcesser_Rol'
                          AND PTCP_RELKIND = N'1'
                        )
                   )
             )
        );

執行計畫如下,

疑,這麼多的 OR,依以往的經驗,就將 Where 都加到 index 之中,如下,

CREATE NONCLUSTERED INDEX IDX_WKITEM_PTCP_I_RM
ON [dbo].[WKITEM_PTCP_I] ([PTCP_COMP_ID],[PTCP_KIND],[PTCP_USR],[PTCP_ROL],[PTCP_ACTN_STATE])
INCLUDE ([WKITEM_ID],[PTCP_ID],[PTCP_NAME],[PTCP_DESC],[PTCP_DOMAIN_ID],[PTCP_OU],[PTCP_GRP],[PTCP_RELKIND],[PTCP_PRO_STATE],[PTCP_RECV_TIME],[PTCP_COMM])

結果執行計畫好不到那裡去,如下,

— 請先將前面建立的這個 index drop掉哦!​

那是不是不要拆 SQL 的寫法,分別針對 OR 去用各別的 SQL ,再 union 起來呢?

後來公司的天空大大,分別建立了3個 index 後就成功化解了這個一次會先將一堆資料取出來後,再透過 篩選 出資料的問題。以下筆者就來分享 Sky 成功化解這個 Where 很多 OR 的問題。

先依每個 OR 中,有相同欄位的部份來建立各別的 index ,如下,

所以我們可以建立 4 個 index, 如下,

CREATE INDEX IX_WKITEM_PTCP_USR ON WKITEM_PTCP_I(PTCP_KIND, PTCP_USR, PTCP_COMP_ID) 
CREATE INDEX IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND) 
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND) 
CREATE INDEX IX_WKITEM_PTCP_OU_2 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL)

建立完成後,再執行SQL,看一下執行計畫為何,如下,

成功的化解掉先取出一堆資料後再「篩選」的問題,只是有些 index 會有 索引鍵查閱(key lookup)。

要解 索引鍵查閱(key lookup)的問題,我們可以使用 Include Index 。所以我們可以查看原本那些 OR 中,那些非相同的欄位,將它們加到 Include Index 之中,如下,

所以 index 改成如下,

--其他的 OR 還有其他的 WHERE 條件,也放在 INCLUDE 之中 
CREATE INDEX IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND) 
INCLUDE  (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND) 
INCLUDE  (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_OU_2 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL) 
INCLUDE  ( PTCP_DOMAIN_ID, PTCP_COMP_ID);

執行計畫如下,

這樣我們就解掉了 索引鍵查閱(key lookup)的問題,但執行計畫之前可以發現,index 並沒有用到 IX_WKITEM_PTCP_OU_1 ,只有用到了 IX_WKITEM_PTCP_OU_2。

所以我們可以將 IX_WKITEM_PTCP_OU_1 刪掉,並將 IX_WKITEM_PTCP_OU_2 改成 IX_WKITEM_PTCP_OU,如下,

drop index IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I;
EXEC sp_rename N'WKITEM_PTCP_I.IX_WKITEM_PTCP_OU_2', N'IX_WKITEM_PTCP_OU', N'INDEX';

再看一下執行計畫, IX_WKITEM_PTCP_OU_2 的名稱就改成了 IX_WKITEM_PTCP_OU,如下,

如果加入 include index 欄位後,還是有 key lookup 的話,可以再看一下那個 View 中那個 Table 的 Join Key 哦!

註:本篇是依 天空大大建議的 index , 筆者依公司DB狀況測試調整,各位調整時也請依實際環境狀況調整哦!

所以最後新增的index 有3個,如下,

CREATE INDEX IX_WKITEM_PTCP_USR ON WKITEM_PTCP_I(PTCP_KIND, PTCP_USR, PTCP_COMP_ID)
--其他的 OR 還有其他的 WHERE 條件,也放在 INCLUDE 之中 
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND) 
INCLUDE  (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_OU ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL) 
INCLUDE  (PTCP_DOMAIN_ID, PTCP_COMP_ID);

 

作者: 亂馬客

亂馬客 @叡揚資訊 rainmaker_ho@gss.com.tw https://rainmakerho.github.io/ https://www.slideshare.net/rainmakerho

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *