> LIMIT 分页的性能优化：深分页到底慢在哪？

---

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第14天内容。

---

## 背景引入

> 💡 分页查询谁没写过？`LIMIT 100000, 20` 这么写，翻到后面几页就卡死了，你知道为什么吗？

做个填空题：一个 SQL 执行了 5 秒，大概率是因为——

- A. 没加索引
- B. 深分页
- C. 表太大
- D. 以上都是

**答案是：D，但 B 往往是最隐蔽的那个。**

第 1 页飞快的 SQL，翻到第 100 页突然慢了 100 倍。加索引也救不了。

**今天的目标**：搞懂深分页为什么慢，以及三种最实用的优化方案。

---

## 核心概念

### 一个常见的分页查询，MySQL 是怎么跑的？

```sql
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
```

你脑子里想的可能是：从第 100000 行开始，取 20 行，MySQL 就扫了 20 行吧？

**MySQL 实际的内心戏是：**

1. 走全表或索引扫描，找到所有满足条件的行
2. 按 `create_time DESC` 排序
3. 从头数到第 100020 行
4. 丢掉前 100000 行，留下 100001 ~ 100020 行
5. 返回这 20 行

**说白了：LIMIT 100000, 20 的意思是「排好序后，数到第 100000 行，扔掉前面的，我要后面的 20 行」。**

MySQL 再聪明也不知道你只关心后面的 20 行——它得先数到第 100000 行。

**随着页码增大，扫描行数线性增长。** 第 1 页扫描几十行，第 1000 页就要扫描几十万行——这就是「深分页」的代价。

---

### 面试必问的三种优化方案

---

#### 方案一：游标分页（Cursor-based / Keyset Pagination）

核心思想：**不翻页，只翻篇**。

传统的 `LIMIT OFFSET` 需要先「跳过」前面的行，而游标分页直接记住上一页最后一条的位置，从那里开始取。

```sql
-- 传统分页（第 10001 页）
SELECT * FROM orders 
ORDER BY id 
LIMIT 100000, 20;

-- 游标分页（记住上一页最后一条的 id = 100000）
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;
```

第二条 SQL 的 `WHERE id > 100000` 直接走主键索引定位到 100000，然后往后取 20 行就结束了——**每一页扫描的行数都一样，不管你在第几页。**

这里用 `id` 举例是因为主键唯一且有序，最容易理解。换成 `create_time` 也一样——`WHERE create_time > 上次的时间 ORDER BY create_time LIMIT 20`，只是排序字段有重复值时要带上主键做决胜条件（后面会讲）。

**关键条件**：
- 需要排序字段是**有序且唯一**的（通常是主键）
- 不能直接跳到任意页码，只能「上一页/下一页」
- 翻页稳定性好：中间有数据插入也不会导致重复或遗漏

**适用场景**：朋友圈、信息流、下拉加载更多（无限滚动）

---

#### 方案二：延迟关联（Deferred Join / 先查主键再 JOIN）

适合不能改用游标分页、又必须用 OFFSET 的场景：

```sql
-- 原 SQL（慢）
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 100000, 20;

-- 延迟关联优化
SELECT * FROM orders 
INNER JOIN (
  SELECT id FROM orders 
  ORDER BY create_time DESC 
  LIMIT 100000, 20
) AS tmp ON orders.id = tmp.id;
```

**为什么快？**

子查询只查 `id`，如果排序字段有索引，走的就是**覆盖索引扫描**（`Extra: Using index`），不需要回表取完整行数据。等拿到 20 个 id 后，再回表取 20 次完整行。

原 SQL 的流程是：引擎层先回表取 100020 行的完整数据 → 丢给 server 层排序 → 扔掉 100000 行。等于做了 100020 次回表，大部分都白费了。

**但说实话，这个优化的效果取决于三个条件**：

1. **数据量要够大**——几百行的时候回表 100 次和回表 20 次几乎没区别，性能瓶颈根本不在 IO 上
2. **行宽要大**——如果一行只有几十字节，sort buffer 轻松装下，回表开销也不明显。但如果一行有 text/blob 字段、几百字节甚至更多，sort buffer 装不下就会写磁盘临时文件，差距就大了
3. **create_time 字段有索引**——如果没有索引，排序会很慢，回表开销也会很大。

**一句话**：深分页（OFFSET 上万）且行宽较大时，优化效果明显；前几页或窄表场景下，差别微乎其微。

**适用场景**：后台分页报表、搜索列表等不适用游标分页、又确实有深分页压力的业务

---

#### 方案三：范围分页（Range-based Pagination）

如果你的排序字段是连续递增的（比如自增 id），还可以这么做：

```sql
-- 假设每页 20 条
SELECT * FROM orders 
WHERE id BETWEEN 100001 AND 100020 
ORDER BY id;
```

这个方案极度高效，但局限性也明显——必须用连续自增字段做分页，按时间排序就不适用了。

---

## 实战案例

以下是在 5 万行数据上，三个方案的真实 EXPLAIN 对比：

```sql
-- 方案一：传统 LIMIT OFFSET（深分页，扫全表 + filesort）
EXPLAIN SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 30000, 20\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 50440
     filtered: 100.00
        Extra: Using filesort

-- 方案二：延迟关联（子查询只走索引，不回表）
EXPLAIN SELECT * FROM orders 
INNER JOIN (
  SELECT id FROM orders 
  ORDER BY create_time DESC 
  LIMIT 30000, 20
) AS tmp ON orders.id = tmp.id\G

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
         rows: 30020

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: orders
         type: eq_ref
         rows: 1

*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: orders
         type: index
          key: idx_create_time
         rows: 30020
        Extra: Backward index scan; Using index

-- 方案三：游标分页（直接范围定位，只扫 20 行）
EXPLAIN SELECT * FROM orders 
WHERE create_time < '2026-05-05 21:36:27' 
ORDER BY create_time DESC 
LIMIT 20\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
  possible_keys: idx_create_time,idx_covering
          key: idx_create_time
         rows: 20
        Extra: Using index condition; Backward index scan
```

