面試官：從 MySQL 讀取 100w 數據進行處理，應該怎麼做？問倒一大遍！

背景大數據量操作的場景大致如下：數據遷移數據導出批量處理數據在實際工作中當指定查詢數據過大時，我們一般使用分頁查詢的方式一頁一頁的將數據放到記憶體處理。但有些情況不需要分頁的方式查詢數據或分很大一頁查詢數據時，如果一下子將數據全部載入出來到記憶體中，很可能會發生OOM(記憶體溢出)；而且查詢會很 ...

背景

大數據量操作的場景大致如下：

數據遷移
數據導出
批量處理數據

在實際工作中當指定查詢數據過大時，我們一般使用分頁查詢的方式一頁一頁的將數據放到記憶體處理。但有些情況不需要分頁的方式查詢數據或分很大一頁查詢數據時，如果一下子將數據全部載入出來到記憶體中，很可能會發生OOM(記憶體溢出)；而且查詢會很慢，因為框架耗費大量的時間和記憶體去把資料庫查詢的結果封裝成我們想要的對象（實體類）。

舉例：在業務系統需要從 MySQL 資料庫里讀取 100w 數據行進行處理，應該怎麼做？

做法通常如下：

常規查詢： 一次性讀取 100w 數據到 JVM 記憶體中，或者分頁讀取
流式查詢： 建立長連接，利用服務端游標，每次讀取一條載入到 JVM 記憶體（多次獲取，一次一行）
游標查詢： 和流式一樣，通過 fetchSize 參數，控制一次讀取多少條數據（多次獲取，一次多行）

常規查詢

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

舉例：

假設單表 100w 數據量，一般會採用分頁的方式查詢：

@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
 
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page, @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper);
 
}

註：該示例使用的 MybatisPlus

該方式比較簡單，如果在不考慮 LIMIT 深分頁優化情況下，估計你的資料庫伺服器就噶皮了，或者你能等上幾十分鐘或幾小時，甚至幾天時間檢索數據。

推薦一個開源免費的 Spring Boot 最全教程：

https://github.com/javastacks/spring-boot-best-practice

流式查詢

流式查詢指的是查詢成功後不是返回一個集合而是返回一個迭代器，應用每次從迭代器取一條查詢結果。流式查詢的好處是能夠降低記憶體使用。

如果沒有流式查詢，我們想要從資料庫取 100w 條記錄而又沒有足夠的記憶體時，就不得不分頁查詢，而分頁查詢效率取決於表設計，如果設計的不好，就無法執行高效的分頁查詢。因此流式查詢是一個資料庫訪問框架必須具備的功能。

MyBatis 中使用流式查詢避免數據量過大導致 OOM ，但在流式查詢的過程當中，資料庫連接是保持打開狀態的，因此要註意的是：

執行一個流式查詢後，資料庫訪問框架就不負責關閉資料庫連接了，需要應用在取完數據後自己關閉。
必須先讀取（或關閉）結果集中的所有行，然後才能對連接發出任何其他查詢，否則將引發異常。

MyBatis 流式查詢介面

MyBatis 提供了一個叫 org.apache.ibatis.cursor.Cursor 的介面類用於流式查詢，這個介面繼承了 java.io.Closeable 和 java.lang.Iterable 介面，由此可知：

Cursor 是可關閉的；
Cursor 是可遍歷的。

除此之外，Cursor 還提供了三個方法：

isOpen()： 用於在取數據之前判斷 Cursor 對象是否是打開狀態。只有當打開時 Cursor 才能取數據；
isConsumed()： 用於判斷查詢結果是否全部取完。
getCurrentIndex()： 返回已經獲取了多少條數據

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

為什麼要用流式查詢？

如果有一個很大的查詢結果需要遍歷處理，又不想一次性將結果集裝入客戶端記憶體，就可以考慮使用流式查詢；

分庫分表場景下，單個表的查詢結果集雖然不大，但如果某個查詢跨了多個庫多個表，又要做結果集的合併、排序等動作，依然有可能撐爆記憶體；詳細研究了sharding-sphere的代碼不難發現，除了group by與order by欄位不一樣之外，其他的場景都非常適合使用流式查詢，可以最大限度的降低對客戶端記憶體的消耗。

游標查詢

對大量數據進行處理時，為防止記憶體泄漏情況發生，也可以採用游標方式進行數據查詢處理。這種處理方式比常規查詢要快很多。

當查詢百萬級的數據的時候，還可以使用游標方式進行數據查詢處理，不僅可以節省記憶體的消耗，而且還不需要一次性取出所有數據，可以進行逐條處理或逐條取出部分批量處理。一次查詢指定 fetchSize 的數據，直到把數據全部處理完。

Mybatis 的處理加了兩個註解：@Options 和 @ResultType

@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
 
    // 方式一 多次獲取，一次多行
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000)
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page, @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper);
 
    // 方式二 一次獲取，一次一行
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 100000)
    @ResultType(BigDataSearchEntity.class)
    void listData(@Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper, ResultHandler<BigDataSearchEntity> handler);
 
}

@Options

ResultSet.FORWORD_ONLY：結果集的游標只能向下滾動
ResultSet.SCROLL_INSENSITIVE：結果集的游標可以上下移動，當資料庫變化時，當前結果集不變
ResultSet.SCROLL_SENSITIVE：返回可滾動的結果集，當資料庫變化時，當前結果集同步改變
fetchSize：每次獲取量

@ResultType

@ResultType(BigDataSearchEntity.class)：轉換成返回實體類型

註意：返回類型必須為 void ，因為查詢的結果在 ResultHandler 里處理數據，所以這個 hander 也是必須的，可以使用 lambda 實現一個依次處理邏輯。

註意：

雖然上面的代碼中都有 @Options 但實際操作卻有不同：

方式一是多次查詢，一次返回多條；
方式二是一次查詢，一次返回一條；

原因：

Oracle 是從伺服器一次取出 fetch size 條記錄放在客戶端，客戶端處理完成一個批次後再向伺服器取下一個批次，直到所有數據處理完成。

MySQL 是在執行 ResultSet.next() 方法時，會通過資料庫連接一條一條的返回。flush buffer 的過程是阻塞式的，如果網路中發生了擁塞，send buffer 被填滿，會導致 buffer 一直 flush 不出去，那 MySQL 的處理線程會阻塞，從而避免數據把客戶端記憶體撐爆。

非流式查詢和流式查詢區別：