关于mysql:炸了使用-MyBatis-查询千万数据量

因为当初 ORM 框架的成熟使用，很多小伙伴对于 JDBC 的概念有些单薄，ORM 框架底层其实是通过 JDBC 操作的 DB

JDBC（JavaDataBase Connectivity）是 Java 数据库连贯, 说的直白点就是应用 Java 语言操作数据库

由 SUN 公司提供出一套拜访数据库的标准 API，并提供绝对应的连贯数据库协定规范，而后 各厂商依据标准提供一套拜访自家数据库的 API 接口

文章大数据量操作外围围绕 JDBC 开展，目录构造如下：

• MySQL JDBC 大数据量操作

  • 惯例查问
  • 流式查问
  • 游标查问
  • JDBC RowData
  • JDBC 通信原理

• 流式游标内存剖析

  • 单次调用内存应用
  • 并发调用内存应用
• MyBatis 如何应用流式查问
• 结言

MySql JDBC 大数据量操作

整篇文章以大数据量操作为议题，通过开发过程中的需要引出相干知识点

1. 迁徙数据
2. 导出数据
3. 批量解决数据

一般而言笔者认为在 Java Web 程序里，可能被称为大数据量的，几十万到千万不等，再高的话 Java（WEB 利用）解决就不怎么适合了

举个例子，当初业务零碎须要从 MySQL 数据库里读取 500w 数据行进行解决，应该怎么做

1. 惯例查问，一次性读取 500w 数据到 JVM 内存中，或者分页读取
2. 流式查问，建设长连贯，利用服务端游标，每次读取一条加载到 JVM 内存
3. 游标查问，和流式一样，通过 fetchSize 参数，管制一次读取多少条数据

文章首发自公众号：源码趣味圈，专一分享高并发、分布式、框架底层源码等常识

惯例查问

默认状况下，残缺的检索后果集会将其存储在内存中。在大多数状况下，这是最无效的操作形式，并且因为 MySQL 网络协议的设计，因而更易于实现

假如单表 500w 数据量，没有人会一次性加载到内存中，个别会采纳分页的形式

@SneakyThrows
@Override
public void pageQuery() {@Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement();
    long start = System.currentTimeMillis();
    long offset = 0;
    int size = 100;
    while (true) {String sql = String.format("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE LIMIT %s, %s", offset, size);
        @Cleanup ResultSet rs = stmt.executeQuery(sql);
        long count = loopResultSet(rs);
        if (count == 0) break;
        offset += size;
    }

    log.info("???????????? 分页查问耗时 :: {}", System.currentTimeMillis() - start);
}

上述形式比较简单，然而在不思考 LIMIT 深分页优化状况下，线上数据库服务器就凉了，亦或者你能等个几天工夫检索数据

MySQL 千万数据量深分页优化, 回绝线上故障！

流式查问

如果你正在应用具备大量数据行的 ResultSet，并且无奈在 JVM 中为其调配所需的内存堆空间，则能够通知驱动程序从后果流中返回一行

流式查问有一点须要留神：必须先读取（或敞开）后果集中的所有行，而后能力对连贯收回任何其余查问，否则将引发异样

应用流式查问，则要放弃对产生后果集的语句所援用的表的并发拜访，因为其 查问会独占连贯，所以必须尽快解决

@SneakyThrows
public void streamQuery() {@Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);

      long start = System.currentTimeMillis();
    @Cleanup ResultSet rs = stmt.executeQuery("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE");
    loopResultSet(rs);
    log.info("???????????? 流式查问耗时 :: {}", (System.currentTimeMillis() - start) / 1000);
}

流式查问库表数据量 500w 单次调用工夫耗费：≈ 6s

游标查问

SpringBoot 2.x 版本默认连接池为 HikariPool，连贯对象是 HikariProxyConnection，所以下述设置游标形式就不可行了

((JDBC4Connection) conn).setUseCursorFetch(true);

须要在数据库连贯信息里拼接 &useCursorFetch=true。其次设置 Statement 每次读取数据数量，比方一次读取 1000

@SneakyThrows
public void cursorQuery() {@Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(1000);

    long start = System.currentTimeMillis();
    @Cleanup ResultSet rs = stmt.executeQuery("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE");
    loopResultSet(rs);
    log.info("???????????? 游标查问耗时 :: {}", (System.currentTimeMillis() - start) / 1000);
}

游标查问库表数据量 500w 单次调用工夫耗费：≈ 18s

JDBC RowData

下面都应用到了办法 loopResultSet，办法外部只是进行了 while 循环，惯例、流式、游标查问的外围点在于 next 办法

