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;

-- 第三页，显示接下来的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的分页查询是处理大量数据集的常见需求，了解LIMIT和OFFSET关键字的用法可以帮助您有效地实现分页功能。同时，性能优化也是确保查询高效执行的关键。通过合理配置和结合其他优化策略，您可以轻松应对分页查询的挑战，提供更好的用户体验。