SQL Server 索引 - 非常大的表，带有针对非常小范围值的 where 子句 - 我是否需要为 where 子句建立索引？

Question

我正在设计一个带有单个表的数据库，用于我需要实施解决方案的特殊场景。不久之后，该表将拥有数亿行，但每行将相当紧凑。即使有很多行，我也需要插入、更新和选择速度又好又快，所以我需要为作业选择最佳索引。

我的表如下所示：

create table dbo.Domain
(
    Name varchar(255) not null,
    MetricType smallint not null, -- very small range of values, maybe 10-20 at most
    Priority smallint not null, -- extremely small range of values, generally 1-4
    DateToProcess datetime not null,
    DateProcessed datetime null,

    primary key(Name, MetricType)
);

选择查询将如下所示：

select Name from Domain
where MetricType = @metricType
    and DateProcessed is null
    and DateToProcess < GETUTCDATE()
order by Priority desc, DateToProcess asc

第一种更新类型将如下所示：

merge into Domain as target
using @myTablePrm as source
on source.Name = target.Name
    and source.MetricType = target.MetricType
when matched then
    update set
        DateToProcess = source.DateToProcess,
        Priority = source.Priority,
        DateProcessed = case -- set to null if DateToProcess is in the future
            when DateToProcess < DateProcessed then DateProcessed
            else null end
when not matched then
    insert (Name, MetricType, Priority, DateToProcess)
    values (source.Name, source.MetricType, source.Priority, source.DateToProcess);

第二种更新类型将如下所示：

update Domain
set DateProcessed = source.DateProcessed
from @myTablePrm source
where Name = source.Name and MetricType = @metricType

这些是实现最佳插入、更新和选择速度的最佳索引吗?

-- for the order by clause in the select query
create index IX_Domain_PriorityQueue
    on Domain(Priority desc, DateToProcess asc)
    where DateProcessed is null;

-- for the where clause in the select query
create index IX_Domain_MetricType
    on Domain(MetricType asc);

Answer 1

观察：

• 您的更新应该使用 PK
• 为什么不使用tinyint（范围0-255）来使行更窄？
• 您需要日期时间吗？你可以使用smalledatetime吗？

想法：

• 您的 SELECT 查询没有索引来覆盖它。您需要一个 (DateToProcess, MetricType, Priority DESC) INCLUDE (Name) WHERE DateProcessed IS NULL
  `：您必须尝试键列顺序才能获得最佳顺序

• 您可以扩展该索引，以便也为每个 MetricType 提供过滤索引（保留 DateProcessed IS NULL 过滤器）。当我确实有数百万行要测试时，我会在另一行之后执行此操作

Answer 2

我怀疑您的最佳性能来自于在 Priority 和 MetricType 上没有索引。对于索引来说，基数可能太低，无法发挥多大作用。

DateToProcess 上的索引几乎肯定会有所帮助，因为该列中的基数很高，并且它用于 WHERE 和 ORDER BY > 条款。我首先从那开始。

DateProcessed 上的索引是否有帮助还有待讨论。这取决于您期望此列中 NULL 值的百分比。与往常一样，最好的选择是使用一些真实数据检查查询计划。

Answer 3

在表架构部分中，您已突出显示“MetricType”是两个主键之一，因此它肯定应该与“名称”列一起建立索引。至于“Priority”和“DateToProcess”字段，因为它们将出现在 where 子句中，将它们编入索引不会有什么坏处，但我不建议“DateProcessed”索引上的 where 子句为 null ，仅对一组数据建立索引不是一个好主意，请将其删除并为所有这些列建立索引。