Back to Details

api-pagination

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

API Pagination

API 分页

Overview

概述

Implement scalable pagination strategies for handling large datasets with efficient querying, navigation, and performance optimization.
实现可扩展的分页策略，以高效的查询、导航和性能优化来处理大型数据集。

When to Use

适用场景

  • Returning large collections of resources
  • Implementing search results pagination
  • Building infinite scroll interfaces
  • Optimizing large dataset queries
  • Managing memory in client applications
  • Improving API response times
  • 返回大型资源集合
  • 实现搜索结果分页
  • 构建无限滚动界面
  • 优化大型数据集查询
  • 管理客户端应用内存
  • 提升API响应速度

Instructions

实现步骤

1. Offset/Limit Pagination

1. 偏移量/限制分页

javascript
// Node.js offset/limit implementation
app.get('/api/users', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100); // Max 100
  const offset = (page - 1) * limit;

  try {
    const [users, total] = await Promise.all([
      User.find()
        .skip(offset)
        .limit(limit)
        .select('id email firstName lastName createdAt'),
      User.countDocuments()
    ]);

    const totalPages = Math.ceil(total / limit);

    res.json({
      data: users,
      pagination: {
        page,
        limit,
        total,
        totalPages,
        hasNext: page < totalPages,
        hasPrev: page > 1
      },
      links: {
        self: `/api/users?page=${page}&limit=${limit}`,
        first: `/api/users?page=1&limit=${limit}`,
        last: `/api/users?page=${totalPages}&limit=${limit}`,
        ...(page > 1 && { prev: `/api/users?page=${page - 1}&limit=${limit}` }),
        ...(page < totalPages && { next: `/api/users?page=${page + 1}&limit=${limit}` })
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Python offset/limit
from flask import request
from sqlalchemy import func

@app.route('/api/users', methods=['GET'])
def list_users():
    page = request.args.get('page', 1, type=int)
    limit = min(request.args.get('limit', 20, type=int), 100)
    offset = (page - 1) * limit

    total = db.session.query(func.count(User.id)).scalar()
    users = db.session.query(User).offset(offset).limit(limit).all()

    total_pages = (total + limit - 1) // limit

    return jsonify({
        'data': [u.to_dict() for u in users],
        'pagination': {
            'page': page,
            'limit': limit,
            'total': total,
            'totalPages': total_pages,
            'hasNext': page < total_pages,
            'hasPrev': page > 1
        }
    }), 200
javascript
// Node.js offset/limit implementation
app.get('/api/users', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100); // Max 100
  const offset = (page - 1) * limit;

  try {
    const [users, total] = await Promise.all([
      User.find()
        .skip(offset)
        .limit(limit)
        .select('id email firstName lastName createdAt'),
      User.countDocuments()
    ]);

    const totalPages = Math.ceil(total / limit);

    res.json({
      data: users,
      pagination: {
        page,
        limit,
        total,
        totalPages,
        hasNext: page < totalPages,
        hasPrev: page > 1
      },
      links: {
        self: `/api/users?page=${page}&limit=${limit}`,
        first: `/api/users?page=1&limit=${limit}`,
        last: `/api/users?page=${totalPages}&limit=${limit}`,
        ...(page > 1 && { prev: `/api/users?page=${page - 1}&limit=${limit}` }),
        ...(page < totalPages && { next: `/api/users?page=${page + 1}&limit=${limit}` })
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Python offset/limit
from flask import request
from sqlalchemy import func

@app.route('/api/users', methods=['GET'])
def list_users():
    page = request.args.get('page', 1, type=int)
    limit = min(request.args.get('limit', 20, type=int), 100)
    offset = (page - 1) * limit

    total = db.session.query(func.count(User.id)).scalar()
    users = db.session.query(User).offset(offset).limit(limit).all()

    total_pages = (total + limit - 1) // limit

    return jsonify({
        'data': [u.to_dict() for u in users],
        'pagination': {
            'page': page,
            'limit': limit,
            'total': total,
            'totalPages': total_pages,
            'hasNext': page < total_pages,
            'hasPrev': page > 1
        }
    }), 200

2. Cursor-Based Pagination

2. 基于游标分页

javascript
// Cursor-based pagination for better performance
class CursorPagination {
  static encode(value) {
    return Buffer.from(String(value)).toString('base64');
  }

  static decode(cursor) {
    return Buffer.from(cursor, 'base64').toString('utf-8');
  }

  static generateCursor(resource) {
    return this.encode(`${resource.id}:${resource.createdAt.getTime()}`);
  }

  static parseCursor(cursor) {
    if (!cursor) return null;
    const decoded = this.decode(cursor);
    const [id, timestamp] = decoded.split(':');
    return { id, timestamp: parseInt(timestamp) };
  }
}

app.get('/api/users/cursor', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const after = req.query.after ? CursorPagination.parseCursor(req.query.after) : null;

  try {
    const query = {};
    if (after) {
      query.createdAt = { $lt: new Date(after.timestamp) };
    }

    const users = await User.find(query)
      .sort({ createdAt: -1, _id: -1 })
      .limit(limit + 1)
      .select('id email firstName lastName createdAt');

    const hasMore = users.length > limit;
    const data = hasMore ? users.slice(0, limit) : users;
    const nextCursor = hasMore ? CursorPagination.generateCursor(data[data.length - 1]) : null;

    res.json({
      data,
      pageInfo: {
        hasNextPage: hasMore,
        endCursor: nextCursor,
        totalCount: await User.countDocuments()
      },
      links: {
        self: `/api/users/cursor?limit=${limit}`,
        next: nextCursor ? `/api/users/cursor?limit=${limit}&after=${nextCursor}` : null
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});
javascript
// Cursor-based pagination for better performance
class CursorPagination {
  static encode(value) {
    return Buffer.from(String(value)).toString('base64');
  }

  static decode(cursor) {
    return Buffer.from(cursor, 'base64').toString('utf-8');
  }

  static generateCursor(resource) {
    return this.encode(`${resource.id}:${resource.createdAt.getTime()}`);
  }

  static parseCursor(cursor) {
    if (!cursor) return null;
    const decoded = this.decode(cursor);
    const [id, timestamp] = decoded.split(':');
    return { id, timestamp: parseInt(timestamp) };
  }
}

app.get('/api/users/cursor', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const after = req.query.after ? CursorPagination.parseCursor(req.query.after) : null;

  try {
    const query = {};
    if (after) {
      query.createdAt = { $lt: new Date(after.timestamp) };
    }

    const users = await User.find(query)
      .sort({ createdAt: -1, _id: -1 })
      .limit(limit + 1)
      .select('id email firstName lastName createdAt');

    const hasMore = users.length > limit;
    const data = hasMore ? users.slice(0, limit) : users;
    const nextCursor = hasMore ? CursorPagination.generateCursor(data[data.length - 1]) : null;

    res.json({
      data,
      pageInfo: {
        hasNextPage: hasMore,
        endCursor: nextCursor,
        totalCount: await User.countDocuments()
      },
      links: {
        self: `/api/users/cursor?limit=${limit}`,
        next: nextCursor ? `/api/users/cursor?limit=${limit}&after=${nextCursor}` : null
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

3. Keyset Pagination

3. 键集分页

javascript
// Keyset pagination (most efficient for large datasets)
app.get('/api/products/keyset', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const lastId = req.query.lastId;
  const sortBy = req.query.sort || 'price'; // price or createdAt

  try {
    const query = {};

    // Build query based on sort field
    if (lastId) {
      const lastProduct = await Product.findById(lastId);

      if (sortBy === 'price') {
        query.$or = [
          { price: { $lt: lastProduct.price } },
          { price: lastProduct.price, _id: { $lt: lastId } }
        ];
      } else {
        query.$or = [
          { createdAt: { $lt: lastProduct.createdAt } },
          { createdAt: lastProduct.createdAt, _id: { $lt: lastId } }
        ];
      }
    }

    const products = await Product.find(query)
      .sort({ [sortBy]: -1, _id: -1 })
      .limit(limit + 1);

    const hasMore = products.length > limit;
    const data = hasMore ? products.slice(0, limit) : products;

    res.json({
      data,
      pageInfo: {
        hasMore,
        lastId: data.length > 0 ? data[data.length - 1]._id : null
      },
      links: {
        next: hasMore && data.length > 0
          ? `/api/products/keyset?lastId=${data[data.length - 1]._id}&sort=${sortBy}&limit=${limit}`
          : null
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});
javascript
// Keyset pagination (most efficient for large datasets)
app.get('/api/products/keyset', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const lastId = req.query.lastId;
  const sortBy = req.query.sort || 'price'; // price or createdAt

  try {
    const query = {};

    // Build query based on sort field
    if (lastId) {
      const lastProduct = await Product.findById(lastId);

      if (sortBy === 'price') {
        query.$or = [
          { price: { $lt: lastProduct.price } },
          { price: lastProduct.price, _id: { $lt: lastId } }
        ];
      } else {
        query.$or = [
          { createdAt: { $lt: lastProduct.createdAt } },
          { createdAt: lastProduct.createdAt, _id: { $lt: lastId } }
        ];
      }
    }

    const products = await Product.find(query)
      .sort({ [sortBy]: -1, _id: -1 })
      .limit(limit + 1);

    const hasMore = products.length > limit;
    const data = hasMore ? products.slice(0, limit) : products;

    res.json({
      data,
      pageInfo: {
        hasMore,
        lastId: data.length > 0 ? data[data.length - 1]._id : null
      },
      links: {
        next: hasMore && data.length > 0
          ? `/api/products/keyset?lastId=${data[data.length - 1]._id}&sort=${sortBy}&limit=${limit}`
          : null
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

4. Search Pagination

4. 搜索分页

javascript
// Full-text search with pagination
app.get('/api/search', async (req, res) => {
  const query = req.query.q;
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  if (!query) {
    return res.status(400).json({ error: 'Search query required' });
  }

  try {
    // MongoDB text search example
    const [results, total] = await Promise.all([
      Product.find(
        { $text: { $search: query } },
        { score: { $meta: 'textScore' } }
      )
        .sort({ score: { $meta: 'textScore' } })
        .skip(offset)
        .limit(limit),
      Product.countDocuments({ $text: { $search: query } })
    ]);

    const totalPages = Math.ceil(total / limit);

    res.json({
      query,
      results,
      pagination: {
        page,
        limit,
        total,
        totalPages
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Elasticsearch pagination
async function searchElasticsearch(query, page = 1, limit = 20) {
  const from = (page - 1) * limit;

  const response = await esClient.search({
    index: 'products',
    body: {
      from,
      size: limit,
      query: {
        multi_match: {
          query,
          fields: ['name^2', 'description', 'category']
        }
      }
    }
  });

  return {
    results: response.hits.hits.map(hit => hit._source),
    pagination: {
      page,
      limit,
      total: response.hits.total.value,
      totalPages: Math.ceil(response.hits.total.value / limit)
    }
  };
}
javascript
// Full-text search with pagination
app.get('/api/search', async (req, res) => {
  const query = req.query.q;
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  if (!query) {
    return res.status(400).json({ error: 'Search query required' });
  }

  try {
    // MongoDB text search example
    const [results, total] = await Promise.all([
      Product.find(
        { $text: { $search: query } },
        { score: { $meta: 'textScore' } }
      )
        .sort({ score: { $meta: 'textScore' } })
        .skip(offset)
        .limit(limit),
      Product.countDocuments({ $text: { $search: query } })
    ]);

    const totalPages = Math.ceil(total / limit);

    res.json({
      query,
      results,
      pagination: {
        page,
        limit,
        total,
        totalPages
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Elasticsearch pagination
async function searchElasticsearch(query, page = 1, limit = 20) {
  const from = (page - 1) * limit;

  const response = await esClient.search({
    index: 'products',
    body: {
      from,
      size: limit,
      query: {
        multi_match: {
          query,
          fields: ['name^2', 'description', 'category']
        }
      }
    }
  });

  return {
    results: response.hits.hits.map(hit => hit._source),
    pagination: {
      page,
      limit,
      total: response.hits.total.value,
      totalPages: Math.ceil(response.hits.total.value / limit)
    }
  };
}

5. Pagination Response Formats

5. 分页响应格式

json
// Offset/Limit Response
{
  "data": [...],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 145,
    "totalPages": 8,
    "hasNext": true,
    "hasPrev": true
  },
  "links": {
    "self": "/api/users?page=2&limit=20",
    "first": "/api/users?page=1&limit=20",
    "prev": "/api/users?page=1&limit=20",
    "next": "/api/users?page=3&limit=20",
    "last": "/api/users?page=8&limit=20"
  }
}

// Cursor-Based Response
{
  "data": [...],
  "pageInfo": {
    "hasNextPage": true,
    "endCursor": "Y3JlYXRlZEF0OjE2NzA4ODA2MzU3NQ==",
    "totalCount": 1250
  },
  "links": {
    "next": "/api/users?limit=20&after=Y3JlYXRlZEF0OjE2NzA4ODA2MzU3NQ=="
  }
}

// Keyset Response
{
  "data": [...],
  "pageInfo": {
    "hasMore": true,
    "lastId": "507f1f77bcf86cd799439011"
  },
  "links": {
    "next": "/api/products?lastId=507f1f77bcf86cd799439011&sort=price"
  }
}
json
// Offset/Limit Response
{
  "data": [...],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 145,
    "totalPages": 8,
    "hasNext": true,
    "hasPrev": true
  },
  "links": {
    "self": "/api/users?page=2&limit=20",
    "first": "/api/users?page=1&limit=20",
    "prev": "/api/users?page=1&limit=20",
    "next": "/api/users?page=3&limit=20",
    "last": "/api/users?page=8&limit=20"
  }
}

// Cursor-Based Response
{
  "data": [...],
  "pageInfo": {
    "hasNextPage": true,
    "endCursor": "Y3JlYXRlZEF0OjE2NzA4ODA2MzU3NQ==",
    "totalCount": 1250
  },
  "links": {
    "next": "/api/users?limit=20&after=Y3JlYXRlZEF0OjE2NzA4ODA2MzU3NQ=="
  }
}

// Keyset Response
{
  "data": [...],
  "pageInfo": {
    "hasMore": true,
    "lastId": "507f1f77bcf86cd799439011"
  },
  "links": {
    "next": "/api/products?lastId=507f1f77bcf86cd799439011&sort=price"
  }
}

6. Python Pagination (SQLAlchemy)

6. Python 分页（SQLAlchemy）

python
from flask import request, jsonify
from flask_sqlalchemy import Pagination

@app.route('/api/users', methods=['GET'])
def list_users():
    page = request.args.get('page', 1, type=int)
    per_page = min(request.args.get('per_page', 20, type=int), 100)

    pagination: Pagination = User.query.paginate(
        page=page,
        per_page=per_page,
        error_out=False
    )

    return jsonify({
        'data': [user.to_dict() for user in pagination.items],
        'pagination': {
            'page': pagination.page,
            'per_page': pagination.per_page,
            'total': pagination.total,
            'pages': pagination.pages,
            'has_next': pagination.has_next,
            'has_prev': pagination.has_prev
        }
    }), 200
python
from flask import request, jsonify
from flask_sqlalchemy import Pagination

@app.route('/api/users', methods=['GET'])
def list_users():
    page = request.args.get('page', 1, type=int)
    per_page = min(request.args.get('per_page', 20, type=int), 100)

    pagination: Pagination = User.query.paginate(
        page=page,
        per_page=per_page,
        error_out=False
    )

    return jsonify({
        'data': [user.to_dict() for user in pagination.items],
        'pagination': {
            'page': pagination.page,
            'per_page': pagination.per_page,
            'total': pagination.total,
            'pages': pagination.pages,
            'has_next': pagination.has_next,
            'has_prev': pagination.has_prev
        }
    }), 200

Cursor pagination with graphene

Cursor pagination with graphene

class UserNode(relay.Node): class Meta: model = User
@classmethod
def get_node(cls, info, id):
    return User.query.get(id)
class Query(graphene.ObjectType): users = relay.ConnectionField(UserNode)
def resolve_users(self, info, **kwargs):
    return User.query.all()
undefined
class UserNode(relay.Node): class Meta: model = User
@classmethod
def get_node(cls, info, id):
    return User.query.get(id)
class Query(graphene.ObjectType): users = relay.ConnectionField(UserNode)
def resolve_users(self, info, **kwargs):
    return User.query.all()
undefined

Best Practices

最佳实践

✅ DO

✅ 建议

  • Use cursor pagination for large datasets
  • Set reasonable maximum limits (e.g., 100)
  • Include total count when feasible
  • Provide navigation links
  • Document pagination strategy
  • Use indexed fields for sorting
  • Cache pagination results when appropriate
  • Handle edge cases (empty results)
  • Implement consistent pagination formats
  • Use keyset for extremely large datasets
  • 对大型数据集使用游标分页
  • 设置合理的最大限制（例如100条）
  • 在可行的情况下包含总计数
  • 提供导航链接
  • 记录分页策略
  • 对用于排序的字段建立索引
  • 适当时缓存分页结果
  • 处理边缘情况（空结果）
  • 实现一致的分页格式
  • 对超大型数据集使用键集分页

❌ DON'T

❌ 避免

  • Use offset with billions of rows
  • Allow unlimited page sizes
  • Count rows for every request
  • Paginate without sorting
  • Change sort order mid-pagination
  • Use deep pagination without cursor
  • Skip pagination for large datasets
  • Expose database pagination directly
  • Mix pagination strategies
  • Ignore performance implications
  • 对数十亿条数据使用偏移量分页
  • 允许无限制的页面大小
  • 每次请求都统计行数
  • 不排序就进行分页
  • 分页过程中更改排序顺序
  • 不使用游标进行深度分页
  • 不为大型数据集实现分页
  • 直接暴露数据库分页方式
  • 混合使用多种分页策略
  • 忽略性能影响

Performance Tips

性能优化技巧

  • Index fields used for sorting
  • Use database-native pagination
  • Implement caching at application level
  • Monitor query performance
  • Use cursor pagination for large datasets
  • Avoid COUNT queries when possible
  • Consider denormalization for frequently accessed data
  • 对用于排序的字段建立索引
  • 使用数据库原生分页
  • 在应用层实现缓存
  • 监控查询性能
  • 对大型数据集使用游标分页
  • 尽可能避免COUNT查询
  • 考虑对频繁访问的数据进行反规范化处理