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

优化大表分页查询性能:大表LIMIT 1000000, 10该怎么优化?

在处理大数据量的MySQL表时,我们经常会遇到一个问题:当我们尝试使用LIMIT语句进行分页查询时,性能会随着偏移量的增加而显著下降。例如,SELECT * FROM table LIMIT 1000000, 10 这样的查询可能会非常慢。那么,我们应该如何解决这个问题呢?

问题原因

首先,我们需要理解为什么这个问题会发生。MySQL在执行LIMIT语句时,会先跳过指定的偏移量,然后返回接下来的行。这意味着,如果你的偏移量非常大,比如1,000,000,MySQL需要先跳过1,000,000行,这是非常耗时的。

解决方案

对于这个问题,我们有几种可能的解决方案:

  1. 使用索引覆盖扫描(Covering Index Scan):如果你的查询可以被一个索引完全覆盖,那么MySQL可以只读取索引,而不需要读取实际的行。这可以大大提高查询速度。

  2. 记住上次查询的最后一个ID:如果你的表有一个递增的ID列,你可以在每次查询时记住上次查询的最后一个ID,然后在下一次查询时使用这个ID来限制结果。

  3. 使用分区表:如果你的表非常大,你可以考虑使用分区表。这样,你的查询可以只扫描一个分区,而不是整个表。

下面,我们将详细讨论这些解决方案,并提供Java示例代码。

使用索引覆盖扫描

假设我们有一个用户表,表结构如下:

CREATE TABLE `users` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

我们的查询是:SELECT * FROM users ORDER BY id LIMIT 1000000, 10

为了优化这个查询,我们可以创建一个覆盖索引:

CREATE INDEX idx_users_id_username_email ON users(id, username, email);

然后,我们可以修改查询为:

SELECT id, username, email FROM users ORDER BY id LIMIT 1000000, 10;

这样,MySQL可以只读取索引,而不需要读取实际的行。

在Java中,我们可以使用JdbcTemplate来执行这个查询:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;import java.util.List;public class UserDao {private JdbcTemplate jdbcTemplate;public UserDao(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}public List<User> getUsers(int offset, int limit) {String sql = "SELECT id, username, email FROM users ORDER BY id LIMIT ?, ?";return jdbcTemplate.query(sql, new Object[]{offset, limit}, (rs, rowNum) ->new User(rs.getLong("id"), rs.getString("username"), rs.getString("email")));}
}

记住上次查询的最后一个ID

另一个解决方案是在每次查询时记住上次查询的最后一个ID,然后在下一次查询时使用这个ID来限制结果。这样,我们就不需要跳过任何行,而可以直接从需要的位置开始查询。

假设我们的初始查询是:SELECT * FROM users ORDER BY id LIMIT 10。然后,我们记住最后一个用户的ID,假设是10。在下一次查询时,我们可以使用这个ID来限制结果:SELECT * FROM users WHERE id > 10 ORDER BY id LIMIT 10

在Java中,我们可以修改UserDao类来实现这个功能:

public class UserDao {private JdbcTemplate jdbcTemplate;public UserDao(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}public List<User> getUsers(long lastId, int limit) {String sql = "SELECT * FROM users WHERE id > ? ORDER BY id LIMIT ?";return jdbcTemplate.query(sql, new Object[]{lastId, limit}, (rs, rowNum) ->new User(rs.getLong("id"), rs.getString("username"), rs.getString("email")));}
}

使用分区表

如果你的表非常大,你可以考虑使用分区表。例如,你可以按照ID的范围来分区你的表。然后,你的查询可以只扫描一个分区,而不是整个表。

在MySQL中,你可以使用PARTITION BY RANGE语句来创建分区表:

CREATE TABLE users (id INT NOT NULL,username VARCHAR(30) NOT NULL,email VARCHAR(30) NOT NULL,PRIMARY KEY(id)
)
PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN (2000000),PARTITION p2 VALUES LESS THAN MAXVALUE
);

在Java中,我们可以按照分区来查询数据:

public class UserDao {private JdbcTemplate jdbcTemplate;public UserDao(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}public List<User> getUsers(int partition, int limit) {String sql = "SELECT * FROM users PARTITION (p" + partition + ") ORDER BY id LIMIT ?";return jdbcTemplate.query(sql, new Object[]{limit}, (rs, rowNum) ->new User(rs.getLong("id"), rs.getString("username"), rs.getString("email")));}
}

