主页 / dba / 问题 / 307096
Accepted
Twicetimes
Asked: 2022-02-07 21:35:57 +0800 CST

为什么我看到所有读取行的键查找，而不是匹配 where 子句的所有行？

  • 772

我有一个如下表：

create table [Thing]
(
    [Id] int constraint [PK_Thing_Id] primary key,
    [Status] nvarchar(20),
    [Timestamp] datetime2,
    [Foo] nvarchar(100)
)

Status在andTimestamp字段上具有非聚集、非覆盖索引：

create nonclustered index [IX_Status_Timestamp] on [Thing] ([Status], [Timestamp] desc)

如果我查询这些行的“页面”，使用如下偏移/获取，

select * from [Thing]
where Status = 'Pending'
order by [Timestamp] desc
offset 2000 rows
fetch next 1000 rows only

我知道该查询需要读取总共 3000 行才能找到我感兴趣的 1000 行。然后我希望它对这 1000 行中的每一行执行键查找以获取索引中未包含的字段。

但是，执行计划表明它正在对所有 3000 行进行键查找。我不明白为什么，当唯一的标准（按 [Status] 过滤和按 [Timestamp] 排序）都在索引中时。

在此处输入图像描述

如果我用 cte 改写查询，如下所示，我或多或少地得到了我期望第一个查询执行的操作：

with ids as
(
    select Id from [Thing]
    where Status = 'Pending'
    order by [Timestamp] desc
    offset 2000 rows
    fetch next 1000 rows only
)

select t.* from [Thing] t
join ids on ids.Id = t.Id
order by [Timestamp] desc

在此处输入图像描述

来自 SSMS 的一些统计数据来比较这两个查询：

原来的 与 CTE
逻辑读取 12265 4140
子树成本 9.79 3.33
内存授予 0 3584 KB

乍一看，CTE 版本似乎“更好”，尽管我不知道它会为工作台带来内存授权这一事实有多大的重视。（来自的消息set statistics io on表明工作台上的任何类型的读取为零）

我说第一个查询应该能够首先隔离相关的 1000 行（即使这需要先读取 2000 其他行），然后只对这 1000 行进行键查找，我错了吗？不得不尝试使用 CTE 查询“强制”该行为似乎有点奇怪。

（作为次要的第二个问题：我假设 CTE 方法的最后一部分需要根据order by连接结果自行处理，即使 CTE 本身具有order by，因为在连接过程中可能会丢失排序。是这对吗？）

sql-server nonclustered-index

1 个回答

  1. Best Answer

    从根本上说，这是一个长期存在的优化器限制。

    SQL Server 不考虑将Key Lookup转换为Clustered Index Seek。Key Lookup必须几乎立即跟随与其关联的非聚集索引访问（出于I/O 原因，可能存在中间排序）。

    有几种方法可以重写查询以尽可能长时间地仅对索引键进行操作，而无需引入示例中看到的排序：

    WITH IDs AS
(
    SELECT T.* 
    FROM dbo.Thing AS T
    WHERE T.[Status] = N'Pending'
    ORDER BY T.[Timestamp] DESC
    OFFSET 2000 ROWS
    FETCH NEXT 1000 ROWS ONLY
)
SELECT 
    T.* 
FROM IDs AS I
JOIN dbo.Thing AS T
    ON T.Id = I.Id
ORDER BY
    I.[Timestamp] DESC;

    计划

    或者

    SELECT 
    T2.* 
FROM dbo.Thing AS T
JOIN dbo.Thing AS T2
    ON T2.Id = T.Id
WHERE 
    T.[Status] = N'Pending'
ORDER BY 
    T.[Timestamp] DESC
    OFFSET 2000 ROWS
    FETCH NEXT 1000 ROWS ONLY;

    计划

    通过帮助优化器“查看”保留排序顺序，消除了排序的需要。

    进一步阅读：

    • 15

相关问题