我的列表接口前几页飞快、翻到几十万页却慢到超时,同样是查 20 条,凭什么越往后越慢,我对着深分页的 LIMIT offset 排查了大半天的复盘

那是我做的一个数据列表接口,支持翻页,用的是最经典的 LIMIT offset, size 分页。上线后一切正常,直到有用户(或者爬虫)开始翻到很后面的页码。监控告警炸了:同样是每页查 20 条,翻第 1 页只要几毫秒,翻到第几十万页却要好几秒、甚至直接超时。我一脸问号:不都是查 20 条吗?数据量一样,凭什么后面的页就慢成这样?我甚至以为是后面的数据有什么特殊。排查了大半天,我才真正理解了 LIMIT offset, size 这种分页方式,在"深分页"时那个致命的性能陷阱。这篇就把这场"越翻越慢"的事故,从头复盘一遍。

故障现场:同样查 20 条,深页慢上千倍

先看现场。同一条分页 SQL,只是 offset 不同,耗时天差地别:

-- 我的分页 SQL: 经典的 LIMIT offset, size
SELECT * FROM orders ORDER BY id LIMIT 0, 20;        -- 第1页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;  -- 第5万页

-- 耗时对比(假设 orders 有几百万行):
-- LIMIT 0, 20         →  ~2ms     (飞快)
-- LIMIT 10000, 20     →  ~20ms
-- LIMIT 100000, 20    →  ~200ms
-- LIMIT 1000000, 20   →  ~2s      (慢了上千倍!)
-- LIMIT 5000000, 20   →  超时

-- 用 EXPLAIN 看 LIMIT 1000000, 20:
-- 即使 id 上有索引、用了索引扫描, rows 预估也是 1000020 左右!

-- 为什么? LIMIT offset, size 的执行真相:
--   LIMIT 1000000, 20 的含义不是"直接跳到第1000000行取20条",
--   而是: "从头开始, 一行一行地扫描/读取 1000000 + 20 行,
--          然后【丢弃前面的 1000000 行】, 只返回最后的 20 行"。
--   ↑ 关键: 它必须真的"读"过前面那一百万行(哪怕最后扔掉)!

-- 现象拼图:
--   - offset 越大, 数据库要"读取并丢弃"的行就越多。
--   - LIMIT 0, 20: 读20行, 返回20行 → 快。
--   - LIMIT 1000000, 20: 读 1000020 行, 丢弃 1000000 行, 返回20行 → 慢。
--   - 即使走了索引, 也得沿着索引"数着走过"前100万行(还可能回表), 才能到第100万行。
--   - 所以"查的都是20条", 但 offset 决定了"为找到这20条要先趟过多少行"。
--   - ★ LIMIT 的 offset 不是"跳过", 而是"读取后丢弃" —— 这是深分页慢的根源。

看清真相后,我恍然大悟。问题的根源,是我对 LIMIT offset, size 的理解错了:我以为 LIMIT 1000000, 20 是"直接跳到第 100 万行、取 20 条",可实际上,它的含义是"从头开始一行一行地读取 1000020 行,然后丢弃前面的 1000000 行,只返回最后 20 行"。关键就在这:它必须真的"读"过前面那一百万行(哪怕最后全扔掉)!所以,offset 越大,数据库要"读取并丢弃"的行就越多;即使走了索引,也得沿着索引"数着走过"前 100 万行才能到达目标位置。这就解释了那个诡异的现象:"查的都是 20 条",但 offset 决定了"为找到这 20 条,要先趟过多少行"。LIMIT 的 offset 不是"跳过",而是"读取后丢弃"——这正是深分页慢的根源。

第一件事:搞懂 LIMIT offset 为什么是 O(offset)

要解决它,得先彻底搞懂 LIMIT offset, size 的执行成本为什么随 offset 线性增长。

LIMIT offset, size 的执行成本真相

