一、業(yè)務場景

現(xiàn)在業(yè)務系統(tǒng)需要從 MySQL 數(shù)據(jù)庫里讀取 500w 數(shù)據(jù)行進行處理

• 遷移數(shù)據(jù)
• 導出數(shù)據(jù)
• 批量處理數(shù)據(jù)

二、羅列一下三種處理方式

• 常規(guī)查詢：一次性讀取 500w 數(shù)據(jù)到 JVM 內(nèi)存中，或者分頁讀取
• 流式查詢：每次讀取一條加載到 JVM 內(nèi)存進行業(yè)務處理
• 游標查詢：和流式一樣，通過 fetchSize 參數(shù)，控制一次讀取多少條數(shù)據(jù)

2.1 常規(guī)查詢

默認情況下，完整的檢索結(jié)果集會將其存儲在內(nèi)存中。在大多數(shù)情況下，這是最有效的操作方式，更易于實現(xiàn)。

假設單表 500w 數(shù)據(jù)量，沒有人會一次性加載到內(nèi)存中，一般會采用分頁的方式。

在這里，測試demo中只是為了監(jiān)控JVM，所以沒有采用分頁，一次性將數(shù)據(jù)載入內(nèi)存中

@Test
public void generalQuery() throws Exception {
    // 1核2G：查詢一百條記錄：47ms
    // 1核2G：查詢一千條記錄：2050 ms
    // 1核2G：查詢一萬條記錄：26589 ms
    // 1核2G：查詢五萬條記錄：135966 ms
    String sql = "select * from wh_b_inventory limit 10000";
    ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

JVM監(jiān)控

我們將對內(nèi)存調(diào)小-Xms70m -Xmx70m

整個查詢過程中，堆內(nèi)存占用逐步增長，并且最終導致OOM：

java.lang.OutOfMemoryError: GC overhead limit exceeded

1、頻繁觸發(fā)GC

2、存在OOM隱患

2.2 流式查詢

流式查詢有一點需要注意：必須先讀取（或關(guān)閉）結(jié)果集中的所有行，然后才能對連接發(fā)出任何其他查詢，否則將引發(fā)異常，其 查詢會獨占連接。

從測試結(jié)果來看，流式查詢并沒有提升查詢的速度

@Test
public void streamQuery() throws Exception {
    // 1核2G：查詢一百條記錄：138ms
    // 1核2G：查詢一千條記錄：2304 ms
    // 1核2G：查詢一萬條記錄：26536 ms
    // 1核2G：查詢五萬條記錄：135931 ms
    String sql = "select * from wh_b_inventory limit 50000";
    statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);
    ResultSet rs = statement.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

JVM監(jiān)控

我們將堆內(nèi)存調(diào)小-Xms70m -Xmx70m

我們發(fā)現(xiàn)即使堆內(nèi)存只有70m，卻依然沒有發(fā)生OOM

2.3 游標查詢

注意：

1、需要在數(shù)據(jù)庫連接信息里拼接參數(shù) useCursorFetch=true

2、其次設置 Statement 每次讀取數(shù)據(jù)數(shù)量，比如一次讀取 1000

從測試結(jié)果來看，游標查詢在一定程度縮短了查詢速度

@Test
public void cursorQuery() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    // 注意這里需要拼接參數(shù)，否則就是普通查詢
    conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
    start = System.currentTimeMillis();
 
     // 1核2G：查詢一百條記錄：52 ms
     // 1核2G：查詢一千條記錄：1095 ms
    // 1核2G：查詢一萬條記錄：17432 ms
    // 1核2G：查詢五萬條記錄：90244 ms
    String sql = "select * from wh_b_inventory limit 50000";
    ((JDBC4Connection) conn).setUseCursorFetch(true);
    statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(1000);
    ResultSet rs = statement.executeQuery(sql);
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println(count);
}

JVM監(jiān)控

我們將堆內(nèi)存調(diào)小-Xms70m -Xmx70m

我們發(fā)現(xiàn)在單線程情況下，游標查詢和流式查詢一樣，都能很好的規(guī)避OOM，并且游標查詢能夠優(yōu)化查詢速度。

三、RowData

ResultSet.next() 的邏輯是實現(xiàn)類 ResultSetImpl 每次都從 RowData 獲取下一行的數(shù)據(jù)。RowData 是一個接口，實現(xiàn)關(guān)系圖如下

3.1 RowDataStatic

默認情況下 ResultSet 會使用 RowDataStatic 實例，在生成 RowDataStatic 對象時就會把 ResultSet 中所有記錄讀到內(nèi)存里，之后通過 next() 再一條條從內(nèi)存中讀

3.2 RowDataDynamic

