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

郗流婉

在数据量非常大的情况下，深分页查询则变得很常见，深分页会导致MySQL需要扫描大量前面的数据，从而效率低下。例如，使用LIMIT 100000, 10时，MySQL需要扫描前100000条数据才能找到第10000页的数据。
在MySQL中解决深分页问题，可通过以下5种优化方案实现：

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

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

1. SELECT t.*
   
2. FROM user t
   
3. INNER JOIN (
   
4. SELECT id
   
5. FROM user
   
6. ORDER BY sort_field
   
7. LIMIT 100000, 10
   
8. ) AS tmp ON t.id = tmp.id;

复制代码

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

要求：表中存在有序唯一键（如自增ID）
这种方法的原理就是我们在进行范围查询后需要记录页尾的行号，当查询以行号开始的范围数据时直接根据行号匹配，避免了扫描前面的数据。

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

复制代码

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

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

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

复制代码

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

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

1. -- 先获取预估偏移量
   
2. SELECT COUNT(*)
   
3. FROM user
   
4. WHERE sort_field < {target_value};
   
5. 
   
6. 
   
7. -- 再使用延迟关联获取精确数据
   
8. SELECT t.*
   
9. FROM user t
   
10. INNER JOIN (
    
11. SELECT id
    
12. FROM user
    
13. WHERE sort_field < {target_value}
    
14. ORDER BY sort_field
    
15. LIMIT 10
    
16. ) AS tmp ON t.id = tmp.id;

复制代码

方案五：缓存优化 (Caching)

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

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

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

方案传统LIMIT延迟关联有序唯一键书签分页1000页查询耗时2.3s420ms8ms12ms内存占用高中低低
最佳实践建议

1.优先使用有序唯一键分页（如自增ID），时间复杂度从O(n)降至O(1)
2.对高频查询的排序字段建立索引
3.结合业务场景选择方案：

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

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

方法补充

下面小编为大家整理了一些Mysql深度分页优化的其他思路和方案，希望对大家有所帮助
1.普通分页的优化方法
一般分页不是很深的情况下，我们一般可以通过以下方法解决大部分的分页问题
通过增加主键排序，例如：order by id
如果需要根据时间排序，就给常用的字段增加索引，包括时间字段。例如：order by create_time
以上两种手段其实可以解决大部分的分页问题了。但是如果后面的页数很深了，比如从100w条开始取20条，我们就会发现再执行sql语句就会非常慢，这是因为mysql的优化器在发现sql查询的行数超过一定比例的时候，就会自动转换成全表扫描，可以自己模拟数据测试一下。
什么是Mysql的深度分页？
查询偏移量过大的分页的场景我们称为深度分页，例如以下sql语句就是一个典型的深度分页场景

1. SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20

复制代码

2.深度分页的优化方案
强制索引 force index（不推荐）
一开始想着使用force index强制走索引，但是我的leader跟我说过，不建议添加强制索引来进行sql优化，主要有以下几种缺点：

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

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

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

复制代码

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

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

复制代码

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

1. 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过滤优化的方式进行优化，例如：

1. 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深分页内容请搜索晓枫资讯以前的文章或继续浏览下面的相关文章希望大家以后多多支持晓枫资讯！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！