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

运维 数据库运维 开发工具
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:

  1. 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

  1. SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue 
  2. FROM lineorder_flat 
  3. WHERE (toYear(LO_ORDERDATE) = 1993) AND ((LO_DISCOUNT >= 1) AND (LO_DISCOUNT <= 3)) AND (LO_QUANTITY < 25) 
  4.  
  5. ┌────────revenue─┐ 
  6. │ 44652567249651 │ 
  7. └────────────────┘ 
  8.  
  9. 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

  1. SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue 
  2. FROM lineorder_flat 
  3. WHERE (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.  
  9. 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

  1. SELECT  
  2.     sum(LO_REVENUE), 
  3.     toYear(LO_ORDERDATE) AS year
  4.     P_BRAND 
  5. FROM lineorder_flat 
  6. WHERE (P_CATEGORY = 'MFGR#12'AND (S_REGION = 'AMERICA'
  7. GROUP BY  
  8.     year
  9.     P_BRAND 
  10. ORDER 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.  
  22. 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

  1. SELECT  
  2.     sum(LO_REVENUE), 
  3.     toYear(LO_ORDERDATE) AS year
  4.     P_BRAND 
  5. FROM lineorder_flat 
  6. WHERE ((P_BRAND >= 'MFGR#2221'AND (P_BRAND <= 'MFGR#2228')) AND (S_REGION = 'ASIA'
  7. GROUP BY  
  8.     year
  9.     P_BRAND 
  10. ORDER 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.  
  22. 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

  1. SELECT  
  2.     C_NATION, 
  3.     S_NATION, 
  4.     toYear(LO_ORDERDATE) AS year
  5.     sum(LO_REVENUE) AS revenue 
  6. FROM lineorder_flat 
  7. WHERE (C_REGION = 'ASIA'AND (S_REGION = 'ASIA'AND (year >= 1992) AND (year <= 1997) 
  8. GROUP BY  
  9.     C_NATION, 
  10.     S_NATION, 
  11.     year 
  12. ORDER 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.  
  24. 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

  1. SELECT  
  2.     C_CITY, 
  3.     S_CITY, 
  4.     toYear(LO_ORDERDATE) AS year
  5.     sum(LO_REVENUE) AS revenue 
  6. FROM lineorder_flat 
  7. WHERE (C_NATION = 'UNITED STATES'AND (S_NATION = 'UNITED STATES'AND (year >= 1992) AND (year <= 1997) 
  8. GROUP BY  
  9.     C_CITY, 
  10.     S_CITY, 
  11.     year 
  12. ORDER 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.  
  24. 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

  1. SELECT  
  2.     toYear(LO_ORDERDATE) AS year
  3.     C_NATION, 
  4.     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit 
  5. FROM lineorder_flat 
  6. WHERE (C_REGION = 'AMERICA'AND (S_REGION = 'AMERICA'AND ((P_MFGR = 'MFGR#1'OR (P_MFGR = 'MFGR#2')) 
  7. GROUP BY  
  8.     year
  9.     C_NATION 
  10. ORDER 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.  
  22. 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

  1. SELECT  
  2.     toYear(LO_ORDERDATE) AS year
  3.     S_NATION, 
  4.     P_CATEGORY, 
  5.     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit 
  6. FROM lineorder_flat 
  7. WHERE (C_REGION = 'AMERICA'AND (S_REGION = 'AMERICA'AND ((year = 1997) OR (year = 1998)) AND ((P_MFGR = 'MFGR#1'OR (P_MFGR = 'MFGR#2')) 
  8. GROUP BY  
  9.     year
  10.     S_NATION, 
  11.     P_CATEGORY 
  12. ORDER 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.  
  25. 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

 

责任编辑:武晓燕 来源: 博客园
相关推荐

2020-08-19 12:29:35

留存分析数据工具

2019-02-19 08:46:57

物联网IOT物联网设备

2020-11-10 09:05:45

用户画像苏宁

2020-08-17 08:21:31

数据查询项目

2019-05-27 09:56:00

数据库高可用架构

2024-02-26 10:30:27

Biome开发前端

2024-01-22 06:55:09

BiomeWeb 应用Prettier

2020-09-10 17:41:14

ClickHouse数据引擎

2021-08-31 09:39:06

Python数据工具

2022-05-12 14:34:14

京东数据

2019-05-28 09:31:05

Elasticsear亿级数据ES

2015-04-09 14:26:07

2022-02-25 15:20:54

6G网络通信

2023-02-22 09:47:40

查询计算

2019-06-05 14:30:21

MySQL数据库索引

2021-11-24 15:16:02

Quick阿里云操作系统

2016-12-06 20:09:15

Freeline编译Android

2024-01-23 12:56:00

数据库微服务MySQL

2020-10-27 09:18:16

ClickHouse数据库架构
点赞
收藏

51CTO技术栈公众号