您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

从MySQL读取100w数据进行处理,应该怎么做?

时间:2023-03-05 14:37:04  来源:CSDN  作者:旷野历程

背景

大数据量操作的场景大致如下:

 

  • 数据迁移

  • 数据导出

  • 批量处理数据

 

在实际工作中当指定查询数据过大时,我们一般使用分页查询的方式一页一页的将数据放到内存处理。但有些情况不需要分页的方式查询数据或分很大一页查询数据时,如果一下子将数据全部加载出来到内存中,很可能会发生OOM(内存溢出);而且查询会很慢,因为框架耗费大量的时间和内存去把数据库查询的结果封装成我们想要的对象(实体类)。

举例:在业务系统需要从 MySQL 数据库里读取 100w 数据行进行处理,应该怎么做?

做法通常如下:

 

  • 常规查询: 一次性读取 100w 数据到 JVM 内存中,或者分页读取

  • 流式查询: 建立长连接,利用服务端游标,每次读取一条加载到 JVM 内存(多次获取,一次一行)

  • 游标查询: 和流式一样,通过 fetchSize 参数,控制一次读取多少条数据(多次获取,一次多行)

     

常规查询

 

默认情况下,完整的检索结果集会将其存储在内存中。在大多数情况下,这是最有效的操作方式,并且由于 MySQL 网络协议的设计,因此更易于实现。

举例:假设单表 100w 数据量,一般会采用分页的方式查询:

@MApper
public interface BigDataSearchMapper extends BaseMapper {

 

@Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
Page pageList(@Param("page") Page page, @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

}

注:该示例使用的 MyBatisPlus。

该方式比较简单,如果在不考虑 LIMIT 深分页优化情况下,估计你的数据库服务器就噶皮了,或者你能等上几十分钟或几小时,甚至几天时间检索数据。

流式查询

流式查询指的是查询成功后不是返回一个集合而是返回一个迭代器,应用每次从迭代器取一条查询结果。流式查询的好处是能够降低内存使用。

如果没有流式查询,我们想要从数据库取 100w 条记录而又没有足够的内存时,就不得不分页查询,而分页查询效率取决于表设计,如果设计的不好,就无法执行高效的分页查询。因此流式查询是一个数据库访问框架必须具备的功能。

MyBatis 中使用流式查询避免数据量过大导致 OOM ,但在流式查询的过程当中,数据库连接是保持打开状态的,因此要注意的是:

 

  • 执行一个流式查询后,数据库访问框架就不负责关闭数据库连接了,需要应用在取完数据后自己关闭。

  • 必须先读取(或关闭)结果集中的所有行,然后才能对连接发出任何其他查询,否则将引发异常。

     

MyBatis 流式查询接口

 

MyBatis 提供了一个叫 org.Apache.ibatis.cursor.Cursor 的接口类用于流式查询,这个接口继承了 JAVA.io.Closeable 和 java.lang.Iterable 接口,由此可知:

 

  • Cursor 是可关闭的;

  • Cursor 是可遍历的。

 

除此之外,Cursor 还提供了三个方法:

 

  • isOpen(): 用于在取数据之前判断 Cursor 对象是否是打开状态。只有当打开时 Cursor 才能取数据;

  • isConsumed(): 用于判断查询结果是否全部取完。

  • getCurrentIndex(): 返回已经获取了多少条数据

 

使用流式查询,则要保持对产生结果集的语句所引用的表的并发访问,因为其查询会独占连接,所以必须尽快处理。另外,推荐公众号Java精选,回复java面试,获取面试资料,支持在线刷题。

为什么要用流式查询?

如果有一个很大的查询结果需要遍历处理,又不想一次性将结果集装入客户端内存,就可以考虑使用流式查询;

分库分表场景下,单个表的查询结果集虽然不大,但如果某个查询跨了多个库多个表,又要做结果集的合并、排序等动作,依然有可能撑爆内存;详细研究了sharding-sphere的代码不难发现,除了group by与order by字段不一样之外,其他的场景都非常适合使用流式查询,可以最大限度的降低对客户端内存的消耗。

游标查询

对大量数据进行处理时,为防止内存泄漏情况发生,也可以采用游标方式进行数据查询处理。这种处理方式比常规查询要快很多。

当查询百万级的数据的时候,还可以使用游标方式进行数据查询处理,不仅可以节省内存的消耗,而且还不需要一次性取出所有数据,可以进行逐条处理或逐条取出部分批量处理。一次查询指定 fetchSize 的数据,直到把数据全部处理完。

Mybatis 的处理加了两个注解:@Options 和 @ResultType

@Mapper
public interface BigDataSearchMapper extends BaseMapper {

 

// 方式一 多次获取,一次多行
@Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000)
Page pageList(@Param("page") Page page, @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

// 方式二 一次获取,一次一行
@Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 100000)
@ResultType(BigDataSearchEntity.class)
void listData(@Param(Constants.WRAPPER) QueryWrapper queryWrapper, ResultHandler handler);

}

