在这里插入图片描述

目录

    • 前言
    • 1. LIMIT offset, count 的性能陷阱：为什么它慢？😩
    • 2. 优化策略一：基于排序字段的“跳跃式”查询 (Seek Method) 🚀
    • 3. 优化策略二：利用子查询优化 OFFSET 扫描 (ID Subquery)
    • 4. 基础优化：为 ORDER BY 列创建索引
    • 5. 优化总页数/总记录数计算 (COUNT(*) 问题)
    • 6. EXPLAIN 分析分页查询
    • 7. 总结与选择最佳策略

🌟我的其他文章也讲解的比较有趣😁，如果喜欢博主的讲解方式，可以多多支持一下，感谢🤗！

其他优质专栏： 【🎇SpringBoot】【🎉多线程】【🎨Redis】【✨设计模式专栏（已完结）】…等

如果喜欢作者的讲解方式，可以点赞收藏加关注，你的支持就是我的动力
✨更多文章请看个人主页： 码熔burning

前言

你好呀，正在与大量数据打交道的开发者们！👋 分页，这个再寻常不过的功能，背后却隐藏着一个性能杀手——那就是 LIMIT offset, count 中不断增大的 offset 值。随着用户翻页越来越深，查询速度却越来越慢，这不仅影响用户体验，还可能给数据库带来巨大的压力。

今天，我们就来揭露 LIMIT offset, count 的性能问题，并学习几种有效的优化策略，让你的分页查询在数据量和深度增加时依然保持高性能。

1. LIMIT offset, count 的性能陷阱：为什么它慢？😩

我们常用的分页查询语句通常长这样：

SELECT * FROM articles ORDER BY publish_time DESC LIMIT 10000, 10; -- 查找第 10001 到 10010 条记录

这条语句的意图很明确：跳过前面 10000 条记录，然后返回接下来的 10 条。

问题就出在这个 offset (10000) 上。

MySQL 在处理 LIMIT offset, count 时，如果不借助优化手段，它的基本行为是：

  1. 首先，找到所有符合 WHERE 条件（如果没有 WHERE 就是全表）的记录。
  2. 然后，对这些记录进行 ORDER BY 排序（如果 ORDER BY 的列没有合适的索引，这里还会发生 Using filesort，进一步加剧性能问题）。
  3. 接下来，跳过前 offset 条记录！ MySQL 必须把这些记录一条条地读出来，然后再把它们抛弃掉。
  4. 最后，返回接下来的 count 条记录。

想象一下，如果 offset 是 100万，count 是 10。数据库需要找到并排序至少 100万零 10 条记录，然后丢弃掉 100万条，只返回最后的 10条。这前 100万条记录的读取和处理，就是白白浪费的性能开销！ 而且 offset 越大，浪费的开销就越大，查询就越慢。这就是深层分页的痛点。

在 EXPLAIN 中，虽然 LIMIT 子句会体现在执行计划中，但你无法直接从 rows 这一列看出 offset 带来的巨大开销。rows 显示的是优化器预估需要扫描的行数，而不是实际跳过的行数。LIMIT 的影响体现在它强制优化器必须找到 offset + count 条记录才能结束扫描。

2. 优化策略一：基于排序字段的“跳跃式”查询 (Seek Method) 🚀

这是最常用且效果最好的优化方法，特别是对于只能一页一页往后翻（或往前翻）的场景。它的核心思想是：不再使用 OFFSET 跳过，而是记录上一页最后一条记录的排序字段值，然后通过 WHERE 条件直接定位到下一页的起始位置。

适用场景：

  • 你希望实现“下一页”、“上一页”这样的分页导航，而不是直接跳转到任意页码。
  • 你的排序字段（或联合排序字段）是有索引的。

原理：

