6億數(shù)據(jù)秒級(jí)查詢，ClickHouse太快了！
圖片來(lái)自 Pexels
本文記錄一下測(cè)試結(jié)果，有做超大數(shù)據(jù)量分析技術(shù)選型需求的朋友可以參考下。
服務(wù)器信息
如下：
- CPU：Intel Xeon Gold 6240 @ 8x 2.594GHz
- 內(nèi)存：32G
- 系統(tǒng)：CentOS 7.6
- Linux 內(nèi)核版本：3.10.0
- 磁盤(pán)類型：機(jī)械硬盤(pán)
- 文件系統(tǒng)：ext4
Clickhouse 信息
如下：
- 部署方式：?jiǎn)螜C(jī)部署
- 版本：20.8.11.17
測(cè)試情況
測(cè)試數(shù)據(jù)和測(cè)試方法來(lái)自 Clickshouse 官方的 Star Schema Benchmark：
- https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/
按照官方指導(dǎo)造出了測(cè)試數(shù)據(jù)之后，先看一下數(shù)據(jù)量和空間占用情況。
①數(shù)據(jù)量和空間占用
如下圖：
可以看到 Clickhouse 的壓縮率很高，壓縮率都在 50 以上，基本可以達(dá)到 70 左右。
數(shù)據(jù)體積的減小可以非常有效的減少磁盤(pán)空間占用、提高 I/O 性能，這對(duì)整體查詢性能的提升非常有效。
supplier、customer、part、lineorder 為一個(gè)簡(jiǎn)單的「供應(yīng)商-客戶-訂單-地區(qū)」的星型模型。
lineorder_flat 為根據(jù)這個(gè)星型模型數(shù)據(jù)關(guān)系合并的大寬表，所有分析都直接在這張大寬表中執(zhí)行，減少不必要的表關(guān)聯(lián)，符合我們實(shí)際工作中的分析建表邏輯。
以下性能測(cè)試的所有分析 SQL 都在這張大寬表中運(yùn)行，未進(jìn)行表關(guān)聯(lián)查詢。
查詢性能測(cè)試詳情
①Q(mào)uery 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.)
掃描行數(shù)：91,010,000，大約 9100 萬(wàn)
耗時(shí)(秒)：0.242。
查詢列數(shù)：2。
結(jié)果行數(shù)：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.)
掃描行數(shù)：7,750,000，775 萬(wàn)。
耗時(shí)(秒)：0.040。
查詢列數(shù)：2。
返回行數(shù)：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.)
掃描行數(shù)：600,040,000，大約 6 億。
耗時(shí)(秒)：8.558。
查詢列數(shù)：3。
結(jié)果行數(shù)：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.)
掃描行數(shù)：600,040,000，大約 6 億。
耗時(shí)(秒)：1.242。
查詢列數(shù)：3。
結(jié)果行數(shù)：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.)
掃描行數(shù)：546,670,000，大約 5 億 4 千多萬(wàn)。
耗時(shí)(秒)：3.533。
查詢列數(shù)：4。
結(jié)果行數(shù)：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.)
掃描行數(shù)：546,670,000，大約 5 億 4 千多萬(wàn)。
耗時(shí)(秒)：1.00。
查詢列數(shù)：4。
結(jié)果行數(shù)：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.)
掃描行數(shù)：600,040,000，大約 6 億。
耗時(shí)(秒)：5.066。
查詢列數(shù)：4。
結(jié)果行數(shù)：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.)
掃描行數(shù)：144,420,000，大約 1 億 4 千多萬(wàn)。
耗時(shí)(秒)：0.826。
查詢列數(shù)：4。
結(jié)果行數(shù)：100。
性能測(cè)試結(jié)果匯總
如下圖：
在當(dāng)前軟硬件環(huán)境下，掃描 6 億多行數(shù)據(jù)，常見(jiàn)的分析語(yǔ)句首次運(yùn)行最慢在 8 秒左右能返回結(jié)果。
相同的分析邏輯更換條件再次查詢的時(shí)候效率有明顯的提升，可以縮短到 1 秒左右。
如果只是簡(jiǎn)單的列查詢沒(méi)有加減乘除、聚合等邏輯，掃描全表 6 億多行數(shù)據(jù)首次查詢基本可以在 2 秒內(nèi)執(zhí)行完成。
作者：LifeLogs
編輯：陶家龍
出處：cnblogs.com/asimov/p/14546106.html
