讓關係型資料庫查詢再飛一會兒
背景
有一個系統的業務正在膨脹中,某一些報表(報表資料在mysql中)資料量增長比較厲害,報表頁面已經處於卡爆了
的狀態。中間經過mysql本身的優化,已經到了當前系統架構+儲存模型的瓶頸。本文提供一種優化思路,拋磚引玉。
任務分析
以一條sql的優化為例(這條sql裡面的欄位隨便改了改,不保證正確性)。
SELECT d.col, COUNT(DISTINCT risk.inst_id) AS `count` FROM risk INNER JOIN d ON d.inst_id = risk.inst_id AND d.id = risk.id INNER JOIN b ON b.business_key = d.id AND d.type = b.type INNER JOIN r ON risk.inst_id = r.inst_id AND risk.id = r.id WHERE (r.visit_time >= '2018-10-27 00:00:00' AND r.visit_time <= '2018-11-28 15:54:40' AND d.id = '22821111115042' AND b.business_key = concat('22821111115042', '')) GROUP BY d.col
其中,risk表大小112MB,d表大小為9.5GB,b表208KB,r表大小為4.2GB。這個報表的生成邏輯中含有較多inner join。經過一些列的索引優化之後,該條sql的查詢時間是36s,前端體驗仍然不是很好,且隨著報表時間範圍的拉長,使用者資料量的增長,查詢時間會持續惡化。
這裡就不討論更改表結構、遷移資料來優化查詢了。
優化思路
本身沒有太多技術難度,但中間經過一段時間的摸索,直接說結論吧,希望對有需要的同學帶來便利。
用SparkSQL分散式計算的能力來加速查詢,SparkSQL原生支援通過jdbc連線外部儲存。
首先,嘗試了直接在sparksql的jdbc連線中執行上述sql,結果在意料之中,36秒左右。通過spark監控頁面看到,該任務task數量為1,沒有併發起來,SparkSQL將查詢完全下推給mysql執行。
那麼問題來了,如何提升併發度呢?
根據官方文件,使用jdbc連線有這麼幾個可用引數,這些引數的含義參考附錄連結。
numPartitions
,partitionColumn
,lowerBound
,upperBound
值得注意的是,partitionColumn
必須為數值型別,日期或者時間戳。lowerBound
和upperBound
必須為數字。在上面的case中,我們可以對r表的visit_time進行分割槽,並根據範圍設定上下界線。(時間戳轉化成long型)
分別在SparkSQL load這4張表,其中對r表的visit_time進行分割槽,並分別在SparkSQL中註冊臨時表,在SparkSQL內執行上述SQL,上述SQL執行時間由36s降低到12s,如果調調SparkSQL的引數,效能可能會更好。
這個方法從理論上來說,適用於任何單機關係型資料庫。
原理簡單剖析
這裡是將SparkSQL作為一個分散式查詢引擎,mysql作為SparkSQL的一種資料來源。SparkSQL內部有高度的統一抽象(DataFrame/DataSet)。SparkSQL從mysql中抽取資料然後根據自身的邏輯來進行運算。如果對細節感興趣可以參考連結2。
參考文件
[1]ofollow,noindex">http://spark.apache.org/docs/latest/sql-data-sources-jdbc.html
[2]http://spark.apache.org/docs/latest/sql-programming-guide.html