当前位置：首页 > news >正文

MySQL常用的分页方案

news 2026/4/14 15:46:23

方案一：游标分页
方案二：延迟关联（针对超大 offset 的优化）
方案三：Elasticsearch 等搜索引擎（专业方案）
方案四：子查询优化（特定场景）
方案五：数据归档 + 分表（架构层解决）
总结与选型建议

当数据量达到千万级别时，传统的 LIMIT offset, size 分页（尤其是翻到后面几百页）会变得极慢甚至不可用，因为数据库需要从头扫描 offset + size 行数据。

针对千万级数据，常用的高效分页方案有以下几种，按推荐程度排序：

方案一：游标分页

核心原理：记住上一页最后一条数据的排序字段值（如自增ID、创建时间），下一页直接用 WHERE id > lastId。

SQL示例：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 20;-- 第二页（前端传回上一页最后一条的id=101）
SELECT * FROM orders WHERE id > 101 ORDER BY id LIMIT 20;-- 第三页（传回上一页最后一条id=121）
SELECT * FROM orders WHERE id > 121 ORDER BY id LIMIT 20;

优点：

速度极快，无论翻到多少页，性能恒定（走主键索引）
完全不扫描无用数据

缺点：

只能顺序翻页（上一页、下一页），不支持随机跳页（直接跳第10000页）
要求排序字段是唯一且递增的（如自增主键、时间戳）

适用场景：App/Web 的“加载更多”、后台管理系统的顺序翻页（99%场景够用）

方案二：延迟关联（针对超大 offset 的优化）

核心原理：先通过覆盖索引查出主键（只查ID，不走回表），再用主键关联查全部字段。

SQL示例：

-- 慢：需要扫描 1000020 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;-- 快：先快速查出20个ID，再关联
SELECT * FROM orders t1
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t2 ON t1.id = t2.id;

优点：

比直接 LIMIT 快几倍到几十倍
仍然支持随机跳页

缺点：

翻到很后面（如第100万页）时依然会扫描大量索引行，性能还是会下降
无法解决根本问题（offset 越大越慢）

适用场景：只能随机跳页、且最多翻几百页的系统（如电商后台翻到第50页）

方案一和方案二的对比：

对比维度	方案一：游标分页（ID > lastId）	方案二：延迟关联（JOIN子查询）
核心思路	记住上一页最后一条的ID，下一页从该ID之后开始查	先通过覆盖索引查出主键（只查ID），再关联查全部字段
SQL示例	`WHERE id > 100 ORDER BY id LIMIT 20`	`SELECT * FROM t1 JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000,20) t2 ON t1.id=t2.id`
是否支持随机跳页	❌ 不支持（只能上一页/下一页）	✅ 支持（可指定任意offset）
深度翻页性能	⚡ 恒定极快（永远只扫描20行+索引定位）	📉 越深越慢（仍需扫描offset行索引）
是否依赖唯一递增字段	✅ 必须（如自增ID、时间戳）	❌ 不需要
实现复杂度	简单（前端需记住lastId）	中等（SQL稍复杂）
用户体验	类似“加载更多”，无限滚动	传统页码跳转（1,2,3…100…）

实战选型建议：

业务场景	推荐方案
App/Web 下拉加载更多（朋友圈、评论区、订单列表）	方案一 ⭐⭐⭐⭐⭐
后台管理系统只能前后翻页（没有页码跳转）	方案一 ⭐⭐⭐⭐⭐
后台管理系统需要跳转到第N页（且N不大，如<500页）	方案二 ⭐⭐⭐⭐
后台管理系统需要跳转到第10000页（产品设计不合理）	两个都不行，建议改需求或用ES

方案三：Elasticsearch 等搜索引擎（专业方案）

核心原理：将数据同步到 ES，利用 ES 的分片 + 倒排索引 + search_after 实现深度分页。

优点：

支持复杂的全文检索、聚合、排序
深度分页性能远优于 MySQL
支持随机跳页（但深度分页仍然建议用游标）

缺点：

引入额外组件，增加系统复杂度
数据同步有延迟（可监听 binlog 解决）

适用场景：C端用户搜索、日志检索、报表系统（数据量极大且查询条件复杂）

方案四：子查询优化（特定场景）

核心原理：利用 WHERE (id, create_time) > (lastId, lastTime) 实现多字段游标。

SQL示例：

-- 假设排序字段是 create_time 和 id（防止时间重复）
SELECT * FROM orders
WHERE (create_time, id) > ('2024-01-01 10:00:00', 1001)
ORDER BY create_time, id
LIMIT 20;

优点：

解决了时间字段可能重复的问题
性能同样恒定

缺点：

语法稍复杂，需要传递两个值
依然只能顺序翻页

方案五：数据归档 + 分表（架构层解决）

核心原理：不要让单表达到千万级。

冷热分离：将历史数据迁移到归档表或历史库
水平分表：按 ID 哈希或时间范围分表（如每张表 500 万）

优点：

从根本上解决了性能问题
分页逻辑可能简化（每张表数据量小）

缺点：

需要改造业务代码
分表后全局分页变得复杂（需要聚合多表结果）

适用场景：数据量持续高速增长，长期超过千万的黄金表（如订单表、流水表）

总结与选型建议

方案	是否支持随机跳页	深度翻页性能	实现复杂度	推荐指数
游标分页（ID > lastId）	❌	恒定极快	⭐	⭐⭐⭐⭐⭐
延迟关联	✅	仍会下降	⭐⭐	⭐⭐⭐
Elasticsearch	✅（但推荐游标）	快	⭐⭐⭐⭐	⭐⭐⭐⭐
子查询游标	❌	恒定快	⭐⭐	⭐⭐⭐⭐
归档/分表	✅	根本解决	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐（终极方案）