# 它到底怎么执行?
#   LIMIT M, N(从第M行起取N行)的语义:
#   1. 按 ORDER BY 的顺序(通常沿着某个索引或全表), 从头开始读行。
#   2. 一行一行地数, 一直读到第 M+N 行。
#   3. 把前 M 行【丢弃】, 只返回第 M+1 到 M+N 这 N 行。
#   → 成本 ≈ O(M + N), 主要是 O(M)。M(offset)越大越慢。

# 为什么"走了索引"还是慢?
#   情况A: 用 ORDER BY id 且 SELECT *(需要回表):
#     - 沿主键/索引顺序, 读M+N行的【完整数据】(可能回表M+N次), 丢弃M行。
#     - 既要趟过M行, 还可能回表M次 → 很慢。
#   情况B: 即使是覆盖索引(不回表):
#     - 仍要沿索引"数着"走过 M 个条目才能定位到第 M 行 → 还是 O(M)。

# 核心矛盾:
#   - "页码翻页"这个需求, 天然要求"跳到第 offset 行"。
#   - 但 B+树索引/数据存储, 不支持"O(1)直接定位到第N行"(它不是数组下标)。
#   - 它只支持"从某个值开始, 高效地顺序/范围扫描"。
#   - 所以 offset 只能靠"一行行数过去"来实现 → 深分页必然慢。

# 一个直觉类比:
#   像在一本没有页码、只按姓名排序的电话簿里, 找"第100万个人":
#   你没法直接翻到, 只能从第一页开始一个一个数到第100万个。
#   但如果你知道"从'张三'之后开始找", 就能直接翻到张三那页往后读 —— 快!
#   (这就是下面正解的思路: 用"上次的位置"代替"页码偏移量")

# 核心: LIMIT offset 的本质是"读取M+N行再丢弃前M行", 成本O(offset)随页码线性增长;
#   因索引不支持O(1)定位第N行, 深分页必然慢; 解法是用"上次位置"代替"偏移量"。

原来,深分页慢是 LIMIT offset 的执行机制决定的,几乎无法通过"调优"绕过。LIMIT M, N 的语义是:按顺序从头读行,一行行数到第 M+N 行,丢弃前 M 行、只返回 N 行,成本约 O(M+N)、主要是 O(M)。为什么走了索引还慢?SELECT * 时要沿索引读 M+N 行的完整数据(可能回表 M 次);即使是覆盖索引(不回表),也仍要沿索引"数着"走过 M 个条目才能定位到第 M 行——还是 O(M)。核心矛盾在于:"页码翻页"天然要求"跳到第 offset 行",但 B+ 树索引/数据存储不支持"O(1) 直接定位到第 N 行"(它不是数组下标),只支持"从某个值开始高效地范围扫描"——所以 offset 只能靠"一行行数过去"实现,深分页必然慢。一个直觉类比:像在一本只按姓名排序的电话簿里找"第 100 万个人",你没法直接翻到、只能从头一个个数;但如果你知道"从'张三'之后开始找",就能直接翻到那页往后读——快!这正是正解的思路:用"上次的位置"代替"页码偏移量"。

第二件事:正解——用"游标分页"代替"偏移量分页"

搞懂了原理,正解就清晰了:用上一页最后一条的位置(游标)做 WHERE 条件,直接定位、不再 offset;或延迟关联只对主键深分页。

-- ====== 正解一(推荐): 游标分页 / Keyset Pagination(基于"上次位置")======
-- 不用 offset, 而是记住"上一页最后一条的 id", 下一页从它之后开始查:
-- 第1页:
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;
--   返回 id 为 1..20, 记住最后一条 id = 20。
-- 第2页(传入上次的 last_id=20):
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20;
--   → 利用 id 索引【直接定位】到 id>20 的位置, 往后读20行。
-- 第N页(无论多深):
SELECT * FROM orders WHERE id > {上次最后的id} ORDER BY id LIMIT 20;
--   ★ 每一页都是 O(20)! 不管翻到多后面, 都一样快(走索引直接定位)。

