報(bào)表需求背景

報(bào)表是一個(gè)很常見的需求，在項(xiàng)目中后期往往會(huì)需要加多種維度的一些統(tǒng)計(jì)信息，今天就來(lái)談?wù)勆暇€近10個(gè)月后的一次報(bào)表優(yōu)化優(yōu)化之路（從一天報(bào)表跑需要五分鐘，優(yōu)化至秒級(jí)）
需求：對(duì)代理商進(jìn)行日統(tǒng)計(jì)
統(tǒng)計(jì)數(shù)據(jù)：門店數(shù)量、設(shè)備總數(shù)、當(dāng)日訂單數(shù)/金額/退款/收益、門店七日新增數(shù)、30日0訂單門店數(shù)量
前置約束：未明確標(biāo)明指定主庫(kù)操作 以及 事務(wù)，則默認(rèn)代表走 從庫(kù) 以及 默認(rèn)事務(wù)

先來(lái)看看這一版的流程：

// 以下所有查詢/統(tǒng)計(jì) 均為從MySQL中獲取按天 開始 循環(huán)（任務(wù)調(diào)度時(shí)可指定日期補(bǔ)償重跑，防止后續(xù)定時(shí)任務(wù)中斷，默認(rèn)跑昨日數(shù)據(jù)）1. 獲取所有代理商（大幾千個(gè)）代理商列表 循環(huán)開始2. 門店統(tǒng)計(jì)2.1    獲取代理名下所有門店列表2.2    查詢代理近三十天內(nèi)有訂單的門店ID，對(duì)比門店列表 得到：30日0訂單門店數(shù)量2.3    獲取代理名下七日新增門店3. 設(shè)備總數(shù)統(tǒng)計(jì)4. 訂單統(tǒng)計(jì)4.1    統(tǒng)計(jì)代理昨日訂單數(shù)/訂單金額/退款（訂單/收益 均是千萬(wàn)級(jí)表）4.2    統(tǒng)計(jì)代理昨日收益代理商列表 循環(huán)結(jié)束5. 新開事務(wù) 且 指定主庫(kù)5.1    清理對(duì)應(yīng)日期的統(tǒng)計(jì)數(shù)據(jù)5.2    對(duì)統(tǒng)計(jì)數(shù)據(jù)進(jìn)行分批提交（mybatis拼接SQL，千條為一個(gè)批次，防止后續(xù)當(dāng)日統(tǒng)計(jì)數(shù)據(jù)過(guò)多，導(dǎo)致SQL長(zhǎng)度超限）5.3    事務(wù)提交
按天 結(jié)束 循環(huán)

以上流程跑當(dāng)日耗時(shí)大約在4-5分鐘，乍一看其實(shí)并不慢，但此時(shí)距離上線已有九月有余，乍一算這個(gè)任務(wù)得跑20+小時(shí)
不管了，能跑就行，先上線再優(yōu)化
…
after a long time
午夜驚醒，這玩意得優(yōu)化哇，這也太不好用了
-_- 還債的時(shí)刻到了
…

第二版

思考：報(bào)表任務(wù)里都是一些MySQL查詢 以及 內(nèi)存循環(huán)對(duì)比，且門店統(tǒng)計(jì)那塊是嵌套循環(huán)查詢，訂單的查詢時(shí)間也有點(diǎn)長(zhǎng)
帶著這些思路去排查，發(fā)現(xiàn)幾個(gè)問(wèn)題：

1. 每個(gè)代理都需要去查詢一遍門店統(tǒng)計(jì)信息，這里網(wǎng)絡(luò)IO次數(shù) = 總代理數(shù)量
   若每次50ms * 幾千，emm，怎么這么多…
2. 訂單的查詢某些代理耗時(shí)很高，去看了下索引，emm，1 2 3 4 …8 9 10個(gè)索引
   了解到MySQL8.0是基于成本模型來(lái)生成執(zhí)行計(jì)劃的，那么有可能是索引不完全匹配 或 執(zhí)行計(jì)劃偏移，下面貼一下SQL與表當(dāng)前索引

# 訂單統(tǒng)計(jì)SQL
SELECTcount( * ) orderTotal,sum( pay_amount ) AS orderAmount,sum( refund_amount ) AS refundTotal
FROMorder 
WHEREagent_id = #{groupId}AND pay_rev_time BETWEEN #{startDate} and #{endDate}    # 這個(gè)時(shí)間可能會(huì)有跨度# 貼下部分索引
uk_order_no            `order_no` ASC
idx_agent_id            `agent_id` ASC
idx_pay_rev_time    `pay_rev_time` ASC
idex_emp            `empower_time` ASC

發(fā)現(xiàn)問(wèn)題，那么就開始一個(gè)個(gè)嘗試改造優(yōu)化下：

問(wèn)題一流程優(yōu)化

