一、两种写法，等价

取前三行：limit 3。 取第 4 到第 6 行，两种写法都行：

SELECT id, name FROM stu ORDER BY id LIMIT 3 OFFSET 3;
SELECT id, name FROM stu ORDER BY id LIMIT 3, 3;

Demo 里这两句的结果一模一样，都是 4 吴四、5 郑五、6 王六。 逗号版的规矩是「跳过几行, 取几行」—— 第一个数是偏移量，第二个是条数。 顺序和英文读法一致（skip, take），但和 offset 版的语序相反。 所以「每页 10 条、第 3 页」有两种算法：limit 10 offset 20，或者 limit 20, 10。 两个数字位置完全不同，这就是搞反的根源。 建议统一用 offset 版—— 参数顺序和「跳过、取几条」的思考顺序一致，不容易错。

二、错法一：两个数搞反

想「每页 6 条」却写成 limit 1, 6， Demo 里取出来是 2 到 7 这六行——第一页就少一个人，后面全错位。 这种错不报错，只是数据不对。 所以分页写完一定要拿「第一页」和「最后一页」各对一眼： 第一页该有第一条，最后一页该有最后一条。 两头都对，中间一般就不会错。

三、错法二：翻过头以为出错

表里十行、每页三条，第 4 页只拿到 1 条， 第 5 页一条也没有。 两句都不报错，都是正常返回。 这是分页最常见的一种「看着没问题」的 bug： 页面显示「没有数据」，实际是页码算错了。 用户看到的是「空的」，你看到的是「没报错」，两边都以为正常。 排查这类问题的第一步：把 count(*)、offset、limit 三个数打出来， 手算一遍该返回几行。 别在页面上猜。

四、错法三：不带 order by

这是三个错里最隐蔽的一个。 SQL 不承诺「不排序时按什么顺序给」——数据怎么存，就怎么给。 Demo 里专门造了一张按主键聚簇存表（without rowid）， 插入顺序是 c、a、b、d，取出来却是 a、b、c、d。 它按主键存，就按主键给。 这时候谈「第 1 页、第 2 页」根本没有意义——「第几行」本身就没有定义。 更能说明问题的是插一行之后： 上一轮的头两条是 a 和 b，插了一行 aa 之后变成 a 和 aa， b 被挤到下一页去了。 用户翻页时会看到 b 两次，或者一次都没看到。 这就是「翻页重复／丢数据」的根。 规矩很简单： 只要用 limit，就必须带 order by。 而且排序字段要能唯一确定顺序—— 按 score 排、一群人同分时，两次请求里同分行可能换位置， 一样会漏。 补一个唯一的列做后备就行：order by score desc, id desc。

五、总页数怎么算

分页界面上要两个数：当前第几页、一共几页。 后一个得先数总数再除：count(*) 拿到总数，除以每页条数，向上取整。 注意别在 SQL 里算页数。 SQL 的整数相除通常直接砍掉小数—— Demo 里 8 / 3 给 2，不是 2.67。 拿它当页数，8 条数据分 3 条一页会算成 2 页，最后一页那 2 条就丢了。 正确做法是向上取整：程序里 ceil(总数 / 每页条数)； SQL 里也能写 (总数 + 每页 - 1) / 每页， 靠整数除法凑出向上的效果。 这个式子的道理是「先补到整页，再整除」，值得记一下。 还有一个边界：总数是 0 的时候，ceil(0 / 10) 是 0 页， 但界面应该显示「第 1 页，共 1 页」， 不然分页器会算出一堆负数页码。 这种边界没人测试，但用户一定会碰到。

六、深翻页为什么突然变慢

