SQL Server如何在内存中缓存数据

Question

我有几个关于数据缓存工作原理的问题
想象一下我们有以下情况：

服务器重新启动或者我们刚刚运行了DBCC DROPCLEANBUFFERS

我们有一个大小为50 GB的Table1，它有列A、B、C、D、E。
列A是聚集索引键，列B和C上有非聚集索引。

1. 当我们执行

   select top 100 * from Table1
   

   是否会将整个聚集索引（表）从磁盘读入内存，即使我们只需要100行？ 还是只有100行（它们的数据页）从磁盘读入内存？

2. 对于非聚集索引也是一样，当我们执行

   select top 100 * from Table1 where column B = 'some value'
   

   是否会将整个非聚集索引 + 聚集索引加载到内存中？ 还是只有来自非聚集索引的100行和来自聚集索引的100行？

- Aleksey Vitsko

Answer 1

测试数据

CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
                     ,B varchar(255),C int,D int,E  int);

INSERT  INTO dbo.Table1 WITH(TABLOCK)
(B,C,D,E)

SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

CREATE INDEX IX_B
On dbo.Table1(B);
CREATE INDEX IX_C
On dbo.Table1(C);

---

问题1

当我们执行"select top 100 * from Table1"时，整个聚集索引（表）是否会从磁盘读取到内存中，即使我们只需要100行？还是只有100行（它们的数据页）从磁盘读取到内存中？

只有来自聚集索引的100行被读取。因此，只有它们的数据页被缓存在内存中。

示例

在我的情况下，它从聚集索引向下读取，获取列A的1-100个值。

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1

这是一个读取操作：

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

4个逻辑读取 = 4页

你可能会问，为什么我们仍然看到扫描计数=1，但只有4个逻辑读取？

因为它正在执行一个范围扫描：

为了满足顶级运算符。

---

Q2

和非聚集索引一样，当我们执行 "select top 100 * from Table1 where column B = 'some value'" 这个查询时，是将整个非聚集索引和聚集索引都加载到内存中，还是只加载非聚集索引的100行和聚集索引的100行？

示例1

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1
WHERE B='Some Value 200'

你可能期望在这里使用非聚集索引，但实际上仍然使用了聚集索引。

清除缓存后，逻辑读取次数为287次，预读取次数为2528次。

Table 'Table1'. Scan count 1, logical reads 287, physical reads 1, read-ahead reads 2528, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

我们在从磁盘读取这些预读数据后，将其缓存在内存中，然后从内存中读取287页。

预读机制是SQL Server的一个功能，它在查询请求数据之前，将数据页面提前加载到缓冲区缓存中。 来源

如果我们检查了缓存的页面：

cached_pages_count  objectname  indexname   indexid
2536    Table1  PK__Table1__3BD019AE039DA497    1

我们也看到了这个。

所以在这种情况下，我们会缓存更多的页面以更快地满足查询，因为我们需要读取更多的行来应用剩余谓词：

但我们只是从聚集索引中读取这些页面。

示例2

您可以更改查询以利用非聚集索引：

select top 100 B from dbo.Table1
WHERE B='Some Value 200';

它所做的事情是：

这次又是一个小范围的扫描：

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这里只会加载非聚集索引页到内存中。 示例3 我们可以通过使用索引提示来强制执行一个键查找以满足示例1中的查询。

SET STATISTICS IO, TIME ON;
select top 100 * 
from dbo.Table1
WITH(INDEX(IX_B))
WHERE B='Some Value 200';

这又导致了一些预读和比例读取的次数比example1多。

Table 'Table1'. Scan count 1, logical reads 684, physical reads 3, read-ahead reads 465, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

由于我们正在从非聚集索引查询到聚集索引，所以我们将缓存来自两个索引的页面。

- Randi Vertongen

---

非常感谢Randi提供的出色示例！我会在今天或明天稍后查看并标记为答案。 - Aleksey Vitsko

@voodoo_sh 没问题：）。如果需要我添加部分/更改部分/详细说明，请告诉我。 - Randi Vertongen

Answer 2

即使我们只需要100行？或者只有100行（它们的数据页）从磁盘读入内存？ 这可能是由于SQL查询的逻辑处理顺序，因为TOP子句将在逻辑顺序的最后考虑。更多细节..。因此，当在WHERE子句中应用过滤器以仅返回100行时，它将读取较少的页面，而不是应用TOP 100。

- Shekar Kola