MySQL常用的分页方案

目录

当数据量达到千万级别时，传统的 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 ✅（但推荐游标） 快 ⭐⭐⭐⭐ ⭐⭐⭐⭐
子查询游标 ❌ 恒定快 ⭐⭐ ⭐⭐⭐⭐
归档/分表 ✅ 根本解决 ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐⭐（终极方案）

一句话建议：

  • 优先采用游标分页（WHERE id > lastId），能满足90%以上的业务场景。
  • 如果非要随机跳页且深度不大（<500页），用延迟关联。
  • 如果业务必须深度随机跳页且数据量巨大，考虑迁移到 ES 或重新设计产品交互（实际上用户很少翻到100页以后）。

面试加分点：提到“用户实际上不会翻到1000页，与其优化技术不如优化产品交互，比如只保留前100页，之后只允许按条件筛选”。

posted @ 2026-04-14 15:30  LARRY1024  阅读(56)  评论(0)    收藏  举报