MySQL 深分页为什么慢？游标分页为什么快？再到 B+ 树索引底层原理

最新推荐文章于 2026-06-17 18:09:57 发布

原创最新推荐文章于 2026-06-17 18:09:57 发布 · 148 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #adb #android

Java开发专栏收录该内容

43 篇文章

订阅专栏

在学习 SQL 调优时，很多人都会遇到一个经典问题：深分页为什么慢？

比如这条 SQL：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
ORDER BY create_time DESC, id DESC
LIMIT 90000, 20;

它的意思是：

跳过前 90000 条数据，再取 20 条数据。

看起来只是取 20 条，为什么会慢？

核心原因是：

MySQL 不能直接“瞬移”到第 90001 条，它需要先扫描、排序或计数前面的 90000 条数据，然后把它们丢掉，最后再返回 20 条。

所以深分页慢，不是慢在返回 20 条，而是慢在前面大量被跳过的数据也需要被处理。

一、LIMIT 两个参数是什么意思？

MySQL 里 LIMIT 有两种常见写法。

第一种：

LIMIT 20;

表示：

取前 20 条。

它等价于：

LIMIT 0, 20;

第二种：

LIMIT 90000, 20;

第一个参数是 offset，表示跳过多少条。

第二个参数是 count，表示取多少条。

所以：

LIMIT 90000, 20;

不是取 90000 条，而是：

跳过前 90000 条，再取 20 条。

二、深分页为什么慢？

假设有一张订单表：

CREATE TABLE t_order (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    create_time DATETIME NOT NULL
) ENGINE=InnoDB;

现在执行：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
ORDER BY create_time DESC, id DESC
LIMIT 90000, 20;

这条 SQL 的逻辑是：

1. 按 create_time DESC, id DESC 排序
2. 跳过前 90000 条
3. 返回后面的 20 条

如果没有合适索引，MySQL 可能需要：

1. 扫描大量数据
2. 对数据按照 create_time 和 id 排序
3. 排序后跳过前 90000 条
4. 返回第 90001 到第 90020 条

用伪代码理解：

List<Order> temp = new ArrayList<>();

for (Order row : t_order所有数据) {
    temp.add(row);
}

temp.sort(按照 create_time DESC, id DESC);

return temp.subList(90000, 90020);

这时候很慢，因为 MySQL 不仅要筛选数据，还要排序，还要丢弃大量无用数据。

如果 EXPLAIN 看到类似：

type: ALL
key: NULL
rows: 1000000
Extra: Using filesort

说明它可能在全表扫描，并且还要额外排序。

其中：

type = ALL：全表扫描
key = NULL：没有用上索引
Using filesort：MySQL 需要额外排序，没有直接利用索引顺序

三、有索引时，深分页还会慢吗？

假设我们创建索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

这个索引的顺序刚好和 SQL 的排序一致：

ORDER BY create_time DESC, id DESC

那么 MySQL 可以沿着索引顺序扫描：

第 1 条
第 2 条
第 3 条
...
第 90000 条，跳过
第 90001 条，返回
...
第 90020 条，返回

这时候它可能不需要 filesort，因为索引本身已经排好序了。

但是问题仍然存在：

即使有索引，LIMIT 90000, 20 仍然要从索引开头扫描并跳过前 90000 条。

所以有索引以后，深分页可能从：

全表扫描 + 排序 + 跳过大量数据

优化成：

索引顺序扫描 + 跳过大量数据

虽然已经快了一些，但依然要扫描很多无用数据。

这就是深分页的本质问题：offset 越大，前面被跳过的数据越多。

四、游标分页是什么？

游标分页，也叫 seek pagination。

它的思想是：

不要每次都从头开始数，而是记录上一页最后一条数据的位置，下一页直接从这个位置后面继续查。

比如第一页：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
ORDER BY create_time DESC, id DESC
LIMIT 20;

假设第一页最后一条是：

create_time = '2025-01-01 12:00:00'
id = 888888

那么下一页就不要写：

LIMIT 20, 20;

而是写：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)
ORDER BY create_time DESC, id DESC
LIMIT 20;

这条 SQL 的意思是：

找排在上一页最后一条后面的数据，然后取 20 条。

五、为什么这里是小于，不是大于？

因为排序是：

