SQLSERVR语句 in和exists哪个效率高本人测试证明最近很多人讨论in和exists哪个效率高，今天就自己测试一下我使用的是客户的数据库GPOSDB(已经有数据) 环境：SQLSERVER2005 Windows7 我的测试条件：两个表作连接根据VC_IC_CardNO字段，查出CT_InhouseCard

SQLSERVR语句 in和exists哪个效率高本人测试证明

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

我使用的是客户的数据库GPOSDB(已经有数据)

环境：SQLSERVER2005 Windows7

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

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

例如下面两个SQL语句

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

ログイン後にコピー

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

<span>1</span> <span>SELECT</span><span>  OrderNo, SiteCode, AreaCode
</span><span>2</span> <span>FROM</span><span>    SchedulingProgram
</span><span>3</span> <span>WHERE</span>   ( AreaCode <span>IN</span> ( <span>'</span><span>P</span><span>'</span>, <span>'</span><span>M</span><span>'</span> ) <span>AND</span> SiteCode <span>IN</span> ( <span>SELECT</span><span> SiteCode
</span><span>4</span>                                                      <span>FROM</span><span>   EnvBasicInfo
</span><span>5</span>                                                      <span>WHERE</span>  cityiD <span>=</span> <span>31</span><span> )
</span><span>6</span>         ) <span>AND</span> <span>NOT</span> <span>EXISTS</span> ( <span>SELECT</span><span>   OrderNo
</span><span>7</span>                            <span>FROM</span><span>     KK_DeliveryinfoTmp
</span><span>8</span>                            <span>WHERE</span>    KK_DeliveryinfoTmp.OrderNo <span>=</span> 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表

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

ログイン後にコピー

CT_InhouseCard表

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

ログイン後にコピー

测试脚本

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

<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>UPDATE</span> <span>STATISTICS</span><span> CT_FuelingData
</span><span>4</span> <span>UPDATE</span> <span>STATISTICS</span><span> CT_InhouseCard
</span><span>5</span> <span>GO</span>

ログイン後にコピー

IN语句

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

ログイン後にコピー

EXISTS语句

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

ログイン後にコピー

测试结果

IN语句

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

ログイン後にコピー

EXISTS语句

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

ログイン後にコピー

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

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

IN语句的执行计划

SQLSERVER语句 in和exists哪个效率高本人测试证明