面经策

MySQL 中如何解决深度分页的问题？ ​

  • 标签：后端、MySQL、数据库、场景题

🏛 业务架构师 ​

深度分页的问题本质：limit 1000000, 20 要扫过并丢弃前 100 万行，offset 越深成本越高。业务治理我按成本从低到高分三档：

第一档：产品层消解（最便宜，优先做）

  • 后台列表限制最大页数（天猫、京东搜索页只放 100 页）、强制带过滤条件、改滚动加载/游标式"下一页"——"翻到第 5 万页"是伪需求，砍掉它就是最好的优化，这是我拉着产品一起做的动作。

第二档：SQL 层优化

  • 游标/续传式：where id > {上一页最后一条 id} limit 20，利用索引直接定位，O(1) 起步，适合信息流、消息列表；
  • 延迟关联：select t.* from orders t join (select id from orders where user_id=? order by created_at desc limit 1000000, 20) tmp on t.id=tmp.id——子查询在覆盖索引上完成定位，回表只做 20 次。

第三档：架构层分流

  • 运营后台深查询路由到 ES/ClickHouse，分析流量彻底离开交易库；导出类需求走异步任务 + 游标分页，不占在线接口。

一句话：深度分页的根因是"把全量遍历伪装成翻页"。先和产品对齐真实需求，再谈技术方案，成本排序永远是：改需求 < 改 SQL < 改架构。

🏗 系统架构师 ​

深度分页（limit 1000000, 20）的代价在于 MySQL 要扫描并丢弃前 100 万行，再回表取后 20 行，越往后越慢。我按改造成本从低到高给方案：

方案一：游标/续传式分页（首选）：记住上一页最后一条的排序值，where id > last_id order by id limit 20，每次只扫 20 行。限制是只能连续翻页、不能跳页——信息流、消息列表本来就该这么设计，我把产品交互改掉比硬扛技术问题更划算；

方案二：延迟关联：必须跳页时，先用覆盖索引拿主键再回表：

sql
select t.* from t join (
  select id from t where a = ? order by id limit 1000000, 20
) tmp on t.id = tmp.id;

子查询在索引树上完成，回表只有 20 次，大偏移下常有数量级提升；

方案三：产品/架构层：限制最大页数（搜索引擎都只给 100 页）；总数用缓存或计数表，避免 count 全扫；明细导出走异步任务 + 文件，不走在线分页。

我的治理顺序：先问业务是否真需要深度跳页 → 游标化 → 延迟关联 → 计数与列表分离缓存。SQL 层优化永远排在交互与架构重构之后。

💻 全栈开发专家 ​

LIMIT 1000000, 20 的问题：MySQL 要扫过前 1000020 行再丢弃前 100 万行，还要 100 万次回表，越往后翻越慢。

方案一：游标/续传分页（首选，前提有序且翻页连续）

sql
-- 记录上一页最后一条的 id，下一页从它开始
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 索引上顺序扫 20 行，O(1) 级别，和第几页无关

适合无限滚动的信息流；缺点是不能跳页（要么接受，要么跳页用方案二）。

方案二：延迟关联（跳页可用）

sql
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE user_id = 7 ORDER BY create_time DESC LIMIT 1000000, 20) t
ON o.id = t.id;
-- 子查询在索引 idx(user_id, create_time) 上完成，覆盖索引不回表
-- 只有最终 20 条回表取整行

方案三：产品层限制——搜索类产品只允许前 50 页（ES 场景 max_result_window 同样思路），或用滚动导出代替跳页。

我落地的选择：管理后台跳页用延迟关联（10 万页内），C 端信息流一律游标。深分页本质是"偏移量语义"的错，换成"位置语义"才是根治。