MyBatis中怎么查詢千萬(wàn)數(shù)據(jù)量

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)MyBatis中怎么查詢千萬(wàn)數(shù)據(jù)量，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

常規(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ù)

流式查詢

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

流式查詢有一點(diǎn)需要注意：必須先讀取(或關(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

游標(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

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)系圖如下

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

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

4. 并將返回結(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ù)

JDBC 通信原理

普通查詢

在 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ì)涼涼，原因如下：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

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

5. 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ì)返回

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

游標(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)象：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

3. 磁盤空間飆升，因?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 回收

4. 客戶端 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

5. 數(shù)據(jù)準(zhǔn)備完成后，進(jìn)行到傳輸數(shù)據(jù)階段，網(wǎng)絡(luò)響應(yīng)開始飆升，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ì)非常高，占用大量的磁盤空間以及性能

流式查詢

當(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ū)

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

4. 對(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)單

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

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

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

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

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

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

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

圖1 數(shù)據(jù)僅供參考

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

圖2 數(shù)據(jù)僅供參考

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

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

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

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

圖3 數(shù)據(jù)僅供參考

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

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

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

圖4 數(shù)據(jù)僅供參考

游標(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è)試

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

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

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

上述就是小編為大家分享的MyBatis中怎么查詢千萬(wàn)數(shù)據(jù)量了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。