最近協助改善查詢 DB 中的時序資料性能上的問題
由於現階段補零的動作交由前端處理，但是性能低落，導致前端圖表顯示較慢
測試資料
以下為範例資料表 test 為每 15 分鐘一筆，若此 15 分鐘無資料則不會寫進 DB
- Table
- seq - Primary Key
- created_at - INDEX
| seq | value | created_at |
|---|---|---|
| 1 | 100 | 2021-12-20 10:00:00 |
| 2 | 500 | 2021-12-20 11:15:00 |
| 3 | 100 | 2021-12-20 12:45:00 |
| … | … | … |
| 50 | 300 | 2021-12-20 22:30:00 |
解決方法
-
問題
- 前端透過 for loop 依序對缺失的時間軸補 0 ，效能低下，花費了約 3.24 秒左右的時間
-
前言
- 久久查詢一次
- 前端效能未知
- 查詢範圍固定
-
解決思路
- 補 0 透過 DB 處理
- 優化前端補 0 方法
這裡我選擇由 DB 進行補 0
產生時間刻度
首先讓 DB 產生欲查尋的時間區段、刻度
|
|
產生如下花費約 0.0046 秒
| time |
|---|
| 2021-12-01 09:00:00 |
| 2021-12-01 09:15:00 |
| 2021-12-01 09:30:00 |
| 2021-12-01 09:45:00 |
| … |
| 2021-12-31 09:00:00 |
JOIN 查詢
再透過 Left Join 查詢有此時間列表的資料
|
|
查詢出如下，很好的產生好時間及值，花費 0.0091 秒
| value | time |
|---|---|
| 100 | 2021-12-20 10:00:00 |
| 500 | 2021-12-20 11:15:00 |
| … | … |
| NULL | 2021-12-01 09:00:00 |
| NULL | 2021-12-01 09:15:00 |
| NULL | 2021-12-01 09:30:00 |
| NULL | 2021-12-01 09:45:00 |
| … | … |
| NULL | 2021-12-31 09:00:00 |
補零
最後進行補零及排序
|
|
查詢結果如下，花費 0.0091 秒
| value | time |
|---|---|
| 0 | 2021-12-01 09:00:00 |
| 0 | 2021-12-01 09:15:00 |
| 0 | 2021-12-01 09:30:00 |
| 0 | 2021-12-01 09:45:00 |
| … | … |
| 100 | 2021-12-20 10:00:00 |
| 0 | 2021-12-20 10:15:00 |
| 0 | 2021-12-20 10:30:00 |
| 500 | 2021-12-20 11:15:00 |
| … | … |
| 0 | 2021-12-31 09:00:00 |
結論
很好的完成優化，DB 進行補 0，再到前端顯示只花了不到 1 秒內的時間