当前位置：首页 > news >正文

Mysql的深度分页查询优化

news 来源：原创 2025/6/29 8:24:42

一、深度分页为什么慢？

当执行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 10 时：

MySQL 会扫描前 1,000,010 行，丢弃前 100 万行，仅返回 10 行。
偏移量（offset）越大，扫描行数越多，时间复杂度为 O(N+M)（N = 偏移量，M = 每页条数）。

核心痛点：LIMIT 本质是 “跳过” 数据，而非 “直接定位”，导致全表扫描。

二、5 大优化方案（按优先级排序）

🔍 方案 1：覆盖索引 + 延迟关联（通用最优解）

原理：先通过索引定位主键，再批量回表，减少无效扫描。
实现：

sql

-- 1. 创建覆盖索引（包含排序字段+主键）
ALTER TABLE orders ADD INDEX idx_created_at_id (created_at, id);-- 2. 优化查询（子查询只查索引，主查询回表）
SELECT o.* 
FROM orders o
INNER JOIN (SELECT id  -- 仅需主键（覆盖索引）FROM orders ORDER BY created_at, id  -- 利用索引排序LIMIT 1000000, 10  -- 仅扫描 10 行索引
) AS tmp ON o.id = tmp.id;

效果：扫描行数从 1,000,010 降至 10，速度提升 10 万倍！
适用场景：所有带排序的分页（如订单、日志）。

📌 方案 2：游标分页（适合连续翻页）

原理：记录上一页最后一条的 id，避免 OFFSET。
示例：

sql

-- 第 1 页（初始查询）
SELECT * FROM orders ORDER BY id DESC LIMIT 10;  -- 假设最后一条 id=1000-- 第 2 页（基于游标）
SELECT * 
FROM orders 
WHERE id < 1000  -- 定位起始点
ORDER BY id DESC 
LIMIT 10;  -- 仅扫描 10 行

优点：时间复杂度 O(M)，与页码无关；适合无限滚动（如抖音刷视频）。
缺点：不支持随机跳页（如直接跳转到第 1000 页）。

🗂️ 方案 3：范围分片（依赖有序数据）

原理：按时间 / ID 分段，缩小查询范围。
示例：

sql

-- 按月份分区查询（假设数据按月递增）
SELECT * 
FROM orders 
WHERE created_at BETWEEN '2023-10-01' AND '2023-10-31'
ORDER BY id 
LIMIT 10;

适用场景：时间序列数据（如报表、日志），用户可接受 “按范围筛选”。

💡 方案 4：预计算分页（冷数据专用）

原理：提前计算分页书签，存入汇总表。
实现：

sql

-- 创建汇总表（每日凌晨更新）
CREATE TABLE order_pager (page_num INT PRIMARY KEY,min_id BIGINT,max_id BIGINT
);-- 插入分页书签（每页 100 条）
INSERT INTO order_pager
SELECT FLOOR((id-1)/100) + 1 AS page_num,MIN(id) AS min_id,MAX(id) AS max_id
FROM orders
GROUP BY page_num;-- 查询第 1000 页
SELECT * 
FROM orders 
WHERE id BETWEEN (SELECT min_id FROM order_pager WHERE page_num=1000) AND (SELECT max_id FROM order_pager WHERE page_num=1000)
LIMIT 100;

优点：查询时间稳定，适合历史数据（如年报）；缺点：存储冗余。

🚀 方案 5：Elasticsearch 兜底（海量数据）

原理：利用 ES 的 search_after 避免深度分页性能衰减。
示例：

json

GET /orders/_search
{"size": 10,"sort": ["_id"],  -- 按主键排序"search_after": [1000000],  -- 上一页最后一条的 _id"query": { ... }
}

适用场景：百万级以上数据，需复杂查询（如全文搜索）。

三、索引设计黄金法则

场景	索引建议	示例
时间 + 主键排序	复合索引（时间，主键）	`(created_at, id)`
多条件过滤 + 排序	最左匹配索引（WHERE > ORDER BY）	`(status, created_at, id)`
字符串排序	前缀索引（平衡长度与选择性）	`name(20)` （取前 20 字符）

⚠️ 注意：索引非越多越好，单表索引不超过 5 个，避免冗余。

四、方案对比与选择指南

方案	时间复杂度	适用场景	推荐指数
覆盖索引 + 延迟关联	O(M)	通用场景（90% 业务首选）	⭐⭐⭐⭐⭐
游标分页	O(M)	连续翻页（如 App 列表）	⭐⭐⭐⭐
预计算分页	O(1)	冷数据历史查询	⭐⭐⭐
Elasticsearch	O(logN)	海量数据 + 复杂查询	⭐⭐⭐⭐

