MySQL深分页性能优化：从LIMIT OFFSET到游标分页实战指南-北京尧图网络

接手过一个后台项目客户反馈非常一致列表页翻得越深响应越慢到第 1000 页左右基本已经卡到让人抓狂。查了下监控一条很普通的订单分页查询耗时从最初的 30ms 飙到了 5 秒以上。SQL 长这样SELECT * FROM t_order WHERE shop_id 123 AND status PAID ORDER BY id DESC LIMIT 100000, 20;这就是典型的 MySQL 深分页问题。LIMIT 100000, 20 看起来只是“从第 100000 行开始取 20 行”但 MySQL 实际执行时并不会直接跳过去而是一行一行数过去、丢掉前面十万行再把剩下的 20 行返回给你。服务端扫了一大堆无用数据响应自然越来越慢。本文我会按一条排查主线来写先讲清楚深分页到底慢在哪再说是怎么在生产环境定位到它的接着对比几种常规优化手段最后重点展开一套在电商、SaaS 后台里验证过多次的游标分页方案以及各种边边角角的坑。1. 深分页的根因剖析LIMIT 与 OFFSET 的真实执行路径1.1 偏移量分页的假象不是“跳到第 N 行”而是“先扫再扔”很多开发者对 OFFSET 的理解是错的以为 MySQL 能通过某种偏移计算直接定位到数据文件的第 100000 行然后从那里开始读。实际上MySQL 在处理 LIMIT 100000, 20 时执行路径大致是根据 WHERE 条件从索引或表中找到所有匹配的行如果要排序先按 ORDER BY 字段排序从头开始数数到 100000 行之后再取 20 行返回。这里最关键的是第二步和第三步。MySQL 没有办法“跳过”前面的 100000 行它只能把匹配的行一条一条读出来、判断是否在窗口之外、然后丢弃。扫描的成本是 offset limit也就是至少 100020 行而不是 20 行。所以深分页本质上是用几十倍甚至几万倍的工作量去换取一页数据。用生活里的例子类比你去自助餐厅取餐假设菜台是一排很长的桌子你要拿第 1000 道菜但必须从第一道菜开始顺着走过去边走边看直到走到第 1000 道面前才能夹菜。这不是“定位到第 1000 道”而是“肉身遍历前 999 道”。MySQL 的深分页就是这个“肉身遍历”。1.2 二级索引扫描与回表每一行都要付出的随机 I/O 成本如果表上只有一个唯一主键索引比如id而查询条件是shop_id 123 AND status PAID那么 InnoDB 只能通过主键索引做全表扫描或者扫描某个二级索引后再回到主键索引取完整行。重点在“回表”这个词上。InnoDB 中二级索引的叶子节点存储的是索引列值和主键值。假如你建立了idx_shopid_status (shop_id, status)执行SELECT * FROM t_order WHERE shop_id 123 AND status PAID找到的是符合条件的全部主键 id接着每一条都要拿着主键 id 到聚集索引里再读取完整行数据。这个动作叫回表。回表是随机 I/O。因为即使 shop_id 相同这些订单分散在表空间的不同数据页里并不是连续排列的。深分页时被扫描的 100020 行里有差不多十万行都要做一次回表而最终只需要 20 行。相当于把十万波随机 I/O 的绝大多数成本花在了“确定要不要丢弃”上。更崩溃的是深分页扫描越深被扫描到的目标行越“孤立”。InnoDB 的预读机制通常按顺序把相邻页面加载到缓冲池几十行的浅分页可能正好命中连续页但十万行之后的数据页很难被预判热数据命中率下降I/O 成本进一步放大。1.3 排序、filesort 与深分页的叠加放大如果查询里还有 ORDER BY并且排序字段和索引顺序不匹配MySQL 会走排序缓冲甚至临时文件这个过程叫 filesort。排序本身需要把结果集放入sort_buffer_size指定的内存区域放不下就溢写到磁盘临时文件会产生额外的磁盘读写。深分页遇到 filesort 是最糟糕的组合。例如SELECT * FROM t_order WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20;即使status上有索引只要 order by 的不是索引顺序仍可能先取出所有statusPAID的行把所有需要的列塞进排序缓冲区排序完成后再砍掉前十万行。也就是说排序的是十万行甚至几十万行而返回值只有 20 行。深分页的性能问题不是单因子的而是扫描行数、回表次数、filesort 磁盘写、数据页分散四者叠加的结果。任何一项单独看都不致命但四个因素一起出现时慢是必然的。2. 生产环境下的深分页“症状”与快速定位2.1 哪些业务场景最容易中招不是所有分页都会触发深分页。我梳理一下最容易中招的场景这些都是真实的线上典型管理后台列表订单、会员、对账单、日志动辄几百万行数据默认按创建时间倒序排列翻页没有上限带有筛选条件的列表比如“查询某店铺全部已支付订单”命中行数巨大第几万页之后毫无约束报表导出前的前端预览先加载前 500 页的分页数据用户在页面上拖到很后面移动端瀑布流接口被改造成“页码分页”本应做游标翻页结果前端还是用了page*size来请求定时任务把大数据集分批处理每批LIMIT offset, batch_size并循环执行越往后越慢。这些业务有个共同特点查询条件本身不够收敛或者产品没有限制最大页数让已经膨胀的偏移量无限变大。后台系统的分页最容易出问题因为用户是运营或客服他们真的会翻到第几万行去找一条历史单据。2.2 用 EXPLAIN 和 EXPLAIN ANALYZE 确认深分页排查深分页时第一件事是拿到出问题的 SQL放到测试库执行EXPLAIN重点看四个字段Explain 字段出现什么说明有问题typeALL 或 index说明在做全表或全索引扫描rows数值远大于返回行数比如高到几十万key可能没用到合适的索引Extra出现 Using filesort说明排序没有走索引但EXPLAIN里的 rows 是优化器估算值不一定准确。MySQL 8.0.18 之后可以用EXPLAIN ANALYZE直接看到实际执行过程中扫描了多少行、每条算子耗时多少EXPLAIN ANALYZE SELECT * FROM t_order WHERE shop_id 123 AND status PAID ORDER BY id DESC LIMIT 100000, 20;输出里会显示类似rows_examined: 100020的数据如果这个数字远远大于rows_sent: 20深分页问题基本坐实。我见过很多“看似只查 20 行”的慢 SQL实际扫描了几十万行上线后数据库 CPU 直接被拖垮原因就在这里。2.3 从慢查询日志和性能指标侧确认如果线上表现是间歇性的可以通过慢查询日志采集证据。打开慢日志并设置阈值SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;然后在慢日志里找到对应 SQL观察Rows_examined和Rows_sent的差距。举例来说某条查询Rows_sent: 20但Rows_examined: 120040这条 SQL 的“有效工作率”只有 0.017%。数据库大部分时间都在做无用功。此外还可以通过 performance_schema 的语句历史表确认SELECT THREAD_ID, SQL_TEXT, ROWS_EXAMINED, ROWS_SENT, TIMER_WAIT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE %LIMIT% ORDER BY TIMER_WAIT DESC LIMIT 10;观察到的典型特征是查询时间随页号线性增长CPU 在数据库实例上飙升磁盘 I/O 频繁但 QPS 总量并不高。这种“单个查询吃掉大量资源”的表现就是深分页的特征。3. 常规优化手段盘点能用但有边界3.1 先从产品侧砍掉深分页优化 SQL 之前先确认业务是否真的需要支持第 10000 页之后的数据。大部分场景下用户看前几十页就满足了。产品可以限制最大页码比如最多翻到第 200 页超过后提示“请增加筛选条件或使用导出功能”。这一步做掉之后深分页在后端直接没有机会发生。另一种产品侧调整是强制加时间筛选。比如订单列表没有时间范围时默认只能看近 90 天或者必须选择一个时间区间才能查询。这个约束能大幅减少扫描行数比任何索引优化都有效。对一个合格的后端系统来说“约束查询范围”应当是第一优先级。接口层也可以设计防御手段后台分页接口的page参数超过某个阈值就拒绝执行并对导出操作走异步任务通道不直接同步跑深分页。3.2 覆盖索引把回表砍掉覆盖索引的意思是查询要返回的列全部包含在某个二级索引里这样 InnoDB 就不需要回表了。比如列表只需要显示订单号、创建时间、状态那么建立(status, create_time, id, order_no)索引查询时所有字段都能从索引叶子节点拿到。SELECT order_no, create_time, status FROM t_order WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20;如果走了覆盖索引扫描十万行索引记录依然存在但没有了回表随机 I/O。相比全表扫描性能提升是数量级的。不过覆盖索引有局限后台列表通常要展示几十个字段不可能全部塞进索引里。你把索引建得很宽写入性能就下降而且索引体积膨胀后扫描成本也被抬高。所以覆盖索引适合字段少的场景比如列表的“摘要接口”或“导出任务取数”。3.3 延迟关联只回表真正要返回的 20 行延迟关联的核心思路是先在一个覆盖索引上查出需要的 20 个主键 id再用这 20 个 id 去关联原表查出完整数据。原来的 SQL 是扫描十万行并回表十万次优化后只回表 20 次。SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE shop_id 123 AND status PAID ORDER BY id DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id ORDER BY t.id DESC;内层查询只取主键 id可以在索引上快速扫描外层按照 20 个主键回表。深分页的扫描行数并没有减少内层仍然要扫十万行索引但回表次数从十万降到了 20省下了大部分随机 I/O。需要提醒的是内层这种写法在 MySQL 8.0 里有些版本会做派生表合并效果可能退化成和原 SQL 一样。执行计划里如果看到 Extra 没有Materialized或Derived而还是直接全表访问就需要考虑用optimizer_switch临时关闭派生表合并或者用别的写法保证内层先物化。线上改造这类 SQL 时一定要用EXPLAIN对比优化前后的执行计划不要想当然。3.4 用主键排序替代业务字段排序很多业务排序字段是create_time但创建时间是有可能重复的而且未必有索引。如果表的主键是自增 ID 或者雪花算法生成的单调递增 ID那么ORDER BY create_time的结果通常和ORDER BY id是相同的但用id排序可以天然走主键索引避免 filesort。-- 改造前 SELECT * FROM t_order WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20; -- 改造后 SELECT * FROM t_order WHERE status PAID ORDER BY id DESC LIMIT 100000, 20;这里有一个前提创建时间和主键顺序在同向单调关系上是“业务可接受”的。如果订单允许后补、或者数据由多套系统写入且时钟不一致就不能直接用 id 排序。这个判断必须结合业务确认否则排错序就不是性能问题了是正确性问题。4. 生产级解决方案键集分页游标分页4.1 换一种思路前端不再传页码而是传上一页的最后一条记录前面几种优化其实都治标不治本因为只要还在用LIMIT offset, size扫描行数就会随着 offset 的增长线性上涨。真正生产级的做法是砍掉“偏移量”这个概念改用“锚点”来翻页也就是键集分页也叫游标分页、keyset pagination、seek method。核心思想非常简单上一页返回的最后一条记录已经给出了一个“锚点”。下一页的请求不用再说“我要第 100000 行之后的 20 行”而是说“我要从某个排序键之后开始拿 20 行”。比如按主键倒序分页第一页返回的最大 id 是 100050那么第二页直接请求SELECT * FROM t_order WHERE shop_id 123 AND status PAID AND id 100050 ORDER BY id DESC LIMIT 20;这样 MySQL 可以利用主键索引快速定位到id 100050附近然后顺序向后扫描 20 条记录扫描行数大约是 20 行而不是十万行。无论你翻到多深单页成本几乎是恒定的。这才是深分页场景下值得长期维护的方案。4.2 单字段游标的落地实现先看最简单的单字段游标假设列表排序字段就是主键id且是倒序。前端请求参数除了原来的筛选条件之外多一个cursor字段。后端维护一个游标值为上一页最后一条记录的 id。第一页请求GET /api/order/list?shop_id123statusPAIDpage_size20返回给前端的数据里带上下一次请求游标{ list: [...], next_cursor: 100050 }前端翻下一页时带上这个游标GET /api/order/list?shop_id123statusPAIDcursor100050page_size20后端解析出 cursor 中的数值生成 SQLSELECT * FROM t_order WHERE shop_id 123 AND status PAID AND id 100050 ORDER BY id DESC LIMIT 20;如果返回的list长度小于page_size说明没有更多数据了终止游标携带。这个过程中间不需要维护任何“页码”状态服务端是无状态的游标本身就是状态。4.3 复合排序键的游标写法单字段游标只能应对“按主键排序”的场景。很多列表是按时间倒序排的主键没有业务含义这时需要多个排序键来锁定位置。比如列表按create_time DESC, id DESC排序。上一页最后一条记录的create_time是2025-06-01 12:30:00id是582310。那么下一页的条件就可以用行值比较row value constructor comparisonSELECT * FROM t_order WHERE shop_id 123 AND status PAID AND (create_time, id) (2025-06-01 12:30:00, 582310) ORDER BY create_time DESC, id DESC LIMIT 20;MySQL 会把(create_time, id) (...)翻译成create_time 2025-06-01 12:30:00 OR ( create_time 2025-06-01 12:30:00 AND id 582310 )这种写法恰好能利用(status, create_time, id)这样的复合索引先按等值条件过滤status再在索引上做一个范围定位。不管前面的页有多少只要锚点确定MySQL 都能在索引树里通过范围找到目标位置然后再顺序取 20 条。升序分页同理条件方向反过来SELECT * FROM t_order WHERE shop_id 123 AND status PAID AND (create_time, id) (2025-06-01 12:30:00, 582310) ORDER BY create_time ASC, id ASC LIMIT 20;很多团队对行值比较不熟容易写出两层嵌套子查询结果索引利用不了速度反而慢。直接利用 MySQL 的元组比较能力配合 position 索引是这套方案最高效的写法。4.4 游标的编码、校验与防篡改生产环境用游标分页不能直接把create_time和id拼成字符串传给前端这里有几个工程问题游标信息可能包含多个字段需要结构化编码前端可以随意修改游标后端必须校验游标要和当前查询条件绑定否则用户把 A 列表的游标拿过来查 B 列表结果会错乱。我建议的做法是在后端把游标字段组装成一个字符串做一次签名再 Base64 编码。游标解码失败、签名不匹配、字段类型不合法时直接返回参数错误而不是当成有效游标执行 SQL。一个简单的签名思路把需要参与游标定位的字段 JSON 序列化拼上当前查询条件的哈希值用统一密钥做 HMAC 签名把签名数据一起做 Base64URL 编码传给前端。后端收到游标后先解码、校验签名、比对查询条件哈希再解析出排序字段。这样既保证安全也解决了“改了筛选条件但游标还是旧的”这类脏数据请求。另外注意游标分页天然不支持“跳页”。如果产品必须保留页码跳转能力那只能在“前几页用偏移分页之后改成游标”或者“限制最大页数”之间选一个。不能既想支持无限页码跳转又想扫描行数恒定这两个诉求在单机 MySQL 里是互斥的。4.5 与偏移量分页的对比把两种方式放到一起看优劣势很清楚维度偏移量分页 LIMIT offset, size键集分页 / 游标分页首页查询性能很快很快深页查询性能线性下降基本恒定实现复杂度低中需要维护游标是否支持跳页支持不支持只能上一页/下一页对索引的要求相对宽松要求排序键和 WHERE 条件能命中索引并发写场景稳定性数据变更时可能重复/漏读锚点确定受插入影响更小是否容易被前端篡改页码篡改风险低需要签名校验线上接入游标分页后我观察到的最直观变化是同一个深列表接口原来翻到 500 页时耗时 3 秒现在翻到第几万页也是 20ms 左右。代价是前后端协议多了一个cursor字段以及后端多了编码校验逻辑。5. 常见问题与避坑实录5.1 排序字段重复导致数据重复或漏读这是一个很容易踩的坑。假设列表只用create_time排序但同秒内有多条订单游标携带的锚点也是create_time。因为排序键不唯一同一时间点可能出现几十条数据游标判断create_time 2025-06-01 12:30:00时等于这一秒内的部分记录被丢掉或者下一次查询又重复返回上一秒的数据。解决办法是ORDER BY 永远加上一个唯一键做二级排序例如ORDER BY create_time DESC, id DESC游标也必须携带create_time和id两个字段。之前提过这是游标分页的底线不是建议项。5.2 排序字段为 NULL 时的诡异行为MySQL 对 NULL 的排序默认是升序时 NULL 排最前降序时 NULL 排最后。如果排序字段允许为空锚点判断会出现很多边界 bug。比如用create_time DESC排序某个订单的create_time为 NULL它会排在列表最后但游标条件create_time last_create_time永远匹配不到它。我的建议是数据库设计阶段就把排序字段设为 NOT NULL给定默认值。如果线上已经是 NULL那就先在查询里用COALESCE转成默认值再参与排序但要注意函数包裹字段可能影响索引。最省心的方式还是在应用层写入数据时保证排序字段非空。5.3 优化器吞掉派生表延迟关联失效前面提到延迟关联时如果内层查询被优化器合并到外层整个优化就白做了。实践中不是每条 SQL 都这么听话尤其查询里没有聚合、没有 LIMIT 时MySQL 8.0 会倾向于把派生表合并掉。遇到这种情况我先看执行计划如果发现实际执行的扫描行数没降下去就临时关闭该语句的派生表合并或者改写为临时表形式迫使 MySQL 先物化内层结果。建议上线前用EXPLAIN ANALYZE实测rows_examined不要只看逻辑上“写得对不对”。5.4 批量导出任务还在用 offset 循环有些系统做批量导出时用类似for page : 1; ; page的方式分批拉取全部数据每次都执行LIMIT (page-1)*size, size。数据量一大前面批次还快后面批次越来越慢整个导出任务可能要跑几个小时。正确的做法是改成游标循环每次查询拿到一批数据记录最后一条的排序键下一批用这个键继续查。这样不仅快而且不会因为导出过程中有新增数据导致重复扫描。对导出、同步、清洗这类离线任务游标分页是必然选择。5.5 数据删除和状态变更后的游标稳定性游标分页看起来比偏移分页稳但在有删除或状态变更的场景下也会带来困惑。上一页的最后一条记录在用户点击下一页时如果刚好被删了下一页从这条记录的锚点继续查一般不会丢失数据因为游标只用于定位不需要回查那条记录本身。但状态变更更麻烦。比如列表条件限定status PAID上一页最后一条是已支付订单。用户在这一页把某条订单取消支付了那么这条记录已经不符合查询条件。下一次从锚点往后翻这条被改状态的记录不会出现但它在当前页已经展示给用户过了用户可能感觉“列表里少了一条”。这类问题很难通过数据库层解决需要前端在下拉刷新或重新加载时重建游标。这也是交互上不要“无限下拉但永不刷新”的原因之一。6. 从深分页反推索引设计与监控体系6.1 游标分页对索引的要求其实很讲究索引设计口诀可以总结为三句话等值条件放前面排序字段放中间唯一键兜底放最后。以订单列表为例SELECT * FROM t_order WHERE shop_id 123 AND status PAID AND (create_time, id) (2025-06-01 12:30:00, 582310) ORDER BY create_time DESC, id DESC LIMIT 20;复合索引可以设计为idx_shop_status_time_id (shop_id, status, create_time, id)。shop_id和status在查询中是等值条件放在最前create_time和id用于排序和游标范围放后面。由于shop_id、status已经通过等值条件过滤掉MySQL 真正要处理的只是每个店铺和状态下的一段索引范围效率非常高。如果查询还有AND type 2这种条件就要评估哪个等值列区分度更高把高区分度的列放前面。必要时可以用多套索引分别覆盖不同的筛选组合。索引设计不是一次建一堆而是基于实际 SQL 模式去配配合慢查询日志迭代。6.2 深分页排查需要长期监控的指标生产环境不可能每次都等到用户投诉才去抓慢 SQL。可以做一套针对性的监控慢查询日志里Rows_examined / Rows_sent比值超过 1000 的 SQL 全部接入告警对依赖分页接口的表定期抽查EXPLAIN的预估 rows性能数据采集任务中比对聚合索引 scans per query这类指标记录接口的 P99 耗时和深分页请求占比一旦深页访问量增加立刻预警。很多时候深分页是一个“慢慢恶化”的问题因为数据量在增长同样的 offset 查询扫描行数也在增长。今天第 100 页不慢不代表三个月后还不慢。所以监控的价值不只是发现问题更是发现趋势。6.3 什么时候该放弃深分页 SQL游标分页能解决大部分单表深分页但有些业务天然不适合在 MySQL 里翻页。比如需要跨多张表、多维筛选、全文搜索的复杂列表需要支持任意“跳页”的对外搜索引擎式产品数据量千万级且查询条件组合特别多MySQL 索引数量已经失控。这类场景更适合把查询压力转移到搜索引擎或离线数仓MySQL 只做核心事务存储。不要抱着“所有查询都必须走 MySQL”的执念。深分页优化的目的不是把 MySQL 变成全能查询引擎而是把数据库性能维持在一个可预期的健康区间。最后分享一个我自己的经验很多团队在解决深分页时第一反应是加内存缓存或者上搜索引擎但实际定位后发现问题非常初级——只是缺一个合适的复合索引以及分页协议该从页码改成游标。把这两件事做好超过九成的深分页问题都能在 MySQL 层解决。真遇到跨库、多字段全文搜索这类需求再去引入重组件也不迟。每个系统都该定期翻一翻自己的慢查询特别是那些“翻页越深越慢”的接口它们往往早就在日志里呼喊了很久。

MySQL深分页性能优化：从LIMIT OFFSET到游标分页实战指南

相关推荐

Koa2 项目前端渲染实战：React.js 从 JSX 到 virtual DOM 的编译与集成全流程

技术人跨界农业5年：养殖数字化与物联网实践

数据库表字段命名规范全指南：从原则到落地实践

更多资讯

Android bindService深度解析：从Java到Binder驱动的全流程拆解

科研仪器共享平台管理系统：从数据库设计到计费落地

AI辅助绘制机制图：从文献解析到科研绘图的完整管线

Windows本地部署Docker+Dify+Ollama+DeepSeek全攻略

基于Matlab的六杆快回机构运动学与动力学仿真

微信表情包怎么转到 QQ 上？

今日更新

声子晶体仿真：三角晶格与六角晶格的能带计算实战

度假村安保定岗定编：岗位梳理、编制测算与排班设计

COMSOL等离子体模块实战：氩气双层DBD仿真建模全解析

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？