怎样在数据库中实现分页查询？

从原理到高性能实现

目录导读

1. 分页查询的核心概念 – 为什么需要分页？常见分页模型解析
2. 主流数据库的分页实现对比 – MySQL、PostgreSQL、SQL Server、Oracle 差异详解
3. 高性能分页的7个优化策略 – 避免OFFSET陷阱、索引优化、延迟关联
4. 实战问答 – 高频面试题与线上故障排查方法

分页查询的核心概念

为什么需要分页？

当数据库中存储数百万条记录时,一次性返回所有数据会导致：① 网络传输压力剧增 ② 应用服务器内存溢出 ③ 用户等待时间过长，分页查询通过“分段加载”机制，每次仅返回指定数量的记录（如每页20条），这是Web系统、API接口与数据分析工具的基础能力。

两种主流分页模型

• 物理分页：数据库层面通过LIMIT/OFFSET或ROW_NUMBER()实现，仅查询当前页数据，性能最优。
• 逻辑分页：将全量数据加载到内存后，由应用代码截取分页，仅适用于数据量小于 10 万条且用户交互频繁的场景（如Excel导出）。

主流数据库的分页实现对比

MySQL分页语法

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;  -- 第3页（每页20条）

• 核心机制：OFFSET会跳过前N条物理记录，即使这些记录不需要返回，数据库仍需扫描它们。
• 性能陷阱：当OFFSET值大到如100000时，MySQL需扫描100020条记录后丢弃前10万条，导致查询速度随页码增加而急剧下降。

PostgreSQL分页方式

SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;
-- 或使用游标分页
SELECT * FROM users WHERE id > last_id ORDER BY id LIMIT 20;

• 优势：PostgreSQL的排序算法优化较好，但同样存在OFFSET深分页问题。
• 推荐方案：基于排序字段（如自增ID、创建时间）的“游标分页”（Keyset Pagination），在高并发场景下性能稳定。

SQL Server分页

SELECT * FROM users ORDER BY id 
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;  -- SQL Server 2012+

• 注意：必须先有ORDER BY，否则分页查询无意义。
• 传统方案：使用ROW_NUMBER() OVER (ORDER BY id) 实现，适用于旧版本。

Oracle分页（12c前）

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM users ORDER BY id
  ) a WHERE ROWNUM <= 60
) WHERE rn > 40;

• 缺陷：需要三层嵌套，且ROWNUM在排序前执行，复杂过滤时容易出错。
• 12c+新语法：支持OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY。

高性能分页的7个优化策略

策略1：避免OFFSET深分页

当用户访问第1000页时,数据量假设20000条，传统写法性能极差，改用游标分页：

SELECT * FROM users WHERE id > 2000  -- 上一页最后一条记录的ID
ORDER BY id LIMIT 20;

• 应用场景：社交动态、商品列表，无中间跳页需求。
• 注意：需配合唯一排序字段（如ID、UUID），且用户不能随意跳转到特定页码。

策略2：强制使用覆盖索引

-- 坏写法
SELECT * FROM users ORDER BY create_time LIMIT 20 OFFSET 1000;
-- 好写法：索引覆盖
SELECT id, name, email FROM users ORDER BY create_time LIMIT 20 OFFSET 1000;

• 原理：使用索引包含所有查询字段，避免回表查询（Secondary Index Lookup），如果必须查询大字段（如text），考虑先查ID再关联主表。

策略3：延迟关联技术

-- 低效：外层查询需回表多次
SELECT * FROM users u JOIN orders o ON u.id = o.user_id 
ORDER BY u.id LIMIT 20 OFFSET 1000;
-- 高效：先查ID再关联
SELECT u.*, o.order_name FROM users u 
JOIN (SELECT id FROM users ORDER BY id LIMIT 20 OFFSET 1000) tmp ON u.id = tmp.id
LEFT JOIN orders o ON u.id = o.user_id;

• 适用场景：多表关联分页，可大幅减少回表次数。

策略4：利用子查询定位起始点

SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 1000)
ORDER BY id LIMIT 20;

• 优点：两次查询，但第一次仅返回一个ID，第二次主查询范围极窄。
• 注意：仅限于排序字段唯一且递增。

策略5：禁止在分页中使用COUNT(*)

很多业务需要在分页接口同时返回总记录数,但百万级表的COUNT(*)极其耗时，应：

• 使用近似值（如MySQL的SHOW TABLE STATUS中的rows字段）。
• 只在前端加载时调用一次,后续分页时缓存总记录数。
• 改用“是否有下一页”的布尔判断，而非精确总数。

策略6：缓存分页结果

• 对静态数据（如文章列表）使用Redis缓存分页结果，设置过期时间。
• 对动态数据,仅缓存“第一页”以提升首屏加载速度。

策略7：结合数据库分区表

• 按时间（如月份、年份）对表进行范围分区，分页查询时仅扫描相关分区，而非全表。

实战问答

Q1：为什么我的MySQL分页查询第100页很慢，但第1页很快？

A：这是OFFSET的典型问题，第100页需要扫描并跳过 (100-1)*20=1980条记录，而第1页只需扫描20条，深度分页建议改用游标分页或子查询定位。

Q2：订单表有500万条数据，用户需要按时间排序并分页，如何优化？

A：第一步，在order_time列上建立联合索引（order_time, id）；第二步，使用游标分页，前端传入最后一条记录的order_time和id；第三步，如果用户必须跳页，考虑将总页数固定在20页内，超出后提示使用搜索。

Q3：分页查询时，COUNT(*)和分页SQL可以放在一个事务中吗？

A：可以，但要注意两点：① 如果表数据频繁插入/删除，可能导致COUNT结果与分页数据不一致；② 建议对COUNT查询使用SHOW TABLE STATUS或缓存，而非每次都执行。

Q4：使用游标分页后，如何实现“上一页”和“下一页”？

A：每次分页请求时，返回当前页最后一条记录的排序字段值（如ID），请求“下一页”时传入该值，请求“上一页”时使用反向排序+限制条件，代码示例：

-- 下一页：WHERE id > last_id ORDER BY id ASC LIMIT 20
-- 上一页：WHERE id < first_id ORDER BY id DESC LIMIT 20（再对结果反转）

Q5：Oracle 11g中，为什么我的分页查询结果不稳定？

A：常见原因是ORDER BY字段不唯一（如按姓名排序时，多行同名数据顺序随机），解决方案：在ORDER BY中添加唯一字段，如ORDER BY name, id。

---

优化分页的核心原则：减少数据库扫描的记录数，让每次查询仅访问“当前页需要的数据”，而非全表扫描后进行截取，实际开发中，建议根据数据量级、用户访问模式选择最适合的策略。没有万能的分页方案，但遵循“索引优先、避免OFFSET、善用游标”的准则，可以在多数场景中保持高效。