EXISTS(select 1 from t1)与EXISTS(select * from t1)的区别

21 浏览
0 Comments

EXISTS(select 1 from t1)与EXISTS(select * from t1)的区别

我过去通常这样写我的EXISTS检查：

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

以前的一位数据库管理员告诉我，当我使用EXISTS子句时，应该使用SELECT 1而不是SELECT *

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

这样真的有区别吗？

0

3 答案

0 Comments

这个问题的出现原因是人们错误地认为使用EXISTS(select *)会读取所有列。然而，实际情况并非如此。可以看到，SQL Server实际上能够使用最窄的索引来检查结果，尽管该索引不包含所有列。索引访问位于半连接操作符下，这意味着一旦返回第一行，它就可以停止扫描。因此，很明显上述观点是错误的。

然而，Query Optimiser团队的Conor Cunningham在这里解释说，在编译查询时，他通常在这种情况下使用SELECT 1，因为它可以在编译查询时产生微小的性能差异。他解释说，查询处理器将在流水线的早期将所有*展开，并将其绑定到对象（在这种情况下是列的列表）。然后，根据查询的性质，它将删除不需要的列。因此，对于一个简单的EXISTS子查询，*将展开为一些潜在的大列列表，然后将确定EXISTS的语义不需要这些列，所以基本上所有列都可以被删除。SELECT 1将避免在查询编译期间检查那个表的任何不需要的元数据。然而，在运行时，这两种形式的查询将是相同的，并且具有相同的运行时间。

为了验证这一观点，我对一个空表使用了四种可能的查询方式：SELECT 1、SELECT *、SELECT Primary_Key、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进程并在执行以下操作时随机中断，可以发现在表有1024列的情况下，大部分时候调用堆栈看起来像下面这样，这表明它确实在加载列元数据时花费了大部分时间，即使使用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() + 0x37 bytes

这次手动的分析尝试得到了VS 2012代码分析器的支持，它显示了在两种情况下编译时间消耗的函数选择上非常不同（1024列的情况下的Top 15函数 vs 1列的情况下的Top 15函数）。

SELECT 1和SELECT *两个版本最终都会检查列权限，并且如果用户没有被授予对表中的所有列的访问权限，查询将失败。以下是一次聊天中的例子，可以看到这一点：

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 |

+-------------+-----------+------------+-----------+--------------+

可以看到，在移除OPTION (RECOMPILE)后，SELECT 1和SELECT *之间的差异变得更小，而SELECT Not Null col和SELECT PK的差异似乎更大。

0
0 Comments

这个问题的出现原因是要比较两种写法的性能，即(EXISTS(select 1 from t1))和(EXISTS(select * from t1))。为了解决这个问题，可以进行性能测试并查看两种写法的执行计划。选择一个具有大量列的表进行测试。

代码如下：

-- 创建测试表
CREATE TABLE t1 (
  id INT PRIMARY KEY,
  col1 INT,
  col2 INT,
  ...
  colN INT
);
-- 插入测试数据
INSERT INTO t1 (id, col1, col2, ..., colN)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
  FLOOR(RAND() * 100),
  FLOOR(RAND() * 100),
  ...,
  FLOOR(RAND() * 100)
FROM
  sys.sysobjects a
CROSS JOIN sys.sysobjects b;
-- 进行性能测试
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 以(EXISTS(select 1 from t1))方式执行查询
IF EXISTS(SELECT 1 FROM t1)
  PRINT 'Exists';
-- 以(EXISTS(select * from t1))方式执行查询
IF EXISTS(SELECT * FROM t1)
  PRINT 'Exists';
-- 查看执行计划
SELECT
  t.text,
  p.query_plan
FROM
  sys.dm_exec_cached_plans p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE
  t.text LIKE '%EXISTS(select 1 from t1)%'
  OR t.text LIKE '%EXISTS(select * from t1)%';

通过以上代码，我们可以对(EXISTS(select 1 from t1))和(EXISTS(select * from t1))两种写法进行性能测试，并查看它们的执行计划。这样我们就能够知道哪种写法更加高效，从而选择合适的写法来提升查询性能。

0
0 Comments

在SQL Server中，当使用EXISTS关键字时，数据库系统会智能地返回NO DATA。这是因为在EXISTS子查询中，通常不需要列出具体的列名，只需要测试满足子查询条件的行是否存在。

为了验证这一点，可以尝试运行以下代码：

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

如果实际上对SELECT列表进行了操作，就会抛出除以零的错误，但实际上并没有发生错误。

需要注意的是，SQL标准中也提到了这个问题。ANSI SQL 1992标准第191页指出：

3) Case:
       a) If the   is
  equivalent to a 
  that is an arbitrary .

这意味着，当一个SELECT列表中的"*"出现在一个紧跟在EXISTS谓词之后的子查询中时，它等同于一个任意的字面值。

根据这个原理，可以进一步扩展使用1/0的EXISTS技巧，例如：

SELECT 1 WHERE EXISTS(SELECT 1/0)

或者

SELECT COUNT(*) WHERE EXISTS(SELECT 1/0)

在SQL Server中，没有FROM子句的SELECT语句被视为从单行表中进行选择（类似于在其他RDBMS中选择从"dual"表中进行选择），所以即使1/0是错误的，仍然会创建一个1行表来进行检查是否存在。

以上是SQL Server的行为特点，不仅如此，这种行为在不同版本的SQL Server中也是一致的，而不是某个特定版本的优化。

，SQL Server在处理EXISTS子查询时，会智能地返回NO DATA，这是根据SQL标准的规定。这种行为特点不仅适用于不同版本的SQL Server，而且在处理具有特定选择列表的子查询时非常有用。

0