代码之家 › 专栏 › 技术社区 › Payal Bansal

SQL Server未对查询使用筛选索引

performance sql-server

Payal Bansal · 技术社区 · 1 年前

我创建了此索引:

CREATE NONCLUSTERED INDEX [IDX_Speedup_04] 
ON [Sessions] ([ServerName] ASC, [SessionId] ASC, [CreatedTime] ASC)
WHERE ([UniqueSessionId] IS NULL)

但问题是:

 UPDATE [Sessions]  
 SET UniqueSessionId = COALESCE(ServerName, '') + '-' + SessionId + '-' + CONVERT(VARCHAR(12), CAST(CreatedTime AS DATE), 120)
 WHERE UniqueSessionId IS NULL

没有使用该索引。我重建了索引并更新了统计数据。

SQL Server仍在对其他具有列搜索功能的索引进行完全扫描 ServerName , SessionId 和 CreatedTime 然后在对这么多行进行扫描之后进行过滤。

此外,当前表中没有具有的行 UniqueSessionId 无效的所以它应该使用过滤后的索引。

我还清除了查询缓存。

不确定是否在执行扫描时提取了0行,为什么仍在servername上执行密钥查找。。

即使删除合并和重建,仍然不使用筛选的索引

更改为concat,仍然不变不确定基数是如何影响的。。

4 回复 | 直到 1 年前

Tim Biegeleisen 1 年前

这里有两条一般性意见。首先,索引中的三列甚至不出现在 WHERE 子句。只有 UniqueSessionId 确实出现在中 哪里 子句,但它不是索引的一部分。一个简单的索引可以习惯只包括 UniqueSessionId 单独列。

但第二,SQL Server甚至可能不会使用这样的索引,假设有相当均匀的null和非null记录划分 UniqueSessionId 。在这种情况下,只使用完整的表扫描进行更新可能会更快。

Cesar 1 年前

您正在按UniqueSessionId进行搜索。

但该字段不在索引中。您创建的索引仅包含ServerName、SessionId和CreatedTime。 “WHERE([UniqueSessionId]IS NULL)”只是一个条件。

您应该搜索其他列或在索引中包括UniqueSessionId,如:

CREATE NONCLUSTERED INDEX [IDX_Speedup_04] ON [Sessions]
(
    [ServerName] ASC,
    [SessionId] ASC,
    [CreatedTime] ASC,
    [UniqueSessionId]
)

编辑:如果在搜索中仅使用该字段,请为其创建另一个独占索引:

CREATE NONCLUSTERED INDEX [IDX_Speedup_04B] ON [Sessions]
(
    [UniqueSessionId]
)

这是因为单独使用时要产生效果的字段应该是索引上的第一个字段。

Payal Bansal 1 年前

在bar.CMS上创建索引IDX_CMS_Sessions_Speedup_04。会话(UniqueSessionId)包括(ServerName、SessionId、CreatedTime) 其中UniqueSessionId为NULL

上述指数起了作用。但是 不知道为什么要做线轴。返回了0行。

Martin Smith 1 年前

您问题中的图片显示该表具有 10,86,977 排。

您确认当前 0 其中的匹配 [UniqueSessionId] IS NULL 谓语

为了设置类似的东西,我使用

CREATE TABLE [Sessions]
(
RowId INT IDENTITY PRIMARY KEY,
ServerName varchar(50),
[SessionId] int, 
[CreatedTime] datetime,
[UniqueSessionId] varchar(100) null
)

insert [Sessions]
SELECT 'foo',1,getdate(),'foo'
FROM generate_series(1, 10986977)

CREATE NONCLUSTERED INDEX [IDX_Speedup_04] 
ON [Sessions] ([ServerName] ASC, [SessionId] ASC, [CreatedTime] ASC)
WHERE ([UniqueSessionId] IS NULL)

我得到的执行计划 UPDATE 下面的估计成本为 40.7141 并进行全表扫描

 UPDATE [Sessions]  
 SET UniqueSessionId = COALESCE(ServerName, '') + '-' + SessionId + '-' + CONVERT(VARCHAR(12), CAST(CreatedTime AS DATE), 120)
 WHERE UniqueSessionId IS NULL

当它可以扫描时,这当然很烦人 IDX_Speedup_04 并获取与 UniqueSessionId IS NULL 谓词(在本例中为零),然后驱动 更新 从那些。

显式索引提示确实给出了此计划(计划成本为 0.0165721 )

 UPDATE [Sessions]
  SET UniqueSessionId = COALESCE(ServerName, '') + '-' + SessionId + '-' + CONVERT(VARCHAR(12), CAST(CreatedTime AS DATE), 120)
  from [Sessions] with(index = IDX_Speedup_04)
 WHERE UniqueSessionId IS NULL

密钥查找也很烦人,原因如下 in the bug linked by Dan 但即使进行了密钥查找,该计划的成本仍然低于 1/2000 它最终选择的计划的成本。

如果索引更改为

CREATE NONCLUSTERED INDEX [IDX_Speedup_05] 
ON [Sessions] ([ServerName] ASC, [SessionId] ASC, [CreatedTime] ASC) INCLUDE ([UniqueSessionId])
WHERE ([UniqueSessionId] IS NULL)

它现在在没有任何提示的情况下自然地选择了所需的计划,这次的计划成本是 0.0132844

我认为这只是一个限制,它不会自然地(在没有提示的情况下)考虑第二个计划,因为当被迫考虑时,它确实会花得更便宜。

Payal Bansal 1 年前

https://www.linkedin.com/pulse/filtered-index-sql-server-null-condition-payal-bansal-6tykc/?trackingId=gxff21olysTBHV4Ofhhv2Q%3D%3D

终于得到了答案,并在这里添加了解释