优化器忽略过滤的索引条件 [英] Filtered index condition is ignored by optimizer

问题描述

假设我经营一个网站，展示有趣的猫图片.我有一个名为 CatPictures 的表，其中包含 Filename、Awesomeness 和 DeletionDate 列，以及以下索引:

Assume I'm running a website that shows funny cat pictures. I have a table called CatPictures with the columns Filename, Awesomeness, and DeletionDate, and the following index:

create nonclustered index CatsByAwesomeness
on CatPictures (Awesomeness) 
include (Filename)
where DeletionDate is null

我的主要查询是这样的:

My main query is this:

select Filename from CatPictures where DeletionDate is null and Awesomeness > 10

作为一个人，我知道上面的索引就是SQL Server所需要的，因为索引过滤条件已经保证了DeletionDate为null部分.

I, as a human being, know that the above index is all that SQL Server needs, because the index filter condition already ensures the DeletionDate is null part.

SQL Server 不知道这一点；我的查询的执行计划不会使用我的索引:

SQL Server however doesn't know this; the execution plan for my query will not use my index:

即使添加了索引提示，它仍然会通过查看实际表数据来显式检查DeletionDate:

Even if adding an index hint, it will still explicitly check DeletionDate by looking at the actual table data:

(此外还抱怨缺少包含 DeletionDate 的索引).

(and in addition complain about a missing index that would include DeletionDate).

当然可以

include (Filename, DeletionDate)

相反，它会起作用:

但是包含该列似乎是一种浪费，因为这只会占用空间而没有添加任何新信息.

But it seems a waste to include that column, since this just uses up space without adding any new information.

有没有办法让 SQL Server 知道过滤条件已经在执行检查 DeletionDate 的工作?

Is there a way to make SQL Server aware that the filter condition is already doing the job of checking DeletionDate?

推荐答案

不，目前没有.

查看此连接项.它因无法修复而关闭.(或 这个专门针对IS NULL情况)

See this connect item. It is Closed as Won't Fix. (Or this one for the IS NULL case specifically)

连接项确实提供了如下所示的解决方法.

The connect item does provide a workaround shown below.

RichardB CFCU 于 29/09/2011 上午 9:15 发布

Posted by RichardB CFCU on 29/09/2011 at 9:15 AM

一种解决方法是INCLUDE被过滤的列.

A workaround is to INCLUDE the column that is being filtered on.

CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE] 
(
    [TABLE_ID] ASC,
    [TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)

这篇关于优化器忽略过滤的索引条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！