主页 / dba / 问题 / 299708
Accepted
Doug Coats
Asked: 2021-09-17 07:42:58 +0800 CST

索引定义 Order 和 ORDER BY 子句

  • 772

所以我在早上阅读博客时偶然发现了这个有趣的练习：

https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/

这是文章中的问题和他提出的索引。

SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       p.Score,
       p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND   p.LastActivityDate >= '20110101'
ORDER BY p.Score DESC;

CREATE INDEX whatever 
    ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate) 
        INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);

非常有趣的构建和索引，并尝试相应地调整它。但是，我之前可能误解了索引键顺序很重要，并且当索引键顺序与查询不匹配时，某些 WHERE 子句可能不使用某些索引。意思是，我对列出的特定场景缺乏经验，我假设这个查询不会使用这个索引，因为 Score 位于索引键定义的中间，但不在查询的 where 子句中。

当优化器决定使用什么索引并且只要 WHERE 子句列和 ORDER by 列在索引定义中时，是否会评估 ORDER BY 列，然后它会使用它？

我想我的问题更多是关于优化器如何评估关于 WHERE 子句和 ORDER BY 子句的索引。

sql-server index

1 个回答

  1. Best Answer

    键的顺序绝对重要。这两个建议的索引采用不同的方法来解决它。

    让我们考虑一下当索引打开时查询是如何运行的(PostTypeID, LastActivityDate)，考虑手动操作。

    我们可以很容易地找到与 WHERE 子句匹配的所有行 - PostTypeID 1 并且足够近。但是我们需要按分数对它们进行排序以找到前 5000 名。如果我们有很多行要排序，这可能会很昂贵。

    或者，使用 上的索引(PostTypeID, Score DESC)，我们只能过滤到 PostTypeID，但是我们可以按正确的顺序遍历该数据。是的，我们必须根据 LastActivityDate 拒绝任何不够新的行，但是一旦我们找到了我们关心的 5000 行，我们就可以停止。我们不必做那种昂贵的事情。但是我们正在查看比我们关心的更多的行。顺便说一句，我认为在键列中包含 LastActivityDate 没有多大价值 - 它同样适合包含的列，因为它不参与 Seek 谓词。Seek 运算符只是按分数顺序返回该 PostTypeID 值的所有行。

    查询优化器知道可以按照索引中指定的顺序从索引中提取数据，这可以在许多方面受益。也许它有助于 Merge Join、Stream Aggregate 或 ORDER BY 子句。

    如果我们认为 PostTypeID 被过滤为单个值，则一个索引位于 Score 上以避免排序，而另一个索引位于 LastActivityDate 上以收紧 Seek 范围。QO 权衡每个的预期成本并选择“更便宜”的一个。

    • 7

相关问题