從 MySQL 數(shù)據(jù)庫(kù)里讀取大量數(shù)據(jù)行進(jìn)行處理，怎么做更效益化

這篇文章主要講解了“從 MySQL 數(shù)據(jù)庫(kù)里讀取大量數(shù)據(jù)行進(jìn)行處理，怎么做更效益化”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“從 MySQL 數(shù)據(jù)庫(kù)里讀取大量數(shù)據(jù)行進(jìn)行處理，怎么做更效益化”吧！

前言

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

JDBC（JavaDataBase Connectivity）是 Java 數(shù)據(jù)庫(kù)連接, 說(shuō)得直白點(diǎn)就是使用 Java 語(yǔ)言操作數(shù)據(jù)庫(kù)

由 SUN 公司提供出一套訪問(wèn)數(shù)據(jù)庫(kù)的規(guī)范 API， 并提供相對(duì)應(yīng)的連接數(shù)據(jù)庫(kù)協(xié)議標(biāo)準(zhǔn)， 然后 各廠商根據(jù)規(guī)范提供一套訪問(wèn)自家數(shù)據(jù)庫(kù)的 API 接口

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

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

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

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

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

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

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

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

• 流式查詢，建立長(zhǎng)連接，利用服務(wù)端游標(biāo)，每次讀取一條加載到 JVM 內(nèi)存

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

1.1 常規(guī)查詢

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

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

@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("   分頁(yè)查詢耗時(shí) :: {} ", System.currentTimeMillis() - start);
}

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

1.2 流式查詢

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

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

使用流式查詢，則要保持對(duì)產(chǎn)生結(jié)果集的語(yǔ)句所引用的表的并發(fā)訪問(wèn)，因?yàn)槠?查詢會(huì)獨(dú)占連接，所以必須盡快處理

@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("   流式查詢耗時(shí) :: {} ", (System.currentTimeMillis() - start) / 1000);
}

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

1.3 游標(biāo)查詢

SpringBoot 2.x 版本默認(rèn)連接池為 HikariPool，連接對(duì)象是 HikariProxyConnection，所以下述設(shè)置游標(biāo)方式就不可行了

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

需要在數(shù)據(jù)庫(kù)連接信息里拼接 &useCursorFetch=true。其次設(shè)置 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("   游標(biāo)查詢耗時(shí) :: {} ", (System.currentTimeMillis() - start) / 1000);
}

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

1.4 JDBC RowData

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

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

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

切換到 Markdown 編輯器下 ResultSet 會(huì)使用 RowDataStatic 實(shí)例，在生成 RowDataStatic 對(duì)象時(shí)就會(huì)把 ResultSet 中所有記錄讀到內(nèi)存里，之后通過(guò) next() 再一條條從內(nèi)存中讀

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

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

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

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

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

總結(jié)來(lái)說(shuō)就是，默認(rèn)的 RowDataStatic 讀取全部數(shù)據(jù)到客戶端內(nèi)存中，也就是我們的 JVM；RowDataCursor 一次讀取 fetchSize 行，消費(fèi)完成再發(fā)起請(qǐng)求調(diào)用；RowDataDynamic 每次 IO 調(diào)用讀取一條數(shù)據(jù)

1.5 JDBC 通信原理

（1）普通查詢

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

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

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

• MySQL Server 會(huì)將檢索出的 SQL 結(jié)果集通過(guò)輸出流寫入到內(nèi)核對(duì)應(yīng)的 Socket Buffer

• 內(nèi)核緩沖區(qū)通過(guò) JDBC 發(fā)起的 TCP 鏈路進(jìn)行回傳數(shù)據(jù)，此時(shí)數(shù)據(jù)會(huì)先進(jìn)入 JDBC 客戶端所在內(nèi)核緩沖區(qū)

• JDBC 發(fā)起 SQL 操作后，程序會(huì)被阻塞在輸入流的 read 操作上，當(dāng)緩沖區(qū)有數(shù)據(jù)時(shí)，程序會(huì)被喚醒進(jìn)而將緩沖區(qū)數(shù)據(jù)讀取到 JVM 內(nèi)存中

