MySQL 深分页OFFSET 越大越慢怎么解决目录深分页问题LIMIT offset, count 到底在干什么为什么 OFFSET 越大越慢方案一延迟关联先捞 ID 再取数据方案二游标分页用上一页的最后一条当起点方案三子查询优化让 MySQL 先走索引三种方案对比小结深分页问题一个商品列表页后端接口用的分页查询SELECT*FROMproductsORDERBYidLIMIT20OFFSET0;前几页加载很快用户也没啥感觉。但当翻到第 500 页的时候接口响应时间从 50ms 飙到了 3 秒。你打开慢查询日志一看又是这条 SQL 在搞事。这就是深分页问题表里有 50 万条数据id是主键按理说走索引应该很快。但 OFFSET 一大性能就断崖式下跌。这不是个例几乎所有用LIMIT offset, count做分页的系统随着数据量的增加都会撞上这堵墙。LIMIT offset, count 到底在干什么先看一条最简单的分页 SQLSELECT*FROMproductsORDERBYidLIMIT20OFFSET1000;这条语句的执行过程是这样的1. MySQL 从索引主键上从第一条开始逐条往后扫 2. 扫到第 1 条时开始计数跳过前 1000 条 3. 从第 1001 条开始取 20 条返回 4. 对这 20 条记录回表取完整行数据关键在第 2 步。MySQL 必须逐条跳过前 1000 条记录即使它不需要这些数据。这些被跳过的记录MySQL 一样要扫描、一样要比较只是最终不返回而已。跳过不等于不扫描。OFFSET 越大跳过越多扫描越多。为什么 OFFSET 越大越慢用 EXPLAIN 看一下这条查询的执行计划EXPLAINSELECT*FROMproductsORDERBYidLIMIT20OFFSET1000;-------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | products | NULL | index| NULL | PRIMARY | 8 | NULL | 1020 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------注意type index和rows 1020。type 为 index 说明走了全索引扫描遍历整棵索引树rows 为 1020 说明预估要扫描 1020 行。OFFSET 越大这个 rows 值就越大。扫到第 100 万页时光跳过就得扫描 100 万条记录。即使每条记录扫描只要 0.1 毫秒100 万条也要 100 秒。更糟的是这个查询除了扫描索引还要回表取*的所有字段。每一条被跳过的记录MySQL 可能都要做一次回表。因为SELECT *取的是完整行数据索引里存不下了必须回表。这就是深分页慢的两个根源扫描浪费OFFSET 越大MySQL 丢弃的记录越多但扫描成本不变回表浪费SELECT *导致每条被跳过的记录都可能触发回表方案一延迟关联先查 ID 再取数据延迟关联的核心思路是先用覆盖索引快速拿到需要的 ID再用 ID 回表取完整数据。SELECTp.*FROMproducts pINNERJOIN(SELECTidFROMproductsORDERBYidLIMIT20OFFSET1000)tONp.idt.id;这条 SQL 分两步执行第一步子查询 SELECT id FROM products ORDER BY id LIMIT 20 OFFSET 1000 → 只扫主键索引不需要回表快速拿到 20 个 ID 第二步外层查询 SELECT p.* FROM products p WHERE p.id IN (...) → 用主键精确查 20 条直接走聚簇索引零回表为什么这样更快对比一下步骤原始写法延迟关联扫描阶段扫描 1020 条每条都要判断扫描 1020 条只读 ID覆盖索引回表阶段跳过的 1000 条也可能回表跳过的 1000 条不回表取数阶段20 条全量回表20 条精确回表子查询用了覆盖索引只取 id扫描阶段的开销大幅降低。外层查询用主键精确查找不用扫描、不用排序。方案二游标分页用上一页的最后一条当起点延迟关联解决了回表浪费但扫描浪费还在——OFFSET 1000 时还是要跳过 1000 条。游标分页直接把 OFFSET 干掉了。思路是记住上一页最后一条记录的 ID下一页查询时从这个 ID 之后开始取。-- 第一页SELECT*FROMproductsORDERBYidLIMIT20;-- 返回的最后一条 id 1000-- 第二页从 id 1000 之后开始SELECT*FROMproductsWHEREid1000ORDERBYidLIMIT20;-- 第三页从上一页最后一条 id 1020 之后开始SELECT*FROMproductsWHEREid1020ORDERBYidLIMIT20;EXPLAIN 看一下执行计划EXPLAINSELECT*FROMproductsWHEREid1000ORDERBYidLIMIT20;------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | products | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 20 | 100.00 | NULL | ------------------------------------------------------------------------------------------------------------type rangerows 20。MySQL 直接定位到 id 1000 的位置取 20 条就停了。不管翻到第几页扫描行数永远是 20。但游标分页有局限只能下一页不能跳页。用户点第 5 页你没法直接算出对应的 ID 是多少。所以它适用于无限滚动、加载更多这类场景不适合有页码的分页器。方案三子查询优化让 MySQL 先走索引这个方案适合没有主键可用、或者排序字段不是主键的场景。SELECTp.*FROMproducts pWHEREp.id(SELECTidFROMproductsORDERBYidLIMIT1OFFSET1000)ORDERBYp.idLIMIT20;子查询只执行一次拿到 OFFSET 位置的那条记录的 ID。外层查询从这个 ID 开始往后取 20 条。和延迟关联的区别在于延迟关联是先查一批 ID再用 ID 取数据这个方案是先找一个起点 ID再从起点往后取。子查询只返回一条记录开销极小。用伪代码理解// 子查询找起点 start_id SELECT id FROM products ORDER BY id LIMIT 1 OFFSET 1000 // 外层从起点取数据 SELECT * FROM products WHERE id start_id ORDER BY id LIMIT 20外层查询id start_id加上ORDER BY id和LIMIT 20MySQL 可以直接走主键范围扫描rows 只有 20。三种方案对比方案原理适用场景能否跳页性能延迟关联覆盖索引查 ID再回表取数据通用改造成本低能OFFSET 大时显著提升游标分页用上一页 ID 当起点去掉 OFFSET无限滚动、加载更多不能任何 OFFSET 下恒定子查询优化子查询找起点外层范围取数排序字段不是主键时能子查询开销小外层走范围选择建议有页码导航的需求后台管理系统、商品搜索延迟关联或子查询优化无限滚动、信息流朋友圈、微博游标分页数据量千万级游标分页是唯一选择其他方案在超大 OFFSET 下依然会退化小结深分页慢的根源OFFSET 越大MySQL 丢弃的数据越多但扫描的成本一点没少。延迟关联用覆盖索引减少了回表浪费子查询优化用一个精确的起点取代了逐条跳过游标分页则直接绕过了 OFFSET 的问题。三者本质都在做同一件事让 MySQL 跳过那些不需要的记录而不是扫描了再丢掉。