Demo 造了 5 万行，从三个位置各取 10 条，每个跑 100 次取最快。 结果很说明问题： offset 到 0 时是零点零几毫秒，到 10000 是零点二几， 到 49990 是一毫秒出头。 越往后越慢，而且是线性的。 执行计划只有一行：scan big。 它在扫全表。 这里有个特别容易误解的地方：limit 10 offset 49990 不是「跳到第 49991 行」， 是「数过 49990 行，再取 10 行」。 偏移量越大，要数的行越多。 每一页都把前面的行重新数一遍。 所以「第 5000 页」和「第 1 页」的成本差了三个数量级。 数据量再大一点、磁盘不是内存，这个差距会变成几秒钟——用户点「下一页」转圈转到以为卡死了。 要补一句公道话：这次 5 万行全在内存里，数一遍很快， 所以绝对耗时只有一毫秒多。 慢是相对的—— 换成磁盘上五千万行的表，同样的语句会慢到不能用。

七、键集分页：记住上一页最后那个值

换一个问法：不问「第几页」，问「id 大于多少的，取 10 条」。

SELECT id FROM big WHERE id > 49990 ORDER BY id LIMIT 10;

它不需要知道前面有多少行，所以偏移量再深也一样的快。 Demo 里的执行计划换成了： search big using integer primary key (rowid>?)—— scan 变成了 search，一个词就说明白了。 它按主键直接定位到起点，然后往后读 10 行就停。 耗时对比： 键集写法零点零几毫秒，offset 写法一毫秒出头。 这个倍率只能当方向看—— 键集那次已经贴着计时器的分辨率了，再跑一遍可能差 80 倍，也可能差 200 倍。 能确定的只有一件事： offset 越大越慢，键集分页基本不随深度变慢。 方向对了，具体数字不必较真。

八、键集分页的代价

第一个代价：不能「跳到第 37 页」。 你只能一页一页往后翻， 因为每页都依赖上一页的最后一个 id。 没有上一页，就没有下一页。 所以常规做法是两套混用：用户一路往下滚的列表（信息流、评论、日志）用键集； 后台里需要「跳到第 20 页」的地方用 offset。 场景不同，选法不同。 第二个代价：排序字段得唯一。 按 score 排、一群人同分时，「上一页最后那个值」就指不清—— where score < 88 会把所有 88 分的都跳过，或者都重复。 补一个唯一列做后备就行： order by score desc, id desc，同分也不会乱。 第三个代价：第一页拿不到总数。 键集分页只知道「还有没有下一页」， 不知道「一共几页」。 要显示总数就得另外跑一次 count(*)， 而那个 count 在大表上本身就慢。 很多产品的做法是干脆不显示总页数， 只给一个「加载更多」——就是为了省掉这次 count。

九、动手 Demo：从「翻过头」到「深翻页」，一路跑到底

Demo 分两半：前半张十行的小表看分页的语法和三个坑，后半造 5 万行看「深翻页」到底慢在哪。 前半段里那个 without rowid 的表是自己加的—— 只有把表换成「按主键聚簇」的存储方式，才能让「不排序时顺序会变」这件事在现场发生。 普通的主键表在同一个小库里顺序太稳定，演示不出来。

import sqlite3
import time

c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE big(id INTEGER PRIMARY KEY, v TEXT)")
c.executemany("INSERT INTO big(v) VALUES (?)",
               [("x",) for _ in range(50000)])
c.commit()


def bench(sql, n=100):
    best = 1e9
    for _ in range(n):
        t = time.perf_counter()
        c.execute(sql).fetchall()
        best = min(best, time.perf_counter() - t)
    return best * 1000


# 1) 深翻页：数过 49990 行再取 10 行
deep = "SELECT id FROM big ORDER BY id LIMIT 10 OFFSET 49990"
print(bench(deep))
for row in c.execute("EXPLAIN QUERY PLAN " + deep):
    print(row)

# 2) 键集分页：直接按主键定位
keyset = "SELECT id FROM big WHERE id > 49990 ORDER BY id LIMIT 10"
print(bench(keyset))
for row in c.execute("EXPLAIN QUERY PLAN " + keyset):
    print(row)

实跑输出：

