首页/发现新知
视界 · 深度阅读

MySQL深分页LIMIT 1000000卡死的三种改写方案

2026-10-20编程开发59122 约 16 分钟阅读

MySQL分页翻到后面越来越慢？OFFSET 100万行要等十几秒？本文实测了深分页的代价,并给出3种优化方案：延迟关联、游标分页和业务妥协,帮你彻底解决大表分页性能问题,需要的朋友可以参考下

目录
  • 一、实测：OFFSET 的代价是怎么随深度增长的
  • 二、为什么 OFFSET 这么慢：三层代价
  • 三、3 种改写
    • 改写 1：延迟关联（deferred join）—— 保留跳页能力
    • 改写 2：书签 / 游标分页 —— 性能最好，但要改交互
    • 改写 3：覆盖索引 + 业务妥协
  • 四、命令清单
    • 五、代码模板（Go 侧游标分页）
      • 六、4 步优化步骤
        • 七、总结

          后台列表页前几页秒开，翻到第 1000 页就要等十几秒，再往后直接超时。DBA 一看 SQL：

          SELECT id, name, score FROM user ORDER BY id LIMIT 20 OFFSET 1000000;

          这条语句看着很无辜——只取 20 行。为什么慢？因为 OFFSET 1000000 意味着 MySQL 必须先扫出前 100 万行，然后一行一行丢掉，只把最后 20 行返回给你。翻得越深，扔掉的行越多。

          这篇文章先用一组实测把代价量化，再给 3 种改写方案和各自适用的场景。分页是 MySQL 里最典型的性能优化场景之一，改法不难，难在先量清楚代价。

          一、实测：OFFSET 的代价是怎么随深度增长的

          我在本机建了一张 100 万行的表（id 主键自增），用不同的 OFFSET 深度各查几次取最快值：

          写法 耗时
          LIMIT 20 OFFSET 0 ~0 ms
          LIMIT 20 OFFSET 10000 0.1 ms
          LIMIT 20 OFFSET 100000 2.3 ms
          LIMIT 20 OFFSET 500000 11.7 ms
          LIMIT 20 OFFSET 900000 22.1 ms
          WHERE id > 900000 ORDER BY id LIMIT 20（游标） 0.02 ms

          说明：这组数字用 SQLite（100 万行）复现 OFFSET 的扫描代价机制。MySQL 的行为一致——都要扫描并丢弃 offset 之前的行，且 MySQL 还要额外承担回表开销（下面第二节），所以真实环境里差距只会更大。绝对值取决于表大小、缓存命中率和硬件，请用自己的表跑一遍确认（文末给了可复制的验证脚本）。

          两个结论很直观：

          1. OFFSET 的耗时随深度线性增长（0 → 10 万 → 50 万 → 90 万：0.1 → 2.3 → 11.7 → 22.1 ms）；
          2. 游标写法是恒定成本，90 万行处比 OFFSET 快了约 1000 倍，而且不随深度变化。

          二、为什么 OFFSET 这么慢：三层代价

          SELECT * FROM user ORDER BY id LIMIT 20 OFFSET 1000000;

① 扫描：按 id 顺序读出前 1000020 行（不是只读 20 行）
② 回表：如果用的是二级索引，每一行都要回主键取完整行
        → 100 万次随机 IO，这是最贵的一层
③ 丢弃：把前 100 万行扔掉，只返回最后 20 行

          第 ② 层是 MySQL 上最致命的，也是单纯的「加索引」解决不了的部分。用 EXPLAIN 能看到 rows 估算值远超 20：

          EXPLAIN SELECT id, name, score FROM user ORDER BY id LIMIT 20 OFFSET 1000000\G
-- type: index / ALL
-- rows: 1000020      ← 关键：优化器自己承认要扫 100 万行
-- Extra: Using filesort （排序字段没索引时还会多一层排序）

          顺带一个常被问的问题：COUNT(*) 也很慢（实测 100 万行 6.2 ms，在大表上会到秒级），如果分页要显示「共 N 条」，建议单独缓存总数，而不是每页都 COUNT。

          三、3 种改写

          改写 1：延迟关联（deferred join）—— 保留跳页能力

          思路：先用覆盖索引只取 20 个 id（不回表），再用这 20 个 id 回表取完整行。把 100 万次回表压缩成 20 次。

          -- ❌ 原始写法：100 万次回表
SELECT id, name, score FROM user ORDER BY id LIMIT 20 OFFSET 1000000;