结论

在处理大数据量的MySQL表时,我们需要考虑如何优化我们的分页查询。我们可以使用索引覆盖扫描,记住上次查询的最后一个ID,或者使用分区表。每种方法都有其优点和适用场景,我们需要根据我们的具体需求来选择最适合的方法。

👉 💐🌸 公众号请关注 "果酱桑", 一起学习,一起进步! 🌸💐

相关文章:

优化大表分页查询性能:大表LIMIT 1000000, 10该怎么优化?

在处理大数据量的MySQL表时&#xff0c;我们经常会遇到一个问题&#xff1a;当我们尝试使用LIMIT语句进行分页查询时&#xff0c;性能会随着偏移量的增加而显著下降。例如&#xff0c;SELECT * FROM table LIMIT 1000000, 10 这样的查询可能会非常慢。那么&#xff0c;我们应该…...

ubuntu PX4 vscode stlink debug设置

硬件 stlink holybro debug板 pixhawk4 安装openocd 官方文档&#xff0c;但是第一步安装建议从源码安装&#xff0c;bug少很多 github链接 编译安装&#xff0c;参考 ./bootstrap (when building from the git repository)./configure [options]makesudo make install安装后…...

Flask的一种启动方式和三种托管方式

1. 原生启动 Flask 支持使用原生的 app.run() 方法来启动应用程序。这种方法是最简单、最基本的启动方式&#xff0c;适用于开发环境和小型应用程序。 from flask import Flaskapp Flask(__name__)app.route(/) def hello_world():return Hello, World!if __name__ __main__…...

cudnn too short

原因是libcudnn.so为软链接&#xff0c;相当于快捷键&#xff0c;但是没有映射到真正的libcudnn.so.8.9.5上 cd /usr/local/cuda-11.6/lib64 ln -s libcudnn.so.8.9.5 libcudnn.so.8...

01、SpringBoot + MyBaits-Plus 集成微信支付 -->项目搭建

目录 SpringBoot MyBaits-Plus 集成微信支付 之 项目搭建1、创建boot项目2、引入Swagger作用&#xff1a;2-1、引入依赖2-2、写配置文件进行测试2-3、访问Swagger页面2-4、注解优化显示 3、定义统一结果作用&#xff1a;3-1、引入lombok依赖3-2、写个统一结果的类-->RR类的…...

Linux 性能调优之网络优化

写在前面 考试整理相关笔记分享一些 Linux 中网络内核参数调优的笔记理解不足小伙伴帮忙指正 对每个人而言&#xff0c;真正的职责只有一个&#xff1a;找到自我。然后在心中坚守其一生&#xff0c;全心全意&#xff0c;永不停息。所有其它的路都是不完整的&#xff0c;是人的逃…...

RT-Thread系统使用常见问题处理记录

1.使用telnet连接系统时发送help指令显示不全的问题。 原因&#xff1a;telnet发送缓存太小。 解决办法&#xff1a;更改agile_telnet软件包里Set agile_telnet tx buffer size的大小。 2.使用Paho MQTT软件包过一段时间报错hard fault on thread: mqtt0 解决办法&#xff1…...

优先队列----数据结构

概念 不知道你玩过英雄联盟吗&#xff1f;英雄联盟里面的防御塔会攻击离自己最近的小兵&#xff0c;但是如果有炮车兵在塔内&#xff0c;防御塔会优先攻击炮车&#xff08;因为炮车的威胁性更大&#xff09;&#xff0c;只有没有兵线在塔内时&#xff0c;防御塔才会攻击英雄。…...

nginx项目部署教程

nginx项目部署教程 1. 项目部署介绍 当我们的项目开发完毕后&#xff0c;我们需要将项目打包、部署到服务器上&#xff0c;供用户来使用。 目前&#xff0c;常见的部署方式有两种&#xff1a; 后端部署 前后端分离部署 1-1 后端部署 这是最古老的部署方式&#xff0c;也是…...

资源限流 + 本地分布式多重锁——高并发性能挡板,隔绝无效流量请求

