從雲資料遷移服務看MySQL大表抽取模式

華為雲開發者社群發表於2021-02-07

MySql模式

摘要：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);
    }
}