• MySQL Server 會(huì)不斷發(fā)送數(shù)據(jù)，JDBC 不斷讀取緩沖區(qū)數(shù)據(jù)到 Java 內(nèi)存中，雖然此時(shí)數(shù)據(jù)已到 JDBC 所在程序本地，但是 JDBC 還沒(méi)有對(duì) execute 方法調(diào)用處進(jìn)行響應(yīng)，因?yàn)樾枰鹊綄?duì)應(yīng)數(shù)據(jù)讀取完畢才會(huì)返回

• 弊端就顯而易見(jiàn)了，如果查詢數(shù)據(jù)量過(guò)大，會(huì)不斷經(jīng)歷 GC，然后就是內(nèi)存溢出

（2）游標(biāo)查詢

通過(guò)上文得知，游標(biāo)可以解決普通查詢大數(shù)據(jù)量的內(nèi)存溢出問(wèn)題，但是

小伙伴有沒(méi)有思考過(guò)這么一個(gè)問(wèn)題，MySQL 不知道客戶端程序何時(shí)消費(fèi)完成，此時(shí)另一連接對(duì)該表造成 DML 寫入操作應(yīng)該如何處理？

其實(shí)，在我們使用游標(biāo)查詢時(shí)，MySQL 需要建立一個(gè)臨時(shí)空間來(lái)存放需要被讀取的數(shù)據(jù)，所以不會(huì)和 DML 寫入操作產(chǎn)生沖突

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

• IOPS 飆升，因?yàn)樾枰祷氐臄?shù)據(jù)需要寫入到臨時(shí)空間中，存在大量的 IO 讀取和寫入，此流程可能會(huì)引起其它業(yè)務(wù)的寫入抖動(dòng)

• 磁盤空間飆升，因?yàn)閷懭肱R時(shí)空間的數(shù)據(jù)是在原表之外的，如果表數(shù)據(jù)過(guò)大，極端情況下可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)磁盤寫滿，這時(shí)網(wǎng)絡(luò)輸出時(shí)沒(méi)有變化的。而寫入臨時(shí)空間的數(shù)據(jù)會(huì)在 讀取完成或客戶端發(fā)起 ResultSet#close 操作時(shí)由 MySQL 回收

• 客戶端 JDBC 發(fā)起 SQL 查詢，可能會(huì)有長(zhǎng)時(shí)間等待 SQL 響應(yīng)，這段時(shí)間為服務(wù)端準(zhǔn)備數(shù)據(jù)階段。但是 普通查詢等待時(shí)間與游標(biāo)查詢等待時(shí)間原理上是不一致的，前者是一致在讀取網(wǎng)絡(luò)緩沖區(qū)的數(shù)據(jù)，沒(méi)有響應(yīng)到業(yè)務(wù)層面；后者是 MySQL 在準(zhǔn)備臨時(shí)數(shù)據(jù)空間，沒(méi)有響應(yīng)到 JDBC

• 數(shù)據(jù)準(zhǔn)備完成后，進(jìn)行到傳輸數(shù)據(jù)階段，網(wǎng)絡(luò)響應(yīng)開(kāi)始飆升，IOPS 由"讀寫"轉(zhuǎn)變?yōu)?quot;讀取"

采用游標(biāo)查詢的方式 通信效率比較低，因?yàn)榭蛻舳讼M(fèi)完 fetchSize 行數(shù)據(jù)，就需要發(fā)起請(qǐng)求到服務(wù)端請(qǐng)求，在數(shù)據(jù)庫(kù)前期準(zhǔn)備階段 IOPS 會(huì)非常高，占用大量的磁盤空間以及性能

（3）流式查詢

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

• JDBC 通過(guò)輸入流 read 方法去讀取內(nèi)核緩沖區(qū)數(shù)據(jù)，因?yàn)殚_(kāi)啟了流式讀取，每次業(yè)務(wù)程序接收到的數(shù)據(jù)只有一條

