報表需求背景

報表是一個很常見的需求，在項目中后期往往會需要加多種維度的一些統計信息，今天就來談談上線近10個月后的一次報表優化優化之路（從一天報表跑需要五分鐘，優化至秒級）
需求：對代理商進行日統計
統計數據：門店數量、設備總數、當日訂單數/金額/退款/收益、門店七日新增數、30日0訂單門店數量
前置約束：未明確標明指定主庫操作 以及 事務，則默認代表走 從庫 以及 默認事務

先來看看這一版的流程：

// 以下所有查詢/統計 均為從MySQL中獲取按天 開始 循環（任務調度時可指定日期補償重跑，防止后續定時任務中斷，默認跑昨日數據）1. 獲取所有代理商（大幾千個）代理商列表 循環開始2. 門店統計2.1    獲取代理名下所有門店列表2.2    查詢代理近三十天內有訂單的門店ID，對比門店列表 得到：30日0訂單門店數量2.3    獲取代理名下七日新增門店3. 設備總數統計4. 訂單統計4.1    統計代理昨日訂單數/訂單金額/退款（訂單/收益 均是千萬級表）4.2    統計代理昨日收益代理商列表 循環結束5. 新開事務 且 指定主庫5.1    清理對應日期的統計數據5.2    對統計數據進行分批提交（mybatis拼接SQL，千條為一個批次，防止后續當日統計數據過多，導致SQL長度超限）5.3    事務提交
按天 結束 循環

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

第二版

思考：報表任務里都是一些MySQL查詢 以及 內存循環對比，且門店統計那塊是嵌套循環查詢，訂單的查詢時間也有點長
帶著這些思路去排查，發現幾個問題：

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

# 訂單統計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}    # 這個時間可能會有跨度# 貼下部分索引
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

發現問題，那么就開始一個個嘗試改造優化下：

問題一流程優化

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

至此重跑，發現統計一天的數據已經達到秒級，這里給到一段真實執行時間

問題二SQL優化

看到這里就會有小伙伴有疑問了，為什么上面 9.1流程 中不采用預先一次性統計所有代理數據呢？
這里是為了引出第二個優化方向，不然這不就結束了嘛~~~

修改后打補丁繼續執行，又又又失敗了…

# 回顧上面的 訂單統計SQL，有兩個條件，分別是：agent_id、pay_rev_time
# 而這兩個字段也分別有自己的獨立索引，分別是：idx_agent_id、idx_pay_rev_time# 那么對于優化器就大概以下幾個策略來進行查詢：
#     1. 根據 idx_pay_rev_time索引來找到一段時間內數據，然后再根據agent_id 篩選出最終的結果
#     2. 根據 agent_id索引來找到具體代理商的數據，然后再根據pay_rev_time 篩選出最終的結果
#     3. 全表 掃# 在業務中，使用上述幾種方式去查詢都將不是最優解，而 agent_id、pay_rev_time又是此SQL的必填條件，
# 此時可以為他們創建一個聯合索引：ALTER TABLE order ADD INDEX idx_agentid_paytime (agent_id,pay_rev_time);
# 并且在SQL上強制使用此索引，防止執行計劃偏移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}

后記

問題一流程優化解釋

此解題思路實際上是避免了循環查詢MySQL，以 一次慢查詢 來 優化后續的 多次快查詢。

但事無絕對，在某些情景下，一次統計的慢查詢可能會令系統負載很高，甚至影響到實時業務，那么保持現狀：多次快查詢 可能會更優。

少量多次 與 一次解決，需要根據業務以及系統現狀來衡量，有時候快并不是唯一的追求

參考資料

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