决策树：

数据量 < 10 万：直接 LIMIT，无需优化。
10 万～100 万：方案 1（覆盖索引）。
100 万～1000 万：方案 1 + 方案 2（游标）。
千万级以上：方案 5（ES）+ 方案 1 兜底。

五、实战避坑指南

** 避免 SELECT ***：只查必要字段，减少回表数据量。

监控执行计划：

sql

EXPLAIN SELECT * FROM orders ...;  -- 重点看 `rows` 列，理想值接近 LIMIT 数量。

慢查询日志：捕获真实慢查询（如 LIMIT 500000,10 超过 1 秒）。
业务妥协：限制最大分页深度（如 App 最多显示 500 页），避免极端场景。

总结

深度分页的核心优化思路是 “跳过扫描，直接定位”：

热数据：用覆盖索引 + 延迟关联，确保每次查询只扫描少量索引。
冷数据：预计算分页或归档到 ES，牺牲空间换时间。
业务层：结合游标分页和分页深度限制，避免数据库被拖垮。

通过这一套组合拳，可将深度分页的耗时从 “秒级” 优化到 “毫秒级”，轻松应对百万级数据分页！

Mysql的深度分页查询优化

一、深度分页为什么慢？ 当执行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 10 时： MySQL 会扫描前 1,000,010 行，丢弃前 100 万行，仅返回 10 行。偏移量（offset）越大，扫描行数越多&…...

编程日记 2025/6/29 8:24:42

OpenCv高阶（十一）——物体跟踪

文章目录前言一、OpenCV 中的物体跟踪算法1、均值漂移（Mean Shift）：2、CamShift：3、KCF（Kernelized Correlation Filters）：4、MIL（Multiple Instance Learning）&#xf…...

编程日记 2025/6/29 7:48:28

第一章：Model Context Protocol (MCP)

Chapter 1: Model Context Protocol (MCP) 🌟 为什么需要MCP？ 想象你正在训练一只小狗，希望它能听懂你的指令并执行任务。但如果你和小狗用不同语言交流，它可能完全不知道你的意思。类似地，大型语言模型（L…...

编程日记 2025/4/25 12:46:07

【SAP PP】COOIS报表分析

本文目录一、基本查询操作二、订单参数文件三、COOIS报表增强BADI COOIS报表是PP模块核心报表，是系统中一个功能强大的标准报表，COOIS可查询查询生产订单的状态、进度、组件、工序、报工等信息的综合型报表，，关联了生产订单…...

编程日记 2025/5/3 13:30:16

2025上海车展|紫光展锐发布新一代旗舰级智能座舱芯片平台A888

4月24日，在第二十一届上海国际汽车工业展览会（以下简称“上海车展”）期间，紫光展锐重磅推出新一代旗舰级智能座舱芯片平台A8880，以强劲实力全面助力汽车座舱智能化迈向新征程。三大核心能力，紧抓市场机遇 …...

编程日记 2025/4/25 12:45:01

蓝牙4.0与蓝牙5.0的区别

蓝牙4.0与蓝牙5.0的主要区别： 传输速度：蓝牙5.0的传输速度是蓝牙4.0的两倍，理论速率可达2Mbps，而蓝牙4.0为1Mbps。传输距离：蓝牙5.0的传输距离是蓝牙4.0的四倍，在开放空间可达300米，而蓝牙4.0…...

编程日记 2025/5/27 7:02:34

Vue 的单文件组件（.vue 文件）script 标签的使用说明

在 Vue 的单文件组件（.vue 文件）中，最多可以编写 2 个 <script> 标签，但需要满足特定条件： 1. Vue 3 的情况（主流用法） 从 Vue 3.2 开始，官方支持以下两种形式共存&#xff1…...

编程日记 2025/4/25 12:43:51

TIM输入捕获知识部分

越往左，频率越高；越往右，频率越低。【越紧凑，相同时间，次数越多】计算频率的方法：测评法、测周法、中界频率。频率的定义：1s内出现了多少个重复的周期测评法就是从频率的定义出发的&#…...

编程日记 2025/4/25 12:42:46

【数据可视化-30】Netflix电影和电视节目数据集可视化分析

