6億數據秒級查詢，ClickHouse太快了！

作者：LifeLogs 2021-03-26 07:58:34

運維數據庫運維開發工具

ClickHouse 在數據分析技術領域早已聲名遠揚，最近由于項目需求使用到了 ClickHouse 做分析數據庫，于是用測試環境做了一個單表 6 億數據量的性能測試。

[[389655]]

圖片來自 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：

https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/

按照官方指導造出了測試數據之后，先看一下數據量和空間占用情況。

①數據量和空間占用

如下圖：

可以看到 Clickhouse 的壓縮率很高，壓縮率都在 50 以上，基本可以達到 70 左右。

數據體積的減小可以非常有效的減少磁盤空間占用、提高 I/O 性能，這對整體查詢性能的提升非常有效。

supplier、customer、part、lineorder 為一個簡單的「供應商-客戶-訂單-地區」的星型模型。

lineorder_flat 為根據這個星型模型數據關系合并的大寬表，所有分析都直接在這張大寬表中執行，減少不必要的表關聯，符合我們實際工作中的分析建表邏輯。

以下性能測試的所有分析 SQL 都在這張大寬表中運行，未進行表關聯查詢。

查詢性能測試詳情

①Query 1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue 
FROM lineorder_flat 
WHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25) 
 
┌────────revenue─┐ 
│ 44652567249651 │ 
└────────────────┘ 
 
1 rows in set. Elapsed: 0.242 sec. Processed 91.01 million rows, 728.06 MB (375.91 million rows/s., 3.01 GB/s.)

掃描行數：91,010,000，大約 9100 萬

耗時(秒)：0.242。

查詢列數：2。

結果行數：1。

②Query 1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue 
FROM lineorder_flat 
WHERE (toYYYYMM(LO_ORDERDATE) = 199401) AND ((LO_DISCOUNT >= 4) AND (LO_DISCOUNT <= 6)) AND ((LO_QUANTITY >= 26) AND (LO_QUANTITY <= 35)) 
 
┌───────revenue─┐ 
│ 9624332170119 │ 
└───────────────┘ 
 
1 rows in set. Elapsed: 0.040 sec. Processed 7.75 million rows, 61.96 MB (191.44 million rows/s., 1.53 GB/s.)

掃描行數：7,750,000，775 萬。

耗時(秒)：0.040。

查詢列數：2。

返回行數：1。

③Query 2.1

SELECT  
    sum(LO_REVENUE), 
    toYear(LO_ORDERDATE) AS year, 
    P_BRAND 
FROM lineorder_flat 
WHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA') 
GROUP BY  
    year, 
    P_BRAND 
ORDER BY  
    year ASC, 
    P_BRAND ASC 
 
┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐ 
│     64420005618 │ 1992 │ MFGR#121  │ 
│     63389346096 │ 1992 │ MFGR#1210 │ 
│     ........... │ .... │ ..........│ 
│     39679892915 │ 1998 │ MFGR#128  │ 
│     35300513083 │ 1998 │ MFGR#129  │ 
└─────────────────┴──────┴───────────┘ 
 
280 rows in set. Elapsed: 8.558 sec. Processed 600.04 million rows, 6.20 GB (70.11 million rows/s., 725.04 MB/s.)

掃描行數：600,040,000，大約 6 億。

耗時(秒)：8.558。

查詢列數：3。

結果行數：280。

④Query 2.2

SELECT  
    sum(LO_REVENUE), 
    toYear(LO_ORDERDATE) AS year, 
    P_BRAND 
FROM lineorder_flat 
WHERE ((P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA') 
GROUP BY  
    year, 
    P_BRAND 
ORDER BY  
    year ASC, 
    P_BRAND ASC 
 
┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐ 
│     66450349438 │ 1992 │ MFGR#2221 │ 
│     65423264312 │ 1992 │ MFGR#2222 │ 
│     ........... │ .... │ ......... │ 
│     39907545239 │ 1998 │ MFGR#2227 │ 
│     40654201840 │ 1998 │ MFGR#2228 │ 
└─────────────────┴──────┴───────────┘ 
 
56 rows in set. Elapsed: 1.242 sec. Processed 600.04 million rows, 5.60 GB (482.97 million rows/s., 4.51 GB/s.)

掃描行數：600,040,000，大約 6 億。

耗時(秒)：1.242。

查詢列數：3。

結果行數：56。

⑤Query 3.1

SELECT  
    C_NATION, 
    S_NATION, 
    toYear(LO_ORDERDATE) AS year, 
    sum(LO_REVENUE) AS revenue 
FROM lineorder_flat 
WHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997) 
GROUP BY  
    C_NATION, 
    S_NATION, 
    year 
ORDER BY  
    year ASC, 
    revenue DESC 
 
┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐ 
│ INDIA     │ INDIA     │ 1992 │ 537778456208 │ 
│ INDONESIA │ INDIA     │ 1992 │ 536684093041 │ 
│ .....     │ .......   │ .... │ ............ │ 
│ CHINA     │ CHINA     │ 1997 │ 525562838002 │ 
│ JAPAN     │ VIETNAM   │ 1997 │ 525495763677 │ 
└───────────┴───────────┴──────┴──────────────┘ 
 
150 rows in set. Elapsed: 3.533 sec. Processed 546.67 million rows, 5.48 GB (154.72 million rows/s., 1.55 GB/s.)

掃描行數：546,670,000，大約 5 億 4 千多萬。

耗時(秒)：3.533。

查詢列數：4。

結果行數：150。

⑥Query 3.2

SELECT  
    C_CITY, 
    S_CITY, 
    toYear(LO_ORDERDATE) AS year, 
    sum(LO_REVENUE) AS revenue 
FROM lineorder_flat 
WHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997) 
GROUP BY  
    C_CITY, 
    S_CITY, 
    year 
