使用版本

 

20.10.3.30

云主机配置

 

数据及操作来源

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

表类型及数据量

表名

表结构

表定义语句

表数据量

customerCREATE TABLE customer\
(\
C_CUSTKEY UInt32,\
C_NAME String,\
C_ADDRESS String,\
C_CITY LowCardinality(String),\
C_NATION LowCardinality(String),\
C_REGION LowCardinality(String),\
C_PHONE String,\
C_MKTSEGMENT LowCardinality(String)\
)\
ENGINE = MergeTree ORDER BY (C_CUSTKEY);

30000000

lineorderCREATE TABLE lineorder\
(\
LO_ORDERKEY UInt32,\
LO_LINENUMBER UInt8,\
LO_CUSTKEY UInt32,\
LO_PARTKEY UInt32,\
LO_SUPPKEY UInt32,\
LO_ORDERDATE Date,\
LO_ORDERPRIORITY LowCardinality(String),\
LO_SHIPPRIORITY UInt8,\
LO_QUANTITY UInt8,\
LO_EXTENDEDPRICE UInt32,\
LO_ORDTOTALPRICE UInt32,\
LO_DISCOUNT UInt8,\
LO_REVENUE UInt32,\
LO_SUPPLYCOST UInt32,\
LO_TAX UInt8,\
LO_COMMITDATE Date,\
LO_SHIPMODE LowCardinality(String)\
)\
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

导数据的时候磁盘不够了，所以只导了

4085911421条数据。

其他表导入的时间都基本可以算秒级完成的，这个表的导入时间为：

Thu Nov 12 14:32:24 CST 2020
Thu Nov 12 15:38:53 CST 2020

用时1小时6分钟

partCREATE TABLE part\
(\
P_PARTKEY UInt32,\
P_NAME String,\
P_MFGR LowCardinality(String),\
P_CATEGORY LowCardinality(String),\
P_BRAND LowCardinality(String),\
P_COLOR LowCardinality(String),\
P_TYPE LowCardinality(String),\
P_SIZE UInt8,\
P_CONTAINER LowCardinality(String)\
)\
ENGINE = MergeTree ORDER BY P_PARTKEY;

2百万

supplierCREATE TABLE supplier\
(\
S_SUPPKEY UInt32,\
S_NAME String,\
S_ADDRESS String,\
S_CITY LowCardinality(String),\
S_NATION LowCardinality(String),\
S_REGION LowCardinality(String),\
S_PHONE String\
)\
ENGINE = MergeTree ORDER BY S_SUPPKEY;

2百万

 

 

创建一个新的联合表，数据从已有的表中获取：

表定义语句

创建时使用nmon

创建时间

处理行数

读磁盘速度

最后大小

CREATE TABLE lineorder_flat\
ENGINE = MergeTree\
PARTITION BY toYear(l.LO_ORDERDATE)\
ORDER BY (l.LO_ORDERDATE, l.LO_ORDERKEY) AS\
SELECT\
l.*,\
c.*,\
s.*,\
p.*\
FROM lineorder AS l\
ANY INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY\
ANY INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY\
ANY INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

 

对磁盘吞吐和cpu占用非常高，吞吐来说大部分时间是全部占用的

452.055 sec

4.12 billion rows,

178.49 GB

9.11 million rows/s.,

394.83 MB/s

1065161行

188.85M

 

 

在表中查到的数据大小和直接用ll -lh查到的不太一样：

 

查询SQL语句及执行时间

sql语句

查询时间

处理行数

读磁盘速度

截图

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue \
FROM lineorder \
WHERE toYear(LO_ORDERDATE) = 1993 \
AND LO_DISCOUNT BETWEEN 1 AND 3 \
AND LO_QUANTITY < 25;
1.832 sec

619.86 million rows

4.96 GB

338.31 million rows/s.,

2.71 GB/s.

 

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue \
FROM lineorder \
WHERE toYYYYMM(LO_ORDERDATE) = 199401 \
AND LO_DISCOUNT BETWEEN 4 AND 6 \
AND LO_QUANTITY BETWEEN 26 AND 35;

 

0.261 sec

52.70 million rows

421.51 MB

202.10 million rows/s.,

1.62 GB/s.

 

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue \
FROM lineorder \
WHERE toISOWeek(LO_ORDERDATE) = 6 \
AND toYear(LO_ORDERDATE) = 1994 \
AND LO_DISCOUNT BETWEEN 5 AND 7 \
AND LO_QUANTITY BETWEEN 26 AND 35;

 

0.357 sec

12.06 million rows

96.48 MB

33.83 million rows/s., 270.56 MB/s.

(三表联合查询)

SELECT sum(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, P_BRAND \
FROM lineorder l \
INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY)\
INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY)\
WHERE p.P_CATEGORY = 'MFGR#12' AND s.S_REGION = 'AMERICA' \
GROUP BY year, P_BRAND \
ORDER BY year, P_BRAND;

225.177 sec

4.09 billion rows

57.23 GB

18.16 million rows/s.,

254.14 MB/s

 

(四表联合查询)

SELECT toYear(l.LO_ORDERDATE) AS year, \

sum(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit \
FROM lineorder l\
INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY)\
INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY)\
INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY)\
WHERE c.C_REGION = 'AMERICA' \
AND s.S_REGION = 'AMERICA' \
AND (year = 1997 OR year = 1998) \
AND (p.P_MFGR = 'MFGR#1' OR p.P_MFGR = 'MFGR#2') \
GROUP BY year, s.S_NATION, p.P_CATEGORY \
ORDER BY year, s.S_NATION, p.P_CATEGORY;

61.644 sec1.02 billion rows, 21.81 GB

16.50 million rows/s.,

353.76 MB/s

 

(四表联合查询,inner join调换顺序)

SELECT toYear(l.LO_ORDERDATE) AS year, sum(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit \
FROM lineorder l\
INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY)\
INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY)\
INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY)\
WHERE c.C_REGION = 'AMERICA' \
AND s.S_REGION = 'AMERICA' \
AND (year = 1997 OR year = 1998) \
AND (p.P_MFGR = 'MFGR#1' OR p.P_MFGR = 'MFGR#2') \
GROUP BY year, s.S_NATION, p.P_CATEGORY \
ORDER BY year, s.S_NATION, p.P_CATEGORY;

72.457 sec1.02 billion rows, 21.81 GB14.04 million rows/s., 300.97 MB/s

(四表联合查询,inner join调换顺序)

SELECT toYear(l.LO_ORDERDATE) AS year, sum(l.LO_REVENUE - l.LO_SUPPLYCOST) AS profit \
FROM supplier s\
INNER JOIN lineorder l ON (s.S_SUPPKEY = l.LO_SUPPKEY)\
INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY)\
INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY)\
WHERE c.C_REGION = 'AMERICA' \
AND s.S_REGION = 'AMERICA' \
AND (year = 1997 OR year = 1998) \
AND (p.P_MFGR = 'MFGR#1' OR p.P_MFGR = 'MFGR#2') \
GROUP BY year, s.S_NATION, p.P_CATEGORY \
ORDER BY year, s.S_NATION, p.P_CATEGORY;

超内存，运行错误超内存，运行错误超内存，运行错误

可以看出，clickhouse并不会更改语句的执行顺序，做到优化，且在多表联合查询的测试下，比单表要慢一个档次。

 

# 数据仓库
Logo
天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

avatar 天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

avatar 天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能

avatar 天工造物局