MyBatis Plus 解决大数据量查询慢问题

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MyBatis Plus 解决大数据量查询慢问题

MyBatis Plus 解决大数据量查询慢问题

@[toc]
大数据量操作的场景大致如下:

  • 数据迁移

  • 数据导出

  • 批量处理数据

在实际工作中当指定查询数据过大时,我们一般使用分页查询的方式一页一页的将数据放到内存处理。但有些情况不需要分页的方式查询数据或分很大一页查询数据时,如果一下子将数据全部加载出来到内存中,很可能会发生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,学习官网:https://baomidou.com/

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

流式查询

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

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

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

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

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

MyBatis 流式查询接口

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

  • Cursor 是可关闭的;

  • Cursor 是可遍历的。

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

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

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

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

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

为什么要用流式查询?

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

分库分表场景下,单个表的查询结果集虽然不大,但如果某个查询跨了多个库多个表,又要做结果集的合并、排序等动作,依然有可能撑爆内存;详细研究了sharding-sphere的代码不难发现,除了group byorder 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();

代码示例

大数据量下使用MybatisPlus流式查询导出商品信息数据到Excel

参考文章:https://mp.weixin.qq.com/s?__biz=Mzg4ODA3MDg2OA==&mid=2247485154&idx=1&sn=beb331bcddbf51f1b3b5127ba42b3eeb&scene=19#wechat_redirect

调整查询商品信息的具体代码

TtlProductInfoMapper.java
    /**
     * 流式查询,查询商品信息
     *
     * @param sql
     * @param resultHandler
     */
    @Select("${sql}")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    @ResultType(TtlProductInfoPo.class)
    void selectProductList(@Param("sql") String sql, ResultHandler<TtlProductInfoPo> resultHandler);
TtlProductInfoServiceImpl.java
    @Override
    public List<TtlProductInfoPo> listProduct(Map<String, Object> map) {
   
        // 查询商品信息
        List<TtlProductInfoPo> list = new ArrayList<>();
        String sql = "select * from ttl_product_info limit " + map.get("offset") + " , " + map.get("limit");
        ttlProductInfoMapper.selectProductList(sql, resultContext -> {
   
            TtlProductInfoPo resultObject = resultContext.getResultObject();
            list.add(resultObject);
        });

        return list;
    }

说明:

SpringBoot集成mybatisplus这里不再详细介绍,项目代码工程获取方式:后台回复:【流式查询】即可获取哦。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
相关文章
|
5天前
|
SQL Java 关系型数据库
Mybatis多表关联查询与动态SQL(下)
Mybatis多表关联查询与动态SQL
18 0
|
5天前
|
SQL Java 数据库连接
Mybatis多表关联查询与动态SQL(上)
Mybatis多表关联查询与动态SQL
10 0
|
5天前
|
SQL 缓存 Java
mybatis 一对多查询
mybatis 一对多查询
23 0
|
5天前
|
SQL
MyBatis-Plus-Join关联查询
MyBatis-Plus-Join关联查询
|
5天前
|
SQL XML Java
MyBatis-Plus多表关联查询
MyBatis-Plus多表关联查询
|
5天前
|
分布式计算 DataWorks 数据管理
DataWorks操作报错合集之DataWorks中udf开发完后,本地和在MaxCompute的工作区可以执行函数查询,但是在datawork里报错FAILED: ODPS-0130071:[2,5],是什么原因
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
29 0
|
5天前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之在 DataWorks 中,使用Oracle作为数据源进行数据映射和查询,如何更改数据源为MaxCompute或其他类型
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
33 1
|
5天前
|
分布式计算 大数据 调度
MaxCompute产品使用合集之大数据计算MaxCompute底层加速查询的原理是什么
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
5天前
|
SQL 分布式计算 运维
MaxCompute产品使用合集之在大数据计算MaxCompute中,怎么查看工作空间中正在运行的查询和任务
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
5天前
|
存储 分布式计算 DataWorks
MaxCompute产品使用合集之大数据计算MaxCompute默认限制查询结果集的大小为1万条,怎么解开限制
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。