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

相关文章

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

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

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

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

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述:数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1

504 Gateway Timeout网关超时的根源及完美解决方法

《504GatewayTimeout网关超时的根源及完美解决方法》在日常开发和运维过程中,504GatewayTimeout错误是常见的网络问题之一,尤其是在使用反向代理(如Nginx)或... 目录引言为什么会出现 504 错误?1. 探索 504 Gateway Timeout 错误的根源 1.1 后端

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景:表空却 “吃满” 磁盘的怪事二、问题复现:一步步编程还原异常场景1. 准备测试源表与数据

python 线程池顺序执行的方法实现

《python线程池顺序执行的方法实现》在Python中,线程池默认是并发执行任务的,但若需要实现任务的顺序执行,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋... 目录方案一:强制单线程(伪顺序执行)方案二:按提交顺序获取结果方案三:任务间依赖控制方案四:队列顺序消

SpringBoot通过main方法启动web项目实践

《SpringBoot通过main方法启动web项目实践》SpringBoot通过SpringApplication.run()启动Web项目,自动推断应用类型,加载初始化器与监听器,配置Spring... 目录1. 启动入口:SpringApplication.run()2. SpringApplicat