6 億數據秒級查詢，ClickHouse 太快了！
“
ClickHouse 在數據分析技術領域早已聲名遠揚，最近由於項目需求使用到了 ClickHouse 做分析數據庫，於是用測試環境做了一個單表 6 億數據量的性能測試。
圖片來自 Pexels
本文記錄一下測試結果，有做超大數據量分析技術選型需求的朋友可以參考下。
服務器信息
如下：
-
CPU：Intel Xeon Gold 6240 @ 8x 2.594GHz
-
內存：32G
-
系統：CentOS 7.6
-
Linux 內核版本：3.10.0
-
磁盤類型：機械硬盤
-
文件系統：ext4
Clickhouse 信息
如下：
-
部署方式：單機部署
-
版本：20.8.11.17
測試情況
測試數據和測試方法來自 Clickshouse 官方的 Star Schema Benchmark：
1https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/
2
3
按照官方指導造出了測試數據之後，先看一下數據量和空間佔用情況。
①數據量和空間佔用
如下圖：
可以看到 Clickhouse 的壓縮率很高，壓縮率都在 50 以上，基本可以達到 70 左右。
數據體積的減小可以非常有效的減少磁盤空間佔用、提高 I/O 性能，這對整體查詢性能的提升非常有效。
supplier、customer、part、lineorder 爲一個簡單的「供應商 - 客戶 - 訂單 - 地區」的星型模型。
lineorder_flat 爲根據這個星型模型數據關係合併的大寬表，所有分析都直接在這張大寬表中執行，減少不必要的表關聯，符合我們實際工作中的分析建表邏輯。
以下性能測試的所有分析 SQL 都在這張大寬表中運行，未進行表關聯查詢。
查詢性能測試詳情
①Query 1.1
1SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
2FROM lineorder_flat
3WHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25)
4
5┌────────revenue─┐
6│ 44652567249651 │
7└────────────────┘
8
91 rows in set. Elapsed: 0.242 sec. Processed 91.01 million rows, 728.06 MB (375.91 million rows/s., 3.01 GB/s.)
10
11
**掃描行數：**91,010,000，大約 9100 萬
**耗時（秒）：**0.242。
**查詢列數：**2。
**結果行數：**1。
1SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
2FROM lineorder_flat
3WHERE (toYYYYMM(LO_ORDERDATE) = 199401) AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6)) AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35))
4
5┌───────revenue─┐
6│ 9624332170119 │
7└───────────────┘
8
91 rows in set. Elapsed: 0.040 sec. Processed 7.75 million rows, 61.96 MB (191.44 million rows/s., 1.53 GB/s.)
10
11
**耗時（秒）：**0.040。
**查詢列數：**2。
**返回行數：**1。
③Query 2.1
1SELECT
2 sum(LO_REVENUE),
3 toYear(LO_ORDERDATE) AS year,
4 P_BRAND
5FROM lineorder_flat
6WHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA')
7GROUP BY
8 year,
9 P_BRAND
10ORDER BY
11 year ASC,
12 P_BRAND ASC
13
14┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
15│ 64420005618 │ 1992 │ MFGR#121 │
16│ 63389346096 │ 1992 │ MFGR#1210 │
17│ ........... │ .... │ ..........│
18│ 39679892915 │ 1998 │ MFGR#128 │
19│ 35300513083 │ 1998 │ MFGR#129 │
20└─────────────────┴──────┴───────────┘
21
22280 rows in set. Elapsed: 8.558 sec. Processed 600.04 million rows, 6.20 GB (70.11 million rows/s., 725.04 MB/s.)
23
24
**掃描行數：**600,040,000，大約 6 億。
**耗時（秒）：**8.558。
**查詢列數：**3。
**結果行數：**280。
④Query 2.2
1SELECT
2 sum(LO_REVENUE),
3 toYear(LO_ORDERDATE) AS year,
4 P_BRAND
5FROM lineorder_flat
6WHERE ((P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA')
7GROUP BY
8 year,
9 P_BRAND
10ORDER BY
11 year ASC,
12 P_BRAND ASC
13
14┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
15│ 66450349438 │ 1992 │ MFGR#2221 │
16│ 65423264312 │ 1992 │ MFGR#2222 │
17│ ........... │ .... │ ......... │
18│ 39907545239 │ 1998 │ MFGR#2227 │
19│ 40654201840 │ 1998 │ MFGR#2228 │
20└─────────────────┴──────┴───────────┘
21
2256 rows in set. Elapsed: 1.242 sec. Processed 600.04 million rows, 5.60 GB (482.97 million rows/s., 4.51 GB/s.)
23
24
**掃描行數：**600,040,000，大約 6 億。
**耗時（秒）：**1.242。
**查詢列數：**3。
**結果行數：**56。
⑤Query 3.1
1SELECT
2 C_NATION,
3 S_NATION,
4 toYear(LO_ORDERDATE) AS year,
5 sum(LO_REVENUE) AS revenue
6FROM lineorder_flat
7WHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997)
8GROUP BY
9 C_NATION,
10 S_NATION,
11 year
12ORDER BY
13 year ASC,
14 revenue DESC
15
16┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐
17│ INDIA │ INDIA │ 1992 │ 537778456208 │
18│ INDONESIA │ INDIA │ 1992 │ 536684093041 │
19│ ..... │ ....... │ .... │ ............ │
20│ CHINA │ CHINA │ 1997 │ 525562838002 │
21│ JAPAN │ VIETNAM │ 1997 │ 525495763677 │
22└───────────┴───────────┴──────┴──────────────┘
23
24150 rows in set. Elapsed: 3.533 sec. Processed 546.67 million rows, 5.48 GB (154.72 million rows/s., 1.55 GB/s.)
25
26
**掃描行數：**546,670,000，大約 5 億 4 千多萬。
**耗時（秒）：**3.533。
**查詢列數：**4。
**結果行數：**150。
⑥Query 3.2
1SELECT
2 C_CITY,
3 S_CITY,
4 toYear(LO_ORDERDATE) AS year,
5 sum(LO_REVENUE) AS revenue
6FROM lineorder_flat
7WHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)
8GROUP BY
9 C_CITY,
10 S_CITY,
11 year
12ORDER BY
13 year ASC,
14 revenue DESC
15
16┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐
17│ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 │
18│ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 │
19│ .......... │ .......... │ .... │ .......... │
20│ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 │
21│ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │
22└────────────┴────────────┴──────┴────────────┘
23
24600 rows in set. Elapsed: 1.000 sec. Processed 546.67 million rows, 5.56 GB (546.59 million rows/s., 5.56 GB/s.)
25
26
**掃描行數：**546,670,000，大約 5 億 4 千多萬。
**耗時（秒）：**1.00。
**查詢列數：**4。
**結果行數：**600。
⑦Query 4.1
1SELECT
2 toYear(LO_ORDERDATE) AS year,
3 C_NATION,
4 sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
5FROM lineorder_flat
6WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
7GROUP BY
8 year,
9 C_NATION
10ORDER BY
11 year ASC,
12 C_NATION ASC
13
14┌─year─┬─C_NATION──────┬────────profit─┐
15│ 1992 │ ARGENTINA │ 1041983042066 │
16│ 1992 │ BRAZIL │ 1031193572794 │
17│ .... │ ...... │ ............ │
18│ 1998 │ PERU │ 603980044827 │
19│ 1998 │ UNITED STATES │ 605069471323 │
20└──────┴───────────────┴───────────────┘
21
2235 rows in set. Elapsed: 5.066 sec. Processed 600.04 million rows, 8.41 GB (118.43 million rows/s., 1.66 GB/s.)
23
24
**掃描行數：**600,040,000，大約 6 億。
**耗時（秒）：**5.066。
**查詢列數：**4。
**結果行數：**35。
⑧Query 4.2
1SELECT
2 toYear(LO_ORDERDATE) AS year,
3 S_NATION,
4 P_CATEGORY,
5 sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
6FROM lineorder_flat
7WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((year = 1997) OR (year = 1998)) AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))
8GROUP BY
9 year,
10 S_NATION,
11 P_CATEGORY
12ORDER BY
13 year ASC,
14 S_NATION ASC,
15 P_CATEGORY ASC
16
17┌─year─┬─S_NATION──────┬─P_CATEGORY─┬───────profit─┐
18│ 1997 │ ARGENTINA │ MFGR#11 │ 102369950215 │
19│ 1997 │ ARGENTINA │ MFGR#12 │ 103052774082 │
20│ .... │ ......... │ ....... │ ............ │
21│ 1998 │ UNITED STATES │ MFGR#24 │ 60779388345 │
22│ 1998 │ UNITED STATES │ MFGR#25 │ 60042710566 │
23└──────┴───────────────┴────────────┴──────────────┘
24
25100 rows in set. Elapsed: 0.826 sec. Processed 144.42 million rows, 2.17 GB (174.78 million rows/s., 2.63 GB/s.)
26
27
**掃描行數：**144,420,000，大約 1 億 4 千多萬。
**耗時（秒）：**0.826。
**查詢列數：**4。
**結果行數：**100。
性能測試結果彙總
如下圖：
在當前軟硬件環境下，掃描 6 億多行數據，常見的分析語句首次運行最慢在 8 秒左右能返回結果。
相同的分析邏輯更換條件再次查詢的時候效率有明顯的提升，可以縮短到 1 秒左右。
如果只是簡單的列查詢沒有加減乘除、聚合等邏輯，掃描全表 6 億多行數據首次查詢基本可以在 2 秒內執行完成。
_作者：_LifeLogs
編輯：陶家龍
出處：cnblogs.com/asimov/p/14546106.html
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Jr96fYGb3MjwLBAm-c8o_w