--------------------------------------------------------------------
第八十二课：分页查询——`limit` 的两个数字别搞反
--------------------------------------------------------------------

  先备一张十行的表
  SQL> SELECT id, name FROM stu ORDER BY id
  id     name
  ------------
  1      陈一
  2      林二
  3      周三
  4      吴四
  5      郑五
  6      王六
  7      冯七
  8      褚八
  9      卫九
  10     蒋十
  （10 行）
  **十个学生，一人一行。**

  两种写法，等价
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 3
  id     name
  ------------
  1      陈一
  2      林二
  3      周三
  （3 行）
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 3 OFFSET 3
  id     name
  ------------
  4      吴四
  5      郑五
  6      王六
  （3 行）
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 3, 3
  id     name
  ------------
  4      吴四
  5      郑五
  6      王六
  （3 行）
  **后两句一模一样。** **`limit 3, 3` 的意思是「跳过 3 行、取 3 行」。**
  **所以第一个数字是「跳过几行」，第二个是「取几行」。**
  **`offset` 写在后面的写法更好读**，**推荐这个。**

  搞反了会怎样
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 1, 6
  id     name
  ------------
  2      林二
  3      周三
  4      吴四
  5      郑五
  6      王六
  7      冯七
  （6 行）
  **想「每页 6 条」却写成 `limit 1, 6`**，
  **结果从第 2 行开始取 6 条**——**第一页就少一个人，后面全错位。**
  **这种错不会报错，只是数据不对。** **所以分页写完一定要拿「第一页」和「最后一页」对一眼。**

  翻过头不报错，只是给你空
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 3 OFFSET 9
  id     name
  ------------
  10     蒋十
  （1 行）
  SQL> SELECT id, name FROM stu ORDER BY id LIMIT 3 OFFSET 12
  id     name
  ------------
  （0 行）
  **取第 4 页只拿到 1 条，取第 5 页一条也没有。**
  **两句都不报错。** **分页最常见的一种「看着没问题」的 bug 就是这样**：
  **页面显示「没有数据」，实际是页码算错了。**

  不带 order by 的分页是耍流氓
  **前面每句都写了 `order by id`**，**这不是可有可无的。**
  **SQL 不承诺「不排序时按什么顺序给」**，**数据怎么存，就怎么给。**
  **下面这张表按主键聚簇存**（`without rowid`），**插入顺序和取出来的顺序完全不一样：**
  **插入顺序是 `c, a, b, d`：**
  SQL> SELECT code, v FROM w
  code     v
  --------------
  a        一
  b        二
  c        三
  d        四
  （4 行）
  **取出来是 `a, b, c, d`**——**它按主键存，就按主键给。**
  **这时候谈「第 1 页、第 2 页」是没有意义的**，
  **因为「第几行」本身就没有定义。**

  **更能说明问题的是插一行之后：**
  SQL> SELECT code, v FROM w LIMIT 2
  code     v
  --------------
  a        一
  aa       新
  （2 行）
  **上一轮的头两条是 `a` 和 `b`**，**插了一行 `aa` 之后变成 `a` 和 `aa`**——
  **`b` 被挤到下一页去了。**
  **用户翻页时会看到 `b` 两次**（第 2 页、第 3 页都有），
  **或者一次都没看到。** **这就是「翻页重复/丢数据」的根。**

  **规矩很简单**：**只要用 `limit`，就必须带 `order by`。** **而且排序字段要能唯一确定顺序**，
  **不然同分的行在两次请求里可能换位置，一样会漏。**

  总页数怎么算
  **分页要两个数**：**当前第几页**、**一共几页。** **后一个得先数总数再除。** **现在表里 10 行。**
  **每页 3 条 → 共 4 页，最后一页 1 条。**
  **每页 4 条 → 共 3 页，最后一页 2 条。**
  **每页 5 条 → 共 2 页，最后一页 5 条。**
  **另外，页数别在 SQL 里算。** **SQL 的整数相除通常直接砍掉小数：**
  SQL> SELECT 8 / 3 AS 整数相除, 8.0 / 3 AS 带小数相除
  整数相除     带小数相除
  ------------------------
  2            2.67
  （1 行）
  **`8 / 3` 给 `2`，不是 `2.67`。**
  **拿它当页数**：**8 条数据、每页 3 条会算成 2 页**，**最后一页那 2 条就丢了。**
  **正确做法是向上取整**：**程序里 `ceil(总数 / 每页条数)`。**
  **SQL 里也能写**：`(总数 + 每页 - 1) / 每页`，**靠整数除法凑出向上的效果。**

  翻到第 5000 页，为什么突然变慢
  **造 5 万行**，**然后从三个位置各取 10 条**，**每个跑 100 次取最快：**

  **offset 0      → 0.01 毫秒**
  **offset 10000  → 0.23 毫秒**
  **offset 49990  → 1.10 毫秒**

  **offset 越大越慢，但慢得不算狠**——**5 万行都在内存里，数一遍很快。**
  **看执行计划，问题就露出来了：**
  | SCAN big
  **`scan big`**：**它得从头数。**
  **`limit 10 offset 49990` 不是「跳到第 49991 行」**，**是「数过 49990 行，再取 10 行」。**
  **每往后翻一页，前面那些行都要重新数一遍。** **这就是「深翻页」慢的原因。**

  键集分页：记住上一页最后那个 id
  **换个问法**：**「id 大于 49990 的，按 id 排取 10 条」。** **它不需要知道前面有多少行。**
  | SEARCH big USING INTEGER PRIMARY KEY (rowid>?)
  **`search big using integer primary key (rowid>?)`**——**直接按主键定位。**

  **键集写法 0.01 毫秒，offset 写法 1.10 毫秒**，差 134 倍。
  **这个倍率只能当方向看**——**键集那次已经贴着计时器的分辨率了。**
  **再跑一遍可能差 80 倍，也可能差 200 倍**，**小数位一动，倍率就翻。**
  **能确定的只有一件事**：**offset 越大越慢**，**键集分页基本不随深度变慢。**

  键集分页的代价
  **它不能「跳到第 37 页」。** **只能一页一页往后翻，因为每页都依赖上一页的最后一个 id。**
  **所以常规做法是两套混用**：**用户一路往下滚的列表用键集**，
  **后台里需要「跳到第 20 页」的地方用 offset。**
  **还有一个坑：排序字段得唯一。** **按 `score` 排、一群人同分时，「上一页最后那个值」就指不清。**
  **补一个唯一列做后备**：`order by score desc, id desc`，**同分也不会乱。**

