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

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

比如这条 SQL：

SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,20;

它的意思是：

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

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

核心原因是：

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

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

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

MySQL 里LIMIT有两种常见写法。

第一种：

LIMIT20;

表示：

取前 20 条。

它等价于：

LIMIT0,20;

第二种：

LIMIT90000,20;

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

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

所以：

LIMIT90000,20;

不是取 90000 条，而是：

跳过前 90000 条，再取 20 条。

二、深分页为什么慢？

假设有一张订单表：

CREATETABLEt_order(idBIGINTPRIMARYKEY,order_noVARCHAR(64)NOTNULL,total_amountDECIMAL(10,2)NOTNULL,create_timeDATETIMENOTNULL)ENGINE=InnoDB;

现在执行：

SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,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=newArrayList<>();for(Orderrow:t_order所有数据){temp.add(row);}temp.sort(按照 create_timeDESC,idDESC);returntemp.subList(90000,90020);

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

如果EXPLAIN看到类似：

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

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

其中：

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

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

假设我们创建索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

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

ORDERBYcreate_timeDESC,idDESC

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

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

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

但是问题仍然存在：

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

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

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

优化成：

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

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

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

四、游标分页是什么？

游标分页，也叫 seek pagination。

它的思想是：

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

比如第一页：

SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT20;

假设第一页最后一条是：

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

那么下一页就不要写：

LIMIT20,20;

而是写：

SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;

这条 SQL 的意思是：

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

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

因为排序是：

ORDERBYcreate_timeDESC,idDESC

也就是倒序。

倒序规则下：

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开始。

所以条件应该是：

WHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)

意思是：

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

如果是升序排序：

ORDERBYcreate_timeASC,idASC

那么下一页就应该用：

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

所以规律是：

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

更准确地说：

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

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

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

比如：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

这个索引的顺序就是：

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

当执行：

SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;

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

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

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

深分页： 从头开始扫，跳过前 offset 条，再取 count 条。 游标分页： 从上一页最后一条的位置继续往后扫，扫够 count 条就停止。

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

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

不一定。

这是非常关键的一点。

如果没有这个索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

那么游标分页 SQL：

SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;

仍然可能变成：

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

伪代码类似：

List<Order>temp=newArrayList<>();for(Orderrow:t_order所有数据){if(row.createTime<'2025-01-0112:00:00'||(row.createTime=='2025-01-0112:00:00'&&row.id<888888)){temp.add(row);}}temp.sort(按照 create_timeDESC,idDESC);returntemp前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. 主键索引的叶子节点

如果表有主键：

PRIMARYKEY(id)

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

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

完整的一整行数据

比如：

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

完整行数据包括：

id order_no user_id shop_id total_amount create_time ...

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

这就是聚簇索引。

2. 二级索引的叶子节点

比如创建普通索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

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

这棵树按照：

create_time DESC id DESC

排序。

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

索引字段值 + 主键值

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

create_time id

其中id又是主键。

如果创建的是：

CREATEINDEXidx_order_user_idONt_order(user_id);

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

user_id + 主键 id

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

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

这个过程叫：

回表

十二、用 create_time + id 索引举例

创建索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

假设有这些数据：

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_timeDESC,idDESC

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

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]

所以当执行：

ORDERBYcreate_timeDESC,idDESCLIMIT20;

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

当执行游标分页：

WHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;

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

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

十三、什么是索引值？

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

比如：

CREATEINDEXidx_user_phoneONt_user(phone);

这个索引的索引值就是：

phone 字段的值

比如：

'13000010001' '13000010002' '13000010003'

如果是联合索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

它的索引值就是：

(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]

如果查：

WHEREidBETWEEN4AND10

MySQL 可以：

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

这就非常适合范围查询。

游标分页也是类似思想：

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

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

深分页：

SELECTid,order_no,total_amount,create_timeFROMt_orderORDERBYcreate_timeDESC,idDESCLIMIT90000,20;

执行特点：

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

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

游标分页：

SELECTid,order_no,total_amount,create_timeFROMt_orderWHEREcreate_time<'2025-01-01 12:00:00'OR(create_time='2025-01-01 12:00:00'ANDid<888888)ORDERBYcreate_timeDESC,idDESCLIMIT20;

执行特点：

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

前提是有索引：

CREATEINDEXidx_order_create_time_idONt_order(create_timeDESC,idDESC);

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

十六、面试可以这样回答

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

可以这样回答：

深分页慢的原因是 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 能利用这棵树的顺序，就能少扫描、少排序、少回表，从而提升性能。