从零起步学习MySQL 第十二章：MySQL分页性能如何优化？

前言今天在看学长面经时发现了这样一个问题字节三面——MySQL分页性能如何优化作为后端开发工程师分页是我们日常开发中高频接触的需求小到后台管理系统的列表查询大到百万级商品库的分页展示看似简单的 LIMIT 语句在数据量激增后很容易成为系统性能瓶颈。所以今天我们就来系统地讲解 MySQL 分页pagination在大数据量下常见的性能问题深入分析背后的执行机制用真实场景举例说明为什么 LIMIT OFFSET 会变慢并给出多种实用的优化方案与实践建议含完整 SQL 示例、优缺点详细比较与工程化落地注意事项。本文适合后端开发工程师、数据库工程师以及想要深入理解 MySQL 性能优化的读者阅读全程干货建议收藏备用。一、什么是分页为什么要分页在 Web 应用中分页是将大结果集分成若干页逐步返回给客户端的常见需求最典型的场景就是商品列表、搜索结果、用户日志浏览、后台数据查询等。比如我们在电商平台搜索“手机”会看到“第1页、第2页...”的分页控件点击后只加载当前页的10-20条数据这就是分页的应用。MySQL 中最典型的分页 SQL 表达式为SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000;一键获取完整项目代码分页的核心价值在于“按需加载”一方面可以节省网络带宽避免一次性返回几十万、几百万条数据导致的网络拥堵另一方面可以降低前端渲染成本减少页面卡顿提升用户体验。但很多开发者在使用分页时只关注“实现功能”却忽略了性能问题不当的分页实现会给数据库带来巨大压力甚至拖垮整个系统。二、MySQL 常见分页实现与执行成本日常开发中我们最常用的分页写法就是 LIMIT offset, count或 LIMIT count OFFSET offset比如“LIMIT 10 OFFSET 1000”表示跳过前1000条数据返回接下来的10条。看似简单的语句底层执行过程却远比我们想象的复杂这里给大家拆解简化版的执行流程根据 SQL 中的 ORDER BY 规则扫描或读取符合条件的行如果排序字段有合适的索引就按索引顺序读取如果没有索引就会进行全表扫描如果无法利用索引完成排序比如排序字段没有索引、多字段排序且未建立联合索引MySQL 会创建临时表将读取到的行放入临时表中进行排序极端情况下还会产生磁盘临时文件当临时表数据量超过阈值时读取 offset count 条结果然后丢弃前 offset 条数据只返回剩下的 count 条数据给客户端。这里有一个关键结论当 offset 很大时数据库需要扫描或读取大量不需要返回的行导致 I/O 开销、排序开销和 CPU 开销急剧上升。offset 越大这种浪费就越严重分页性能就越差。举例说明真实场景还原假设我们有一张 orders 订单表表中共有 200 万行数据主键 id 是自增的现在需要查询第 200000 页每页10条数据对应的 SQL 如下SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1999990;一键获取完整项目代码从逻辑上看这条 SQL 只需要返回10条数据但 MySQL 的执行过程是先扫描并读取前 1999990 10 2000000 条数据然后丢弃前 1999990 条只返回最后10条。这就意味着我们为了获取10条有用的数据让数据库做了200万条数据的扫描、排序和筛选操作这是极大的资源浪费在高并发场景下这样的 SQL 很容易成为慢查询拖慢整个数据库的响应速度。三、具体性能问题与触发条件结合上面的执行过程我们可以总结出 MySQL 分页常见的4个性能问题以及对应的触发条件帮大家快速定位自己项目中的隐患1. 大 OFFSET 导致大量扫描/读取这是最常见的问题也是分页性能差的核心原因。越靠后的页offset 越大数据库需要扫描的行数就越多I/O 和 CPU 开销就越大。比如第1页OFFSET 0只需要扫描10条第1000页OFFSET 9990需要扫描10000条第100000页OFFSET 999990需要扫描1000000条性能差距呈指数级扩大。2. 无索引的排序如果 ORDER BY 后的字段没有建立合适的索引MySQL 就无法利用索引排序只能进行“文件排序”Using filesort此时会创建临时表将数据写入临时表后再排序。临时表如果过大会写入磁盘导致磁盘 I/O 激增排序速度大幅下降。触发条件排序字段无索引、多字段排序未建立联合索引、索引失效如使用函数、隐式转换。3. SELECT * 导致回表非覆盖索引很多开发者习惯用 SELECT * 查询所有字段但如果查询的字段不在索引中MySQL 会先通过索引找到对应的主键 id再根据主键 id 去主键索引中读取完整的行数据这个过程就是“回表”。回表会产生大量的随机 I/O尤其是在大数据量分页场景下回表次数越多性能越差。触发条件查询字段未完全包含在索引中非覆盖索引、使用 SELECT *。4. 并发场景下的读不一致性与重复/漏数据基于页码的分页LIMIT OFFSET在数据频繁变更插入、删除、更新的场景下很容易出现数据重复或漏数据的问题。比如用户正在浏览第2页此时有一条新数据插入到第1页那么原来第2页的第一条数据就会变成第1页的最后一条用户再次刷新第2页时就会漏掉这条数据反之如果删除了第1页的一条数据第2页的第一条数据会重复出现。触发条件高并发写入、数据频繁变更、使用页码式分页。四、常见优化方案按实用性排序附实战示例针对上面的性能问题我整理了6种常见的优化方案按实用性和落地难度排序每一种都附上完整的 SQL 示例、优缺点分析和适用场景方便大家直接套用。1. 基于游标的分页Keyset Pagination—— 首选方案思路放弃使用 OFFSET而是根据排序键值通常是自增主键、时间戳或有序索引列记录当前页的边界值比如上一页最后一条数据的 id然后通过 WHERE 条件过滤掉边界之前的数据再用 LIMIT 限制返回条数。这种方式本质是“基于位置的分页”而非“基于页码的分页”。实战 SQL 示例以 orders 表为例按 id 升序分页-- 第 1 页无边界值直接取前10条SELECT id, order_no, create_time, status FROM orders WHERE status OPEN ORDER BY id ASC LIMIT 10;-- 第 2 页假设上一页最后一条数据的 id 1023过滤掉 id ≤ 1023 的数据SELECT id, order_no, create_time, status FROM orders WHERE status OPEN AND id 1023 ORDER BY id ASC LIMIT 10;-- 第 3 页上一页最后一条 id 1033SELECT id, order_no, create_time, status FROM orders WHERE status OPEN AND id 1033 ORDER BY id ASC LIMIT 10;SELECT id, order_no, create_time, status FROM orders WHERE status OPEN AND id 1023 ORDER BY id ASC LIMIT 10;优点性能稳定无论分页到第几页读取的数据量都与返回量成正比只读取当前页的10条数据不会出现大 offset 导致的性能骤降适合深度分页比如分页到10000页以后避免回表如果查询字段包含在排序索引中比如索引是 (status, id, create_time)可以实现覆盖索引无需回表进一步提升性能数据一致性在并发写入场景下只要排序键是唯一且有序的如自增主键就不会出现重复或漏数据的问题。缺点不支持随机跳页无法直接实现“跳到第1000页”的功能只能通过“上一页、下一页”的方式逐步翻页适合“加载更多”“无限滚动”的场景依赖有序排序键必须有唯一、有序的排序字段如自增主键、时间戳id如果排序字段是无序的如随机字符串则无法使用边界一致性需注意如果排序键是时间戳可能重复需要结合主键 id 一起作为边界条件如 WHERE create_time 2025-11-14 AND id 1023避免重复数据。适用场景无限滚动列表如朋友圈、商品列表、深度分页场景、数据变更频繁但需保证一致性的场景。2. 子查询定位起点定位 扩展—— 兼容跳页场景思路如果业务必须支持随机跳页比如后台管理系统的分页控件需要支持“跳到第N页”可以用子查询先定位到第 offset 条记录的排序键如 id然后用 WHERE 条件过滤掉排序键小于该值的数据再用 LIMIT 返回当前页的条数。这种方式可以避免一次性扫描 offset count 条数据只需要扫描 offset 条数据定位起点再扫描 count 条数据返回结果。实战 SQL 示例查询第10001页每页10条offset 100000SELECT id, order_no, create_time, status FROM orders WHERE id ( SELECT id FROM orders WHERE status OPEN ORDER BY id ASC LIMIT 100000, 1 ) ORDER BY id ASC LIMIT 10;优化点子查询只查询排序键 id索引列无需回表定位速度更快主查询根据 id 过滤只扫描当前页的10条数据大幅减少扫描量。优点支持随机跳页兼容传统分页控件的“跳页”功能无需修改前端交互性能优于直接 OFFSET避免一次性扫描 offset count 条数据尤其是在大 offset 场景下性能提升明显实现简单在原有分页 SQL 基础上修改即可无需大幅调整业务逻辑。缺点子查询仍需扫描 offset 条数据如果 offset 极大如100万子查询扫描100万条数据仍会有一定开销但比直接 OFFSET 更优依赖排序键索引子查询中的排序字段必须有索引否则子查询会进行全表扫描性能反而更差并发场景仍有一致性问题本质还是基于页码的分页数据频繁变更时可能出现重复或漏数据。适用场景后台管理系统、需要支持随机跳页的场景、offset 不是特别大如10万以内的场景。3. 覆盖索引Covering Index—— 减少回表开销思路覆盖索引是指查询的所有字段SELECT 后的字段都包含在索引中此时 MySQL 无需回表直接从索引中读取数据即可大幅减少随机 I/O 开销。结合分页场景我们可以创建包含“过滤条件排序字段查询字段”的联合索引让分页查询成为覆盖索引查询。实战 SQL 示例查询 status 为 OPEN 的订单分页展示 id、order_no、create_time-- 第一步创建覆盖索引包含过滤条件 status、排序字段 id、查询字段 order_no、create_timeCREATE INDEX idx_status_id_order_create ON orders(status, id, order_no, create_time);-- 第二步分页查询无需回表直接从索引中读取数据SELECT id, order_no, create_time FROM orders WHERE statusOPEN ORDER BY id ASC LIMIT 10 OFFSET 100000;一键获取完整项目代码关键说明创建覆盖索引时要遵循“最左前缀原则”将过滤条件status放在最前面排序字段id放在中间查询字段order_no、create_time放在最后确保索引能被有效利用。优点大幅提升性能避免回表操作减少随机 I/O尤其是在大数据量分页场景下性能提升明显兼顾排序性能索引本身是有序的无需额外排序避免文件排序和临时表的产生适用范围广可结合 OFFSET 分页或游标分页使用兼容性强。缺点索引维护成本高索引越多写入操作INSERT、UPDATE、DELETE的开销越大因为每次写入都需要更新索引无法覆盖所有字段如果查询字段较多如10个以上创建包含所有字段的覆盖索引会导致索引体积过大反而影响性能索引选择需谨慎需根据实际查询场景设计索引避免创建无用索引。适用场景查询字段固定且较少、分页查询频繁、写入频率不高的场景如商品列表、用户列表。4. 物化分页缓存/预计算—— 热点页优化思路对于热点分页如前10页用户访问频率极高我们可以将这些页面的查询结果缓存起来如 Redis、本地缓存用户再次访问时直接从缓存中获取数据无需查询数据库。对于静态数据或变化不频繁的数据如历史订单、新闻列表还可以预计算分页结果存储在缓存或数据库中定期更新。实战方案示例Redis 缓存热点页缓存 key 设计page:orders:status:OPEN:1表示 status 为 OPEN 的订单第1页缓存 value将分页查询结果10条数据序列化为 JSON 格式存储缓存失效策略设置合理的过期时间如10分钟或在数据变更时主动删除对应缓存如新增、删除订单时删除对应状态的所有热点页缓存降级策略如果缓存失效或缓存穿透直接查询数据库并重新缓存结果。优点性能极高缓存访问速度远快于数据库查询能大幅降低数据库压力提升接口响应速度从毫秒级优化到微秒级降低数据库负载热点页的访问无需查询数据库减少数据库的 I/O 和 CPU 开销实现简单借助 Redis 等缓存工具开发成本低易于落地。缺点缓存一致性问题数据变更后缓存可能与数据库不一致需要设计合理的失效策略增加了开发复杂度不适合冷页对于访问频率极低的冷页如第1000页以后缓存没有意义反而浪费缓存空间缓存维护成本需要处理缓存失效、缓存穿透、缓存击穿等问题。适用场景热点页前10-20页、静态数据、变化不频繁的数据、高并发访问场景。5. 分区表 / 分表策略 —— 大数据量终极优化思路当单表数据量达到千万级、亿级时即使使用上述优化方案分页性能也会受到限制。此时可以采用分区表或分表策略将大表拆分成多个小表减少单次查询需要扫描的数据量间接提升分页性能。常见方案分区表根据时间、主键范围等维度将单表分成多个分区如 orders 表按 create_time 分区每个分区存储一个月的数据分页查询时只需要扫描对应分区的数据无需扫描全表水平分表将单表按主键哈希、用户 ID 哈希等维度拆分成多个结构相同的小表如 orders_1、orders_2、orders_3分页查询时根据分表规则定位到具体的小表再进行分页垂直分表将表中不常用的字段拆分到另一张表如将 orders 表中的备注、附件等字段拆分到 orders_ext 表减少主表的数据量提升查询速度。优点大幅提升查询性能将大表拆分成小表单次查询扫描的数据量大幅减少分页性能显著提升便于维护单个小表的数据量小备份、恢复、扩容更方便支持更大的数据量突破单表数据量限制适合亿级、十亿级数据场景。缺点开发复杂度高需要设计分表/分区规则修改查询、写入逻辑适配分表场景跨分区/跨表查询复杂如果分页查询需要跨多个分区/小表需要额外处理数据聚合、排序等问题维护成本高需要管理多个小表/分区监控每个分区的状态处理分区扩容、数据迁移等问题。适用场景单表数据量千万级以上、分页查询频繁、性能要求极高的场景如电商订单表、用户行为日志表。6. 使用全文搜索 / 专门的搜索引擎 —— 复杂场景优化思路如果分页场景涉及复杂的搜索条件如多字段模糊查询、多字段权重排序MySQL 的分页查询性能会很差此时可以将检索功能交给专门的搜索引擎如 Elasticsearch、Solr数据库仅负责存储原始数据分页查询时先从搜索引擎中获取符合条件的主键 id再到数据库中回表查询完整数据或直接从搜索引擎中返回所需字段。实战流程示例将 orders 表中的数据同步到 Elasticsearch 中建立合适的索引如分词索引、权重索引分页查询时先向 Elasticsearch 发送查询请求获取当前页的主键 id 列表如10条 id根据 id 列表到 MySQL 中查询完整的订单数据返回给客户端如果查询字段可以在 Elasticsearch 中完全获取可直接从 Elasticsearch 返回数据无需回表。优点支持复杂查询能高效处理多字段模糊查询、权重排序、地理位置查询等复杂场景性能远优于 MySQL分页性能稳定搜索引擎专门针对检索和分页优化即使大数据量、复杂条件下分页性能也能保持稳定减轻数据库压力将复杂的检索和分页操作转移到搜索引擎数据库只负责存储和简单查询。缺点架构复杂度高需要引入搜索引擎处理数据同步MySQL 到 Elasticsearch、索引维护等问题开发成本高需要学习搜索引擎的使用修改查询逻辑适配双存储架构数据一致性问题MySQL 与 Elasticsearch 之间的数据同步存在延迟可能出现数据不一致的情况。适用场景复杂搜索分页场景如电商商品搜索、新闻搜索、多字段权重排序场景、大数据量检索场景。五、工程实践建议与注意事项优化方案的选择没有绝对的好坏只有是否适合当前业务场景。结合实际开发经验给大家以下6条工程实践建议帮大家避坑落地优化方案1. 优先使用 Keyset 分页如果业务场景是“按时间线、按主键顺序翻页”如朋友圈、商品列表并且不需要随机跳页优先选择基于游标的 Keyset 分页。这种方案性能最稳定落地成本低还能避免并发场景下的数据一致性问题。2. 不要 SELECT *只选必要字段尽量避免使用 SELECT * 查询所有字段只选择业务需要的字段这样更容易实现覆盖索引减少回表开销。比如查询订单列表只需要查询 id、order_no、create_time、status 即可无需查询备注、附件等不常用字段。3. 为 ORDER BY 字段建索引分页查询几乎都会用到 ORDER BY 排序一定要为排序字段建立合适的索引避免触发文件排序和临时表。如果是多字段排序要建立联合索引并且遵循最左前缀原则。4. 使用 EXPLAIN 分析执行计划优化分页 SQL 前一定要用 EXPLAIN 分析执行计划检测 SQL 是否走索引、是否使用临时表、是否触发文件排序。重点关注 type访问类型如 ref、range、ALL、rows扫描行数、Extra额外信息如 Using filesort、Using temporary、Using index。示例EXPLAIN SELECT id, order_no FROM orders WHERE statusOPEN ORDER BY id LIMIT 10000, 10;一键获取完整项目代码如果 Extra 中出现 Using filesort说明没有利用索引排序需要优化索引如果出现 Using temporary说明需要创建临时表性能较差如果出现 Using index说明使用了覆盖索引性能最优。5. 监控慢查询与对热点页做缓存开启 MySQL 的慢查询日志监控分页相关的慢查询及时发现性能问题。对于热点页前10-20页一定要做缓存如 Redis这是最简单、最有效的短期优化方案能快速降低数据库压力。6. 注意并发写入下的数据一致性如果业务场景中数据频繁变更插入、删除、更新使用 Keyset 分页时建议结合时间戳或快照机制避免重复或漏数据。比如用 (id, create_time) 作为边界条件确保即使有数据插入也能准确定位下一页的起点。补充示例结合时间戳的 Keyset 分页-- 上一页最后一条数据id1023create_time2025-11-14 10:00:00SELECT id, order_no, create_time, status FROM ordersWHERE statusOPEN AND (create_time 2025-11-14 10:00:00 OR (create_time 2025-11-14 10:00:00 AND id 1023))ORDER BY create_time ASC, id ASC LIMIT 10;一键获取完整项目代码7. 考虑用户体验优化分页交互真正的用户通常不会浏览到极深的页面比如第100页以后建议将传统的“页码分页”改为“加载更多”或“无限滚动”结合 Keyset 分页实现既提升性能又优化用户体验。同时SELECT id, order_no, create_time, status FROM orders WHERE statusOPEN AND (create_time 2025-11-14 10:00:00 OR (create_time 2025-11-14 10:00:00 AND id 1023)) ORDER BY create_time ASC, id ASC LIMIT 10;可以限制最大分页页数如最多显示100页引导用户使用搜索功能快速定位数据。六、常见误区在分页性能优化过程中很多开发者会陷入以下3个误区大家一定要避开误区1误以为 OFFSET 是常数成本很多人觉得“LIMIT 10 OFFSET 1000”和“LIMIT 10 OFFSET 0”的性能差不多都是返回10条数据。但实际上OFFSET 越大数据库扫描的行数越多性能成本呈线性增长offset 达到10万、100万时性能会急剧下降。误区2仅靠增加索引就能解决一切索引确实能提升分页性能但无法消除“扫描 offset 条数据”的本质问题除非采用 Keyset 分页。如果 offset 极大即使有索引子查询或直接 OFFSET 仍会扫描大量数据性能依然很差。同时过多的索引会增加写入成本反而影响整体性能。误区3忽视并发导致的数据重复/漏失很多开发者只关注分页性能却忽略了并发场景下的数据一致性问题。基于页码的分页LIMIT OFFSET在数据频繁变更时很容易出现重复或漏数据的问题尤其是在高并发场景下这个问题会更加明显需要结合 Keyset 分页或快照机制解决。七、示例对比伪基准测试为了让大家更直观地感受到不同优化方案的性能差异这里做一个伪基准测试相同硬件环境、相同数据分布orders 表200万行id 为主键索引status 字段有联合索引分页方式SQL 示例扫描行数响应时间伪数据性能评价普通 OFFSET 分页LIMIT 10 OFFSET 19999902000000行500ms极差不适合深度分页子查询定位起点WHERE id (SELECT id LIMIT 1999990,1) LIMIT 101999991行子查询10行主查询150ms一般适合需要跳页的场景Keyset 分页WHERE id 1999990 LIMIT 1010行10ms优秀适合深度分页覆盖索引分页SELECT id,status FROM orders LIMIT 10 OFFSET 19999902000000行索引扫描80ms良好适合查询字段较少的场景缓存分页热点页从 Redis 读取第200000页数据0行数据库1ms极佳适合热点页注意以上数据为伪基准测试结果具体数值依赖机器配置、存储类型、索引设计和数据分布实际项目中需以 EXPLAIN 分析和真实压测为准。八、结论MySQL 分页性能优化的核心是“减少不必要的扫描和 I/O 开销”不同场景下的优化方案取舍需结合业务需求、数据量和性能要求综合判断对于大多数需要深度分页、无需随机跳页的场景如无限滚动列表Keyset 分页基于游标是首选性能稳定、落地简单还能保证数据一致性如果业务必须支持“跳页”或随机访问任一页如后台管理系统可以结合子查询定位起点或采用缓存/物化方法兼顾性能和交互体验对于查询字段固定、写入频率不高的场景覆盖索引是性价比很高的优化方案能大幅减少回表开销当单表数据量达到千万级以上时需考虑分区表/分表策略突破单表性能限制对于复杂搜索分页场景建议引入专门的搜索引擎将检索和分页压力转移提升整体性能。最后分页优化不是一蹴而就的需要结合实际业务场景通过 EXPLAIN 分析执行计划、监控慢查询、进行真实压测不断调整优化方案才能达到最佳的性能效果。希望本文能帮助大家解决 MySQL 分页性能问题顺利应对面试中的相关提问也能应用到实际项目中提升系统性能。