SQL Server中 in和exists哪个效率高本人测试证明

最近很多人讨论in和exists哪个效率高，今天就自己测试一下

我使用的是测试数据库GPOSDB

环境：SQLServer 2005 Windows7

我的测试条件：两个表作连接根据VC_IC_CardNO字段，查出CT_InhouseCard表中的VC_IC_CardNO（卡号）在CT_FuelingData表中存在的记录

前提：某些人可能在SQL语句中有多个in，或者多个exists，这些情况很难测试效率的，因为大家的条件都不相同

例如下面两个SQL语句

1 SELECT  OrderNo, SiteCode, AreaCode
2 FROM    SchedulingProgram
3 WHERE   AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT   SiteCode
4                                                    FROM     EnvBasicInfo
5                                                    WHERE    cityiD = 31 ) AND OrderNo NOT IN (
6         SELECT  OrderNo
7         FROM    KK_DeliveryinfoTmp )

上面SQL语句IN里面有IN和NOT IN

1 SELECT  OrderNo, SiteCode, AreaCode
2 FROM    SchedulingProgram
3 WHERE   ( AreaCode IN ( 'P', 'M' ) AND SiteCode IN ( SELECT SiteCode
4                                                      FROM   EnvBasicInfo
5                                                      WHERE  cityiD = 31 )
6         ) AND NOT EXISTS ( SELECT   OrderNo
7                            FROM     KK_DeliveryinfoTmp
8                            WHERE    KK_DeliveryinfoTmp.OrderNo = SchedulingProgram.OrderNo )

上面的SQL语句IN里面又有NOT EXISTS

这样的情况很难测试同等条件下IN语句和EXISTS语句的效率

还有一个非SARG运算符

在《SQLSERVER企业级平台管理实践》的第424页里提到：

SQLSERVER对筛选条件（search argument/SARG）的写法有一定的建议

对于不使用SARG运算符的表达式，索引是没有用的，SQLSERVER对它们很难使用比较优化的做法。非SARG运算符包括

NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部函数，例如：Convert、Upper等

所以当您的表中有索引并且SQL语句包含非SARG运算符，那么当测试SQL语句的执行时间的时候肯定相差很大，

因为有些SQL语句走索引，有些SQL语句不走索引

建表脚本

注意：两个表中都有索引！！

