这种误解的原因可能是因为有人认为它会读取所有的列。很容易看出这不是事实。
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'
给出计划
![Plan](https://istack.dev59.com/PMnmG.webp)
这表明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)
循环运行这些查询,并测量每秒平均执行次数。以下是结果
![enter image description here](https://istack.dev59.com/bJkND.webp)
+
| 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
这给出了以下计划
|
|
|
将调试器附加到 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);
GO
REVERT;
DROP USER blat
DROP TABLE T
因此,可以推测使用
SELECT some_not_null_col
时的微小明显差异在于它仅在该特定列上检查权限(尽管仍会加载所有元数据)。然而,如果底层表中的列数增加,这似乎与事实不符,因为两种方法之间的百分比差异(如果有的话)会变得更小。
无论如何,在查询编译期间,我不会急于更改所有查询以使用此形式,因为差异非常小,只在查询编译期间才显现。删除
OPTION(RECOMPILE)
以便后续执行可以使用缓存计划,结果如下。
![enter image description here](https://istack.dev59.com/b7cci.webp)
+-------------+-----------+------------+-----------+--------------+
| 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 |
+-------------+-----------+------------+-----------+--------------+
我使用的测试脚本可以在这里找到