由於現在 ORM 框架的成熟運用，很多小夥伴對於 JDBC 的概念有些薄弱，ORM 框架底層其實是透過 JDBC 操作的 DB

JDBC(JavaDataBase Connectivity)是 Java 資料庫連線, 說得直白點就是使用 Java 語言操作資料庫

由 SUN 公司提供出一套訪問資料庫的規範 API， 並提供相對應的連線資料庫協議標準， 然後 各廠商根據規範提供一套訪問自家資料庫的 API 介面

文章大資料量操作核心圍繞 JDBC 展開，目錄結構如下：

MySQL JDBC 大資料量操作常規查詢流式查詢遊標查詢JDBC RowDataJDBC 通訊原理流式遊標記憶體分析單次呼叫記憶體使用併發呼叫記憶體使用MyBatis 如何使用流式查詢結言MySql JDBC 大資料量操作

整篇文章以大資料量操作為議題，透過開發過程中的需求引出相關知識點

遷移資料匯出資料批次處理資料

一般而言筆者認為在 Java Web 程式裡，能夠被稱為大資料量的，幾十萬到千萬不等，再高的話 Java(WEB 應用)處理就不怎麼合適了

舉個例子，現在業務系統需要從 MySQL 資料庫裡讀取 500w 資料行進行處理，應該怎麼做

常規查詢，一次性讀取 500w 資料到 JVM 記憶體中，或者分頁讀取流式查詢，建立長連線，利用服務端遊標，每次讀取一條載入到 JVM 記憶體遊標查詢，和流式一樣，透過 fetchSize 引數，控制一次讀取多少條資料常規查詢

預設情況下，完整的檢索結果集會將其儲存在記憶體中。在大多數情況下，這是最有效的操作方式，並且由於 MySQL 網路協議的設計，因此更易於實現

假設單表 500w 資料量，沒有人會一次性載入到記憶體中，一般會採用分頁的方式

@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 深分頁最佳化情況下，線上資料庫伺服器就涼了，亦或者你能等個幾天時間檢索資料

流式查詢

如果你正在使用具有大量資料行的 ResultSet，並且無法在 JVM 中為其分配所需的記憶體堆空間，則可以告訴驅動程式從結果流中返回一行

流式查詢有一點需要注意：必須先讀取(或關閉)結果集中的所有行，然後才能對連線發出任何其他查詢，否則將引發異常

使用流式查詢，則要保持對產生結果集的語句所引用的表的併發訪問，因為其 查詢會獨佔連線，所以必須儘快處理

@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); } 

流式查詢庫表資料量 500w 單次呼叫時間消耗：≈ 6s

遊標查詢

SpringBoot 2.x 版本預設連線池為 HikariPool，連線物件是 HikariProxyConnection，所以下述設定遊標方式就不可行了

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

需要在資料庫連線資訊裡拼接 &useCursorFetch=true。其次設定 Statement 每次讀取資料數量，比如一次讀取 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); } 

遊標查詢庫表資料量 500w 單次呼叫時間消耗：≈ 18s

JDBC RowData

上面都使用到了方法 loopResultSet，方法內部只是進行了 while 迴圈，常規、流式、遊標查詢的核心點在於 next 方法

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

ResultSet.next() 的邏輯是實現類 ResultSetImpl 每次都從 RowData 獲取下一行的資料。RowData 是一個介面，實現關係圖如下

預設情況下 ResultSet 會使用 RowDataStatic 例項，在生成 RowDataStatic 物件時就會把 ResultSet 中所有記錄讀到記憶體裡，之後透過 next() 再一條條從記憶體中讀

RowDataCursor 的呼叫為批處理，然後進行內部快取，流程如下：

首先會檢視自己內部緩衝區是否有資料沒有返回，如果有則返回下一行如果都讀取完畢，向 MySQL Server 觸發一個新的請求讀取 fetchSize 數量結果並將返回結果緩衝到內部緩衝區，然後返回第一行資料

當採用流式處理時，ResultSet 使用的是 RowDataDynamic 物件，而這個物件 next() 每次呼叫都會發起 IO 讀取單行資料

總結來說就是，預設的 RowDataStatic 讀取全部資料到客戶端記憶體中，也就是我們的 JVM;RowDataCursor 一次讀取 fetchSize 行，消費完成再發起請求呼叫;RowDataDynamic 每次 IO 呼叫讀取一條資料

JDBC 通訊原理普通查詢

在 JDBC 與 MySQL 服務端的互動是透過 Socket 完成的，對應到網路程式設計，可以把 MySQL 當作一個 SocketServer，因此一個完整的請求鏈路應該是：

JDBC 客戶端 -> 客戶端 Socket -> MySQL -> 檢索資料返回 -> MySQL 核心 Socket 緩衝區 -> 網路 -> 客戶端 Socket Buffer -> JDBC 客戶端

普通查詢的方式在查詢大資料量時，所在 JVM 可能會涼涼，原因如下：

