主页 / dba / 问题 / 19344
Accepted
Martin Smith
Asked: 2012-06-16 13:53:55 +0800 CST

如何有效地检查多列上的 EXISTS？

  • 772

这是我定期遇到的一个问题，但尚未找到好的解决方案。

假设如下表结构

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

并且要求是确定任一可空列B或C实际上是否包含任何NULL值（如果是，则包含哪些值）。

还假设该表包含数百万行（并且没有可用的列统计信息可以查看，因为我对此类查询的更通用的解决方案感兴趣）。

我可以想到几种方法来解决这个问题，但都有弱点。

两个单独的EXISTS陈述。这将具有允许查询在找到 a 时尽早停止扫描的优点NULL。但是如果两列实际上都包含 no NULL，那么将产生两次完整扫描。

单一聚合查询 

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

这可以同时处理两列，因此最坏的情况是一次完整扫描。缺点是即使NULL很早就在查询中遇到两列中的 a ，最终仍会扫描整个表的其余部分。

用户变量

我可以想到第三种方法

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

但这不适用于生产代码，因为未定义聚合连接查询的正确行为。无论如何，通过抛出错误来终止扫描是一个非常可怕的解决方案。

有没有结合上述方法的优势的另一种选择？

编辑

只是用我到目前为止提交的答案的读取结果来更新它（使用@ypercube的测试数据）

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

对于@Thomas 的回答，我改为TOP 3可能TOP 2允许它提前退出。默认情况下，我为该答案制定了并行计划，因此还尝试了MAXDOP 1提示，以使读取次数与其他计划更具可比性。我对结果感到有些惊讶，因为在我之前的测试中，我在没有阅读整个表格的情况下就看到了查询短路。

我的短路测试数据计划如下

短路

ypercube 的数据计划是

非短路

因此，它在计划中添加了一个阻塞排序运算符。我也尝试了HASH GROUP提示，但最终还是读取了所有行

非短路

因此，关键似乎是让hash match (flow distinct)运营商允许该计划短路，因为其他替代方案无论如何都会阻塞并消耗所有行。我认为没有暗示可以特别强制执行此操作，但显然“通常，优化器会选择一个 Flow Distinct，它确定所需的输出行数少于输入集中的不同值。” .

@ypercube 的数据在每列中只有 1 行带有NULL值（表基数 = 30300），进出运算符的估计行都是1. 通过使谓词对优化器更加不透明，它使用 Flow Distinct 运算符生成了一个计划。

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT

编辑 2

我发生的最后一个调整是，如果上面的查询遇到的第一行在NULL列B和C. 它将继续扫描而不是立即退出。避免这种情况的一种方法是在扫描行时对其进行反透视。所以我对Thomas Kejser 的回答的最后修改如下

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

谓词可能会更好，WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULL但与之前的测试数据相反，一个人没有给我一个带有 Flow Distinct 的计划，而那NullExists IS NOT NULL个人给了我（下面的计划）。

无枢轴

sql-server performance

6 个回答

  1. Best Answer

    怎么样：

    SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
        , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT
    • 20

  2. As I understand the question, you want to know whether a null exists in any of the columns values as opposed to actually returning the rows in which either B or C is null. If that is the case, then why not:

    Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null

    On my test rig with SQL 2008 R2 and one million rows, I got the following results in ms from the Client Statistics tab:

    Kejser                          2907,2875,2829,3576,3103
ypercube                        2454,1738,1743,1765,2305
OP single aggregate solution    (stopped after 120,000 ms) Wouldn't even finish
My solution                     1619,1564,1665,1675,1674

    If you add the nolock hint, the results are even faster:

    Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null

My solution (with nolock)       42,70,94,138,120

    For reference I used Red-gate's SQL Generator to generate the data. Out of my one million rows, 9,886 rows had a null B value and 10,019 had a null C value.

    In this series of tests, every row in column B has a value:

    Kejser                          245200  Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
                                250540  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280

ypercube(1)                     249137  Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
                                248276  Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765

My solution                     250348  Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
                                250327  Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278

    Before each test (both sets) I ran CHECKPOINT and DBCC DROPCLEANBUFFERS.

    这是表中没有空值时的结果。请注意，ypercube 提供的 2 exists 解决方案在读取和执行时间方面与我的几乎相同。我（我们）相信这是由于 Enterprise/Developer 版本使用Advanced Scanning的优势。如果您只使用标准版或更低版本，Kejser 的解决方案很可能是最快的解决方案。

    Kejser                          248875  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290

ypercube(1)                     243349  Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
                                242729  Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
                                242531  Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278

