非聚集索引，以帮助聚集索引？ （更正！） [英] Non Clustered Index to help Clustered Index ? (Corrected!)

问题描述

有人可以为我澄清一些SQL。

请参阅以下脚本和评论：

Can someone please clarify some SQL for me.
Please see the following script and comment:

CREATE TABLE [dbo].[TimeValues]
(
    [Time] DATETIME NOT NULL,
    [Collection_Id] INT NOT NULL,
    [Value] DECIMAL(18,6) NOT NULL,
    CONSTRAINT [PK_TimeValues] PRIMARY KEY ([Time],[Collection_Id]) ON [MyPartitionScheme]([Time])
) ON [MyPartitionScheme]([Time])

--The following script takes 2 min to run ???
SELECT
    DATEADD(DAY,DATEDIFF(DAY,'2000-01-01 00:00:00',[Time]),'2000-01-01 00:00:00') AS [Time]
    ,1 AS [Collection_Id]
    ,SUM([Value]) AS [Value]
FROM [dbo].[TimeValues]
WHERE [Time] BETWEEN '2000-01-01' AND '2020-01-01'
    AND Collection_Id = 1
GROUP BY DATEDIFF(DAY,'2000-01-01 00:00:00',[Time])

--If I add the following Index the script takes 0 sec. to run WHY ????
CREATE NONCLUSTERED INDEX [IX_TimeValues_Collection_Id_Time]
ON [dbo].[TimeValues]([Collection_Id],[Time])
INCLUDE ([Value]) WITH (FILLFACTOR = 80)

推荐答案

您的主键是复合词索引使用时间和 Collection_Id ，按此顺序。因此，当您的Where子句声明 [时间] BETWEEN'2000-01-01'和'2020-01-01'时，优化器最好使用范围扫描，但是如果此范围涵盖了大部分可用日期，但每个日期只有一个 Collection_Id ，那么它可能会采用全表扫描。

你可以通过检查执行计划来确认这一点。



当您添加以唯一 Collection_Id 开头的索引时然后是一个小的有序范围来聚合来自的值。

这是非常快的。

再次，检查执行计划。



如果您的所有查询都使用唯一的 Collection_Id ，我强烈建议您以与索引相同的顺序重新创建主键。

Your primary key is a composite index using Time and Collection_Id, in that order. So when your Where Clause states [Time] BETWEEN '2000-01-01' AND '2020-01-01' the optimizer will in best case use a range scan, but if this range covers a large part of available dates but with only one Collection_Id per date so it will probably resort to a full table scan.
You can probably confirm this by checking the execution plan.

When you add the index that starts with a unique Collection_Id and then one small ordered range to aggregate the values from.
This is very fast.
Again, check the execution plan.

If all your queries use unique Collection_Ids I would strongly suggest that you recreate your Primary key in the same order as your index.

这篇关于非聚集索引，以帮助聚集索引？ （更正！）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！