假设你按照 id 升序排序，每页显示 10 条记录。

  • 第一页： SELECT * FROM articles ORDER BY id ASC LIMIT 10;
    • 获取第一页的 10 条记录，并记住最后一条记录的 id 值，比如是 10。
  • 第二页： 不再使用 LIMIT 10, 10。而是使用上一页最后的 id 值作为起点：
    • SELECT * FROM articles WHERE id > 10 ORDER BY id ASC LIMIT 10;
    • MySQL 可以直接利用 id 索引，快速找到 id > 10 的第一条记录，然后顺序扫描索引和数据行，直到找到 10 条记录为止。完全避免了扫描和丢弃前 10 条记录的开销。
  • 第三页： 记住第二页最后一条记录的 id 值，比如是 20。查询：
    • SELECT * FROM articles WHERE id > 20 ORDER BY id ASC LIMIT 10;

以此类推，无论翻到多深，每一页的查询都只基于上一页的终点进行简单的索引查找和顺序扫描 count 条记录，性能非常稳定，不受 offset 影响。

处理非唯一排序字段：

如果你的排序字段不是唯一的（比如按发布时间 publish_time 排序，可能同一时间发布多篇文章），仅仅使用 WHERE publish_time > last_time 是不够的，可能会漏掉或重复记录。这时需要增加一个次级排序字段作为“决胜”条件，通常是主键 ID：

假设按 publish_time 倒序，然后按 id 倒序（确保唯一性），每页 10 条。

  • 第一页： SELECT * FROM articles ORDER BY publish_time DESC, id DESC LIMIT 10;
    • 获取第一页数据，记住最后一条记录的 publish_time 和 id，比如是 ('2024-05-07 10:00:00', 120)。
  • 第二页： WHERE 条件需要同时考虑这两个字段：
    • SELECT * FROM articles WHERE (publish_time < '2024-05-07 10:00:00') OR (publish_time = '2024-05-07 10:00:00' AND id < 120) ORDER BY publish_time DESC, id DESC LIMIT 10;
    • 这个条件的意思是：找发布时间早于上一页最后一条的记录，或者发布时间与上一页最后一条相同但 ID 更小的记录。
    • 为了让这个 WHERE 条件高效，你需要一个覆盖 (publish_time, id) 的联合索引，且顺序和方向与 ORDER BY 匹配。

优点：

  • 性能极高，不受 offset 大小影响。
  • 避免了扫描和丢弃大量记录的开销。
  • 如果排序字段有索引，MySQL 可以高效定位起始点。

缺点：

  • 无法直接跳转到任意页码。 只能从第一页开始，一页一页往后翻（或往前翻，但 WHERE 条件和 ORDER BY 方向需要反过来）。
  • 需要客户端或应用端记录并传递上一页最后一条记录的关键字段值。
  • 需要排序字段是索引列。

EXPLAIN 对比：

  • 原始查询 EXPLAIN SELECT * FROM articles ORDER BY id LIMIT 10000, 10;：rows 可能会很大（预估扫描 10010 条），Extra 可能有 Using filesort (如果 id 不是主键且没索引)。
  • 优化后查询 EXPLAIN SELECT * FROM articles WHERE id > 10000 ORDER BY id LIMIT 10;：rows 会小很多（预估扫描 10 条，因为 WHERE 条件已经缩小了范围），type 可能是 range，Extra 中没有 Using filesort。

3. 优化策略二：利用子查询优化 OFFSET 扫描 (ID Subquery)

如果你需要支持用户直接跳转到任意页码，或者排序字段没有合适的索引（虽然强烈建议为排序字段建索引！），可以考虑这种方法。它的核心是先在一个子查询中查出当前页的行的主键 ID，然后在外层查询中根据这些 ID 去获取完整的行数据。

原理：

SELECT t1.*
FROM articles t1
JOIN (SELECT idFROM articlesORDER BY publish_time DESCLIMIT 10000, 10 -- 在子查询中进行低成本的 OFFSET 扫描
) as t2 ON t1.id = t2.id
ORDER BY t2.publish_time DESC; -- 注意：如果外层需要保持排序，这里可能需要再次 ORDER BY-- 或者如果子查询的 ORDER BY 列已经包含在 SELECT id 中，可以直接用 t2 的列排序

