MySQL中的流式查詢及遊標查詢方式

2022-08-17 18:03:02

一、業務場景

現在業務系統需要從 MySQL 資料庫裡讀取 500w 資料行進行處理

遷移資料
匯出資料
批次處理資料

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

常規查詢：一次性讀取 500w 資料到 JVM 記憶體中，或者分頁讀取
流式查詢：每次讀取一條載入到 JVM 記憶體進行業務處理
遊標查詢：和流式一樣，通過 fetchSize 引數，控制一次讀取多少條資料

2.1 常規查詢

預設情況下，完整的檢索結果集會將其儲存在記憶體中。在大多數情況下，這是最有效的操作方式，更易於實現。

假設單表 500w 資料量，沒有人會一次性載入到記憶體中，一般會採用分頁的方式。

在這裡，測試demo中只是為了監控JVM，所以沒有采用分頁，一次性將資料載入記憶體中

@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監控

我們將對記憶體調小-Xms70m -Xmx70m

整個查詢過程中，堆記憶體佔用逐步增長，並且最終導致OOM：

java.lang.OutOfMemoryError: GC overhead limit exceeded

1、頻繁觸發GC

2、存在OOM隱患

2.2 流式查詢

流式查詢有一點需要注意：必須先讀取（或關閉）結果集中的所有行，然後才能對連線發出任何其他查詢，否則將引發異常，其查詢會獨佔連線。

從測試結果來看，流式查詢並沒有提升查詢的速度

@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監控

我們將堆記憶體調小-Xms70m -Xmx70m

我們發現即使堆記憶體只有70m，卻依然沒有發生OOM

2.3 遊標查詢

注意：

1、需要在資料庫連線資訊裡拼接引數 useCursorFetch=true

2、其次設定 Statement 每次讀取資料數量，比如一次讀取 1000

從測試結果來看，遊標查詢在一定程度縮短了查詢速度

@Test
public void cursorQuery() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    // 注意這裡需要拼接引數，否則就是普通查詢
    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監控

我們將堆記憶體調小-Xms70m -Xmx70m

我們發現在單執行緒情況下，遊標查詢和流式查詢一樣，都能很好的規避OOM，並且遊標查詢能夠優化查詢速度。

三、RowData

ResultSet.next() 的邏輯是實現類 ResultSetImpl 每次都從 RowData 獲取下一行的資料。RowData 是一個介面，實現關係圖如下

3.1 RowDataStatic

預設情況下 ResultSet 會使用 RowDataStatic 範例，在生成 RowDataStatic 物件時就會把 ResultSet 中所有記錄讀到記憶體裡，之後通過 next() 再一條條從記憶體中讀

3.2 RowDataDynamic

當採用流式處理時，ResultSet 使用的是 RowDataDynamic 物件，而這個物件 next() 每次呼叫都會發起 IO 讀取單行資料

3.3 RowDataCursor

RowDataCursor 的呼叫為批次處理，然後進行內部快取，流程如下：

首先會檢視自己內部緩衝區是否有資料沒有返回，如果有則返回下一行
如果都讀取完畢，向 MySQL Server 觸發一個新的請求讀取 fetchSize 數量結果
並將返回結果緩衝到內部緩衝區，然後返回第一行資料

總結來說就是：

預設的 RowDataStatic 讀取全部資料到使用者端記憶體中，也就是我們的 JVM；

RowDataDynamic 每次 IO 呼叫讀取一條資料；

RowDataCursor 一次讀取 fetchSize 行，消費完成再發起請求呼叫。

四、JDBC 通訊原理

在 JDBC 與 MySQL 伺服器端的互動是通過 Socket 完成的，對應到網路程式設計，可以把 MySQL 當作一個 SocketServer，因此一個完整的請求鏈路應該是：

JDBC 使用者端 -> 使用者端 Socket -> MySQL -> 檢索資料返回 -> MySQL 核心 Socket Buffer -> 網路 -> 使用者端 Socket Buffer -> JDBC 使用者端

4.1 generalQuery 普通查詢

普通查詢會將當次查詢到的所有資料載入到JVM，然後再進行處理。

如果查詢資料量過大，會不斷經歷 GC，然後就是記憶體溢位

4.2 streamQuery 流式查詢

伺服器端準備好從第一條資料開始返回時，向緩衝區懟入資料，這些資料通過TCP鏈路，懟入使用者端機器的核心緩衝區，JDBC會的inputStream.read()方法會被喚醒去讀取資料，唯一的區別是開啟了stream讀取的時候，每次只是從核心中讀取一個package大小的資料，只是返回一行資料，如果1個package無法組裝1行資料，會再讀1個package。