1. 分組查詢所有代理 門店總數(shù)
2. 分組查詢所有代理 7 日新增門店數(shù)
3. 分組查詢所有代理 名下門店總數(shù)
4. 分組查詢所有代理 近三十天內(nèi)有訂單的門店ID
5. 分組查詢所有代理 設(shè)備總數(shù)
6. 分組查詢所有代理 昨日收益金額
按天 開始 循環(huán)（任務(wù)調(diào)度時(shí)可指定日期補(bǔ)償重跑，防止后續(xù)定時(shí)任務(wù)中斷，默認(rèn)跑昨日數(shù)據(jù)）7. 獲取所有的代理代理商列表 循環(huán)開始8. 門店統(tǒng)計(jì)8.1    內(nèi)存中 獲取代理名下所有門店列表（時(shí)間復(fù)雜度O(1)）8.2    內(nèi)存中 查詢代理近三十天內(nèi)有訂單的門店ID，對(duì)比門店列表 得到：30日0訂單門店數(shù)量（時(shí)間復(fù)雜度O(1)）8.3    內(nèi)存中 獲取代理名下七日新增門店（時(shí)間復(fù)雜度O(M+N) 代理門店列表 與 有訂單門店列表求交集）9. 訂單統(tǒng)計(jì)9.1    MySQL 統(tǒng)計(jì)代理昨日訂單數(shù)/訂單金額/退款9.2    內(nèi)存中 統(tǒng)計(jì)代理昨日收益（時(shí)間復(fù)雜度O(1)）10. 內(nèi)存中 獲取設(shè)備總數(shù)統(tǒng)計(jì)（時(shí)間復(fù)雜度O(1)）11. 新開事務(wù) 且 指定主庫(kù)11.1    清理對(duì)應(yīng)日期的統(tǒng)計(jì)數(shù)據(jù)11.2    對(duì)統(tǒng)計(jì)數(shù)據(jù)進(jìn)行分批提交（mybatis拼接SQL，千條為一個(gè)批次，防止后續(xù)當(dāng)日統(tǒng)計(jì)數(shù)據(jù)過(guò)多，導(dǎo)致SQL長(zhǎng)度超限）11.3    事務(wù)提交代理商列表 循環(huán)結(jié)束
按天 結(jié)束 循環(huán)

至此重跑，發(fā)現(xiàn)統(tǒng)計(jì)一天的數(shù)據(jù)已經(jīng)達(dá)到秒級(jí)，這里給到一段真實(shí)執(zhí)行時(shí)間

問(wèn)題二SQL優(yōu)化

看到這里就會(huì)有小伙伴有疑問(wèn)了，為什么上面 9.1流程 中不采用預(yù)先一次性統(tǒng)計(jì)所有代理數(shù)據(jù)呢？
這里是為了引出第二個(gè)優(yōu)化方向，不然這不就結(jié)束了嘛~~~

修改后打補(bǔ)丁繼續(xù)執(zhí)行，又又又失敗了…

# 回顧上面的 訂單統(tǒng)計(jì)SQL，有兩個(gè)條件，分別是：agent_id、pay_rev_time
# 而這兩個(gè)字段也分別有自己的獨(dú)立索引，分別是：idx_agent_id、idx_pay_rev_time# 那么對(duì)于優(yōu)化器就大概以下幾個(gè)策略來(lái)進(jìn)行查詢：
#     1. 根據(jù) idx_pay_rev_time索引來(lái)找到一段時(shí)間內(nèi)數(shù)據(jù)，然后再根據(jù)agent_id 篩選出最終的結(jié)果
#     2. 根據(jù) agent_id索引來(lái)找到具體代理商的數(shù)據(jù)，然后再根據(jù)pay_rev_time 篩選出最終的結(jié)果
#     3. 全表 掃# 在業(yè)務(wù)中，使用上述幾種方式去查詢都將不是最優(yōu)解，而 agent_id、pay_rev_time又是此SQL的必填條件，
# 此時(shí)可以為他們創(chuàng)建一個(gè)聯(lián)合索引：ALTER TABLE order ADD INDEX idx_agentid_paytime (agent_id,pay_rev_time);
# 并且在SQL上強(qiáng)制使用此索引，防止執(zhí)行計(jì)劃偏移SELECTcount( * ) orderTotal,sum( pay_amount ) AS orderAmount,sum( refund_amount ) AS refundTotal
FROMorder force index(idx_agentid_paytime)
WHEREagent_id = #{groupId}AND pay_rev_time BETWEEN #{startDate} and #{endDate}

后記

問(wèn)題一流程優(yōu)化解釋

此解題思路實(shí)際上是避免了循環(huán)查詢MySQL，以 一次慢查詢 來(lái) 優(yōu)化后續(xù)的 多次快查詢。

但事無(wú)絕對(duì)，在某些情景下，一次統(tǒng)計(jì)的慢查詢可能會(huì)令系統(tǒng)負(fù)載很高，甚至影響到實(shí)時(shí)業(yè)務(wù)，那么保持現(xiàn)狀：多次快查詢 可能會(huì)更優(yōu)。

少量多次 與 一次解決，需要根據(jù)業(yè)務(wù)以及系統(tǒng)現(xiàn)狀來(lái)衡量，有時(shí)候快并不是唯一的追求

參考資料

https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
https://www.cnblogs.com/wcwen1990/p/6656611.html