【后端开发】一次把 MySQL 深分页讲透：从 limit 1000000,10 到游标分页的工程化改造

文章目录前言一、复现深分页问题1.1 准备测试表1.2 准备测试数据1.3 先看普通分页查询1.4 用 EXPLAIN 看一下执行计划1.5 LIMIT 1000000, 20 到底慢在哪里1.6 为什么 MySQL 不能直接跳到第 100 万条二、四种常见解决方案2.1 方案一主键游标分页2.2 方案二created_at id 复合游标分页2.3 方案三延迟关联2.4 方案四覆盖索引2.5 几种方案对比三、业务层面怎么解决深分页3.1 限制最大页数3.2 引导用户使用筛选条件3.3 用“加载更多”替代“跳到第 N 页”3.4 冷热数据拆分和历史数据归档写在文后 个人主页铁皮哥欢迎关注 作者简介28届校招生后端开发/Agent 方向在学 学习内容Java、Python、计算机视觉、大语言模型、Agent开发 专栏内容从零开始的Claude Code零代码生活持续更新中✨不只背八股更想搞懂为什么这样设计前言假设我们有一张订单表里面已经有 1000 万条数据。后台管理系统需要支持分页查询订单列表最开始的 SQL 可能会写成这样SELECT*FROMordersORDERBYidLIMIT1000000,20;从语义上看这条 SQL 很简单按照id排序从第 1000000 条之后开始取 20 条数据。但问题也出在这里。很多人第一次看到这条 SQL 时容易下意识觉得既然最后只返回 20 条数据那查询成本应该也只和这 20 条数据有关。可实际上MySQL 并不是直接“跳到”第 1000000 条记录然后取后面的 20 条。它需要先按照排序规则找到前面的 1000020 条记录再丢掉前面的 1000000 条最后才把剩下的 20 条返回。这条 SQL 真正昂贵的地方不是返回了多少数据而是为了返回这 20 条数据数据库需要先扫描并丢弃大量无用数据。如果查询还是SELECT *情况会更糟。因为 MySQL 可能不仅要扫描索引还要根据主键回表读取完整行数据。随着 offset 越来越大这部分无效扫描和回表成本也会越来越高。这就是深分页问题最核心的矛盾用户只想看 20 条数据但数据库可能已经为此处理了上百万条记录。一、复现深分页问题这里我用一张订单表来模拟真实业务里的列表查询场景。1.1 准备测试表先创建一张orders表。为了更接近实际业务这张表不会只保留一个id字段而是加上用户、订单状态、金额、创建时间、更新时间等字段。CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单ID,user_idBIGINTNOTNULLCOMMENT用户ID,statusTINYINTNOTNULLCOMMENT订单状态,amountDECIMAL(10,2)NOTNULLCOMMENT订单金额,created_atDATETIMENOTNULLCOMMENT创建时间,updated_atDATETIMENOTNULLCOMMENT更新时间,INDEXidx_created_at(created_at),INDEXidx_user_created(user_id,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT订单表;这张表里有几个比较关键的点。id是自增主键用来模拟最常见的主键分页场景。created_at表示订单创建时间很多后台列表都会按照时间倒序查询所以这里也单独给它建了一个索引。user_id created_at是一个组合索引用来模拟“查询某个用户的订单列表”这种业务场景。1.2 准备测试数据接下来需要往表里插入一批测试数据。数据大致满足下面几个特点user_id随机分布模拟不同用户下单 status在几个订单状态之间随机 amount随机金额 created_at分布在最近一年 updated_at在 created_at 之后随机生成可以用存储过程、Java 脚本、Go 脚本或者 Python 脚本批量插入。这里不展开完整造数脚本只给一个大概思路。伪代码类似这样INSERTINTOorders(user_id,status,amount,created_at,updated_at)VALUES(10001,1,99.90,2026-01-01 10:00:00,2026-01-01 10:05:00),(10002,2,199.00,2026-01-01 10:01:00,2026-01-01 10:06:00),...;实际插入时不要一条一条插否则会非常慢。可以采用批量插入比如每批插入 5000 条或 10000 条。如果用 Java 或 Python 脚本造数核心逻辑大概是循环生成订单数据 每 5000 条拼接成一批 INSERT 提交一次事务 重复执行直到插入 1000 万条这里有一个小细节造数的时候最好让created_at有一定重复概率。比如同一秒内生成多条订单。这样后面讲created_at id复合游标分页时才更容易解释为什么不能只用created_at做 cursor。1.3 先看普通分页查询数据准备好之后我们先执行几条普通分页 SQL。第一条是浅分页SELECT*FROMordersORDERBYidLIMIT20,20;第二条是中等 offsetSELECT*FROMordersORDERBYidLIMIT100000,20;第三条是深分页SELECT*FROMordersORDERBYidLIMIT1000000,20;从 SQL 语义上看这三条语句都只返回 20 条数据。但它们的区别在于 offset 不同。SQLoffsetsize含义LIMIT 20, 202020跳过 20 条返回 20 条LIMIT 100000, 2010000020跳过 10 万条返回 20 条LIMIT 1000000, 20100000020跳过 100 万条返回 20 条这时候可以打开profiling或者直接用程序统计执行耗时。比如测试结果可能类似这样查询语句offset返回条数耗时LIMIT 20, 2020205 msLIMIT 100000, 201000002080 msLIMIT 1000000, 20100000020700 ms这里的数字不一定和你的机器完全一致因为它会受到很多因素影响比如机器配置、MySQL 版本、Buffer Pool 大小、数据是否在内存中、磁盘性能等等。但趋势通常是比较明显的offset 越大查询越慢。也就是说哪怕最终都只返回 20 条数据MySQL 的查询成本也不是固定的。1.4 用EXPLAIN看一下执行计划接下来可以用EXPLAIN看一下 MySQL 对这条 SQL 的执行计划。EXPLAINSELECT*FROMordersORDERBYidLIMIT1000000,20;因为这里按照主键id排序MySQL 通常会走主键索引。你可能会看到类似这样的结果type: index key: PRIMARY rows: 1000020 Extra:重点看两个地方。第一个是key。如果key是PRIMARY说明 MySQL 使用了主键索引。第二个是rows。它表示 MySQL 预估需要扫描的行数。对于LIMIT1000000,20来说MySQL 并不是只扫描 20 行而是要扫描大约1000000 20行。这就能解释为什么深分页会慢。很多人会有一个误区既然这条 SQL 走了主键索引那应该很快。但实际上走索引不代表一定快。如果 offset 很大即使走的是主键索引MySQL 仍然需要沿着索引从前往后扫描很多条记录。索引可以让扫描更有序但它不能让 MySQL 直接无成本地跳过前面的 100 万条数据。1.5LIMIT 1000000, 20到底慢在哪里现在我们可以回到最核心的问题SELECT*FROMordersORDERBYidLIMIT1000000,20;这条 SQL 的执行过程可以粗略理解成这样1. 按照 id 顺序扫描数据 2. 找到前 1000020 条记录 3. 丢弃前 1000000 条 4. 返回最后 20 条用图表示就是[1] [2] [3] ... [999998] [999999] [1000000] [目标20条] └──────────────── 被扫描但最终丢弃的数据 ────────────────┘所以它慢的关键不是最后返回了 20 条而是前面那 100 万条也被处理过了。如果查询语句是SELECT*FROMordersORDERBYidLIMIT1000000,20;由于查询的是*MySQL 需要返回整行数据。如果排序字段和查询字段不能完全通过索引覆盖就可能发生大量回表。这也是为什么深分页里经常会提到两个关键词大量扫描 大量丢弃如果是SELECT *还要额外加上大量回表1.6 为什么 MySQL 不能直接跳到第 100 万条这里可能会有一个疑问既然有索引为什么 MySQL 不能直接跳到第 100 万条原因是B 树索引擅长的是按条件定位比如WHEREid1000000这种查询可以直接利用索引定位到id 1000000附近然后继续往后扫描。但是LIMIT1000000,20里面的1000000不是某个具体的id值而是“跳过 1000000 行”。这两者是不一样的。id 1000000的意思是从 id 大于 1000000 的位置开始查而LIMIT 1000000, 20的意思是按照排序结果跳过前 1000000 行前者有明确的索引定位条件后者没有。所以 MySQL 只能按照排序结果往后数数够 1000000 行之后再返回后面的 20 行。二、四种常见解决方案上一章我们已经把深分页慢的原因拆开了。简单来说LIMIT 1000000, 20慢不是因为它返回了 20 条数据而是因为 MySQL 为了拿到这 20 条需要先扫描并丢弃前面的 100 万条。所以优化深分页的核心思路也很明确不要让数据库做大量无意义的 offset 扫描。下面来看几种常见解决方案。2.1 方案一主键游标分页如果列表是按照主键递增顺序展示的那么最直接的优化方式就是把 offset 分页改成游标分页。原来的分页 SQL 是这样SELECT*FROMordersORDERBYidLIMIT1000000,20;这种写法的问题在于1000000表示要跳过前 100 万条记录。MySQL 没办法直接跳过这些记录它还是要按照id顺序往后扫描扫描到第 1000020 条之后再丢掉前面的 100 万条。如果改成游标分页SQL 可以写成这样SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT20;这里的1000000就不再是 offset而是上一页最后一条数据的id。也就是说第一页查询SELECT*FROMordersORDERBYidLIMIT20;假设第一页最后一条数据的id是100020那么下一页就可以这样查SELECT*FROMordersWHEREid100020ORDERBYidLIMIT20;这样 MySQL 就可以直接利用主键索引定位到id 100020的位置然后继续往后扫描 20 条数据。相比LIMIT 1000000, 20这种方式不需要从头扫描并丢弃大量数据。对应到接口设计上也可以从传统的页码分页GET /orders?page50000page_size20改成游标分页GET /orders?last_id100020page_size20接口返回时带上下一个游标{items:[{id:100021,user_id:9527,status:1,amount:99.90}],next_cursor:100040}下次请求时前端把next_cursor传回来后端继续从这个位置往后查。这个方案的优点非常明显查询成本和页码深度关系不大只和本次要取多少条数据有关。它很适合下面这些场景订单流水 消息列表 评论列表 操作日志 Feed 流 任务执行记录不过它也有一个缺点不适合跳页。比如用户想直接跳到第 50000 页游标分页就不太好支持。因为它的设计思路不是“第几页”而是“从上一次看到的位置继续往后查”。所以主键游标分页更适合“上一页 / 下一页 / 加载更多”这种交互而不适合强依赖页码跳转的后台表格。2.2 方案二created_at id复合游标分页实际业务里很多列表并不是按照id排序而是按照创建时间倒序排序。比如订单列表通常会把最新订单放在最前面SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,20;这种写法同样会遇到深分页问题。如果直接改成时间游标可以写成SELECT*FROMordersWHEREcreated_at2026-05-01 12:00:00ORDERBYcreated_atDESCLIMIT20;它的含义是查询创建时间早于某个时间点的 20 条订单。看起来没问题但这里有一个很容易被忽略的细节created_at不是唯一的。在高并发系统里同一秒甚至同一毫秒内都可能产生多条订单。如果只用created_at作为游标可能会出现数据重复或者数据丢失。举个例子假设第一页最后一条数据是id 100 created_at 2026-05-01 12:00:00但数据库里还有几条数据的created_at也是这个时间id 99 created_at 2026-05-01 12:00:00 id 98 created_at 2026-05-01 12:00:00 id 97 created_at 2026-05-01 12:00:00如果下一页查询条件写成WHEREcreated_at2026-05-01 12:00:00那么这些同一时间的数据就会被跳过。所以更稳的做法是使用created_at id作为复合游标。排序也要保持一致ORDERBYcreated_atDESC,idDESC下一页查询可以这样写SELECT*FROMordersWHEREcreated_at2026-05-01 12:00:00OR(created_at2026-05-01 12:00:00ANDid100)ORDERBYcreated_atDESC,idDESCLIMIT20;这条 SQL 的含义是如果创建时间更早直接进入下一页如果创建时间相同就继续比较id只取id更小的数据。这样可以保证分页顺序稳定不容易出现重复或丢数据。对应的索引可以设计成CREATEINDEXidx_created_idONorders(created_at,id);如果查询方向是倒序MySQL 也可以利用这个组合索引来减少扫描成本。接口返回时cursor 可以包含两个字段{items:[{id:100,created_at:2026-05-01 12:00:00,status:1}],next_cursor:{created_at:2026-05-01 12:00:00,id:100}}真实项目里一般不会直接把这个对象暴露给前端而是做一层编码。比如把{created_at:2026-05-01 12:00:00,id:100}编码成一个字符串eyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wMSAxMjowMDowMCIsImlkIjoxMDB9前端不需要关心 cursor 内部结构只需要在下一次请求时原样传回来。GET /orders?cursoreyJjcmVhdGVkX2F0IjoiMjAyNi0wNS0wMSAxMjowMDowMCIsImlkIjoxMDB9limit20后端解析 cursor 后再拼出对应的查询条件。这种方式非常适合时间线类型的数据比如订单列表 评论列表 消息列表 通知列表 Agent Run 记录 工具调用日志 会话历史2.3 方案三延迟关联前面两种游标分页都很好但它们有一个共同问题不适合任意跳页。可是有些后台管理系统确实会要求保留页码分页。比如运营后台、订单后台、财务对账系统页面上可能就是一个传统表格第一页 第二页 第三页 ... 跳到第 N 页如果产品形态暂时不能改成 cursor 分页那可以考虑延迟关联。原始 SQL 是SELECT*FROMordersORDERBYidLIMIT1000000,20;这个写法会直接在大 offset 场景下读取完整行数据。优化后可以改成SELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tONo.idt.id;这条 SQL 分成两步理解。第一步子查询只查idSELECTidFROMordersORDERBYidLIMIT1000000,20因为id本身就是主键索引所以这一步只需要扫描主键索引拿到目标页的 20 个主键。第二步再根据这 20 个主键回表查询完整数据SELECTo.*FROMorders oJOIN(...)tONo.idt.id;这样做的好处是前面那 100 万条被跳过的数据只发生在更轻量的索引扫描上而不是对每一行都读取完整数据。尤其是当表字段很多、单行数据比较大时这种优化会更明显。比如订单表里如果还有很多字段收货地址 备注 优惠信息 扩展 JSON 发票信息 物流信息直接SELECT *深分页成本会比较高。而延迟关联可以先在索引里找到目标页的主键再只对最终需要返回的 20 条数据回表。不过这个方案也不要神化。延迟关联并没有从根上消灭 offset。它依然需要扫描前面的 100 万个索引项只是避免了对前面 100 万行都读取完整数据。所以它更适合这种场景业务必须保留页码分页 表字段比较多 直接 SELECT * 深分页很慢 短期内不方便改成 cursor 分页如果业务允许改接口优先级通常还是 cursor 分页更高。2.4 方案四覆盖索引还有一种常见思路是使用覆盖索引。很多慢 SQL 之所以慢是因为查询写成了SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,20;但实际上列表页可能根本不需要展示所有字段。比如订单列表只展示订单 ID 用户 ID 订单状态 订单金额 创建时间那就没必要SELECT *。可以把查询改成SELECTid,user_id,status,amount,created_atFROMordersORDERBYcreated_atDESCLIMIT1000000,20;然后设计一个覆盖索引CREATEINDEXidx_order_listONorders(created_at,id,user_id,status,amount);覆盖索引的意思是查询需要的字段都能从索引里直接拿到不需要再回表读取完整行数据。这样可以减少大量回表成本。如果列表页本来只需要几个字段但是 SQL 却写成SELECT *那就会导致很多不必要的数据读取。这一点在真实项目里非常常见。因为一开始数据量小SELECT *看起来没什么问题开发也方便。等数据量上来以后列表查询越来越慢再回头看就会发现很多字段其实根本没有用到。不过覆盖索引也不是越多越好。索引本身是有成本的会占用更多磁盘空间 插入和更新数据时需要维护索引 索引字段太多会让索引变大 过多索引会影响写入性能所以覆盖索引更适合那些查询频率高、字段相对固定、性能收益明显的列表接口。比如订单列表、日志列表、消息列表这类接口如果它们的查询字段很稳定就可以考虑用覆盖索引优化。2.5 几种方案对比到这里四种常见优化方案就讲完了。它们的目标都是减少深分页带来的无效成本但适用场景不太一样。方案核心思路适合场景优点缺点主键游标分页用id last_id替代LIMIT offset按主键顺序浏览的数据性能好实现简单不支持任意跳页created_at id复合游标用时间和主键共同定位下一页时间线、订单、消息、日志排序稳定适合真实业务接口和 SQL 都比普通分页复杂延迟关联先查目标页主键再回表查完整数据必须保留页码分页的后台系统兼容原有分页模式减少回表仍然需要扫描 offset覆盖索引查询字段全部从索引中获取字段固定的高频列表查询减少回表提升查询效率增加索引存储和维护成本技术方案没有绝对最优只有是否适合当前业务。三、业务层面怎么解决深分页前面讲的几种方案主要还是站在 SQL 和索引的角度解决问题。但在真实项目里深分页很多时候不只是数据库问题。比如下面这条 SQLSELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,20;从技术角度看我们可以用游标分页、延迟关联、覆盖索引去优化它。但从业务角度看也应该反过来问一句用户真的需要翻到第 100 万条订单吗很多性能问题并不是数据库不够强而是业务查询方式本身太粗放。如果产品允许用户在一个几千万数据量的列表里无限制翻页那数据库迟早会被拖慢。所以深分页优化不能只靠 SQL还要从产品交互、接口设计和数据架构上一起处理。3.1 限制最大页数最直接的业务方案是限制最大可翻页范围。比如后台订单列表最多允许查询前 100 页每页 20 条最多查看前 100 页也就是最多查看 2000 条数据如果用户继续往后翻可以提示数据量过大请缩小筛选范围后再查询这个做法听起来有点“粗暴”但在很多后台系统里其实很合理。因为用户翻到非常靠后的页码时通常不是为了“浏览”而是想找某一类数据。比如运营同学想查某个时间段的订单 某个用户的订单 某个状态下的异常订单 某个金额范围内的订单这时继续让他一页一页翻体验并不好。更合理的做法是引导他增加筛选条件。限制最大页数的本质不是禁止用户查数据而是避免用户用低效的方式查数据。这一点在后台系统里尤其重要。后台系统不像普通用户端页面很多查询都是直接打到核心业务表上。如果每个人都能随便翻到几万页数据库压力会非常不可控。所以我觉得对于大数据量列表可以提前定一个规则默认只支持查看最近数据 超过一定页数后必须增加筛选条件 历史数据走专门的查询入口这比单纯在 SQL 上做优化更稳定。3.2 引导用户使用筛选条件深分页出现频繁往往说明查询范围太大。比如用户直接查全量订单SELECT*FROMordersORDERBYcreated_atDESCLIMIT1000000,20;这个查询没有任何过滤条件只是按时间倒序翻页。如果订单表有几千万条数据越往后翻肯定越慢。更合理的方式是让用户先缩小查询范围。比如按时间筛选SELECT*FROMordersWHEREcreated_at2026-05-01 00:00:00ANDcreated_at2026-06-01 00:00:00ORDERBYcreated_atDESCLIMIT20;或者按订单状态筛选SELECT*FROMordersWHEREstatus1ORDERBYcreated_atDESCLIMIT20;再或者组合多个条件SELECT*FROMordersWHEREcreated_at2026-05-01 00:00:00ANDcreated_at2026-06-01 00:00:00ANDstatus1ORDERBYcreated_atDESCLIMIT20;这样做有两个好处。第一减少数据库需要扫描的数据量。第二更符合用户真实的查询意图。很多时候用户不是想看“第 50000 页”而是想找到“5 月份未支付的订单”或者“某个用户最近的订单”。从这个角度看筛选条件其实比分页更重要。分页解决的是“数据怎么分批返回”筛选解决的是“用户到底想查哪部分数据”。如果筛选条件设计得好很多深分页问题自然就不会出现。在实际项目里可以优先考虑这些筛选维度筛选维度示例时间范围最近 7 天、最近 30 天、自定义时间段状态待支付、已支付、已取消、退款中用户维度用户 ID、手机号、用户名业务编号订单号、任务 ID、流水号金额范围大于 100、小于 1000异常类型支付失败、库存不足、调用失败对于后台系统来说最好不要只给一个大列表然后让用户从第一页慢慢翻。更好的设计是先筛选再分页。3.3 用“加载更多”替代“跳到第 N 页”并不是所有列表都适合页码分页。比如下面这些场景消息列表 评论列表 通知列表 Feed 流 操作日志 Agent 执行记录 工具调用日志用户通常只关心最近的数据。这时页面上如果还设计成第 1 页、第 2 页、第 3 页、跳到第 10000 页其实并不符合使用习惯。更自然的交互是下拉刷新 加载更多 继续查看更早记录对应到后端接口也可以从传统的 page 模式GET /orders?page50000page_size20改成 cursor 模式GET /orders?cursorxxxlimit20这个变化看起来只是接口参数变了但背后代表的是查询模型的变化。page page_size关心的是我要第几页cursor limit关心的是从上一次看到的位置继续往后查对于大数据量列表来说后者通常更稳定。尤其是数据还在不断写入的时候cursor 分页也更容易保证分页结果的连续性。比如用户正在查看消息列表时系统又插入了新的消息。如果用页码分页第二页的数据可能会因为新数据插入而发生偏移导致用户看到重复数据或者漏掉数据。而 cursor 分页是基于上一页最后一条记录继续查受新增数据影响更小。所以在设计列表接口时可以先问自己一个问题这个列表真的需要跳到第 N 页吗如果答案是否定的就没必要强行使用页码分页。3.4 冷热数据拆分和历史数据归档如果一张表的数据量持续增长只靠分页优化是不够的。比如订单表、日志表、消息表、任务执行记录表都有一个共同特点数据一直在写入 最近数据访问频繁 历史数据访问较少这类表很适合做冷热数据拆分。比如订单数据可以拆成近 3 个月订单orders_hot 3 个月以前订单orders_archive大多数在线查询只查热表SELECT*FROMorders_hotWHEREcreated_at2026-05-01 00:00:00ORDERBYcreated_atDESCLIMIT20;如果用户确实需要查询历史订单再走历史查询入口。这种设计的好处是核心在线表的数据量可以控制在一个相对稳定的范围内查询性能也更容易保障。对于日志、Agent 执行记录这类数据也可以采用类似思路。比如最近 7 天日志放在 MySQL 热表 更早日志归档到 ClickHouse / Elasticsearch / 对象存储所以当数据量越来越大时不要把所有查询压力都压到一张 MySQL 表上。分页优化只能缓解问题数据分层才能从架构上降低压力。写在文后期待您的一键三连如果有什么问题或建议欢迎在评论区交流