三个方案的 `rows` 字段差距一目了然：

| 方案 | rows | Extra | 回表次数 |
|------|------|-------|----------|
| 传统深分页 | 50440 | Using filesort | 50440 次 |
| 延迟关联 | 30020（索引）+ 20（主表）| Using index | 仅 20 次 |
| 游标分页 | **20** | 无 filesort | 20 次 |

传统方案预估扫 50440 行还要 filesort；延迟关联虽然也扫了 30020 行索引（`Using index`），但这是**索引只读**，不需要回表取完整行数据；游标分页更直接，只扫 20 行就结束了。

---

### 游标分页的边界处理细节

游标分页容易踩的一个坑：排序字段有**重复值**。

假设 `create_time` 有重复，只用 `WHERE create_time < '2025-10-01'` 可能漏掉数据。正确的写法是带上主键作为决胜条件：

```sql
-- 上一页最后一条：create_time = '2025-10-01', id = 50000

-- 正确：用 (create_time, id) 二元组定位
WHERE create_time < '2025-10-01' 
   OR (create_time = '2025-10-01' AND id < 50000)
ORDER BY create_time DESC, id DESC 
LIMIT 20;
```

这也要求游标分页必须有一个**联合索引 `(create_time, id)`** 才能高效执行。

---

## 避坑指南

> ⚠️ 真实踩过的坑：

1. **OFFSET 越大越慢，加索引也没用**
   - 遇到过运营后台的分页报表，翻到第 200 页直接超时
   - 根因不是索引问题，是 OFFSET 的设计问题——优化方案直接改成游标分页，从 8 秒降到了 0.01 秒

2. **游标分页不能随便跳页，且排序字段有重复时需要联合索引**
   - 产品经理问：「为什么不能直接跳到第 100 页？」
   - 游标分页的限制就是不能跳页。如果业务要求跳页，可以用延迟关联做折中
   - 排序字段有重复值时，必须建 `(排序字段, id)` 联合索引才能高效执行游标分页

3. **延迟关联别忘了 ORDER BY 字段要建索引**
   - 子查询里的 `ORDER BY create_time DESC LIMIT 50000, 20`，如果 `create_time` 没有索引，子查询本身就要 filesort，优化的效果大打折扣

---

## 思考题

> 🤔 互动时间：

1. 游标分页下，用户 A 翻到第 3 页时，管理员插入了一条新数据。用户 A 翻到第 4 页会看到什么？（提示：考虑游标位置 vs OFFSET 位置）
2. `LIMIT 50000, 20` 和 `LIMIT 20 OFFSET 50000` 有区别吗？
3. 如果你的排序字段不是主键且允许重复，游标分页的最佳实践是什么？

---

## 总结

> 🎯 **面试考点**

- 深分页慢的根本原因：**OFFSET 越大，MySQL 扫描和丢弃的行数越多**
- 三种优化方案：**游标分页**（性能最好，不能跳页）、**延迟关联**（能跳页，兼容性好）、**范围分页**（最简单，限制最多）
- 游标分页的核心：用 `WHERE 排序字段 > 上一页最后值` 替代 `LIMIT OFFSET`，每页扫描行数恒定
- 游标分页必须处理**重复值**：排序字段 + 主键做二元组，建联合索引

今天就试一下：找一个线上最慢的分页查询，把 `EXPLAIN` 拉出来看看 `rows` 有多大。如果 `rows` 远大于 `LIMIT` 的条数，恭喜你——这就是可以优化的深分页。

下篇第 15 天，我们会分享 `INSERT` 批量插入相关的内容，敬请关注。

---

**下期预告**：INSERT 批量插入的最佳实践 —— 别再逐条 INSERT 了！
**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  

---

*有问题欢迎评论区交流，明天见！*


做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

【MySQL百日打怪升级第14天】 LIMIT 分页的性能优化：深分页到底慢在哪？

本文深入剖析MySQL深分页（如`LIMIT 100000,20`）性能瓶颈：本质是OFFSET导致全量扫描与丢弃，页码越深，扫描行数线性增长。详解三种实战优化方案——游标分页（高效稳定，需有序唯一字段）、延迟关联（兼容OFFSET，索引覆盖减回表）、范围分页（极简但场景受限），并附EXPLAIN对比与避坑指南。（239字）

数据库

后端

算法

本文深入分析MySQL深分页查询性能问题，揭示LIMIT OFFSET在处理大量数据时效率低下的根源。提供三种高效优化方案：游标分页通过记录上页末位实现恒定扫描行数；延迟关联利用子查询减少回表次数；范围分页适用于连续自增字段。详解各方案适用场景及实战对比，帮助开发者解决深分页性能瓶颈。

MySQL

腾讯云数据库特惠，新用户首单0.5折起


腾讯云BI新客户首次购买9.9元起！


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第14天】 LIMIT 分页的性能优化：深分页到底慢在哪？-腾讯云开发者社区-腾讯云

【MySQL百日打怪升级第14天】 LIMIT 分页的性能优化：深分页到底慢在哪？

【MySQL百日打怪升级第14天】 LIMIT 分页的性能优化：深分页到底慢在哪？

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