ORDER BY  
    year ASC, 
    revenue DESC 
 
┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐ 
│ UNITED ST6 │ UNITED ST6 │ 1992 │ 5694246807 │ 
│ UNITED ST0 │ UNITED ST0 │ 1992 │ 5676049026 │ 
│ .......... │ .......... │ .... │ .......... │ 
│ UNITED ST9 │ UNITED ST9 │ 1997 │ 4836163349 │ 
│ UNITED ST9 │ UNITED ST5 │ 1997 │ 4769919410 │ 
└────────────┴────────────┴──────┴────────────┘ 
 
600 rows in set. Elapsed: 1.000 sec. Processed 546.67 million rows, 5.56 GB (546.59 million rows/s., 5.56 GB/s.)

掃描行數：546,670,000，大約 5 億 4 千多萬。

耗時(秒)：1.00。

查詢列數：4。

結果行數：600。

⑦Query 4.1

SELECT  
    toYear(LO_ORDERDATE) AS year, 
    C_NATION, 
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit 
FROM lineorder_flat 
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2')) 
GROUP BY  
    year, 
    C_NATION 
ORDER BY  
    year ASC, 
    C_NATION ASC 
 
┌─year─┬─C_NATION──────┬────────profit─┐ 
│ 1992 │ ARGENTINA     │ 1041983042066 │ 
│ 1992 │ BRAZIL        │ 1031193572794 │ 
│ .... │ ......        │  ............ │ 
│ 1998 │ PERU          │  603980044827 │ 
│ 1998 │ UNITED STATES │  605069471323 │ 
└──────┴───────────────┴───────────────┘ 
 
35 rows in set. Elapsed: 5.066 sec. Processed 600.04 million rows, 8.41 GB (118.43 million rows/s., 1.66 GB/s.)

掃描行數：600,040,000，大約 6 億。

耗時(秒)：5.066。

查詢列數：4。

結果行數：35。

⑧Query 4.2

SELECT  
    toYear(LO_ORDERDATE) AS year, 
    S_NATION, 
    P_CATEGORY, 
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit 
FROM lineorder_flat 
WHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((year = 1997) OR (year = 1998)) AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2')) 
GROUP BY  
    year, 
    S_NATION, 
    P_CATEGORY 
ORDER BY  
    year ASC, 
    S_NATION ASC, 
    P_CATEGORY ASC 
 
┌─year─┬─S_NATION──────┬─P_CATEGORY─┬───────profit─┐ 
│ 1997 │ ARGENTINA     │ MFGR#11    │ 102369950215 │ 
│ 1997 │ ARGENTINA     │ MFGR#12    │ 103052774082 │ 
│ .... │ .........     │ .......    │ ............ │ 
│ 1998 │ UNITED STATES │ MFGR#24    │  60779388345 │ 
│ 1998 │ UNITED STATES │ MFGR#25    │  60042710566 │ 
└──────┴───────────────┴────────────┴──────────────┘ 
 
100 rows in set. Elapsed: 0.826 sec. Processed 144.42 million rows, 2.17 GB (174.78 million rows/s., 2.63 GB/s.)

掃描行數：144,420,000，大約 1 億 4 千多萬。

耗時(秒)：0.826。

查詢列數：4。

結果行數：100。

性能測試結果匯總

如下圖：

在當前軟硬件環境下，掃描 6 億多行數據，常見的分析語句首次運行最慢在 8 秒左右能返回結果。

相同的分析邏輯更換條件再次查詢的時候效率有明顯的提升，可以縮短到 1 秒左右。

如果只是簡單的列查詢沒有加減乘除、聚合等邏輯，掃描全表 6 億多行數據首次查詢基本可以在 2 秒內執行完成。

作者：LifeLogs

編輯：陶家龍

出處：cnblogs.com/asimov/p/14546106.html

責任編輯：武曉燕來源：博客園

數據秒級查詢