前言 在高并发分布式下&#xff0c;我们往往采用分布式锁去维护一个同步互斥的业务需求&#xff0c;但是大家细想一下&#xff0c;在一些高TPS的业务场景下&#xff0c;让这些请求全部卡在获取分布式锁&#xff0c;这会造成什么问题&#xff1f; 瞬时高并发压垮系统 众所周知…...

day52【子序列】300.最长递归子序列 674.最长连续递增序列 718.最长重复子数组

文章目录 300.最长递增子序列674.最长连续递增序列718.最长重复子数组 300.最长递增子序列 题目链接&#xff1a;力扣链接 讲解链接&#xff1a;代码随想录链接 题意&#xff1a;给你一个整数数组 nums &#xff0c;找到其中最长严格递增子序列的长度。 子序列 是由数组派生而…...

计算机视觉 计算机视觉识别是什么?

计算机视觉识别&#xff08;Computer Vision Recognition&#xff09;是计算机科学和人工智能领域中的一个重要分支&#xff0c;它致力于使计算机系统能够模拟和理解人类视觉的过程&#xff0c;从而能够自动识别、分析和理解图像或视频中的内容。这一领域的发展旨在让计算机具备…...

Make.com实现多个APP应用的自动化的入门指南

Make.com是一款基于云的自动化平台&#xff0c;可帮助用户将多个应用程序连接在一起&#xff0c;并通过设置自动化流程来简化日常任务。Make.com提供丰富的API集成&#xff0c;支持连接各种流行的应用程序&#xff0c;包括社交媒体、电子商务、CRM等。 使用Make.com实现多个AP…...

LLMs之HFKR:HFKR(基于大语言模型实现异构知识融合的推荐算法)的简介、原理、性能、实现步骤、案例应用之详细攻略

LLMs之HFKR:HFKR(基于大语言模型实现异构知识融合的推荐算法)的简介、原理、性能、实现步骤、案例应用之详细攻略 目录 HFKR的简介 异构知识融合:一种基于LLM的个性化推荐新方法...

多模态 多引擎 超融合 新生态!2023亚信科技AntDB数据库8.0产品发布

9月20日&#xff0c;以“多模态 多引擎 超融合 新生态”为主题的亚信科技AntDB数据库8.0产品发布会成功举办&#xff0c;从技术和生态两个角度全方位展示了AntDB数据库第8次大型能力升级和生态建设成果。浙江移动、用友、麒麟软件、华录高诚、金云智联等行业伙伴及业界专家共同…...

elasticsearch无法访问9200端口

近期部署elasticsearch后&#xff0c;启动时发现一直报如下错误: curl: (7) Failed connect to localhost:9200&#xff1b; Connection refused 部署的版本为elasticsearch-7.13.2,排查原因是因为开启了ssl认证。 解决方法: 在/opt/software/elasticsearch-7.13.2/config下…...

【Linux】进程等待

文章目录 进程等待进程等待必要性实验(见见猪跑)进程等待的方法wait方法waitpid**方法**宏的使用方法获取子进程status 阻塞VS非阻塞概念对比非阻塞有什么好处 具体代码实现进程的阻塞等待方式:进程的非阻塞等待方式:让父进程做其他任务 进程等待 进程等待必要性 之前讲过&am…...

电视「沉浮录」:跌出家电“三大件”?

【潮汐商业评论/原创】 “这年头谁还看电视&#xff0c;家里电视近一年都没打开过了&#xff0c;我明天就打算把它二手卖掉。”想到已落灰许久的电视机&#xff0c;Andy打开了二手平台。 “要不是这几年孩子网课多&#xff0c;我是真没考虑换新电视&#xff0c;家里用了8年的…...

前端实现调用打印机和小票打印(TSPL )功能

Ⅰ- 壹 - 使用需求 前端 的方式 点击这个按钮&#xff0c;直接让打印机打印我想要的东西 Ⅱ - 贰 - 小票打印 目前比较好的方式就是直接用 TSPL 标签打印指令集, 基础环境就不多说了,这个功能的实现就是利用usb发送指令,现在缺少个来让我们能够和usb沟通的工具,下面这就是推…...

串口通信(6)应用定时器中断+串口中断实现接收一串数据

