6亿数据秒级查询，ClickHouse太快了！

图片来自 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

SELECTsum(LO_EXTENDEDPRICE*LO_DISCOUNT)ASrevenue
FROMlineorder_flat
WHERE(toYear(LO_ORDERDATE)=1993)AND((LO_DISCOUNT>=1)AND(LO_DISCOUNT<=3))AND(LO_QUANTITY<25)

┌────────revenue─┐
│44652567249651│
└────────────────┘

1rowsinset.Elapsed:0.242sec.Processed91.01millionrows,728.06MB(375.91millionrows/s.,3.01GB/s.)

扫描行数：91,010,000，大约 9100 万

耗时(秒)：0.242。

查询列数：2。

结果行数：1。

②Query 1.2

SELECTsum(LO_EXTENDEDPRICE*LO_DISCOUNT)ASrevenue
FROMlineorder_flat
WHERE(toYYYYMM(LO_ORDERDATE)=199401)AND((LO_DISCOUNT>=4)AND(LO_DISCOUNT<=6))AND((LO_QUANTITY>=26)AND(LO_QUANTITY<=35))

┌───────revenue─┐
│9624332170119│
└───────────────┘

1rowsinset.Elapsed:0.040sec.Processed7.75millionrows,61.96MB(191.44millionrows/s.,1.53GB/s.)

扫描行数：7,750,000，775 万。

耗时(秒)：0.040。

查询列数：2。

返回行数：1。

③Query 2.1

SELECT
sum(LO_REVENUE),
toYear(LO_ORDERDATE)ASyear,
P_BRAND
FROMlineorder_flat
WHERE(P_CATEGORY='MFGR#12')AND(S_REGION='AMERICA')
GROUPBY
year,
P_BRAND
ORDERBY
yearASC,
P_BRANDASC

┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
│64420005618│1992│MFGR#121│
│63389346096│1992│MFGR#1210│
│...........│....│..........│
│39679892915│1998│MFGR#128│
│35300513083│1998│MFGR#129│
└─────────────────┴──────┴───────────┘

280rowsinset.Elapsed:8.558sec.Processed600.04millionrows,6.20GB(70.11millionrows/s.,725.04MB/s.)

扫描行数：600,040,000，大约 6 亿。

耗时(秒)：8.558。

查询列数：3。

结果行数：280。

④Query 2.2

SELECT
sum(LO_REVENUE),
toYear(LO_ORDERDATE)ASyear,
P_BRAND
FROMlineorder_flat
WHERE((P_BRAND>='MFGR#2221')AND(P_BRAND<='MFGR#2228'))AND(S_REGION='ASIA')
GROUPBY
year,
P_BRAND
ORDERBY
yearASC,
P_BRANDASC

┌─sum(LO_REVENUE)─┬─year─┬─P_BRAND───┐
│66450349438│1992│MFGR#2221│
│65423264312│1992│MFGR#2222│
│...........│....│.........│
│39907545239│1998│MFGR#2227│
│40654201840│1998│MFGR#2228│
└─────────────────┴──────┴───────────┘

56rowsinset.Elapsed:1.242sec.Processed600.04millionrows,5.60GB(482.97millionrows/s.,4.51GB/s.)

扫描行数：600,040,000，大约 6 亿。

耗时(秒)：1.242。

查询列数：3。

结果行数：56。

⑤Query 3.1

SELECT
C_NATION,
S_NATION,
toYear(LO_ORDERDATE)ASyear,
sum(LO_REVENUE)ASrevenue
FROMlineorder_flat
WHERE(C_REGION='ASIA')AND(S_REGION='ASIA')AND(year>=1992)AND(year<=1997)
GROUPBY
C_NATION,
S_NATION,
year
ORDERBY
yearASC,
revenueDESC

┌─C_NATION──┬─S_NATION──┬─year─┬──────revenue─┐
│INDIA│INDIA│1992│537778456208│
│INDONESIA│INDIA│1992│536684093041│
│.....│.......│....│............│
│CHINA│CHINA│1997│525562838002│
│JAPAN│VIETNAM│1997│525495763677│
└───────────┴───────────┴──────┴──────────────┘

