腳本之家服務器常用軟件

快捷導航

使用MyBatis查詢千萬級數(shù)據(jù)量操作實現(xiàn)

更新時間：2023年05月16日 09:42:18 作者：馬丁玩編程

這篇文章主要為大家介紹了如何使用MyBatis?查詢千萬數(shù)據(jù)量的操作過程詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

引言

由于現(xiàn)在 ORM 框架的成熟運用，很多小伙伴對于 JDBC 的概念有些薄弱，ORM 框架底層其實是通過 JDBC 操作的 DB

JDBC（JavaDataBase Connectivity）是 Java 數(shù)據(jù)庫連接, 說的直白點就是使用 Java 語言操作數(shù)據(jù)庫

由 SUN 公司提供出一套訪問數(shù)據(jù)庫的規(guī)范 API，并提供相對應的連接數(shù)據(jù)庫協(xié)議標準，然后 各廠商根據(jù)規(guī)范提供一套訪問自家數(shù)據(jù)庫的 API 接口

文章大數(shù)據(jù)量操作核心圍繞 JDBC 展開，目錄結構如下：

MySql JDBC 大數(shù)據(jù)量操作

整篇文章以大數(shù)據(jù)量操作為議題，通過開發(fā)過程中的需求引出相關知識點

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

一般而言筆者認為在 Java Web 程序里，能夠被稱為大數(shù)據(jù)量的，幾十萬到千萬不等，再高的話 Java（WEB 應用）處理就不怎么合適了

舉個例子，現(xiàn)在業(yè)務系統(tǒng)需要從 MySQL 數(shù)據(jù)庫里讀取 500w 數(shù)據(jù)行進行處理，應該怎么做

常規(guī)查詢，一次性讀取 500w 數(shù)據(jù)到 JVM 內存中，或者分頁讀取
流式查詢，建立長連接，利用服務端游標，每次讀取一條加載到 JVM 內存
游標查詢，和流式一樣，通過 fetchSize 參數(shù)，控制一次讀取多少條數(shù)據(jù)

常規(guī)查詢

默認情況下，完整的檢索結果集會將其存儲在內存中。在大多數(shù)情況下，這是最有效的操作方式，并且由于 MySQL 網(wǎng)絡協(xié)議的設計，因此更易于實現(xiàn)

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

@SneakyThrows
@Override
public void pageQuery() {
    @Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement();
    long start = System.currentTimeMillis();
    long offset = 0;
    int size = 100;
    while (true) {
        String sql = String.format("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE LIMIT %s, %s", offset, size);
        @Cleanup ResultSet rs = stmt.executeQuery(sql);
        long count = loopResultSet(rs);
        if (count == 0) break;
        offset += size;
    }
    log.info("  ?????? 分頁查詢耗時 :: {} ", System.currentTimeMillis() - start);
}

上述方式比較簡單，但是在不考慮 LIMIT 深分頁優(yōu)化情況下，線上數(shù)據(jù)庫服務器就涼了，亦或者你能等個幾天時間檢索數(shù)據(jù)

MySQL 千萬數(shù)據(jù)量深分頁優(yōu)化, 拒絕線上故障！

流式查詢

如果你正在使用具有大量數(shù)據(jù)行的 ResultSet，并且無法在 JVM 中為其分配所需的內存堆空間，則可以告訴驅動程序從結果流中返回一行

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

使用流式查詢，則要保持對產生結果集的語句所引用的表的并發(fā)訪問，因為其 查詢會獨占連接，所以必須盡快處理

@SneakyThrows
public void streamQuery() {
    @Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);

      long start = System.currentTimeMillis();
    @Cleanup ResultSet rs = stmt.executeQuery("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE");
    loopResultSet(rs);
    log.info("  ?????? 流式查詢耗時 :: {} ", (System.currentTimeMillis() - start) / 1000);
}

流式查詢庫表數(shù)據(jù)量 500w 單次調用時間消耗：≈ 6s

游標查詢

SpringBoot 2.x 版本默認連接池為 HikariPool，連接對象是 HikariProxyConnection，所以下述設置游標方式就不可行了

((JDBC4Connection) conn).setUseCursorFetch(true);

需要在數(shù)據(jù)庫連接信息里拼接 &useCursorFetch=true。其次設置 Statement 每次讀取數(shù)據(jù)數(shù)量，比如一次讀取 1000