本文为博主 日月同辉&#xff0c;与我共生&#xff0c;csdn原创首发。希望看完后能对你有所帮助&#xff0c;不足之处请指正&#xff01;一起交流学习&#xff0c;共同进步&#xff01; > 发布人&#xff1a;日月同辉,与我共生_单片机-CSDN博客 > 欢迎你为独创博主日月同…...

浅谈 React Hooks

React Hooks 是 React 16.8 引入的一组 API&#xff0c;用于在函数组件中使用 state 和其他 React 特性&#xff08;例如生命周期方法、context 等&#xff09;。Hooks 通过简洁的函数接口&#xff0c;解决了状态与 UI 的高度解耦&#xff0c;通过函数式编程范式实现更灵活 Rea…...

观成科技:隐蔽隧道工具Ligolo-ng加密流量分析

1.工具介绍 Ligolo-ng是一款由go编写的高效隧道工具&#xff0c;该工具基于TUN接口实现其功能&#xff0c;利用反向TCP/TLS连接建立一条隐蔽的通信信道&#xff0c;支持使用Let’s Encrypt自动生成证书。Ligolo-ng的通信隐蔽性体现在其支持多种连接方式&#xff0c;适应复杂网…...

五年级数学知识边界总结思考-下册

目录 一、背景二、过程1.观察物体小学五年级下册“观察物体”知识点详解&#xff1a;由来、作用与意义**一、知识点核心内容****二、知识点的由来&#xff1a;从生活实践到数学抽象****三、知识的作用&#xff1a;解决实际问题的工具****四、学习的意义&#xff1a;培养核心素养…...

Xen Server服务器释放磁盘空间

disk.sh #!/bin/bashcd /run/sr-mount/e54f0646-ae11-0457-b64f-eba4673b824c # 全部虚拟机物理磁盘文件存储 a$(ls -l | awk {print $NF} | cut -d. -f1) # 使用中的虚拟机物理磁盘文件 b$(xe vm-disk-list --multiple | grep uuid | awk {print $NF})printf "%s\n"…...

C++.OpenGL (20/64)混合(Blending)

混合(Blending) 透明效果核心原理 #mermaid-svg-SWG0UzVfJms7Sm3e {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-icon{fill:#552222;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-text{fill…...

从 GreenPlum 到镜舟数据库:杭银消费金融湖仓一体转型实践

作者&#xff1a;吴岐诗&#xff0c;杭银消费金融大数据应用开发工程师 本文整理自杭银消费金融大数据应用开发工程师在StarRocks Summit Asia 2024的分享 引言&#xff1a;融合数据湖与数仓的创新之路 在数字金融时代&#xff0c;数据已成为金融机构的核心竞争力。杭银消费金…...

怎么让Comfyui导出的图像不包含工作流信息,

为了数据安全&#xff0c;让Comfyui导出的图像不包含工作流信息&#xff0c;导出的图像就不会拖到comfyui中加载出来工作流。 ComfyUI的目录下node.py 直接移除 pnginfo&#xff08;推荐&#xff09;​​ 在 save_images 方法中&#xff0c;​​删除或注释掉所有与 metadata …...

uniapp 实现腾讯云IM群文件上传下载功能

UniApp 集成腾讯云IM实现群文件上传下载功能全攻略 一、功能背景与技术选型 在团队协作场景中&#xff0c;群文件共享是核心需求之一。本文将介绍如何基于腾讯云IMCOS&#xff0c;在uniapp中实现&#xff1a; 群内文件上传/下载文件元数据管理下载进度追踪跨平台文件预览 二…...

redis和redission的区别

Redis 和 Redisson 是两个密切相关但又本质不同的技术&#xff0c;它们扮演着完全不同的角色&#xff1a; Redis: 内存数据库/数据结构存储 本质&#xff1a; 它是一个开源的、高性能的、基于内存的 键值存储数据库。它也可以将数据持久化到磁盘。 核心功能&#xff1a; 提供丰…...

Linux中《基础IO》详细介绍

目录 理解"文件"狭义理解广义理解文件操作的归类认知系统角度文件类别 回顾C文件接口打开文件写文件读文件稍作修改&#xff0c;实现简单cat命令 输出信息到显示器&#xff0c;你有哪些方法stdin & stdout & stderr打开文件的方式 系统⽂件I/O⼀种传递标志位…...