MySQL分页查询详解:优化大数据集的LIMIT和OFFSET
原创最近在工作中,我们遇到了一个需求,甲方要求直接从数据库导出一个业务模块中所有使用中的工单信息。为了实现这一目标,我编写了一条SQL查询语句,并请求DBA协助导出数据。尽管工单数量并不多,只有3000多条,但每个工单都包含了大量的信息。DBA进行了多次导出操作,不幸的是,每次尝试导出都导致了操作平台的卡顿和无响应。
为了克服这一问题,我们决定采用MySQL的分页技术,具体使用了LIMIT和OFFSET关键词,将导出操作拆分成多个批次进行。通常,我们在项目中使用一些开源插件如pagehelper等来实现页面分页,很少自己在sql中编写分页逻辑。但在这次需求中,我们不得不深入了解并使用了MySQL的分页功能。
在本文中,我们将详细探讨MySQL中的
LIMIT
和
OFFSET
关键词,以及如何通过性能优化来处理分页查询,以满足业务需求。
什么是分页查询?
分页查询是一种将大型数据集拆分成可管理块的技术,以便在用户界面中逐页显示。这在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;