SQL优化(1)

背景：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

执行计划：子表无法使用筛选索引