面试官:从 MySQL 读取 100w 数据进行处理,应该怎么做?问倒一大遍!

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 面试官:从 MySQL 读取 100w 数据进行处理,应该怎么做?问倒一大遍!

背景


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

  • 数据迁移
  • 数据导出
  • 批量处理数据

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


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


做法通常如下:


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

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

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

常规查询

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


举例:


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


@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page, @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper);
}




注:该示例使用的 MybatisPlus


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


推荐一个开源免费的 Spring Boot 最全教程:


https://github.com/javastacks/spring-boot-best-practice


流式查询

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


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


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


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

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

MyBatis 流式查询接口

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


Cursor 是可关闭的;

Cursor 是可遍历的。


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


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

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

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

使用流式查询,则要保持对产生结果集的语句所引用的表的并发访问,因为其 查询会独占连接,所以必须尽快处理


为什么要用流式查询?

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


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


游标查询

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


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


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


@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
    // 方式一 多次获取,一次多行
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000)
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page, @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> 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<BigDataSearchEntity> queryWrapper, ResultHandler<BigDataSearchEntity> 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();



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 SQL 关系型数据库
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
|
19天前
|
存储 关系型数据库 MySQL
美团面试:MySQL为什么 不用 Docker部署?
45岁老架构师尼恩在读者交流群中分享了关于“MySQL为什么不推荐使用Docker部署”的深入分析。通过系统化的梳理,尼恩帮助读者理解为何大型MySQL数据库通常不使用Docker部署,主要涉及性能、管理复杂度和稳定性等方面的考量。文章详细解释了有状态容器的特点、Docker的资源隔离问题以及磁盘IO性能损耗,并提供了小型MySQL使用Docker的最佳实践。此外,尼恩还介绍了Share Nothing架构的优势及其应用场景,强调了配置管理和数据持久化的挑战。最后,尼恩建议读者参考《尼恩Java面试宝典PDF》以提升技术能力,更好地应对面试中的难题。
|
12天前
|
Java 关系型数据库 MySQL
SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
通过详细的步骤和示例代码,您可以在 SpringBoot 项目中成功集成 Flink CDC,并实时追踪 MySQL 数据库的变动。
104 43
|
2天前
|
SQL 关系型数据库 MySQL
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
1.请解释什么是MVCC,它在数据库中的作用是什么? 2.在MySQL中,MVCC是如何实现的?请简述其工作原理。 3.MVCC是如何解决读-写和写-写冲突的? 4.在并发环境中,当多个事务同时读取同一行数据时,MVCC是如何保证每个事务看到的数据版本是一致的? 5.MVCC如何帮助提高数据库的并发性能?
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
|
5天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
9天前
|
缓存 安全 Java
面试中的难题:线程异步执行后如何共享数据?
本文通过一个面试故事,详细讲解了Java中线程内部开启异步操作后如何安全地共享数据。介绍了异步操作的基本概念及常见实现方式(如CompletableFuture、ExecutorService),并重点探讨了volatile关键字、CountDownLatch和CompletableFuture等工具在线程间数据共享中的应用,帮助读者理解线程安全和内存可见性问题。通过这些方法,可以有效解决多线程环境下的数据共享挑战,提升编程效率和代码健壮性。
37 6
|
1月前
|
NoSQL 关系型数据库 MySQL
招行面试:高并发写,为什么不推荐关系数据?
资深架构师尼恩针对高并发场景下为何不推荐使用关系数据库进行数据写入进行了深入剖析。文章详细解释了关系数据库(如MySQL)在高并发写入时的性能瓶颈,包括存储机制和事务特性带来的开销,并对比了NoSQL数据库的优势。通过具体案例和理论分析,尼恩为读者提供了系统化的解答,帮助面试者更好地应对类似问题,提升技术实力。此外,尼恩还分享了多个高并发系统的解决方案及优化技巧,助力开发者在面试中脱颖而出。 文章链接:[原文链接](https://mp.weixin.qq.com/s/PKsa-7eZqXDg3tpgJKCAAw) 更多技术资料和面试宝典可关注【技术自由圈】获取。
|
1月前
|
存储 SQL 关系型数据库
MySQL 面试题
MySQL 的一些基础面试题
|
2月前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
分布式计算 关系型数据库 MySQL
E-Mapreduce如何处理RDS的数据
目前网站的一些业务数据存在了数据库中,这些数据往往需要做进一步的分析,如:需要跟一些日志数据关联分析,或者需要进行一些如机器学习的分析。在阿里云上,目前E-Mapreduce可以满足这类进一步分析的需求。
4988 0