SQL - not in 和 join

最新推荐文章于 2022-03-28 22:22:02 发布

Shine

最新推荐文章于 2022-03-28 22:22:02 发布

阅读量295

点赞数

CC 4.0 BY-SA版权

分类专栏： SQLServer

本文链接：https://blog.youkuaiyun.com/loveandangle/article/details/17395817

SQLServer 专栏收录该内容

24 篇文章

订阅专栏

本文深入探讨了在数据库查询中使用NOT IN和JOIN两种方式的区别，通过实例展示了在特定场景下如何选择更高效的查询策略。通过将应用ID从GUID类型转换为字符串类型，性能得到了显著提升。

今天做了7W行的数据分页存储过程如下：

ALTER PROCEDURE  [dbo].[proc_select_page_top]
@pageindex INT=1,		--当前页数
@pagesize INT=5,		--每页大小
@tablename VARCHAR(1000)='',	--表名
@fields VARCHAR(1000)='',	--查询的字段集合
@keyid VARCHAR(50)='',		--主键
@condition NVARCHAR(1000)='',		--查询条件
@orderstr VARCHAR(500),			--排序条件
@totalRecord BIGINT OUTPUT		--总记录数
AS
    begin 
	IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
	IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
	IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
	DECLARE @sql NVARCHAR(4000)
	SET @sql=N'SELECT @totalRecord=COUNT(*)'
		+N' FROM '+@tablename
		+N' WHERE '+@condition
	EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT

	SET NOCOUNT ON  


	IF(@pageindex=1)
	BEGIN
		SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
			EXEC(@sql)
	END
	ELSE
	BEGIN
		SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE  '+@keyid
		+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '
		+@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
		print @sql
		--EXEC(@sql)
	END

END

每页20条读取，开始运行起来感觉很快，但是越到后面感觉原来越慢甚至到最后页第365页，用了11秒钟去只读取，感觉性能出现了很大的问题，最后发现在not in 这里产生了瓶颈：

+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '

找了很多替代的参考，都说left join 比较省事于是改测试改写成:

原来的sql:

SELECT TOP   20  *  FROM  vw_EmployeeLeaves 
					WHERE  applicationid NOT IN(
					SELECT TOP 7280 applicationid 
					FROM  vw_EmployeeLeaves  
					WHERE  1 = 1 AND EmployeeStatus = 'Effective'   
					ORDER BY applicationID DESC ) AND  1 = 1 AND EmployeeStatus = 'Effective' 
					ORDER BY applicationID DESC

现在的SQL:

   SELECT TOP 20 A.* FROM  vw_EmployeeLeaves A LEFT join (
					 select top  7280 applicationid 
					 FROM  vw_EmployeeLeaves  
					 WHERE  1 = 1 AND EmployeeStatus = 'Effective'  
					 ORDER BY applicationID DESC)  as B
					on A.applicationID=B.applicationid     
					WHERE  1 = 1 AND b.applicationID is null  AND A.EmployeeStatus = 'Effective'   
					ORDER BY A.applicationID DESC

执行结果分别为11秒，120毫秒

descr		 execDate
NotIn --start	2013-12-18 15:47:41.680
NotIn --end	2013-12-18 15:47:52.730
Join start	2013-12-18 15:47:52.730
Join End	         2013-12-18 15:47:52.850

到此为止比较觉得用比较面议的JOIN 代替 NOT IN ,但是在准备代替JOIN前发现了为什么not in 效率很低的问题，问题出现在：

ORDER BY A.applicationID

applicationID 是GUID类型的，为此换成了字符 applicaitonCODE

执行结果分别为57毫秒，124毫秒

descr		execDate
NotIn --start	2013-12-18 15:53:25.260
NotIn --end	2013-12-18 15:53:25.323
Join start	2013-12-18 15:53:25.323
Join End	         2013-12-18 15:53:25.447

not in 和join 取舍大家自己做参考了