子查询使用Exists 1或Exists *

Question

我以前会这样编写我的EXISTS检查:

IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters
END

我以前的一位DBA告诉我，在使用EXISTS子句时，应该使用SELECT 1而不是SELECT *

IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END

这真的会有所不同吗？

- Raj More

---

你忘记了 EXISTS(SELECT NULL FROM ...)。顺便说一下，最近有人问过这个问题。 - OMG Ponies

请换一个数据库管理员（DBA）。迷信在IT行业中没有任何地位，尤其是在数据库管理方面。（来自一名前DBA的建议！！！） - Matt Rogish

Answer 1

不，SQL Server很聪明，它知道自己被用于EXISTS，并向系统返回NO DATA。 引自Microsoft： http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4 引用块中说： 几乎总是由星号(*)组成的子查询的选择列表由EXISTS引入。没有列名的原因是您只是测试是否存在满足子查询中指定条件的行。 要检查自己，请尝试运行以下内容：

SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )

如果实际上使用SELECT列表，它将抛出一个除以零的错误。但它没有。

编辑：请注意，SQL标准实际上讨论了这个问题。

ANSI SQL 1992标准，第191页http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

3) Case:
a) 如果“*”在一个<exists predicate>中立即包含在一个<subquery>中，则<select list>等同于一个任意<literal>的<value expression>。

- Matt Rogish

---

EXISTS技巧可以通过使用1/0来扩展到SELECT 1 WHERE EXISTS(SELECT 1/0)...这似乎比第二个SELECT没有FROM子句更抽象一步。 - whytheq

@whytheq - 或者 SELECT COUNT(*) WHERE EXISTS(SELECT 1/0)。在 SQL Server 中，没有 FROM 的 SELECT 被视为访问单行表，类似于在其他 RDBMS 中从 dual 表中选择。 - Martin Smith

@MartinSmith 感谢 - 所以重点是 SELECT 在执行任何其他操作之前会创建一个1行表，因此即使 1/0 是垃圾，这个1行表仍然存在？ - whytheq

这种情况一直存在，还是在某个版本的SQL Server中引入了优化？ - Martin Brown

@MartinSmith TIL “quoth”。感谢您把它修复回来。 - Gurwinder Singh

Answer 2

这种误解的原因可能是因为有人认为它会读取所有的列。很容易看出这不是事实。

CREATE TABLE T
(
X INT PRIMARY KEY,
Y INT,
Z CHAR(8000)
)

CREATE NONCLUSTERED INDEX NarrowIndex ON T(Y)

IF EXISTS (SELECT * FROM T)
    PRINT 'Y'

给出计划

这表明SQL Server能够使用最窄的可用索引来检查结果，尽管该索引并不包括所有列。索引访问位于半连接运算符下，这意味着它可以在返回第一行后停止扫描。

因此，上述观点是错误的。

但是，查询优化器团队的Conor Cunningham在这里解释说，在编译查询时，他通常会使用SELECT 1，因为它可以在查询的编译中产生微小的性能差异。

QP将在管道的早期获取并展开所有*，并将它们绑定到对象（在本例中为列列表）。然后，由于查询的性质，将删除不需要的列。

因此，对于像这样的简单EXISTS子查询： SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2) *将被展开为一些可能很大的列列表，然后确定EXISTS的语义不需要这些列，因此基本上所有这些列都可以被删除。

"SELECT 1"将避免在查询编译期间检查该表的任何不需要的元数据。

但是，在运行时，两种形式的查询将是相同的，并且具有相同的运行时间。

我在一个空表上测试了四种可能的查询方式，这些表有不同数量的列。 SELECT 1 vs SELECT * vs SELECT Primary_Key vs SELECT Other_Not_Null_Column.

我使用OPTION (RECOMPILE)循环运行这些查询，并测量每秒平均执行次数。以下是结果

+-------------+----------+---------+---------+--------------+
| Num of Cols |    *     |    1    |   PK    | Not Null col |
+-------------+----------+---------+---------+--------------+
| 2           | 2043.5   | 2043.25 | 2073.5  | 2067.5       |
| 4           | 2038.75  | 2041.25 | 2067.5  | 2067.5       |
| 8           | 2015.75  | 2017    | 2059.75 | 2059         |
| 16          | 2005.75  | 2005.25 | 2025.25 | 2035.75      |
| 32          | 1963.25  | 1967.25 | 2001.25 | 1992.75      |
| 64          | 1903     | 1904    | 1936.25 | 1939.75      |
| 128         | 1778.75  | 1779.75 | 1799    | 1806.75      |
| 256         | 1530.75  | 1526.5  | 1542.75 | 1541.25      |
| 512         | 1195     | 1189.75 | 1203.75 | 1198.5       |
| 1024        | 694.75   | 697     | 699     | 699.25       |
+-------------+----------+---------+---------+--------------+
| Total       | 17169.25 | 17171   | 17408   | 17408        |
+-------------+----------+---------+---------+--------------+

可以看到，在SELECT 1和SELECT *之间没有一致的获胜者，两种方法之间的差异微不足道。虽然SELECT Not Null col和SELECT PK似乎要快一些。

这四个查询随着表中列数的增加而性能下降。

由于表为空，因此这种关系似乎仅可通过列元数据的数量来解释。对于COUNT(1)，很容易看出在下面的过程中会被重写为COUNT(*)。

SET SHOWPLAN_TEXT ON;

GO

SELECT COUNT(1)
FROM master..spt_values

