sql-server - 在 SQL Server 中查询最小值比查询所有行要长很多

Question

当我在包含大约一亿行的表中查询特定日期的最小 ID 时，我在数据库中遇到了一个奇怪的行为。查询非常简单:

SELECT MIN(Id) FROM Connection WITH(NOLOCK) WHERE DateConnection = '2012-06-26'

这个查询永远不会结束，至少我让它运行了几个小时。 DateConnection 列不是索引，也不包含在其中。所以我知道这个查询可以持续很长时间。但是我尝试了以下在几秒钟内运行的查询:

SELECT Id FROM Connection WITH(NOLOCK) WHERE DateConnection = '2012-06-26'

它返回 30 万行。

我的表是这样定义的:

CREATE TABLE [dbo].[Connection](  
    [Id] [bigint] IDENTITY(1,1) NOT NULL,  
    [DateConnection] [datetime] NOT NULL,  
    [TimeConnection] [time](7) NOT NULL,  
    [Hour]  AS (datepart(hour,[TimeConnection])) PERSISTED NOT NULL,  
    CONSTRAINT [PK_Connection] PRIMARY KEY CLUSTERED   
    (  
        [Hour] ASC,  
        [Id] ASC  
    )  
)

它有以下索引:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Connection_Id] ON [dbo].[Connection]  
(  
    [Id] ASC  
)ON [PRIMARY]

我发现使用这种奇怪行为的一个解决方案是使用以下代码。但在我看来，对于这样一个简单的查询来说，它似乎有点沉重。

create table #TempId
(
    [Id] bigint
)
go

insert into #TempId
select id from partitionned_connection with(nolock) where dateconnection = '2012-06-26'

declare @displayId bigint
select @displayId = min(Id) from #CoIdTest

print @displayId 
go

drop table #TempId
go

有没有人遇到过这种行为，原因是什么？最小聚合扫描整个表吗？如果是这种情况，为什么简单的选择没有？

Answer 1

问题的根本原因是非对齐非聚集索引，结合统计限制 Martin Smith points out (有关详细信息，请参阅另一个问题的 his answer)。

您的表在 [Hour] 沿以下行分区:

CREATE PARTITION FUNCTION PF (integer)
AS RANGE RIGHT
FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23);

CREATE PARTITION SCHEME PS
AS PARTITION PF ALL TO ([PRIMARY]);

-- Partitioned
CREATE TABLE dbo.Connection
(
    Id              bigint IDENTITY(1,1) NOT NULL,
    DateConnection  datetime NOT NULL,
    TimeConnection  time(7) NOT NULL,
    [Hour]  AS (DATEPART(HOUR, TimeConnection)) PERSISTED NOT NULL,
    
    CONSTRAINT [PK_Connection]
    PRIMARY KEY CLUSTERED
    (  
        [Hour] ASC,  
        [Id] ASC  
    )
    ON PS ([Hour])
);

-- Not partitioned
CREATE UNIQUE NONCLUSTERED INDEX [IX_Connection_Id]
ON dbo.Connection
(  
    Id ASC
)ON [PRIMARY];

-- Pretend there are lots of rows
UPDATE STATISTICS dbo.Connection WITH ROWCOUNT = 200000000, PAGECOUNT = 4000000;

查询和执行计划是:

SELECT 
    MinID = MIN(c.Id)
FROM dbo.Connection AS c WITH (READUNCOMMITTED) 
WHERE
    c.DateConnection = '2012-06-26';

优化器利用索引(按 Id 排序)将 MIN 聚合转换为 TOP (1) - 因为根据定义，最小值将是有序流中遇到的第一个值。 (如果非聚集索引也被分区，优化器将不会选择此策略，因为所需的排序将丢失)。

有点复杂的是，我们还需要在 WHERE 子句中应用谓词，这需要查找基表以获取 DateConnection 值。 Martin 提到的统计限制解释了为什么优化器估计它只需要从有序索引中检查 119 行，然后才能找到具有与 WHERE 子句匹配的 DateConnection 值的行。 DateConnection 和 Id 值之间隐藏的相关性意味着这个估计还有很长的路要走。

如果您有兴趣，Compute Scalar 会计算要在哪个分区中执行键查找。对于非聚集索引中的每一行，它计算一个表达式，如 [PtnId1000] = Scalar Operator(RangePartitionNew([dbo].[Connection].[Hour] as [c].[Hour],(1),( 1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13) ,(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)))，这是用作查找查找的前导键。嵌套循环连接上有预取(预读)，但这需要有序预取以保留 TOP (1) 优化所需的排序。

解决方案

我们可以通过找到每个 Hour 值的最小 Id，然后取每小时最小值中的最小值，来避免统计限制(不使用查询提示) :

-- Global minimum
SELECT 
    MinID = MIN(PerHour.MinId)
FROM 
(
    -- Local minimums (for each distinct hour value)
    SELECT 
        MinID = MIN(c.Id)
    FROM dbo.Connection AS c WITH(READUNCOMMITTED) 
    WHERE
        c.DateConnection = '2012-06-26' 
    GROUP BY
        c.[Hour]
) AS PerHour;

执行计划是:

如果启用了并行性，您将看到一个更像下面的计划，它使用并行索引扫描和多线程流聚合来更快地产生结果: