企业官网建设流程全解析

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（全表扫描）或range，rows估算为1000010，Extra里大概率看不到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：

这个表格是简化后的示意，但趋势是真实的：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的执行逻辑变成了：从索引上定位到id=1000000的位置，向后扫描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 MySQL：LIMIT 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 Server：OFFSET/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是offset+limit而不是limit级别，那这个分页迟早是隐患。早点改，别等慢查询日志把你喊醒。