SQLSERVER语句 in和exists哪个效率高本人测试证明
- WBOYOriginal
- 2016-06-07 15:19:231196Durchsuche
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语句的执行计划
EXISTS语句的执行计划
从执行计划可以看到两个SQL语句的开销都是一样的，而且大家都使用了右半连接（Right Semi Join）
至于什么是半连接（Semi-join）大家可以看一下这篇文章：SQL Join的一些总结
总结
从上面实际的执行来比较,，IN语句和EXISTS语句基本上都是一样的效率
如有不对的地方，欢迎大家来拍砖o(∩_∩)o
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：基于Virtual BOX的Oracle Linux分辨率设置Nächster Artikel：Sqlserver 数据库高级查询和设计