我見過太多網(wǎng)上帖子文章，上來就是各種方案，要換什么API，要加異步，要調(diào)內(nèi)存等各種手段。

看到這些我都有種小時(shí)候感冒鄉(xiāng)村醫(yī)生上來就是一堆藥的無奈感；至今沒見過誰是從真正項(xiàng)目場(chǎng)景出發(fā)去分析因果關(guān)系。

一切手段都是為了服務(wù)場(chǎng)景，服務(wù)用戶。

圖片

這題明面上問的是 Excel 導(dǎo)出優(yōu)化，實(shí)際上是在扒你對(duì)數(shù)據(jù)鏈路全流程（查詢 - 處理 - 生成 - 傳輸）的分析感知&處理能力 —— 大部分人一上來就盯著 Excel 生成本身死磕，根本不理解對(duì)癥下藥以及優(yōu)化過程。

我的風(fēng)格是總分，從問題場(chǎng)景由淺入深，先花2分鐘看下決策圖，再跟著我的思路來分析：

圖片

這是你寫的代碼？

先別急著說你有什么牛逼的方案，我們先從原始問題出發(fā)，你最先接手原始的代碼可能是這樣：前端點(diǎn)導(dǎo)出，后端接口直接查數(shù)據(jù)庫(kù)（可能還沒加索引），把所有數(shù)據(jù)一次性撈到內(nèi)存里，用 POI 同步生成 Excel 文件，最后通過 HTTP 響應(yīng)直接返回。

你是不是覺得這流程特順？如果是這樣，那你問題大了，我們逐步道來。

先說查庫(kù)，百萬條訂單導(dǎo)出掛？

如果導(dǎo)出的數(shù)據(jù)只有幾百條，這方案湊活用。但如果業(yè)務(wù)方說 “我要導(dǎo)出近一年的訂單，大概 100 萬條，甚至更多”，你這代碼會(huì)出啥問題？

首先，查庫(kù)就卡殼了 ——100 萬條數(shù)據(jù)一次性 select *，就算有索引，MySQL 把結(jié)果集拼裝好返回給應(yīng)用，這網(wǎng)絡(luò)傳輸和內(nèi)存占用就夠你喝一壺的。

我早年做報(bào)表系統(tǒng)時(shí)就踩過這坑，一次導(dǎo)出把應(yīng)用服務(wù)的 JVM 堆直接干到 GC 頻繁，最后 OOM 掛了一個(gè)節(jié)點(diǎn)，現(xiàn)在想起來都臉紅。

同步導(dǎo)出是 “連接池殺手”？

你真的以為把查庫(kù)優(yōu)化了（比如加索引、用 limit 分頁）就沒事了？好，就算你分頁查庫(kù)，每次查 1 萬條，100 次查完。

但接下來同步生成 Excel 的過程，還是在占用著當(dāng)前的 HTTP 連接 ——Tomcat 的連接池就那么大，要是同時(shí)有 10 個(gè)用戶這么導(dǎo)出，單機(jī)容量有限，連接池直接滿了，后面的正常請(qǐng)求全排隊(duì)，這服務(wù)不就等于半癱了？

說白了，同步模式下，導(dǎo)出任務(wù)就是個(gè) “連接池殺手”，把請(qǐng)求線程全占著干 “慢活”。

一堆資源都在浪費(fèi)沒有最大化利用，線上風(fēng)險(xiǎn)極高，隨時(shí)可能打爆你的服務(wù)，用戶體驗(yàn)也極差。

大文件傳輸 + 重復(fù)點(diǎn)擊何解？

就算你扛過了查庫(kù)和線程占用，生成的 Excel 文件要是有幾十 MB，問題又來了 ——HTTP 傳輸慢不說，前端等待超時(shí)怎么辦？

用戶點(diǎn)了導(dǎo)出，等了 5 分鐘沒反應(yīng)，以為沒成功，又點(diǎn)了好幾次，結(jié)果后端重復(fù)生成好幾個(gè)大文件，資源直接 double 浪費(fèi)。

此時(shí)你開始大概意識(shí)到問題是什么了，這個(gè)場(chǎng)景面對(duì)的是些什么問題，把你的思考過程抬上來再談解決方案，才是合格的RD，也是面試官喜歡的候選人。

