如何用ClickHouse存儲時序數(shù)據(jù)

簡介

我們需要存儲結(jié)構(gòu)化時序數(shù)據(jù)，時間間隔為5分鐘或1分鐘，計算95峰值、995峰值、最值等指標，并且在網(wǎng)頁中展示。

MySQL

項目開發(fā)初期，為了快速開發(fā)原型，驗證產(chǎn)品，我們使用MySQL作為整個項目的存儲。帶來的問題是時序數(shù)據(jù)庫范圍分析查詢耗時很長，計算30天的數(shù)據(jù)需要30s+，到了無法容忍的地步，即便是創(chuàng)建索引、使用BitInt存儲時間戳，幾乎沒有性能提升。

后來我們組其他同事說換ClickHouse來存儲時序數(shù)據(jù)，于是我們就開始了替換之旅。 

ClickHouse

ClickHouse是面向OLAP（在線分析處理）、兼容SQL標準的列式數(shù)據(jù)庫，主要的不足是不支持事務。因此我們目前沒有把整個存儲都遷移到ClickHouse上，而是只把時序數(shù)據(jù)存過來。

本以為替換過程會很麻煩，可能修改大量的代碼和邏輯，實際上很快，因為之前接口的邏輯設計很合理，所以只替換了數(shù)據(jù)庫ORM庫，從gorm換成了sqlx，花了1天時間(前期重構(gòu)邏輯花了1個星期我會亂說)。

更重要的是，ClickHouse提供了很多聚合函數(shù)，之前計算95值需要2次查詢，而現(xiàn)在只需要一次查詢就夠了，對應的SQL如下：

select d.en_name, max(d.in_value) as peak_in,
    max(d.out_value) as peak_out, max(d.max_value) as peak_max,
    quantileExact(0.95)(d.out_value) as peak_95,
    quantileExact(0.995)(d.out_value) as peak_995,
    quantileExact(0.999)(d.out_value) as peak_999
from table_value d where d.record_time >= '2020-01-01 00:00:00' and d.record_time <= '2020-01-31 23:59:59'
group by d.en_name

經(jīng)驗證，ClickHouse是真的牛逼，30天內(nèi)的查詢耗時從30s降到2s內(nèi)，提升了15倍?。。?/strong>

下圖是ClickHouse的測試結(jié)果，x軸表示查詢的時間范圍，最大12個月，最小1個月，共測試12次。可以看到大部分耗時在3s內(nèi)。

下圖是MySQL存儲中的測試結(jié)果（忽略標題），分別計算1、2、3個月范圍的數(shù)據(jù)，共查詢1次，耗時都在100s以上。