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

整個 SQL 類似如下，

SELECT   …..

FROM    WORKLIST_IV WITH ( READPAST )

WHERE   ( ( PTCP_KIND = N'1'            AND PTCP_COMP_ID = N’655’

            AND PTCP_USR = N'S122484051'          )

          OR ( ( PTCP_KIND = N'3'                 AND PTCP_DOMAIN_ID = N'GSS'                 AND PTCP_COMP_ID = N’655’

               )

               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 = N’655’

               )

               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 = N’655’

               )

               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);