概述
Elasticsearch以其優(yōu)秀的分布式架構(gòu)與全文搜索引擎等特點在機器數(shù)據(jù)的存儲蔗彤、分析領(lǐng)域廣為使用孝扛，但隨著數(shù)據(jù)量的增長舀锨，其聚合分析性能已無法滿足業(yè)務(wù)需求蝇更。而ClickHouse作為一個高性能的OLAP列式數(shù)據(jù)庫管理系統(tǒng)有望解決這一痛點沪编。
本文是對ClickHouse與Elasticsearch聚合性能的簡單對比測試。主要關(guān)注查詢語句的響應(yīng)時間年扩，暫不考慮資源占用情況蚁廓。
測試環(huán)境
| 組件 | 版本 | CPU | 內(nèi)存 |
|---|---|---|---|
| ClickHouse | 7.9.0 | 4C | 8G |
| Elasticsearch | 20.11.4.13 | 4C | 8G |
使用ClickHouse官方提供的測試數(shù)據(jù)集，共67G常遂，約6億行纳令。
測試數(shù)據(jù)集.png
其中，ClickHouse使用LO_ORDERDATE字段作為分區(qū)鍵克胳，使用LO_ORDERDATE, LO_ORDERKEY作為排序鍵平绩。
測試內(nèi)容
某字段出現(xiàn)次數(shù)TOP 10
# ClickHouse
SELECT LO_SHIPMODE,COUNT() FROM lineorder GROUP BY LO_SHIPMODE ORDER BY COUNT() DESC LIMIT 10
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"1": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
}
}
},
"size": 0
}
某字段按年進行計數(shù)
# ClickHouse
SELECT toYear(LO_ORDERDATE),COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval":"1y",
"format":"yyyy-MM-dd"
}
}
},
"size": 0
}
多個字段按年進行統(tǒng)計
# ClickHouse
SELECT LO_ORDERDATE,LO_ORDERKEY,LO_SHIPMODE,LO_ORDERPRIORITY,LO_COMMITDATE FROM lineorder WHERE LO_ORDERDATE >= '1992-01-01' AND LO_ORDERDATE < '1993-01-01' ORDER BY LO_ORDERDATE LIMIT 500
# Elasticsearch
GET lineorder/_search
{
"size": 500,
"sort": [
{
"timestamp": {
"order": "desc",
"unmapped_type": "boolean"
}
}
],
"query": {
"bool": {
"must": [],
"filter": [
{
"match_all": {}
},
{
"match_all": {}
},
{
"range": {
"LO_ORDERDATE": {
"gte": "1992-01-01",
"lte": "1993-01-01",
"format": "strict_date_optional_time"
}
}
}
],
"should": [],
"must_not": []
}
}
}
基于時間的多字段聚合
# ClickHouse
SELECT toYear(LO_ORDERDATE),LO_SHIPMODE,COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE),LO_SHIPMODE ORDER BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"3": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
},
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval": "1y",
"time_zone": "Asia/Shanghai",
"min_doc_count": 1
}
}
}
}
},
"size": 0
}
基于時間的多字段聚合
# ClickHouse
SELECT toYear(LO_ORDERDATE),LO_SHIPMODE,COUNT() FROM lineorder GROUP BY toYear(LO_ORDERDATE),LO_SHIPMODE ORDER BY toYear(LO_ORDERDATE) FORMAT PrettyCompactMonoBlock
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"3": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 10
},
"aggs": {
"2": {
"date_histogram": {
"field": "LO_ORDERDATE",
"calendar_interval": "1y",
"time_zone": "Asia/Shanghai",
"min_doc_count": 1
}
}
}
}
},
"size": 0
}
聚合嵌套（非時間字段）
# ClickHouse
SELECT LO_SHIPMODE,COUNT(LO_SHIPMODE),LO_ORDERPRIORITY,COUNT(LO_ORDERPRIORITY) FROM lineorder GROUP BY LO_SHIPMODE,LO_ORDERPRIORITY ORDER BY COUNT(LO_SHIPMODE),COUNT(LO_ORDERPRIORITY) LIMIT 5 BY LO_SHIPMODE,LO_ORDERPRIORITY
# Elasticsearch
GET lineorder/_search
{
"aggs": {
"2": {
"terms": {
"field": "LO_SHIPMODE.keyword",
"order": {
"_count": "desc"
},
"size": 5
},
"aggs": {
"3": {
"terms": {
"field": "LO_ORDERPRIORITY.keyword",
"order": {
"_count": "desc"
},
"size": 5
}
}
}
}
},
"size": 0
}
測試結(jié)論
| 聚合場景 | ClickHouse(ms) | Elasticsearch(ms) | 性能對比 |
|---|---|---|---|
| 基于時間的多字段聚合 | 5506 | 15599 | 近3倍 |
| 多個字段按年進行計數(shù)（數(shù)據(jù)表） | 381 | 6267 | 16倍多 |
| 某字段出現(xiàn)次數(shù) TOP 10（餅圖） | 4048 | 7317 | 近2倍 |
| 某字段按年進行計數(shù)（時間趨勢圖） | 901 | 23257 | 25倍多 |
| 聚合嵌套（非時間字段） | 6937 | 15767 | 2倍多 |
相同數(shù)據(jù)量下，ClickHouse的聚合性能都要優(yōu)于Elasticsearch漠另，且如果基于排序鍵進行聚合捏雌，性能更好，是ES的數(shù)倍笆搓。
此外性湿，ClickHouse的SummaryMergeTree纬傲、AggregatingMergeTree表引擎支持后臺自動聚合數(shù)據(jù)，所以在某些場景下其聚合分析性能會更優(yōu)肤频。
