sql-server - FROM 子句中的 SQL 子查询

Question

据我了解，如果使用不正确，子查询对性能的影响是众所周知的。我有一个非常具体的场景，用户需要从表中检索一组经过过滤的记录。可用的滤镜种类繁多，并且它们必须支持合成。此外，一组开发人员将定期创建新的过滤器。

我不喜欢一个不断增长的、带有大量参数的单一 SQL 查询的想法。我不喜欢一堆具有相同 SELECT 语句和不同 WHERE 子句的自治 SQL 查询的想法。我确实喜欢动态 SQL 查询的想法，但我不确定应该使用哪种结构。我可以想到 4 个基本选项:(如果我还缺少更多选项，请随时提出建议)

1. “INNER JOIN”:通过 INNER JOINS 连接过滤器来过滤结果。
2. “FROM subqueries”:通过 FROM 语句中的子查询堆叠过滤器。
3. “WHERE子查询”:通过WHERE子句中的子查询连接过滤器。
4. “INNER JOIN 子查询”:一种奇怪的混合体。

我创建了一个 SQL fiddle 来演示(和分析)它们:

http://sqlfiddle.com/#!3/4e17b/9

下面是 fiddle 的摘录，以提供我所谈论的内容的想法:

------------------------------------------------------------------------
--THIS IS AN EXCERPT FROM THE SQL FIDDLE -- IT IS NOT MEANT TO COMPILE--
------------------------------------------------------------------------

--
--"INNER JOIN" test
      SELECT COUNT(*) 
      FROM 
            @TestTable Test0
            INNER JOIN @TestTable Test1 ON Test1.ID=Test0.ID AND Test1.ID % @i = 0
            INNER JOIN @TestTable Test2 ON Test2.ID=Test0.ID AND Test2.ID % @j = 0
            INNER JOIN @TestTable Test3 ON Test3.ID=Test0.ID AND Test3.ID % @k = 0

--
--"FROM subqueries" test   
      SELECT COUNT(*) FROM (
            SELECT * FROM (
                  SELECT * FROM (
                        SELECT * FROM @TestTable Test3 WHERE Test3.ID % @k = 0
                  ) Test2 WHERE Test2.ID % @j = 0
            ) Test1 WHERE Test1.ID % @i = 0
      ) Test0

--
--"WHERE subqueries" test
      SELECT COUNT(*) 
      FROM @TestTable Test0
      WHERE 
            Test0.ID IN (SELECT ID FROM @TestTable Test1 WHERE Test1.ID % @i = 0)
            AND Test0.ID IN (SELECT ID FROM @TestTable Test2 WHERE Test2.ID % @j = 0)
            AND Test0.ID IN (SELECT ID FROM @TestTable Test3 WHERE Test3.ID % @k = 0)

--
--"INNER JOIN subqueries" test
    SELECT COUNT(*) 
    FROM 
        TestTable Test0
        INNER JOIN (SELECT ID FROM TestTable WHERE ID % @i = 0) Test1 ON Test1.ID=Test0.ID
        INNER JOIN (SELECT ID FROM TestTable WHERE ID % @j = 0) Test2 ON Test2.ID=Test0.ID
        INNER JOIN (SELECT ID FROM TestTable WHERE ID % @k = 0) Test3 ON Test3.ID=Test0.ID

--
--"EXISTS subqueries" test
    SELECT COUNT(*) 
    FROM TestTable Test0
    WHERE 
        EXISTS (SELECT 1 FROM TestTable Test1 WHERE Test1.ID = Test0.ID AND Test1.ID % @i = 0)
        AND EXISTS (SELECT 1 FROM TestTable Test2 WHERE Test2.ID = Test0.ID AND Test2.ID % @j = 0)
        AND EXISTS (SELECT 1 FROM TestTable Test3 WHERE Test3.ID = Test0.ID AND Test3.ID % @k = 0)

排名(执行测试的时间)

SQL fiddle :

|INNER JOIN|FROM SUBQUERIES|WHERE SUBQUERIES|INNER JOIN SUBQUERIES|EXISTS SUBQUERIES|
-------------------------------------------------------------------------------------
|     5174 |           777 |           7240 |                5478 |            7359 |

本地环境:(没有缓存:每次测试前清除缓冲区)

|INNER JOIN|FROM SUBQUERIES|WHERE SUBQUERIES|INNER JOIN SUBQUERIES|EXISTS SUBQUERIES|
-------------------------------------------------------------------------------------
|     3281 |          2851 |           2964 |                3148 |            3071 |

本地环境:(有缓存:连续运行两次查询，记录第二次运行的时间)

|INNER JOIN|FROM SUBQUERIES|WHERE SUBQUERIES|INNER JOIN SUBQUERIES|EXISTS SUBQUERIES|
-------------------------------------------------------------------------------------
|      284 |            50 |           3334 |                 278 |             408 |

每种解决方案都有优点/缺点。 WHERE 子句中的子查询的性能非常糟糕。 FROM 子句中的子查询具有相当好的性能(实际上它们通常性能最好)(注意:我相信这种方法会抵消索引的好处？)。 INNER JOIN 具有相当好的性能，尽管它引入了一些有趣的范围问题，因为与子查询不同，INNER JOIN 将在相同的上下文中运行(必须有一个中间系统来避免表别名的冲突)。

总的来说，我认为最干净的解决方案是 FROM 子句中的子查询。过滤器很容易编写和测试(因为与 INNER JOIN 不同，它们不需要提供上下文/基本查询)。

想法？这是子查询的有效使用还是即将发生的灾难？

更新(2012/10/04):

• 更新了 SQL Fiddle 以包含对“EXISTS”方法的测试
• 添加了 SQL Fiddle 和本地环境的性能测试

Answer 1

如果您总是要应用“与”逻辑，则内部联接可能是一个好方法(我是概括性的，但它会因很多因素而异，包括您的表大小和索引等)。如果您希望能够应用“与”或“或”过滤，则需要使用其他解决方案之一。

此外，您应该使用存在子句测试性能:

SELECT COUNT(*) 
      FROM @TestTable Test0
      WHERE 
            EXISTS (SELECT 1 FROM @TestTable Test1 WHERE Test0.ID = Test1.ID AND Test1.ID % @i = 0)
            EXISTS (SELECT 1 FROM @TestTable Test2 WHERE Test0.ID = Test2.ID AND Test2.ID % @j = 0)
            EXISTS (SELECT 1 FROM @TestTable Test3 WHERE Test0.ID = Test3.ID AND Test3.ID % @k = 0)