-- ====== 正解二: 延迟关联(deferred join), 不改前端分页参数时的优化 ======
-- 如果必须支持"跳到任意页码"(还是要 offset), 至少减少回表:
SELECT * FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20  -- 子查询只查主键(覆盖索引)
) t ON o.id = t.id;
--   → 子查询在索引上深分页(只数主键, 不回表, 快很多), 只对最终20个id回表。
--   比直接 SELECT * ... LIMIT 1000000,20(每行都回表)快, 但仍是O(offset), 治标。

-- ====== 正解三: 用 WHERE 条件 + 索引把范围缩小 ======
-- 如果分页带筛选(如按日期), 让 WHERE 先用索引把数据集缩小, 再分页:
SELECT * FROM orders
WHERE create_date = '2026-06-01' AND id > {last_id}
ORDER BY id LIMIT 20;

-- 游标分页 vs 偏移分页 的取舍:
--   游标分页: 极快(每页O(size)), 但【只能上一页/下一页, 不能跳到任意页码】。
--   偏移分页: 能跳到任意页, 但深分页极慢。
--   实际: 大多数"信息流/无限滚动"场景, 用游标分页完全够(用户也不会跳到第5万页)。

-- 核心: 深分页用游标分页(WHERE id > last_id), 利用索引直接定位、每页O(size)恒快;
--   必须任意跳页则用延迟关联减少回表(治标); 带筛选则先用WHERE+索引缩小范围。

修复的核心,是"用'上次的位置'(游标)代替'页码偏移量'(offset),让数据库能直接定位、而不是一行行数过去"。正解一(推荐):游标分页 / Keyset Pagination——不用 offset,而是记住上一页最后一条的 id,下一页用 WHERE id > {last_id} ORDER BY id LIMIT 20;这样每一页都利用 id 索引直接定位、往后读 20 行,无论翻到多深都是 O(20)、一样快。正解二:延迟关联(deferred join)——如果必须支持跳任意页码,让子查询只在索引上深分页查主键(覆盖索引、不回表),只对最终 20 个 id 回表,比每行都回表快很多(但仍是 O(offset),治标)。正解三:用 WHERE + 索引缩小范围——分页带筛选时,让 WHERE 先用索引把数据集缩小再分页。而游标分页和偏移分页的取舍:游标分页极快(每页 O(size))但只能上/下一页、不能跳任意页码;偏移分页能跳任意页但深分页极慢;实际大多数信息流/无限滚动场景,游标分页完全够用(用户也不会跳到第 5 万页)。归根结底:深分页用游标分页(WHERE id > last_id)每页恒快;必须任意跳页用延迟关联减少回表;带筛选则先用 WHERE+索引缩小范围。

第三件事:游标分页的一些实现细节

游标分页虽好,但实现时有几个细节坑,我也一并梳理了,免得用错。

游标分页的实现细节与注意点

# 1. 游标字段必须"唯一 + 有序 + 有索引"
#   - 通常用主键 id(自增, 天然唯一有序有索引), 最简单可靠。
#   - 用别的字段(如 create_time)做游标? 注意它可能【不唯一】!
#     多条记录 create_time 相同 → WHERE time > X 会漏掉/重复同一时刻的记录。

# 2. 非唯一字段排序: 用"组合游标"保证唯一
#   - 如果要按 create_time 排序(它不唯一), 加上 id 作为"第二排序键":
#     ORDER BY create_time, id
#     WHERE (create_time > {last_time})
#        OR (create_time = {last_time} AND id > {last_id})
#   → 用 (time, id) 组合做游标, 保证全局唯一、不漏不重。

# 3. 游标怎么传给前端?
#   - 返回数据时, 带上"下一页游标"(last_id 或 编码后的游标串)。
#   - 前端请求下一页时, 带上这个游标。
#   - 可把游标 base64 编码, 隐藏内部字段, 更优雅(很多API的 next_cursor)。

# 4. 游标分页的限制(要接受):
#   - ✗ 不能直接跳到"第N页"(因为它不知道第N页从哪个id开始)。
#   - ✗ 不能简单显示"共X页"(但可单独 COUNT 总数显示总条数)。
#   - ✓ 适合: 无限滚动、信息流、"加载更多"、数据导出(顺序遍历全量)。
#   - ✗ 不适合: 必须让用户跳到任意页码的传统页码器。