@SneakyThrows
public void cursorQuery() {
    @Cleanup Connection conn = dataSource.getConnection();
    @Cleanup Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(1000);

    long start = System.currentTimeMillis();
    @Cleanup ResultSet rs = stmt.executeQuery("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE");
    loopResultSet(rs);
    log.info("  ?????? 游標查詢耗時 :: {} ", (System.currentTimeMillis() - start) / 1000);
}

游標查詢庫表數(shù)據(jù)量 500w 單次調用時間消耗：≈ 18s

JDBC RowData

上面都使用到了方法 loopResultSet，方法內部只是進行了 while 循環(huán)，常規(guī)、流式、游標查詢的核心點在于 next 方法

@SneakyThrows
private Long loopResultSet(ResultSet rs) {
    while (rs.next()) {
                // 業(yè)務操作
    }
    return xx;
}

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

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

RowDataCursor 的調用為批處理，然后進行內部緩存，流程如下：

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

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

總結來說就是，默認的 RowDataStatic 讀取全部數(shù)據(jù)到客戶端內存中，也就是我們的 JVM；RowDataCursor 一次讀取 fetchSize 行，消費完成再發(fā)起請求調用；RowDataDynamic 每次 IO 調用讀取一條數(shù)據(jù)

JDBC 通信原理

普通查詢

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

JDBC 客戶端 -> 客戶端 Socket -> MySQL -> 檢索數(shù)據(jù)返回 -> MySQL 內核 Socket 緩沖區(qū) -> 網(wǎng)絡 -> 客戶端 Socket Buffer -> JDBC 客戶端

普通查詢的方式在查詢大數(shù)據(jù)量時，所在 JVM 可能會涼涼，原因如下：

MySQL Server 會將檢索出的 SQL 結果集通過輸出流寫入到內核對應的 Socket Buffer
內核緩沖區(qū)通過 JDBC 發(fā)起的 TCP 鏈路進行回傳數(shù)據(jù)，此時數(shù)據(jù)會先進入 JDBC 客戶端所在內核緩沖區(qū)
JDBC 發(fā)起 SQL 操作后，程序會被阻塞在輸入流的 read 操作上，當緩沖區(qū)有數(shù)據(jù)時，程序會被喚醒進而將緩沖區(qū)數(shù)據(jù)讀取到 JVM 內存中
MySQL Server 會不斷發(fā)送數(shù)據(jù)，JDBC 不斷讀取緩沖區(qū)數(shù)據(jù)到 Java 內存中，雖然此時數(shù)據(jù)已到 JDBC 所在程序本地，但是 JDBC 還沒有對 execute 方法調用處進行響應，因為需要等到對應數(shù)據(jù)讀取完畢才會返回
弊端就顯而易見了，如果查詢數(shù)據(jù)量過大，會不斷經歷 GC，然后就是內存溢出

游標查詢

通過上文得知，游標可以解決普通查詢大數(shù)據(jù)量的內存溢出問題，但是

小伙伴有沒有思考過這么一個問題，MySQL 不知道客戶端程序何時消費完成，此時另一連接對該表造成 DML 寫入操作應該如何處理？

其實，在我們使用游標查詢時，MySQL 需要建立一個臨時空間來存放需要被讀取的數(shù)據(jù)，所以不會和 DML 寫入操作產生沖突

但是游標查詢會引發(fā)以下現(xiàn)象：

IOPS 飆升，因為需要返回的數(shù)據(jù)需要寫入到臨時空間中，存在大量的 IO 讀取和寫入，此流程可能會引起其它業(yè)務的寫入抖動
磁盤空間飆升，因為寫入臨時空間的數(shù)據(jù)是在原表之外的，如果表數(shù)據(jù)過大，極端情況下可能會導致數(shù)據(jù)庫磁盤寫滿，這時網(wǎng)絡輸出時沒有變化的。而寫入臨時空間的數(shù)據(jù)會在 讀取完成或客戶端發(fā)起 ResultSet#close 操作時由 MySQL 回收
客戶端 JDBC 發(fā)起 SQL 查詢，可能會有長時間等待 SQL 響應，這段時間為服務端準備數(shù)據(jù)階段。但是 普通查詢等待時間與游標查詢等待時間原理上是不一致的，前者是一致在讀取網(wǎng)絡緩沖區(qū)的數(shù)據(jù)，沒有響應到業(yè)務層面；后者是 MySQL 在準備臨時數(shù)據(jù)空間，沒有響應到 JDBC
數(shù)據(jù)準備完成后，進行到傳輸數(shù)據(jù)階段，網(wǎng)絡響應開始飆升，IOPS 由"讀寫"轉變?yōu)?quot;讀取"

