MySQL中的串流查詢及遊標查詢方式（總結分享）

這篇文章為大家帶來了關於mysql的相關知識，主要介紹了MySQL中的串流查詢及遊標查詢方式，具有很好的參考價值，希望對大家有所幫助。

推薦學習：mysql影片教學

#一、業務場景

現在業務系統需要從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。

4.3 cursorQuery 遊標查詢

當開啟遊標的時候，服務端回傳資料的時候，就會按照fetchSize的大小回傳資料了，而客戶端接收資料的時候每次都會把換緩衝區資料全部讀取乾淨，假如數據有1億數據，將FetchSize設定成1000的話，會進行10萬次來回通訊；

由於MySQL方不知道客戶端什麼時候將數據消費完，而自身的對應表可能會有DML寫入操作，此時MySQL需要建立一個臨時空間來存放需要拿走的資料。

因此對於當你啟用useCursorFetch讀取大表的時候會看到MySQL上的幾個現象：

• 1.IOPS飆升
• 2.磁碟空間飆升
• 3.客戶端JDBC發起SQL後，長時間等待SQL回應數據，這段時間就是服務端在準備資料
• 4.在資料準備完成後，開始傳輸數據的階段，網路回應開始飆升，IOPS由「讀寫」轉變為「讀取」。
• IOPS (Input/Output Per Second)：磁碟每秒的讀寫次數
• #5.CPU和記憶體會有一定比例的上升

五、並發場景

並發呼叫：Jmete 1 秒10 個執行緒並發呼叫

串流查詢記憶體效能報告如下

並發呼叫對於記憶體佔用情況也很OK，不存在疊加式增加

遊標查詢記憶體效能報告如下

#六、總結

1、遊標查詢和串流查詢在單執行緒下都能夠規避OOM的情況；

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

3、在並發場景下，串流查詢堆疊記憶體走勢更穩定，不存在疊加式增加。

推薦學習：mysql影片教學

#

以上是MySQL中的串流查詢及遊標查詢方式（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！