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

2025-03-25 15:50

本文主要是介绍Mysql中深分页的五种常用方法整理,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

《Mysql中深分页的五种常用方法整理》在数据量非常大的情况下,深分页查询则变得很常见,这篇文章为大家整理了5个常用的方法,文中的示例代码讲解详细,大家可以根据自己的需求进行选择...

在数据量非常大的情况下,深分页查询则变得很常见,深分页会导致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.chinasem.cnarget_value} 
ORDER BY sort_field 
LIMIT 10
) AS tmp ON t.id = tmp.id;

方案五:缓存优化 (Caching)

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

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

性能对比(100万数据测试)

方案传统LIpythonMIT延迟关联有序唯一键书签分页
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查询的行数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深分页内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多http://www.chinasem.cn支持编程China编程(www.chinasem.cn)!

这篇关于Mysql中深分页的五种常用方法整理的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1153927

相关文章

PHP轻松处理千万行数据的方法详解

《PHP轻松处理千万行数据的方法详解》说到处理大数据集,PHP通常不是第一个想到的语言,但如果你曾经需要处理数百万行数据而不让服务器崩溃或内存耗尽,你就会知道PHP用对了工具有多强大,下面小编就... 目录问题的本质php 中的数据流处理:为什么必不可少生成器:内存高效的迭代方式流量控制:避免系统过载一次性

MyBatis分页查询实战案例完整流程

《MyBatis分页查询实战案例完整流程》MyBatis是一个强大的Java持久层框架,支持自定义SQL和高级映射,本案例以员工工资信息管理为例,详细讲解如何在IDEA中使用MyBatis结合Page... 目录1. MyBATis框架简介2. 分页查询原理与应用场景2.1 分页查询的基本原理2.1.1 分

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》:本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》:本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏

python获取指定名字的程序的文件路径的两种方法

《python获取指定名字的程序的文件路径的两种方法》本文主要介绍了python获取指定名字的程序的文件路径的两种方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要... 最近在做项目,需要用到给定一个程序名字就可以自动获取到这个程序在Windows系统下的绝对路径,以下

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定

JavaScript中的高级调试方法全攻略指南

《JavaScript中的高级调试方法全攻略指南》什么是高级JavaScript调试技巧,它比console.log有何优势,如何使用断点调试定位问题,通过本文,我们将深入解答这些问题,带您从理论到实... 目录观点与案例结合观点1观点2观点3观点4观点5高级调试技巧详解实战案例断点调试:定位变量错误性能分

Python中 try / except / else / finally 异常处理方法详解

《Python中try/except/else/finally异常处理方法详解》:本文主要介绍Python中try/except/else/finally异常处理方法的相关资料,涵... 目录1. 基本结构2. 各部分的作用tryexceptelsefinally3. 执行流程总结4. 常见用法(1)多个e

JavaScript中比较两个数组是否有相同元素(交集)的三种常用方法

《JavaScript中比较两个数组是否有相同元素(交集)的三种常用方法》:本文主要介绍JavaScript中比较两个数组是否有相同元素(交集)的三种常用方法,每种方法结合实例代码给大家介绍的非常... 目录引言:为什么"相等"判断如此重要?方法1:使用some()+includes()(适合小数组)方法2

SpringBoot 获取请求参数的常用注解及用法

《SpringBoot获取请求参数的常用注解及用法》SpringBoot通过@RequestParam、@PathVariable等注解支持从HTTP请求中获取参数,涵盖查询、路径、请求体、头、C... 目录SpringBoot 提供了多种注解来方便地从 HTTP 请求中获取参数以下是主要的注解及其用法:1