采用游標查詢的方式 通信效率比較低，因為客戶端消費完 fetchSize 行數(shù)據(jù)，就需要發(fā)起請求到服務端請求，在數(shù)據(jù)庫前期準備階段 IOPS 會非常高，占用大量的磁盤空間以及性能

流式查詢

當客戶端與 MySQL Server 端建立起連接并且交互查詢時，MySQL Server 會通過輸出流將 SQL 結果集返回輸出，也就是 向本地的內核對應的 Socket Buffer 中寫入數(shù)據(jù)，然后將內核中的數(shù)據(jù)通過 TCP 鏈路回傳數(shù)據(jù)到 JDBC 對應的服務器內核緩沖區(qū)

JDBC 通過輸入流 read 方法去讀取內核緩沖區(qū)數(shù)據(jù)，因為開啟了流式讀取，每次業(yè)務程序接收到的數(shù)據(jù)只有一條
MySQL 服務端會向 JDBC 代表的客戶端內核源源不斷的輸送數(shù)據(jù)，直到客戶端請求 Socket 緩沖區(qū)滿，這時的 MySQL 服務端會阻塞
對于 JDBC 客戶端而言，數(shù)據(jù)每次讀取都是從本機器的內核緩沖區(qū)，所以性能會更快一些，一般情況不必擔心本機內核無數(shù)據(jù)消費（除非 MySQL 服務端傳遞來的數(shù)據(jù)，在客戶端不做任何業(yè)務邏輯，拿到數(shù)據(jù)直接放棄，會發(fā)生客戶端消費比服務端超前的情況）

看起來，流式要比游標的方式更好一些，但是事情往往不像表面上那么簡單

相對于游標查詢，流式對數(shù)據(jù)庫的影響時間要更長一些
另外流式查詢依賴網(wǎng)絡，導致網(wǎng)絡擁塞可能性較大

流式游標內存分析

表數(shù)據(jù)量：500w

內存查看工具：JDK 自帶 Jvisualvm

設置 JVM 參數(shù)： -Xmx512m -Xms512m

單次調用內存使用

流式查詢內存性能報告如下

游標查詢內存性能報告如下

根據(jù)內存占用情況來看，游標查詢和流式查詢都 能夠很好的防止 OOM

并發(fā)調用內存使用

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

流式查詢內存性能報告如下

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

流式查詢并發(fā)調用時間平均消耗：≈ 55s

游標查詢內存性能報告如下

游標查詢并發(fā)調用時間平均消耗：≈ 83s

因為設備限制，以及部分情況只會在極端下產生，所以沒有進行生產、測試多環(huán)境驗證，小伙伴感興趣可以自行測試

MyBatis 如何使用流式查詢

上文都是在描述如何使用 JDBC 原生 API 進行查詢，ORM 框架 Mybatis 也針對流式查詢進行了封裝

ResultHandler 接口只包含 handleResult 方法，可以獲取到已轉換后的 Java 實體類

@Slf4j
@Service
public class MyBatisStreamService {
    @Resource
    private MyBatisStreamMapper myBatisStreamMapper;
    public void mybatisStreamQuery() {
        long start = System.currentTimeMillis();
        myBatisStreamMapper.mybatisStreamQuery(new ResultHandler<YOU_TABLE_DO>() {
            @Override
            public void handleResult(ResultContext<? extends YOU_TABLE_DO> resultContext) { }
        });
        log.info("  ?????? MyBatis查詢耗時 :: {} ", System.currentTimeMillis() - start);
    }
}

除了下述注解式的應用方式，也可以使用 .xml 文件的形式

@Mapper
public interface MyBatisStreamMapper {
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
    @ResultType(YOU_TABLE_DO.class)
    @Select("SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM YOU_TABLE")
    void mybatisStreamQuery(ResultHandler&lt;YOU_TABLE_DO&gt; handler);
}

Mybatis 流式查詢調用時間消耗：≈ 18s

JDBC 流式與 MyBatis 封裝的流式讀取對比