在处理大数据表时，分页查询是非常常见的需求。分页不仅可以提高用户体验，还能有效减少数据库查询返回的数据量，避免一次性加载大量记录引起的性能瓶颈。
然而，在数据量较大或复杂查询中，简单的分页方式可能导致性能下降。本文将探讨 SQL 中实现分页查询的不同方式，并对比它们的优缺点，帮助优化大数据量场景下的分页方案。
一、分页查询的常见方式
1. 基本分页方式：LIMIT OFFSET
- 语法：
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 20; -- 跳过前 20 条，返回第 21~30 条
- 说明：
LIMIT指定返回的行数，OFFSET指定跳过的行数。
2. 使用 ROW_NUMBER() 进行分页
- 适用数据库：MySQL 8.0 及以上、SQL Server、PostgreSQL
- 语法：
WITH ordered_orders AS ( SELECT order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn FROM orders
)
SELECT *
FROM ordered_orders
WHERE rn BETWEEN 21 AND 30;
- 说明：
ROW_NUMBER()为每一行分配唯一编号，可通过WHERE进行分页筛选。- 分页不受数据量影响，性能稳定。
3. 游标分页（基于主键或唯一索引）
- 适用场景：持续查询下一页数据，适合前端“加载更多”方式。
- 语法：
SELECT * FROM orders
WHERE order_date < '2024-01-01 00:00:00'
ORDER BY order_date DESC
LIMIT 10;
- 说明：
- 基于主键或时间字段进行分页，避免大偏移量问题，性能更优。
- 适合按时间或 ID 递增的分页加载，避免全表扫描。
4. 使用 SQL_CALC_FOUND_ROWS 计算总数
- 语法：
SELECT SQL_CALC_FOUND_ROWS * FROM orders
LIMIT 10 OFFSET 20;
SELECT FOUND_ROWS();
- 说明：
- 查询结果同时计算总行数，减少一次额外的
COUNT(*)查询。 - 在 MySQL 8.0 以后不推荐，效率较低。
- 查询结果同时计算总行数，减少一次额外的
二、实战案例：大数据分页方案设计与优化
案例 1：简单分页（LIMIT OFFSET）
需求描述：
查询订单表中按下单日期排序的第 1001-1020 条订单记录。
SQL 实现：
SELECT order_id, customer_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 1000;
优点：
- 简单直接，适合小数据量分页。
缺点：
- 当
OFFSET较大时（如 100000），性能下降显著。 - 数据量越大，查询速度越慢，因为数据库仍需扫描前
OFFSET行。
案例 2：使用 ROW_NUMBER() 实现分页
需求描述：
查询订单表中，按订单金额降序排列的第 51-60 条记录。
SQL 实现：
WITH cte_orders AS ( SELECT order_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn FROM orders
)
SELECT * FROM cte_orders
WHERE rn BETWEEN 51 AND 60;
优点：
- 即使数据量大，
ROW_NUMBER()也能有效避免大偏移量问题。 - 使用 CTE（公用表表达式），代码更清晰。
缺点：
- 需要 SQL 8.0 以上版本或其他支持窗口函数的数据库。
案例 3：游标方式分页（基于索引分页）
需求描述：
查询比上次加载时间更早的订单记录，每次加载 20 条记录。
SQL 实现：
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date < '2024-05-01 00:00:00'
ORDER BY order_date DESC
LIMIT 20;
优点：
- 不依赖
OFFSET，即使数据量大也能快速查询。 - 可动态加载下一页，避免重复数据查询。
缺点：
- 需要前端记录上一页最后一条数据的时间或 ID。
案例 4：SQL_CALC_FOUND_ROWS 计算总记录数
需求描述：
查询订单表第 21-40 条记录，同时返回总记录数，用于前端分页展示。
SQL 实现：
SELECT SQL_CALC_FOUND_ROWS order_id, customer_id
FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 20; SELECT FOUND_ROWS();
优点：
- 无需额外执行
COUNT(*)查询，减少一次数据库交互。
缺点：
- MySQL 8.0 开始弃用，
SQL_CALC_FOUND_ROWS效率较低。
三、性能对比与优化策略
1. 性能对比
| 方式 | 性能表现（小数据） | 性能表现（大数据） | 复杂性 | 推荐场景 |
|---|---|---|---|---|
LIMIT OFFSET | 高 | 低 | 简单 | 小数据量、简单分页 |
ROW_NUMBER() | 高 | 高 | 一般 | 大数据量分页、多层次筛选 |
| 游标分页 | 高 | 高 | 较复杂 | 无限加载、动态分页 |
SQL_CALC_FOUND_ROWS | 中 | 低 | 简单 | 需要总行数时（不推荐大数据量） |
2. 大数据分页优化策略
- 避免深度分页：
- 限制最大
OFFSET，提供“跳页”或“前端加载更多”方式。 - 使用游标或基于索引的分页方式逐步加载数据。
- 索引优化：
- 在分页查询涉及的字段上建立索引，提高数据检索效率。
CREATE INDEX idx_order_date ON orders(order_date);
- 改用
ROW_NUMBER()：
- 对于复杂多条件查询，使用窗口函数（如
ROW_NUMBER()）替代LIMIT OFFSET，能有效避免性能下降问题。
- 提前过滤数据：
- 分页前尽可能过滤不必要的数据，减少扫描行数。
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY order_date DESC
LIMIT 20 OFFSET 2000;
四、总结
- 小数据量分页：使用
LIMIT OFFSET简单高效。 - 大数据量分页：优先选择游标或基于索引的分页方式，减少
OFFSET扫描行数。 - 复杂分页：使用
ROW_NUMBER()结合 CTE 实现多层次分页，性能稳定，推荐大数据环境下使用。 - 动态加载：基于唯一索引或时间字段的游标分页方式，在实际业务中更具实用性，避免性能瓶颈。
