流式查詢1. mybatis的遊標Cursor，分頁大資料查詢

威兰达發表於2024-07-11
MyBatis大資料

流式查詢
流式查詢 指的是查詢成功後不是返回一個集合而是返回一個迭代器，應用可以透過迭代器每次取一條查詢結果。流式查詢的好處是能夠降低記憶體使用。例如我們想要從資料庫取 1000 萬條記錄而又沒有足夠的記憶體時，就不得不分頁查詢。

而分頁查詢就需要我們按照順序查詢並設定一個引數來記錄當前進度並在下次查詢時將進度作為引數傳入。

（比如按id升序查詢，記錄每次查詢結果的最大id, 下次查詢將這個最大id傳入只查詢大於這個id的），否則就會出現深度分頁的情況。而查詢效率取決於表設計，如果設計的不好，那麼每次查詢都會是一次單獨的低效查詢。
而流式查詢不需要自己記錄進度（資料庫來記錄），且即使表設計的較差或在sql比較複雜，也僅僅只需要一次低效查詢。
流式查詢的過程當中，要保證資料庫連線是保持開啟狀態的，否則就會流關閉。
MyBaits透過遊標Cursor實現了流式查詢。 MyBaits Plus基於Mybais, 自然也是支援的。

如何使用
寫一個獲取流的Mapper
不需要其他配置， 像平常我們寫查詢一樣在Mapper定義查詢，並將返回結果設為Cursor即可實現一個流式查詢。
sql也正常按照時間查詢條件寫，不需要加limit之類的引數。

1. 配置遊標分頁

在MyBatis Plus中，使用遊標分頁需要在mybatis-config.xml或application.yml 中新增配置:

mybatis-plus:
configuration:
settings:
useCursorFetch: true

    @Transactional
    @Async("asyncServiceExecutor")
    public void asyncSaveAndUpdateCardPool(String buId, CardStatusReqVO cardReqVO) {
        try (ShardingCtx s = ShardingCtx.setShardingValue(buId)) {
            log.info("asyncSaveAndUpdateCardPool: {}, {}", buId, cardReqVO);

            try (Cursor<CardUpdateVO> cursor = memberCardRepository.getCardCursorByBatchIdOrCardNum(
                    cardReqVO.getBatchId(),
                    cardReqVO.getStartingNumber(),
                    cardReqVO.getEndingNumber(),
                    cardReqVO.getCardRange())) {

                final int BATCH_SIZE = 1000;
                AtomicBoolean isFirstIteration = new AtomicBoolean(true);
                CopyOnWriteArrayList<CardUpdateVO> updateVOList = new CopyOnWriteArrayList<>();

                for (CardUpdateVO cardUpdateVO : cursor) {
                    updateVOList.add(cardUpdateVO);
                    if (updateVOList.size() == BATCH_SIZE) {
                        batchUpdateCardStatusAsync(buId, updateVOList, cardReqVO, isFirstIteration);
                        updateVOList.clear();
                    }
                }

                if (!updateVOList.isEmpty()) {
                    batchUpdateCardStatusAsync(buId, updateVOList, cardReqVO, isFirstIteration);
                }
            } catch (Exception e) {
                log.error("Cursor process error: ", e);
            }
        }
        log.info("Finished asyncSaveAndUpdateCardPool for cardReqVO: {}, {}", buId, cardReqVO);
    }
介面：
Cursor<CardUpdateVO> getCardCursorByBatchIdOrCardNum(String batchId, String startingNumber, String endingNumber, String cardRange);

實現類：
 @Override
 public Cursor<CardUpdateVO> getCardCursorByBatchIdOrCardNum(String batchId, String startingNumber, String endingNumber, String cardRange) {
     return customerLoyCardMapper.getCardCursorByBatchIdOrCardNum(batchId, startingNumber, endingNumber, cardRange);
 }
    
mapper:
Cursor<CardUpdateVO> getCardCursorByBatchIdOrCardNum(@Param("batchId")String batchId,
                                                         @Param("startingNumber")String startingNumber,
                                                         @Param("endingNumber")String endingNumber,
                                                         @Param("cardRange")String cardRange);