My solution                     243094  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
                                243444  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
    • 6

  3. 在 SQL-Fiddle 中测试的版本：2008 r2和2012，有 30K 行。

    • 当EXISTS查询及早发现 Null 时，该查询在效率方面显示出巨大的优势——这是意料之中的。
    • 我通过查询获得了更好的性能EXISTS- 在 2012 年的所有情况下，我无法解释。
    • 在 2008R2 中，当没有 Null 时，它比其他 2 个查询慢。它越早发现空值，它就越快，当两列都早早地出现空值时，它比其他 2 个查询快得多。
    • 与 Martin 的查询相比，Thomas Kejser 的查询似乎在 2012 年表现稍好，但在 2008R2 中表现更好，而在 2008R2 中表现更差CASE。
    • 2012 版的性能似乎要好得多。它可能与 SQL-Fiddle 服务器的设置有关，而不仅仅是优化器的改进。

    查询和计时。完成时间：

    • 第一个完全没有空值
    • 第二列B有一个NULL小的id。
    • 第三，两列NULL各有一个小ID。

    开始吧（计划有问题，我稍后再试。现在点击链接）：

    使用 2 个 EXISTS 子查询进行查询

    SELECT 
      CASE WHEN EXISTS (SELECT * FROM test WHERE b IS NULL)
             THEN 1 ELSE 0 
      END AS B,
      CASE WHEN EXISTS (SELECT * FROM test WHERE c IS NULL)
             THEN 1 ELSE 0 
      END AS C ;

-------------------------------------
Times in ms (2008R2): 1344 - 596 -  1  
Times in ms   (2012):   26 -  14 -  2

    Martin Smith 的单一聚合查询

    SELECT 
    MAX(CASE WHEN b IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN c IS NULL THEN 1 ELSE 0 END) AS C
FROM test ;

--------------------------------------
Times in ms (2008R2):  558 - 553 - 516  
Times in ms   (2012):   37 -  35 -  36

    Thomas Kejser 的询问

    SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT ;

--------------------------------------
Times in ms (2008R2):  859 - 705 - 668  
Times in ms   (2012):   24 -  19 -  18

    我的建议（一）

    WITH tmp1 AS
  ( SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id 
  ) 

  SELECT 
      tmp1.*, 
      NULL AS id2, NULL AS b2, NULL AS c2
  FROM tmp1
UNION ALL
  SELECT *
  FROM
    ( SELECT TOP (1)
          tmp1.id, tmp1.b, tmp1.c,
          test.id AS id2, test.b AS b2, test.c AS c2 
      FROM test
        CROSS JOIN tmp1
      WHERE test.id >= tmp1.id
        AND ( test.b IS NULL AND tmp1.c IS NULL
           OR tmp1.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id
    ) AS x ;

--------------------------------------
Times in ms (2008R2): 1089 - 572 -  16   
Times in ms   (2012):   28 -  15 -   1

    它需要对输出进行一些抛光，但效率与EXISTS查询相似。我认为没有空值会更好，但测试表明事实并非如此。

    建议 (2)

    试图简化逻辑：

    CREATE TABLE tmp
( id INT
, b CHAR(1000)
, c CHAR(1000)
) ;

DELETE  FROM tmp ;

INSERT INTO tmp 
    SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id  ; 

INSERT INTO tmp 
    SELECT TOP (1)
        test.id, test.b, test.c 
      FROM test
        JOIN tmp 
          ON test.id >= tmp.id
      WHERE ( test.b IS NULL AND tmp.c IS NULL
           OR tmp.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id ;

SELECT *
FROM tmp ;

    它在 2008R2 中的表现似乎比之前的建议更好，但在 2012 年更差（也许第二个INSERT可以用 重写IF，就像@8kb 的答案）：

    ------------------------------------------
Times in ms (2008R2): 416+6 - 1+127 -  1+1   
Times in ms   (2012):  14+1 - 0+27  -  0+29
    • 4

  4. 是否IF允许陈述？

    这应该允许您通过表一次确认 B 或 C 的存在：

    DECLARE 
  @A INT, 
  @B CHAR(10), 
  @C CHAR(10)

SET @B = 'X'
SET @C = 'X'

SELECT TOP 1 
  @A = A, 
  @B = B, 
  @C = C
FROM T 
WHERE B IS NULL OR C IS NULL 

IF @@ROWCOUNT = 0 
BEGIN 
  SELECT 'No nulls'
  RETURN
END

IF @B IS NULL AND @C IS NULL
BEGIN
  SELECT 'Both null'
  RETURN
END 

IF @B IS NULL 
BEGIN
  SELECT TOP 1 
    @C = C
  FROM T
  WHERE A > @A
  AND C IS NULL

  IF @B IS NULL AND @C IS NULL 
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'B is null'
    RETURN
  END
END

IF @C IS NULL 
BEGIN
  SELECT TOP 1 
    @B = B
  FROM T 
  WHERE A > @A
  AND B IS NULL

  IF @C IS NULL AND @B IS NULL
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'C is null'
    RETURN
  END
END
    • 4

  5. 当您使用 EXISTS 时，SQL Server 知道您正在执行存在性检查。当它找到第一个匹配值时，它返回 TRUE 并停止查找。

    当您连接 2 列并且如果任何为空，则结果将为空

    例如

    null + 'a' = null

    所以检查这段代码

    IF EXISTS (SELECT 1 FROM T WHERE B+C is null)
SELECT Top 1 ISNULL(B,'B ') + ISNULL(C,'C') as [Nullcolumn] FROM T WHERE B+C is null
    • 0

  6. 怎么样：

    select 
    exists(T.B is null) as 'B is null',
    exists(T.C is null) as 'C is null'
from T;

    如果这可行（我还没有测试过），它将产生一个包含 2 列的单行表，每一列都是 TRUE 或 FALSE。我没有测试效率。

    • -3

相关问题