CT_FuelingData表

 1 USE [GPOSDB]
 2 GO
 3 /****** 对象:  Table [dbo].[CT_FuelingData]    脚本日期: 08/24/2013 11:00:34 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 SET ANSI_PADDING ON
 9 GO
10 CREATE TABLE [dbo].[CT_FuelingData](
11     [RecordNO] [int] IDENTITY(1,1) NOT NULL,
12     [I_FD_StationNo] [int] NOT NULL,
13     [VC_FD_No] [varchar](50) NOT NULL,
14     [VC_FD_Cardno] [varchar](50) NOT NULL,
15     [I_FD_CardStatus] [int] NULL,
16     [LI_FD_CTC] [bigint] NOT NULL,
17     [I_FD_TypeCode] [int] NULL,
18     [I_FD_PumpID] [int] NOT NULL,
19     [VC_FD_OilType] [varchar](50) NULL,
20     [DE_FD_Volume] [decimal](18, 2) NULL,
21     [DE_FD_Price] [decimal](18, 2) NULL,
22     [DE_FD_Amount] [decimal](18, 2) NULL,
23     [I_FD_Point] [decimal](10, 2) NULL,
24     [D_FD_DateTime] [datetime] NOT NULL,
25     [VC_FD_GroupNo] [varchar](50) NULL,
26     [D_FD_GroupDate] [datetime] NULL,
27     [DE_FD_CardAmount] [decimal](18, 2) NULL,
28     [DE_FD_VolumeTotals] [decimal](18, 2) NULL,
29     [DE_FD_AmountTotals] [decimal](18, 2) NULL,
30     [I_FD_ISSend] [int] NULL,
31     [VC_FD_CardMoneyauthFile] [varchar](50) NULL,
32     [D_Month] [datetime] NULL,
33  CONSTRAINT [PK_CT_FuelingData_1] PRIMARY KEY CLUSTERED 
34 (
35     [VC_FD_No] ASC
36 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
37 ) ON [PRIMARY]
38 
39 GO
40 SET ANSI_PADDING OFF

CT_InhouseCard表

 1 USE [GPOSDB]
 2 GO
 3 /****** 对象:  Table [dbo].[CT_InhouseCard]    脚本日期: 08/24/2013 10:59:58 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 SET ANSI_PADDING ON
 9 GO
10 CREATE TABLE [dbo].[CT_InhouseCard](
11     [RecordNO] [int] IDENTITY(1,1) NOT NULL,
12     [VC_IC_CardNO] [varchar](50) NOT NULL,
13     [VC_IC_PhysicalNO] [varchar](50) NULL,
14     [I_IC_CardType] [int] NULL,
15     [VC_IC_UserName] [varchar](50) NULL,
16     [VC_IC_JobNO] [varchar](50) NULL,
17     [VC_IC_UserID] [varchar](50) NULL,
18     [VC_IC_Password] [varchar](50) NULL,
19     [DE_IC_CardAmount] [decimal](18, 2) NULL,
20     [DE_IC_AppendAmount] [decimal](18, 2) NULL,
21     [DE_IC_ConsumerAmount] [decimal](18, 2) NULL,
22     [I_IC_ISLost] [int] NULL,
23     [D_IC_UsedDateTime] [datetime] NULL,
24     [D_IC_UselifeDateTime] [datetime] NULL,
25     [I_IC_IssueStationNO] [int] NULL,
26     [VC_IC_IssuerNO] [varchar](50) NULL,
27     [D_IC_IssueDateTime] [datetime] NULL,
28     [D_IC_LastUpdateDateTime] [datetime] NULL,
29     [I_IC_CardStatus] [int] NULL,
30     [VC_IC_Remark] [varchar](256) NULL,
31  CONSTRAINT [PK_CT_InhouseCard] PRIMARY KEY CLUSTERED 
32 (
33     [VC_IC_CardNO] ASC
34 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
35 ) ON [PRIMARY]
36 
37 GO
38 SET ANSI_PADDING OFF

测试脚本

因为这个是客户的数据库，本来里面已经有数据了，所以在测试之前先更新两个表的统计信息，以做到公正

1 USE [GPOSDB]
2 GO
3 UPDATE STATISTICS CT_FuelingData
4 UPDATE STATISTICS CT_InhouseCard
5 GO

IN语句

 1 USE [GPOSDB]
 2 GO
 3 DBCC DROPCLEANBUFFERS
 4 GO
 5 DBCC FREEPROCCACHE
 6 GO
 7 SET STATISTICS IO ON
 8 GO
 9 SET STATISTICS TIME ON
10 GO
11 SET STATISTICS PROFILE ON
12 GO
13 SELECT * FROM [dbo].[CT_FuelingData] WHERE [VC_FD_Cardno] IN (SELECT [VC_IC_CardNO] FROM [dbo].[CT_InhouseCard])

EXISTS语句

 1 USE [GPOSDB]
 2 GO
 3 DBCC DROPCLEANBUFFERS
 4 GO
 5 DBCC FREEPROCCACHE
 6 GO
 7 SET STATISTICS IO ON
 8 GO
 9 SET STATISTICS TIME ON
10 GO
11 SET STATISTICS PROFILE ON
12 GO
13 SELECT  *
14 FROM    [dbo].[CT_FuelingData]
15 WHERE   EXISTS ( SELECT [VC_IC_CardNO]
16                  FROM   [dbo].[CT_InhouseCard]
17                  WHERE  [dbo].[CT_FuelingData].[VC_FD_Cardno] = [dbo].[CT_InhouseCard].[VC_IC_CardNO] )

测试结果

IN语句

 1 SQL Server 执行时间:
 2    CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。
 3 SQL Server 分析和编译时间: 
 4    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 5 
 6 SQL Server 执行时间:
 7    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 8 SQL Server 分析和编译时间: 
 9    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
10 
11 SQL Server 执行时间:
12    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
13 SQL Server 分析和编译时间: 
14    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
15 
16 SQL Server 执行时间:
17    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
18 SQL Server 分析和编译时间: 
19    CPU 时间 = 31 毫秒，占用时间 = 67 毫秒。
20 
21 (167 行受影响)
22 表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
23 表 'CT_FuelingData'。扫描计数 1，逻辑读取 31 次，物理读取 1 次，预读 64 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
24 表 'CT_InhouseCard'。扫描计数 1，逻辑读取 2 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
25 
26 (4 行受影响)
27 
28 SQL Server 执行时间:
29    CPU 时间 = 16 毫秒，占用时间 = 192 毫秒。

EXISTS语句

 1 SQL Server 分析和编译时间: 
 2    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 3 
 4 SQL Server 执行时间:
 5    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
 6 SQL Server 分析和编译时间: 
 7    CPU 时间 = 0 毫秒，占用时间 = 34 毫秒。
 8 
 9 (167 行受影响)
10 表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
11 表 'CT_FuelingData'。扫描计数 1，逻辑读取 31 次，物理读取 1 次，预读 64 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
12 表 'CT_InhouseCard'。扫描计数 1，逻辑读取 2 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
13 
14 (4 行受影响)
15 
16 SQL Server 执行时间:
17    CPU 时间 = 0 毫秒，占用时间 = 163 毫秒。

大家可以看到除了执行时间有一点差别，IO是一样的

因为数据量比较大，所以两个查询都用到了Worktable（中间表）来存储中间结果

IN语句的执行计划

EXISTS语句的执行计划

从执行计划可以看到两个SQL语句的开销都是一样的，而且大家都使用了右半连接（Right Semi Join）

至于什么是半连接（Semi-join）大家可以看一下这篇文章：SQL Join的一些总结

SQL改写

in，exists，not in，not exists

生成测试数据

create table t1(
c1 int primary key,
c2 varchar(10),
key idx_c1(c2));


create table t2(
c1 int primary key,
c2 varchar(10),
key idx_c1(c2));


insert into t1 values(1,'a'),(2,'b'),(3,'b'),(4,'c');
insert into t2 values(1,'a'),(2,'c'),(3,'c'),(4,'c');
insert into t2 values(5,null);      
insert into t1 values(5,null);

所谓非关联子查询就是子查询中内查询可以独立执行，与外查询没有关系，互不影响。
而关联子查询的执行依赖于外部查询，通常情况下因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次。

--非关联查询，要注意去重，t2含有null值，结果集为空，NULL 属于未知值，无法与其他值进行比较，无从判断，返回最终结果集为空，要加上t2.c2  is not null
-- t1.c2 使用 not in 在参与比较时就隐含了 t1.c2 is not null 的含义，所以最终结果集中不含t1(3, NULL)这条数据。
select * from t1 where
t1.c2 not in (
select t2.c2 from t2 where 
t2.c2  is not null);

这种反连接经常被改写为外连接，t1 与 t2 使用左外连接，条件加上右表 t2 的连接列 is null，也就是左外连接时没有关联上右表的数据，
即返回左表中无法在右表找到匹配的行
具体含义：t1 表中 c2 列值在 t2 表的 c2 列值中不存在的记录。

  
--关联子查询，要注意去重，t2含有null值，结果集不为空
--在将外查询的 NULL 值传递给内查询时子查询中找不到记录，所以条件返回 false， 表示 not exists 为 true，所以会返回（3, NULL)这条数据
select * from t1 where
not exists (
select 1 from t2 where 
t2.c2=t1.c2)
and t1.c2 is not null;

  
--反连接改写为外连接，不会导致关联结果集放大，要注意去重，t2含有null值，结果集不为空
--左外连接 left join 与 not exists 相同，左表的 NULL 值在右表中关联不上数据，所以会返回（3, NULL)这条数据
select distinct(t1.c2) from t1 left join
t2 on t1.c2=t2.c2 where
t2.c2 is null
and t1.c2 is not null;

反连接改写为外连接，不会导致关联结果集放大，因为没有关联上的 t1 表数据只显示 1 条，
当使用外连接时，t1 表的每行都会尝试与 t2 表中的行进行匹配。
如果 t1 中的行在 t2 中有对应的行，则这些行会被匹配上。
如果 t1 中的行在 t2 中没有对应的行，则这些行在 t2 相关的列上会显示为 NULL。
WHERE 子句中的 t2.c2 IS NULL 条件将过滤掉所有在 t2 中有匹配的 t1 行。
因此，每行 t1 数据最多只出现一次，结果集不会因为外连接而放大。

联接的两个表都存在NULL值情况下，如果子查询返回至少一个 NULL 值，NOT IN 子句将不匹配任何行。
这是因为NOT IN通过比较指定的列与子查询结果集中的所有值来工作。SQL 中的比较逻辑与 NULL 值相关时，结果通常是未知的（在 SQL 术语中称为“三值逻辑”）。
当 t1.c2 与 t2.c2 中的任意一个 NULL 值进行比较时，结果不是真也不是假，而是“未知”。
在 NOT IN 的情况下，这意味着整个 NOT IN 表达式的结果将是未知，这样的话，任何包含 NULL 的比较都不会被包含在结果集中。
相比之下，NOT EXISTS 子句与外连接（LEFT JOIN）的逻辑不受 NULL 值的影响。这两种方法在执行时不会直接比较 NULL，因此它们可以正确返回那些在 t1 中但不在 t2 中的 c2 值的行。

--非关联查询
select * from t1 where
t1.c2  in (select t2.c2 from t2 where t2.c2  is not null);



--半连接改写为内连接，要注意去重
select distinct(t1.c2)  from t1 inner join t2 on t1.c2=t2.c2

总结

从上面实际的执行来比较,，IN语句和EXISTS语句基本上都是一样的效率

如有不对的地方，欢迎大家来拍砖o(∩_∩)o

posted @ 2013-08-24 11:52 桦仔阅读(36565) 评论(17) 编辑收藏举报

刷新页面返回顶部

SQL Server中 in和exists哪个效率高本人测试证明

SQL Server中 in和exists哪个效率高本人测试证明

公告