分页大概是 MySQL 里最容易被低估的操作。很多新手写的第一条带业务含义的 SQL 就是LIMIT查询语法简单到三分钟就能上手于是在很多人心里分页就等于LIMIT 10 OFFSET 20。但我在带团队和做面试官的时候发现能把这条 SQL 写出来的人一大把能说清楚深分页为什么会慢、翻页为什么会出现重复数据、游标分页和普通分页到底差在哪的人十个里面可能都凑不齐一个。这篇文章就按我自己在实际项目里摸索的经验把 MySQL 分页从头拆到尾。从最基础的LIMIT/OFFSET语法开始讲到深分页的瓶颈原理再到覆盖索引、延迟关联、游标分页这几套优化方案最后聊到排序稳定性、COUNT(*)统计这些容易被忽略但真正决定线上体验的细节。无论你是刚接触 MySQL 的初学者还是已经被深分页慢查询折磨过好几轮的开发老手这篇都能给你一份能直接落地的参考。1. 为什么要分页业务场景与最朴素的 LIMIT 写法1.1 没有 ORDER BY 的分页等于碰运气先聊一个看起来特别基础、但真的有很多人踩坑的问题分页查询到底要不要写ORDER BY答案很明确必须写。MySQL 的 InnoDB 存储引擎在物理层并不保证任何行序你执行SELECT * FROM t_user LIMIT 10 OFFSET 0时看到的结果顺序只是当前执行计划下的巧合。它可能是主键顺序可能是某个索引顺序也可能随着数据变更、统计信息更新、优化器选了不同索引而完全变化。这会带来什么后果假如你在翻页时不指定排序第一页取到的是 id 从 1 到 10 的行第二页执行LIMIT 10 OFFSET 10时刚好有一批新数据插入物理顺序变了你可能会发现第一页出现过的数据又出现在第二页或者某些行被漏掉了。这不是 MySQL 的 bug而是你根本没告诉 MySQL 它应该按什么逻辑稳定地划分每一页。所以分页的第一原则是任何时候分页查询都要带有稳定的ORDER BY。这里的稳定指的是排序字段的组合能在表里唯一确定每一行的先后位置否则就会出现第 4 节要讲的重复和丢失问题。1.2 LIMIT 的两种写法与一个高频低级错误MySQL 的分页语法核心就是LIMIT子句它有两种写法-- 写法一LIMIT 偏移量, 行数 SELECT * FROM t_order ORDER BY id LIMIT 20, 10; -- 写法二LIMIT 行数 OFFSET 偏移量 SELECT * FROM t_order ORDER BY id LIMIT 10 OFFSET 20;两种写法效果完全一样都是跳过前 20 行、返回接下来的 10 行。我个人的习惯是推荐第二种因为它的语义更显式LIMIT 10 OFFSET 20一眼就能看出要 10 条、跳过 20 条不容易搞混。第一种写法里LIMIT 20, 10经常被人误读成要 20 条、从第 10 条开始实际恰恰相反这是新手最容易犯的低级错误。还需要特别注意一个细节OFFSET是从 0 开始计数的。也就是说第 1 页的 SQL 是LIMIT 10 OFFSET 0第 2 页是LIMIT 10 OFFSET 10第 N 页的偏移量是(N - 1) * 每页条数。很多业务代码里的分页 bug 都出在这个page - 1上前端传过来的页码从 1 开始后端如果忘了减 1第一页的数据就会被跳过直接翻到第二页。1.3 分页参数换算从业务到底层如果你在做接口设计通常前端会传page和pageSize后端要换算成 SQL 里的OFFSET和LIMIT# 以 Python 为例伪代码 page max(1, int(request_params.get(page, 1))) page_size min(100, int(request_params.get(page_size, 10))) offset (page - 1) * page_size sql SELECT * FROM t_order ORDER BY id LIMIT %s OFFSET %s % (page_size, offset)这里两个个经验点第一page_size一定要做上限保护否则用户传一个 100000 的 pageSize一次查十万条数据直接把数据库和网络带宽打满第二page也要做下限保护不能是负数或 0否则OFFSET计算出来是负数SQL 直接报错。这些都属于接口层防御看似不起眼却是分页功能稳定性的第一道防线。2. 深分页为什么慢一次查询在 MySQL 内部实际干了什么2.1 回表深分页性能劣化的根源很多人在数据量小的时候感受不到分页有什么性能问题直到某天线上订单表到了几百万行发现翻到第 100 页之后的请求动不动就几百毫秒甚至几秒慢查询日志里全是这条 SQL。这时候大多数人的第一反应是加索引但加完发现效果有限——为什么因为LIMIT/OFFSET分页的核心瓶颈根本不在索引缺失而在它必须先找到所有被跳过的行。拿这条查询举例SELECT * FROM t_order ORDER BY create_time DESC LIMIT 10 OFFSET 100000;MySQL 的执行过程大致是这样的先按create_time DESC把表里的数据排序然后从排序结果里从头开始数数到第 100001 条时才开始取取 10 条返回。也就是说为了给你这 10 条数据MySQL 实际需要定位前面整整 10 万条记录的位置。如果create_time上没有索引MySQL 就得把所有满足条件的行全部读出来做一次文件排序filesort这是灾难性的。如果create_time上有索引情况好一些——它可以通过索引按顺序扫描省去了显式排序。但问题在于你要的是SELECT *索引里通常只包含create_time和主键id其他字段都得通过主键回表去数据行里取。每定位一条索引记录就可能触发一次随机 IO 回表10 万次回表哪怕每次只要 0.1 毫秒累计下来也是 10 秒级别的开销。用一个生活化的类比OFFSET 100000就像你在一盘老式磁带里想听第 50 首歌但播放器没有直接跳到第 50 首的功能只能从第 1 首开始快进一首一首地过。磁带前 49 首歌你根本不想听但你还是为它们花了时间。2.2 用 EXPLAIN 看深分页的真实成本口说无凭直接看执行计划。在表上不加任何针对性索引时EXPLAIN SELECT * FROM t_order ORDER BY create_time DESC LIMIT 10 OFFSET 100000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_order type: ALL possible_keys: NULL key: NULL key_len: NULL rows: 1048576 filtered: 100.00 Extra: Using filesorttype是ALL意味着全表扫描Extra里有Using filesort意味着 MySQL 把整张表的数据都读到内存或磁盘临时文件里做了一次排序。这种执行计划下偏移量越大排序的数据量越大耗时呈线性甚至超线性增长。通过执行计划你可以确认两件事一是这条分页查询有没有走索引二是它有没有触发文件排序。绝大多数深分页问题的第一步排查就是看这两点。加了合适的索引之后type会变成index索引全扫描或者rangeUsing filesort会消失查询耗时会显著下降但依然会随着OFFSET增大而增长——因为回表次数没有减少。2.3 实测对比不同偏移量下的查询耗时我在本地测试库里放了一张 100 万行的订单表单条记录很小只包含id、create_time、order_no、amount等字段在create_time上建有普通索引。测出来的数据大概是这样的绝对数值取决于机器和表结构但变化趋势在任何环境都成立查询目标SQL 写法扫描/回表行数耗时参考第 1 页LIMIT 10 OFFSET 010约 5ms第 1000 页LIMIT 10 OFFSET 1000010010约 30ms第 10000 页LIMIT 10 OFFSET 100000100010约 200ms第 50000 页LIMIT 10 OFFSET 500000500010约 1s可以清楚看到返回的行数始终只有 10 条但耗时从 5ms 涨到了 1 秒以上。这就是深分页问题的量化表现你的业务只需要 10 条数据数据库却为你干了 50 万条的活。而且这还是在测试环境、单表查询的情况下放到线上并发环境里几十个这样的深分页请求同时进来数据库的 IO 和 CPU 立刻就会被拖垮。所以深分页的核心矛盾可以总结成一句话OFFSET越大MySQL 需要白做的工作越多性能必然越差。理解了这一点接下来第 3 节的三套优化方案就好理解了——它们的本质都是想办法减少那些白做的工作。3. 打破深分页瓶颈覆盖索引、延迟关联与游标分页3.1 覆盖索引让分页查询不碰数据行第一种优化思路是尽量少回表极致的做法是完全不回表这就是覆盖索引。所谓覆盖索引是指查询所需的全部列都在某一个索引中MySQL 可以直接通过索引完成查询无需回表读取数据行。例如你的查询只需要id和create_time两个字段SELECT id, create_time FROM t_order ORDER BY create_time DESC LIMIT 10 OFFSET 100000;如果存在一个复合索引(create_time, id)那么索引本身就包含了这两个字段MySQL 扫描索引就能拿到全部数据一次回表都不会发生。因为二级索引的叶子节点天然带有主键值所以凡是SELECT 主键 索引列这种查询都有机会命中覆盖索引。但它的局限也很明显一旦你SELECT *或者需要索引之外的字段比如order_no、amount覆盖索引就失效了MySQL 还是得回表取整行。所以在真实业务里纯靠覆盖索引解决深分页问题的情况并不多它通常作为辅助手段配合下一种方案一起使用。3.2 延迟关联小范围的精确回表延迟关联Deferred Join的思路是先在索引上完成跳过前 N 条的定位操作只取出这一页对应的主键然后再用主键回表去查完整数据。因为回表的次数从前 N 条全部回表变成了只回表当前页这 10 条成本瞬间降了三个数量级。SELECT o.* FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 10 OFFSET 100000 ) tmp ON o.id tmp.id ORDER BY o.create_time DESC;这段 SQL 的关键在子查询它只查id配合(create_time, id)复合索引就能以覆盖索引的方式完成排序和偏移定位完全不需要回表。子查询拿到 10 个主键后外层再用主键 join 回原表这里最多触发 10 次主键回表而且是eq_ref级别的快速查找。需要注意两个细节第一外层查询一定要保留ORDER BY因为 join 之后结果的物理顺序不受子查询顺序影响不重新排序的话返回的数据顺序是乱的第二验证优化的效果时务必用EXPLAIN查看子查询是否真的走了覆盖索引Extra里没有Using filesort和Using temporary才说明排序是在索引上完成的。这套方案在需要保留跳页能力但数据量又很大的场景下非常实用。它没有改变分页的交互方式只是把实现成本大幅降低。不过在偏移量极大的极端场景下索引扫描本身仍然要遍历前 N 条索引记录耗时还是会缓慢增长只是比原来快得多。3.3 游标分页彻底摆脱 OFFSET如果你的业务是加载更多或无限滚动这类交互不需要用户直接跳转到第 N 页那么最推荐的是游标分页也叫 keyset pagination。它的思路是完全不用OFFSET而是基于排序字段的边界值来定位下一页的数据。假设当前页的最后一条数据是create_time 2024-05-20 10:30:00id 10086那么下一页的查询是这样的SELECT id, create_time, order_no, amount FROM t_order WHERE (create_time, id) (2024-05-20 10:30:00, 10086) ORDER BY create_time DESC, id DESC LIMIT 10;这里的关键是元组比较(create_time, id) (2024-05-20 10:30:00, 10086)。MySQL 支持行构造器的字典序比较它等价于先比较 create_time如果相等再比较 id正好和我们的排序规则ORDER BY create_time DESC, id DESC一一对应。只要这条查询能命中(create_time, id)复合索引MySQL 就能直接从索引定位到边界值的位置向后扫 10 条即可完全不需要跳过前面 N 条的冗余工作。游标分页的优势非常明显性能恒定无论翻了 1 页还是 10 万页每次查询都只扫 10 条索引记录耗时基本不变。数据一致性好如果在翻页过程中有新数据插入使用 OFFSET 分页时新数据会把后续所有页挤得偏移一位导致用户看到重复数据游标分页则完全不受影响因为它是基于上次看到的最后一条数据的绝对位置继续向下走的。它的局限是不支持随机跳页。用户没法直接输入第 100 页跳转过去只能一页一页往下翻。所以适合信息流、商品推荐、操作日志这类一直往下刷的场景不适合管理后台那种需要跳页操作数据的场景。3.4 三种方案怎么选把三套方案的适用场景整理成了一张表方便直接对照方案适合场景优势注意点普通LIMIT/OFFSET数据量小、后台管理、内部分页写法简单支持任意跳页数据量大后深分页性能差覆盖索引 延迟关联数据量大但仍需跳页大幅降低回表成本保留跳页能力SQL 更复杂需验证执行计划游标分页无限滚动、加载更多、C 端列表性能恒定翻页结果稳定不支持跳页需要前端配合我的习惯做法是线上核心列表接口无脑优先考虑游标分页管理后台因为确实需要跳页数据量又通常可控用延迟关联方案最稳。至于裸LIMIT/OFFSET在数据量超过几十万之后就尽量不要出现在对外的核心接口里了这算是我踩过深分页性能坑之后总结出来的硬性标准。4. 翻页数据错乱排序稳定性与字符集排序规则4.1 排序字段不唯一重复和丢失数据的元凶这一节聊一个非常隐蔽但后果严重的问题翻页翻着翻着发现某条数据在第一页见过翻到第三页又出现了或者明明总条数没变某条数据却人间蒸发。遇到这种情况十有八九是排序字段不唯一导致的。原理不复杂。假设你执行的是ORDER BY create_time DESC而表里有很多订单的create_time完全相同。MySQL 只保证这些 create_time 相同的行依然按 create_time 排序但它们在彼此之间的先后顺序是不确定的。第一次查第一页时某个 created_time 相同的行排在前面由于数据分布、索引页缓存状态的变化第二次查第二页时这个顺序可能就变了于是它就可能从第一页漏到第二页或者反过来。解决方案也很简单排序字段里必须包含一个唯一字段通常是主键 id。把ORDER BY create_time DESC改成ORDER BY create_time DESC, id DESC因为 id 全局唯一整个排序序列就唯一确定了每一行都有且只有一个固定位置翻页永远不会乱。这是我在做订单列表时强制要求团队遵守的一条规范。4.2 复合排序与索引方向让排序真正走索引既然排序从单字段变成了多字段索引也得跟着调整。很多人只建了(create_time)单列索引然后对着ORDER BY create_time DESC, id DESC的慢查询发愁——把执行计划拉出来一看Extra里赫然写着Using filesort因为索引顺序和排序要求对不上MySQL 只能把数据捞出来重新排序。正确的做法是建立与排序规则匹配的复合索引ALTER TABLE t_order ADD INDEX idx_create_time_id (create_time DESC, id DESC);MySQL 8.0 开始支持真正的降序索引索引可以按降序存储。这样ORDER BY create_time DESC, id DESC就能完全在索引上顺序扫描完成既不需要临时文件排序也不需要回表如果查询字段都在索引内。这里有个小坑想提醒一下MySQL 5.7 及之前的版本你在建索引时写DESC关键字它也会接受但实际上会忽略它索引仍然是升序的。所以如果你用的是 5.7想支持时间倒序 id 倒序的排序通常要反过来建升序索引(create_time, id)然后让 MySQL 反向扫描索引也能达到同样的效果只是反向扫描对优化器的选择更挑剔一些。升级到 8.0 之后这些问题就省心很多。4.3 字符集与排序规则一个容易忽略的隐性变量还有一个比排序字段更隐蔽的问题字符集的排序规则collation。MySQL 8.0 默认的utf8mb4_0900_ai_ci、老版本常见的utf8mb4_general_ci、以及utf8mb4_unicode_ci三种排序规则对字符串的比较结果并不完全一致。如果你的分页查询ORDER BY的是一个 varchar 字段那么排序规则直接决定这列数据的排列顺序。这意味着两件事。第一表结构一旦定了排序规则就不要随便改。假设你在中将utf8mb4_general_ci改成utf8mb4_unicode_ci某些字符的相对顺序会变化已发布的分页接口会立刻出现数据重排现象用户感受到的就是翻页混乱。第二联表查询时排序规则必须一致否则 MySQL 直接报ERROR 1267: Illegal mix of collations错误这是很多人写分页关联查询时莫名其妙踩到的坑。另外想多说一句对于中文排序MySQL 默认的排序规则基本是按 Unicode 码点或拼音映射来排的如果你业务上需要按笔画排序或者按特定业务规则排序靠默认 collation 是搞不定的。这类需求我会建议在表里单独存一个排序列由业务层在写入时算好值而不是指望 MySQL 的字符集规则来解决。5. COUNT(*) 统计与接口层的分页设计5.1 两条 SQL 完成的分页COUNT 才是隐形杀手传统的分页接口通常是两条 SQL先SELECT COUNT(*)统计总数再执行分页查询取当前页数据。第一条 SQL 才是真正的隐形杀手。InnoDB 不像 MyISAM 那样维护一个独立的行数计数器SELECT COUNT(*)必须扫描索引或数据页来统计表越大越慢。在千万级大表上即使COUNT(*)走了最小的二级索引一次统计也可能耗时数百毫秒比分页查询本身还贵。好消息是COUNT(*)的优化手段很多必须搭配WHERE不能只 count 全表否则数据规模一大必然扛不住。小索引优先COUNT(*)不关心你 select 了什么列它会选择最小的索引来扫描所以如果你有(id)这种小索引统计成本相对可控。不需要精确总数时可以用information_schema.tables的近似行数TABLE_ROWS误差在可接受范围内但只适合展示用的统计。典型的加载更多场景完全可以弃用 COUNT直接查出LIMIT pageSize 1条如果能取到第pageSize 1条说明还有下一页返回给前端的has_more字段就为 true然后把多取的那条丢掉。这是我个人在 C 端列表接口里最常用的方案省一次大查询接口响应明显变快。5.2 接口参数设计OFFSET 风格还是 Cursor 风格接口层面的分页参数常见有三套设计风格我根据自己的实践对比一下风格请求参数示例优点缺点page / pageSize?page1page_size20语义直观便于跳页后端要换算 OFFSET深层页数不友好offset / limit?offset20limit20和 SQL 直译便于联调容易有人传超大 offsetcursor / limit?cursoreyJ0aW1lIjoi...limit20性能恒定隐私友好不支持跳页符号化实现略复杂这三套我目前最推荐的是 cursor 风格尤其在 C 端产品里。它的一个额外好处是cursor经过编码和签名用户无法通过改参数直接跳去第 999 页这对防止有人恶意深翻数据DDoS 式拖库有一定的缓解作用。cursor本质就是把上一页最后一条数据的排序字段值和主键值打包签名下页查询时由后端解出来拼到WHERE条件里正好对应第 3.3 节的游标分页。不管用哪种风格接口层都该做这几件防御给limit设上限比如最多 100 条给cursor做签名校验防止篡改对过期的cursor给出明确错误码而不是让 SQL 直接报错。这些细节决定了分页接口在异常情况下是优雅降级还是直接 500。5.3 分页期间的并发写入一致性怎么保证最后聊一个很多人不会主动想、但线上一定遇到过的场景用户在分页浏览时后台同时有新订单插入或旧订单被删除。这时你会发现用 OFFSET 分页的接口翻页时出现前一条数据在下一页重复出现或者某条数据凭空跳过这不一定是代码 bug而是并发写入导致分页游标失效的必然结果。原因在于OFFSET 是一个相对位置概念它依赖前面的数据行数保持不变。新插入一行在排序序列的前部后面的行整体后移一位原来的第 N 页位置就包含了之前第 N-1 页的内容对用户来说就是重复。删除操作同理会造成跳行。如何缓解分几种情况讨论允许一定程度的不一致大多数内容列表场景直接用游标分页新插入的数据只影响未来的翻页结果已经翻过的页不会重现或丢失用户在交互上几乎无感知。这是游标分页除了性能之外最大的价值。严格要求一致性比如对账、后台审核列表用快照读即在事务里读取并确保隔离级别下查询使用的是同一时间点的快照。InnoDB 的 MVCC 能让一个事务内的多次查询基于同一个快照翻页就不会受其他会话的写入影响。接受跳页但保证不错乱管理后台场景数据量通常不大只要排序稳定加上主键兜底并发写入带来的问题在多数情况下可以接受。我个人在这块的底线是对外提供给 C 端用户看的分页列表一律游标分页对内部运营和后端需要跳页操作的列表用延迟关联方案但要对数据量设上限到达阈值就提醒运营人员按筛选条件缩小范围。这样既保证了性能又把并发写造成的数据错乱控制在可接受范围内。最后分享一个实际排查经验。如果你的分页接口变慢了不要一上来就怀疑 MySQL 配置或者服务器负载先拿一条线上真实的分页 SQL一步步做三件事用EXPLAIN看有没有走索引、有没有 filesort看OFFSET是不是已经大到离谱然后问自己这个场景到底需不需要跳页。多数情况下问题出在这三个问题之一上面答案也顺手就有了。分页这件事真的不只是LIMIT后面跟两个数字那么简单。