Clickhouse似乎很慢

Question

我们正在考虑将ClickHouse作为物联网使用的时间序列数据库，因此我们遵循了为此设计的表的最佳实践，但是在运行测试时，结果在第一次运行查询时似乎相当慢，但随后它非常快(由于冷缓存)。

我们的表模式如下:

CREATE TABLE IF NOT EXISTS feeds (
ts UInt64,
uuid UUID,
value Decimal(30, 5),
date Date MATERIALIZED toDate(round(ts/1000)) CODEC(DoubleDelta),
time DateTime MATERIALIZED toDateTime(round(ts/1000)) CODEC(DoubleDelta)
) ENGINE = ReplacingMergeTree() PARTITION BY toYYYYMM(date) PRIMARY KEY (uuid, ts) ORDER BY (uuid, ts) SETTINGS index_granularity=4096

对于查询，执行如下命令:

SELECT toStartOfInterval(time, INTERVAL '1 day') AS agreg,
avg(value) AS value
FROM feeds
WHERE uuid='391becbb-39fd-4205-aba1-5088c9529d42'
AND ts > 1629378660000
AND ts < 1632057060000
GROUP BY agreg
ORDER BY agreg ASC

表示大约一个月的数据，按日汇总。

我们得到这样的结果:

┌───────────────agreg─┬──────────────value─┐
│ 2021-08-19 00:00:00 │  43.54795698924731 │
│ 2021-08-20 00:00:00 │  33.68460122699386 │
│ 2021-08-23 00:00:00 │   26.8002874251497 │
│ 2021-08-24 00:00:00 │  45.30143348623853 │
│ 2021-08-25 00:00:00 │  43.78214139344263 │
│ 2021-08-26 00:00:00 │  35.66887477313974 │
│ 2021-08-27 00:00:00 │  87.03632541133456 │
│ 2021-08-28 00:00:00 │  87.00372191011236 │
│ 2021-08-29 00:00:00 │  87.16366666666666 │
│ 2021-08-30 00:00:00 │  87.62234215885947 │
│ 2021-08-31 00:00:00 │  87.62653798256538 │
│ 2021-09-01 00:00:00 │  87.08854251012146 │
│ 2021-09-02 00:00:00 │ 44.809177939646204 │
│ 2021-09-03 00:00:00 │  20.85095168374817 │
│ 2021-09-06 00:00:00 │ 21.086067551266584 │
│ 2021-09-07 00:00:00 │ 20.904265569917744 │
│ 2021-09-08 00:00:00 │ 20.988032407407406 │
│ 2021-09-09 00:00:00 │ 21.070418848167538 │
│ 2021-09-10 00:00:00 │ 20.900507674144038 │
│ 2021-09-14 00:00:00 │  34.17651296829971 │
│ 2021-09-15 00:00:00 │  33.79448634590377 │
│ 2021-09-16 00:00:00 │   33.7209536423841 │
│ 2021-09-17 00:00:00 │   33.5361985472155 │
└─────────────────────┴────────────────────┘
Ok. 23 rows in set. Elapsed: 6.891 sec. Processed: 0 rows, 0.0B (0 rows/s, 0.0B/s)

对于这样一个简单的操作，7s似乎太慢了。

我们这样做是错的吗?

Answer 1

不使用分区和主索引

1. 您有3列具有相同的时间数据。当您查询ts和time时，您的查询将处理两倍以上的数据。摆脱time和date列 !!!!!!!!!!!!!!!

2. 您的表由列date分区，您的where部分使用AND ts > 1629378660000===分区消除不起作用!!使用partition by toYYYYMM(toDateTime(intDiv(ts,1000))!

3. 不要用round(ts/1000)，用intDiv(ts,1000)

4. 不使用主键索引。查询优化器是愚蠢的，使用GROUP BY uuid, agreg ORDER BY uuid, agreg ASC+tryset optimize_aggregation_in_order=1Makeagregfromts.

(uuid, agreg (ts) )pair匹配table的ORDER BY

5. 是的，CH仍然会比其他时间序列数据库慢，因为CH不是为这样的查询而设计的。