一、OFFSET是个大坑：越往后翻越慢

先看一段最熟悉的分页查询：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 10 OFFSET 100000;

这条SQL的逻辑是：先查100010条，再扔掉前100000条，返回最后10条。

我在一张1000万行的订单表上做过实测，结果触目惊心：

偏移量	耗时	状态
OFFSET 100	1ms	✅ 正常
OFFSET 10000	120ms	⚠️ 警告
OFFSET 100000	1.8s	❌ 不可接受
OFFSET 1000000	12s+	系统崩了

原因很简单：MySQL需要扫描全部100万行数据，然后才给你最后10条。越到后面的页面，扫描的数据越多，速度自然越慢。

二、高手怎么写分页？用“游标”代替“页码”

既然OFFSET的问题在于“扫描太多”，那我们就换一种思路：不跳过，直接定位。

这种写法叫做“游标分页”或“Keyset Pagination” ：

-- 第一页：正常查SELECT * FROM orders WHERE user_id = 100 ORDER BY id DESC LIMIT 10;-- 下一页：记住上一页的最后一条idSELECT * FROM orders WHERE user_id = 100 AND id < 上一页最后一条idORDER BY id DESC LIMIT 10;

核心原理：利用索引直接定位，每次查询只扫描需要的10条数据，和页码无关 。

三、实战案例：从70秒到0.5秒

上个月帮一个电商系统优化订单分页，原SQL长这样：

-- 查第9万页，耗时70秒！SELECT * FROM order_detail WHERE status = 1 ORDER BY id LIMIT 900000, 10;

执行计划显示：扫描90万行，Using filesort 。

优化后的SQL：

-- 先查ID，再关联回表SELECT t.* FROM order_detail tINNER JOIN (    SELECT id FROM order_detail     WHERE status = 1     ORDER BY id     LIMIT 900000, 10) tmp ON t.id = tmp.id;

耗时从70秒降到了0.47秒 。

如果再配合游标写法，效果更夸张：

-- 记住上一页的最大idSELECT * FROM order_detail WHERE status = 1 AND id > 上一页最大idORDER BY id LIMIT 10;

耗时8毫秒，比原来快了将近10000倍 。

四、多个字段排序怎么办？

很多业务需要按时间排序，但时间可能重复。这时候需要用复合游标 ：

-- 第一页SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC, id DESC LIMIT 10;-- 下一页（假设上一页最后一条 create_time='2024-01-01 10:00:00', id=9527）SELECT * FROM orders WHERE user_id = 100 AND (create_time < '2024-01-01 10:00:00'      OR (create_time = '2024-01-01 10:00:00' AND id < 9527))ORDER BY create_time DESC, id DESC LIMIT 10;

这种写法完美利用联合索引，性能依然是毫秒级。

五、还有一种更优雅的写法：延迟关联

如果业务必须保留跳页功能（比如必须能直接跳到第100页），可以用“延迟关联 + 覆盖索引” ：

SELECT * FROM orders tINNER JOIN (    SELECT id FROM orders     WHERE user_id = 100     ORDER BY create_time DESC     LIMIT 90000, 10) tmp ON t.id = tmp.id;

子查询只在索引上扫描，不回表；主查询用主键快速关联。实测1000万数据下，从12秒降到0.8秒 。