主页 / dba / 问题 / 141999
Accepted
Vladimir Baranov
Asked: 2016-06-23 18:07:14 +0800 CST

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) 不在 (A,B,C) 上使用索引

  • 772

考虑这两个函数：

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

据我了解，它们产生完全相同的结果。换句话说，您在PARTITION BY子句中列出列的顺序无关紧要。

如果有一个索引，(A,B,C)我希望优化器在两个变体中都使用这个索引。

但是，令人惊讶的是，优化器决定在第二个变体中做一个额外显式的排序。

我在 SQL Server 2008 Standard 和 SQL Server 2014 Express 上见过它。

这是我用来重现它的完整脚本。

已在 Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 2014 年 2 月 20 日 20:04:26 上试用 版权所有 (c) Microsoft Corporation Express Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service Pack 1)

和 Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) 2016 年 5 月 27 日 15:33:17 版权所有 (c) Microsoft Corporation Express Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service包装 1)

OPTION (QUERYTRACEON 9481)通过使用和使用新旧基数估计器OPTION (QUERYTRACEON 2312)。

设置表、索引、样本数据

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

查询

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

执行计划

A、B 分区

AB

B、A 分区

文学学士

两个都

两个都

如您所见，第二个计划有一个额外的排序。它按 B、A、C 订购。显然，优化器不够聪明，无法意识到这与数据PARTITION BY B,A相同PARTITION BY A,B并重新排序。

有趣的是，第三个查询有两个变体ROW_NUMBERin 它并且没有额外的排序！该计划与第一个查询相同。（序列项目在额外列的输出列表中有额外的表达式，但没有额外的排序）。因此，在这种更复杂的情况下，优化器似乎足够聪明，可以意识到PARTITION BY B,A与PARTITION BY A,B.

在第一个和第三个查询中，索引扫描运算符具有属性 Ordered:True，在第二个查询中为 False。

更有趣的是，如果我像这样重写第三个查询（交换两列）：

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

然后额外的排序再次出现！

有人可以解释一下吗？这里的优化器发生了什么？

sql-server sql-server-2008

1 个回答

  1. Best Answer

    似乎对于“优化器中发生了什么”这个问题没有很好的明确“答案”，除非你是它的开发者并且知道它的内部结构。

    我会在这里整理评论。

    总的来说，将其称为 bug 似乎过于苛刻，因为查询的最终结果是正确的。在某些情况下，执行计划根本不是最优的。ypercubeᵀᴹ、Martin Smith和Aaron Bertrand称其为“错过的优化”。

    • 似乎GROUP BY a,b并GROUP BY b,a产生相同的计划，但PARTITION BY不能使用相同的转换

    • 还有其他缺失的优化，如果在选择列表中被一个具有不同规范的窗口函数分隔，则具有相同窗口规范的窗口函数可以进行额外的排序操作。

    • 是的，这似乎是另一个错过的优化，而且还有很多。优化器是人类编写的，并不完美

    有一篇相关的文章降序索引。Itzik Ben-Gan 的索引排序、并行性和排名计算。Itzik 讨论了降序索引，并举例说明了索引定义的方向如何影响具有分区的窗口函数。他展示了查询和生成计划的示例，ROW_NUMBER其中包含优化器可以避免的额外排序运算符。

    对我来说，实际的结果是牢记优化器的这种特性。在使用PARTITION BY窗口函数时，请始终尝试将列出列PARTITION BY的顺序与它们在索引中列出的顺序相匹配。尽管这应该无关紧要。

    这种预防措施的另一方面是当您查看索引并决定在索引定义中交换一些列时。请注意，您可能会无意中影响一些看似不应受到影响的现有查询。这实际上就是我注意到优化器的这种特殊性的方式。

    否则，优化器可能无法充分利用索引。即使优化器确实选择了最佳计划，这种计划也可能会因为对查询的最轻微的无害更改而变得不太理想，例如更改SELECT语句中列的顺序。

    • 3

相关问题