SQL优化(1)

 

 

背景：StoreNotifyMainTask为主表，StoreNotifySubTask为子表，应用几秒钟关联查询一下，根据主、子表的条件查出top 100；

         目前主表记录数648W，单表符合条件的记录647W（基本全部符合条件）

         子表记录数425W，单表符合条件的记录106W

         主表id列与子表maintaskid为逻辑主外键关系

 

         由于子表条件固定，于是创建筛选索引

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

初始的SQL如下：

SELECT TOP 100
        sub.Id ,
        sub.SubscriberId ,
        sub.MainTaskId ,
        sub.Pin ,
        sub.BlogPin ,
        sub.SkuId ,
        sub.SkuName ,
        sub.Wpid1 ,
        sub.Wpid2 ,
        sub.Wpid3 ,
        sub.Email ,
        sub.PhoneNo ,
        sub.Price ,
        sub.SendPrice ,
        sub.RetryNum ,
        sub.AddressId ,
        sub.CreateTime ,
        ISNULL(sub.MessageTag, 0) AS MessageTag ,
        sub.UpdateTime ,
        sub.SendTime ,
        sub.NotifyState ,
        sub.YN ,
        sub.Ext ,
        sub.SkuPicUrl ,
        sub.SubscriberTime
FROM    StoreNotifySubTask sub WITH ( NOLOCK) 
        INNER JOIN StoreNotifyMainTask main ( NOLOCK ) ON sub.MainTaskId = main.Id
WHERE   main.TaskState = 2
        AND main.YN = 1
        AND sub.NotifyState = 0
        AND sub.RetryNum < 3
        AND sub.YN = 1

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

 

        

(0 行受影响)

表'StoreNotifyMainTask'。扫描计数0，逻辑读取4333270 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'StoreNotifySubTask'。扫描计数1，逻辑读取209314 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

SQL Server 执行时间:

   CPU 时间= 10592 毫秒，占用时间= 11298 毫秒。

 

强制使用筛选索引，导致子表逻辑读上升；

 

(0 行受影响)

表'StoreNotifyMainTask'。扫描计数0，逻辑读取4333270 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'StoreNotifySubTask'。扫描计数1，逻辑读取4338240 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

SQL Server 执行时间:

   CPU 时间= 11965 毫秒，占用时间= 12014 毫秒。

 

优化思路：尽管子表中单表符合条件的记录有106W，但按照maintaskid分组查询发现，一个maintaskid有大量的子记录，实际符合条件的maintaskid只有130个左右

于是，先从子表中查询符合条件的的maintaskid（做group by），然后验证这些maintaskid在主表中是否符合条件，再回到子表中按照最终筛选的maintaskid做自关联；

由于业务逻辑上可能出现子表中同一个maintaskid也包含不符合条件的记录，因此最后一步的按照maintaskid的子表自关联，还需要加上之前子表的条件作为限定；

 

 

优化后的SQL：

; WITH cte AS (    
   SELECT sub.MainTaskId
   FROM    StoreNotifySubTask sub WITH ( NOLOCK )
WHERE sub.NotifyState = 0
        AND sub.RetryNum < 3
        AND sub.YN = 1
GROUP BY MainTaskId
)
,cte1 AS(
SELECT  MainTaskId FROM cte where
EXISTS(SELECT id FROM StoreNotifyMainTask main ( NOLOCK ) WHERE  cte.MainTaskId = main.Id AND  main.TaskState = 2
        AND main.YN = 1)
)
select TOP 100
        sub.Id ,
        sub.SubscriberId ,
        sub.MainTaskId ,
        sub.Pin ,
        sub.BlogPin ,
        sub.SkuId ,
        sub.SkuName ,
        sub.Wpid1 ,
        sub.Wpid2 ,
        sub.Wpid3 ,
        sub.Email ,
        sub.PhoneNo ,
        sub.Price ,
        sub.SendPrice ,
        sub.RetryNum ,
        sub.AddressId ,
        sub.CreateTime ,
        ISNULL(sub.MessageTag, 0) AS MessageTag ,
        sub.UpdateTime ,
        sub.SendTime ,
        sub.NotifyState ,
        sub.YN ,
        sub.Ext ,
        sub.SkuPicUrl ,
        sub.SubscriberTime
FROM    StoreNotifySubTask sub WITH ( NOLOCK ) JOIN cte1 ON sub.MainTaskId=cte1.maintaskid
WHERE sub.NotifyState = 0
        AND sub.RetryNum < 3
        AND sub.YN = 1

 

上述SQL中，可以将cte和cte1合并，改为：

; WITH cte AS (    
   SELECT sub.MainTaskId
   FROM    StoreNotifySubTask sub WITH ( NOLOCK )
WHERE sub.NotifyState = 0
        AND sub.RetryNum < 3
        AND sub.YN = 1
        AND EXISTS(SELECT id FROM StoreNotifyMainTask main ( NOLOCK ) WHERE  sub.MainTaskId = main.Id AND  main.TaskState = 2
        AND main.YN = 1)
GROUP BY MainTaskId
)

 

对比执行结果：

优化前：

(0 行受影响)

表'StoreNotifyMainTask'。扫描计数0，逻辑读取4333270 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'StoreNotifySubTask'。扫描计数1，逻辑读取209361 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

SQL Server 执行时间:

   CPU 时间= 12465 毫秒，占用时间= 12599 毫秒。

 

 

优化后：

(0 行受影响)

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'StoreNotifySubTask'。扫描计数1，逻辑读取4970 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'StoreNotifyMainTask'。扫描计数0，逻辑读取537 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

 

SQL Server 执行时间:

   CPU 时间= 312 毫秒，占用时间= 323 毫秒。

 

 

执行时间、IO，均明显下降；