🧑 博主简介：曾任某智慧城市类企业算法总监，目前在美国市场的物流公司从事高级算法工程师一职，深耕人工智能领域，精通python数据挖掘、可视化、机器学习等，发表过AI相关的专利并多次在AI类比赛中获奖。CSDN…...

编程日记 2025/5/4 6:50:35

多线程事务？拿捏！

场景:有一批1万或者10万数据，插入数据库，怎么做事务中进行批量提交 publList<List<OrderPo>> partition Lists.partition(list, 450);StopWatch stopWatch new StopWatch();stopWatch.start();// 顺序插入for (List<OrderPo> sub…...

编程日记 2025/4/25 12:40:36

Spring Boot 自动配置深度解析：从源码结构到设计哲学

Spring Boot 自动配置深度解析：从源码结构到设计哲学为什么自动配置如此重要？ 在传统 Spring 开发中，开发者要手动配置大量 XML 或 JavaConfig，过程繁琐、重复且容易出错。Spring Boot 引入自动配置机制，极大地简化…...

编程日记 2025/5/4 7:48:36

Linux下载与安装——笔记

Linux 是一种自由和开放源代码的操作系统（OS），其核心（Kernel）由 Linus Torvalds 于 1991 年首次发布。 1、选择适合的 Linux 发行版根据使用场景和技术水平选择： 新手入门：Ubuntu&#xff08…...

编程日记 2025/4/25 12:40:33

09前端项目----分页功能

分页功能分页器的优点实现分页功能自定义分页器先实现静态分页器调试分页器动态数据/交互 Element UI组件分页器的优点电商平台同时展示的数据很多，所以采用分页功能实现分页功能 Element UI已经有封装好的组件，但是也要掌握原理，以及自定…...

编程日记 2025/4/25 12:39:27

头歌之动手学人工智能-机器学习 --- PCA

目录第1关：维数灾难与降维第2关：PCA算法流程任务描述编程要求测试说明第3关：sklearn中的PCA 任务描述编程要求测试说明第1关：维数灾难与降维第2关：PCA算法流程任务描述本关任务：补充…...

编程日记 2025/4/29 21:44:52

Spring 中的循环引用问题

本章来聊聊Spring 中的循环引用问题该如何解决。这里聊的很粗糙，并没有那么细节，只是大概了解了一点。什么是循环引用？ 如下图所示： 图中有两个类，一个 Class A ，A 中又引用了 B，Class B 中又…...

编程日记 2025/5/4 19:35:13

author instruction https://www.siggraph.org/preparing-your-content/author-instructions/ 使用latex模板 https://research.siggraph.org/blog/guides/how-to-use-the-acm-siggraph-tog-latex-template/ 格式要求（字体、页面大小等） https://sa202…...

编程日记 2025/4/25 12:38:19

Python学习笔记（三）（程序流程控制）

文章目录一、条件语句（if/elif/else）语法：示例： 二、循环语句1. for 循环语法：示例： 2. while 循环语法：示例： 三、循环控制语句1. break：立即终止循环2. continue&…...

编程日记 2025/4/25 12:37:17

onnx注册cpu版flashattention

摘要本教程展示了如何在 ONNX Runtime 中注册一个 CPU 可执行的 FlashAttention 算子。首先，可以直接升级到 ONNX Runtime v1.16 及以上，以获得内置的 FlashAttention CPU 实现citeturn0search2；其次，演示了如何通过 ONNX Runtime 的 Custom Op 接口自定义实现并注…...

编程日记 2025/4/25 12:37:16

WebAssembly：开启高性能Web应用新时代

一、引言随着互联网技术的飞速发展，Web应用的复杂度和性能要求越来越高。传统的Web开发技术，如JavaScript，虽然功能强大，但在处理复杂计算和高性能需求时仍存在一些局限性。WebAssembly（简称Wasm）作为一种…...

编程日记 2025/4/25 12:36:10

【前端】手写代码输出题易错点汇总

不定期补充。目录异步事件循环this作用域/变量提升/闭包原型/继承异步事件循环 const promise new Promise((resolve, reject) > {console.log(1);console.log(2); }); promise.then(() > {console.log(3); }); console.log(4); //1 //2 //4promise.then 是微任务&…...

编程日记 2025/4/25 12:35:08

STM32F103_HAL库+寄存器学习笔记20 - CAN发送中断+ringbuffer + CAN空闲接收中断+接收所有CAN报文+ringbuffer

