背景：StoreNotifyMainTask為主表，StoreNotifySubTask為子表，應(yīng)用幾秒鐘關(guān)聯(lián)查詢一下，根據(jù)主、子表的條件查出top 100；

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

子表記錄數(shù)425W，單表符合條件的記錄106W

主表id列與子表maintaskid為邏輯主外鍵關(guān)系

由于子表?xiàng)l件固定，于是創(chuàng)建篩選索引

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

執(zhí)行計(jì)劃：子表無法使用篩選索引