我正在努力说服查询计划按我认为的那样行事。在查询索引视图时添加 TOP 子句会导致次优计划,我希望在排序方面有所帮助。
环境
- SQL Server 2019
- StackOverflow2013 数据库(50GB 版本),Compat Mode 150(问题不是这个版本特有的)
设置:
首先,我创建了一个视图来回报每个人的高声誉:
CREATE VIEW vwHighReputation
WITH SCHEMABINDING
AS
SELECT [Id],
[DisplayName],
[Reputation]
FROM [dbo].[Users]
WHERE [Reputation] > 10000
接下来,由于我将按显示名称进行搜索,因此我在视图上创建了几个索引:
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id])
GO
CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation)
GO
如果我通过视图查询,我可以看到我的非聚集索引正在被使用:
SELECT *
FROM [dbo].[vwHighReputation]
WHERE [DisplayName] LIKE 'J%'
计划:(https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv)
到目前为止,一切都很好。我什至可以将我的视图用作带有 OUTER APPLY 的更复杂查询的一部分,并且我仍然会在我的索引中仅获得 63 次读取(这显然是一个人为的示例,但有助于说明我将要解决的问题):
SELECT [U].[Id],
[A].[Reputation],
[A].[DisplayName]
FROM [dbo].[Users] AS [U]
OUTER APPLY (
SELECT *
FROM [dbo].[vwHighReputation] AS [v]
WHERE [v].[Id] = [U].[Id]
) AS [A]
WHERE [A].[DisplayName] LIKE 'J%';
计划:https ://www.brentozar.com/pastetheplan/?id=HJaw3y6ov
但是,如果我将 TOP 1 添加到我的 OUTER APPLY:
SELECT [U].[Id],
[A].[Reputation],
[A].[DisplayName]
FROM [dbo].[Users] AS [U]
OUTER APPLY (
SELECT TOP 1 *
FROM [dbo].[vwHighReputation] AS [v]
WHERE [v].[Id] = [U].[Id]
) AS [A]
WHERE [A].[DisplayName] LIKE 'J%';
然后情况变得糟糕......非常非常糟糕......
计划:https ://www.brentozar.com/pastetheplan/?id=HyOS6yaiw
我对这个观点的逻辑阅读计数现在接近 500 万。从计划中我可以看出,SQL Server 现在选择以用户 ID 作为谓词对聚集索引执行搜索,但这样做大约 250 万次。它还在扫描整个用户表。它不再寻找视图的索引。
显然优化器决定这是最有效的方法,但我不明白为什么!我认为这可能与基础表的排序方式有关,但我不确定。
顺便说一句,将其重写为简单的 SUB QUERY 而不是 CROSS APPLY 会产生相同的结果。
任何帮助或建议都会很棒!
外应用
您正在使用
OUTER APPLY
,但带有会拒绝NULL
值的 where 子句。它被转换为没有以下内容的内部连接
TOP (1)
:我已经对您的代码进行了一些格式化,并添加了一个
ORDER BY
以跨查询验证结果。没有冒犯的意思。外涂 + TOP (1)
当您使用 时
TOP (1)
,连接是LEFT OUTER
多种多样的:内部显然
TOP (1)
使OUTER APPLY
优化器无法将相同的转换应用于内部连接,即使使用冗余谓词:注意剩余谓词以评估
Id
和DisplayName
列是否为NULL
。这也不仅仅是一个
TOP (1)
问题——您可以替换最大 int max (9223372036854775807) 的任何值并查看相同的计划。如果您完全跳过视图,也会发生这种情况。
重写
TOP (1)
获得与没有各种优化器副作用相同的效果的一种方法TOP
是使用ROW_NUMBER
这将为您提供原始计划: