戰(zhàn)：千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出與性能優(yōu)化指南)
1. 項(xiàng)目概述當(dāng)MyBatis遇上千萬(wàn)級(jí)數(shù)據(jù)做后端開(kāi)發(fā)尤其是處理數(shù)據(jù)報(bào)表、數(shù)據(jù)導(dǎo)出或者大數(shù)據(jù)量分析的場(chǎng)景你肯定遇到過(guò)這樣的頭疼時(shí)刻一個(gè)查詢需要返回幾十萬(wàn)甚至上百萬(wàn)條記錄。如果直接用傳統(tǒng)的ListT一次性加載到內(nèi)存輕則接口響應(yīng)緩慢內(nèi)存飆升重則直接OutOfMemoryError服務(wù)掛掉。這時(shí)候MyBatis的流式查詢Streaming Query就成了你的救命稻草。它允許你像打開(kāi)一個(gè)水龍頭一樣從數(shù)據(jù)庫(kù)里“流式”地、一條一條地獲取數(shù)據(jù)而不是把整桶水都先搬到內(nèi)存里。今天我就結(jié)合自己處理千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出的實(shí)戰(zhàn)經(jīng)驗(yàn)來(lái)徹底拆解MyBatis流式查詢的原理、實(shí)現(xiàn)、坑點(diǎn)以及最佳實(shí)踐。無(wú)論你是想優(yōu)化現(xiàn)有的大數(shù)據(jù)查詢接口還是為即將到來(lái)的海量數(shù)據(jù)處理做準(zhǔn)備這篇內(nèi)容都能給你一套可直接落地的方案。2. 流式查詢的核心原理與為什么需要它2.1 傳統(tǒng)查詢的瓶頸全量加載之痛在深入流式查詢之前我們必須先搞清楚傳統(tǒng)方式為什么不行。當(dāng)我們執(zhí)行一個(gè)典型的MyBatis查詢例如select idselectLargeData resultTypecom.example.User SELECT id, name, email FROM user WHERE create_time #{startTime} /select對(duì)應(yīng)的Mapper接口方法返回一個(gè)ListUser。MyBatis或者說(shuō)底層的JDBC驅(qū)動(dòng)在執(zhí)行這個(gè)查詢時(shí)其默認(rèn)行為是一次性將所有匹配的結(jié)果集從數(shù)據(jù)庫(kù)服務(wù)器通過(guò)網(wǎng)絡(luò)傳輸?shù)綉?yīng)用服務(wù)器的內(nèi)存中并封裝成完整的List對(duì)象。這個(gè)過(guò)程存在幾個(gè)致命問(wèn)題內(nèi)存壓力假設(shè)一條User記錄在內(nèi)存中占用1KB1000萬(wàn)條數(shù)據(jù)就是約10GB。JVM堆內(nèi)存很可能無(wú)法容納直接導(dǎo)致OOM。網(wǎng)絡(luò)與數(shù)據(jù)庫(kù)壓力數(shù)據(jù)庫(kù)需要一次性準(zhǔn)備并發(fā)送整個(gè)結(jié)果集這期間會(huì)長(zhǎng)時(shí)間占用數(shù)據(jù)庫(kù)連接和網(wǎng)絡(luò)帶寬可能導(dǎo)致數(shù)據(jù)庫(kù)響應(yīng)變慢影響其他查詢。響應(yīng)延遲應(yīng)用必須等待所有數(shù)據(jù)都傳輸、反序列化完成后才能開(kāi)始處理并返回給客戶端。用戶會(huì)經(jīng)歷漫長(zhǎng)的等待體驗(yàn)極差。2.2 流式查詢的工作機(jī)制細(xì)水長(zhǎng)流流式查詢改變了這個(gè)范式。它的核心思想是保持?jǐn)?shù)據(jù)庫(kù)游標(biāo)Cursor打開(kāi)然后讓應(yīng)用像迭代器Iterator一樣一次從游標(biāo)中獲取一條或一小批記錄進(jìn)行處理處理完一條就丟棄一條或批量處理內(nèi)存中始終只保持少量數(shù)據(jù)。其背后的技術(shù)棧是JDBC層面通過(guò)Statement.setFetchSize(Integer.MIN_VALUE)MySQL驅(qū)動(dòng)或使用ResultSet.TYPE_FORWARD_ONLY和CONCUR_READ_ONLY模式并設(shè)置合適的fetchSize來(lái)告訴驅(qū)動(dòng)我們想要流式獲取結(jié)果。MyBatis層面提供了CursorT接口作為流式查詢的返回類型。Cursor實(shí)現(xiàn)了IterableT和IteratorT你可以像遍歷普通集合一樣遍歷它但每次next()調(diào)用才會(huì)驅(qū)動(dòng)JDBC從網(wǎng)絡(luò)連接中獲取下一條數(shù)據(jù)。數(shù)據(jù)庫(kù)層面以MySQL為例當(dāng)使用流式結(jié)果集時(shí)數(shù)據(jù)庫(kù)服務(wù)器會(huì)保持結(jié)果集和相關(guān)的連接、資源處于打開(kāi)狀態(tài)等待客戶端逐條請(qǐng)求數(shù)據(jù)。關(guān)鍵區(qū)別類比傳統(tǒng)查詢就像點(diǎn)外賣餐廳數(shù)據(jù)庫(kù)必須把所有菜數(shù)據(jù)都做好、打包好騎手網(wǎng)絡(luò)一次性全部送到你家內(nèi)存你才能開(kāi)始吃。流式查詢就像吃回轉(zhuǎn)壽司廚師數(shù)據(jù)庫(kù)不斷地把做好的壽司數(shù)據(jù)放在傳送帶連接上你應(yīng)用坐在旁邊看到想吃需要處理的就拿下來(lái)吃完盤子就被收走內(nèi)存釋放。你永遠(yuǎn)不需要同時(shí)擁有所有的壽司。2.3 哪些場(chǎng)景必須使用流式查詢不是所有查詢都需要流式。引入流式查詢會(huì)帶來(lái)額外的復(fù)雜性如事務(wù)和連接管理。判斷標(biāo)準(zhǔn)很簡(jiǎn)單數(shù)據(jù)量極大無(wú)法一次性裝入內(nèi)存這是最直接的信號(hào)。當(dāng)你預(yù)計(jì)查詢結(jié)果在數(shù)萬(wàn)條以上且每條記錄字段較多、體積較大時(shí)就應(yīng)該考慮流式。需要逐條或分批處理且處理邏輯可獨(dú)立例如數(shù)據(jù)導(dǎo)出為CSV/Excel文件、數(shù)據(jù)清洗后寫入另一個(gè)存儲(chǔ)系統(tǒng)如Elasticsearch、另一個(gè)數(shù)據(jù)庫(kù)、實(shí)時(shí)計(jì)算統(tǒng)計(jì)指標(biāo)等。處理完的數(shù)據(jù)可以立即丟棄或轉(zhuǎn)移。需要提供實(shí)時(shí)或漸進(jìn)式響應(yīng)比如一個(gè)大型報(bào)表生成你可以邊查詢邊生成文件并即時(shí)提供下載鏈接或者通過(guò)WebSocket分批推送數(shù)據(jù)到前端提升用戶體驗(yàn)。注意流式查詢并非為了提升“查詢速度”。實(shí)際上由于需要保持連接和游標(biāo)整個(gè)處理過(guò)程的總耗時(shí)可能比一次性獲取更長(zhǎng)。它的核心價(jià)值在于用時(shí)間換空間以及提供漸進(jìn)式處理的能力避免內(nèi)存瓶頸。3. MyBatis流式查詢的三種實(shí)現(xiàn)方式與選型MyBatis提供了不止一種方式來(lái)實(shí)現(xiàn)流式查詢每種方式各有優(yōu)劣和適用場(chǎng)景。理解它們之間的區(qū)別是正確選型的關(guān)鍵。3.1 方式一使用CursorT接口推薦這是MyBatis官方最直接、最現(xiàn)代的支持方式。你只需要將Mapper方法的返回值定義為CursorT類型。定義Mapper接口import org.apache.ibatis.cursor.Cursor; public interface UserMapper { CursorUser selectLargeDataStream(Param(startTime) Date startTime); }編寫XML映射select idselectLargeDataStream resultTypecom.example.User SELECT id, name, email, create_time FROM user WHERE create_time #{startTime} ORDER BY id !-- 流式查詢強(qiáng)烈建議排序保證順序和可重復(fù)性 -- /select服務(wù)層調(diào)用與遍歷Service Transactional // 事務(wù)至關(guān)重要 public class DataExportService { Autowired private UserMapper userMapper; public void exportLargeData(Date startTime, OutputStream outputStream) { try (CursorUser cursor userMapper.selectLargeDataStream(startTime)) { CSVWriter writer new CSVWriter(new OutputStreamWriter(outputStream)); // 寫入表頭 writer.writeNext(new String[]{ID, Name, Email, Create Time}); for (User user : cursor) { // 這里開(kāi)始逐條遍歷觸發(fā)數(shù)據(jù)獲取 // 處理每條數(shù)據(jù)例如寫入CSV writer.writeNext(new String[]{ String.valueOf(user.getId()), user.getName(), user.getEmail(), user.getCreateTime().toString() }); // 可選每處理1000條刷新一次輸出流避免內(nèi)存堆積 if (cursor.getCurrentIndex() % 1000 0) { writer.flush(); } } writer.flush(); } catch (IOException e) { throw new RuntimeException(導(dǎo)出失敗, e); } // Cursor在try-with-resources中會(huì)自動(dòng)關(guān)閉確保資源釋放 } }為什么推薦這種方式語(yǔ)義清晰CursorT類型明確表達(dá)了“這是一個(gè)流式查詢”。資源管理方便Cursor實(shí)現(xiàn)了AutoCloseable配合try-with-resources語(yǔ)法可以確保數(shù)據(jù)庫(kù)游標(biāo)和連接被正確關(guān)閉避免資源泄漏。與Spring事務(wù)集成好在Transactional注解的方法內(nèi)使用可以確保在整個(gè)遍歷過(guò)程中數(shù)據(jù)庫(kù)連接和事務(wù)保持一致。3.2 方式二使用ResultHandler更底層控制ResultHandler是一個(gè)回調(diào)接口。MyBatis在從數(shù)據(jù)庫(kù)獲取到每一行結(jié)果時(shí)都會(huì)調(diào)用這個(gè)接口的handleResult方法。這種方式將處理邏輯完全交給開(kāi)發(fā)者控制粒度最細(xì)。定義ResultHandlerimport org.apache.ibatis.session.ResultHandler; public class UserExportResultHandler implements ResultHandlerUser { private final CSVWriter writer; private int count 0; public UserExportResultHandler(CSVWriter writer) { this.writer writer; writer.writeNext(new String[]{ID, Name, Email}); } Override public void handleResult(ResultContext? extends User resultContext) { User user resultContext.getResultObject(); // 處理單條記錄 writer.writeNext(new String[]{ String.valueOf(user.getId()), user.getName(), user.getEmail() }); count; if (count % 1000 0) { writer.flush(); } // 你甚至可以根據(jù)條件停止處理 // if (count 10000) { // resultContext.stop(); // } } public int getCount() { return count; } }Mapper接口和XML定義Mapper接口方法返回值為void并增加ResultHandler參數(shù)。public interface UserMapper { void selectLargeDataWithHandler(Param(startTime) Date startTime, ResultHandlerUser handler); }XML映射文件不需要特殊改動(dòng)和普通查詢一樣。服務(wù)層調(diào)用Service Transactional public class DataExportServiceV2 { Autowired private UserMapper userMapper; public void exportLargeData(Date startTime, OutputStream outputStream) throws IOException { try (CSVWriter writer new CSVWriter(new OutputStreamWriter(outputStream))) { UserExportResultHandler handler new UserExportResultHandler(writer); // 執(zhí)行查詢結(jié)果將通過(guò)handler處理 userMapper.selectLargeDataWithHandler(startTime, handler); writer.flush(); System.out.println(共處理數(shù)據(jù): handler.getCount() 條); } } }適用場(chǎng)景與注意事項(xiàng)優(yōu)點(diǎn)絕對(duì)的控制權(quán)可以在處理每條數(shù)據(jù)時(shí)做任何事甚至可以中途停止resultContext.stop()。缺點(diǎn)代碼更復(fù)雜需要自己創(chuàng)建和管理ResultHandler實(shí)例。資源關(guān)閉的邏輯也需要更小心主要關(guān)閉SqlSession。適用當(dāng)你需要對(duì)結(jié)果集進(jìn)行非常復(fù)雜的、有狀態(tài)的逐行處理時(shí)。3.3 方式三自定義ExecutorType為REUSE或BATCH誤區(qū)澄清網(wǎng)上有些資料會(huì)提到在SqlSession上設(shè)置ExecutorType為REUSE或BATCH來(lái)實(shí)現(xiàn)“流式”或“批量”效果。這里必須澄清一個(gè)常見(jiàn)的誤區(qū)ExecutorType.SIMPLE默認(rèn)執(zhí)行器。每次執(zhí)行完語(yǔ)句就關(guān)閉Statement對(duì)象。ExecutorType.REUSE復(fù)用Statement對(duì)象。對(duì)于同一模式的SQL例如多次插入不同參數(shù)可以復(fù)用預(yù)編譯的Statement提升效率。但它不改變結(jié)果集的獲取方式。ExecutorType.BATCH批處理執(zhí)行器。將多個(gè)更新操作INSERT, UPDATE, DELETE攢在一起一次性發(fā)送給數(shù)據(jù)庫(kù)大幅提升批量寫入性能。它只針對(duì)更新語(yǔ)句對(duì)SELECT查詢無(wú)效。結(jié)論ExecutorType主要用于優(yōu)化寫入性能無(wú)法實(shí)現(xiàn)SELECT查詢的流式讀取。流式查詢的核心在于對(duì)ResultSet的處理方式而不是Statement的執(zhí)行方式。實(shí)現(xiàn)流式查詢必須依靠Cursor或ResultHandler或者在JDBC層面直接設(shè)置fetchSize。3.4 選型決策指南特性CursorT方式ResultHandler方式易用性高。符合Java迭代器習(xí)慣代碼簡(jiǎn)潔。中。需要實(shí)現(xiàn)回調(diào)接口代碼稍顯分散?？刂屏６戎??？梢灾饤l處理也能獲取當(dāng)前索引。高。可以訪問(wèn)ResultContext能中途停止、跳過(guò)。資源管理優(yōu)。支持try-with-resources自動(dòng)關(guān)閉。需注意。需要在正確的作用域內(nèi)確保SqlSession關(guān)閉。與Spring集成優(yōu)。在Transactional中工作良好。良。同樣需要事務(wù)上下文。推薦場(chǎng)景絕大多數(shù)流式查詢場(chǎng)景如數(shù)據(jù)導(dǎo)出、批量轉(zhuǎn)換。需要精細(xì)控制處理流程或提前終止的場(chǎng)景。對(duì)于90%的開(kāi)發(fā)者首選CursorT方式。它平衡了易用性、安全性和功能性。4. 流式查詢的實(shí)戰(zhàn)配置、陷阱與深度優(yōu)化知道怎么用只是第一步用得好、不出錯(cuò)才是關(guān)鍵。這部分是真正的干貨來(lái)自大量實(shí)戰(zhàn)踩坑后的總結(jié)。4.1 強(qiáng)制要求事務(wù)管理與連接持有這是流式查詢最核心、也最容易出錯(cuò)的地方。流式查詢的本質(zhì)是保持一個(gè)數(shù)據(jù)庫(kù)游標(biāo)打開(kāi)。而游標(biāo)是依附于數(shù)據(jù)庫(kù)連接Connection和事務(wù)Transaction的。錯(cuò)誤示范// 沒(méi)有事務(wù)注解 public void exportData() { CursorUser cursor userMapper.selectLargeDataStream(...); // 遍歷cursor... // 問(wèn)題方法執(zhí)行過(guò)程中MyBatis可能會(huì)在每次cursor.next()時(shí)從連接池獲取新連接 // 導(dǎo)致游標(biāo)所在的連接被關(guān)閉拋出 Connection is closed 異常。 }正確做法必須確保整個(gè)遍歷過(guò)程在一個(gè)數(shù)據(jù)庫(kù)事務(wù)內(nèi)從而保證始終使用同一個(gè)物理連接。Service public class ExportService { Transactional // 關(guān)鍵確保方法在一個(gè)事務(wù)內(nèi)執(zhí)行 public void exportWithTransaction() { try (CursorUser cursor mapper.selectLargeDataStream(...)) { for (User u : cursor) { // 處理數(shù)據(jù) } } } }為什么Transactional會(huì)為這個(gè)方法創(chuàng)建一個(gè)事務(wù)上下文。Spring會(huì)為此上下文綁定一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)連接。在整個(gè)方法執(zhí)行期間所有數(shù)據(jù)庫(kù)操作包括Cursor的遍歷都使用這個(gè)連接游標(biāo)得以保持。連接池注意事項(xiàng)常用的連接池如HikariCP、Druid都有連接回收機(jī)制。如果沒(méi)有事務(wù)保護(hù)連接可能在Cursor未關(guān)閉時(shí)就被回收到池中造成狀態(tài)混亂。事務(wù)阻止了連接被提前歸還。4.2 數(shù)據(jù)庫(kù)驅(qū)動(dòng)與FetchSize的奧秘流式查詢的行為高度依賴于JDBC驅(qū)動(dòng)的實(shí)現(xiàn)。不同數(shù)據(jù)庫(kù)、不同驅(qū)動(dòng)版本配置可能不同。1. MySQL (mysql-connector-java)經(jīng)典方式Statement.setFetchSize(Integer.MIN_VALUE)。這是告訴MySQL驅(qū)動(dòng)使用流式結(jié)果集的“魔法值”。在MyBatis中可以通過(guò)在Mapper XML的select標(biāo)簽里配置fetchSize屬性來(lái)實(shí)現(xiàn)。select idselectLargeDataStream fetchSize-2147483648 resultType... SELECT ... /select驅(qū)動(dòng)版本的影響在較新的驅(qū)動(dòng)版本如8.x中僅設(shè)置fetchSize為負(fù)值可能還不夠。你可能還需要在JDBC連接字符串中顯式指定使用流式讀取spring.datasource.urljdbc:mysql://localhost:3306/db?useCursorFetchtrue設(shè)置useCursorFetchtrue后fetchSize的正值表示每次從服務(wù)器獲取的行數(shù)實(shí)現(xiàn)了“客戶端游標(biāo)”式的分批流式獲取對(duì)服務(wù)器更友好。2. PostgreSQLPostgreSQL的驅(qū)動(dòng)對(duì)流式支持很好。通常只需要設(shè)置一個(gè)合理的正數(shù)fetchSize即可。select idselectLargeDataStream fetchSize1000 resultType... SELECT ... /select這里fetchSize1000意味著每次網(wǎng)絡(luò)往返從服務(wù)器獲取1000條記錄。這是一個(gè)平衡內(nèi)存和網(wǎng)絡(luò)開(kāi)銷的常用值。3. OracleOracle JDBC驅(qū)動(dòng)默認(rèn)就是流式的fetchSize默認(rèn)是10。對(duì)于海量數(shù)據(jù)你可以根據(jù)情況調(diào)大fetchSize比如5000來(lái)減少網(wǎng)絡(luò)通信次數(shù)但要注意客戶端內(nèi)存。實(shí)操心得fetchSize沒(méi)有銀彈。Integer.MIN_VALUEMySQL流式或一個(gè)較小的正數(shù)如1000是安全的起點(diǎn)。對(duì)于超大數(shù)據(jù)量可以嘗試調(diào)大fetchSize以減少網(wǎng)絡(luò)延遲的影響但務(wù)必在測(cè)試環(huán)境中監(jiān)控客戶端內(nèi)存使用。一定要查閱你所使用數(shù)據(jù)庫(kù)驅(qū)動(dòng)的最新官方文檔。4.3 SQL語(yǔ)句的編寫禁忌不是所有SQL都適合流式查詢。必須排序ORDER BY流式處理通常意味著順序處理。如果沒(méi)有ORDER BY數(shù)據(jù)庫(kù)可能以任意順序返回?cái)?shù)據(jù)。在多批次處理或中斷重試時(shí)可能導(dǎo)致數(shù)據(jù)重復(fù)或丟失。強(qiáng)烈建議使用一個(gè)唯一或遞增的字段如主鍵ID、創(chuàng)建時(shí)間進(jìn)行排序。避免大字段BLOB, TEXT, CLOB流式查詢解決的是“行數(shù)多”的問(wèn)題而不是“單行數(shù)據(jù)大”的問(wèn)題。如果單行記錄包含一個(gè)幾十MB的BLOB字段即使只流式獲取一行也可能撐爆內(nèi)存。對(duì)于包含大字段的表考慮分兩次查詢或者使用數(shù)據(jù)庫(kù)特定的流式讀取大對(duì)象API。使用覆蓋索引確保你的WHERE條件和ORDER BY字段能被索引覆蓋。流式查詢雖然減輕了客戶端壓力但數(shù)據(jù)庫(kù)服務(wù)器仍然需要執(zhí)行完整的查詢。一個(gè)全表掃描的流式查詢對(duì)數(shù)據(jù)庫(kù)同樣是災(zāi)難。使用EXPLAIN分析你的SQL。4.4 資源泄漏你必須關(guān)閉CursorCursor背后是打開(kāi)的數(shù)據(jù)庫(kù)ResultSet和Statement。如果不關(guān)閉就會(huì)導(dǎo)致數(shù)據(jù)庫(kù)游標(biāo)泄漏消耗服務(wù)器資源。數(shù)據(jù)庫(kù)連接無(wú)法及時(shí)釋放回連接池可能導(dǎo)致連接池耗盡。關(guān)閉的最佳實(shí)踐// 正確做法1: try-with-resources (Java 7) try (CursorUser cursor userMapper.selectLargeDataStream(...)) { for (User user : cursor) { // process } } // 無(wú)論是否異常cursor都會(huì)自動(dòng)關(guān)閉 // 正確做法2: 在finally塊中手動(dòng)關(guān)閉 CursorUser cursor null; try { cursor userMapper.selectLargeDataStream(...); // ... 遍歷處理 } finally { if (cursor ! null !cursor.isClosed()) { cursor.close(); } }絕對(duì)不要在遍歷到一半時(shí)直接return而不關(guān)閉Cursor。4.5 超時(shí)與中斷處理流式查詢可能運(yùn)行很長(zhǎng)時(shí)間。你需要考慮超時(shí)和用戶中斷。查詢超時(shí)可以在MyBatis的select標(biāo)簽中設(shè)置timeout屬性單位秒或者在數(shù)據(jù)源連接字符串中配置socketTimeout。select idselectLargeDataStream timeout300 ... !-- 5分鐘超時(shí) --事務(wù)超時(shí)如果你使用了Spring的Transactional可以設(shè)置事務(wù)超時(shí)Transactional(timeout 300)。注意這個(gè)超時(shí)是從事務(wù)開(kāi)始算起如果事務(wù)中還做了其他操作需要留有余地。用戶中斷在Web應(yīng)用中如果用戶取消了導(dǎo)出請(qǐng)求你需要有能力停止正在進(jìn)行的流式查詢。這通常需要將Cursor的遍歷放在一個(gè)可中斷的線程中。提供一個(gè)取消接口該接口設(shè)置一個(gè)中斷標(biāo)志。在遍歷循環(huán)中定期檢查這個(gè)中斷標(biāo)志如果被中斷則調(diào)用cursor.close()并退出。 這是一個(gè)相對(duì)高級(jí)的特性需要結(jié)合具體的應(yīng)用框架如Spring MVC的DeferredResult來(lái)實(shí)現(xiàn)。5. 性能調(diào)優(yōu)與監(jiān)控讓千萬(wàn)級(jí)查詢飛起來(lái)處理千萬(wàn)級(jí)數(shù)據(jù)光有流式查詢還不夠需要一套組合拳。5.1 分頁(yè) vs 流式查詢?nèi)绾芜x擇很多人面對(duì)大數(shù)據(jù)查詢第一反應(yīng)是“分頁(yè)”。但分頁(yè)在處理超大數(shù)據(jù)量時(shí)存在嚴(yán)重問(wèn)題深度分頁(yè)性能極差LIMIT 1000000, 100這種查詢數(shù)據(jù)庫(kù)需要先掃描并跳過(guò)前100萬(wàn)條記錄成本極高。數(shù)據(jù)一致性風(fēng)險(xiǎn)如果數(shù)據(jù)在分頁(yè)過(guò)程中被增刪可能導(dǎo)致某一頁(yè)數(shù)據(jù)重復(fù)或丟失。決策指南使用流式查詢當(dāng)你的目的是處理全部數(shù)據(jù)如導(dǎo)出、ETL、計(jì)算總和且不需要將全部數(shù)據(jù)同時(shí)呈現(xiàn)給用戶時(shí)。使用分頁(yè)當(dāng)你的目的是在UI上展示數(shù)據(jù)且用戶只需要瀏覽其中一部分時(shí)。對(duì)于深度分頁(yè)應(yīng)使用“游標(biāo)分頁(yè)”或“seek method”即WHERE id last_id LIMIT 100利用索引避免偏移。兩者結(jié)合有時(shí)可以先用流式查詢處理數(shù)據(jù)將處理結(jié)果如聚合后的統(tǒng)計(jì)信息、生成的文件存儲(chǔ)起來(lái)再通過(guò)分頁(yè)提供給用戶查看。這是非常成熟的架構(gòu)模式。5.2 應(yīng)用層批處理減少I/O開(kāi)銷即使使用流式查詢逐條獲取如果逐條寫入文件或調(diào)用遠(yuǎn)程接口I/O效率也會(huì)極低。優(yōu)化在應(yīng)用層做批處理。try (CursorUser cursor userMapper.selectLargeDataStream(...)) { ListUser buffer new ArrayList(BATCH_SIZE); // 例如 BATCH_SIZE 1000 for (User user : cursor) { buffer.add(user); if (buffer.size() BATCH_SIZE) { // 批量處理寫入文件、插入ES、發(fā)送消息等 batchWriteToCSV(buffer, writer); buffer.clear(); writer.flush(); // 定期刷新輸出流 } } // 處理最后一批不滿 BATCH_SIZE 的數(shù)據(jù) if (!buffer.isEmpty()) { batchWriteToCSV(buffer, writer); } }通過(guò)內(nèi)存緩沖區(qū)積累一定數(shù)量的記錄后再進(jìn)行批量I/O操作可以大幅減少系統(tǒng)調(diào)用或網(wǎng)絡(luò)請(qǐng)求的次數(shù)提升整體吞吐量。5.3 JVM內(nèi)存與GC優(yōu)化流式查詢的目標(biāo)是降低內(nèi)存壓力但如果處理邏輯不當(dāng)仍然可能引起GC問(wèn)題。避免在遍歷中積累數(shù)據(jù)最忌諱在遍歷Cursor時(shí)又將所有數(shù)據(jù)添加到一個(gè)新的ArrayList中這就失去了流式的意義。及時(shí)釋放對(duì)象引用對(duì)于每一條處理完的記錄確保沒(méi)有全局的或長(zhǎng)時(shí)間存活的對(duì)象引用它。讓垃圾回收器可以及時(shí)回收。調(diào)整JVM參數(shù)雖然流式查詢降低了堆內(nèi)存需求但頻繁創(chuàng)建和丟棄大量短期對(duì)象User對(duì)象可能加劇Young GC?？梢赃m當(dāng)調(diào)整新生代大小-Xmn并考慮使用G1或ZGC這類低延遲垃圾收集器來(lái)應(yīng)對(duì)這種“高分配速率”的場(chǎng)景。5.4 數(shù)據(jù)庫(kù)層面的配合優(yōu)化只查詢需要的字段SELECT *是萬(wàn)惡之源。明確列出需要的字段減少網(wǎng)絡(luò)傳輸和內(nèi)存占用。使用只讀事務(wù)對(duì)于純粹的導(dǎo)出查詢可以在Spring事務(wù)中設(shè)置只讀屬性Transactional(readOnly true)。這會(huì)給數(shù)據(jù)庫(kù)一個(gè)提示可能觸發(fā)一些優(yōu)化。從庫(kù)查詢?nèi)绻麡I(yè)務(wù)允許將這類消耗資源的分析型、導(dǎo)出型查詢路由到只讀從庫(kù)避免影響主庫(kù)的OLTP事務(wù)性能。6. 常見(jiàn)問(wèn)題排查與實(shí)戰(zhàn)案例實(shí)錄這里記錄了幾個(gè)我在實(shí)際項(xiàng)目中遇到的典型問(wèn)題及其解決方案。6.1 問(wèn)題一遍歷Cursor時(shí)拋出“Connection is closed”異?，F(xiàn)象在for (User user : cursor)循環(huán)中處理到一部分?jǐn)?shù)據(jù)后突然拋出異常提示數(shù)據(jù)庫(kù)連接已關(guān)閉。根因分析缺少事務(wù)這是最常見(jiàn)的原因。沒(méi)有Transactional注解MyBatis可能在使用完一次連接后比如執(zhí)行完Mapper方法就將其歸還給連接池。當(dāng)遍歷Cursor需要再次讀取數(shù)據(jù)時(shí)使用的可能已經(jīng)是另一個(gè)連接。事務(wù)傳播行為不當(dāng)如果方法被另一個(gè)沒(méi)有事務(wù)的方法調(diào)用且事務(wù)傳播行為是REQUIRED默認(rèn)則不會(huì)開(kāi)啟新事務(wù)。需要檢查調(diào)用鏈。連接池超時(shí)連接池如Druid設(shè)置了removeAbandonedTimeout或idleTimeout長(zhǎng)時(shí)間未歸還的連接被強(qiáng)制回收。流式查詢耗時(shí)過(guò)長(zhǎng)觸發(fā)了這個(gè)機(jī)制。解決方案確保流式查詢的整個(gè)遍歷過(guò)程在一個(gè)Transactional方法內(nèi)。檢查并調(diào)大連接池的超時(shí)參數(shù)確保其大于流式查詢處理的最大預(yù)估時(shí)間。對(duì)于超長(zhǎng)任務(wù)考慮將連接池的testOnBorrow或validationQuery屬性打開(kāi)確保取出的連接是有效的。6.2 問(wèn)題二流式查詢速度比一次性查詢還慢現(xiàn)象改用Cursor后處理完所有數(shù)據(jù)的總時(shí)間反而變長(zhǎng)了。根因分析網(wǎng)絡(luò)往返Round-Trip開(kāi)銷如果fetchSize設(shè)置過(guò)小比如默認(rèn)是1每獲取一條記錄都需要一次網(wǎng)絡(luò)通信延遲成為主要瓶頸。數(shù)據(jù)庫(kù)端游標(biāo)開(kāi)銷保持游標(biāo)打開(kāi)本身對(duì)數(shù)據(jù)庫(kù)有一定資源消耗特別是當(dāng)有大量并發(fā)流式查詢時(shí)。客戶端處理邏輯過(guò)重如果每處理一條記錄都要進(jìn)行復(fù)雜的計(jì)算或遠(yuǎn)程調(diào)用那么I/O等待時(shí)間會(huì)掩蓋流式獲取的優(yōu)勢(shì)。解決方案調(diào)整fetchSize根據(jù)網(wǎng)絡(luò)狀況調(diào)整。在局域網(wǎng)內(nèi)可以設(shè)置為1000甚至更大。使用useCursorFetchtrueMySQL并設(shè)置一個(gè)合適的正數(shù)fetchSize。應(yīng)用層批處理如前所述積累一定數(shù)量如1000條再批量處理減少I/O次數(shù)。優(yōu)化SQL和索引確保查詢本身是高效的。流式解決的是內(nèi)存問(wèn)題不解決慢查詢問(wèn)題。6.3 問(wèn)題三內(nèi)存使用仍然很高現(xiàn)象使用了Cursor但通過(guò)監(jiān)控發(fā)現(xiàn)JVM堆內(nèi)存使用率依然在持續(xù)上升。根因分析內(nèi)存泄漏在遍歷Cursor時(shí)無(wú)意中將處理的對(duì)象添加到了某個(gè)全局集合如Map、List中導(dǎo)致所有對(duì)象都無(wú)法被GC回收。大對(duì)象駐留處理的單條記錄中包含大字段如長(zhǎng)文本、Base64圖片即使只存在一條在內(nèi)存中也可能占用很大空間?？蚣芑蝌?qū)動(dòng)緩存某些ORM框架或JDBC驅(qū)動(dòng)可能有內(nèi)部緩存機(jī)制。排查與解決使用jmap或VisualVM等工具做堆轉(zhuǎn)儲(chǔ)分析查看內(nèi)存中數(shù)量最多的對(duì)象是什么。審查處理邏輯確保處理完的對(duì)象引用被及時(shí)清除。對(duì)于大字段考慮在SQL中不查詢它們或者使用數(shù)據(jù)庫(kù)特定的流式API來(lái)分段讀取。6.4 一個(gè)完整的千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出案例需求將過(guò)去一年超過(guò)2000萬(wàn)的用戶訂單數(shù)據(jù)導(dǎo)出為CSV文件。技術(shù)棧Spring Boot MyBatis MySQL HikariCP實(shí)現(xiàn)步驟Mapper定義public interface OrderMapper { CursorOrderExportDTO streamOrdersForExport(Param(startDate) LocalDate startDate, Param(endDate) LocalDate endDate); }select idstreamOrdersForExport resultTypeOrderExportDTO fetchSize-2147483648 SELECT order_id, user_id, amount, status, create_time FROM orders WHERE create_time BETWEEN #{startDate} AND #{endDate} ORDER BY order_id ASC !-- 按主鍵排序保證順序且利于數(shù)據(jù)庫(kù)掃描 -- /selectService層Service Slf4j public class OrderExportService { private static final int BATCH_SIZE 2000; Transactional(readOnly true, timeout 7200) // 只讀事務(wù)2小時(shí)超時(shí) public void exportOrdersToCsv(LocalDate startDate, LocalDate endDate, Path outputPath) throws IOException { long start System.currentTimeMillis(); try (BufferedWriter writer Files.newBufferedWriter(outputPath, StandardCharsets.UTF_8); CSVPrinter csvPrinter new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(HEADERS)); CursorOrderExportDTO cursor orderMapper.streamOrdersForExport(startDate, endDate)) { ListOrderExportDTO batch new ArrayList(BATCH_SIZE); for (OrderExportDTO order : cursor) { batch.add(order); if (batch.size() BATCH_SIZE) { writeBatchToCsv(csvPrinter, batch); batch.clear(); csvPrinter.flush(); // 定期刷新緩沖區(qū)到磁盤 } } // 處理剩余數(shù)據(jù) if (!batch.isEmpty()) { writeBatchToCsv(csvPrinter, batch); } csvPrinter.flush(); } long duration (System.currentTimeMillis() - start) / 1000; log.info(訂單導(dǎo)出完成耗時(shí): {} 秒, duration); } private void writeBatchToCsv(CSVPrinter printer, ListOrderExportDTO batch) throws IOException { for (OrderExportDTO order : batch) { printer.printRecord( order.getOrderId(), order.getUserId(), order.getAmount(), order.getStatus(), order.getCreateTime() ); } } }關(guān)鍵配置application.ymlspring: datasource: hikari: maximum-pool-size: 20 connection-timeout: 30000 idle-timeout: 600000 # 10分鐘確保長(zhǎng)事務(wù)連接不被回收 max-lifetime: 1800000 # 30分鐘 url: jdbc:mysql://localhost:3306/order_db?useCursorFetchtrueserverTimezoneAsia/Shanghai mybatis: configuration: default-fetch-size: -2147483648 # 全局設(shè)置流式獲取監(jiān)控與告警在導(dǎo)出服務(wù)中集成Metrics記錄導(dǎo)出速率行/秒、內(nèi)存使用情況并設(shè)置耗時(shí)過(guò)長(zhǎng)或內(nèi)存異常的告警。通過(guò)這套方案我們成功將單次導(dǎo)出2000萬(wàn)條訂單數(shù)據(jù)的內(nèi)存占用從預(yù)期的數(shù)十GB如果全量加載降低到穩(wěn)定的幾百M(fèi)B批處理緩沖區(qū)任務(wù)總耗時(shí)在可控范圍內(nèi)且對(duì)數(shù)據(jù)庫(kù)主庫(kù)的影響降到了最低。 MyBatis流式查詢實(shí)戰(zhàn)：千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出與性能優(yōu)化指南
1. 項(xiàng)目概述當(dāng)MyBatis遇上千萬(wàn)級(jí)數(shù)據(jù)做后端開(kāi)發(fā)尤其是處理數(shù)據(jù)報(bào)表、數(shù)據(jù)導(dǎo)出或者大數(shù)據(jù)量分析的場(chǎng)景你肯定遇到過(guò)這樣的頭疼時(shí)刻一個(gè)查詢需要返回幾十萬(wàn)甚至上百萬(wàn)條記錄。如果直接用傳統(tǒng)的ListT一次性加載到內(nèi)存輕則接口響應(yīng)緩慢內(nèi)存飆升重則直接OutOfMemoryError服務(wù)掛掉。這時(shí)候MyBatis的流式查詢Streaming Query就成了你的救命稻草。它允許你像打開(kāi)一個(gè)水龍頭一樣從數(shù)據(jù)庫(kù)里“流式”地、一條一條地獲取數(shù)據(jù)而不是把整桶水都先搬到內(nèi)存里。今天我就結(jié)合自己處理千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出的實(shí)戰(zhàn)經(jīng)驗(yàn)來(lái)徹底拆解MyBatis流式查詢的原理、實(shí)現(xiàn)、坑點(diǎn)以及最佳實(shí)踐。無(wú)論你是想優(yōu)化現(xiàn)有的大數(shù)據(jù)查詢接口還是為即將到來(lái)的海量數(shù)據(jù)處理做準(zhǔn)備這篇內(nèi)容都能給你一套可直接落地的方案。2. 流式查詢的核心原理與為什么需要它2.1 傳統(tǒng)查詢的瓶頸全量加載之痛在深入流式查詢之前我們必須先搞清楚傳統(tǒng)方式為什么不行。當(dāng)我們執(zhí)行一個(gè)典型的MyBatis查詢例如select idselectLargeData resultTypecom.example.User SELECT id, name, email FROM user WHERE create_time #{startTime} /select對(duì)應(yīng)的Mapper接口方法返回一個(gè)ListUser。MyBatis或者說(shuō)底層的JDBC驅(qū)動(dòng)在執(zhí)行這個(gè)查詢時(shí)其默認(rèn)行為是一次性將所有匹配的結(jié)果集從數(shù)據(jù)庫(kù)服務(wù)器通過(guò)網(wǎng)絡(luò)傳輸?shù)綉?yīng)用服務(wù)器的內(nèi)存中并封裝成完整的List對(duì)象。這個(gè)過(guò)程存在幾個(gè)致命問(wèn)題內(nèi)存壓力假設(shè)一條User記錄在內(nèi)存中占用1KB1000萬(wàn)條數(shù)據(jù)就是約10GB。JVM堆內(nèi)存很可能無(wú)法容納直接導(dǎo)致OOM。網(wǎng)絡(luò)與數(shù)據(jù)庫(kù)壓力數(shù)據(jù)庫(kù)需要一次性準(zhǔn)備并發(fā)送整個(gè)結(jié)果集這期間會(huì)長(zhǎng)時(shí)間占用數(shù)據(jù)庫(kù)連接和網(wǎng)絡(luò)帶寬可能導(dǎo)致數(shù)據(jù)庫(kù)響應(yīng)變慢影響其他查詢。響應(yīng)延遲應(yīng)用必須等待所有數(shù)據(jù)都傳輸、反序列化完成后才能開(kāi)始處理并返回給客戶端。用戶會(huì)經(jīng)歷漫長(zhǎng)的等待體驗(yàn)極差。2.2 流式查詢的工作機(jī)制細(xì)水長(zhǎng)流流式查詢改變了這個(gè)范式。它的核心思想是保持?jǐn)?shù)據(jù)庫(kù)游標(biāo)Cursor打開(kāi)然后讓應(yīng)用像迭代器Iterator一樣一次從游標(biāo)中獲取一條或一小批記錄進(jìn)行處理處理完一條就丟棄一條或批量處理內(nèi)存中始終只保持少量數(shù)據(jù)。其背后的技術(shù)棧是JDBC層面通過(guò)Statement.setFetchSize(Integer.MIN_VALUE)MySQL驅(qū)動(dòng)或使用ResultSet.TYPE_FORWARD_ONLY和CONCUR_READ_ONLY模式并設(shè)置合適的fetchSize來(lái)告訴驅(qū)動(dòng)我們想要流式獲取結(jié)果。MyBatis層面提供了CursorT接口作為流式查詢的返回類型。Cursor實(shí)現(xiàn)了IterableT和IteratorT你可以像遍歷普通集合一樣遍歷它但每次next()調(diào)用才會(huì)驅(qū)動(dòng)JDBC從網(wǎng)絡(luò)連接中獲取下一條數(shù)據(jù)。數(shù)據(jù)庫(kù)層面以MySQL為例當(dāng)使用流式結(jié)果集時(shí)數(shù)據(jù)庫(kù)服務(wù)器會(huì)保持結(jié)果集和相關(guān)的連接、資源處于打開(kāi)狀態(tài)等待客戶端逐條請(qǐng)求數(shù)據(jù)。關(guān)鍵區(qū)別類比傳統(tǒng)查詢就像點(diǎn)外賣餐廳數(shù)據(jù)庫(kù)必須把所有菜數(shù)據(jù)都做好、打包好騎手網(wǎng)絡(luò)一次性全部送到你家內(nèi)存你才能開(kāi)始吃。流式查詢就像吃回轉(zhuǎn)壽司廚師數(shù)據(jù)庫(kù)不斷地把做好的壽司數(shù)據(jù)放在傳送帶連接上你應(yīng)用坐在旁邊看到想吃需要處理的就拿下來(lái)吃完盤子就被收走內(nèi)存釋放。你永遠(yuǎn)不需要同時(shí)擁有所有的壽司。2.3 哪些場(chǎng)景必須使用流式查詢不是所有查詢都需要流式。引入流式查詢會(huì)帶來(lái)額外的復(fù)雜性如事務(wù)和連接管理。判斷標(biāo)準(zhǔn)很簡(jiǎn)單數(shù)據(jù)量極大無(wú)法一次性裝入內(nèi)存這是最直接的信號(hào)。當(dāng)你預(yù)計(jì)查詢結(jié)果在數(shù)萬(wàn)條以上且每條記錄字段較多、體積較大時(shí)就應(yīng)該考慮流式。需要逐條或分批處理且處理邏輯可獨(dú)立例如數(shù)據(jù)導(dǎo)出為CSV/Excel文件、數(shù)據(jù)清洗后寫入另一個(gè)存儲(chǔ)系統(tǒng)如Elasticsearch、另一個(gè)數(shù)據(jù)庫(kù)、實(shí)時(shí)計(jì)算統(tǒng)計(jì)指標(biāo)等。處理完的數(shù)據(jù)可以立即丟棄或轉(zhuǎn)移。需要提供實(shí)時(shí)或漸進(jìn)式響應(yīng)比如一個(gè)大型報(bào)表生成你可以邊查詢邊生成文件并即時(shí)提供下載鏈接或者通過(guò)WebSocket分批推送數(shù)據(jù)到前端提升用戶體驗(yàn)。注意流式查詢并非為了提升“查詢速度”。實(shí)際上由于需要保持連接和游標(biāo)整個(gè)處理過(guò)程的總耗時(shí)可能比一次性獲取更長(zhǎng)。它的核心價(jià)值在于用時(shí)間換空間以及提供漸進(jìn)式處理的能力避免內(nèi)存瓶頸。3. MyBatis流式查詢的三種實(shí)現(xiàn)方式與選型MyBatis提供了不止一種方式來(lái)實(shí)現(xiàn)流式查詢每種方式各有優(yōu)劣和適用場(chǎng)景。理解它們之間的區(qū)別是正確選型的關(guān)鍵。3.1 方式一使用CursorT接口推薦這是MyBatis官方最直接、最現(xiàn)代的支持方式。你只需要將Mapper方法的返回值定義為CursorT類型。定義Mapper接口import org.apache.ibatis.cursor.Cursor; public interface UserMapper { CursorUser selectLargeDataStream(Param(startTime) Date startTime); }編寫XML映射select idselectLargeDataStream resultTypecom.example.User SELECT id, name, email, create_time FROM user WHERE create_time #{startTime} ORDER BY id !-- 流式查詢強(qiáng)烈建議排序保證順序和可重復(fù)性 -- /select服務(wù)層調(diào)用與遍歷Service Transactional // 事務(wù)至關(guān)重要 public class DataExportService { Autowired private UserMapper userMapper; public void exportLargeData(Date startTime, OutputStream outputStream) { try (CursorUser cursor userMapper.selectLargeDataStream(startTime)) { CSVWriter writer new CSVWriter(new OutputStreamWriter(outputStream)); // 寫入表頭 writer.writeNext(new String[]{ID, Name, Email, Create Time}); for (User user : cursor) { // 這里開(kāi)始逐條遍歷觸發(fā)數(shù)據(jù)獲取 // 處理每條數(shù)據(jù)例如寫入CSV writer.writeNext(new String[]{ String.valueOf(user.getId()), user.getName(), user.getEmail(), user.getCreateTime().toString() }); // 可選每處理1000條刷新一次輸出流避免內(nèi)存堆積 if (cursor.getCurrentIndex() % 1000 0) { writer.flush(); } } writer.flush(); } catch (IOException e) { throw new RuntimeException(導(dǎo)出失敗, e); } // Cursor在try-with-resources中會(huì)自動(dòng)關(guān)閉確保資源釋放 } }為什么推薦這種方式語(yǔ)義清晰CursorT類型明確表達(dá)了“這是一個(gè)流式查詢”。資源管理方便Cursor實(shí)現(xiàn)了AutoCloseable配合try-with-resources語(yǔ)法可以確保數(shù)據(jù)庫(kù)游標(biāo)和連接被正確關(guān)閉避免資源泄漏。與Spring事務(wù)集成好在Transactional注解的方法內(nèi)使用可以確保在整個(gè)遍歷過(guò)程中數(shù)據(jù)庫(kù)連接和事務(wù)保持一致。3.2 方式二使用ResultHandler更底層控制ResultHandler是一個(gè)回調(diào)接口。MyBatis在從數(shù)據(jù)庫(kù)獲取到每一行結(jié)果時(shí)都會(huì)調(diào)用這個(gè)接口的handleResult方法。這種方式將處理邏輯完全交給開(kāi)發(fā)者控制粒度最細(xì)。定義ResultHandlerimport org.apache.ibatis.session.ResultHandler; public class UserExportResultHandler implements ResultHandlerUser { private final CSVWriter writer; private int count 0; public UserExportResultHandler(CSVWriter writer) { this.writer writer; writer.writeNext(new String[]{ID, Name, Email}); } Override public void handleResult(ResultContext? extends User resultContext) { User user resultContext.getResultObject(); // 處理單條記錄 writer.writeNext(new String[]{ String.valueOf(user.getId()), user.getName(), user.getEmail() }); count; if (count % 1000 0) { writer.flush(); } // 你甚至可以根據(jù)條件停止處理 // if (count 10000) { // resultContext.stop(); // } } public int getCount() { return count; } }Mapper接口和XML定義Mapper接口方法返回值為void并增加ResultHandler參數(shù)。public interface UserMapper { void selectLargeDataWithHandler(Param(startTime) Date startTime, ResultHandlerUser handler); }XML映射文件不需要特殊改動(dòng)和普通查詢一樣。服務(wù)層調(diào)用Service Transactional public class DataExportServiceV2 { Autowired private UserMapper userMapper; public void exportLargeData(Date startTime, OutputStream outputStream) throws IOException { try (CSVWriter writer new CSVWriter(new OutputStreamWriter(outputStream))) { UserExportResultHandler handler new UserExportResultHandler(writer); // 執(zhí)行查詢結(jié)果將通過(guò)handler處理 userMapper.selectLargeDataWithHandler(startTime, handler); writer.flush(); System.out.println(共處理數(shù)據(jù): handler.getCount() 條); } } }適用場(chǎng)景與注意事項(xiàng)優(yōu)點(diǎn)絕對(duì)的控制權(quán)可以在處理每條數(shù)據(jù)時(shí)做任何事甚至可以中途停止resultContext.stop()。缺點(diǎn)代碼更復(fù)雜需要自己創(chuàng)建和管理ResultHandler實(shí)例。資源關(guān)閉的邏輯也需要更小心主要關(guān)閉SqlSession。適用當(dāng)你需要對(duì)結(jié)果集進(jìn)行非常復(fù)雜的、有狀態(tài)的逐行處理時(shí)。3.3 方式三自定義ExecutorType為REUSE或BATCH誤區(qū)澄清網(wǎng)上有些資料會(huì)提到在SqlSession上設(shè)置ExecutorType為REUSE或BATCH來(lái)實(shí)現(xiàn)“流式”或“批量”效果。這里必須澄清一個(gè)常見(jiàn)的誤區(qū)ExecutorType.SIMPLE默認(rèn)執(zhí)行器。每次執(zhí)行完語(yǔ)句就關(guān)閉Statement對(duì)象。ExecutorType.REUSE復(fù)用Statement對(duì)象。對(duì)于同一模式的SQL例如多次插入不同參數(shù)可以復(fù)用預(yù)編譯的Statement提升效率。但它不改變結(jié)果集的獲取方式。ExecutorType.BATCH批處理執(zhí)行器。將多個(gè)更新操作INSERT, UPDATE, DELETE攢在一起一次性發(fā)送給數(shù)據(jù)庫(kù)大幅提升批量寫入性能。它只針對(duì)更新語(yǔ)句對(duì)SELECT查詢無(wú)效。結(jié)論ExecutorType主要用于優(yōu)化寫入性能無(wú)法實(shí)現(xiàn)SELECT查詢的流式讀取。流式查詢的核心在于對(duì)ResultSet的處理方式而不是Statement的執(zhí)行方式。實(shí)現(xiàn)流式查詢必須依靠Cursor或ResultHandler或者在JDBC層面直接設(shè)置fetchSize。3.4 選型決策指南特性CursorT方式ResultHandler方式易用性高。符合Java迭代器習(xí)慣代碼簡(jiǎn)潔。中。需要實(shí)現(xiàn)回調(diào)接口代碼稍顯分散?？刂屏６戎??？梢灾饤l處理也能獲取當(dāng)前索引。高。可以訪問(wèn)ResultContext能中途停止、跳過(guò)。資源管理優(yōu)。支持try-with-resources自動(dòng)關(guān)閉。需注意。需要在正確的作用域內(nèi)確保SqlSession關(guān)閉。與Spring集成優(yōu)。在Transactional中工作良好。良。同樣需要事務(wù)上下文。推薦場(chǎng)景絕大多數(shù)流式查詢場(chǎng)景如數(shù)據(jù)導(dǎo)出、批量轉(zhuǎn)換。需要精細(xì)控制處理流程或提前終止的場(chǎng)景。對(duì)于90%的開(kāi)發(fā)者首選CursorT方式。它平衡了易用性、安全性和功能性。4. 流式查詢的實(shí)戰(zhàn)配置、陷阱與深度優(yōu)化知道怎么用只是第一步用得好、不出錯(cuò)才是關(guān)鍵。這部分是真正的干貨來(lái)自大量實(shí)戰(zhàn)踩坑后的總結(jié)。4.1 強(qiáng)制要求事務(wù)管理與連接持有這是流式查詢最核心、也最容易出錯(cuò)的地方。流式查詢的本質(zhì)是保持一個(gè)數(shù)據(jù)庫(kù)游標(biāo)打開(kāi)。而游標(biāo)是依附于數(shù)據(jù)庫(kù)連接Connection和事務(wù)Transaction的。錯(cuò)誤示范// 沒(méi)有事務(wù)注解 public void exportData() { CursorUser cursor userMapper.selectLargeDataStream(...); // 遍歷cursor... // 問(wèn)題方法執(zhí)行過(guò)程中MyBatis可能會(huì)在每次cursor.next()時(shí)從連接池獲取新連接 // 導(dǎo)致游標(biāo)所在的連接被關(guān)閉拋出 Connection is closed 異常。 }正確做法必須確保整個(gè)遍歷過(guò)程在一個(gè)數(shù)據(jù)庫(kù)事務(wù)內(nèi)從而保證始終使用同一個(gè)物理連接。Service public class ExportService { Transactional // 關(guān)鍵確保方法在一個(gè)事務(wù)內(nèi)執(zhí)行 public void exportWithTransaction() { try (CursorUser cursor mapper.selectLargeDataStream(...)) { for (User u : cursor) { // 處理數(shù)據(jù) } } } }為什么Transactional會(huì)為這個(gè)方法創(chuàng)建一個(gè)事務(wù)上下文。Spring會(huì)為此上下文綁定一個(gè)獨(dú)立的數(shù)據(jù)庫(kù)連接。在整個(gè)方法執(zhí)行期間所有數(shù)據(jù)庫(kù)操作包括Cursor的遍歷都使用這個(gè)連接游標(biāo)得以保持。連接池注意事項(xiàng)常用的連接池如HikariCP、Druid都有連接回收機(jī)制。如果沒(méi)有事務(wù)保護(hù)連接可能在Cursor未關(guān)閉時(shí)就被回收到池中造成狀態(tài)混亂。事務(wù)阻止了連接被提前歸還。4.2 數(shù)據(jù)庫(kù)驅(qū)動(dòng)與FetchSize的奧秘流式查詢的行為高度依賴于JDBC驅(qū)動(dòng)的實(shí)現(xiàn)。不同數(shù)據(jù)庫(kù)、不同驅(qū)動(dòng)版本配置可能不同。1. MySQL (mysql-connector-java)經(jīng)典方式Statement.setFetchSize(Integer.MIN_VALUE)。這是告訴MySQL驅(qū)動(dòng)使用流式結(jié)果集的“魔法值”。在MyBatis中可以通過(guò)在Mapper XML的select標(biāo)簽里配置fetchSize屬性來(lái)實(shí)現(xiàn)。select idselectLargeDataStream fetchSize-2147483648 resultType... SELECT ... /select驅(qū)動(dòng)版本的影響在較新的驅(qū)動(dòng)版本如8.x中僅設(shè)置fetchSize為負(fù)值可能還不夠。你可能還需要在JDBC連接字符串中顯式指定使用流式讀取spring.datasource.urljdbc:mysql://localhost:3306/db?useCursorFetchtrue設(shè)置useCursorFetchtrue后fetchSize的正值表示每次從服務(wù)器獲取的行數(shù)實(shí)現(xiàn)了“客戶端游標(biāo)”式的分批流式獲取對(duì)服務(wù)器更友好。2. PostgreSQLPostgreSQL的驅(qū)動(dòng)對(duì)流式支持很好。通常只需要設(shè)置一個(gè)合理的正數(shù)fetchSize即可。select idselectLargeDataStream fetchSize1000 resultType... SELECT ... /select這里fetchSize1000意味著每次網(wǎng)絡(luò)往返從服務(wù)器獲取1000條記錄。這是一個(gè)平衡內(nèi)存和網(wǎng)絡(luò)開(kāi)銷的常用值。3. OracleOracle JDBC驅(qū)動(dòng)默認(rèn)就是流式的fetchSize默認(rèn)是10。對(duì)于海量數(shù)據(jù)你可以根據(jù)情況調(diào)大fetchSize比如5000來(lái)減少網(wǎng)絡(luò)通信次數(shù)但要注意客戶端內(nèi)存。實(shí)操心得fetchSize沒(méi)有銀彈。Integer.MIN_VALUEMySQL流式或一個(gè)較小的正數(shù)如1000是安全的起點(diǎn)。對(duì)于超大數(shù)據(jù)量可以嘗試調(diào)大fetchSize以減少網(wǎng)絡(luò)延遲的影響但務(wù)必在測(cè)試環(huán)境中監(jiān)控客戶端內(nèi)存使用。一定要查閱你所使用數(shù)據(jù)庫(kù)驅(qū)動(dòng)的最新官方文檔。4.3 SQL語(yǔ)句的編寫禁忌不是所有SQL都適合流式查詢。必須排序ORDER BY流式處理通常意味著順序處理。如果沒(méi)有ORDER BY數(shù)據(jù)庫(kù)可能以任意順序返回?cái)?shù)據(jù)。在多批次處理或中斷重試時(shí)可能導(dǎo)致數(shù)據(jù)重復(fù)或丟失。強(qiáng)烈建議使用一個(gè)唯一或遞增的字段如主鍵ID、創(chuàng)建時(shí)間進(jìn)行排序。避免大字段BLOB, TEXT, CLOB流式查詢解決的是“行數(shù)多”的問(wèn)題而不是“單行數(shù)據(jù)大”的問(wèn)題。如果單行記錄包含一個(gè)幾十MB的BLOB字段即使只流式獲取一行也可能撐爆內(nèi)存。對(duì)于包含大字段的表考慮分兩次查詢或者使用數(shù)據(jù)庫(kù)特定的流式讀取大對(duì)象API。使用覆蓋索引確保你的WHERE條件和ORDER BY字段能被索引覆蓋。流式查詢雖然減輕了客戶端壓力但數(shù)據(jù)庫(kù)服務(wù)器仍然需要執(zhí)行完整的查詢。一個(gè)全表掃描的流式查詢對(duì)數(shù)據(jù)庫(kù)同樣是災(zāi)難。使用EXPLAIN分析你的SQL。4.4 資源泄漏你必須關(guān)閉CursorCursor背后是打開(kāi)的數(shù)據(jù)庫(kù)ResultSet和Statement。如果不關(guān)閉就會(huì)導(dǎo)致數(shù)據(jù)庫(kù)游標(biāo)泄漏消耗服務(wù)器資源。數(shù)據(jù)庫(kù)連接無(wú)法及時(shí)釋放回連接池可能導(dǎo)致連接池耗盡。關(guān)閉的最佳實(shí)踐// 正確做法1: try-with-resources (Java 7) try (CursorUser cursor userMapper.selectLargeDataStream(...)) { for (User user : cursor) { // process } } // 無(wú)論是否異常cursor都會(huì)自動(dòng)關(guān)閉 // 正確做法2: 在finally塊中手動(dòng)關(guān)閉 CursorUser cursor null; try { cursor userMapper.selectLargeDataStream(...); // ... 遍歷處理 } finally { if (cursor ! null !cursor.isClosed()) { cursor.close(); } }絕對(duì)不要在遍歷到一半時(shí)直接return而不關(guān)閉Cursor。4.5 超時(shí)與中斷處理流式查詢可能運(yùn)行很長(zhǎng)時(shí)間。你需要考慮超時(shí)和用戶中斷。查詢超時(shí)可以在MyBatis的select標(biāo)簽中設(shè)置timeout屬性單位秒或者在數(shù)據(jù)源連接字符串中配置socketTimeout。select idselectLargeDataStream timeout300 ... !-- 5分鐘超時(shí) --事務(wù)超時(shí)如果你使用了Spring的Transactional可以設(shè)置事務(wù)超時(shí)Transactional(timeout 300)。注意這個(gè)超時(shí)是從事務(wù)開(kāi)始算起如果事務(wù)中還做了其他操作需要留有余地。用戶中斷在Web應(yīng)用中如果用戶取消了導(dǎo)出請(qǐng)求你需要有能力停止正在進(jìn)行的流式查詢。這通常需要將Cursor的遍歷放在一個(gè)可中斷的線程中。提供一個(gè)取消接口該接口設(shè)置一個(gè)中斷標(biāo)志。在遍歷循環(huán)中定期檢查這個(gè)中斷標(biāo)志如果被中斷則調(diào)用cursor.close()并退出。 這是一個(gè)相對(duì)高級(jí)的特性需要結(jié)合具體的應(yīng)用框架如Spring MVC的DeferredResult來(lái)實(shí)現(xiàn)。5. 性能調(diào)優(yōu)與監(jiān)控讓千萬(wàn)級(jí)查詢飛起來(lái)處理千萬(wàn)級(jí)數(shù)據(jù)光有流式查詢還不夠需要一套組合拳。5.1 分頁(yè) vs 流式查詢?nèi)绾芜x擇很多人面對(duì)大數(shù)據(jù)查詢第一反應(yīng)是“分頁(yè)”。但分頁(yè)在處理超大數(shù)據(jù)量時(shí)存在嚴(yán)重問(wèn)題深度分頁(yè)性能極差LIMIT 1000000, 100這種查詢數(shù)據(jù)庫(kù)需要先掃描并跳過(guò)前100萬(wàn)條記錄成本極高。數(shù)據(jù)一致性風(fēng)險(xiǎn)如果數(shù)據(jù)在分頁(yè)過(guò)程中被增刪可能導(dǎo)致某一頁(yè)數(shù)據(jù)重復(fù)或丟失。決策指南使用流式查詢當(dāng)你的目的是處理全部數(shù)據(jù)如導(dǎo)出、ETL、計(jì)算總和且不需要將全部數(shù)據(jù)同時(shí)呈現(xiàn)給用戶時(shí)。使用分頁(yè)當(dāng)你的目的是在UI上展示數(shù)據(jù)且用戶只需要瀏覽其中一部分時(shí)。對(duì)于深度分頁(yè)應(yīng)使用“游標(biāo)分頁(yè)”或“seek method”即WHERE id last_id LIMIT 100利用索引避免偏移。兩者結(jié)合有時(shí)可以先用流式查詢處理數(shù)據(jù)將處理結(jié)果如聚合后的統(tǒng)計(jì)信息、生成的文件存儲(chǔ)起來(lái)再通過(guò)分頁(yè)提供給用戶查看。這是非常成熟的架構(gòu)模式。5.2 應(yīng)用層批處理減少I/O開(kāi)銷即使使用流式查詢逐條獲取如果逐條寫入文件或調(diào)用遠(yuǎn)程接口I/O效率也會(huì)極低。優(yōu)化在應(yīng)用層做批處理。try (CursorUser cursor userMapper.selectLargeDataStream(...)) { ListUser buffer new ArrayList(BATCH_SIZE); // 例如 BATCH_SIZE 1000 for (User user : cursor) { buffer.add(user); if (buffer.size() BATCH_SIZE) { // 批量處理寫入文件、插入ES、發(fā)送消息等 batchWriteToCSV(buffer, writer); buffer.clear(); writer.flush(); // 定期刷新輸出流 } } // 處理最后一批不滿 BATCH_SIZE 的數(shù)據(jù) if (!buffer.isEmpty()) { batchWriteToCSV(buffer, writer); } }通過(guò)內(nèi)存緩沖區(qū)積累一定數(shù)量的記錄后再進(jìn)行批量I/O操作可以大幅減少系統(tǒng)調(diào)用或網(wǎng)絡(luò)請(qǐng)求的次數(shù)提升整體吞吐量。5.3 JVM內(nèi)存與GC優(yōu)化流式查詢的目標(biāo)是降低內(nèi)存壓力但如果處理邏輯不當(dāng)仍然可能引起GC問(wèn)題。避免在遍歷中積累數(shù)據(jù)最忌諱在遍歷Cursor時(shí)又將所有數(shù)據(jù)添加到一個(gè)新的ArrayList中這就失去了流式的意義。及時(shí)釋放對(duì)象引用對(duì)于每一條處理完的記錄確保沒(méi)有全局的或長(zhǎng)時(shí)間存活的對(duì)象引用它。讓垃圾回收器可以及時(shí)回收。調(diào)整JVM參數(shù)雖然流式查詢降低了堆內(nèi)存需求但頻繁創(chuàng)建和丟棄大量短期對(duì)象User對(duì)象可能加劇Young GC?？梢赃m當(dāng)調(diào)整新生代大小-Xmn并考慮使用G1或ZGC這類低延遲垃圾收集器來(lái)應(yīng)對(duì)這種“高分配速率”的場(chǎng)景。5.4 數(shù)據(jù)庫(kù)層面的配合優(yōu)化只查詢需要的字段SELECT *是萬(wàn)惡之源。明確列出需要的字段減少網(wǎng)絡(luò)傳輸和內(nèi)存占用。使用只讀事務(wù)對(duì)于純粹的導(dǎo)出查詢可以在Spring事務(wù)中設(shè)置只讀屬性Transactional(readOnly true)。這會(huì)給數(shù)據(jù)庫(kù)一個(gè)提示可能觸發(fā)一些優(yōu)化。從庫(kù)查詢?nèi)绻麡I(yè)務(wù)允許將這類消耗資源的分析型、導(dǎo)出型查詢路由到只讀從庫(kù)避免影響主庫(kù)的OLTP事務(wù)性能。6. 常見(jiàn)問(wèn)題排查與實(shí)戰(zhàn)案例實(shí)錄這里記錄了幾個(gè)我在實(shí)際項(xiàng)目中遇到的典型問(wèn)題及其解決方案。6.1 問(wèn)題一遍歷Cursor時(shí)拋出“Connection is closed”異?，F(xiàn)象在for (User user : cursor)循環(huán)中處理到一部分?jǐn)?shù)據(jù)后突然拋出異常提示數(shù)據(jù)庫(kù)連接已關(guān)閉。根因分析缺少事務(wù)這是最常見(jiàn)的原因。沒(méi)有Transactional注解MyBatis可能在使用完一次連接后比如執(zhí)行完Mapper方法就將其歸還給連接池。當(dāng)遍歷Cursor需要再次讀取數(shù)據(jù)時(shí)使用的可能已經(jīng)是另一個(gè)連接。事務(wù)傳播行為不當(dāng)如果方法被另一個(gè)沒(méi)有事務(wù)的方法調(diào)用且事務(wù)傳播行為是REQUIRED默認(rèn)則不會(huì)開(kāi)啟新事務(wù)。需要檢查調(diào)用鏈。連接池超時(shí)連接池如Druid設(shè)置了removeAbandonedTimeout或idleTimeout長(zhǎng)時(shí)間未歸還的連接被強(qiáng)制回收。流式查詢耗時(shí)過(guò)長(zhǎng)觸發(fā)了這個(gè)機(jī)制。解決方案確保流式查詢的整個(gè)遍歷過(guò)程在一個(gè)Transactional方法內(nèi)。檢查并調(diào)大連接池的超時(shí)參數(shù)確保其大于流式查詢處理的最大預(yù)估時(shí)間。對(duì)于超長(zhǎng)任務(wù)考慮將連接池的testOnBorrow或validationQuery屬性打開(kāi)確保取出的連接是有效的。6.2 問(wèn)題二流式查詢速度比一次性查詢還慢現(xiàn)象改用Cursor后處理完所有數(shù)據(jù)的總時(shí)間反而變長(zhǎng)了。根因分析網(wǎng)絡(luò)往返Round-Trip開(kāi)銷如果fetchSize設(shè)置過(guò)小比如默認(rèn)是1每獲取一條記錄都需要一次網(wǎng)絡(luò)通信延遲成為主要瓶頸。數(shù)據(jù)庫(kù)端游標(biāo)開(kāi)銷保持游標(biāo)打開(kāi)本身對(duì)數(shù)據(jù)庫(kù)有一定資源消耗特別是當(dāng)有大量并發(fā)流式查詢時(shí)。客戶端處理邏輯過(guò)重如果每處理一條記錄都要進(jìn)行復(fù)雜的計(jì)算或遠(yuǎn)程調(diào)用那么I/O等待時(shí)間會(huì)掩蓋流式獲取的優(yōu)勢(shì)。解決方案調(diào)整fetchSize根據(jù)網(wǎng)絡(luò)狀況調(diào)整。在局域網(wǎng)內(nèi)可以設(shè)置為1000甚至更大。使用useCursorFetchtrueMySQL并設(shè)置一個(gè)合適的正數(shù)fetchSize。應(yīng)用層批處理如前所述積累一定數(shù)量如1000條再批量處理減少I/O次數(shù)。優(yōu)化SQL和索引確保查詢本身是高效的。流式解決的是內(nèi)存問(wèn)題不解決慢查詢問(wèn)題。6.3 問(wèn)題三內(nèi)存使用仍然很高現(xiàn)象使用了Cursor但通過(guò)監(jiān)控發(fā)現(xiàn)JVM堆內(nèi)存使用率依然在持續(xù)上升。根因分析內(nèi)存泄漏在遍歷Cursor時(shí)無(wú)意中將處理的對(duì)象添加到了某個(gè)全局集合如Map、List中導(dǎo)致所有對(duì)象都無(wú)法被GC回收。大對(duì)象駐留處理的單條記錄中包含大字段如長(zhǎng)文本、Base64圖片即使只存在一條在內(nèi)存中也可能占用很大空間?？蚣芑蝌?qū)動(dòng)緩存某些ORM框架或JDBC驅(qū)動(dòng)可能有內(nèi)部緩存機(jī)制。排查與解決使用jmap或VisualVM等工具做堆轉(zhuǎn)儲(chǔ)分析查看內(nèi)存中數(shù)量最多的對(duì)象是什么。審查處理邏輯確保處理完的對(duì)象引用被及時(shí)清除。對(duì)于大字段考慮在SQL中不查詢它們或者使用數(shù)據(jù)庫(kù)特定的流式API來(lái)分段讀取。6.4 一個(gè)完整的千萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出案例需求將過(guò)去一年超過(guò)2000萬(wàn)的用戶訂單數(shù)據(jù)導(dǎo)出為CSV文件。技術(shù)棧Spring Boot MyBatis MySQL HikariCP實(shí)現(xiàn)步驟Mapper定義public interface OrderMapper { CursorOrderExportDTO streamOrdersForExport(Param(startDate) LocalDate startDate, Param(endDate) LocalDate endDate); }select idstreamOrdersForExport resultTypeOrderExportDTO fetchSize-2147483648 SELECT order_id, user_id, amount, status, create_time FROM orders WHERE create_time BETWEEN #{startDate} AND #{endDate} ORDER BY order_id ASC !-- 按主鍵排序保證順序且利于數(shù)據(jù)庫(kù)掃描 -- /selectService層Service Slf4j public class OrderExportService { private static final int BATCH_SIZE 2000; Transactional(readOnly true, timeout 7200) // 只讀事務(wù)2小時(shí)超時(shí) public void exportOrdersToCsv(LocalDate startDate, LocalDate endDate, Path outputPath) throws IOException { long start System.currentTimeMillis(); try (BufferedWriter writer Files.newBufferedWriter(outputPath, StandardCharsets.UTF_8); CSVPrinter csvPrinter new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(HEADERS)); CursorOrderExportDTO cursor orderMapper.streamOrdersForExport(startDate, endDate)) { ListOrderExportDTO batch new ArrayList(BATCH_SIZE); for (OrderExportDTO order : cursor) { batch.add(order); if (batch.size() BATCH_SIZE) { writeBatchToCsv(csvPrinter, batch); batch.clear(); csvPrinter.flush(); // 定期刷新緩沖區(qū)到磁盤 } } // 處理剩余數(shù)據(jù) if (!batch.isEmpty()) { writeBatchToCsv(csvPrinter, batch); } csvPrinter.flush(); } long duration (System.currentTimeMillis() - start) / 1000; log.info(訂單導(dǎo)出完成耗時(shí): {} 秒, duration); } private void writeBatchToCsv(CSVPrinter printer, ListOrderExportDTO batch) throws IOException { for (OrderExportDTO order : batch) { printer.printRecord( order.getOrderId(), order.getUserId(), order.getAmount(), order.getStatus(), order.getCreateTime() ); } } }關(guān)鍵配置application.ymlspring: datasource: hikari: maximum-pool-size: 20 connection-timeout: 30000 idle-timeout: 600000 # 10分鐘確保長(zhǎng)事務(wù)連接不被回收 max-lifetime: 1800000 # 30分鐘 url: jdbc:mysql://localhost:3306/order_db?useCursorFetchtrueserverTimezoneAsia/Shanghai mybatis: configuration: default-fetch-size: -2147483648 # 全局設(shè)置流式獲取監(jiān)控與告警在導(dǎo)出服務(wù)中集成Metrics記錄導(dǎo)出速率行/秒、內(nèi)存使用情況并設(shè)置耗時(shí)過(guò)長(zhǎng)或內(nèi)存異常的告警。通過(guò)這套方案我們成功將單次導(dǎo)出2000萬(wàn)條訂單數(shù)據(jù)的內(nèi)存占用從預(yù)期的數(shù)十GB如果全量加載降低到穩(wěn)定的幾百M(fèi)B批處理緩沖區(qū)任務(wù)總耗時(shí)在可控范圍內(nèi)且對(duì)數(shù)據(jù)庫(kù)主庫(kù)的影響降到了最低。