-- ✅ 延迟关联：先只查 id（走覆盖索引），再回表 20 次
SELECT u.id, u.name, u.score
FROM user u
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 20 OFFSET 1000000
) t ON u.id = t.id
ORDER BY u.id;

          适用：必须支持跳页（用户能直接输入页码，或跳到「第 1000 页」）。代价是仍然要扫 offset 行（第一层代价还在），但省掉了最贵的回表。

          配合覆盖索引效果更好——如果子查询只需要的字段都在某个二级索引里，连主键回表都省了：

          -- 假设有索引 idx_status_id(status, id)
SELECT u.id, u.name, u.score
FROM user u
INNER JOIN (
    SELECT id FROM user WHERE status = 1 ORDER BY id LIMIT 20 OFFSET 1000000
) t ON u.id = t.id;
-- 子查询走 idx_status_id，完全覆盖，不回表

          改写 2：书签 / 游标分页 —— 性能最好，但要改交互

          思路：记住上一页最后一行的 id，下一页从它之后开始，彻底消灭 offset。

          -- 第一页
SELECT id, name, score FROM user ORDER BY id LIMIT 20;
-- 假设返回的最后一行 id = 10086

-- 下一页（用上一页的最大 id 当游标）
SELECT id, name, score FROM user WHERE id > 10086 ORDER BY id LIMIT 20;

          优点：成本恒定（实测 0.02 ms），翻到第一千万页也一样快。
          代价：

          • 不能跳页（不知道第 1000 页的游标在哪）；
          • 排序字段必须唯一且有序（用 id 最稳；如果用 create_time，同时间的行会漏/重，要写成 (create_time, id) > (last_time, last_id) 的复合游标）；
          • 有插入/删除时可能出现轻微的重复或遗漏（对信息流类列表通常可接受）。

          复合排序字段的正确写法：

          -- 按 create_time 排序，id 做 tie-breaker
SELECT id, name, create_time FROM user
WHERE (create_time, id) < ('2026-10-01 12:00:00', 9527)
ORDER BY create_time DESC, id DESC
LIMIT 20;

          改写 3：覆盖索引 + 业务妥协

          如果上面两种都不合适（必须跳页、且表很大），可以从业务上做三个妥协，每一个都能显著降低成本：

          1. 限制可翻深度：搜索类结果最多给 100 页，超出的引导用户加筛选条件（Google、淘宝都是这么做的）；
          2. 用筛选替代翻页：让用户按时间/状态筛选，把 100 万行切成若干小段；
          3. 预计算 / 冗余表：把分页结果按页缓存到 Redis 或一张中间表，定时刷新。

          三种改写的选择建议：

          场景 推荐方案 理由
          后台管理系统，必须跳页 延迟关联 + 覆盖索引 保留交互，砍掉回表
          App 信息流 / 无限滚动 游标分页 成本恒定，体验最好
          前台搜索列表 限制深度 + 筛选 用户其实不需要第 1000 页

          四、命令清单

          命令 作用 看什么
          EXPLAIN \G 看执行计划 ⭐ rows 是不是远大于 20；Extra 有没有 Using filesort
          EXPLAIN FORMAT=JSON 详细成本 rows_examined_per_scan 更直观
          EXPLAIN ANALYZE （8.0.18+） ⭐ 真实执行统计 直接给出实际扫描行数和时间，不用猜
          SHOW INDEX FROM user 看索引 排序字段有没有索引、Cardinality 如何
          SET profiling = 1; ; SHOW PROFILE; 单条耗时拆解 5.7 可用，8.0 推荐用 EXPLAIN ANALYZE
          SELECT @@long_query_time; SHOW VARIABLES LIKE 'slow_query_log%' 慢日志配置 把阈值调到 500ms 更容易抓到深分页
          SHOW GLOBAL STATUS LIKE 'Created_tmp%'、Sort_merge_passes 临时表/排序 深分页 + filesort 会推高这两个值
          ANALYZE TABLE user 更新统计信息 优化器估算离谱时先做这一步

          EXPLAIN ANALYZE 是 8.0 里最省事的工具，它会把「扫描了多少行、实际花了多久」直接打印出来，不用像 EXPLAIN 那样靠 rows 估算。

          五、代码模板（Go 侧游标分页）

          package main