解释：

  1. 子查询 (SELECT id FROM articles ORDER BY publish_time DESC LIMIT 10000, 10)：
    • 这个子查询仍然使用了 LIMIT offset, count，会扫描并丢弃前 offset 条记录。
    • 但它只查询了主键 ID。主键 ID 通常很小，且主键本身就是索引。相比于查询整个宽行并进行排序，只查询和排序 ID 列表的开销要小得多。
    • 如果 ORDER BY publish_time DESC 可以利用 publish_time 的索引，那么子查询的速度会更快（避免 Filesort）。
  2. 外层查询 SELECT t1.* FROM articles t1 JOIN ... ON t1.id = t2.id：
    • 外层查询根据子查询返回的少数（10个）ID，通过主键索引 (id) 去 articles 表中精确地找到并获取这 10 行完整的记录。这是高效的等值 JOIN。
    • 最后的 ORDER BY t2.publish_time DESC 确保结果集按照正确的顺序返回。如果 publish_time 也在子查询的 SELECT 列表中，可以直接用 t2.publish_time 排序。

优点：

  • 支持跳转到任意页码。
  • 相比直接对全行进行 LIMIT offset, count，子查询对更小的 ID 集合进行 OFFSET 扫描和排序，性能有所提升（尤其当原始表的行很宽，或者 ORDER BY 的列没有索引时）。

缺点：

  • 性能仍然会随着 offset 增大而下降，只是下降的速度可能比直接 LIMIT offset, count 慢。
  • SQL 语句更复杂。
  • 引入了 JOIN 的开销。

EXPLAIN 分析：

EXPLAIN 这个子查询 JOIN 语句，你会看到子查询 (DERIVED) 的执行计划，它会显示 LIMIT 和可能的 Filesort (如果 publish_time 没索引)。外层 JOIN 会显示通过 id 进行 JOIN 的类型（通常是 eq_ref 或 ref）。

4. 基础优化：为 ORDER BY 列创建索引

无论你选择哪种分页策略，为 ORDER BY 子句中使用的列创建索引都是至关重要的一步。

  • 如果你的 ORDER BY 列没有索引，MySQL 就会进行 Using filesort，这本身就是一个巨大的性能开销。分页只会加剧这个开销，因为它需要对 offset + count 这么多行进行排序。
  • 如果 ORDER BY 列有索引，MySQL 可以避免 Filesort，直接按照索引顺序读取数据。这使得上面的策略一成为可能，也使得策略二中的子查询更快。

请回顾之前关于 ORDER BY 优化的部分，确保你的排序字段有合适的索引，特别是联合索引的列顺序和方向。

5. 优化总页数/总记录数计算 (COUNT(*) 问题)

分页界面通常需要显示总记录数或总页数。计算这个值通常需要执行一个独立的 SELECT COUNT(*) 语句，而且这个语句需要和分页查询使用相同的 WHERE 条件。

SELECT COUNT(*) FROM articles WHERE status = 'published';

对于大表，COUNT(*) 也可能非常慢，因为它通常需要扫描大量数据（至少是索引）。频繁执行这个查询会给数据库带来压力。

优化 COUNT(*) 的策略：

  • 牺牲精确性，提供估算值： 告诉用户“大约有xxx条记录”，而不是精确数字。可以使用 EXPLAIN SELECT ... 的 rows 估算值（不准确），或者维护一个近似的计数器。
  • 限制总页数： 只显示前几十页或前一百页的页码，更深的页只提供“下一页”按钮。这样可以避免计算一个巨大的 COUNT(*) 值。
  • 异步加载计数： 先加载数据，后在后台异步计算总数。
  • 缓存计数： 将计数结果缓存一段时间，而不是每次都实时查询。
  • 维护计数器表： 对于非常频繁且需要精确计数的场景，可以考虑通过触发器或业务逻辑维护一个独立的计数器表。
  • SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() (谨慎使用): 这是一个 MySQL 特性，在执行完 LIMIT 查询后，可以通过 SELECT FOUND_ROWS() 获取如果没有 LIMIT 的总行数。看起来很方便，但底层实现并不总是高效（尤其在 InnoDB 中），它可能还是需要扫描大量行来确定总数。在很多情况下，单独执行一个优化好的 COUNT(*)（可能利用覆盖索引）会比 SQL_CALC_FOUND_ROWS 更快。通常不推荐在新的设计中使用它。

