PDF大白话说Java面试题 — 03-Mysql篇第28题LIMIT 1000000加载很慢的话你是怎么解决的呢回答核心考点深分页问题Deep Pagination是指当偏移量OFFSET很大时MySQL 查询性能会断崖式下降。例如LIMIT 1000000, 10需要扫描前 100 万条记录并丢弃才能返回目标 10 条数据。这是互联网大厂面试中高频考察的性能调优场景面试官真正想考察的是你是否理解 MySQL 的索引扫描与回表机制以及能否给出可落地的生产级优化方案。1. 问题根因分析1.1 执行机制剖析MySQL 的LIMIT offset, N并非直接跳转到第offset行而是必须从头顺序扫描offset N条记录然后丢弃前offset行仅保留最后N行返回给客户端 [citation:1]。当查询依赖二级索引且不满足覆盖索引时MySQL 需要对前offset条记录执行大量毫无意义的回表查询产生海量随机 I/O最后再将这些辛苦查出的数据全部丢弃 [citation:1]。1.2 优化器的选择困境MySQL 查询优化器采用基于成本的策略。当offset过大时优化器可能认为全表扫描的成本低于索引扫描 回表从而放弃使用索引退化为全表扫描 [citation:1]。这意味着即使有索引深分页也可能不走索引。1.3 性能衰减模型偏移量扫描行数预估耗时示例LIMIT 0, 1010~6msLIMIT 100000, 10100010~742ms [citation:8]LIMIT 1000000, 101000010数秒甚至超时可以看到耗时随offset线性增长且斜率极大。1.4 示例代码与执行计划分析-- 原始慢查询SELECT*FROMusersORDERBYidLIMIT1000000,10;使用EXPLAIN分析type: ALL全表扫描rows: 1000010Extra: Using filesort这说明 MySQL 扫描了 100 万 行数据并在 Server 层进行排序后丢弃了前 100 万行仅返回 10 行。大量 I/O、CPU 排序和内存消耗都被浪费了 [citation:8]。2. 生产级优化方案2.1 方案一基于主键范围查询标签记录法 / 游标分页核心思路放弃OFFSET改用WHERE id last_id直接定位利用主键索引的有序性将扫描行数恒定在LIMIT大小。适用条件主键为连续自增整数或具有单调递增特性的字段。示例代码-- 原始查询慢SELECT*FROMusersORDERBYidLIMIT1000000,10;-- 优化后快SELECT*FROMusersWHEREid1000000ORDERBYidLIMIT10;执行计划对比方案typerowsExtra原始ALL1000010Using filesort优化range10Using where优势扫描行数恒定为LIMIT值性能与页码深度无关千万级数据下仍可保持毫秒级响应 [citation:7]。局限性不支持随机跳页只能顺序翻页若主键不连续如存在大量删除可能跳过数据或返回空页。2.2 方案二基于游标分页Cursor Pagination核心思路记录上一页最后一条记录的主键值或排序字段值作为下一页查询的起点。示例代码-- 第一次查询SELECT*FROMusersORDERBYidLIMIT10;-- 假设最后一条 id 1000-- 第二次查询传入 last_id 1000SELECT*FROMusersWHEREid1000ORDERBYidLIMIT10;-- 第三次查询传入 last_id 1010SELECT*FROMusersWHEREid1010ORDERBYidLIMIT10;边界处理——排序字段有重复值时若排序字段如create_time存在重复仅用WHERE create_time 2025-10-01可能漏数据。正确做法是使用(排序字段, 主键)二元组作为游标并建立联合索引 [citation:0]。-- 上一页最后一条create_time 2025-10-01, id 50000SELECT*FROMusersWHERE(create_time2025-10-01)OR(create_time2025-10-01ANDid50000)ORDERBYcreate_timeDESC,idDESCLIMIT10;索引要求必须建立(create_time, id)联合索引否则无法高效执行 [citation:0]。适用场景移动端无限滚动列表、后台管理系统列表翻页、消息流等不需要跳页的场景 [citation:4]。2.3 方案三延迟关联Deferred Join核心思路先通过子查询在索引上完成分页仅获取目标主键 ID再通过INNER JOIN回表取完整数据。将回表次数从offset N次降至N次 [citation:0]。示例代码-- 原始查询慢SELECT*FROMordersORDERBYcreate_timeDESCLIMIT100000,20;-- 延迟关联优化SELECT*FROMordersINNERJOIN(SELECTidFROMordersORDERBYcreate_timeDESCLIMIT100000,20)AStmpONorders.idtmp.id;为什么快子查询只查id如果create_time有索引子查询走覆盖索引扫描Extra: Using index不需要回表。拿到 20 个id后再回表取 20 次完整行数据。原 SQL 需要回表 100020 次优化后仅需回表 20 次 [citation:0]。效果对比来自真实压测数据方案预估扫描行数回表次数耗时传统深分页5044050440 次8s延迟关联30020索引 20主表仅 20 次~0.034s [citation:8]适用场景后台分页报表、搜索列表等必须支持跳页、又有深分页压力的业务 [citation:0]。注意事项ORDER BY字段必须有索引否则子查询本身就要filesort优化效果大打折扣。数据量不够大或行宽较小时优化效果不明显——因为瓶颈根本不在 I/O 上 [citation:0]。2.4 方案四子查询优化先查主键再过滤核心思路与延迟关联类似但使用子查询而非 JOIN先定位起始主键值再范围查询。示例代码-- 先查出第 1000000 条记录的主键SELECTidFROMusersORDERBYidLIMIT1000000,1;-- 假设返回 id 1000001-- 再基于主键范围查询SELECT*FROMusersWHEREid1000001ORDERBYidLIMIT10;或者合并为一行SELECT*FROMusersWHEREid(SELECTidFROMusersORDERBYidLIMIT1000000,1)ORDERBYidLIMIT10;原理将分页条件转移到主键索引树避免二级索引深分页带来的大量回表 [citation:8]。2.5 方案五覆盖索引优化核心思路建立包含查询所需全部字段的联合索引使查询完全在索引上完成无需回表。示例代码-- 假设经常查询 order_id, order_date, amountCREATEINDEXidx_coverONorders(order_id,order_date,amount);-- 查询可以直接从索引获取数据SELECTorder_id,order_date,amountFROMordersWHEREorder_id1000000ORDERBYorder_idLIMIT10;执行计划Extra: Using index覆盖索引。优势完全避免回表I/O 开销最小 [citation:2]。局限性字段过多时索引维护成本高写入性能下降、磁盘占用增加。大结果集时优化器可能认为全表扫描更优从而放弃索引 [citation:1]。2.6 方案六缓存结果集核心思路对于高频访问的分页数据如热门榜单、首页推荐将结果集缓存到 Redis 或 Memcached直接命中缓存返回。示例代码伪代码StringcacheKeyuser:list:page:pageNo;ListUserusersredisTemplate.opsForValue().get(cacheKey);if(usersnull){usersuserMapper.selectByPage(pageNo,pageSize);redisTemplate.opsForValue().set(cacheKey,users,Duration.ofMinutes(5));}适用场景读多写少、分页参数固定的场景如运营后台报表、商品列表。注意事项需考虑缓存一致性缓存穿透、缓存击穿、缓存雪崩和过期策略。2.7 方案七业务层限制最大页码核心思路从产品设计层面规避深分页。例如限制用户最多只能翻到第 1000 页超过则提示没有更多数据。原理绝大多数用户不会翻到很深的页面Google、淘宝等搜索引擎均采用此策略 [citation:11]。实现方式if(pageNoMAX_PAGE_LIMIT){returnCollections.emptyList();// 或抛出业务异常}3. 方案选型对比优化方案核心思路是否支持跳页性能等级适用场景主要限制主键范围查询WHERE id last_id LIMIT n❌ 不支持⭐⭐⭐⭐⭐主键连续且有序不支持跳页、主键不连续会跳过数据游标分页记录上一页最后值作为起点❌ 不支持⭐⭐⭐⭐⭐无限滚动、消息流需联合索引处理重复值、不能跳页延迟关联子查询先取 ID再 JOIN 回表✅ 支持⭐⭐⭐⭐后台报表、搜索列表SQL 较复杂、依赖排序字段索引子查询优化先查起始主键再范围查询✅ 支持⭐⭐⭐⭐需要兼容传统分页子查询可能产生临时表覆盖索引索引包含全部查询字段✅ 支持⭐⭐⭐查询字段固定且较少字段多时效维护成本高缓存结果集Redis/Memcached 缓存分页数据✅ 支持⭐⭐⭐⭐⭐命中时高频访问的固定分页缓存一致性、内存成本限制最大页码业务层限制最大 offset✅ 支持⭐⭐⭐所有分页场景牺牲极深分页的用户体验4. 生产环境避坑指南4.1 深分页的锁竞争问题当LIMIT offset, size的扫描范围过大MySQL 可能采用全表扫描 filesort这意味着大范围的读锁或间隙锁会被长时间持有极易引发死锁 [citation:4]。4.2 网络传输与内存消耗虽然返回给客户端的只有 20 行但 MySQL Server 层需要从存储引擎读取 100 万行数据到内存经过排序和过滤后再丢弃。这会直接影响 Buffer Pool 的命中率 [citation:4]。4.3FORCE INDEX不是万能药强制索引可能阻止优化器选择更优的执行计划应谨慎使用。正确的做法是通过EXPLAIN分析执行计划确保优化器按预期使用索引 [citation:1]。4.4 监控与告警开启慢查询日志监控LIMIT偏移量过大的 SQL。设置long_query_time阈值捕获深分页查询。定期使用EXPLAIN检查关键分页 SQL 的执行计划 [citation:1]。5. 面试官追问与高分回答模板追问 1“为什么深分页性能差”低分回答“因为 OFFSET 太大了。”过于笼统没有触及本质高分回答“深分页性能差的根本原因在于 MySQL 的执行机制LIMIT offset, N必须从头扫描offset N条记录然后丢弃前offset行。如果查询走二级索引且不满足覆盖索引会产生大量无意义的回表操作随机 I/O最后这些数据又被丢弃。即使优化器退化为全表扫描顺序扫描百万行的成本依然巨大。所以性能瓶颈不在索引本身而在 OFFSET 的设计机制上。” [citation:1][citation:8]追问 2“如何优化深分页查询”低分回答“加索引。”面试官会摇头高分回答优化深分页需要分场景讨论不需要跳页的场景如无限滚动列表首选游标分页用WHERE id last_id替代OFFSET扫描行数恒定为LIMIT值性能与页码深度无关。必须支持跳页的场景如后台报表使用延迟关联先在索引上通过子查询获取目标 ID再回表取完整数据将回表次数从offset N降至N次。高频固定分页使用缓存Redis直接命中避免打到数据库。产品设计层面限制最大页码绝大多数用户不会翻到第 1000 页以后。关键是先通过EXPLAIN分析执行计划确认瓶颈到底在索引扫描、回表还是 filesort再对症下药。 [citation:0][citation:1][citation:4]追问 3“游标分页有什么坑”高分回答游标分页主要有三个坑不支持跳页用户不能直接跳到第 100 页只能顺序翻页。排序字段重复值问题如果排序字段如create_time有重复仅用单一字段作为游标可能漏数据。正确做法是使用 (排序字段, 主键) 二元组并建立联合索引。数据插入导致的数据漂移用户翻到第 3 页时如果有新数据插入到前面翻到第 4 页可能看到重复数据。这是游标分页的固有特性需要在业务层接受或补偿。 [citation:0]追问 4“延迟关联在什么情况下效果不明显”高分回答延迟关联的效果取决于三个条件数据量不够大几百行数据时回表 100 次和 20 次几乎没有区别瓶颈根本不在 I/O 上。行宽太小如果一行只有几十字节sort buffer轻松装下回表开销也不明显。但如果一行有TEXT/BLOB字段、几百字节以上sort buffer装不下就会写磁盘临时文件差距就大了。排序字段没有索引子查询里的ORDER BY如果无法走索引本身就要filesort优化效果大打折扣。 [citation:0]追问 5“如果排序字段不是主键且允许重复游标分页的最佳实践是什么”高分回答最佳实践是使用(排序字段, 主键)组成的二元组作为游标并在数据库中建立对应的联合索引。例如排序字段是create_time主键是id则WHEREcreate_time2025-10-01OR(create_time2025-10-01ANDid50000)ORDERBYcreate_timeDESC,idDESCLIMIT20;同时必须建立(create_time, id)联合索引确保查询能高效利用索引的有序性避免filesort和全表扫描。 [citation:0]6. 方案选型速查表业务场景推荐方案理由后台管理系统列表翻页游标分页后台通常不需要随机跳页游标方案性能最优用户端无限滚动列表游标分页移动端上拉加载天然适合游标方案需要随机跳转页码如搜索结果页延迟关联 / 子查询必须兼容OFFSET模式用覆盖索引降低 I/O数据量不大 10 万行普通LIMIT简单直接性能瓶颈不明显高频访问的固定分页缓存结果集直接命中缓存零数据库压力运营后台深分页报表延迟关联 限制最大页码兼顾跳页需求与性能防止恶意深分页面试官想要的满分总结深分页问题的本质是OFFSET 机制缺陷——MySQL 无法直接跳转到指定偏移位置必须顺序扫描并丢弃大量数据。优化思路不是加索引这么简单而是要从执行机制、索引结构、业务场景三个维度综合考量。如果业务允许游标分页是性能最优解扫描行数恒定为LIMIT值千万级数据仍可毫秒级响应如果必须支持跳页延迟关联是最佳折中将回表次数从offset N降至N次。无论哪种方案都必须先用EXPLAIN分析执行计划确认瓶颈在索引扫描、回表还是filesort再对症下药。最后别忘了产品设计层面的兜底限制最大页码、缓存热点数据、甚至引导用户缩小查询范围——很多时候最好的优化是让这个问题根本不要发生。觉得对您有帮助麻烦点点关注啦您的关注是我创作的最大动力~