从云数据迁移服务看 MySQL 大表抽取模式

摘要：MySQL JDBC 抽取到底应该采用什么样的方式，且听小编给你娓娓道来。

小编最近在云上的一个迁移项目中被 MySQL 抽取模式折磨的很惨。一开始爆内存被客户怼，再后来迁移效率低下再被怼。MySQL JDBC 抽取到底应该采用什么样的方式，且听小编给你娓娓道来。

1.1 Java-JDBC 通信原理

JDBC 与数据库之间的通信是通过 socket 完，大致流程如下图所示。Mysql Server ->内核 Socket Buffer -> 客户端 Socket Buffer ->JDBC 所在的 JVM

1.2 JDBC 读取数据的三种模式

1.2.1 方式 1：使用 JDBC 默认参数读取数据

主要分为以下几步：

1）Mysql Server 通过 OuputStream 向 Socket Server 本地 Kennel Buffer 写入数据，这里是一次内存拷贝。

2）当 Socket Server 本地 Kennel Buffer 有数据，就会通过 TCP 链路把数据传输到 Socket Client 所在机器的 Kennel Buffer。

3）JDBC 所在 JVM 利用 InputSream 读取本地 Kennel Buffer 数据到 JVM 内存，没有数据时，则读取被阻塞。

接下来就是不断重复 1，2，3 的过程。问题是，Socket Client 端的 JVM 在默认模式下读取 Kennel Buffer 是没有考虑本机内存大小的，有多少读多少。如果数据太大，就会造成 FULL GC，紧接着内存溢出。

参考 JDBC API docs，默认模式 Java demo 代码如下

1.2.2 方式 2：游标查询

为了解决方式 1 爆内存的问题，JDBC 提供了一个游标参数，在建立 jdbc 连接时加上 useCursorFetch=true。设置游标后，JDBC 每次会告诉 Server 端每次抽取的数据量，避免爆内存。通信过程如下图所示。

​ 方式 2 游标查询虽然解决了内存溢出的问题，方式 2 极大的依赖网络质量。当网络时延增大，假设每次通信增加 10ms，10 万次通信就会多出 1000s。这里仅仅是每次发请求的 RT，TCP 每次发送报文，都要求反馈 ACK 保证数据可靠性。client 每取 100 行（请求行数可配置），就会有多次通信，进一步放大时延增加导致的效率问题。此外，游标查询下，Mysql 无法预知查询的结束时延，为了应对自身的 DML 操作会在本地建立一个临时空间存放要抽取的数据。因此，游标查询时会有以下几个现象发生

a. IOPS 飙升，Mysql 将数据写入到临时空间，数据传输时从临时空间读取数据，这都会引发大量 IO 操作。

b. 磁盘空间飙升，临时空间生命周期存在于整个 JDBC 读取阶段，直到客户端发起 Result.close()时才会被 Mysql 回收。

c. CPU 和内存有一定比例上升。

有关游标查询的原理可参考博客 MySQL JDBC StreamResult 通信原理浅析以及 JDBC 源码，本文不在赘述。

参考 JDBC API docs，游标模式 Java demo 代码如下

1.2.3 方式 3： Stream 读取数据

方式 1 会导致 JVM 内存溢出，方式 2 虽然不会 FULL GC 但是通信效率较低，而且也会导致 Mysql 服务端 IOPS 飙升，消耗磁盘空间等问题。因此，我们介绍 Stream 读取数据 ，流式需要在读取 Result 前设置

方式 3 在通信前不会做任何 Server-Cient 的交互操作，避免通信效率低下。服务端准备好数据写入 Server 的 Kennel Buffer 中，这些数据通过 TCP 链路传输到 Client 的 Kennel Buffer 中，紧接着 client 端 inputStream.read()方法被唤醒去读取数据，与方式 1 不同，client 每次只会读取一个 package 大小的数据，如果一个 package 不满一行则会再读取一个 package。当 client 消费数据的速度不及数据传输速率时，client 端 kennel 区的数据就会被堆满，紧接着 Server 端的 kennel 数据也会堆满进而阻塞了 OuputStream。这样，JDBC 在 Stream 模式下就像一个水管连接两个蓄水池，Client 和 Server 达到一个平衡。

​对于 JDBC 客户端，由于每次都是从 kennel 读取数据，效率会比方式 2 高很多，每次读取一小部分数据也不会导致 JVM 内存溢出。对于服务端，Mysql 每次都是往 kennel 写数据，无需建立临时空间，不涉及 IO 读取，服务端压力也变小了。当然，方式 3 也有自己的问题，例如 Stream 流式时无法 cancel，cancel 不阻塞等等。

参考 JDBC API docs，网上很多教程需要设置 useCursorFetch=trueResultSet.FETCH_REVERSE 等，其实小编研究完 JDBC 驱动源码后发现，只需要设 fetchSize=Integer. MIN_VALUE，其他配置均和默认配置保持一致即可。游标模式 Java demo 代码如下

1.3 云数据迁移服务在三种模式下的调优

云数据迁移服务（Cloud Data Migration, CDM）是华为云上一个迁移工具，详见CDM官网,小编则通过 CDM 介绍如何切换三种模式抽取数据。CDM 默认使用的是方式 3，流式抽取数据，如果需要切换方式 1，方式 2 需额外配置。

1.3.1 配置方式 1：默认读取

新建 Mysql 连接器，建立方法详见官网，在高级属性中增加 useCursorFetch=false 和 adopt.stream=false

1.3.2 配置方式 2：游标查询

编辑 Mysql 连接器，在高级属性中增加 useCursorFetch=true 和 adopt.stream=false。游标查询的大小可通过界面上的 Fetch Size 调整，默认 1000。

1.3.3 配置方式 3：流式

CDM 默认走的流式，无需额外配置。注意 Stream 模式下，界面上的Fetch Size是不起作用的，原因参考上一节。

1.3.4 性能对比

新建 Mysql2Hive 的 CDM 迁移作业，源表 101 个字段，100 万行数据，配置如下

方式 1：写入 100 万行数据耗时 1m22s

方式 2：同样写入 100 万行，调整 fetchSzie 分别为 1，10，100，100，最低耗时 2m1s

方式 3：同样写入 100 万行，耗时 1m5s

小编还测试了 100 万的小表，明显方式 1 和方式 3 的速率要远远高于方式 2，另外小编还测试了 1000 万的大表，方式 1 爆内存，方式 2 正常迁移但耗时 20 分钟以上，而方式 3 仍然可以在 15 分钟内跑完。

本文分享自华为云社区《从云数据迁移服务看 MySQL 大表抽取模式》，原文作者：Leef724。

点击关注，第一时间了解华为云新鲜技术~