6亿数据秒级查询,ClickHouse太快了!

沙海
沙海
沙海
735
文章
2
评论
2021年4月4日02:28:46
评论
3 7466字阅读24分53秒
摘要

6亿数据秒级查询,ClickHouse太快了! 点击关注 ? 芋道源码

6亿数据秒级查询,ClickHouse太快了!

点击关注 ? 芋道源码

点击上方“芋道源码”,选择“设为星标

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 8:55 更新文章,每天掉亿点点头发...

源码精品专栏

 

来源:cnblogs.com/asimov/p/14546106.html

6亿数据秒级查询,ClickHouse太快了!

clickhouse 在数据分析技术领域早已声名远扬,如果还不知道可以 点这里 了解下。

最近由于项目需求使用到了 clickhouse 做分析数据库,于是用测试环境做了一个单表 6 亿数据量的性能测试,记录一下测试结果,有做超大数据量分析技术选型需求的朋友可以参考下。

服务器信息

  • 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

按照官方指导造出了测试数据之后,先看一下数据量和空间占用情况。

数据量和空间占用

表名 列数 数据行数 原始大小 压缩大小 压缩率
supplier 6 200,000 11.07 MiB 7.53 MiB 68
customer 7 3,000,000 168.83 MiB 114.72 MiB 68
part 8 1,400,000 34.29 MiB 24.08 MiB 70
lineorder 16 600,037,902 24.03 GiB 16.67 GiB 69
lineorder_flat 37 688,552,212 111.38 GiB 61.05 GiB 55

可以看到 clickhouse 的压缩率很高,压缩率都在 50 以上,基本可以达到 70 左右。数据体积的减小可以非常有效的减少磁盘空间占用、提高 I/O 性能,这对整体查询性能的提升非常有效。

supplier、customer、part、lineorder 为一个简单的「供应商-客户-订单-地区」的星型模型,lineorder_flat 为根据这个星型模型数据关系合并的大宽表,所有分析都直接在这张大宽表中执行,减少不必要的表关联,符合我们实际工作中的分析建表逻辑。

以下性能测试的所有分析 SQL 都在这张大宽表中运行,未进行表关联查询。

查询性能测试详情

Query 1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenueFROM lineorder_flatWHERE (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 revenueFROM lineorder_flatWHERE (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,000775万

耗时(秒):0.040

查询列数:2

返回行数:1

Query 2.1

SELECT    sum(LO_REVENUE),    toYear(LO_ORDERDATE) AS year,    P_BRANDFROM lineorder_flatWHERE (P_CATEGORY = 'MFGR#12') AND (S_REGION = 'AMERICA')GROUP BY    year,    P_BRANDORDER 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_BRANDFROM lineorder_flatWHERE ((P_BRAND >= 'MFGR#2221') AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA')GROUP BY    year,    P_BRANDORDER 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 revenueFROM lineorder_flatWHERE (C_REGION = 'ASIA') AND (S_REGION = 'ASIA') AND (year >= 1992) AND (year <= 1997)GROUP BY    C_NATION,    S_NATION,    yearORDER 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 revenueFROM lineorder_flatWHERE (C_NATION = 'UNITED STATES') AND (S_NATION = 'UNITED STATES') AND (year >= 1992) AND (year <= 1997)GROUP BY    C_CITY,    S_CITY,    yearORDER 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 profitFROM lineorder_flatWHERE (C_REGION = 'AMERICA') AND (S_REGION = 'AMERICA') AND ((P_MFGR = 'MFGR#1') OR (P_MFGR = 'MFGR#2'))GROUP BY    year,    C_NATIONORDER 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 profitFROM lineorder_flatWHERE (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_CATEGORYORDER 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

性能测试结果汇总

查询语句 SQL简要说明 扫描行数 返回行数 查询列数 耗时(秒)
Q1.1 乘积、汇总、4个条件、首次运行 91,010,000 1 2 0.242
Q1.2 Q1.1增加1个条件运行 7,750,000 1 2 0.040
Q2.1 汇总、函数、2列分组、2列排序、首次运行 600,040,000 280 3 8.558
Q2.2 Q2.1增加1个条件运行 600,040,000 56 3 1.242
Q3.1 汇总、函数、3列分组、2列排序、首次运行 546,670,000 150 4 3.533
Q3.2 Q3.1更换条件运行 546,670,000 600 4 1
Q4.1 相减、汇总、函数、2列分组、2列排序、首次运行 600,040,000 35 4 5.006
Q4.2 Q4.1增加2个条件运行 144,420,000 100 4 0.826

在当前软硬件环境下,扫描 6 亿多行数据,常见的分析语句首次运行最慢在 8 秒左右能返回结果,相同的分析逻辑更换条件再次查询的时候效率有明显的提升,可以缩短到 1 秒左右,如果只是简单的列查询没有加减乘除、聚合等逻辑,扫描全表 6 亿多行数据首次查询基本可以在 2 秒内执行完成。

欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢

6亿数据秒级查询,ClickHouse太快了!

已在知识星球更新源码解析如下:

6亿数据秒级查询,ClickHouse太快了!

6亿数据秒级查询,ClickHouse太快了!

6亿数据秒级查询,ClickHouse太快了!

6亿数据秒级查询,ClickHouse太快了!

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 20 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

文章有帮助的话,在看,转发吧。谢谢支持哟 (*^__^*)

阅读原文

继续阅读
weinxin
资源分享QQ群
本站是一个IT技术分享社区, 会经常分享资源和教程; 分享的时代, 请别再沉默!
沙海
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: