分页查询

要点

分页查询避免一次性加载大量数据，提高性能
Offset 分页简单直观，但深分页性能差
Cursor 分页性能好，适合无限滚动
关键集分页是 Cursor 分页的优化版本
AI 项目的对话列表、文档列表通常需要分页
选择合适的分页方式取决于使用场景

1. Offset 分页

Offset 分页是最常见的分页方式，通过 OFFSET 和 LIMIT 跳过前面的记录：

// Drizzle
import { db } from './lib/db'
import { conversations } from './schema'
 
const page = 2
const pageSize = 10
 
const results = await db
  .select()
  .from(conversations)
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .orderBy(conversations.createdAt)

// Prisma
const results = await prisma.conversation.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
})

1.1 API 设计

app.get('/conversations', async (c) => {
  const page = parseInt(c.req.query('page') ?? '1')
  const pageSize = parseInt(c.req.query('pageSize') ?? '10')
 
  const [items, total] = await Promise.all([
    db.select().from(conversations)
      .limit(pageSize)
      .offset((page - 1) * pageSize)
      .orderBy(conversations.createdAt),
    db.select({ count: sql<number>`count(*)` }).from(conversations),
  ])
 
  return c.json({
    items,
    pagination: {
      page,
      pageSize,
      total: total[0].count,
      totalPages: Math.ceil(total[0].count / pageSize),
    },
  })
})

1.2 优点

简单直观
可以跳转到任意页
可以获取总数

1.3 缺点

深分页性能差：OFFSET 100000 需要扫描前 100000 行再丢弃
数据不一致：分页期间如果有新数据插入，会出现重复或遗漏

2. Cursor 分页

Cursor 分页通过上一页的最后一条记录的标识（通常是 ID 或创建时间）来获取下一页：

// Drizzle
const cursor = c.req.query('cursor')  // 上一页最后一条的 ID
 
const results = await db
  .select()
  .from(conversations)
  .where(cursor ? gt(conversations.id, cursor) : undefined)
  .limit(pageSize)
  .orderBy(conversations.id)

// Prisma
const results = await prisma.conversation.findMany({
  take: pageSize,
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { id: 'asc' },
})

2.1 API 设计

app.get('/conversations', async (c) => {
  const cursor = c.req.query('cursor')
  const pageSize = parseInt(c.req.query('pageSize') ?? '10')
 
  const items = await db
    .select()
    .from(conversations)
    .where(cursor ? gt(conversations.id, cursor) : undefined)
    .limit(pageSize + 1)  // 多取一条，判断是否有下一页
    .orderBy(conversations.id)
 
  const hasMore = items.length > pageSize
  const data = items.slice(0, pageSize)
  const nextCursor = hasMore ? data[data.length - 1].id : null
 
  return c.json({
    items: data,
    nextCursor,
    hasMore,
  })
})

2.2 优点

性能好：不需要扫描和丢弃前面的行
数据一致：不受新数据插入影响

2.3 缺点

不能跳转：只能一页一页翻
不能获取总数（除非单独查询）
实现稍复杂

3. 关键集分页

关键集分页是 Cursor 分页的优化版本，使用排序字段的值而不是 ID 作为游标：

// 按创建时间排序，用时间作为游标
const cursor = c.req.query('cursor')  // ISO 时间字符串
 
const results = await db
  .select()
  .from(conversations)
  .where(cursor ? gt(conversations.createdAt, new Date(cursor)) : undefined)
  .limit(pageSize)
  .orderBy(conversations.createdAt)

这样前端不需要知道数据库的 ID 结构，游标是不透明的时间戳。

4. 无限滚动

无限滚动通常用 Cursor 分页实现：

// 前端
let cursor = null
 
async function loadMore() {
  const url = new URL('/api/conversations', window.location.origin)
  if (cursor) url.searchParams.set('cursor', cursor)
 
  const res = await fetch(url)
  const { items, nextCursor } = await res.json()
 
  appendToUI(items)
  cursor = nextCursor
}
 
// 滚动到底部时加载
window.addEventListener('scroll', () => {
  if (window.innerHeight + window.scrollY >= document.body.offsetHeight) {
    loadMore()
  }
})

5. 选择分页方式

场景	推荐方式
后台管理列表（需要跳页）	Offset 分页
无限滚动 / 加载更多	Cursor 分页
数据量大、深分页	Cursor 分页
需要显示总数	Offset 分页
实时数据流	Cursor 分页

6. 性能优化

6.1 Offset 分页优化

对于深分页，可以用延迟关联（deferred join）优化：

-- 原始查询（慢）
SELECT * FROM conversations ORDER BY created_at LIMIT 10 OFFSET 100000;
 
-- 优化查询（快）
SELECT c.* FROM conversations c
INNER JOIN (
  SELECT id FROM conversations ORDER BY created_at LIMIT 10 OFFSET 100000
) AS tmp ON c.id = tmp.id
ORDER BY c.created_at;

先通过覆盖索引获取 ID，再关联获取完整数据。

6.2 Cursor 分页优化

确保排序字段有索引：

// Drizzle
export const conversations = pgTable('conversations', {
  id: uuid('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  createdAtIdx: index('idx_created_at').on(table.createdAt),
}))

6.3 避免 COUNT(*)

获取总数时，COUNT(*) 在大表上很慢。可以：

缓存总数：定时更新，不实时计算
估算总数：PostgreSQL 的 reltuples 提供估算值
不显示总数：用 hasMore 代替

7. 分页与排序

分页通常需要配合排序：

// 按创建时间倒序
const results = await db
  .select()
  .from(conversations)
  .limit(pageSize)
  .orderBy(desc(conversations.createdAt))

多个排序字段：

const results = await db
  .select()
  .from(conversations)
  .limit(pageSize)
  .orderBy(desc(conversations.createdAt), asc(conversations.title))

Cursor 分页时，排序字段需要唯一，否则可能出现重复或遗漏：

// createdAt 不唯一，需要加 id 作为次要排序
const results = await db
  .select()
  .from(conversations)
  .where(cursor ? gt(conversations.createdAt, cursor.createdAt) : undefined)
  .limit(pageSize)
  .orderBy(desc(conversations.createdAt), asc(conversations.id))

8. 分页 API 最佳实践

限制 pageSize：防止客户端请求过多数据

const pageSize = Math.min(parseInt(c.req.query('pageSize') ?? '10'), 100)

提供合理的默认值：pageSize 默认 10-20
返回分页元数据：total、page、pageSize、hasMore、nextCursor
使用 HATEOAS：返回 next、prev 链接

return c.json({
  items,
  links: {
    next: nextCursor ? `/conversations?cursor=${nextCursor}` : null,
  },
})

总结

分页查询是列表 API 的必备功能。Offset 分页简单但深分页性能差，Cursor 分页性能好但不能跳页。

这一节涉及到的几个实践：

Offset 分页：OFFSET + LIMIT，适合后台管理
Cursor 分页：基于游标，适合无限滚动
关键集分页：Cursor 的优化版本，用排序字段值作为游标
无限滚动：前端配合 Cursor 分页实现
性能优化：延迟关联、索引、避免 COUNT(*)
分页与排序：排序字段需要唯一，配合索引
API 最佳实践：限制 pageSize、返回元数据、HATEOAS

选择分页方式取决于使用场景。需要跳页用 Offset，无限滚动用 Cursor。

下一篇看软删除与审计字段——deletedAt、createdAt/updatedAt、审计日志。