嵌套查询的性能表现

Question

我知道这是一个常见的问题，我已经阅读了几篇其他的文章和论文，但是我没有找到一个考虑索引字段和两个查询可能返回的记录数量的答案。

我的问题其实很简单。在类似SQL语法的情况下，哪一个查询在性能上更被推荐。

第一个查询:

Select *
from someTable s
where s.someTable_id in
                    (Select someTable_id 
                     from otherTable o
                     where o.indexedField = 123)

第二个查询：

Select *
from someTable
where someTable_id in
                  (Select someTable_id 
                   from otherTable o
                   where o.someIndexedField = s.someIndexedField
                   and o.anotherIndexedField = 123)

我的理解是第二个查询将查询数据库中外部查询返回的每个元组，而第一个查询将首先评估内部选择，然后将筛选器应用于外部查询。

现在，考虑到someIndexedField字段已经建立索引，第二个查询可能会超快地查询数据库，但是如果我们有成千上万或数百万条记录，使用第一个查询会更快吗？

注意：这是针对Oracle数据库的。

- mixkat

---

一般来说，没有指定数据库的性能问题是没有意义的。SQL是一种描述性语言，而不是过程性语言，因此优化器（引擎的一部分）可以自由选择最适合给定查询的查询计划。 - Gordon Linoff

@GordonLinoff 说得好。数据库是Oracle数据库。语言应该并不重要，我想只是用类似SQL的语法写了出来。 - mixkat

根据Tom Kyte的说法，Oracle优化器足够智能，可以识别相关子查询并将其转换为适当的连接（http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:3167884300346662300）。Oracle具有非常好的优化器。有趣的是，MySQL的第一个版本在性能方面表现非常糟糕......直到5.6版本解决了这个问题。 - Gordon Linoff

Answer 1

在MySQL中，如果嵌套查询涉及同一张表，那么查询的执行时间可能会非常长。为了提高MySQL的性能，一个好的方法是为嵌套查询创建临时表，并对该表应用主查询。

例如：

Select *
from someTable s1
where s1.someTable_id in
                    (Select someTable_id 
                     from someTable s2
                     where s2.Field = 123);

可以通过以下方式提高性能：

create temporary table 'temp_table' as (
  Select someTable_id 
  from someTable s2
  where s2.Field = 123
);

Select *
from someTable s1
where s1.someTable_id in
                    (Select someTable_id 
                     from tempTable s2);

我不确定针对大量数据的性能。

- vigoncas

Answer 2

关于第一个查询：

第一个查询会先评估内部选择，然后将过滤器应用于外部查询。

这并不简单。

在 SQL 中，大多数情况下无法告知哪些操作会首先执行，哪些会稍后执行。

因为 SQL 是一种声明性语言。

你的“嵌套选择” - 仅在视觉上存在，而非实际存在。

示例1 - 在“someTable”中有10行，在“otherTable”中有10000行。

在大多数情况下，数据库优化器会先读取“someTable”，然后检查“otherTable”以匹配。为此，它可能会或可能不会使用索引，具体取决于情况，但在该情况下，我认为它会使用“indexedField”索引。

示例2 - 在“someTable”中有10000行，在“otherTable”中有10行。

在大多数情况下，数据库优化器会先将所有行从“otherTable”中读入内存，按123进行筛选，然后会在someTable PK（someTable_id）索引中查找匹配项。结果是-来自“otherTable”的不使用索引。

关于第二个查询：

它与第一个查询完全不同。所以，我不知道如何比较它们：

• 第一个查询通过一个对进行了两个表的关联：s.someTable_id = o.someTable_id
• 第二个查询通过两个对进行了两个表的关联：s.someTable_id = o.someTable_id AND o.someIndexedField = s.someIndexedField。

将两个表关联起来的常规方法是使用您的第一个查询。 但是，o.someTable_id 应该建立索引。

因此，常见规则如下：

• 所有 PK - 应该有索引（它们默认已经被索引）
• 所有用于筛选的列（如在 WHERE 部分中使用的）都应该建立索引
• 所有用于提供表之间匹配项的列（包括 IN、JOIN 等）- 也是过滤器，因此应该建立索引。
• 数据库引擎会自行选择最佳操作顺序（或并行执行）。在大多数情况下，你无法确定此操作顺序。
• 使用 Oracle EXPLAIN PLAN（其他大多数数据库也有类似语句）在实际数据上比较不同查询的执行计划。

- vitrilo

Answer 3

当我直接使用

where not exists (select VAL_ID FROM @newVals = OLDPAR.VAL_ID)时，它的成本是20秒。当我添加了临时表后，它的成本为0秒。我不明白为什么。就像C++开发人员一样，想象一下内部循环的值)

-- Temp table for IDX give me big speedup
  declare @newValID table (VAL_ID int INDEX IX1 CLUSTERED);
    insert into @newValID select VAL_ID  FROM @newVals

  insert into @deleteValues
    select OLDPAR.VAL_ID
    from @oldVal AS OLDPAR
    where 
        not exists (select VAL_ID from @newValID where VAL_ID=OLDPAR.VAL_ID) 
    or      exists (select VAL_ID from @VaIdInternals where VAL_ID=OLDPAR.VAL_ID);

- Dmitry Ivanov