ORDER BY create_time DESC, id DESC

也就是倒序。

倒序规则下：

create_time 越大，越靠前；
create_time 相同，id 越大，越靠前。

假设数据顺序是：

create_time              id
2025-01-05 10:00:00      999999
2025-01-04 09:00:00      900000
2025-01-01 12:00:00      888888   ← 第一页最后一条
2025-01-01 12:00:00      888887
2025-01-01 12:00:00      888886
2024-12-31 20:00:00      777777

下一页应该从 888887 开始。

所以条件应该是：

WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)

意思是：

1. 找 create_time 更小的数据；
2. 如果 create_time 相同，就找 id 更小的数据。

如果是升序排序：

ORDER BY create_time ASC, id ASC

那么下一页就应该用：

WHERE create_time > #{lastCreateTime}
   OR (create_time = #{lastCreateTime} AND id > #{lastId})

所以规律是：

DESC 倒序分页：下一页用 <
ASC 升序分页：下一页用 >

更准确地说：

游标分页的条件要和 ORDER BY 的排序方向保持一致。

六、游标分页为什么更快？

游标分页快的前提是：必须有合适的索引。

比如：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

这个索引的顺序就是：

create_time 倒序；
create_time 相同，再按 id 倒序。

索引里的数据大概是：

create_time              id
2025-01-05 10:00:00      999999
2025-01-04 09:00:00      900000
2025-01-01 12:00:00      888888
2025-01-01 12:00:00      888887
2025-01-01 12:00:00      888886
2024-12-31 20:00:00      777777

当执行：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)
ORDER BY create_time DESC, id DESC
LIMIT 20;

MySQL 可以利用索引快速定位到上一页最后一条附近，然后沿着索引继续向后扫描，扫够 20 条就停止。

它不需要从第一页重新数到第 90000 条。

所以深分页和游标分页的区别是：

深分页：
从头开始扫，跳过前 offset 条，再取 count 条。

游标分页：
从上一页最后一条的位置继续往后扫，扫够 count 条就停止。

这就是游标分页快的原因。

七、如果没有索引，游标分页还快吗？

不一定。

这是非常关键的一点。

如果没有这个索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

那么游标分页 SQL：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)
ORDER BY create_time DESC, id DESC
LIMIT 20;

仍然可能变成：

1. 扫描全表
2. 判断 WHERE 条件
3. 把符合条件的数据拿出来
4. 按 create_time DESC, id DESC 排序
5. 取前 20 条

伪代码类似：

List<Order> temp = new ArrayList<>();

for (Order row : t_order所有数据) {
    if (row.createTime < '2025-01-01 12:00:00'
        || (row.createTime == '2025-01-01 12:00:00' && row.id < 888888)) {
        temp.add(row);
    }
}

temp.sort(按照 create_time DESC, id DESC);

return temp前20条;

所以必须强调：

游标分页不是因为多写了 WHERE 就快，而是因为这个 WHERE 条件能够配合索引，变成索引范围扫描。

如果没有索引，它仍然可能全表扫描。

八、B+ 树索引到底怎么存储？

要理解游标分页为什么能“从某个位置继续往后扫”，就要理解 MySQL InnoDB 的 B+ 树索引结构。

InnoDB 的索引底层主要是 B+ 树。

B+ 树不是二叉树，不是一个节点只有左孩子和右孩子。

B+ 树是一种多叉平衡搜索树。

它的特点是：

1. 一个节点里可以存很多索引值；
2. 一个节点可以指向很多个子节点；
3. 非叶子节点只负责导航；
4. 叶子节点存真正的数据或主键值；
5. 叶子节点之间通过链表连接，适合范围查询。

九、B+ 树的节点是什么？

在 InnoDB 里，可以把 B+ 树的一个节点理解成一个数据页。

默认情况下，一个页大小通常是 16KB。

一个页里不是只存一个值，而是可以存很多索引记录。

比如一个非叶子节点可能长这样：

[100 | 300 | 600 | 900]

这些值都是排好序的。

它们像目录一样，把数据范围分成很多段：

小于 100 的，去第 1 个子节点找；
100 到 299 的，去第 2 个子节点找；
300 到 599 的，去第 3 个子节点找；
600 到 899 的，去第 4 个子节点找；
大于等于 900 的，去第 5 个子节点找。