150rowsinset.Elapsed:3.533sec.Processed546.67millionrows,5.48GB(154.72millionrows/s.,1.55GB/s.)

扫描行数：546,670,000，大约 5 亿 4 千多万。

耗时(秒)：3.533。

查询列数：4。

结果行数：150。

⑥Query 3.2

SELECT
C_CITY,
S_CITY,
toYear(LO_ORDERDATE)ASyear,
sum(LO_REVENUE)ASrevenue
FROMlineorder_flat
WHERE(C_NATION='UNITEDSTATES')AND(S_NATION='UNITEDSTATES')AND(year>=1992)AND(year<=1997)
GROUPBY
C_CITY,
S_CITY,
year
ORDERBY
yearASC,
revenueDESC

┌─C_CITY─────┬─S_CITY─────┬─year─┬────revenue─┐
│UNITEDST6│UNITEDST6│1992│5694246807│
│UNITEDST0│UNITEDST0│1992│5676049026│
│..........│..........│....│..........│
│UNITEDST9│UNITEDST9│1997│4836163349│
│UNITEDST9│UNITEDST5│1997│4769919410│
└────────────┴────────────┴──────┴────────────┘

600rowsinset.Elapsed:1.000sec.Processed546.67millionrows,5.56GB(546.59millionrows/s.,5.56GB/s.)

扫描行数：546,670,000，大约 5 亿 4 千多万。

耗时(秒)：1.00。

查询列数：4。

结果行数：600。

⑦Query 4.1

SELECT
toYear(LO_ORDERDATE)ASyear,
C_NATION,
sum(LO_REVENUE-LO_SUPPLYCOST)ASprofit
FROMlineorder_flat
WHERE(C_REGION='AMERICA')AND(S_REGION='AMERICA')AND((P_MFGR='MFGR#1')OR(P_MFGR='MFGR#2'))
GROUPBY
year,
C_NATION
ORDERBY
yearASC,
C_NATIONASC

┌─year─┬─C_NATION──────┬────────profit─┐
│1992│ARGENTINA│1041983042066│
│1992│BRAZIL│1031193572794│
│....│......│............│
│1998│PERU│603980044827│
│1998│UNITEDSTATES│605069471323│
└──────┴───────────────┴───────────────┘

35rowsinset.Elapsed:5.066sec.Processed600.04millionrows,8.41GB(118.43millionrows/s.,1.66GB/s.)

扫描行数：600,040,000，大约 6 亿。

耗时(秒)：5.066。

查询列数：4。

结果行数：35。

⑧Query 4.2

SELECT
toYear(LO_ORDERDATE)ASyear,
S_NATION,
P_CATEGORY,
sum(LO_REVENUE-LO_SUPPLYCOST)ASprofit
FROMlineorder_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'))
GROUPBY
year,
S_NATION,
P_CATEGORY
ORDERBY
yearASC,
S_NATIONASC,
P_CATEGORYASC

┌─year─┬─S_NATION──────┬─P_CATEGORY─┬───────profit─┐
│1997│ARGENTINA│MFGR#11│102369950215│
│1997│ARGENTINA│MFGR#12│103052774082│
│....│.........│.......│............│
│1998│UNITEDSTATES│MFGR#24│60779388345│
│1998│UNITEDSTATES│MFGR#25│60042710566│
└──────┴───────────────┴────────────┴──────────────┘

100rowsinset.Elapsed:0.826sec.Processed144.42millionrows,2.17GB(174.78millionrows/s.,2.63GB/s.)

扫描行数：144,420,000，大约 1 亿 4 千多万。

耗时(秒)：0.826。

查询列数：4。

结果行数：100。

性能测试结果汇总

如下图：

在当前软硬件环境下，扫描 6 亿多行数据，常见的分析语句首次运行最慢在 8 秒左右能返回结果。

相同的分析逻辑更换条件再次查询的时候效率有明显的提升，可以缩短到 1 秒左右。

如果只是简单的列查询没有加减乘除、聚合等逻辑，扫描全表 6 亿多行数据首次查询基本可以在 2 秒内执行完成。

作者：LifeLogs

编辑：陶家龙

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