SQL sever-not in子查询优化(left join)

作者: zxws1009 | 来源:发表于2020-09-01 13:42 被阅读0次

SQL sever-not in子查询优化(left join)
6、SQL优化手段有哪些
连接查询
SQL inner join和left join on
left join,right join,inner join,
几个复杂查询语句相关内容
SQL语句使用小技巧
关于left join
mysql join
innerjoin

场景概述：系统中有一个自动推送人脸识别的任务，但是在运行的时候，，因为数据量较大，在定时任务执行过程中IO非常慢，导致数据库存取出了问题，导致部分member未被推送，因此需要找到哪些应该被推送但是没被推送的，手动插入一个记录；

系统中需要进行人脸识别的member：

select a.*   from Members a
            left join users b on a.UserId=b.id
            left join Dealers c on a.DealerId=c.id
            where a.Status=1 and b.UserType=1 and c.DealerType=2 
            and c.Name COLLATE Chinese_PRC_CS_AS_WS  like '%'+( SELECT  SUBSTRING ( Name, LEN(Name)-3, 4 )   FROM dbo.Dealers WHERE Id=1243)+'%'

定时任务已推送给member人脸识别记录

select * from
  (select *, ROW_NUMBER() OVER (PARTITION BY  memberid order by [IsSuccess] desc) as RowNumber from [MemberFaceIDDetectRecords] where CreatedDate > '2020.08.31'
) tb where RowNumber = 1

查出未被推送的member
现在问题来了，最开始的sql 使用的是 not in 子查询，运行很慢，原因很明显，这个查询的次数是m*n，效率很低；

其实可以使用left join语法代替

select * from
 (
 select a.Id as memberid, a.*   from Members a
            left join users b on a.UserId=b.id
            left join Dealers c on a.DealerId=c.id
            where a.Status=1 and b.UserType=1 and c.DealerType=2 
            and c.Name COLLATE Chinese_PRC_CS_AS_WS  like '%'+( SELECT  SUBSTRING ( Name, LEN(Name)-3, 4 )   FROM dbo.Dealers WHERE Id=1243)+'%'
 ) tb1
LEFT JOIN 
 (select * from 
   (select *, ROW_NUMBER() OVER (PARTITION BY  memberid order by [IsSuccess] desc) as RowNumber from [MemberFaceIDDetectRecords] where CreatedDate > '2020.08.31'
   ) tb where RowNumber = 1
 ) tb2
ON tb1.memberid=tb2.memberid 
where tb2.memberid IS NULL

插入推送记录
为未被推送的人新插入一条记录：

begin tran

INSERT INTO [dbo].[MemberFaceIDDetectRecords]
select memberid, 
0 as [IsSuccess],
0 as [IsValidTask],
'2020-08-31 12:55:55' as [StartDate],
'2020-09-05 12:55:55' as [EndDate],
'2020-08-31 12:55:55' as [CreatedDate],
0 as [CreatedBy],
NULL AS [UpdatedDate],
NULL AS [UpdatedBy],
0 [TriedCount]
from
(select tb1.* from
    (
    select a.Id as memberid, a.*   from Members a
            left join users b on a.UserId=b.id
            left join Dealers c on a.DealerId=c.id
            where a.Status=1 and b.UserType=1 and c.DealerType=2 
            and c.Name COLLATE Chinese_PRC_CS_AS_WS  like '%'+( SELECT  SUBSTRING ( Name, LEN(Name)-3, 4 )   FROM dbo.Dealers WHERE Id=1243)+'%'
    ) tb1
LEFT JOIN 
    (select * from 
            (select *, ROW_NUMBER() OVER (PARTITION BY  memberid order by [IsSuccess] desc) as RowNumber from [MemberFaceIDDetectRecords] where CreatedDate > '2020.08.31'
            ) tb where RowNumber = 1
    ) tb2
ON tb1.memberid=tb2.memberid 
where tb2.memberid IS NULL
) ttt

rollback tran

结束！