SQL Server查询优化--简单查询中意外的缓慢性

Question

可能的解释是在这里in the comment

在SQL Server 2014企业版(64位)中，我正在尝试从视图中读取。标准查询只包含一个ORDER BY和OFFSET-FETCH子句，如下所示。

方法1

SELECT

    *

FROM Metadata
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC
OFFSET 150000 ROWS
FETCH NEXT 40 ROWS ONLY

但是，这个相当简单的查询执行的速度几乎比返回相同结果的查询慢9倍(跳过像150 k这样的大量行时可以注意到)。

在本例中，我首先读取主键，然后使用它作为WHERE...IN函数的参数。

方法2

SELECT 
    * 
FROM Metadata
WHERE NewsId IN (
    SELECT

        NewsId

    FROM Metadata
    ORDER BY
        AgeInHours      ASC, 
        RankingPoint    DESC, 
        PublishDate     DESC
    OFFSET 150000 ROWS
    FETCH NEXT 40 ROWS ONLY
)
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC

台式机标记显示了这两者的区别。

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 14748 ms,  elapsed time = 3329 ms.

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3828 ms,  elapsed time = 469 ms.

我在主键上有索引，PubilshDate和它们的碎片非常低。我也尝试过对数据库表运行类似的查询，但在每种情况下，第二种方法都会带来很大的性能提高。我还在Server 2012上测试了这一点。

有人能解释一下这是怎么回事吗？

模式

​

​

方法1:执行计划

​

​

方法2:执行计划(左部分)

​

​

方法2:执行计划(右)

​

​

Answer 1

对于具有相同结果集的不同结构的查询，可以使用不同的方法和查询成本获得不同的查询计划。这在各种SQL实现中是常见的。

基本上，在上面的示例中，当从大表中选择一小部分数据时，首先是减少和最小化结果中的行数，然后选择所有列的完整行，就像您的2.Query一样。

另一种方法是为减少第一步的结果集建立准确的指标。在上面的查询中，可能来自ORDER子句的列在相同的列中和排序顺序是一个解决方案。

(您没有发送查询计划中提到的索引的结构，我可以想象它们的名字背后隐藏着什么。)

还可以使用SQL索引暗示将SQL优化器定向到特定的索引，如果SQL优化器不执行任务，则该索引对任务是最好的。

Answer 2

执行查询时，引擎会查找可用于获得最佳性能的索引。您的方法1使用的索引不包括SELECT语句中的所有列，这将导致查询计划中的键查找，在我的经验中，这种方法的性能总是较低，只使用SELECT语句中的索引列。

如果为AgeInHours, RankingPoint, PublishDate创建索引并包含所有列(仅推荐用于测试目的)，则可以看到差异。

对于第二种方法，如果使用CTE，然后使用IN进行联接而不是使用IN创建WHERE，或者如果有数百万行，则可以获得更好的性能。