import (
	"context"
	"database/sql"
	"fmt"
	"time"
)
// Cursor 游标分页的入参与出参
type Cursor struct {
	LastID   int64 `json:"last_id"`
	LastTime time.Time
	Limit    int
}
// FetchByCursor 用 (create_time, id) 复合游标取下一页
func FetchByCursor(ctx context.Context, db *sql.DB, c Cursor) ([]User, int64, time.Time, error) {
	const q = `
SELECT id, name, create_time
FROM user
WHERE (? = 1) OR (create_time, id) < (?, ?)
ORDER BY create_time DESC, id DESC
LIMIT ?`
	first := 0
	if c.LastID == 0 {
		first = 1 // 第一页：不加游标条件
	}
	rows, err := db.QueryContext(ctx, q, first, c.LastTime, c.LastID, c.Limit)
	if err != nil {
		return nil, 0, time.Time{}, err
	}
	defer rows.Close()
	var list []User
	var lastID int64
	var lastTime time.Time
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.CreateTime); err != nil {
			return nil, 0, time.Time{}, err
		}
		list = append(list, u)
		lastID, lastTime = u.ID, u.CreateTime
	}
	if err := rows.Err(); err != nil {
		return nil, 0, time.Time{}, err
	}
	return list, lastID, lastTime, nil
}
// FetchByOffsetDelayed 必须跳页时的延迟关联写法
func FetchByOffsetDelayed(ctx context.Context, db *sql.DB, page, size int) ([]User, error) {
	offset := (page - 1) * size
	const q = `
SELECT u.id, u.name, u.create_time
FROM user u
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT ? OFFSET ?
) t ON u.id = t.id
ORDER BY u.id`
	rows, err := db.QueryContext(ctx, q, size, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.CreateTime); err != nil {
			return nil, err
		}
		list = append(list, u)
	}
	return list, rows.Err()
}
type User struct {
	ID         int64
	Name       string
	CreateTime time.Time
}
func main() {
	fmt.Println("cursor / deferred join 两种分页实现")
}

          两条使用提醒：

          1. 游标里的时间要用数据库的时区，前后端传递建议直接用 id 做游标（最简单也最不容易错），时间只作为排序展示；
          2. LIMIT ? OFFSET ? 一定要参数化，别把页码直接拼进 SQL。

          六、4 步优化步骤

          第 1 步：先用 EXPLAIN ANALYZE 量化问题。
          确认 rows / 实际扫描行数是不是远大于返回行数。如果不是（比如 rows=20），那慢的原因不是深分页，去查别的东西。

          第 2 步：确认排序字段有索引，且没有 filesort。
          EXPLAIN 的 Extra 里有 Using filesort 说明还要额外排序。给排序字段建索引（复合排序就建复合索引，注意字段顺序和升降序一致）。

          第 3 步：按业务场景选改写方案。
          能改成游标就改（性能最好）；必须跳页就用延迟关联；都不行就限制可翻深度。

          第 4 步：改完必须回到第 1 步复测。
          同样深度再跑一次 EXPLAIN ANALYZE，对比扫描行数和耗时。只看「页面是不是变快了」不可靠——缓存预热、并发波动都会干扰感受。

          七、总结

          1. OFFSET N 的代价是扫描并丢弃前 N 行，不是「直接跳到第 N 行」。实测 100 万行表上，OFFSET 从 0 到 90 万，耗时从 ~0ms 涨到 22ms，而游标写法恒定在 0.02ms（约 1000 倍差距）。
          2. MySQL 上最贵的是回表：用二级索引时，OFFSET 会对前 N 行逐行回表。延迟关联把这 N 次回表压缩成 20 次，是必须跳页场景的首选。
          3. 3 种改写：① 延迟关联（保留跳页，砍回表）；② 游标/书签（成本恒定，不能跳页，排序字段要唯一有序）；③ 业务妥协（限深度/加筛选/预计算）。
          4. 复合排序要用复合游标：ORDER BY create_time DESC, id DESC 对应的游标条件是 (create_time, id) < (?, ?)，只用时间会漏数据。
          5. 改完必须复测：用 EXPLAIN ANALYZE（8.0.18+）对比扫描行数，别凭感觉判断优化是否生效。

          本文实测数字来自本机 SQLite（100 万行）复现 OFFSET 扫描代价机制；MySQL 机制相同且多了回表开销。请用自己的表和 EXPLAIN ANALYZE 复测绝对值。

          以上就是MySQL深分页LIMIT 1000000卡死的三种改写方案的详细内容，更多关于MySQL深分页LIMIT 1000000卡死的资料请关注爱读视界其它相关文章！

          您可能感兴趣的文章:

          • MySQL LIMIT 深分页性能问题与优化实战
          • MySQL 深分页查询优化实践与经验分享
          • MySQL中深分页LIMIT 100000的优化方案
          • 深入理解MySQL深分页慢问题及性能优化
          • MySQL深分页进行性能优化的常见方法
          读到这里，发现更多一点。探索更多内容 ↗

          接着读点什么

          使用Python实现向量化的三种方式↗结局由玩家决定！《权游》新作可改写艾莉亚复仇宿命↗《人工智能的选择》首款DLC“羁绊”发售，新增三种结局与多条分支故事↗讯飞绘文改写营销文案的操作方法↗