sql - 实现分页的有效方法

Question

我应该使用 LINQ 的 Skip() 和 Take() 方法进行分页，还是使用 SQL 查询实现我自己的分页？

哪个效率最高？为什么我会选择其中之一而不是另一个？

我正在使用 SQL Server 2008、ASP.NET MVC 和 LINQ。

Answer 1

尝试为您的疑问提供一个简短的答案，如果您在 linq 上执行 skip(n).take(m) 方法(使用 SQL 2005/2008 作为数据库服务器)，您的查询将使用 Select ROW_NUMBER() Over ... 语句，以某种方式在 SQL 引擎中直接分页。

举个例子，我有一个名为 mtcity 的数据库表，我编写了以下查询(也适用于 linq toEntity):

using (DataClasses1DataContext c = new DataClasses1DataContext())
{
    var query = (from MtCity2 c1 in c.MtCity2s
                select c1).Skip(3).Take(3);
    //Doing something with the query.
}

生成的查询将是:

SELECT [t1].[CodCity], 
    [t1].[CodCountry], 
    [t1].[CodRegion], 
    [t1].[Name],  
    [t1].[Code]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]) AS [ROW_NUMBER], 
        [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
    FROM [dbo].[MtCity] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

这是一个窗口数据访问(非常酷，顺便说一句，因为从一开始就会返回数据，并且只要满足条件就会访问表)。这将非常类似于:

With CityEntities As 
(
    Select ROW_NUMBER() Over (Order By CodCity) As Row,
        CodCity //here is only accessed by the Index as CodCity is the primary
    From dbo.mtcity
)
Select [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

异常(exception)的是，第二个查询的执行速度比 linq 结果更快，因为它将专门使用索引来创建数据访问窗口；这意味着，如果您需要一些过滤，过滤应该(或必须)在实体列表(创建行的位置)中，并且还应该创建一些索引以保持良好的性能。

现在，什么更好？

如果您的逻辑中有相当可靠的工作流程，那么实现正确的 SQL 方式将会很复杂。在这种情况下，LINQ 将是解决方案。

如果您可以将这部分逻辑直接降低到 SQL(在存储过程中)，那就更好了，因为您可以实现我向您展示的第二个查询(使用索引)并允许 SQL 生成和存储执行查询计划(提高性能)。