这给出了以下计划

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
       |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
            |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

将调试器附加到 SQL Server 进程并在执行以下操作时随机中断

DECLARE @V int 

WHILE (1=1)
    SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)
    

我发现在表格有1,024列的情况下，大部分时间调用栈看起来像下面这样，表明它确实花费了大量时间加载列元数据，即使使用SELECT 1（对于只有1列的情况，在10次尝试中随机断开没有触及此部分调用栈）。

sqlservr.exe!CMEDAccess::GetProxyBaseIntnl()  - 0x1e2c79 bytes  
sqlservr.exe!CMEDProxyRelation::GetColumn()  + 0x57 bytes   
sqlservr.exe!CAlgTableMetadata::LoadColumns()  + 0x256 bytes    
sqlservr.exe!CAlgTableMetadata::Bind()  + 0x15c bytes   
sqlservr.exe!CRelOp_Get::BindTree()  + 0x98 bytes   
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CRelOp_FromList::BindTree()  + 0x5c bytes  
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CRelOp_QuerySpec::BindTree()  + 0xbe bytes 
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CScaOp_Exists::BindScalarTree()  + 0x72 bytes  
... Lines omitted ...
msvcr80.dll!_threadstartex(void * ptd=0x0031d888)  Line 326 + 0x5 bytes C
kernel32.dll!_BaseThreadStart@8()  + 0x37 bytes 

这个手动分析尝试得到了VS 2012代码分析器的支持，该工具显示两种情况下消耗编译时间的函数选择有很大不同（Top 15 Functions 1024 columns与Top 15 Functions 1 column）。

无论是SELECT 1还是SELECT *版本都会检查列权限，并在用户未被授予表中所有列的访问权限时失败。

我从the heap上的一次对话中抄来了一个例子。

CREATE USER blat WITHOUT LOGIN;
GO
CREATE TABLE dbo.T
(
X INT PRIMARY KEY,
Y INT,
Z CHAR(8000)
)
GO

GRANT SELECT ON dbo.T TO blat;
DENY SELECT ON dbo.T(Z) TO blat;
GO
EXECUTE AS USER = 'blat';
GO

SELECT 1
WHERE  EXISTS (SELECT 1
               FROM   T); 
/*  ↑↑↑↑ 
Fails unexpectedly with 

The SELECT permission was denied on the column 'Z' of the 
           object 'T', database 'tempdb', schema 'dbo'.*/

GO
REVERT;
DROP USER blat
DROP TABLE T

因此，可以推测使用SELECT some_not_null_col时的微小明显差异在于它仅在该特定列上检查权限（尽管仍会加载所有元数据）。然而，如果底层表中的列数增加，这似乎与事实不符，因为两种方法之间的百分比差异（如果有的话）会变得更小。

无论如何，在查询编译期间，我不会急于更改所有查询以使用此形式，因为差异非常小，只在查询编译期间才显现。删除OPTION(RECOMPILE)以便后续执行可以使用缓存计划，结果如下。

+-------------+-----------+------------+-----------+--------------+
| Num of Cols |     *     |     1      |    PK     | Not Null col |
+-------------+-----------+------------+-----------+--------------+
| 2           | 144933.25 | 145292     | 146029.25 | 143973.5     |
| 4           | 146084    | 146633.5   | 146018.75 | 146581.25    |
| 8           | 143145.25 | 144393.25  | 145723.5  | 144790.25    |
| 16          | 145191.75 | 145174     | 144755.5  | 146666.75    |
| 32          | 144624    | 145483.75  | 143531    | 145366.25    |
| 64          | 145459.25 | 146175.75  | 147174.25 | 146622.5     |
| 128         | 145625.75 | 143823.25  | 144132    | 144739.25    |
| 256         | 145380.75 | 147224     | 146203.25 | 147078.75    |
| 512         | 146045    | 145609.25  | 145149.25 | 144335.5     |
| 1024        | 148280    | 148076     | 145593.25 | 146534.75    |
+-------------+-----------+------------+-----------+--------------+
| Total       | 1454769   | 1457884.75 | 1454310   | 1456688.75   |
+-------------+-----------+------------+-----------+--------------+

我使用的测试脚本可以在这里找到

- Martin Smith

---

这个回答因为付出了获取真实数据的努力而值得更多点赞。 - Jon

这些统计数据是在哪个版本的SQL Server上生成的？ - Martin Brown

@MartinBrown - 如果我没记错的话，最初是在2008年进行的测试，尽管最近针对最新版本我在2012年又重新进行了测试，并得到了相同的结果。 - Martin Smith

Answer 3

最好的方法是对两个版本进行性能测试，并检查两个版本的执行计划。选择一个有很多列的表。

- HLGEM

---

不知道为什么这个被踩了。我一直认为教人钓鱼比直接给他鱼更好。否则人们怎么能学到东西呢？ - Ogre Psalm33

Answer 4

在SQL Server中，这两种写法是没有区别的，也从来不会成为问题。优化器知道它们是相同的。如果你查看执行计划，你会发现它们是完全一样的。

- Cade Roux

Answer 5

就我个人而言，我非常难以相信他们不会优化到相同的查询计划。但是在你特定的情况下了解答案的唯一方法就是进行测试。如果你这样做了，请回报结果！

- Larry Lustig

Answer 6

实际上没有什么区别，但可能会有非常小的性能损失。一般来说，你不应该请求比你需要的更多的数据。

- orjan