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

举报
修己xj 发表于 2023/09/02 22:46:23 2023/09/02
【摘要】 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;

查询分析

优化后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;

查询分析

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

总结

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

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。