从云数据迁移服务看MySQL大表抽取模式_小言_互联网的博客

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

2021-02-17 16:35 468人阅读评论(0)

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

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

Java-JDBC通信原理

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

JDBC读取数据的三种模式

方式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 代码如下


  
   
    
     
    
    
     
      public static Connection getConnection() throws SQLException {
     
    
   
    
     
    
    
     
          Properties connectionProps = 
      new Properties();
     
    
   
    
     
    
    
     
          connectionProps.put(
      "user", 
      this.userName);
     
    
   
    
     
    
    
     
          connectionProps.put(
      "password", 
      this.password);
     
    
   
    
     
    
    
     
          Connection conn = DriverManager.getConnection(
     
    
   
    
     
    
    
                        
      "jdbc:" + 
      this.dbms + 
      "://" + 
      "127.0.0.1:3306",
     
    
   
    
     
    
    
     
                          connectionProps);
     
    
   
    
     
    
    
         
      return conn;
     
    
   
    
     
    
    
     
      }
     
    
   
    
     
    
    
      
     
    
   
    
     
    
    
     
      public static void viewTable(Connection con) throws SQLException {
     
    
   
    
     
    
    
     
          String query = 
      "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
     
    
   
    
     
    
    
     
          Connection conn = getConnection();
     
    
   
    
     
    
    
         
      try (Statement stmt = conn.createStatement()) {
     
    
   
    
     
    
    
     
            ResultSet rs = stmt.executeQuery(query);
     
    
   
    
     
    
    
           
      while (rs.next()) {
     
    
   
    
     
    
    
             
      // ...
     
    
   
    
     
    
    
     
            }
     
    
   
    
     
    
    
     
          } 
      catch (SQLException e) {
     
    
   
    
     
    
    
     
            JDBCTutorialUtilities.printSQLException(e);
     
    
   
    
     
    
    
     
          }
     
    
   
    
     
    
    
     
      }

方式2：游标查询

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

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

IOPS飙升，Mysql将数据写入到临时空间，数据传输时从临时空间读取数据，这都会引发大量IO操作。
磁盘空间飙升，临时空间生命周期存在于整个JDBC读取阶段，直到客户端发起Result.close()时才会被Mysql回收。
CPU和内存有一定比例上升。

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

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


  
   
    
     
    
    
      
      public static Connection getConnection() throws SQLException {
     
    
   
    
     
    
    
     
          Properties connectionProps = 
      new Properties();
     
    
   
    
     
    
    
     
          connectionProps.put(
      "user", 
      this.userName);
     
    
   
    
     
    
    
     
          connectionProps.put(
      "password", 
      this.password);
     
    
   
    
     
    
    
     
          Connection conn = DriverManager.getConnection(
     
    
   
    
     
    
    
                        
      "jdbc:" + 
      this.dbms + 
      "://" + 
      "127.0.0.1:3306?"
     
    
   
    
     
    
    
     
                         +
      "useCursorFetch=true",
     
    
   
    
     
    
    
     
                          connectionProps);
     
    
   
    
     
    
    
         
      return conn;
     
    
   
    
     
    
    
     
      }
     
    
   
    
     
    
    
      
     
    
   
    
     
    
    
     
      public static void viewTable(Connection con) throws SQLException {
     
    
   
    
     
    
    
     
          String query = 
      "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
     
    
   
    
     
    
    
     
          Connection conn = getConnection();
     
    
   
    
     
    
    
         
      try (PrepareStatement stmt = conn.prepareStatement(query)) {
     
    
   
    
     
    
    
     
            stmt.setFetchSize(
      100);
     
    
   
    
     
    
    
     
            ResultSet rs = stmt.executeQuery();
     
    
   
    
     
    
    
           
      while (rs.next()) {
     
    
   
    
     
    
    
             
      // ...
     
    
   
    
     
    
    
     
            }
     
    
   
    
     
    
    
     
          } 
      catch (SQLException e) {
     
    
   
    
     
    
    
     
            JDBCTutorialUtilities.printSQLException(e);
     
    
   
    
     
    
    
     
          }
     
    
   
    
     
    
    
     
      }

方式3： Stream读取数据

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

statement.setFetchSize(Integer.MIN_VALUE)

方式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=true，ResultSet.FETCH_REVERSE等，其实小编研究完JDBC驱动源码后发现，只需要设fetchSize=Integer. MIN_VALUE，其他配置均和默认配置保持一致即可。游标模式 Java demo 代码如下，


  
   
    
     
    
    
      
      public static Connection getConnection() throws SQLException {
     
    
   
    
     
    
    
     
          Properties connectionProps = 
      new Properties();
     
    
   
    
     
    
    
     
          connectionProps.put(
      "user", 
      this.userName);
     
    
   
    
     
    
    
     
          connectionProps.put(
      "password", 
      this.password);
     
    
   
    
     
    
    
     
          Connection conn = DriverManager.getConnection(
     
    
   
    
     
    
    
                        
      "jdbc:" + 
      this.dbms + 
      "://" + 
      "127.0.0.1:3306",
     
    
   
    
     
    
    
     
                          connectionProps);
     
    
   
    
     
    
    
         
      return conn;
     
    
   
    
     
    
    
     
      }
     
    
   
    
     
    
    
      
     
    
   
    
     
    
    
     
      public static void viewTable(Connection con) throws SQLException {
     
    
   
    
     
    
    
     
          String query = 
      "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
     
    
   
    
     
    
    
     
          Connection conn = getConnection();
     
    
   
    
     
    
    
         
      try (PrepareStatement stmt = conn.prepareStatement(query)) {
     
    
   
    
     
    
    
     
            statement.setFetchSize(Integer.MIN_VALUE)
     
    
   
    
     
    
    
     
            ResultSet rs = stmt.executeQuery();
     
    
   
    
     
    
    
           
      while (rs.next()) {
     
    
   
    
     
    
    
             
      // ...
     
    
   
    
     
    
    
     
            }
     
    
   
    
     
    
    
     
          } 
      catch (SQLException e) {
     
    
   
    
     
    
    
     
            JDBCTutorialUtilities.printSQLException(e);
     
    
   
    
     
    
    
     
          }
     
    
   
    
     
    
    
     
      }