为什么简单的“或”SQL 成本就会爆炸？

Question

我有以下语句来查找数据中的明确名称（约 100 万条）：

select Prename, Surname from person p1 
where Prename is not null and Surname is not null 
and not exists (
   select * from person p2 where (p1.Surname = p2.Surname OR p1.Surname = p2.Altname) 
   and p2.Prename LIKE CONCAT(CONCAT('%', p1.Prename), '%') and p2.id <> p1.id
) and inv_date IS NULL

Oracle 显示了 1477315000 的巨大成本，并且执行在 5 分钟后没有结束。简单地将 OR 拆分为自己的存在子条款可将性能​​提高到 0.5 秒，并将成本提高到 45000：

select Prename, Surname from person p1 
where Prename is not null and Surname is not null 
and not exists (
   select * from person p2 where p1.Surname = p2.Surname and
   p2.Prename LIKE CONCAT(CONCAT('%', p1.Prename), '%') and p2.id <> p1.id
) and not exists (
   select * from person p2 where p1.Surname = p2.Altname and 
   p2.Prename LIKE CONCAT(CONCAT('%', p1.Prename), '%') and p2.id <> p1.id
) and inv_date IS NULL

我的问题不是将其调整到最佳状态，因为它只是一个很少执行的查询，而且我知道 CONTACT 超越了任何索引，但我只是想知道这么高的成本从何而来。这两个查询在语义上对我来说似乎是等价的。

Answer 1

答案就在您的疑问的解释计划中。它们在语义上可能是等效的，但查询幕后的执行计划却截然不同。

EXISTS 的操作方式与 JOIN 不同，本质上，OR 过滤语句是将表连接在一起的。

第二个查询中不会发生 JOIN，因为您仅从一张表中检索记录。

Answer 2

两个查询的结果在语义上可能是等效的，但执行在操作上并不等效。您的第二个示例从不使用 OR 运算符来组合谓词。第二个示例中的所有谓词均使用 AND 进行组合。

性能更好，因为如果与 AND 组合的第一个谓词计算结果不为 true，则跳过第二个（或任何其他谓词）（不计算）。如果您使用 OR，则必须频繁评估两个（或所有）谓词，从而减慢查询速度。 （对 ORed 谓词进行检查，直到其计算结果为 true。）

Answer 3

我会考虑测试重写如下的查询...根据“限定”被视为匹配的条件从一个到另一个进行直接连接...然后，在 WHERE 子句中，如果不匹配，则将其丢弃 根据您的评论，没有找到匹配项

select 
      p1.Prename, 
      p1.Surname
   from 
      person p1 
         join person p2
            on p1.ID <> p2.ID
            and (  p1.Surname = p2.Surname
                or p1.SurName = p2.AltName )
            and p2.PreName like concat( concat( '%', p1.Prename ), '%' )
   where
          p1.PreName is not null
      and p1.SurName is not null
      and p1.Inv_date is null
      and p2.id is null

，但从您正在寻找的内容来看...不，不要进行左外连接...如果您正在寻找与您想要清除的名称相似的名称（不过你会处理的），你只想通过自连接（因此是正常连接）预先限定那些确实有匹配的记录。如果您有一个没有相似名称的名称，您可能不想管它......因此它会自动被排除在结果集中。

现在，WHERE 子句开始发挥作用...左侧有一个有效的人...右侧也有一个人...这些是重复项...所以您可以匹配，现在通过输入逻辑“ p2.ID IS NULL”创建的结果与 NOT EXIST 给出的最终结果相同。

我将查询恢复为正常的“连接”。