主页 / dba / 问题 / 154227
Accepted
AV94
Asked: 2016-11-04 21:42:28 +0800 CST

SQL Server 中的分页

  • 772

我有一个非常大的数据库，大约 100 GB。我正在执行查询：

select * from <table_name>;

我只想显示第 100 到第 200 行。

我想了解这是如何在内部发生的。数据库是否将所有记录从磁盘提取到内存并将第 100 到第 400 行发送回查询客户端？或者是否存在任何机制，以便仅从数据库中获取那些记录（第 100 条 -200 条） - 通过使用 B 树等索引机制？

我发现这与分页概念有关，但我无法准确找到它在数据库级别内部是如何发生的。

sql-server pagination

3 个回答

  1. Best Answer

    在您发布的查询中：

    select * from <table_name>;

    没有第 100-200 行这样的东西，因为您没有指定 ORDER BY。除非您出于许多有趣的原因包括 ORDER BY，否则无法保证订单，但这并不是重点。

    因此，为了说明您的观点，让我们使用一个表 - 我将使用Stack Overflow 数据转储中的 Users 表，并运行以下查询：

    SELECT * FROM dbo.Users ORDER BY DisplayName;

    默认情况下，DisplayName 字段没有索引，因此 SQL Server 必须扫描整个表，然后按 DisplayName 对其进行排序。这是执行计划：

    带排序的聚集索引扫描

    这并不漂亮 - 工作量很大，估计子树成本约为 30k。（您可以通过将鼠标悬停在 PasteThePlan 的选择运算符上来查看它。）那么如果我们只想要第 100-200 行会发生什么？我们可以在 SQL Server 2012+ 中使用这种语法：

    SELECT * FROM dbo.Users ORDER BY DisplayName OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

    执行计划也很丑陋：

    带有排序和顶部的聚集索引扫描

    SQL Server 仍在扫描整个表来构建排序列表，只是为了给你 100-200 行，成本仍然在 30k 左右。更糟糕的是，每次运行查询时都会重新构建整个列表（因为毕竟，有人可能已经更改了他们的 DisplayName。）

    为了让它更快，我们可以在 DisplayName 上创建一个非聚集索引，它是我们表的副本，按特定字段排序：

    CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

    使用该索引，我们的查询执行计划现在执行索引查找：

    索引查找和键查找

    查询立即完成，估计子树成本仅为 0.66（而不是 30k）。

    总之，如果您以支持您经常运行的查询的方式组织数据，那么是的，SQL Server 可以采取捷径让您的查询运行得更快。另一方面，如果你只有堆或聚集索引，那你就完蛋了。

    • 37

  2. 就像在使用非覆盖索引来避免排序时对布伦特的回答的补充一样，后面的页码可能存在问题，可以从运行下面看到

    SELECT * 
FROM dbo.Users 
ORDER BY DisplayName 
OFFSET 100000 ROWS 
FETCH NEXT 100 ROWS ONLY;

    执行计划显示查找执行了 100,100 次，尽管 TOP 运算符随后过滤掉了除 100 之外的所有行。

    在此处输入图像描述

    这可以通过使用下面的模式来缓解

    WITH T
     AS (SELECT Id,
                DisplayName
         FROM   dbo.Users
         ORDER  BY DisplayName
        OFFSET 100000 ROWS 
        FETCH NEXT 100 ROWS ONLY
        )
SELECT U.*
FROM   dbo.Users U
       JOIN T
         ON U.Id = T.Id
ORDER  BY T.DisplayName

    这会在进行查找之前过滤掉除最后 100 行之外的所有行，这会对大偏移值的速度产生重大影响。

    在此处输入图像描述

    • 16

  3. 这实际上取决于您如何在查询中实现分页、数据的性质以及系统的配置方式。可以肯定地说，SQL Server 将尝试使用它认为尽可能少的工作量来返回您的数据。如果您没有明确的排序顺序、过滤、分组或任何窗口化，那么 SQL Server 可能会优化查询计划，以便它可以只返回磁盘中包含查询所需数据的页面——甚至更好，直接从缓冲池。一旦您开始将查询更改为包括排序、分组、窗口化和过滤，它就会开始变得复杂。

    这里有一篇关于 SQL 性能的非常好的文章，详细介绍了各种分页方法以及它们如何影响查询计划。我强烈建议您阅读它，然后尝试他们指出的一些不同方法，看看在您自己的系统上选择了哪种查询计划。

    • 3

相关问题