所以 B+ 树不是：

左子树 / 右子树

而是：

第 1 个子节点 / 第 2 个子节点 / 第 3 个子节点 / 第 4 个子节点 / ...

它是多叉树。

十、非叶子节点存什么？

非叶子节点存的是：

索引值 + 指向下一层页的指针

也就是说，非叶子节点本质上是目录页。

比如主键索引 PRIMARY KEY(id) 的非叶子节点可能存：

300 -> page_10
600 -> page_20
900 -> page_30

它的作用是告诉 MySQL：

你要找 id = 520，应该去 page_20 继续找。

非叶子节点不存完整行数据。

如果非叶子节点也存完整行，会导致每个节点能放的数据变少，树变高，磁盘 IO 次数变多，查询性能反而下降。

所以 B+ 树的设计思想是：

非叶子节点尽量小，只负责指路；叶子节点才保存真正的数据。

十一、叶子节点存什么？

InnoDB 里要区分两类索引：

1. 主键索引，也叫聚簇索引；
2. 二级索引，也叫普通索引、辅助索引。

1. 主键索引的叶子节点

如果表有主键：

PRIMARY KEY(id)

那么 InnoDB 会按照 id 建一棵主键 B+ 树。

主键索引的叶子节点存的是：

完整的一整行数据

比如：

id = 100 -> 这一行订单完整数据
id = 200 -> 这一行订单完整数据
id = 300 -> 这一行订单完整数据

完整行数据包括：

id
order_no
user_id
shop_id
total_amount
create_time
...

所以 InnoDB 的表数据本身就是存放在主键索引的叶子节点上的。

这就是聚簇索引。

2. 二级索引的叶子节点

比如创建普通索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

它会额外建立一棵 B+ 树。

这棵树按照：

create_time DESC
id DESC

排序。

二级索引的叶子节点一般存的是：

索引字段值 + 主键值

在这个例子里，索引字段本身就是：

create_time
id

其中 id 又是主键。

如果创建的是：

CREATE INDEX idx_order_user_id
ON t_order(user_id);

那么二级索引叶子节点存的就是：

user_id + 主键 id

为什么二级索引要存主键 id？

因为如果查询需要整行数据，MySQL 可以先通过二级索引找到主键 id，再拿主键 id 回到主键索引里查完整行。

这个过程叫：

回表

十二、用 create_time + id 索引举例

创建索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

假设有这些数据：

id        create_time
100       2025-01-05 10:00:00
200       2025-01-04 09:00:00
300       2025-01-01 12:00:00
400       2025-01-01 12:00:00
500       2024-12-31 20:00:00

因为索引是：

create_time DESC, id DESC

所以二级索引叶子节点里的顺序大概是：

create_time              id
2025-01-05 10:00:00      100
2025-01-04 09:00:00      200
2025-01-01 12:00:00      400
2025-01-01 12:00:00      300
2024-12-31 20:00:00      500

注意：

create_time 相同的时候，id 大的排前面。

因为 id DESC。

这棵 B+ 树的非叶子节点存的是类似：

索引边界值 + 子页指针

叶子节点存的是：

create_time + id

叶子节点之间还有链表连接：

[2025-01-05,100] -> [2025-01-04,200] -> [2025-01-01,400] -> [2025-01-01,300] -> [2024-12-31,500]

所以当执行：

ORDER BY create_time DESC, id DESC
LIMIT 20;

MySQL 可以直接沿着这个索引顺序扫描，不需要额外排序。

当执行游标分页：

WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)
ORDER BY create_time DESC, id DESC
LIMIT 20;

MySQL 可以利用 B+ 树定位到对应位置附近，然后沿着叶子节点链表继续扫描，取够 20 条就停止。

这就是游标分页快的底层原因。

十三、什么是索引值？

索引值就是你创建索引时指定字段的值。

比如：

CREATE INDEX idx_user_phone
ON t_user(phone);

这个索引的索引值就是：

phone 字段的值

比如：

'13000010001'
'13000010002'
'13000010003'

如果是联合索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

它的索引值就是：

(create_time, id)

比如：

('2025-01-05 10:00:00', 100)
('2025-01-04 09:00:00', 200)
('2025-01-01 12:00:00', 400)

联合索引的排序规则是：