选择哪种策略取决于你的业务需求和对精确度的要求。 对于大多数用户来说，知道有“下一页”或者一个大致的数字就足够了，不需要精确到个位的总数。

6. EXPLAIN 分析分页查询

在优化分页查询时，EXPLAIN 是必不可少的工具。

  • 分析原始查询 (LIMIT offset, count)： 查看 type, key, rows, Extra。特别关注 Extra 中的 Using filesort 和 rows 的大小。rows 会告诉你为了得到这 count 行，优化器预估扫描了多少行，这个数字通常接近 offset + count 或更多。
  • 分析子查询优化方案 (JOIN (SELECT ID ... LIMIT offset, count))： 分别 EXPLAIN 外层查询和子查询。查看子查询的 type, key, rows, Extra，看看 ID 的扫描是否高效，是否有 Filesort。查看外层 JOIN 的类型和使用的索引。
  • 分析跳跃式查询方案 (WHERE sort_key > last_value LIMIT count)： 查看 type ( ideally range), key, rows (应该很小，接近 count), Extra (没有 Using filesort，可能有 Using index condition 如果有额外 WHERE 条件)。

通过对比不同方案的 EXPLAIN 输出，你可以直观地看到哪种方法减少了扫描的行数，消除了 Filesort，从而提高了效率。

7. 总结与选择最佳策略

  • 首要任务： 永远为你的 ORDER BY 列创建合适的索引。这是所有高效分页的基础。
  • 首选方案（对于“下一页/上一页”导航）： 使用基于索引排序字段的跳跃式查询（Seek Method）。实现方式是记录上一页最后一条记录的排序字段值，下一页查询使用 WHERE sort_key > last_value ORDER BY sort_key LIMIT count。处理非唯一排序字段时，记得加上主键作为次级排序字段和 WHERE 条件的组合判断。
  • 备选方案（对于任意页码跳转）： 如果必须支持任意页码跳转，可以考虑子查询方案，先在子查询中利用索引（特别是主键或排序字段索引）快速获取当前页的 ID 列表，再 JOIN 回原表。性能优于直接 LIMIT offset, count，但仍会随 offset 增加而性能下降。
  • 优化总数： 重新评估是否需要精确的总记录数。如果不需要，考虑使用估算、限制页数或异步计算等方法，避免昂贵的 COUNT(*) 查询。
  • 实践验证： 任何优化都需要在实际环境和数据下用 EXPLAIN 和性能测试来验证效果。

分页查询的优化是一个常见但关键的环节。通过理解 LIMIT OFFSET 的原理并应用基于索引的优化策略，特别是跳跃式查询，你可以显著提升数据库的查询性能，为用户带来更流畅的体验。

希望这篇详细的讲解能够帮助你彻底理解并解决分页查询的性能问题！实战出真知，赶紧在你的项目中试试这些方法吧！💪

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/web/82090.shtml
繁体地址，请注明出处：http://hk.pswp.cn/web/82090.shtml
英文地址，请注明出处：http://en.pswp.cn/web/82090.shtml

如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

使用curlconverter网站快速生成requests请求包

使用curlconverter网站快速生成requests请求包

在python写requests请求的时候&#xff0c;抓包后需要复制粘贴包的内容&#xff0c;然后手动修改和写代码。 最近发现一个好的网站 https://curlconverter.com/python/ 可以复制curl(bash)数据后&#xff0c;直接生成数据包&#xff0c;非常便捷。 举例说明&#xff1a; 选…
阅读更多...
python打卡day41

python打卡day41

简单CNN 知识回顾 数据增强 卷积神经网络定义的写法 batch归一化&#xff1a;调整一个批次的分布&#xff0c;常用与图像数据 特征图&#xff1a;只有卷积操作输出的才叫特征图 调度器&#xff1a;直接修改基础学习率 卷积操作常见流程如下&#xff1a; 1. 输入 → 卷积层 →…
阅读更多...
系统思考：化繁为简的艺术

