主页 / dba / 问题 / 301557
Accepted
Pantea
Asked: 2021-10-24 05:48:11 +0800 CST

逻辑运算符使用不当导致查询性能不佳

  • 772

我有一张包含大量数据（近 1500 万）及以下结构的表格。

create table test
(a int,--> /* There is a normal index on this column */
 b int,
<other columns>)

有一个从此表中选择的查询，where 子句中的条件之一是：

where a!=1 or (a=1 and b!=0) /* The original condition */

查询非常慢，我认为这种糟糕的性能大部分可能是因为逻辑运算符使用不当。我已经改变了条件，如下所示：

where not (a=1 and b=0) /* The edited version*/

并且性能发生了巨大变化！我需要确定的是这两个条件完全相同，所以我不会错过任何数据。我想知道您是否可以帮助我，并告诉我您是否有更好的替代方案。

如果您知道任何关于正确使用逻辑操作以及方式/顺序优化器处理它们的文章，请分享链接。

提前致谢

sql-server query-performance

3 个回答

  1. Best Answer

    为了确定两个条件是否真的等价，您可以尝试为每个条件构建真值表，看看这两个表是否相同。

    以下是构建真值表的方法。您有两个变量，a，可能等于或不等于 1，和b，可能等于或不等于 0。编写并执行如下查询：

    SELECT
  a
, b
, [a!=1 or (a=1 and b!=0)] = CASE WHEN a!=1 or (a=1 and b!=0) THEN 'True' ELSE 'False' END
, [not (a=1 and b=0)]      = CASE WHEN not (a=1 and b=0)      THEN 'True' ELSE 'False' END
FROM
  (
    VALUES
      (   1,    0)
    , (   1, 9999)
    , (9999,    0)
    , (9999, 9999)
  ) AS v (a, b)
;

    对于每个变量，指定与变量进行比较的值，以便进行相应的比较或真或假（取决于它是=还是!=），以及产生相反结果的另一个值。上面的值 9999 只是一个任意值，在与 的比较时代表“非 1”，在与 的比较时代表a“非 0” b。（我选择了与 1 或 0 完全不同的东西，以免结果表过于混乱。）

    上述查询将返回以下输出：

    一个 b a!=1 或 (a=1 and b!=0) 不是（a=1 和 b=0）
    1 0 错误的 错误的
    1 9999 真的 真的
    9999 0 真的 真的
    9999 9999 真的 真的

    如您所见，对于相同的输入值，两个表达式给出相同的结果。

    但是请注意，上表仅包含使比较评估为True或False的值。这就是布尔代数中通常的情况。但是，在 SQL 世界中，布尔表达式可以评估为第三种状态，即Unknown aka Null。如果a可以为空并且确实为空，那么a=1（或a!=1就此而言）将评估为Unknown / Null。如果需要考虑可空性，那么我们的真值表应该包含空值作为输入值。

    这是上述脚本的修改版本，其中包含两个变量的空值：

    SELECT
  a
, b
, [a!=1 or (a=1 and b!=0)] = CASE
                               WHEN      a!=1 or (a=1 and b!=0)  THEN 'True'
                               WHEN NOT (a!=1 or (a=1 and b!=0)) THEN 'False'
                               ELSE 'Unknown'
                             END
, [not (a=1 and b=0)]      = CASE
                               WHEN      not (a=1 and b=0)       THEN 'True'
                               WHEN NOT (not (a=1 and b=0)     ) THEN 'False'
                               ELSE 'Unknown'
                             END
FROM
  (
    VALUES
      (   1,    0)
    , (   1, 9999)
    , (   1, NULL)
    , (9999,    0)
    , (9999, 9999)
    , (9999, NULL)
    , (NULL,    0)
    , (NULL, 9999)
    , (NULL, NULL)
  ) AS v (a, b)
;

    它给出了以下输出：

    一个 b a!=1 或 (a=1 and b!=0) 不是（a=1 和 b=0）
    1 0 错误的 错误的
    1 9999 真的 真的
    1 无效的 未知 未知
    9999 0 真的 真的
    9999 9999 真的 真的
    9999 无效的 真的 真的
    无效的 0 未知 未知
    无效的 9999 未知 真的
    无效的 无效的 未知 未知

    上面突出显示的是两个条件不产生相同结果的一种情况，即当a为空并且b是非 0 的非空值时。在这种情况下，第一个条件的结果未知，而另一个条件的结果为真。

    同样，这是假设a可以为空，并且在该假设下，您的两个逻辑表达式不等价。但是，例如，只能b为空不能为空a，那么您可以从上面的输出中看到相应行中的结果是相同的。

    因此，您将根据所涉及变量的可空性找到答案。

    更多阅读的几个链接：

    • 25

  2. 条件not (a=1 and b=0)等于a!=1 or b!=0当然不等于a!=0 or (a=1 and b!=0)

    例如：

    a = 0
b = 0

a!=0 or (a=1 and b!=0) => false or (false and false) => false

not (a=1 and b=0) => not (false and true) => true
    • 4

  3. 不幸的是，我不认为您的重写在逻辑上是等效的。在您的第一个谓词中，这意味着将返回任何不等于where a != 0 or ...的记录。这将包括记录（这特别是由于使用了一个子句）。您的第二个重写谓词将排除相同的情况。a0where a = 1 and b = 0ORwhere not (a = 1 and b = 0)

    但是您应该能够通过比较两个谓词的行数来验证这一点。如果需要，您可以使用您正在测试的每个值组合（例如(a,b) = {(0,0), (0,1), (1,0), (1,1)}）创建一个小型临时表，然后应用每个谓词来查看结果。

    我可以提供的一个提示是，有时OR子句可以更有效地重写为UNION单独查询中子句两侧之间的一个。例如：

    SELECT ...
FROM test
WHERE a!=0 

UNION

SELECT ...
FROM test
WHERE a=1 and b!=0

    根据您的索引和谓词，这可能允许有效地查找索引。虽然我不确定在使用不等式运算符（如!=.

    但除此之外，对于性能改进建议，我们可能需要查看您的执行计划。

    • 3

相关问题