先按第一个字段排；
第一个字段相同，再按第二个字段排；
第二个字段相同，再按第三个字段排；
依次类推。

这就是最左前缀原则的底层基础。

十四、为什么 B+ 树适合范围查询？

B+ 树的叶子节点之间是有序链表。

比如：

[1, 2, 3] -> [4, 5, 6] -> [7, 8, 9] -> [10, 11, 12]

如果查：

WHERE id BETWEEN 4 AND 10

MySQL 可以：

1. 先通过非叶子节点快速定位到 id = 4 附近；
2. 然后沿着叶子节点链表继续往后扫；
3. 扫到 id = 10 停止。

这就非常适合范围查询。

游标分页也是类似思想：

先定位到上一页最后一条附近；
再沿着叶子节点链表继续扫描；
扫够 LIMIT 20 就停。

十五、深分页与游标分页的最终对比

深分页：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
ORDER BY create_time DESC, id DESC
LIMIT 90000, 20;

执行特点：

从头开始扫描；
跳过前 90000 条；
再返回 20 条。

即使有索引，也要从索引开头数过 90000 条。

游标分页：

SELECT
    id,
    order_no,
    total_amount,
    create_time
FROM t_order
WHERE create_time < '2025-01-01 12:00:00'
   OR (create_time = '2025-01-01 12:00:00' AND id < 888888)
ORDER BY create_time DESC, id DESC
LIMIT 20;

执行特点：

从上一页最后一条后面继续扫描；
取够 20 条就停止。

前提是有索引：

CREATE INDEX idx_order_create_time_id
ON t_order(create_time DESC, id DESC);

否则它仍然可能全表扫描。

十六、面试可以这样回答

如果面试官问：深分页为什么慢？怎么优化？

可以这样回答：

深分页慢的原因是 offset 太大。比如 LIMIT 90000, 20，MySQL 需要先找到前 90020 条数据，然后丢弃前 90000 条，只返回 20 条。前面被丢弃的数据也要扫描、排序或计数，所以 offset 越大越慢。
优化方式可以使用游标分页，也就是记录上一页最后一条数据的排序字段和主键，比如 create_time 和 id，下一页通过 WHERE create_time < lastCreateTime OR (create_time = lastCreateTime AND id < lastId) 继续往后查。
但游标分页快的前提是要有和排序字段一致的联合索引，比如 (create_time DESC, id DESC)。如果没有索引，游标分页也可能全表扫描，并不会真正快。
它快的底层原因是 B+ 树索引本身已经按照 create_time 和 id 排好序，MySQL 可以通过索引快速定位到上一页最后一条附近，然后沿着叶子节点链表继续扫描，扫够 LIMIT 20 就停止。

如果面试官继续问：B+ 树内部怎么存？

可以这样回答：

B+ 树不是二叉树，而是多叉平衡搜索树。一个节点里可以存很多有序的索引值，并指向多个子节点。
在 InnoDB 中，一个 B+ 树节点可以理解成一个数据页。非叶子节点存的是索引值和子页指针，主要负责导航；叶子节点存真正的数据。
主键索引的叶子节点存完整行数据，所以主键索引也叫聚簇索引。二级索引的叶子节点存索引字段值和主键值，如果查询需要其他字段，就要通过主键值回到主键索引中查完整行，这个过程叫回表。
B+ 树的叶子节点之间有链表，所以非常适合范围查询和游标分页这类场景。

十七、总结

深分页慢的本质：

offset 太大，前面被跳过的数据也要被处理。

游标分页快的本质：

记录上一页最后一条的位置，下一页从这个位置继续查。

但是必须强调：

游标分页快，不是因为多写了 WHERE 条件；
而是因为 WHERE 条件可以配合索引，变成索引范围扫描。

B+ 树索引的本质：

非叶子节点存索引值和指针，负责导航；
叶子节点存真实索引记录；
主键索引叶子节点存整行数据；
二级索引叶子节点存索引字段值和主键值；
叶子节点之间有链表，适合范围扫描。

所以 SQL 调优时，不能只背“加索引”，而要理解：

索引本身就是一棵按照字段值排好序的 B+ 树。
如果 SQL 的 WHERE、ORDER BY、LIMIT 能利用这棵树的顺序，就能少扫描、少排序、少回表，从而提升性能。