當采用流式處理時，ResultSet 使用的是 RowDataDynamic 對象，而這個對象 next() 每次調(diào)用都會發(fā)起 IO 讀取單行數(shù)據(jù)

3.3 RowDataCursor

RowDataCursor 的調(diào)用為批處理，然后進行內(nèi)部緩存，流程如下：

• 首先會查看自己內(nèi)部緩沖區(qū)是否有數(shù)據(jù)沒有返回，如果有則返回下一行
• 如果都讀取完畢，向 MySQL Server 觸發(fā)一個新的請求讀取 fetchSize 數(shù)量結(jié)果
• 并將返回結(jié)果緩沖到內(nèi)部緩沖區(qū)，然后返回第一行數(shù)據(jù)

總結(jié)來說就是：

默認的 RowDataStatic 讀取全部數(shù)據(jù)到客戶端內(nèi)存中，也就是我們的 JVM；

RowDataDynamic 每次 IO 調(diào)用讀取一條數(shù)據(jù)；

RowDataCursor 一次讀取 fetchSize 行，消費完成再發(fā)起請求調(diào)用。

四、JDBC 通信原理

在 JDBC 與 MySQL 服務端的交互是通過 Socket 完成的，對應到網(wǎng)絡編程，可以把 MySQL 當作一個 SocketServer，因此一個完整的請求鏈路應該是：

JDBC 客戶端 -> 客戶端 Socket -> MySQL -> 檢索數(shù)據(jù)返回 -> MySQL 內(nèi)核 Socket Buffer -> 網(wǎng)絡 -> 客戶端 Socket Buffer -> JDBC 客戶端

4.1 generalQuery 普通查詢

普通查詢會將當次查詢到的所有數(shù)據(jù)加載到JVM，然后再進行處理。

如果查詢數(shù)據(jù)量過大，會不斷經(jīng)歷 GC，然后就是內(nèi)存溢出

4.2 streamQuery 流式查詢

服務端準備好從第一條數(shù)據(jù)開始返回時，向緩沖區(qū)懟入數(shù)據(jù)，這些數(shù)據(jù)通過TCP鏈路，懟入客戶端機器的內(nèi)核緩沖區(qū)，JDBC會的inputStream.read()方法會被喚醒去讀取數(shù)據(jù)，唯一的區(qū)別是開啟了stream讀取的時候，每次只是從內(nèi)核中讀取一個package大小的數(shù)據(jù)，只是返回一行數(shù)據(jù)，如果1個package無法組裝1行數(shù)據(jù)，會再讀1個package。

4.3 cursorQuery 游標查詢

當開啟游標的時候，服務端返回數(shù)據(jù)的時候，就會按照fetchSize的大小返回數(shù)據(jù)了，而客戶端接收數(shù)據(jù)的時候每次都會把換緩沖區(qū)數(shù)據(jù)全部讀取干凈，假如數(shù)據(jù)有1億數(shù)據(jù)，將FetchSize設置成1000的話，會進行10萬次來回通信；

由于MySQL方不知道客戶端什么時候?qū)?shù)據(jù)消費完，而自身的對應表可能會有DML寫入操作，此時MySQL需要建立一個臨時空間來存放需要拿走的數(shù)據(jù)。

因此對于當你啟用useCursorFetch讀取大表的時候會看到MySQL上的幾個現(xiàn)象：

• 1.IOPS飆升
• 2.磁盤空間飆升
• 3.客戶端JDBC發(fā)起SQL后，長時間等待SQL響應數(shù)據(jù)，這段時間就是服務端在準備數(shù)據(jù)
• 4.在數(shù)據(jù)準備完成后，開始傳輸數(shù)據(jù)的階段，網(wǎng)絡響應開始飆升，IOPS由“讀寫”轉(zhuǎn)變?yōu)?ldquo;讀取”。
• IOPS (Input/Output Per Second)：磁盤每秒的讀寫次數(shù)
• 5.CPU和內(nèi)存會有一定比例的上升

五、并發(fā)場景

并發(fā)調(diào)用：Jmete 1 秒 10 個線程并發(fā)調(diào)用

流式查詢內(nèi)存性能報告如下

并發(fā)調(diào)用對于內(nèi)存占用情況也很 OK，不存在疊加式增加

游標查詢內(nèi)存性能報告如下

六、總結(jié)

1、游標查詢和流式查詢在單線程下都能夠規(guī)避OOM的情況；

2、在查詢速度上游標查詢比流式查詢更快，流式查詢和普通查詢相比并不能縮短查詢時間；

3、在并發(fā)場景下，流式查詢堆內(nèi)存走勢更加穩(wěn)定，不存在疊加式增加。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。