什么值得买App下载 App ›
连续签到专享现金活动

千万 QPS 架构,第 130 讲:「分页查询:从LIMIT到游标」

在数据库表规模达到千万级、并发量达到百万甚至千万QPS时,一条看似简单的分页查询语句可能成为压垮系统的导火索。典型的场景是:当用户在后台管理系统中翻阅订单列表,查询语句使用了 LIMIT offset, size 的语法,随着翻页深度增加,数据库响应时间呈线性增长,甚至导致CPU飙高、主从延迟剧增。这并非单纯的慢查询问题,而是在高并发规模下被无限放大的系统性瓶颈。解决这一问题不能仅依赖SQL调优,而需要根据业务场景对分页模型进行架构层面的重构。
01深翻页陷阱:LIMIT offset 的性能瓶颈
传统分页通常采用 LIMIT offset, size 的语法,例如 LIMIT 160000, 20。MySQL在执行此类查询时,执行模型非常朴素:扫描索引(或全表),读取并丢弃前 offset 条记录,直到 offset 数量满足后,才开始读取并返回 size 条数据。这意味着,offset 越大,数据库需要扫描并丢弃的行数就越多,但实际返回的数据量始终保持不变。
这种“线性扫描换取常数输出”的模式在小数据集下成本可忽略,但在高并发深翻页场景下极其致命。基于千万级行表的实测数据显示,随着 offset 的增加,扫描行数(rows_examined)成倍放大,查询耗时显著增长。在十万QPS的系统中,即使只有1%的请求发生深翻页,大量累积的扫描操作也足以瞬间打满数据库资源。
翻页深度SQL 模板rows_examined实际耗时扫描放大比
第 1 页LIMIT 0, 20202 ms1x
第 100 页LIMIT 2000, 20202018 ms100x
第 1000 页LIMIT 20000, 2020020160 ms1000x
第 10000 页LIMIT 200000, 202000201.6 s10000x
第 50000 页LIMIT 1000000, 2010000208.2 s50000x

不同翻页深度下的性能损耗对比

02优化演进:从覆盖索引到游标分页
针对深翻页导致的性能衰退,技术演进路径通常从减少单次查询成本开始,最终过渡到改变分页模型。第一阶段采用“覆盖索引 + 延迟关联”策略。该策略的核心在于避免对大量不需要的数据进行“回表”操作。通过子查询先在二级索引中定位到所需的主键ID,再根据ID回表查询完整数据,从而将昂贵的回表操作从16万次降低到20次,显著减少IO开销。
  1. 覆盖索引优化
    在索引中包含查询所需的所有字段,避免回表。
  2. 延迟关联
    通过JOIN子查询延迟回表时机,先利用二级索引快速过滤出主键ID。
  3. 适用范围
    有效降低 offset 在 1 万以内时的查询耗时,但当 offset 达到十万级以上时,索引扫描本身的线性增长依然是瓶颈。
要彻底打破 offset 带来的线性增长,必须采用游标分页(Keyset Pagination)。其核心思想是放弃“跳过多少条”的语义,改为告诉数据库“从哪个点开始”。SQL查询条件不再依赖 offset,而是基于上一页最后一条记录的排序字段值(如 created_at 和 id)进行定位。这种方式将查询复杂度从 O(offset) 降低为 O(1),无论翻到第几页,扫描成本恒定。

两种分页模型对比

传统 LIMIT 分页

基于偏移量的传统分页方式

  • 支持任意页码跳转,体验符合直觉
  • 性能随 offset 增加线性劣化
  • 深翻页会导致大量无效扫描和回表
游标分页

基于有序键的连续分页方式

  • 查询性能恒定,与翻页深度无关
  • 要求排序字段具有唯一索引支撑
  • 不支持随机跳页,仅支持上一页/下一页
03特殊场景处理:总数统计与跳页需求
游标分页虽然解决了性能问题,但也引入了新的约束,其中最突出的是无法直接获取“总记录数”以及无法“跳页”。在千万QPS架构下,对于总数统计的需求通常采用降级策略:小规模时使用实时 COUNT,中大规模时采用估算(如利用 MySQL information_schema 或维护独立计数器),超大规模时则直接放弃精确总数,仅通过多查一条数据(LIMIT 21)来判断是否存在“下一页”。
针对必须保留的“跳页”需求(如运营后台、财务对账),工程上通常采取折中方案:限制最大翻页深度并强制用户增加筛选条件;利用时间窗口分桶,将“翻页”转化为“按时间范围查询”;对于极深度的查询,转为异步任务处理,利用离线库或从库执行;或将前台C端(MySQL游标)与后台B端(OLAP引擎)分流,利用 ClickHouse 等引擎处理复杂的大偏移量排序。
分页优化的本质不是单纯加速 SQL,而是重新审视业务需求。通过剥离非必要的功能(如精确总数、任意跳页)并按场景分流(C端游标、B端OLAP、导出异步),系统可以在千万级数据规模下依然保持高性能。每一次架构演进,本质上都是通过增加约束条件,换取了系统扩展性的提升。

评论

评论区空态插画

当前文章无评论,是时候发表评论了