優(yōu)化1：同步改異步

那咱開始優(yōu)化。首先要解決的不是 Excel，是 **“同步阻塞” 這個(gè)巨坑 **。怎么解？異步化。

把 “用戶觸發(fā)導(dǎo)出” 和 “Excel 生成” 拆成兩回事 —— 用戶點(diǎn)導(dǎo)出時(shí)，后端不直接處理，而是生成一個(gè) “導(dǎo)出任務(wù) ID”，扔到 MQ 里，然后立刻返回給前端 “任務(wù)已受理，請(qǐng)用 ID 查結(jié)果”。

前端拿著 ID 輪詢（或者后端直接生成完后通過消息助手通知用戶），等 MQ 消費(fèi)者把 Excel 生成完，再通知用戶下載。

圖片

當(dāng)然最好能利用mq分布式多機(jī)消費(fèi)的特性，將數(shù)據(jù)量進(jìn)行分批拆分，每臺(tái)機(jī)器處理一批或者一段，這樣就不會(huì)導(dǎo)致單機(jī)oom

這一步就把請(qǐng)求線程解放了，連接池再也不會(huì)被堵死。但這里要注意，異步不是一勞永逸的 —— 你得處理任務(wù)狀態(tài)（等待中 / 生成中 / 成功 / 失?。?；

還得考慮失敗重試（比如生成到一半 MQ 掛了怎么辦？），甚至要做任務(wù)限流，不能讓 1000 個(gè)用戶同時(shí)扔導(dǎo)出任務(wù)，把 MQ 和生成服務(wù)壓垮。

優(yōu)化2：分頁不夠，游標(biāo) + 數(shù)倉(cāng)來湊

解決了異步，再看數(shù)據(jù)查詢。分頁查庫(kù)是基礎(chǔ)，但有個(gè)坑：當(dāng)偏移量很大時(shí)（比如 limit 100000, 1000），MySQL 會(huì)掃描前面 10 萬條數(shù)據(jù)再跳過，這時(shí)候索引效率會(huì)驟降。

怎么辦？用 “游標(biāo)分頁”—— 比如按訂單 ID 排序，每次查的時(shí)候帶上上次的最大 ID（where id > last_id limit 1000），這樣索引一直有效。

另外，要是查庫(kù)涉及多表關(guān)聯(lián)，或者計(jì)算邏輯復(fù)雜（比如統(tǒng)計(jì)每個(gè)用戶的訂單總額）；

直接查業(yè)務(wù)庫(kù)會(huì)影響線上業(yè)務(wù)，如果實(shí)時(shí)性不是那么高，完全可以考慮離線數(shù)倉(cāng) —— 把導(dǎo)出需要的明細(xì)或匯總數(shù)據(jù)，提前用定時(shí)任務(wù)同步到 ClickHouse、Hive 這類 OLAP 數(shù)據(jù)庫(kù)里，導(dǎo)出時(shí)查數(shù)倉(cāng)，不碰業(yè)務(wù)庫(kù)。

或者直接扔異步隊(duì)列里面（注意，最好不要扔線程池，單機(jī)容易o(hù)om，最好分布式多機(jī)消費(fèi)處理）

大廠稍微大一點(diǎn)的非實(shí)時(shí)場(chǎng)景基本都是這樣干！

優(yōu)化3：Excel 生成，拆服務(wù)、換格式？

接下來才到 Excel 生成本身。直接在應(yīng)用服務(wù)里用 POI 生成，還是有問題 ——100 萬條數(shù)據(jù)生成 Excel，就算用 SXSSF（流式生成，避免 OOM），也會(huì)占用不少 CPU 和內(nèi)存。

能不能把這步也拆出去？（不到萬不得已一般不拆，工作量反而增大，但是你得有這個(gè)意識(shí)）搞個(gè)專門的 “Excel 生成服務(wù)”，只負(fù)責(zé)從 MQ 接任務(wù)、從數(shù)倉(cāng)查數(shù)據(jù)、生成文件。這樣業(yè)務(wù)服務(wù)和生成服務(wù)解耦，各自擴(kuò)容 —— 業(yè)務(wù)服務(wù)扛并發(fā)請(qǐng)求，生成服務(wù)扛 CPU 密集型的文件生成。