@Options

  • ResultSet.FORword_ONLY:结果集的游标只能向下滚动

  • ResultSet.SCROLL_INSENSITIVE:结果集的游标可以上下移动,当数据库变化时,当前结果集不变

  • ResultSet.SCROLL_SENSITIVE:返回可滚动的结果集,当数据库变化时,当前结果集同步改变

  • fetchSize:每次获取量

     

@ResultType

 

  • @ResultType(BigDataSearchEntity.class):转换成返回实体类型

     

注意:返回类型必须为 void ,因为查询的结果在 ResultHandler 里处理数据,所以这个 hander 也是必须的,可以使用 lambda 实现一个依次处理逻辑。

 

注意:

虽然上面的代码中都有 @Options 但实际操作却有不同:

 

  • 方式一是多次查询,一次返回多条;

  • 方式二是一次查询,一次返回一条;

     

原因:

Oracle 是从服务器一次取出 fetch size 条记录放在客户端,客户端处理完成一个批次后再向服务器取下一个批次,直到所有数据处理完成。

MySQL 是在执行 ResultSet.next() 方法时,会通过数据库连接一条一条的返回。flush buffer 的过程是阻塞式的,如果网络中发生了拥塞,send buffer 被填满,会导致 buffer 一直 flush 不出去,那 MySQL 的处理线程会阻塞,从而避免数据把客户端内存撑爆。

非流式查询和流式查询区别:

 

  • 非流式查询:内存会随着查询记录的增长而近乎直线增长。

  • 流式查询:内存会保持稳定,不会随着记录的增长而增长。其内存大小取决于批处理大小BATCH_SIZE的设置,该尺寸越大,内存会越大。所以BATCH_SIZE应该根据业务情况设置合适的大小。

     

另外要切记每次处理完一批结果要记得释放存储每批数据的临时容器,即上文中的gxids.clear();

 