@SneakyThrows
private Long loopResultSet(ResultSet rs) {while (rs.next()) {// 业务操作}
    return xx;
}

ResultSet.next() 的逻辑是实现类 ResultSetImpl 每次都从 RowData 获取下一行的数据。RowData 是一个接口，实现关系图如下

默认状况下 ResultSet 会应用 RowDataStatic 实例，在生成 RowDataStatic 对象时就会把 ResultSet 中所有记录读到内存里，之后通过 next() 再一条条从内存中读

RowDataCursor 的调用为批处理，而后进行外部缓存，流程如下：

1. 首先会查看本人外部缓冲区是否有数据没有返回，如果有则返回下一行
2. 如果都读取结束，向 MySQL Server 触发一个新的申请读取 fetchSize 数量后果
3. 并将返回后果缓冲到外部缓冲区，而后返回第一行数据

当采纳流式解决时，ResultSet 应用的是 RowDataDynamic 对象，而这个对象 next() 每次调用都会发动 IO 读取单行数据

总结来说就是，默认的 RowDataStatic 读取全副数据到客户端内存中，也就是咱们的 JVM；RowDataCursor 一次读取 fetchSize 行，生产实现再发动申请调用；RowDataDynamic 每次 IO 调用读取一条数据

JDBC 通信原理

一般查问

在 JDBC 与 MySQL 服务端的交互是通过 Socket 实现的，对应到网络编程，能够把 MySQL 当作一个 SocketServer，因而一个残缺的申请链路应该是：

JDBC 客户端 -> 客户端 Socket -> MySQL -> 检索数据返回 -> MySQL 内核 Socket 缓冲区 -> 网络 -> 客户端 Socket Buffer -> JDBC 客户端

一般查问的形式在查问大数据量时，所在 JVM 可能会凉凉，起因如下：

1. MySQL Server 会将检索出的 SQL 后果集通过输入流写入到内核对应的 Socket Buffer
2. 内核缓冲区通过 JDBC 发动的 TCP 链路进行回传数据，此时数据会先进入 JDBC 客户端所在内核缓冲区
3. JDBC 发动 SQL 操作后，程序会被阻塞在输出流的 read 操作上，当缓冲区有数据时，程序会被唤醒进而将缓冲区数据读取到 JVM 内存中
4. MySQL Server 会一直发送数据，JDBC 一直读取缓冲区数据到 Java 内存中，尽管此时数据已到 JDBC 所在程序本地，然而 JDBC 还没有对 execute 办法调用处进行响应，因为须要等到对应数据读取结束才会返回
5. 弊病就不言而喻了，如果查问数据量过大，会一直经验 GC，而后就是内存溢出

游标查问

通过上文得悉，游标能够解决一般查问大数据量的内存溢出问题，然而

小伙伴有没有思考过这么一个问题，MySQL 不晓得客户端程序何时生产实现，此时另一连贯对该表造成 DML 写入操作应该如何解决？

其实，在咱们应用游标查问时，MySQL 须要建设一个长期空间来寄存须要被读取的数据，所以不会和 DML 写入操作产生抵触

然而游标查问会引发以下景象：

1. IOPS 飙升 ，因为须要返回的数据须要写入到长期空间中， 存在大量的 IO 读取和写入，此流程可能会引起其它业务的写入抖动
2. 磁盘空间飙升 ，因为写入长期空间的数据是在原表之外的，如果表数据过大， 极其状况下可能会导致数据库磁盘写满 ，这时网络输入时没有变动的。而写入长期空间的数据会在 读取实现或客户端发动 ResultSet#close 操作时由 MySQL 回收
3. 客户端 JDBC 发动 SQL 查问，可能会有长时间期待 SQL 响应，这段时间为服务端筹备数据阶段。然而 一般查问等待时间与游标查问等待时间原理上是不统一的，前者是统一在读取网络缓冲区的数据，没有响应到业务层面；后者是 MySQL 在筹备长期数据空间，没有响应到 JDBC
4. 数据筹备实现后，进行到传输数据阶段，网络响应开始飙升，IOPS 由 ” 读写 ” 转变为 ” 读取 ”

采纳游标查问的形式 通信效率比拟低，因为客户端生产完 fetchSize 行数据，就须要发动申请到服务端申请，在数据库后期筹备阶段 IOPS 会十分高，占用大量的磁盘空间以及性能

流式查问

当客户端与 MySQL Server 端建设起连贯并且交互查问时，MySQL Server 会通过输入流将 SQL 后果集返回输入，也就是 向本地的内核对应的 Socket Buffer 中写入数据，而后将内核中的数据通过 TCP 链路回传数据到 JDBC 对应的服务器内核缓冲区

1. JDBC 通过输出流 read 办法去读取内核缓冲区数据，因为开启了流式读取，每次业务程序接管到的数据只有一条
2. MySQL 服务端会向 JDBC 代表的客户端内核源源不断的输送数据，直到客户端申请 Socket 缓冲区满，这时的 MySQL 服务端会阻塞
3. 对于 JDBC 客户端而言，数据每次读取都是从本机器的内核缓冲区，所以性能会更快一些，个别状况不用放心本机内核无数据生产（除非 MySQL 服务端传递来的数据，在客户端不做任何业务逻辑，拿到数据间接放弃，会产生客户端生产比服务端超前的状况）

看起来，流式要比游标的形式更好一些，然而事件往往不像外表上那么简略

1. 绝对于游标查问，流式对数据库的影响工夫要更长一些
2. 另外流式查问依赖网络，导致网络拥塞可能性较大

流式游标内存剖析

表数据量：500w

内存查看工具：JDK 自带 Jvisualvm

设置 JVM 参数：-Xmx512m -Xms512m

单次调用内存应用

流式查问内存性能报告如下

游标查问内存性能报告如下

依据内存占用状况来看，游标查问和流式查问都 可能很好的避免 OOM

并发调用内存应用

并发调用：Jmete 1 秒 10 个线程并发调用

流式查问内存性能报告如下

并发调用对于内存占用状况也很 OK，不存在叠加式减少

流式查问并发调用工夫均匀耗费：≈ 55s

游标查问内存性能报告如下

游标查问并发调用工夫均匀耗费：≈ 83s

因为设施限度，以及局部状况只会在极其下产生，所以没有进行生产、测试多环境验证，小伙伴感兴趣能够自行测试

MyBatis 如何应用流式查问

上文都是在形容如何应用 JDBC 原生 API 进行查问，ORM 框架 Mybatis 也针对流式查问进行了封装

ResultHandler 接口只蕴含 handleResult 办法，能够获取到已转换后的 Java 实体类

@Slf4j
@Service
public class MyBatisStreamService {
    @Resource
    private MyBatisStreamMapper myBatisStreamMapper;

    public void mybatisStreamQuery() {long start = System.currentTimeMillis();
        myBatisStreamMapper.mybatisStreamQuery(new ResultHandler<YOU_TABLE_DO>() {
            @Override
            public void handleResult(ResultContext<? extends YOU_TABLE_DO> resultContext) {}});
        log.info("???????????? MyBatis 查问耗时 :: {}", System.currentTimeMillis() - start);
    }
}

除了下述注解式的利用形式，也能够应用 .xml 文件的模式

@Mapper
public interface MyBatisStreamMapper {@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
    @ResultType(YOU_TABLE_DO.class)
    @Select("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE")
    void mybatisStreamQuery(ResultHandler<YOU_TABLE_DO> handler);
}

Mybatis 流式查问调用工夫耗费：≈ 18s

JDBC 流式与 MyBatis 封装的流式读取比照

1. MyBatis 绝对于原生的流式还是慢上了不少，然而思考到底层的封装的个性，这点性能还是能够承受的
2. 从内存占比而言，两者稳定相差无几
3. MyBatis 绝对于原生 JDBC 更为的不便，因为封装了回调函数以及序列化对象等个性

两者具体的应用，能够针对我的项目理论状况而定，没有最好的，只有最适宜的

结言

流式查问、游标查问能够防止 OOM，数据量大能够思考此计划 。然而这两种形式会占用数据库连贯，应用中不会开释，所以线上针对大数据量业务用到游标和流式操作， 肯定要进行并发管制

另外针对 JDBC 原生流式查问，Mybatis 中也进行了封装，尽管会慢一些，然而 性能以及代码的整洁水平会好上不少

作者马称，坐标帝都 Java 后端研发，CSDN 博客专家，专一高并发、分布式、框架底层源码等常识分享

长按上图微信二维码，增加作者好友，备注公众号，共同进步

举荐几篇品质还行的文章：

1. 【通俗易懂】1.5 w 字，16 张图，入门 RLock、AQS 并发编程原理
2. 【我的项目必备】1w 字，18 张图，彻底说分明什么是 springboot starter
3. 【强烈推荐】审慎应用 JDK 8 新个性并行流 ParallelStream
4. 【强烈推荐】一文疾速把握 Redisson 如何实现分布式锁原理

参考文章：

1. https://blog.csdn.net/xieyuoo…