MySQL分页查询详解:优化大数据集的LIMIT和OFFSET

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL的分页查询是处理大量数据集的常见需求,了解`LIMIT`和`OFFSET`关键字的用法可以帮助您有效地实现分页功能。同时,性能优化也是确保查询高效执行的关键。通过合理配置和结合其他优化策略,您可以轻松应对分页查询的挑战,提供更好的用户体验。

最近在工作中,我们遇到了一个需求,甲方要求直接从数据库导出一个业务模块中所有使用中的工单信息。为了实现这一目标,我编写了一条SQL查询语句,并请求DBA协助导出数据。尽管工单数量并不多,只有3000多条,但每个工单都包含了大量的信息。DBA进行了多次导出操作,不幸的是,每次尝试导出都导致了操作平台的卡顿和无响应。

为了克服这一问题,我们决定采用MySQL的分页技术,具体使用了LIMIT和OFFSET关键词,将导出操作拆分成多个批次进行。通常,我们在项目中使用一些开源插件如pagehelper等来实现页面分页,很少自己在sql中编写分页逻辑。但在这次需求中,我们不得不深入了解并使用了MySQL的分页功能。

在本文中,我们将详细探讨MySQL中的LIMITOFFSET关键词,以及如何通过性能优化来处理分页查询,以满足业务需求。

什么是分页查询?

分页查询是一种将大型数据集拆分成可管理块的技术,以便在用户界面中逐页显示。这在Web应用、移动应用和报告生成中非常常见,它有助于提高性能并改善用户体验,因为不需要一次加载全部数据。

分页关键字

LIMIT关键字

LIMIT关键字用于限制返回结果集中的行数。其基本语法如下:

SELECT * FROM 表名 LIMIT 行数;

例如,要从名为mark_info的表中选择最新创建的10个工单的信息,可以执行以下查询:

SELECT * FROM mark_info ORDER BY CREATE_TIME DESC  LIMIT 10;

OFFSET关键字

OFFSET关键字用于指定从结果集的哪一行开始返回数据。通常,它与LIMIT一起使用,以实现分页效果。其语法如下:

SELECT * FROM 表名 LIMIT 行数 OFFSET 偏移量;

或者

SELECT * FROM 表名 LIMIT 行数 , 偏移量;

这两写法效果是一样的。

例如,要从mark_info表中选择选择最新创建的第11到第20个工单的信息,可以执行以下查

SELECT * FROM mark_info ORDER BY CREATE_TIME DESC  LIMIT 10 OFFSET 10;

或者

SELECT * FROM mark_info ORDER BY CREATE_TIME DESC  LIMIT 10 , 10;

分页查询的示例

假设我们有一个名为bus_work_order_operate_info的表,其中存储了大量工单操作记录。我们希望实现一个分页功能,每页显示10个工单操作的信息。以下是如何执行分页查询的示例:

-- 第一页,显示最新的10个工单操作信息
SELECT * FROM bus_work_order_operate_info ORDER BY OPERATE_TIME DESC LIMIT 10;

-- 第二页,显示接下来的10个工单操作信息
SELECT * FROM bus_work_order_operate_info ORDER BY OPERATE_TIME DESC LIMIT 10 OFFSET 10;

-- 第三页,显示接下来的10个工单操作信息
SELECT * FROM bus_work_order_operate_info ORDER BY OPERATE_TIME DESC LIMIT 10 OFFSET 20;

-- 以此类推...

性能优化

我们在很多的实际应用场景中,一般 limit 加上偏移量,加上order by子句,配合合适的索引,效率通常不错。但是当偏移量非常大的时候,需要跳过大量的数据,这样会有很大的性能问题。以下是一个优化的示例:

未优化sql

SELECT * FROM bus_work_order_operate_info ORDER BY OPERATE_TIME DESC LIMIT 10 OFFSET 5000;

查询分析

_20230902222023.png

优化后sql

select T1.* from bus_work_order_operate_info T1  INNER JOIN (select ID FROM bus_work_order_operate_info ORDER BY OPERATE_TIME DESC LIMIT 10 OFFSET 5000  ) T2 ON T1.ID = T2.ID;

查询分析

_20230902222301.png

通过查询分析对比,优化后的sql扫描的数据行数更少,查询可能会更加高效,所以我们可以考虑用这种方式对大偏移量的limit 进行优化。

总结

MySQL的分页查询是处理大量数据集的常见需求,了解LIMITOFFSET关键字的用法可以帮助您有效地实现分页功能。同时,性能优化也是确保查询高效执行的关键。通过合理配置和结合其他优化策略,您可以轻松应对分页查询的挑战,提供更好的用户体验。

目录
相关文章
|
26天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
1月前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
68 3
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
70 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
94 9
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
191 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
67 5
|
1月前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
48 1
|
2月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
100 1
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
78 0
|
2月前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
219 0