另外，生成格式也能做文章：如果業(yè)務(wù)對(duì) Excel 格式要求不高（比如只是看數(shù)據(jù)，不用公式、圖表），可以先生成 CSV 文件（生成速度比 Excel 快 10 倍不止），再轉(zhuǎn)成 Excel；

或者直接讓用戶下載 CSV（前端也能打開）；如果必須要 Excel，除了 POI，還可以試試 Alibaba 的 EasyExcel，它對(duì)內(nèi)存的優(yōu)化比原生 POI 更到位，還支持注解配置，少寫不少破代碼。

優(yōu)化4：大文件傳輸卡？扔去 OSS

最后是大文件傳輸?shù)膯栴}。生成好的 Excel 文件，要是幾十 MB 甚至上百 MB，讓應(yīng)用服務(wù)直接通過 HTTP 返給用戶，還是會(huì)占用帶寬。

這時(shí)候就得用對(duì)象存儲(chǔ)（比如 OSS、S3）—— 剛才上面也提過，可以生成服務(wù)把 Excel 文件寫完后，直接上傳到 OSS，然后把 OSS 的下載鏈接（帶簽名、設(shè)過期時(shí)間，避免泄露）存到數(shù)據(jù)庫(kù)里。

用戶查結(jié)果時(shí)，后端直接返回這個(gè)鏈接，用戶去 OSS 下載，應(yīng)用服務(wù)徹底不用扛?jìng)鬏攭毫?。也可以直接用消息助手通知用戶通過鏈接下載，省時(shí)省力（不過小公司可能成本略高）

這里要注意簽名的安全性，比如鏈接過期時(shí)間設(shè) 1 小時(shí)，避免用戶把鏈接分享出去，泄露數(shù)據(jù)。

優(yōu)化5：重復(fù)？加緩存唄

到這你是不是覺得差不多了？別急，還有個(gè)容易被忽略的點(diǎn)：緩存重復(fù)請(qǐng)求。

比如同一個(gè)用戶，一天內(nèi)重復(fù)導(dǎo)出 “近 7 天的訂單”，數(shù)據(jù)沒變化，沒必要重復(fù)生成。這時(shí)候可以加個(gè)緩存，key 是 “用戶 ID + 導(dǎo)出條件（時(shí)間范圍、字段）”，value 是 OSS 鏈接，緩存過期時(shí)間設(shè)成數(shù)據(jù)更新的周期（比如訂單數(shù)據(jù)實(shí)時(shí)更新，緩存設(shè) 1 小時(shí)）。

這樣重復(fù)請(qǐng)求直接命中緩存，省了查庫(kù)和生成的成本。但要注意緩存失效策略 —— 如果底層數(shù)據(jù)更新了（比如用戶改了訂單狀態(tài)），得及時(shí)清掉對(duì)應(yīng)的緩存，不然用戶下載到的是舊數(shù)據(jù)。

總結(jié)

最后總結(jié)下，沒有什么 “牛逼的方案” 能解決所有導(dǎo)出慢的問題，核心思路是 “拆解鏈路、分治優(yōu)化”：把 “查數(shù)據(jù) - 生成文件 - 傳輸文件” 拆成三個(gè)獨(dú)立環(huán)節(jié)，每個(gè)環(huán)節(jié)用最適合的技術(shù)去扛（數(shù)倉(cāng)扛查詢、異步服務(wù)扛生成、對(duì)象存儲(chǔ)扛?jìng)鬏敚?/span>

同時(shí)用緩存減少重復(fù)勞動(dòng)，通過打點(diǎn)監(jiān)控（比如任務(wù)成功率、生成耗時(shí)）盯著鏈路中的坑。

任何脫離場(chǎng)景談優(yōu)化都是耍流氓 —— 如果業(yè)務(wù)方導(dǎo)出的數(shù)據(jù)永遠(yuǎn)不超過 1 萬條，那搞異步和對(duì)象存儲(chǔ)就是過度設(shè)計(jì)；

但如果是 To B 業(yè)務(wù)，用戶動(dòng)不動(dòng)導(dǎo)出百萬級(jí)數(shù)據(jù)，那上述的鏈路改造就是必須的。