• MySQL 服務(wù)端會(huì)向 JDBC 代表的客戶端內(nèi)核源源不斷地輸送數(shù)據(jù)，直到客戶端請(qǐng)求 Socket 緩沖區(qū)滿，這時(shí)的 MySQL 服務(wù)端會(huì)阻塞

• 對(duì)于 JDBC 客戶端而言，數(shù)據(jù)每次讀取都是從本機(jī)器的內(nèi)核緩沖區(qū)，所以性能會(huì)更快一些，一般情況不必?fù)?dān)心本機(jī)內(nèi)核無(wú)數(shù)據(jù)消費(fèi)（除非 MySQL 服務(wù)端傳遞來(lái)的數(shù)據(jù)，在客戶端不做任何業(yè)務(wù)邏輯，拿到數(shù)據(jù)直接放棄，會(huì)發(fā)生客戶端消費(fèi)比服務(wù)端超前的情況）

看起來(lái)，流式要比游標(biāo)的方式更好一些，但是事情往往不像表面上那么簡(jiǎn)單

• 相對(duì)于游標(biāo)查詢，流式對(duì)數(shù)據(jù)庫(kù)的影響時(shí)間要更長(zhǎng)一些

• 另外流式查詢依賴網(wǎng)絡(luò)，導(dǎo)致網(wǎng)絡(luò)擁塞可能性較大

2 流式游標(biāo)內(nèi)存分析

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

內(nèi)存查看工具：JDK 自帶 Jvisualvm

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

2.1 單次調(diào)用內(nèi)存使用

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

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

根據(jù)內(nèi)存占用情況來(lái)看，游標(biāo)查詢和流式查詢都 能夠很好地防止 OOM

2.2 并發(fā)調(diào)用內(nèi)存使用

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

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

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

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

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

游標(biāo)查詢并發(fā)調(diào)用時(shí)間平均消耗：≈ 83s

因?yàn)樵O(shè)備限制，以及部分情況只會(huì)在極端下產(chǎn)生，所以沒(méi)有進(jìn)行生產(chǎn)、測(cè)試多環(huán)境驗(yàn)證，小伙伴感興趣可以自行測(cè)試

3 MyBatis 如何使用流式查詢

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

ResultHandler 接口只包含 handleResult 方法，可以獲取到已轉(zhuǎn)換后的 Java 實(shí)體類

@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查詢耗時(shí) :: {} ", System.currentTimeMillis() - start);
    }
}

除了下述注解式的應(yīng)用方式，也可以使用 .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<YOU_TABLE_DO> handler);
}

Mybatis 流式查詢調(diào)用時(shí)間消耗：≈ 18s

JDBC 流式與 MyBatis 封裝的流式讀取對(duì)比

• MyBatis 相對(duì)于原生的流式還是慢上了不少，但是考慮到底層的封裝的特性，這點(diǎn)性能還是可以接受的

• 從內(nèi)存占比而言，兩者波動(dòng)相差無(wú)幾

• MyBatis 相對(duì)于原生 JDBC 更為的方便，因?yàn)榉庋b了回調(diào)函數(shù)以及序列化對(duì)象等特性

兩者具體的使用，可以針對(duì)項(xiàng)目實(shí)際情況而定，沒(méi)有最好的，只有最適合的

結(jié)言

流式查詢、游標(biāo)查詢可以避免 OOM，數(shù)據(jù)量大可以考慮此方案。但是這兩種方式會(huì)占用數(shù)據(jù)庫(kù)連接，使用中不會(huì)釋放，所以線上針對(duì)大數(shù)據(jù)量業(yè)務(wù)用到游標(biāo)和流式操作，一定要進(jìn)行并發(fā)控制

另外針對(duì) JDBC 原生流式查詢，Mybatis 中也進(jìn)行了封裝，雖然會(huì)慢一些，但是 功能以及代碼的整潔程度會(huì)好上不少

感謝各位的閱讀，以上就是“從 MySQL 數(shù)據(jù)庫(kù)里讀取大量數(shù)據(jù)行進(jìn)行處理，怎么做更效益化”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)從 MySQL 數(shù)據(jù)庫(kù)里讀取大量數(shù)據(jù)行進(jìn)行處理，怎么做更效益化這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！