sql:
<select id="getCardCursorByBatchIdOrCardNum" resultType="com.aswatson.csc.member.req.CardUpdateVO" resultSetType="FORWARD_ONLY" fetchSize = "500">
        SELECT M.BU_ID as buId,
        M.MEMBER_ID as memberId,
        M.PROGRAM_ID as programId,
        C.CARD_ID as cardId,
        C.CARD_NUM as cardNum,
        C.VISIBLE_CARD as visibleCard,
        C.CARD_TYPE_CD as cardTypeCd,
        C.STATUS_CD as statusCd,
        C.ACTIVE_FLAG as activeFlag
        FROM CUSTOMER_LOY_CARD C, CUSTOMER_LOY_MEMBER M
        WHERE C.MEMBER_ID = M.MEMBER_ID
        <if test="startingNumber != null and endingNumber != null and cardRange == 1 ">
            AND C.VISIBLE_CARD BETWEEN #{startingNumber} AND #{endingNumber}
        </if>
        <if test="batchId != null and batchId != '' and cardRange == 2 ">
            AND C.BATCH_ID = #{batchId}
        </if>
    </select>

需要注意的點：

獲取一個開啟狀態的連線。

上面提過，使用流式查詢需要保證資料庫連線是保持開啟狀態。而正常情況下我們使用mybaits執行一次查詢，連線都會被關閉或在重置。因此我們需要一些方法來保持連線。

使用事務：事務執行完畢之前連線會一直保持因此，我們可以來使用事務來保持連線。

流式查詢1. mybatis的遊標Cursor，分頁大資料查詢

這是最簡單的方法，但是需要注意的是，由於cursor在遍歷結束的方法末尾後會主動關閉連線。因此：

1. 方法內事務是正常的，在cursor查詢前和遍歷後的的資料操作依然是一個事務。從這裡可以猜測，連線並不在在遍歷完立即關閉，而是採用了類似AOP的手段在方法末尾關閉。
2. 如果你使用了資料庫連線池，那麼池中的這個連線會被關掉，這可能並不會導致你的連線池數量減少，因為連線池的連線資料依賴於各自的規則，
當有連線斷開，連線池會根據自己的策略對做相應的處理，比如重新建立，因此不需要過分關注這個，需要注意的是這個行為可能帶來的影響，
比如druid連線池，druid在事務執行完畢後會進行連線的清理。但是這個連線在myDbTableMapper遍歷完成時已經關閉，
就會導致日誌列印java.sql.SQLException: No operations allowed after statement closed。這個問題不會帶來資料影響，
但是依然屬於一個錯誤，見github-druid-issues，在1.2.10， druid將這個日誌改為debug級別進行了臨時的遮蔽。其他連線池尚未進行測試。

迴圈獲取資料
Cursor繼承了迭代器，可以透過Cursor獲取他的Iterator, 或者直接使用for迴圈來獲取資料。
需要注意的是，Cursor在查詢一瞬間資料就固化了，如果你先查詢，再更新，再遍歷，即使在同一個事務裡面，獲得的結果也是未更新的資料。

關閉流
cursor在遍歷結束後會主動關閉連線。如果未遍歷結束中途退出，可以呼叫cursor的close方法關閉連線。推薦finaly內總是呼叫close或者try(resoure)來保證連線總是被正常關閉。

在使用MyBatis Plus的遊標分頁時，有一些注意事項和建議：

資料庫支援： 遊標分頁依賴資料庫的遊標支援，因此確保資料庫支援遊標分頁功能。

分頁大小： 需要根據實際情況設定合適的分頁大小，過小可能導致頻繁查詢，過大可能失去遊標分頁的優勢。

及時關閉遊標： 使用Cursor時，確保在處理完資料後及時關閉遊標，釋放資源。

效能監控： 對於大資料量的場景，建議進行效能監控和測試，確保遊標分頁帶來的效能提升符合預期。

合理使用快取： 需要根據實際情況考慮是否使用快取，以及如何合理使用快取，以提高查詢效率。

版本更新： MyBatis Plus的版本可能會更新，建議關注最新版本的特性和改進，以獲取更好的支援和效能。
————————————————

相關文章