系统思考：化繁为简的艺术

系统思考&#xff0c;其实是一门化繁为简的艺术。当我们能够把复杂的问题拆解成清晰的核心以及更加简单&#xff0c;从而提升团队的思考品质和行动品质&#xff0c;发挥最大的合力。 每个公司都想在某方面成为最优秀的&#xff0c;但是实际上具有穿透性的洞察力和摆脱虚荣心的清…
阅读更多...
2025.05.28【Parallel】Parallel绘图：拟时序分析专用图

2025.05.28【Parallel】Parallel绘图：拟时序分析专用图

Improve general appearance Add title, use a theme, change color palette, control variable orders and more Highlight a group Highlight a group of interest to help people understand your story 文章目录 Improve general appearanceHighlight a group探索Paralle…
阅读更多...
Elasticsearch父子关系解析

Elasticsearch父子关系解析

引言 在复杂业务场景中&#xff0c;数据关联查询是搜索与分析的核心需求。以电商订单、文章评论、客户关系等场景为例&#xff0c;传统关系型数据库通过外键实现的多表关联&#xff0c;在分布式搜索场景下面临性能与扩展性挑战。Elasticsearch通过父子关系&#xff08;Parent-…
阅读更多...
MCP架构全解析：从核心原理到企业级实践

MCP架构全解析：从核心原理到企业级实践

&#x1f49d;&#x1f49d;&#x1f49d;欢迎莅临我的博客&#xff0c;很高兴能够在这里和您见面&#xff01;希望您在这里可以感受到一份轻松愉快的氛围&#xff0c;不仅可以获得有趣的内容和知识&#xff0c;也可以畅所欲言、分享您的想法和见解。 推荐&#xff1a;「storms…
阅读更多...
开发者体验提升：打造高效愉悦的开发环境

开发者体验提升：打造高效愉悦的开发环境

“开发者体验不是奢侈品&#xff0c;而是生产力的倍增器。优秀的工具链能让开发者从机械劳动中解放&#xff0c;专注于创造真正有价值的东西。” —— 前端架构师 Sarah Drasner 1. 自定义 CLI 工具开发 (1) 基于 plop.js 的组件模板生成器 痛点分析&#xff1a;在大型项目中…
阅读更多...
运用集合知识做斗地主案例

运用集合知识做斗地主案例

方法中可变参数 一种特殊形参&#xff0c;定义在方法&#xff0c;构造器的形参列表里&#xff0c;格式&#xff1a;数据类型...参数名称&#xff1b; 可变参数的特点和好处 特点&#xff1a;可以不传数据给它&#xff1b;可以传一个或者同时传多个数据给它&#xff1b;也可以…
阅读更多...
websocket在vue中的使用步骤，以及实现聊天

websocket在vue中的使用步骤，以及实现聊天

一、WebSocket集成步骤 ‌连接初始化‌ 在Vue组件中创建WebSocket实例&#xff0c;建议在mounted生命周期中执行&#xff1a; data() {return {socket: null,messages: []} }, mounted() {this.socket new WebSocket(wss://your-server-endpoint); }‌事件监听配置 ‌连接成…
阅读更多...
HarmonyOS鸿蒙Uniapp三方框架

HarmonyOS鸿蒙Uniapp三方框架

鸿蒙Uniapp三方框架集成指南 一、环境配置 // 安装必要依赖 npm install ohos/hvigor-ohos-plugin --save-dev // 配置harmony模块 "harmony": {"compileSdkVersion": 9,"compatibleSdkVersion": 8,"arktsVersion": "1.0.0&quo…
阅读更多...
【HW系列】—溯源与定位—Linux入侵排查

【HW系列】—溯源与定位—Linux入侵排查

文章目录 一、Linux入侵排查1.账户安全2.特权用户排查&#xff08;UID0&#xff09;3.查看历史命令4.异常端口与进程端口排查进程排查 二、溯源分析1. 威胁情报&#xff08;Threat Intelligence&#xff09;2. IP定位&#xff08;IP Geolocation&#xff09;3. 端口扫描&#x…
阅读更多...
C++17新特性 Lambda表达式