# 5. 深分页的"根本之问": 用户真的需要翻到第5万页吗?
#   - 往往不需要! 没人会一页页翻到第5万页。
#   - 深分页请求, 很多是【爬虫/攻击】。可考虑: 限制最大页码、
#     或深页要求更精确的筛选条件, 从产品层面规避深分页。

# 核心: 游标字段要唯一有序有索引(首选主键id), 非唯一字段用(字段,id)组合游标;
#   游标分页不能跳任意页/显示总页数, 适合无限滚动; 也可从产品层面规避深分页需求。

游标分页虽好,实现时也有讲究。一、游标字段必须"唯一 + 有序 + 有索引"——通常用主键 id(自增,天然满足);用 create_time 这类字段做游标要注意它可能不唯一(同一时刻多条记录会漏掉/重复)。二、非唯一字段排序用"组合游标"——按 create_time 排序时加上 id 作为第二排序键(ORDER BY create_time, id,游标条件用 (time, id) 组合),保证全局唯一、不漏不重。三、游标传给前端——返回时带上"下一页游标",可 base64 编码隐藏内部字段(很多 API 的 next_cursor)。四、接受它的限制——不能直接跳到第 N 页、不能简单显示"共 X 页",适合无限滚动/信息流/"加载更多"/数据导出,不适合传统页码器。而最值得思考的是五、深分页的"根本之问":用户真的需要翻到第 5 万页吗?往往不需要!深分页请求很多是爬虫/攻击,可以从产品层面规避(限制最大页码、深页要求更精确筛选)。下面这张图,是这次深分页慢的成因与解法:

第四件事:偏移分页 vs 游标分页对比速查

这次踩坑后,我把两种分页方式的对比整理成一张表,以后选分页方案时对照着看。

这张表,把两种分页的优劣摆清了。结论是:需要传统页码器(跳任意页、显示总页数)且数据量不大,用偏移分页;无限滚动/信息流/大数据量、且只需上下翻,用游标分页(深页恒快)。一个常被忽略的点是:游标分页在"数据动态增删"时还更稳定——偏移分页下,如果你看第 2 页时第 1 页插入/删除了数据,offset 会"错位",导致某条数据重复出现或被跳过;而游标分页基于"具体的值(id)"定位,不受总量变化影响。它给我的启发是:"基于位置(序号/offset)"和"基于值(游标/key)"是两种不同的定位哲学:前者依赖"集合的当前排布"(一变就错位、且定位慢),后者锚定"一个确定的值"(稳定且能借索引快速定位)。很多场景下,用"值"而非"序号"去定位,既更快、又更稳——这个思路,远不止用于分页。

第五件事:分页性能的其他注意点

除了深分页,分页还有几个常见的性能/正确性坑,我一并梳理了。

这张表,把分页里那些"不止深分页"的坑都列了出来。几个高频的:SELECT * 查了大量用不上的列还加重回表(应只查需要的列);没有 ORDER BY 时分页顺序不确定、可能重复或遗漏(分页必须带稳定排序);排序字段无索引会触发 filesort 全表排序;每页都 COUNT(*) 大表也慢(可缓存/估算/只首页算)。它给我的最大启发是:"分页"这个看似最基础、最不起眼的功能,背后其实藏着一连串的性能和正确性考量。很多人(包括曾经的我)觉得分页就是"LIMIT offset, size 一把梭",却不知道这里面有深分页性能、排序稳定性、COUNT 开销、数据动态变化下的一致性等一堆讲究。这让我领悟到:越是"基础、常见、人人都在用"的功能,越值得我们去深究它的实现细节和边界——因为它被调用得太频繁、太广泛,一旦它有性能或正确性问题,影响面就极大;而恰恰是这些"看起来简单到不值得深究"的地方,往往藏着最容易被忽略、也最普遍的坑。对最基础的东西保持敬畏和好奇,把它们真正吃透,是工程师内功的体现。