导言如上所示，在[[STM32F103_HAL库寄存器学习笔记19 - CAN发送中断CAN接收中断接收所有CAN报文ringbuffer数据结构]]的基础上，为CAN发送端也引入了ringbuffer（环形缓冲区）机制。CAN发送有三个发送邮箱，为什么还另外需…...

编程日记 2025/4/25 12:35:05

小白自学python第二天

学习python的第二天一、判断语句 1、布尔类型和比较运算符 1、布尔类型表示现实生活中的逻辑，真（True，用数字1表示）和假（False，用数字0表示） 2、布尔类型变量的定义变量的名称布尔类…...

编程日记 2025/4/25 12:33:58

JavaScript 异步编程与请求取消全指南

JavaScript 异步编程与请求取消全指南涵盖：同步/异步、Promise、async/await、AbortController、前后端协作一、同步与异步 1. 同步（Synchronous） 定义：代码按顺序执行，前一步完成才能执行下一步。特点&#xff1…...

编程日记 2025/5/26 3:41:02

Redis 核心应用场景

高性能缓存 Redis 作为内存数据库，读写性能可达10万 QPS，适合缓存热点数据（如商品详情、用户会话），显著降低数据库压力。通过设置过期时间（TTL）自动清理非热点数据，推荐结合allkeys-…...

编程日记 2025/5/5 0:09:42

KMS工作原理及其安全性分析

在当今数字化时代，数据安全已经成为企业和个人最为关注的话题之一。随着云计算和大数据的快速发展，如何安全地管理密钥成为了一个重要的挑战。KMS（Key Management Service，密钥管理服务）作为一种专业的密钥管理解决方案…...

编程日记 2025/5/19 22:36:22

施磊老师基于muduo网络库的集群聊天服务器(六)

文章目录客户端开发开始客户端首页面功能为何不逐行开发？客户端CMake代码搭配知识补充--有很多漏的客户端main-登录,注册,退出群组有问题测试客户端好友添加与聊天功能表驱动设计：commandMapcommandHandlerMap为什么都是int, string添加好友和聊天功能…...

编程日记 2025/4/25 12:31:44

有关字体，语言，字符编码相关的基础知识，询问chatgpt所得

学习这个知识点的背景是，我需要做一个在canvas 上书写矢量文本的功能， 使用opentype来加载字体文件，并将内容转换为 svg，导入画布。但是有些字体文件是不包含一些其他语言的字符的。就可能出现 “无效字符”。花了点时间研究…...

编程日记 2025/4/25 12:31:43

Obsidian和Ollama大语言模型的交互过程

之前的文章中介绍了Obsidian配合Ollama的使用案例，那么它们是如何配合起来的呢？其实这个问题并不准确，问题的准确描述应该是Obsidian的Copilot插件是如何与Ollama大语言模型交互的。因为Obsidian在这里只是一个载体，核心功能还是C…...

编程日记 2025/4/25 12:29:35

架构-数据库系统

数据库系统一、数据库系统概述 （一）课程核心模块覆盖数据库设计、关系代数、规范化理论、数据控制四大核心模块，旨在构建从理论到实践的完整知识体系至。 （二）典型应用场景数据管理：学生信息管理&a…...

编程日记 2025/4/25 12:29:33

【C到Java的深度跃迁：从指针到对象，从过程到生态】第三模块·面向对象深度进化 —— 第十二章接口：比C函数指针更强大的契约