C++17新特性 Lambda表达式

//lambda表达式的基本语法如下&#xff1a; /* [捕获列表] (参数列表)mutable(可选)异常属性 -> 返回类型 { // 函数体 }*/ 1&#xff0c;值捕获 //1&#xff0c; 值捕获示例 #include <iostream> void lambda_value_capture() {int value 1;auto copy_value/*返…
阅读更多...
园区智能化集成平台汇报方案

园区智能化集成平台汇报方案

该方案为园区智能化集成平台设计，依据《智能建筑设计标准》等 20 余项国家与行业规范，针对传统园区信息孤岛、反应滞后、经验流失、管理粗放等痛点，构建可视化智慧园区管理平台，实现大屏数据可视化、三维设备监控、智慧运维（含工单管理、巡检打卡）、能源能耗分析、AI 安防…
阅读更多...
Vue-自定义指令

Vue-自定义指令

自定义指令 简单写法 v-twoAge 功能&#xff1a; 当前年龄翻倍 注意&#xff1a;指令方法名称 小写 代码 <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8" /><title>自定义指令</title><!-- 引入V…
阅读更多...
Kotlin 中的数据类型有隐式转换吗？为什么？

Kotlin 中的数据类型有隐式转换吗？为什么？

在 Kotlin 中&#xff0c;基本数据类型没有隐式转换。主要出于安全性和明确性的考虑。 1 Kotlin 的显式类型转换规则 Kotlin 要求开发者显式调用转换函数进行类型转换&#xff0c; 例如&#xff1a; val a: Int 10 val b: Long a.toLong() // 必须显式调用 toLong() // 错…
阅读更多...
Android获取设备信息

Android获取设备信息

使用java: List<TableMessage> dataListnew ArrayList<TableMessage>();//获取设备信息Hashtable<String,String> ht MyDeviceInfo.getDeviceAllInfo2(LoginActivity.this);for (Map.Entry<String, String> entry : ht.entrySet()) {String key entry…
阅读更多...
WIN11使用vscode搭建c语言开发环境

WIN11使用vscode搭建c语言开发环境

安装 VS Code 下载地址: Visual Studio Code - Code Editing. Redefined 安装时勾选 "添加到 PATH"&#xff08;方便在终端中调用 code 命令 下载 MSYS2 官网&#xff1a;MSYS2 下载 msys2-x86_64-xxxx.exe&#xff08;64位版本&#xff09;并安装。 默认安装路径…
阅读更多...
微信小程序带数组参数跳转页面，微信小程序跳转页面带数组参数

微信小程序带数组参数跳转页面，微信小程序跳转页面带数组参数

在微信小程序中&#xff0c;带数组参数跳转页面需要通过JSON序列化和URL编码处理&#xff0c;以下是具体实现方法 传递数组参数‌&#xff08;发送页面&#xff09; wx.navigateTo({url: /pages/targetPage?arr encodeURIComponent(JSON.stringify(yourArray)) });接收数组参…
阅读更多...
Mac M1编译OpenCV获取libopencv_java490.dylib文件

Mac M1编译OpenCV获取libopencv_java490.dylib文件

Window OpenCV下载地址 https://opencv.org/releases/OpenCV源码下载 https://github.com/opencv/opencv/tree/4.9.0 https://github.com/opencv/opencv_contrib/tree/4.9.0OpenCV依赖 brew install libjpeg libpng libtiff cmake3 ant freetype构建open CV cmake -G Ninja…
阅读更多...
前端面试准备-3

前端面试准备-3

1.let、const、var的区别 ①&#xff1a;let和const为块级作用域&#xff0c;var为全局作用域 ②&#xff1a;let和var可以重新赋值定义&#xff0c;而const不可以 ③&#xff1a;var会提升到作用域顶部&#xff0c;但不会初始化&#xff1b;let和const也会提升到作用不顶部…
阅读更多...
最新文章

Copyright @ 2022~2023