复杂谓词的索引

Question

我正在努力确定有效的索引(或重写查询(以改进具有以下混淆谓词的查询：

• JOIN 在一个表中的日期在范围内 - 在第二个表的两个日期字段之间(一个可为空，一个在 PK 中不可为空(。
• 使用的日期实际上是日期字段中的值(可为空(+1。
• WHERE 子句在多个标志字段上包含 OR 逻辑。

查询的简化版本为：

select
d.dim_date_id
,f.dim_provider_id
,f.dim_event_id
,d.date
from DWH.dbo.tbl_fact_outcome f
join   DWH.dbo.tbl_dim_date d on DATEADD(DAY,1,d.date) between f.known_from and f.known_to 
where
f.known_from > getdate()-12
and (d.flag_latest_day = 'Y' or  d.flag_end_of_month = 'Y'  or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
and d.flag_future_day = 'N'
and f.deleted = 0

tbl_fact_outcome具有以下索引：

• input_form_id上的 PK 聚集索引，known_from
• 删除、known_from known_to上的非唯一非聚集索引(包括必填_dim_id字段(

tbl_dim_date具有以下索引：

• dim_date_id上的 PK 聚集索引
• flag_future_day、日期上的非唯一非聚集索引(包括相关标志字段(

目前，它估计有 853 行，但返回 16,784 行。

下面是查询计划： https://www.brentozar.com/pastetheplan/?id=rydKb_3AI

统计数据是最新的。 我尝试对覆盖索引重新排序，但没有改进。

我完全不知道还能尝试使用索引或代码本身来提高性能，所以任何指针都值得赞赏。

<小时 />

编辑 05/07/2020

此处排除了以下建议：

• tbl_fact_outcome上过滤索引(已删除( - 只有不到 1% 的记录会被过滤掉，所以不值得
• tbl_dim_date上的过滤索引(使用查询中的整个 WHERE 子句( - 无法在索引中使用 OR
。
• 以 INCLUDEd 字段作为键字段的tbl_dim_date上的索引 - 尝试过这个，没有区别，优化器不使用。

Answer 1

猜测所有/大多数查询会过滤deleted我会建议使用过滤索引。

CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_fact_outcome (
known_from ASC
,known_to ASC
)
INCLUDE (dim_event_id,dim_provider_id)
WHERE deleted = 0;

如果此查询确实经常运行，您还可以考虑对tbl_dim_date使用过滤索引。这可能只由此查询使用，因为 where 与您的查询完全匹配：

CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_dim_date (DATE ASC)
WHERE (
d.flag_latest_day = 'Y'
OR d.flag_end_of_month = 'Y'
OR (
d.flag_end_of_week = 'Y'
AND d.flag_latest_week = 'Y'
)
)
AND d.flag_future_day = 'N'

如果您不希望在标志字段上使用筛选索引。您应该将标志字段添加到索引中，而不是包含。

CREATE NONCLUSTERED INDEX TodoNewIndexName ON DWH.dbo.tbl_dim_date (
DATE ASC
,flag_latest_day ASC
,flag_end_of_month ASC
,flag_end_of_week ASC
,flag_latest_week ASC
) 

这应该做的是摆脱有关急切线轴的更多信息Index Spool (Eager Spool)。

急切索引假脱机通常表明有用的永久索引是 数据库架构中缺少。情况并非总是如此，因为 流式处理表值函数示例所示。

Answer 2

您的日期维度是次日列或其他什么？如果没有，您可以添加此列并将 DATEADD(DAY，1，d.date( 替换为此新列。