SELECT * FROM table LIMIT 1000000, 10。第一次看到这条SQL时我以为自己眼花了——翻到第100万行之后取10条谁会在生产环境写这种东西直到后来我在一个电商后台管理系统里亲眼看到这条SQL出现在慢查询日志里而且执行时间从最初的几十毫秒变成了十几秒我才意识到分页查询尤其是深分页是很多业务系统从“能用”走向“卡顿”的隐形杀手。这事儿说起来挺有意思。分页查询几乎是每个后端开发写过的第一条业务SQL也是面试八股里的常客但真正把它讲透的人不多。LIMIT 1000000, 10 看起来就两个关键字背后却牵扯到索引结构、执行计划、回表机制、排序策略、数据库方言差异甚至还有业务架构设计的问题。这篇博客就用“庖丁解牛”的方式把这条SQL一层层拆开从执行原理讲到优化方案再讲到不同数据库下的分页写法差异和实际排查经验。适合正在被慢SQL困扰的后端开发、DBA也适合想系统理解分页查询原理的初学者。1. 拆解LIMIT 1000000, 10 到底在数据库里做了什么1.1 从EXPLAIN说起数据库是怎么“翻书”的我习惯拿到一条慢SQL先EXPLAIN一把。这条SQL语句在MySQL的InnoDB存储引擎下执行计划通常长这样type为ALL全表扫描或rangerows估算为1000010Extra里大概率看不到Using index。这意味着数据库根本不知道第1000000行数据在磁盘的哪个位置它只能老老实实地从第一行开始一行一行地数数到第1000001行才开始取数据取够10行再返回。这个行为可以类比成翻一本厚书你要看第100001页但书没有目录也没有页码索引你只能从第一页开始一页一页翻过去。翻到第100001页本身不慢慢的是你前面翻了100000页这个动作。数据库的LIMIT分页就是这个道理OFFSET越大扫描的无用数据就越多响应时间几乎是线性增长的。1.2 隐藏的成本回表、排序与随机IO如果只是“数行数”倒还好更致命的是InnoDB的二级索引和聚簇索引结构决定了大多数情况下你不能只扫描索引就完成任务。以SELECT *为例如果WHERE条件走的是二级索引那么每命中一条记录数据库都要拿着索引里的主键去聚簇索引里“回表”取一次完整的数据行。一次回表可能对应一次随机IO而回表次数不是10次是1000010次——前面那100万条虽然不返回给用户但每一条都经历了索引查找和回表确认。如果语句里还带了ORDER BY情况会雪上加霜。想象一下你要对100万行数据做排序然后只取出第1000001到第1000010行这意味着数据库要先排完这100万行再扔掉前面大部分结果。排序可能用文件排序filesort内存装不下就往磁盘写临时文件这I/O成本比前面说的回表还要夸张。这就是为什么同样的分页语句在数据量从10万涨到100万后执行时间不是涨10倍而是涨几十倍。1.3 浅分页和深分页差距有多大拿一组真实量级的数据来对比感受一下。假设一张订单表有200万行主键是自增id分页SQL扫描行数回表次数相对耗时LIMIT 0, 1010101xLIMIT 10000, 101001010010约8xLIMIT 1000000, 1010000101000010约200x以上这个表格是简化后的示意但趋势是真实的OFFSET每增加一个数量级扫描量就增加一个数量级直到某一天数据库的连接池被撑满然后整个服务的接口都开始超时。很多系统崩掉不是QPS突然涨了多少而是某个运营在后台点了“下一页”两百次一堆深分页SQL把数据库打垮了。2. 业务场景什么样的系统会出现“百万行深分页”2.1 触发深分页的典型场景我在实际排查过程中发现深分页SQL从来不是某个人故意写出来的而是业务演进到一定阶段后的必然产物。最常见的触发场景有这几类后台管理系统的列表页。运营人员确实会手动翻到几百页甚至几千页去看历史数据尤其是订单管理、用户管理这类模块。定时任务或数据同步脚本。为了分批处理全表数据用页码递增的方式循环查询每一批LIMIT 100000, 100跑着跑着offset就逼近百万。报表导出功能。前端看起来是“导出全部”后端实现却是按1000条一页去分页拉取Offset越攒越大。无限滚动加载。某些前台产品用“加载更多”代替翻页接口每次带上当前已加载的总条数作为offset。这些场景有个共同点都在用“页码偏移”这种最简单的分页模型而且业务初期数据量小谁都想不到它会在某一天变成性能瓶颈。2.2 别急着调SQL先看业务设计是否合理我踩过一个大坑花了整整一周去优化一条深分页SQL从延迟关联到覆盖索引都试了一遍效果是有但没过多久数据量继续涨又慢回去了。后来我回头审视业务才发现这个接口根本不需要允许用户翻到第100万条记录——后台管理列表的真实使用习惯是用户要么用筛选条件缩小范围要么只看前几十页真正需要“翻到底”的几乎没有。所以我在做技术方案时现在会先问三个问题第一业务是否真的需要随机跳页第二是否可以限制最大翻页深度第三是否可以把“页码”改成“游标”这三个问题的答案决定了你该用哪种优化方案。如果业务可以接受只翻前100页那在应用层直接把offset超过10000的请求拦截掉比什么SQL优化都有效。2.3 数据分布和写放大也会影响分页稳定性还有一个容易被忽视的因素数据的增删改会导致索引页产生空洞同一个ORDER BY id的分页查询在数据频繁插入删除的表中翻页过程中可能出现重复数据或缺失数据而且页空洞还会让扫描路径变长。这说明一个系统的分页性能不是静态的它和数据质量、写入模式都有关系。这也是为什么我在后面的优化方案里会强调“稳定排序”这个点。3. 核心优化实战四套方案把深分页拉回快车道3.1 方案一覆盖索引 延迟关联让回表只发生10次延迟关联的核心思想是不要在定位阶段就回表拿全量字段先在索引上完成定位、排序、偏移最后再一次性把需要的行关联回来。针对开头那条SQL可以改写成SELECT t.* FROM table t INNER JOIN ( SELECT id FROM table ORDER BY id LIMIT 1000000, 10 ) tmp ON t.id tmp.id;里层子查询只查主键id在InnoDB里主键索引本身就是聚簇索引扫描1000010个主键值的代价比扫描完整行要小很多外层再通过主键回表取10条完整记录。实测下来在百万级数据量下这种写法通常能让执行时间从秒级降到百毫秒级。但这个方案有一个前提ORDER BY的字段必须走索引否则子查询内部可能还是要filesort那就等于白优化了。如果排序字段是普通索引建议把排序字段和主键建成联合索引让排序在索引内部完成。3.2 方案二Keyset分页 / Seek Method彻底消灭OFFSET延迟关联只是降低单次查询的成本没有解决“每次都要扫描前面所有数据”的本质问题。如果想彻底根治就用基于游标的分页不用页码而是每次把上一次拿到的最后一条记录的排序字段值传回来用WHERE条件直接定位。-- 第一页 SELECT * FROM table ORDER BY id LIMIT 10; -- 第二页假设上一页最后一条id是1000000 SELECT * FROM table WHERE id 1000000 ORDER BY id LIMIT 10;这条SQL的执行逻辑变成了从索引上定位到id1000000的位置向后扫描10条返回。无论翻到第几页扫描量都是10条性能恒定。这就是为什么像Twitter、Facebook这类信息流产品分页都是基于游标的“加载更多”而不是基于页码的“上一页下一页”。Keyset分页唯一的缺点是不支持随机跳页用户不能直接输入第500页跳过去。但对于绝大多数“加载更多”交互和“顺序处理”场景这是完全够用的。多条件排序时比如ORDER BY create_time, id游标条件要变成复合条件WHERE (create_time, id) (:last_time, :last_id)并且建对应的联合索引。3.3 方案三流式查询解决的是内存问题而非响应速度有时候慢不是SQL本身慢而是结果集太大把内存打爆了。比如一次性取100万行用来做数据迁移或生成报表JVM直接报OutOfMemoryError。这种情况我用过流式查询方案MySQL JDBC驱动里把fetchSize设置为Integer.MIN_VALUE会让PreparedStatement变成流式读取每次只从服务端拉取一小批逐行消费。这样内存占用稳住了但响应时间并不会因此变快只是把“全量查一次”的原子操作拆成了分批拉取系统不至于因为一次查询内存就崩。顺带提一句Java里比较常见的内存溢出报错“GC overhead limit exceeded”和“Java heap space”很多就发生在一次深分页查询把百万行结果全量加载到内存的场景。所以说流式查询不是用来优化深分页慢的它更像是一个保命手段。3.4 方案四业务层兜底把深分页直接管住方案再花哨也不如从入口把问题掐掉。我在多个项目里落地过的做法有这几种前端页码输入框加上限比如最多100页超出就提示不再支持。后端接口校验offset最大值超过50000直接返回参数错误。用“上一页/下一页”按钮替代数字页码配合keyset分页实现。对于后台列表强制要求带筛选条件不带条件只能看前几页。这些手段听起来很“不技术”但确实是最稳的。我见过太多因为运营同学手滑点到第3000页导致数据库CPU打满的线上事故。与其等事故发生了再去扩容数据库不如在业务上做约束把分页的边界限制在系统能承受的范围内。4. 分页也有“方言”主流数据库的实现差异与通用坑4.1 MySQLLIMIT offset, count 的两种写法MySQL里LIMIT 1000000, 10和LIMIT 10 OFFSET 1000000是等价的都表示从第1000000行开始取10行。需要注意MySQL 8.0对OFFSET的优化能力依然有限深分页高成本问题依然存在。在排查MySQL慢SQL时我习惯用EXPLAIN ANALYZE来看实际执行时间MySQL 8.0.18它能告诉我们每一行扫描到底花了多久比单纯看rows估算值更直观。另外MySQL的分页查询经常要和COUNT()搭配使用总条数一出来才能计算总页数。但COUNT()在InnoDB里需要全表扫描尤其是加上WHERE条件后可能比分页查询本身还慢。优化方案通常有用一个近似的估算值比如EXPLAIN里的rows代替精确值维护一张计数表在写入时同步更新或者干脆不显示总条数只提供“加载更多”。4.2 PostgreSQL优化器更强但OFFSET大的病一样犯PostgreSQL的LIMIT/OFFSET语法和MySQL基本一致而且它的优化器在多数场景下比MySQL更聪明但遇到LIMIT 1000000, 10这类深分页同样要对前面的行做排序和丢弃。我在PG里做分页优化时用的还是keyset思路不过PG的优势是支持更复杂的行值比较WHERE (create_time, id) (:last_time, :last_id)可以直接走复合索引写起来比MySQL更顺手。4.3 SQL ServerOFFSET/FETCH与ROW_NUMBER()窗口函数SQL Server 2012及以上版本支持OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY写法虽然不同底层性能特性和MySQL深分页差不多。更老的兼容性写法是用ROW_NUMBER()窗口函数SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM table ) t WHERE rn BETWEEN 1000001 AND 1000010;这种方式的好处是排序逻辑可以写得很灵活坏处和直接OFFSET一样要先生成全量行号深分页照样不便宜。SQL Server的另一个坑是它默认按“读取顺序”返回结果如果ORDER BY字段不唯一翻页过程中可能出现数据漂移。解决方法是ORDER BY里加上唯一字段比如ORDER BY create_time, id。4.4 国产数据库达梦等兼容层下的分页写法最近几年国产数据库用得越来越多我在项目里也接触过达梦。达梦的分页语法兼容Oracle的ROWNUM也支持类似SQL Server的写法具体取决于兼容模式。比如有的版本可以这样写SELECT * FROM ( SELECT t.*, ROWNUM rn FROM table t ) WHERE rn BETWEEN 1000001 AND 1000010;这种写法在数据量大时性能同样堪忧而且ROWNUM在排序之前就会生成如果里层没有先排序很容易翻页翻出乱序数据。国产数据库的文档和社区生态相对较薄遇到分页慢的问题最快的方式还是用EXPLAIN看执行计划再套用延迟关联或keyset方案。4.5 一个经常被问到的组合LIMIT 1 FOR UPDATE SKIP LOCKED热词里提到了LIMIT 1 FOR UPDATE SKIP LOCKED这个和分页关系不大但确实是LIMIT的一个经典应用场景顺手讲一下。它用于并发任务队列多个worker同时抢任务时FOR UPDATE锁定要更新的行SKIP LOCKED跳过已经被其他事务锁定的行避免锁等待。这里的关键点是SKIP LOCKED的语义是“跳过锁定的行”而不是“只锁第一条”配合LIMIT 1的时候它会在目前未被锁定的行里选一条返回。这个写法在MySQL 8.0和PostgreSQL里都支持用来做分布式任务分发非常方便。5. 常见问题与排查技巧实录5.1 慢SQL排查先看执行计划再动手优化排查分页慢SQL我有一套固定的流程。第一步是打开慢查询日志确认是哪条SQL在消耗时间第二步是EXPLAIN看执行计划重点看type字段ALL还是range、rows字段估算扫描行数、Extra字段是否出现Using filesort、Using temporary第三步是根据执行计划决定优化方向。如果Extra里出现Using filesort优先建索引消除排序如果type是ALL先看看能不能用索引覆盖如果都正常但还是慢就要考虑是不是业务上允许深分页的问题。5.2 翻页数据重复或缺失多半是排序字段不唯一分页过程中出现重复数据是运维同学反馈最多的问题之一。根因通常是ORDER BY的字段不是唯一的比如只按create_time排序而同一秒内插入了多条记录数据库返回顺序可能不稳定。翻到下一页时前一次查询的最后一条数据和这次查询的第一条数据发生了重叠。解决办法很简单排序条件里加一个唯一字段比如ORDER BY create_time, id并在查询接口里把上一页最后一条记录的完整排序字段既是create_time又是id作为游标传回来。5.3 “分页查询慢怎么用Redis优化”这个问题要分情况看网上常有人问“分页慢能不能用Redis优化”我只给一个务实回答能做但别迷信。Redis适合优化的场景是热点数据分页比如首页榜单前10页可以预先把结果集缓存到Redis里用ZSET或者LIST按页读取。但对于“用户随便输入条件组合出来的深分页”缓存根本帮不上忙因为Query的组合空间太大你缓存不过来而且生成第100万页数据本身是慢的这个成本躲不掉。更合理的用法是把COUNT(*)的结果缓存起来减轻总页数查询的压力或者把第一页、第二页这些高访问量页面缓存起来挡住大部分流量深分页仍然走后端数据库。5.4 别用SELECT *这不是玄学前面提到的延迟关联方案要用到覆盖索引而覆盖索引的前提是你查询的字段必须都在索引里。一旦写下SELECT *数据库就得回表拿所有字段覆盖索引直接失效。所以我在任何代码评审里看到SELECT *都会建议改成显式列名。这不只是为了性能还为了方便后续加字段时不影响线上查询。5.5 分页参数也要防SQL注入分页参数是整数但很多老系统的SQL是字符串拼接的这就会引出SQL注入问题。比如页号参数被拼成“1; DROP TABLE”那后果不敢想。我的习惯是分页参数一律用预编译占位符传入同时在接口层做类型校验和范围校验offset和size必须是正整数且不能超过预设上限。网上流传的“万能密码绕过”之类攻击本质上都是参数拼接导致的结构改变防注入最有效的方式就是参数化查询。5.6 分页查询里的序号字段怎么生成有些业务在列表显示时希望每条数据前面有个“序号”比如第1000001行显示1000001。MySQL 8.0可以用ROW_NUMBER()窗口函数老版本可以用自定义变量rownum : rownum 1。SQL Server用ROW_NUMBER() OVER (ORDER BY id)PostgreSQL也一样。这个操作本身不难但要小心如果分页查询的排序不稳定序号在翻页时会跳动和上面的排序字段唯一性问题一脉相承。我在实际项目里翻过最狠的一次车是一条SELECT * FROM table LIMIT 1000000, 10它出现在一个运营后台的导出逻辑里原意是“每页拿1万条分100次导出”结果offset算错循环了200多次数据库直接被打爆。从那之后我对分页查询的态度就变成了能不用OFFSET就不用OFFSET用完必须看一眼执行计划。现在自己写代码凡是列表接口第一反应都是keyset分页凡是要做全量扫描的任务第一反应都是游标流式。最后再分享一个小技巧判断你的分页会不会出问题不用等线上报警直接在测试环境造个10万条数据把EXPLAIN的rows打出来看一眼——如果rows是offsetlimit而不是limit级别那这个分页迟早是隐患。早点改别等慢查询日志把你喊醒。 
长河君 · 2026/9/7 20:55:39 · 阅读 1.2k 深分页SQL性能优化：从LIMIT百万行到延迟关联与Keyset分页
SELECT * FROM table LIMIT 1000000, 10。第一次看到这条SQL时我以为自己眼花了——翻到第100万行之后取10条谁会在生产环境写这种东西直到后来我在一个电商后台管理系统里亲眼看到这条SQL出现在慢查询日志里而且执行时间从最初的几十毫秒变成了十几秒我才意识到分页查询尤其是深分页是很多业务系统从“能用”走向“卡顿”的隐形杀手。这事儿说起来挺有意思。分页查询几乎是每个后端开发写过的第一条业务SQL也是面试八股里的常客但真正把它讲透的人不多。LIMIT 1000000, 10 看起来就两个关键字背后却牵扯到索引结构、执行计划、回表机制、排序策略、数据库方言差异甚至还有业务架构设计的问题。这篇博客就用“庖丁解牛”的方式把这条SQL一层层拆开从执行原理讲到优化方案再讲到不同数据库下的分页写法差异和实际排查经验。适合正在被慢SQL困扰的后端开发、DBA也适合想系统理解分页查询原理的初学者。1. 拆解LIMIT 1000000, 10 到底在数据库里做了什么1.1 从EXPLAIN说起数据库是怎么“翻书”的我习惯拿到一条慢SQL先EXPLAIN一把。这条SQL语句在MySQL的InnoDB存储引擎下执行计划通常长这样type为ALL全表扫描或rangerows估算为1000010Extra里大概率看不到Using index。这意味着数据库根本不知道第1000000行数据在磁盘的哪个位置它只能老老实实地从第一行开始一行一行地数数到第1000001行才开始取数据取够10行再返回。这个行为可以类比成翻一本厚书你要看第100001页但书没有目录也没有页码索引你只能从第一页开始一页一页翻过去。翻到第100001页本身不慢慢的是你前面翻了100000页这个动作。数据库的LIMIT分页就是这个道理OFFSET越大扫描的无用数据就越多响应时间几乎是线性增长的。1.2 隐藏的成本回表、排序与随机IO如果只是“数行数”倒还好更致命的是InnoDB的二级索引和聚簇索引结构决定了大多数情况下你不能只扫描索引就完成任务。以SELECT *为例如果WHERE条件走的是二级索引那么每命中一条记录数据库都要拿着索引里的主键去聚簇索引里“回表”取一次完整的数据行。一次回表可能对应一次随机IO而回表次数不是10次是1000010次——前面那100万条虽然不返回给用户但每一条都经历了索引查找和回表确认。如果语句里还带了ORDER BY情况会雪上加霜。想象一下你要对100万行数据做排序然后只取出第1000001到第1000010行这意味着数据库要先排完这100万行再扔掉前面大部分结果。排序可能用文件排序filesort内存装不下就往磁盘写临时文件这I/O成本比前面说的回表还要夸张。这就是为什么同样的分页语句在数据量从10万涨到100万后执行时间不是涨10倍而是涨几十倍。1.3 浅分页和深分页差距有多大拿一组真实量级的数据来对比感受一下。假设一张订单表有200万行主键是自增id分页SQL扫描行数回表次数相对耗时LIMIT 0, 1010101xLIMIT 10000, 101001010010约8xLIMIT 1000000, 1010000101000010约200x以上这个表格是简化后的示意但趋势是真实的OFFSET每增加一个数量级扫描量就增加一个数量级直到某一天数据库的连接池被撑满然后整个服务的接口都开始超时。很多系统崩掉不是QPS突然涨了多少而是某个运营在后台点了“下一页”两百次一堆深分页SQL把数据库打垮了。2. 业务场景什么样的系统会出现“百万行深分页”2.1 触发深分页的典型场景我在实际排查过程中发现深分页SQL从来不是某个人故意写出来的而是业务演进到一定阶段后的必然产物。最常见的触发场景有这几类后台管理系统的列表页。运营人员确实会手动翻到几百页甚至几千页去看历史数据尤其是订单管理、用户管理这类模块。定时任务或数据同步脚本。为了分批处理全表数据用页码递增的方式循环查询每一批LIMIT 100000, 100跑着跑着offset就逼近百万。报表导出功能。前端看起来是“导出全部”后端实现却是按1000条一页去分页拉取Offset越攒越大。无限滚动加载。某些前台产品用“加载更多”代替翻页接口每次带上当前已加载的总条数作为offset。这些场景有个共同点都在用“页码偏移”这种最简单的分页模型而且业务初期数据量小谁都想不到它会在某一天变成性能瓶颈。2.2 别急着调SQL先看业务设计是否合理我踩过一个大坑花了整整一周去优化一条深分页SQL从延迟关联到覆盖索引都试了一遍效果是有但没过多久数据量继续涨又慢回去了。后来我回头审视业务才发现这个接口根本不需要允许用户翻到第100万条记录——后台管理列表的真实使用习惯是用户要么用筛选条件缩小范围要么只看前几十页真正需要“翻到底”的几乎没有。所以我在做技术方案时现在会先问三个问题第一业务是否真的需要随机跳页第二是否可以限制最大翻页深度第三是否可以把“页码”改成“游标”这三个问题的答案决定了你该用哪种优化方案。如果业务可以接受只翻前100页那在应用层直接把offset超过10000的请求拦截掉比什么SQL优化都有效。2.3 数据分布和写放大也会影响分页稳定性还有一个容易被忽视的因素数据的增删改会导致索引页产生空洞同一个ORDER BY id的分页查询在数据频繁插入删除的表中翻页过程中可能出现重复数据或缺失数据而且页空洞还会让扫描路径变长。这说明一个系统的分页性能不是静态的它和数据质量、写入模式都有关系。这也是为什么我在后面的优化方案里会强调“稳定排序”这个点。3. 核心优化实战四套方案把深分页拉回快车道3.1 方案一覆盖索引 延迟关联让回表只发生10次延迟关联的核心思想是不要在定位阶段就回表拿全量字段先在索引上完成定位、排序、偏移最后再一次性把需要的行关联回来。针对开头那条SQL可以改写成SELECT t.* FROM table t INNER JOIN ( SELECT id FROM table ORDER BY id LIMIT 1000000, 10 ) tmp ON t.id tmp.id;里层子查询只查主键id在InnoDB里主键索引本身就是聚簇索引扫描1000010个主键值的代价比扫描完整行要小很多外层再通过主键回表取10条完整记录。实测下来在百万级数据量下这种写法通常能让执行时间从秒级降到百毫秒级。但这个方案有一个前提ORDER BY的字段必须走索引否则子查询内部可能还是要filesort那就等于白优化了。如果排序字段是普通索引建议把排序字段和主键建成联合索引让排序在索引内部完成。3.2 方案二Keyset分页 / Seek Method彻底消灭OFFSET延迟关联只是降低单次查询的成本没有解决“每次都要扫描前面所有数据”的本质问题。如果想彻底根治就用基于游标的分页不用页码而是每次把上一次拿到的最后一条记录的排序字段值传回来用WHERE条件直接定位。-- 第一页 SELECT * FROM table ORDER BY id LIMIT 10; -- 第二页假设上一页最后一条id是1000000 SELECT * FROM table WHERE id 1000000 ORDER BY id LIMIT 10;这条SQL的执行逻辑变成了从索引上定位到id1000000的位置向后扫描10条返回。无论翻到第几页扫描量都是10条性能恒定。这就是为什么像Twitter、Facebook这类信息流产品分页都是基于游标的“加载更多”而不是基于页码的“上一页下一页”。Keyset分页唯一的缺点是不支持随机跳页用户不能直接输入第500页跳过去。但对于绝大多数“加载更多”交互和“顺序处理”场景这是完全够用的。多条件排序时比如ORDER BY create_time, id游标条件要变成复合条件WHERE (create_time, id) (:last_time, :last_id)并且建对应的联合索引。3.3 方案三流式查询解决的是内存问题而非响应速度有时候慢不是SQL本身慢而是结果集太大把内存打爆了。比如一次性取100万行用来做数据迁移或生成报表JVM直接报OutOfMemoryError。这种情况我用过流式查询方案MySQL JDBC驱动里把fetchSize设置为Integer.MIN_VALUE会让PreparedStatement变成流式读取每次只从服务端拉取一小批逐行消费。这样内存占用稳住了但响应时间并不会因此变快只是把“全量查一次”的原子操作拆成了分批拉取系统不至于因为一次查询内存就崩。顺带提一句Java里比较常见的内存溢出报错“GC overhead limit exceeded”和“Java heap space”很多就发生在一次深分页查询把百万行结果全量加载到内存的场景。所以说流式查询不是用来优化深分页慢的它更像是一个保命手段。3.4 方案四业务层兜底把深分页直接管住方案再花哨也不如从入口把问题掐掉。我在多个项目里落地过的做法有这几种前端页码输入框加上限比如最多100页超出就提示不再支持。后端接口校验offset最大值超过50000直接返回参数错误。用“上一页/下一页”按钮替代数字页码配合keyset分页实现。对于后台列表强制要求带筛选条件不带条件只能看前几页。这些手段听起来很“不技术”但确实是最稳的。我见过太多因为运营同学手滑点到第3000页导致数据库CPU打满的线上事故。与其等事故发生了再去扩容数据库不如在业务上做约束把分页的边界限制在系统能承受的范围内。4. 分页也有“方言”主流数据库的实现差异与通用坑4.1 MySQLLIMIT offset, count 的两种写法MySQL里LIMIT 1000000, 10和LIMIT 10 OFFSET 1000000是等价的都表示从第1000000行开始取10行。需要注意MySQL 8.0对OFFSET的优化能力依然有限深分页高成本问题依然存在。在排查MySQL慢SQL时我习惯用EXPLAIN ANALYZE来看实际执行时间MySQL 8.0.18它能告诉我们每一行扫描到底花了多久比单纯看rows估算值更直观。另外MySQL的分页查询经常要和COUNT()搭配使用总条数一出来才能计算总页数。但COUNT()在InnoDB里需要全表扫描尤其是加上WHERE条件后可能比分页查询本身还慢。优化方案通常有用一个近似的估算值比如EXPLAIN里的rows代替精确值维护一张计数表在写入时同步更新或者干脆不显示总条数只提供“加载更多”。4.2 PostgreSQL优化器更强但OFFSET大的病一样犯PostgreSQL的LIMIT/OFFSET语法和MySQL基本一致而且它的优化器在多数场景下比MySQL更聪明但遇到LIMIT 1000000, 10这类深分页同样要对前面的行做排序和丢弃。我在PG里做分页优化时用的还是keyset思路不过PG的优势是支持更复杂的行值比较WHERE (create_time, id) (:last_time, :last_id)可以直接走复合索引写起来比MySQL更顺手。4.3 SQL ServerOFFSET/FETCH与ROW_NUMBER()窗口函数SQL Server 2012及以上版本支持OFFSET 1000000 ROWS FETCH NEXT 10 ROWS ONLY写法虽然不同底层性能特性和MySQL深分页差不多。更老的兼容性写法是用ROW_NUMBER()窗口函数SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM table ) t WHERE rn BETWEEN 1000001 AND 1000010;这种方式的好处是排序逻辑可以写得很灵活坏处和直接OFFSET一样要先生成全量行号深分页照样不便宜。SQL Server的另一个坑是它默认按“读取顺序”返回结果如果ORDER BY字段不唯一翻页过程中可能出现数据漂移。解决方法是ORDER BY里加上唯一字段比如ORDER BY create_time, id。4.4 国产数据库达梦等兼容层下的分页写法最近几年国产数据库用得越来越多我在项目里也接触过达梦。达梦的分页语法兼容Oracle的ROWNUM也支持类似SQL Server的写法具体取决于兼容模式。比如有的版本可以这样写SELECT * FROM ( SELECT t.*, ROWNUM rn FROM table t ) WHERE rn BETWEEN 1000001 AND 1000010;这种写法在数据量大时性能同样堪忧而且ROWNUM在排序之前就会生成如果里层没有先排序很容易翻页翻出乱序数据。国产数据库的文档和社区生态相对较薄遇到分页慢的问题最快的方式还是用EXPLAIN看执行计划再套用延迟关联或keyset方案。4.5 一个经常被问到的组合LIMIT 1 FOR UPDATE SKIP LOCKED热词里提到了LIMIT 1 FOR UPDATE SKIP LOCKED这个和分页关系不大但确实是LIMIT的一个经典应用场景顺手讲一下。它用于并发任务队列多个worker同时抢任务时FOR UPDATE锁定要更新的行SKIP LOCKED跳过已经被其他事务锁定的行避免锁等待。这里的关键点是SKIP LOCKED的语义是“跳过锁定的行”而不是“只锁第一条”配合LIMIT 1的时候它会在目前未被锁定的行里选一条返回。这个写法在MySQL 8.0和PostgreSQL里都支持用来做分布式任务分发非常方便。5. 常见问题与排查技巧实录5.1 慢SQL排查先看执行计划再动手优化排查分页慢SQL我有一套固定的流程。第一步是打开慢查询日志确认是哪条SQL在消耗时间第二步是EXPLAIN看执行计划重点看type字段ALL还是range、rows字段估算扫描行数、Extra字段是否出现Using filesort、Using temporary第三步是根据执行计划决定优化方向。如果Extra里出现Using filesort优先建索引消除排序如果type是ALL先看看能不能用索引覆盖如果都正常但还是慢就要考虑是不是业务上允许深分页的问题。5.2 翻页数据重复或缺失多半是排序字段不唯一分页过程中出现重复数据是运维同学反馈最多的问题之一。根因通常是ORDER BY的字段不是唯一的比如只按create_time排序而同一秒内插入了多条记录数据库返回顺序可能不稳定。翻到下一页时前一次查询的最后一条数据和这次查询的第一条数据发生了重叠。解决办法很简单排序条件里加一个唯一字段比如ORDER BY create_time, id并在查询接口里把上一页最后一条记录的完整排序字段既是create_time又是id作为游标传回来。5.3 “分页查询慢怎么用Redis优化”这个问题要分情况看网上常有人问“分页慢能不能用Redis优化”我只给一个务实回答能做但别迷信。Redis适合优化的场景是热点数据分页比如首页榜单前10页可以预先把结果集缓存到Redis里用ZSET或者LIST按页读取。但对于“用户随便输入条件组合出来的深分页”缓存根本帮不上忙因为Query的组合空间太大你缓存不过来而且生成第100万页数据本身是慢的这个成本躲不掉。更合理的用法是把COUNT(*)的结果缓存起来减轻总页数查询的压力或者把第一页、第二页这些高访问量页面缓存起来挡住大部分流量深分页仍然走后端数据库。5.4 别用SELECT *这不是玄学前面提到的延迟关联方案要用到覆盖索引而覆盖索引的前提是你查询的字段必须都在索引里。一旦写下SELECT *数据库就得回表拿所有字段覆盖索引直接失效。所以我在任何代码评审里看到SELECT *都会建议改成显式列名。这不只是为了性能还为了方便后续加字段时不影响线上查询。5.5 分页参数也要防SQL注入分页参数是整数但很多老系统的SQL是字符串拼接的这就会引出SQL注入问题。比如页号参数被拼成“1; DROP TABLE”那后果不敢想。我的习惯是分页参数一律用预编译占位符传入同时在接口层做类型校验和范围校验offset和size必须是正整数且不能超过预设上限。网上流传的“万能密码绕过”之类攻击本质上都是参数拼接导致的结构改变防注入最有效的方式就是参数化查询。5.6 分页查询里的序号字段怎么生成有些业务在列表显示时希望每条数据前面有个“序号”比如第1000001行显示1000001。MySQL 8.0可以用ROW_NUMBER()窗口函数老版本可以用自定义变量rownum : rownum 1。SQL Server用ROW_NUMBER() OVER (ORDER BY id)PostgreSQL也一样。这个操作本身不难但要小心如果分页查询的排序不稳定序号在翻页时会跳动和上面的排序字段唯一性问题一脉相承。我在实际项目里翻过最狠的一次车是一条SELECT * FROM table LIMIT 1000000, 10它出现在一个运营后台的导出逻辑里原意是“每页拿1万条分100次导出”结果offset算错循环了200多次数据库直接被打爆。从那之后我对分页查询的态度就变成了能不用OFFSET就不用OFFSET用完必须看一眼执行计划。现在自己写代码凡是列表接口第一反应都是keyset分页凡是要做全量扫描的任务第一反应都是游标流式。最后再分享一个小技巧判断你的分页会不会出问题不用等线上报警直接在测试环境造个10万条数据把EXPLAIN的rows打出来看一眼——如果rows是offsetlimit而不是limit级别那这个分页迟早是隐患。早点改别等慢查询日志把你喊醒。