作者:旷野历程 https://blog.csdn.NET/xhaimail/article/details/119386460


Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
MySQL提供了许多常用的字符串函数,以下是其中一些常用的字符串函数和用法:1、CONCATCONCAT函数用于连接两个或多个字符串。以下是一个示例:SELECT CONCAT('Hello', &#3...【详细内容】
2023-03-07  Tags: MySQL  点击:(5)  评论:(0)  加入收藏
一般情况下,我们会在一个索引上较多的使用等值查询或者范围查询,此时索引大多可以帮助我们极快的查询出我们需要的数据。那当我们在where条件中对索引列使用!=查询,索引还能发...【详细内容】
2023-03-07  Tags: MySQL  点击:(2)  评论:(0)  加入收藏
关于如何实现分布式锁,大家可能对基于Redis​实现比较熟悉,但是往往很多情况是一些并发量不大的项目用不上Redis,Redis往往适用于并发量比较大的场景。但是MySQL基本都是有的,所...【详细内容】
2023-03-06  Tags: MySQL  点击:(3)  评论:(0)  加入收藏
背景大数据量操作的场景大致如下: 数据迁移 数据导出 批量处理数据 在实际工作中当指定查询数据过大时,我们一般使用分页查询的方式一页一页的将数据放到内存处理。但有些...【详细内容】
2023-03-05  Tags: MySQL  点击:(0)  评论:(0)  加入收藏
今天就简单说明现象及引起日志暴涨的原因,希望对你有所帮助!1、现象今天协助其他同学排查问题的时候,发现数据库错误日志文件已经有9G以上了,打开内容查看如下:=================...【详细内容】
2023-03-05  Tags: MySQL  点击:(3)  评论:(0)  加入收藏
如果日常的开发和维护均在类似dos窗口中进行,对于编程初学者来说,上手就略微有点困难,增加了学习成本。我们一般使用mysql图形管理工具来连接Mysql,然后在图形化界面上操作Mysql...【详细内容】
2023-03-05  Tags: MySQL  点击:(3)  评论:(0)  加入收藏
那不知道大家有没有深入了解过,当我们的一条SQL命令被执行时,MySQL是如何把数据从硬盘/内存中查出来并展示到用户面前的呢?对于很多开发小伙伴来说,每天写SQL是必不可少的一项工...【详细内容】
2023-03-03  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
本次分享了一次关于 MySQL 连接挂死问题排查的心路历程,由于环境搭建的工作量巨大,而且该问题复现存在偶然性,整个分析过程还是有些坎坷的(其中也踩了坑)。一、背景近期由测试反...【详细内容】
2023-03-03  Tags: MySQL  点击:(9)  评论:(0)  加入收藏
本文通过逻辑方式、物理方式原地升级来介绍MySQL5.6 升级至MySQL5.7的方法,并介绍其使用场景。当前不少系统的数据库依旧是MySQL5.6,由于MySQL5.7及MySQL8.0在性能及安全方面...【详细内容】
2023-02-27  Tags: MySQL  点击:(11)  评论:(0)  加入收藏
MySQL中如何优化LIMIT分页?这个问题我们今天一起来聊一聊。以下是一个示例,演示如何优化MySQL 中limit 分页查询的性能:假设我们有一个名为 users 的表,其中存储了 1,000,000 条...【详细内容】
2023-02-27  Tags: MySQL  点击:(9)  评论:(0)  加入收藏
▌简易百科推荐
MySQL提供了许多常用的字符串函数,以下是其中一些常用的字符串函数和用法:1、CONCATCONCAT函数用于连接两个或多个字符串。以下是一个示例:SELECT CONCAT('Hello', &#3...【详细内容】
2023-03-07    今日头条  Tags:MySQL   点击:(5)  评论:(0)  加入收藏
一般情况下,我们会在一个索引上较多的使用等值查询或者范围查询,此时索引大多可以帮助我们极快的查询出我们需要的数据。那当我们在where条件中对索引列使用!=查询,索引还能发...【详细内容】
2023-03-07    今日头条  Tags:MySQL   点击:(2)  评论:(0)  加入收藏
键值对存储数据库是NoSQL数据库中的一种类型,也是最简单的NoSQL数据库。键对值对存储数据库中的数据是以键值对的形式来存储的。1.键值对存储数据库键值对存储数据库是NoSQL...【详细内容】
2023-03-06  黑马程序员  今日头条  Tags:NoSQL   点击:(7)  评论:(0)  加入收藏
关于如何实现分布式锁,大家可能对基于Redis​实现比较熟悉,但是往往很多情况是一些并发量不大的项目用不上Redis,Redis往往适用于并发量比较大的场景。但是MySQL基本都是有的,所...【详细内容】
2023-03-06  JAVA旭阳  微信公众号  Tags:MySQL   点击:(3)  评论:(0)  加入收藏
背景大数据量操作的场景大致如下: 数据迁移 数据导出 批量处理数据 在实际工作中当指定查询数据过大时,我们一般使用分页查询的方式一页一页的将数据放到内存处理。但有些...【详细内容】
2023-03-05  旷野历程   CSDN  Tags:MySQL   点击:(0)  评论:(0)  加入收藏
今天就简单说明现象及引起日志暴涨的原因,希望对你有所帮助!1、现象今天协助其他同学排查问题的时候,发现数据库错误日志文件已经有9G以上了,打开内容查看如下:=================...【详细内容】
2023-03-05  数据库干货铺  今日头条  Tags:MySQL   点击:(3)  评论:(0)  加入收藏
如果日常的开发和维护均在类似dos窗口中进行,对于编程初学者来说,上手就略微有点困难,增加了学习成本。我们一般使用mysql图形管理工具来连接Mysql,然后在图形化界面上操作Mysql...【详细内容】
2023-03-05  黑马程序员  今日头条  Tags:MySQL   点击:(3)  评论:(0)  加入收藏
那不知道大家有没有深入了解过,当我们的一条SQL命令被执行时,MySQL是如何把数据从硬盘/内存中查出来并展示到用户面前的呢?对于很多开发小伙伴来说,每天写SQL是必不可少的一项工...【详细内容】
2023-03-03  程序员拾山  今日头条  Tags:MySQL   点击:(12)  评论:(0)  加入收藏
本次分享了一次关于 MySQL 连接挂死问题排查的心路历程,由于环境搭建的工作量巨大,而且该问题复现存在偶然性,整个分析过程还是有些坎坷的(其中也踩了坑)。一、背景近期由测试反...【详细内容】
2023-03-03    dbaplus社群  Tags:MySQL   点击:(9)  评论:(0)  加入收藏
本文通过逻辑方式、物理方式原地升级来介绍MySQL5.6 升级至MySQL5.7的方法,并介绍其使用场景。当前不少系统的数据库依旧是MySQL5.6,由于MySQL5.7及MySQL8.0在性能及安全方面...【详细内容】
2023-02-27  数据库干货铺  今日头条  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
站内最新
站内热门
站内头条