一、从函数指针到接口契约 1.1 C函数指针的本质限制 C语言通过函数指针实现回调机制，但存在根本性缺陷： 回调函数示例： typedef void (*Logger)(const char*); void process_data(int data, Logger logger) { // ... logger("Pro…...

编程日记 2025/5/30 13:39:12

【HFP】蓝牙语音通话控制深度解析：来电拒接与通话终止协议

目录一、来电拒接的核心流程与信令交互 1.1 拒接场景的分类与触发条件 1.2 HF 端拒接流程 1.3 AG 端拒接流程二、通话终止流程：主动断开与异常中断 2.1 终止场景的界定 2.2 HF 端终止流程 2.3 AG 端终止流程三、信令协议的核心要素：AT 命令与…...

编程日记 2025/4/25 12:27:23

linux 中断子系统层级中断编程

虚拟中断控制器代码： #include<linux/kernel.h> #include<linux/module.h> #include<linux/clk.h> #include<linux/err.h> #include<linux/init.h> #include<linux/interrupt.h> #include<linux/io.h> #include<linu…...

编程日记 2025/4/25 12:27:20

continue插件实现IDEA接入本地离线部署的deepseek等大模型

文章目录前言一、IDEA安装continue二、continue部署本地大模型三、continue聊天窗口使用deepseek R1四、continue批量接入硅基流动的模型API 前言亲爱的家人们，创作很不容易，若对您有帮助的话，请点赞收藏加关注哦，您的关注是我…...

编程日记 2025/4/25 12:26:15

Redis-缓存应用本地缓存与分布式缓存的深度解析

Redis缓存场景与策略：本地缓存与分布式缓存的深度解析在当今高并发、低延迟的互联网架构中，缓存技术是优化系统性能的核心手段之一。Redis作为分布式缓存的标杆，与本地缓存共同构成了缓存体系的两大支柱。然而，两者的适用场景与…...

编程日记 2025/4/25 12:26:15

关于按键映射软件的探索（其一）

那么先说结论——重构了一次，我还是失败了，失败于拓展调整个性化的设计，不过我还是实现了按键监测然后显示的功能。只不过是说我对于WPF软件等的封装和软窗口的功能还是不怎么熟悉。引言在许多游戏玩家中，高难度操作&#xff08…...

编程日记 2025/4/25 12:25:11

测试基础笔记第十一天

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录一、查询连接1.内连接2.左连接3.右连接4.左右连接的必要性5.自关联6.普通查询7.子查询8.子查询充当数据源二、数据库高级扩展内容1.外键2.索引3.验证索引效果案例实…...

编程日记 2025/4/25 12:25:10

优选算法第十讲：字符串

优选算法第十讲：字符串 1.最长公共前缀2.最长回文子串3.二进制求和4.字符串相乘 1.最长公共前缀 2.最长回文子串 3.二进制求和 4.字符串相乘...

编程日记 2025/4/25 12:25:09

RK3588芯片NPU的使用：官方rknn_yolov5_android_apk_demo运行与解读

一、本文的目标本文将完成两项任务：官方的调用摄像头动态目标识别例子运行在rk3588的开发板上。解读源码以增加对rknn开发的认识。二、开发环境说明主机系统：Windows 11目标设备：搭载RK3588芯片的安卓开发板核心工具：Android Studio Koala | 2024.1.1 Patch 2，NDK 27.…...

编程日记 2025/4/25 12:25:07

面试篇：Spring Boot

基础概念 Spring Boot的核心优势是什么？ Spring Boot 的核心优势如下： 自动配置：根据项目中的依赖自动进行配置，减少了大量的手动配置工作。内嵌服务器：内置 Tomcat、Jetty 等容器，应用可以直接运行为一…...

编程日记 2025/4/25 12:21:56

开源漏洞扫描器：OpenVAS

一、OpenVAS介绍 OpenVAS (Open Vulnerability Assessment System) 是一款功能强大的开源漏洞扫描器。它由 Greenbone Networks 开发和维护，是 Greenbone 安全管理器 (GSM) 产品的基础，同时也有免费的社区版本（Greenbone Community Edition&…...

编程日记 2025/5/16 8:51:42

PCB封装主要组成元素

PCB（Printed Circuit Board，印刷电路板）封装是指将电子元件固定在 PCB 上，并实现电气连接的方式。主要包括以下几类。 1. 焊盘（Pad） 作用：焊盘是 PCB 封装中最重要的元素之一，它是…...

编程日记 2025/5/23 22:39:46

STC8H DMA 串口1全双工中断方式收发通讯C语言

/************* 功能说明 ************** 本例程基于STC8H8K64U为主控芯片的实验箱9进行编写测试，STC8H系列带DMA模块的芯片可通用参考. 串口1全双工中断方式收发通讯程序。通过PC向MCU发送数据, MCU将收到的数据自动存入DMA空间. 当DMA空间存满设置大小的…...

编程日记 2025/5/28 2:50:16

考研英一学习笔记

2024 年全国硕士研究生招生考试英语（一）试题 （科目代码：201） Section Ⅰ Use of English Directions: Read the following text. Choose the best word(s) for each numbered blank and mark A, B, C or D on the ANS…...

编程日记 2025/5/24 4:22:09

深度理解spring——BeanFactory的实现

BeanFactory Spring之BeanFactory什么是BeanFactoryApplicationContext相对BeanFactory实现的功能性扩展1. MessageSource2. ResourcePatternResolver3. ApplicationEventPublisher4. EnvironmentCapable通用ApplicationContext实践实现BeanFactoryBeanFactory后处理器排序让谁…...

编程日记 2025/5/31 4:14:04