背景：StoreNotifyMainTask為主表，StoreNotifySubTask為子表，應用幾秒鐘關聯查詢一下，根據主、子表的條件查出top 100；

目前主表記錄數648W，單表符合條件的記錄647W（基本全部符合條件）

子表記錄數425W，單表符合條件的記錄106W

主表id列與子表maintaskid為邏輯主外鍵關系

由于子表條件固定，于是創建篩選索引

1 CREATE NONCLUSTERED INDEX [idxw_StoreNotifySubTask_RetryNum_yn_MainTaskId_inc] ON [dbo].[StoreNotifySubTask]2 (3     [RetryNum] ASC,4     [YN] ASC5 )6 INCLUDE ( [MainTaskId])7 WHERE ([RetryNum]<(3) AND [NotifyState]=(0) AND [yn]=(1))

初始的SQL如下：

 1 SELECT TOP 100 2         sub.Id , 3         sub.SubscriberId , 4         sub.MainTaskId , 5         sub.Pin , 6         sub.BlogPin , 7         sub.SkuId , 8         sub.SkuName , 9         sub.Wpid1 ,10         sub.Wpid2 ,11         sub.Wpid3 ,12         sub.Email ,13         sub.PhoneNo ,14         sub.PRice ,15         sub.SendPrice ,16         sub.RetryNum ,17         sub.AddressId ,18         sub.CreateTime ,19         ISNULL(sub.MessageTag, 0) AS MessageTag ,20         sub.UpdateTime ,21         sub.SendTime ,22         sub.NotifyState ,23         sub.YN ,24         sub.Ext ,25         sub.SkuPicUrl ,26         sub.SubscriberTime27 FROM    StoreNotifySubTask sub WITH ( NOLOCK) 28         INNER JOIN StoreNotifyMainTask main ( NOLOCK ) ON sub.MainTaskId = main.Id29 WHERE   main.TaskState = 230         AND main.YN = 131         AND sub.NotifyState = 032         AND sub.RetryNum < 333         AND sub.YN = 1

執行計劃：子表無法使用篩選索引