MySQL Server 會將檢索出的 SQL 結果集透過輸出流寫入到核心對應的 Socket Buffer核心緩衝區透過 JDBC 發起的 TCP 鏈路進行回傳資料，此時資料會先進入 JDBC 客戶端所在核心緩衝區JDBC 發起 SQL 操作後，程式會被阻塞在輸入流的 read 操作上，當緩衝區有資料時，程式會被喚醒進而將緩衝區資料讀取到 JVM 記憶體中MySQL Server 會不斷髮送資料，JDBC 不斷讀取緩衝區資料到 Java 記憶體中，雖然此時資料已到 JDBC 所在程式本地，但是 JDBC 還沒有對 execute 方法呼叫處進行響應，因為需要等到對應資料讀取完畢才會返回弊端就顯而易見了，如果查詢資料量過大，會不斷經歷 GC，然後就是記憶體溢位遊標查詢

透過上文得知，遊標可以解決普通查詢大資料量的記憶體溢位問題，但是

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

其實，在我們使用遊標查詢時，MySQL 需要建立一個臨時空間來存放需要被讀取的資料，所以不會和 DML 寫入操作產生衝突

但是遊標查詢會引發以下現象：

IOPS 飆升，因為需要返回的資料需要寫入到臨時空間中，存在大量的 IO 讀取和寫入，此流程可能會引起其它業務的寫入抖動磁碟空間飆升，因為寫入臨時空間的資料是在原表之外的，如果表資料過大，極端情況下可能會導致資料庫磁碟寫滿，這時網路輸出時沒有變化的。而寫入臨時空間的資料會在 讀取完成或客戶端發起 ResultSet#close 操作時由 MySQL 回收客戶端 JDBC 發起 SQL 查詢，可能會有長時間等待 SQL 響應，這段時間為服務端準備資料階段。但是 普通查詢等待時間與遊標查詢等待時間原理上是不一致的，前者是一致在讀取網路緩衝區的資料，沒有響應到業務層面;後者是 MySQL 在準備臨時資料空間，沒有響應到 JDBC資料準備完成後，進行到傳輸資料階段，網路響應開始飆升，IOPS 由"讀寫"轉變為"讀取"

採用遊標查詢的方式 通訊效率比較低，因為客戶端消費完 fetchSize 行資料，就需要發起請求到服務端請求，在資料庫前期準備階段 IOPS 會非常高，佔用大量的磁碟空間以及效能

流式查詢

當客戶端與 MySQL Server 端建立起連線並且互動查詢時，MySQL Server 會透過輸出流將 SQL 結果集返回輸出，也就是 向本地的核心對應的 Socket Buffer 中寫入資料，然後將核心中的資料透過 TCP 鏈路回傳資料到 JDBC 對應的伺服器核心緩衝區

JDBC 透過輸入流 read 方法去讀取核心緩衝區資料，因為開啟了流式讀取，每次業務程式接收到的資料只有一條MySQL 服務端會向 JDBC 代表的客戶端核心源源不斷地輸送資料，直到客戶端請求 Socket 緩衝區滿，這時的 MySQL 服務端會阻塞對於 JDBC 客戶端而言，資料每次讀取都是從本機器的核心緩衝區，所以效能會更快一些，一般情況不必擔心本機核心無資料消費(除非 MySQL 服務端傳遞來的資料，在客戶端不做任何業務邏輯，拿到資料直接放棄，會發生客戶端消費比服務端超前的情況)

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

相對於遊標查詢，流式對資料庫的影響時間要更長一些另外流式查詢依賴網路，導致網路擁塞可能性較大流式遊標記憶體分析

表資料量：500w

記憶體檢視工具：JDK 自帶 Jvisualvm

設定 JVM 引數：-Xmx512m -Xms512m

單次呼叫記憶體使用

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

圖1 資料僅供參考

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

圖2 資料僅供參考

根據記憶體佔用情況來看，遊標查詢和流式查詢都 能夠很好地防止 OOM

併發呼叫記憶體使用

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

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

圖3 資料僅供參考

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

流式查詢併發呼叫時間平均消耗：≈ 55s

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

圖4 資料僅供參考

遊標查詢併發呼叫時間平均消耗：≈ 83s

因為裝置限制，以及部分情況只會在極端下產生，所以沒有進行生產、測試多環境驗證，小夥伴感興趣可以自行測試

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<YOU_TABLE_DO> handler); } 

Mybatis 流式查詢呼叫時間消耗：≈ 18s

JDBC 流式與 MyBatis 封裝的流式讀取對比MyBatis 相對於原生的流式還是慢上了不少，但是考慮到底層的封裝的特性，這點效能還是可以接受的從記憶體佔比而言，兩者波動相差無幾

MyBatis 相對於原生 JDBC 更為的方便，因為封裝了回撥函式以及序列化物件等特性

兩者具體的使用，可以針對專案實際情況而定，沒有最好的，只有最適合的

結言

流式查詢、遊標查詢可以避免 OOM，資料量大可以考慮此方案。但是這兩種方式會佔用資料庫連線，使用中不會釋放，所以線上針對大資料量業務用到遊標和流式操作，一定要進行併發控制

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