limit 3、limit 3 offset 3、limit 3, 3 三条，后两条结果完全一样（4、5、6）。 逗号版第一个数是跳过行数，别搞反。 limit 1, 6 取出来的第一行是 2 林二，不是 1 陈一——这就是「搞反」的现场。 第 4 页只回 1 行、第 5 页回 0 行，都不报错。 页面显示「没有数据」，实际是页码算过头了。 without rowid 那张表，插入是 c、a、b、d，取出是 a、b、c、d。 插一行 aa 之后， limit 2 从 a、b 变成 a、aa，b 被挤走了——这就是翻页丢数据的根。 5 万行那半段：三个 offset 的耗时是递增的，执行计划是 scan big； 改成键集写法后计划变成 search big using integer primary key (rowid>?)。 一个 scan 一个 search，差别就在这。 最后那句倍率别当结论——键集那次快到 0.01 毫秒，已经贴着计时器的分辨率， 重跑一次数字就可能翻倍。 看方向，别看小数。

30 秒实验

1. 把 Demo 里 limit 3, 3 改成 limit 3, 2，结果会少哪一行？
2. 给 stu 表按 name 建一个索引，再跑一次不带 order by 的 select name from stu limit 4， 顺序会不会变？
3. 把 where id > 49990 换成 where id >= 49990，翻页会出现什么问题？

常见误区

1. 把 limit 1, 6 当成「每页 6 条的第 1 页」。 逗号版第一个数是跳过行数， 第一页应该写 limit 0, 6。
2. 分页不带 order by。 顺序不保证，翻页会重复或漏数据，而且一句错都不报。
3. 在 SQL 里用 总数 / 每页 算页数。 整数相除直接砍小数，最后一页会丢，应该向上取整。

语法和坑都说清了，下一篇换个角度：18.3 表的设计——先想清楚再动手建。

• ☐ （本篇完，下一篇 18.3 表的设计）

相关笔记

• 创建索引
• 系统数据库
• 🦏 MySQL即学即用 · 索引