第六件事:设计分页时,我现在的决策习惯

现在再设计列表分页,我不再无脑 LIMIT offset,而是按这张图先想清楚用什么分页:

这张图的精髓,是"按'要不要跳任意页'和'数据量大不大',选对分页方案"。第一问 "用户需要跳到任意页码吗":不需要(无限滚动/加载更多)就用游标分页(深页恒快);需要传统页码器再看数据量。需要页码器时:数据量小、不会深翻,偏移分页够用;数据量大/可能深翻,用偏移分页 + 延迟关联减少回表,并限制最大页码防爬虫。无论哪种,都要让排序字段有索引、只 SELECT 需要的列、游标字段唯一有序有索引(非唯一用组合游标)。最后一步是我现在的硬习惯:压测深页性能,别只测前几页(这次的坑正是因为只测了前几页就上线)。这套习惯,让我设计分页时,从"LIMIT offset 一把梭"变成了"按场景选对方案、并验证深页"——核心始终是:分页方案要匹配真实的使用场景和数据规模,深分页要用游标分页或专门优化。

我立下的几条规矩

这场"越翻越慢"的事故,换来了我做分页时,刻进骨子里的几条铁律:

1. LIMIT offset 是"读取后丢弃",不是"跳过"。成本 O(offset),深分页必然慢。
2. 深分页优先用游标分页。WHERE id > last_id,利用索引直接定位,每页恒快。
3. 必须跳任意页就用延迟关联。子查询只查主键减少回表,治标但有效。
4. 游标字段要唯一有序有索引。首选主键 id;非唯一字段用 (字段, id) 组合游标。
5. 分页必须带稳定的 ORDER BY 且字段有索引。否则顺序不定 + filesort 慢。
6. 限制最大页码/偏移。从产品层面规避深分页,防爬虫深翻拖垮库。
7. 压测要覆盖深页。别只测前几页就上线,深页才是性能悬崖。

附:游标分页的一段完整后端实现

口说无凭。下面给一段游标分页的完整后端实现(伪 Java/Python 风格),包含游标编解码、组合游标、返回下一页游标,直接抄改即可:

import base64, json

# ====== 游标分页实现(按 create_time 降序, id 作为第二排序键)======
def list_orders(cursor: str = None, size: int = 20):
    """游标分页查询订单。cursor 为 None 表示第一页。"""
    params = []
    where = "WHERE 1=1"

    # 1. 解码游标(拿到上一页最后一条的 create_time 和 id)
    if cursor:
        last = decode_cursor(cursor)   # {"t": "2026-06-01 10:00:00", "id": 12345}
        # 组合游标条件: 保证"按(time,id)排序下, 严格在上次位置之后"
        where += """ AND (create_time < %s
                          OR (create_time = %s AND id < %s))"""
        params += [last["t"], last["t"], last["id"]]

    # 2. 查 size+1 条(多查1条用来判断"还有没有下一页")
    sql = f"""SELECT id, user_id, amount, create_time FROM orders
              {where}
              ORDER BY create_time DESC, id DESC
              LIMIT %s"""
    params.append(size + 1)
    rows = db.query(sql, params)   # ← 走 (create_time, id) 索引, 直接定位, 深页也快

    # 3. 判断是否有下一页, 并生成"下一页游标"
    has_next = len(rows) > size
    rows = rows[:size]             # 去掉多查的那条
    next_cursor = None
    if has_next and rows:
        last_row = rows[-1]
        next_cursor = encode_cursor({"t": str(last_row["create_time"]),
                                     "id": last_row["id"]})

    return {
        "items": rows,
        "next_cursor": next_cursor,  # 前端拿它请求下一页; 为 None 表示没有更多了
        "has_next": has_next,
    }

# ====== 游标编解码: base64(JSON), 隐藏内部字段, 更优雅 ======
def encode_cursor(d: dict) -> str:
    return base64.urlsafe_b64encode(json.dumps(d).encode()).decode()

