MySQL中流式查詢及游標(biāo)查詢的方式是什么

這篇文章主要講解了“MySQL中流式查詢及游標(biāo)查詢的方式是什么”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL中流式查詢及游標(biāo)查詢的方式是什么”吧！

一、業(yè)務(wù)場景

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

• 遷移數(shù)據(jù)

• 導(dǎo)出數(shù)據(jù)

• 批量處理數(shù)據(jù)

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

• 常規(guī)查詢：一次性讀取 500w 數(shù)據(jù)到 JVM 內(nèi)存中，或者分頁讀取

• 流式查詢：每次讀取一條加載到 JVM 內(nèi)存進(jìn)行業(yè)務(wù)處理

• 游標(biāo)查詢：和流式一樣，通過 fetchSize 參數(shù)，控制一次讀取多少條數(shù)據(jù)

2.1 常規(guī)查詢

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

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

在這里，測試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

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

java.lang.OutOfMemoryError: GC overhead limit exceeded

1、頻繁觸發(fā)GC

2、存在OOM隱患

2.2 流式查詢

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

從測試結(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 游標(biāo)查詢

注意：

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

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

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

@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)在單線程情況下，游標(biāo)查詢和流式查詢一樣，都能很好的規(guī)避OOM，并且游標(biāo)查詢能夠優(yōu)化查詢速度。

三、RowData

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

3.1 RowDataStatic

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

3.2 RowDataDynamic

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

3.3 RowDataCursor

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

• 首先會(huì)查看自己內(nèi)部緩沖區(qū)是否有數(shù)據(jù)沒有返回，如果有則返回下一行

• 如果都讀取完畢，向 MySQL Server 觸發(fā)一個(gè)新的請求讀取 fetchSize 數(shù)量結(jié)果

• 并將返回結(jié)果緩沖到內(nèi)部緩沖區(qū)，然后返回第一行數(shù)據(jù)

總結(jié)來說就是：

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

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

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

四、JDBC 通信原理

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

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

4.1 generalQuery 普通查詢

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

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

4.2 streamQuery 流式查詢

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

4.3 cursorQuery 游標(biāo)查詢

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

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

因此對于當(dāng)你啟用useCursorFetch讀取大表的時(shí)候會(huì)看到MySQL上的幾個(gè)現(xiàn)象：

• 1.IOPS飆升

• 2.磁盤空間飆升

• 3.客戶端JDBC發(fā)起SQL后，長時(shí)間等待SQL響應(yīng)數(shù)據(jù)，這段時(shí)間就是服務(wù)端在準(zhǔn)備數(shù)據(jù)

• 4.在數(shù)據(jù)準(zhǔn)備完成后，開始傳輸數(shù)據(jù)的階段，網(wǎng)絡(luò)響應(yīng)開始飆升，IOPS由“讀寫”轉(zhuǎn)變?yōu)椤白x取”。

• IOPS (Input/Output Per Second)：磁盤每秒的讀寫次數(shù)

• 5.CPU和內(nèi)存會(huì)有一定比例的上升

五、并發(fā)場景

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

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

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

游標(biāo)查詢內(nèi)存性能報(bào)告如下

感謝各位的閱讀，以上就是“MySQL中流式查詢及游標(biāo)查詢的方式是什么”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL中流式查詢及游標(biāo)查詢的方式是什么這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！