从 Skip Take 到 Keyset：C# 分页原理与实践

简介

分页基础概念

• 核心分页参数

public class PaginationParams
{
   
   
    private const int MaxPageSize = 100; // 最大每页条数限制
    
    public int PageNumber {
   
    get; set; } = 1;    // 当前页码（从1开始）
    
    private int _pageSize = 10;                 // 每页记录数
    public int PageSize
    {
   
   
        get => _pageSize;
        set => _pageSize = (value > MaxPageSize) ? MaxPageSize : value;
    }
}

• 分页结果封装

public class PagedResult<T>
{
   
   
    public int CurrentPage {
   
    get; set; }
    public int PageSize {
   
    get; set; }
    public int TotalCount {
   
    get; set; }
    public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
    public List<T> Items {
   
    get; set; } = new List<T>();
}

普通分页（Offset & Limit）

为什么普通分页会变慢？

当 page * size 很大时，数据库仍要扫描并丢弃前面 N 条记录，IO 和排序开销显著，上千页甚至秒级以上延迟。此时可考虑「高性能分页」。

原理

• 使用数据库的 OFFSET … FETCH（SQL Server/PostgreSQL/SQLite）或 LIMIT … OFFSET（MySQL）进行分页。

• 优点：实现简单、支持任意页码跳转。

• 缺点：当页码（offset）较大时，数据库仍需扫描并跳过大量行，性能下降明显。

EF Core 实现

public async Task<PagedResult<Product>> GetProductsAsync(PaginationParams pagination)
{
   
   
    var query = _context.Products
        .Where(p => p.IsActive)
        .OrderBy(p => p.Name);
    
    var totalCount = await query.CountAsync();
    
    var items = await query
        .Skip((pagination.PageNumber - 1) * pagination.PageSize)
        .Take(pagination.PageSize)
        .ToListAsync();
    
    return new PagedResult<Product>
    {
   
   
        CurrentPage = pagination.PageNumber,
        PageSize = pagination.PageSize,
        TotalCount = totalCount,
        Items = items
    };
}

FreeSql 实现

public PagedResult<Product> GetProducts(PaginationParams pagination)
{
   
   
    var query = _fsql.Select<Product>()
        .Where(p => p.IsActive)
        .OrderBy(p => p.Name);
    
    var totalCount = query.Count();
    
    var items = query
        .Skip((pagination.PageNumber - 1) * pagination.PageSize)
        .Take(pagination.PageSize)
        .ToList();
    
    return new PagedResult<Product>
    {
   
   
        CurrentPage = pagination.PageNumber,
        PageSize = pagination.PageSize,
        TotalCount = totalCount,
        Items = items
    };
}

LinqToDB 实现

public PagedResult<Product> GetProducts(PaginationParams pagination)
{
   
   
    using (var db = new AppDbContext())
    {
   
   
        var query = db.Products
            .Where(p => p.IsActive)
            .OrderBy(p => p.Name);
        
        var totalCount = query.Count();
        
        var items = query
            .Skip((pagination.PageNumber - 1) * pagination.PageSize)
            .Take(pagination.PageSize)
            .ToList();
        
        return new PagedResult<Product>
        {
   
   
            CurrentPage = pagination.PageNumber,
            PageSize <