def decode_cursor(c: str) -> dict:
    return json.loads(base64.urlsafe_b64decode(c.encode()).decode())

# ====== 配套索引(关键!): ======
# CREATE INDEX idx_time_id ON orders (create_time, id);
#   → 让 WHERE + ORDER BY (create_time, id) 能完全走索引、直接定位。

# 用法:
#   第1页: list_orders()
#   下一页: list_orders(cursor=上次返回的 next_cursor)
#   直到 next_cursor 为 None。

# 核心: 游标分页 = 解码上次位置 -> WHERE组合条件直接定位 -> 多查1条判断下一页
#   -> 返回编码游标; 配 (create_time,id) 索引, 无论翻多深每页都恒快。

这段完整实现,把游标分页从"概念"落成了"能直接用的代码"。它包含了游标分页的几个关键工程细节:用 (create_time, id) 组合游标解决排序字段不唯一的问题(create_time < X OR (create_time = X AND id < lastId));多查一条(size + 1)来判断"还有没有下一页";把游标 base64 编码以隐藏内部字段;以及最关键的——配一个 (create_time, id) 的索引让查询能完全走索引直接定位。这,正是我想用这段代码,留给每个做列表接口的人的最后一课:一个看似简单的"分页",要做到"正确 + 高性能 + 任意深度都快",背后需要SQL 写法、索引设计、游标编码、边界处理(判断下一页)的协同配合。它再次印证了我从这次事故里得到的核心领悟:最基础的功能,往往最值得被认真对待;"能用的分页"和"扛得住千万级数据、任意深度都快的分页",中间隔着的,正是对底层原理(LIMIT 的真实成本)的理解、和把这种理解落实到每一个工程细节(游标、索引、组合条件)的功夫。把一件"人人都会做"的小事,做到"在任何规模下都正确且高效",这种对基础的极致打磨,才是真正拉开工程师水平差距的地方。深分页不可怕,可怕的是从没想过它为什么慢、也从没把它当回事——而一旦你理解了、并用对了方法,它就再也不是问题。

写在最后

回头看,这场由"深分页 LIMIT offset"引发的、越翻越慢的事故,真正教给我的,远不止"用游标分页代替偏移分页"这一个技巧。它让我对一个普遍的认知误区有了深刻的警觉:我们常常会"想当然地假设"一个操作的成本,而这个假设,可能与它的真实实现成本大相径庭。我栽就栽在,我"以为" LIMIT 1000000, 20 和 LIMIT 0, 20 成本差不多(都是"取 20 条");却不知道前者实际要"读取并丢弃一百万行",成本是后者的几万倍。我用"它看起来要做的事(取 20 条)"去估算成本,而没有去了解"它实际上是怎么做的(先读一百万行再丢)"。这让我领悟到一个对写出高性能代码至关重要的道理:要对自己代码的性能有正确的判断,就不能停留在"它在逻辑上要完成什么"的层面,而必须深入到"它在底层实际是怎么执行的"的层面;一个 API/语法"看起来简单",不代表它"执行起来廉价"。尤其是数据库、集合、字符串这些我们every day都在用的东西,它们的很多操作,背后的真实成本(时间/空间复杂度)和它的"表面语义"是脱节的(比如 LIMIT offset 的 O(offset)、字符串拼接的 O(n²)、list.contains 的 O(n)……)。所以,对于任何会在"大数据量、高频次"下运行的代码,我都应该养成一个习惯:不满足于"它能跑出正确结果",更要追问"它在最坏/最大规模下,要付出多大的代价",并用 EXPLAIN、性能压测去验证这个代价。看穿表面语义、洞悉真实成本——这,是从"能写出功能"走向"能写出高性能系统"的必经之路。这,是我用一次"越翻越慢"的事故,换来的、关于数据库、也关于"洞悉真实成本"的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写下 LIMIT offset 时,想一想"翻到深页它要趟过多少行",那我对着那条超时的深分页 SQL 熬的这大半天,就值了。

—— 别看了 · 2026