SQL Server索引

SQL Server索引 - 非常大的表，其中where子句针对非常小的值 - 我需要where子句的索引吗？(SQL Server index - very large table with where clause against a very small range of values - do I need an index for the where clause?)

我正在设计一个带有单个表的数据库，用于我需要实现解决方案的特殊方案。该表在短时间后将有几亿行，但每行都相当紧凑。即使有很多行，我也需要插入，更新和选择速度才能快速，所以我需要为作业选择最佳索引。

我的表看起来像这样：

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);

I am designing a database with a single table for a special scenario I need to implement a solution for. The table will have several hundred million rows after a short time, but each row will be fairly compact. Even when there are a lot of rows, I need insert, update and select speeds to be nice and fast, so I need to choose the best indexes for the job.

My table looks like this:

A select query will look like this:

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

The first type of update will look like this:

The second type of update will look like this:

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

Are these the best indexes for optimal insert, update and select speed?

最满意答案

观察：

您的更新应使用PK 为什么不使用tinyint（范围0-255）使行更窄？你需要约会时间吗？你能用smallledatetime吗？

思路：

您的SELECT查询没有覆盖它的索引。你需要一个（ DateToProcess, MetricType, Priority DESC) INCLUDE (Name) WHERE DateProcessed IS NULL ：你将不得不尝试使用键列顺序来获得最好的一个

您可以扩展该索引以使每个MetricType具有过滤索引（保持DateProcessed IS NULL过滤器）。当我有数百万行要测试时，我会在另一个之后执行此操作

Observations:

Your updates should use the PK Why not use tinyint (range 0-255) to make the rows even narrower? Do you need datetime? Can you use smalledatetime?

Ideas:

Your SELECT query doesn't have an index to cover it. You need one on (DateToProcess, MetricType, Priority DESC) INCLUDE (Name) WHERE DateProcessed IS NULL `: you'll have to experiment with key column order to get the best one

You could extent that index to have a filtered indexes per MetricType too (keeping DateProcessed IS NULL filter). I'd do this after the other one when I do have millions of rows to test with

更多推荐