Mysql中深分页的五种常用方法整理

目录

• 方案一：延迟关联 (Deferred Join)
• 方案二：有序唯一键分页 (Cursor-based Pagination)
• 方案三：书签分页 (Bookmark Pagination)
• 方案四：预估分页 (Approximate Pagination)
• 方案五：缓存优化 (Caching)
• 性能对比（100万数据测试）
• 最佳实践建议
• 方法补充

在数据量非常大的情况下，深分页查询则变得很常见，深分页会导致mysql需要扫描大量前面的数据，从而效率低下。例如，使用LIMIT 100000, 10时，MySQL需要扫描前100000条数据才能找到第10000页的数据。

在MySQL中解决深分页问题，可通过以下5种优化方案实现：

方案一：延迟关联 (Deferred Join)

原理：先通过子查询获取主键，再关联原表获取完整数据

通常我们直接查询分页较大的数据速率较慢，我们可以选择优先查询主键列，因为其可以通过索引查询且速度最快，然后根据获取的主键匹配对应的数据。

SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
OwdjzdXRDER BY sort_field 
LIMIT 100000, 10
) AS tmp ON t.id = tmp.id;

方案二：有序唯一键分页 (Cursor-based Pagination)

要求：表中存在有序唯一键（如自增ID）

这种方法的原理就是我们在进行范围查询后需要记录页尾的行号，当查询以行号开始的范围数据时直接根据行号匹配，避免了扫描前面的数据。

-- 假设已知上一页最后一条记录的id为12345
SELECT * 
FROM user 
WHERE id > 12345 
ORDER BY id 
LIMIT 10;

方案三：书签分页 (Bookmark Pagination)

原理：记录上一页最后一条数据的排序字段值

-- 假设按create_time排序，上一页最后记录的create_time为'2023-01-01 12:00:00'
SELECT * 
FROM user 
WHERE create_time > '2023-01-01 12:00:00' 
ORDER BY create_time 
LIMIT 10;

方案四：预估分页 (Approximate Pagination)

适用场景：允许误差的近似分页

适用于数据量极大的场景，即主键也不再进行分页查询，而是通过预估得到大致行号的范围，再通过主键匹配数据行（此方案可能会有误差，需要根据场景选择）

-- 先获取预估偏移量
SELECT COUNT(*) 
FROM user 
WHERE sort_field < {target_value};
 
 
-- 再使用延迟关联获取精确数据
SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
WHERE sort_field < {thttp://www.devze.comarget_value} 
ORDER BY sort_field 
LIMIT 10
) AS tmp ON t.id = tmp.id;

方案五：缓存优化 (Caching)

适用场景：高频访问的固定排序分页

• 对常用排序方式预生成分页结果
• 使用Redis等缓存中间结果
• 查询时优先读取缓存数据

性能对比（100万数据测试）

最佳实践建议

1.优先使用有序唯一键分页（如自增ID），时间复杂度从O(n)降至O(1)

2.对高频查询的排序字段建立索引

3.结合业务场景选择方案：

• 实时性要求高 → 方案二/三
• 数据量极大 → 方案四/五
• 允许误差 → 方案四

4.对超过10万条数据的分页需求，建议改用滚动加载（无限下拉）模式

方法补充

下面小编为大家整理了一些Mysql深度分页优化的其他思路和方案，希望对大家有所帮助

1.普通分页的优化方法

一般分页不是很深的情况下，我们一般可以通过以下方法解决大部分的分页问题

通过增加主键排序，例如：order by id

如果需要根据时间排序，就给常用的字段增加索引，包括时间字段。例如：order by create_time

以上两种手段其实可以解决大部分的分页问题了。但是如果后面的页数很深了，比如从100w条开始取20条，我们就会发现再执行sql语句就会非常慢，这是因为mysql的优化器在发现sql查询的行数php超过一定比例的时候，就会自动转换成全表扫描，可以自己模拟数据测试一下。

什么是Mysql的深度分页？

查询偏移量过大的分页的场景我们称为深度分页，例如以下sql语句就是一个典型的深度分页场景

SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20

2.深度分页的优化方案

强制索引 force index（不推荐）

一开始想着使用force index强制走索引，但是我的leader跟我说过，不建议添加强制索引来进行sql优化，主要有以下几种缺点：

• 影响选择性最佳的索引：强制使用索引可能会影响数据库引擎选择性最佳的索引，导致查询性能下降
• 增加更新操作的时间：强制使用索引后，数据库更新操作的时间会增加，因为索引文件需要被更新
• 降低查询的灵活性：如果强制使用索引过于固定，会降低查询的灵活性，不方便后期维护。

ID范围查询

如果那种不需要页码的场景下，比如滑动加载（消息列表这种），还有那种只有上下页按钮点击的网站分页，我们可以通过where id > #{上次查询的最后一条记录的id} 进行优化

# 查询指定 ID 范围的数据
SELECT * FROM t_xxx WHERE id > 1000000 AND id <= 1000020 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询
SELECT * FROM t_xxx WHERE id > 1000000 LIMIT 20

子查询+INNER JOIN

可以先根据时间字段（create_time）或者id排序查询到id，比如：

SELECT id FROM t_xxx ORDER BY create_time DESC LIMIT 1000000,20

这个子查询先查出来，作为临时表，然后再让主表join这个临时表去联表查询需要的t_xxx对应的信息字段，这样也可以达到一个很好的效果，最终sql语句就是这样：

SELECT * FROM t_xxx INNER JOIN (SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,20) AS t_temp ON t_xxx.id = t_temp.id

子查询+ID过滤

也可以通过子查询+ID过滤优化的方式进行优化，例如：

SELECT * FROM t_xxx WHERE name = 'xxx' AND id >(SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,1) ORDER BY id LIMIT 20

到此这篇关于Mysql中深分页的五种常用方法整理的文章就介绍到这了,更多相关Mysql深分页内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多http://www.devze.com支持编程客栈(www.devze.com)！