MySQL分页查询详解：优化大数据集的LIMIT和OFFSET-Toy模板网

这篇具有很好参考价值的文章主要介绍了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;

查询分析

MySQL分页查询详解：优化大数据集的LIMIT和OFFSET,mysql,数据库

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

查询分析

MySQL分页查询详解：优化大数据集的LIMIT和OFFSET,mysql,数据库

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

总结

MySQL的分页查询是处理大量数据集的常见需求，了解LIMIT和OFFSET关键字的用法可以帮助您有效地实现分页功能。同时，性能优化也是确保查询高效执行的关键。通过合理配置和结合其他优化策略，您可以轻松应对分页查询的挑战，提供更好的用户体验。文章来源地址https://www.toymoban.com/news/detail-691265.html

到了这里，关于MySQL分页查询详解：优化大数据集的LIMIT和OFFSET的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！