clickhouse相关

最新推荐文章于 2023-04-20 09:20:08 发布

@特立独行的猫

最新推荐文章于 2023-04-20 09:20:08 发布

阅读量911

点赞数

文章标签：数据库 database

什么是ClickHouse？

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。
在这里插入图片描述

组件介绍

clickhouse-server ClickHouse的Server端，也就是CK数据库的核心程序，相当于mysqld命令，提供数据库服务端
clickhouse-client ClickHouse自带的client端，提供命令行的交互操作方式，来连接服务端，相当于mysql命令

ClickHouse的特性

真正的列式数据库管理系统
数据压缩，压缩比可达到8：1
数据的磁盘存储
多核心并行处理,ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询
多服务器分布式处理,数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成，查询可以并行地在所有shard上进行处理。这些对用户来说是透明的
支持SQL
实时的数据更新,ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中。因此，数据可以持续不断地高效的写入到表中，并且写入的过程中不会存在任何加锁的行为。
索引,按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找
适合在线查询
不支持事务
目前不支持存储过程

基本介绍

引擎

CK里的引擎有十几个，这里只推荐3个：

引擎	介绍
MergeTree	是CK里最Advanced的引擎，性能超高，单机写入可以达到50w峰值，查询性能非常快
ReplicatedMergeTree	基于MergeTree，同时引入ZK，做了复制
Distributed	分布式引擎，本身不存储数据，可认为就是一张View，如果写入，会把请求丢到集群里的节点（有算法控制），如果查询，会帮你做查询转发再聚合返回

数据类型

关键字区分大小写

整型

Int Ranges
Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]
Uint Ranges
UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]

浮点型

Float32 - float
Float64 - double

Boolean类型

没有Boolean类型，可以用整型的0或1代替
Clickhouse的字段默认是不允许为NULL的，如果数据有可能为NULL，需要将字段定义为类似Nullable(Int64)的类型。

字符串

任意长度的字符串。长度不限。该值可以包含任意字节集，包括空字节。String类型替换了其他DBMS类型的VARCHAR，BLOB，CLOB和其他类型。

时间类型

Date 接受年-月-日的字符串比如 ‘2019-12-16’
Datetime 接受年-月-日时:分:秒的字符串比如 ‘2019-12-16 20:50:10’
Datetime64 接受年-月-日时:分:秒.亚秒的字符串比如 ‘2019-12-16 20:50:10.66’

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ENGINE - 引擎名
PARTITION BY — 分区键，要按月分区，可以使用表达式 toYYYYMM(date_column) ，分区名的格式会是 “YYYYMM” 。
SETTINGS — 控制 MergeTree 行为的额外参数,默认8192

primary key主键

clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。
主键的设定主要依据是查询语句中的where 条件。
根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全表扫描。
index granularity：
直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。
稀疏索引
稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

order by

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by是MergeTree中唯一一个必填项，甚至比primary key 还重要。如果没有使用 PRIMARY KEY 显式的指定主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple()。
要求：主键必须是order by字段的前缀字段。
比如order by 字段是 (id,name) 那么主键必须是id 或者(id,name)

索引类型

主键索引
与传统数据库不同，主键索引是可以相同的。主键索引存储的是主键值和block，且数据是按照主键进行排序的。若查询条件中含有主键值，则会先根据主键索引得到可能的granule范围
分区键索引
记录当前分区下分区字段对应原始数据的最小和最大值。用于查询时迅速排除不需要的分区
跳数索引
普通索引，是主键索引的一种补充能力

分区

MergeTree的分区目录是在写入数据的过程中被创建出来，每insert一次，就会创建一批次分区目录。也就是说如果仅创建表结构，是不会创建分区目录的，因为没有数据。

MergeTree数据分区目录命名规则
其规则为 – PartitionID_MinBlockNum_MaxBlockNum_Level
比如 202002_4_4_0 其中 202002 是分区ID ，4_4 对应的是
最小的数据块编号和最大的数据块编号，最后的_0 表示目前分区合并的层级。

PartitionID:该值由 insert 数据时分区键的值来决定。分区键支持使用任何一个或者多个字段组合表达式
MinBlockNum 和 MaxBlockNum: BlockNum 是一个整型的自增长型编号，该编号在单张MergeTree表中从1开始全局累加，当有新的分区目录创建后，该值就加1，对新的分区目录来讲，MinBlockNum 和 MaxBlockNum 取值相同。例如上面示例数据为202002_1_1_0 202002_1_5_1，但当分区目录进行合并后，取值规则会发生变化, MinBlockNum 取同一分区所欲目录中最新的 MinBlockNum值。MaxBlockNum 取同一分区内所有目录中的最大值。
表示合并的层级。相当于某个分区被合并的次数，它不是以表全局累加，而是以分区为单位，初始创建的分区，初始值为0，相同分区ID发生合并动作时，在相应分区内累计加1

随着数据的写入MergeTree 存储引擎会很多分区目录。如果分区目录数太多怎么办？
因为Clickhouse 的MergeTree 存储引擎是基于 LSM 实现的。MergeTree可以通过分区合并将属于相同分区的多个目录合并为一个新的目录(官方描述在10到15分钟内会进行合并，也可直接执行optimize语句)，已经存在的旧目录（也即system.parts表中activie为0的分区)在之后某个时刻通过后台任务删除(默认8分钟).

分片

通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上。再通过Distributed表引擎把数据拼接起来一同使用。

副本

分片的备份，副本的主要目的是防止数据丢失，增加数据存储的冗余

sql操作

update和delete
每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

删除操作
alter table t_order_smt delete where sku_id =‘sku_001’;
修改操作
alter table t_order_smt
update total_amount=toDecimal32(2000.00,2)
where id =102;

性能测试

EXPLAIN ANALYSE select count(*) from tb_cfg_user_resident;

输出执行计划：

Aggregate  (cost=191021.86..191021.87 rows=1 width=8)
  Rows out:  1 rows with 1317 ms to end, start offset by 21 ms.
  ->  Gather Motion 4:1  (slice1; segments: 4)  (cost=191021.78..191021.84 rows=1 width=8)
        Rows out:  4 rows at destination with 792 ms to first row, 1317 ms to end, start offset by 21 ms.
        ->  Aggregate  (cost=191021.78..191021.79 rows=1 width=8)
              Rows out:  Avg 1.0 rows x 4 workers.  Max 1 rows (seg0) with 1092 ms to end, start offset by 60 ms.
              ->  Append  (cost=0.00..167062.02 rows=2395976 width=0)
                    Rows out:  Avg 2403167.5 rows x 4 workers.  Max 2406719 rows (seg2) with 0.112 ms to first row, 997 ms to end, start offset by 38 ms.
                    ->  Seq Scan on tb_cfg_user_resident_1_prt_20200811 tb_cfg_user_resident  (cost=0.00..164398.50 rows=2383088 width=0)
                          Rows out:  Avg 2383031.2 rows x 4 workers.  Max 2386551 rows (seg2) with 0.112 ms to first row, 771 ms to end, start offset by 38 ms.
                    ->  Seq Scan on tb_cfg_user_resident_1_prt_20201117 tb_cfg_user_resident  (cost=0.00..0.00 rows=1 width=0)
                          Rows out:  0 rows (seg0) with 0.020 ms to end, start offset by 1129 ms.
                    ->  Seq Scan on tb_cfg_user_resident_1_prt_20200629 tb_cfg_user_resident  (cost=0.00..2631.28 rows=12732 width=0)
                          Rows out:  Avg 16821.0 rows x 4 workers.  Max 17030 rows (seg1) with 17 ms to first row, 18 ms to end, start offset by 792 ms.
                    ->  Seq Scan on tb_cfg_user_resident_1_prt_20200520 tb_cfg_user_resident  (cost=0.00..32.24 rows=156 width=0)
                          Rows out:  Avg 3315.2 rows x 4 workers.  Max 3410 rows (seg2) with 0.023 ms to first row, 0.392 ms to end, start offset by 1271 ms.
                    ->  Seq Scan on tb_cfg_user_resident_1_prt_default tb_cfg_user_resident  (cost=0.00..0.00 rows=1 width=0)
                          Rows out:  0 rows (seg0) with 0.006 ms to end, start offset by 1272 ms.
Slice statistics:
  (slice0)    Executor memory: 223K bytes.
  (slice1)    Executor memory: 241K bytes avg x 4 workers, 241K bytes max (seg0).
Statement statistics:
  Memory used: 128000K bytes
Optimizer status: legacy query optimizer
Total runtime: 1338.763 ms

CK性能测试

单机环境

[hmaster@hadoop-dn08 clickhouse-server]$ clickhouse-client -h 192.168.1.144 -u default --password v16+jS/d --send_logs_level=trace <<<"SELECT OriginCityName,DestCityName, count(*) AS flights FROM (select OriginCityName,DestCityName from upos_city_main.ontime limit 100000000) t1   GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20 ;" > /dev/null

没有使用索引

执行计划输出结果：


[hadoop-dn08] 2021.03.22 13:46:19.842678 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Debug> executeQuery: (from [::ffff:192.168.1.144]:54402) SELECT OriginCityName, DestCityName, count(*) AS flights FROM (SELECT OriginCityName, DestCityName FROM upos_city_main.ontime LIMIT 100000000) AS t1 GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20
[hadoop-dn08] 2021.03.22 13:46:19.842878 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> AccessRightsContext (default): Access granted: SELECT(OriginCityName, DestCityName) ON upos_city_main.ontime
[hadoop-dn08] 2021.03.22 13:46:19.843050 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> AccessRightsContext (default): Access granted: SELECT(OriginCityName, DestCityName) ON upos_city_main.ontime
[hadoop-dn08] 2021.03.22 13:46:19.843302 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Debug> upos_city_main.ontime (SelectExecutor): Key condition: unknown
[hadoop-dn08] 2021.03.22 13:46:19.843326 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Debug> upos_city_main.ontime (SelectExecutor): MinMax index condition: unknown
[hadoop-dn08] 2021.03.22 13:46:19.843593 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Debug> upos_city_main.ontime (SelectExecutor): Selected 117 parts by date, 117 parts by key, 19474 marks to read from 117 ranges
[hadoop-dn08] 2021.03.22 13:46:19.845505 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> upos_city_main.ontime (SelectExecutor): Reading approx. 158475380 rows with 8 streams
[hadoop-dn08] 2021.03.22 13:46:19.845651 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hadoop-dn08] 2021.03.22 13:46:19.845699 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hadoop-dn08] 2021.03.22 13:46:19.847381 [ 23715 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> AggregatingTransform: Aggregating
[hadoop-dn08] 2021.03.22 13:46:19.848165 [ 23715 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> Aggregator: Aggregation method: serialized
[hadoop-dn08] 2021.03.22 13:46:23.369689 [ 23715 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> AggregatingTransform: Aggregated. 100000000 to 3169 rows (from 4236.690 MiB) in 3.524 sec. (28377170.631 rows/sec., 1202.253 MiB/sec.)
[hadoop-dn08] 2021.03.22 13:46:23.369714 [ 23715 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Trace> Aggregator: Merging aggregated data
[hadoop-dn08] 2021.03.22 13:46:23.405118 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Information> executeQuery: Read 100164550 rows, 4.14 GiB in 3.562 sec., 28117179 rows/sec., 1.16 GiB/sec.
[hadoop-dn08] 2021.03.22 13:46:23.405141 [ 18289 ] {3c979e21-4e1d-451a-accd-02040da04c83} <Debug> MemoryTracker: Peak memory usage (for query): 34.75 MiB.

排序键索引没有发挥任何作用，查询性能主要就是靠ClickHouse牛逼的数据并行扫描能力和高效的列式计算引擎在硬抗，这也从侧面反应出ClickHouse在OLAP场景下的绝对性能优势，没有任何索引依旧可以很快。

Key condition: unknown ---- 该SQL没有使用主键索引
MinMax index condition: unknown — 该SQL没有使用分区索引
Selected 117 parts by date, 117 parts by key, 19474 marks to read from 117 ranges — 该SQL查询，共扫描了所有的117个分区目录，共计19474个MarkRange
Read 100164550 rows, 4.14 GiB in 3.562 sec., 28117179 rows/sec., 1.16 GiB/s — 该查询总共读取了100164550行数据(全表)，共4.14 G:
MemoryTracker: Peak memory usage (for query): 34.75 MiB. — 该查询峰值消耗了34.75M内存:

使用索引

[hmaster@hadoop-dn08 lib]$ clickhouse-client -h 192.168.1.144 -u default --password v16+jS/d --send_logs_level=trace <<<" SELECT OriginCityName, DestCityName, count(*) AS flights FROM (SELECT OriginCityName, DestCityName FROM upos_city_main.ontime where FlightDate = '1993-10-01' LIMIT 100000000 ) AS t1 GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20;" > /dev/null 
[hadoop-dn08] 2021.03.24 14:52:23.548241 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> executeQuery: (from [::ffff:192.168.1.144]:23602) SELECT OriginCityName, DestCityName, count(*) AS flights FROM (SELECT OriginCityName, DestCityName FROM upos_city_main.ontime WHERE FlightDate = '1993-10-01' LIMIT 100000000) AS t1 GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20
[hadoop-dn08] 2021.03.24 14:52:23.548928 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "FlightDate = '1993-10-01'" moved to PREWHERE
[hadoop-dn08] 2021.03.24 14:52:23.549007 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AccessRightsContext (default): List of all grants: GRANT SHOW, EXISTS, SELECT, INSERT, ALTER, CREATE, CREATE TEMPORARY TABLE, DROP, TRUNCATE, OPTIMIZE, KILL, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE, CREATE POLICY, ALTER POLICY, DROP POLICY, CREATE QUOTA, ALTER QUOTA, DROP QUOTA, ROLE ADMIN, SYSTEM, dictGet(), TABLE FUNCTIONS ON *.*, SELECT ON system.*
[hadoop-dn08] 2021.03.24 14:52:23.549018 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AccessRightsContext (default): Settings: readonly=0, allow_ddl=1, allow_introspection_functions=0
[hadoop-dn08] 2021.03.24 14:52:23.549025 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AccessRightsContext (default): List of all grants: GRANT SHOW, EXISTS, SELECT, INSERT, ALTER, CREATE, CREATE TEMPORARY TABLE, DROP, TRUNCATE, OPTIMIZE, KILL, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE, CREATE POLICY, ALTER POLICY, DROP POLICY, CREATE QUOTA, ALTER QUOTA, DROP QUOTA, ROLE ADMIN, SYSTEM, dictGet(), TABLE FUNCTIONS ON *.*, SELECT ON system.*
[hadoop-dn08] 2021.03.24 14:52:23.549033 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AccessRightsContext (default): Access granted: SELECT(FlightDate, OriginCityName, DestCityName) ON upos_city_main.ontime
[hadoop-dn08] 2021.03.24 14:52:23.549184 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "FlightDate = '1993-10-01'" moved to PREWHERE
[hadoop-dn08] 2021.03.24 14:52:23.549242 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AccessRightsContext (default): Access granted: SELECT(FlightDate, OriginCityName, DestCityName) ON upos_city_main.ontime
[hadoop-dn08] 2021.03.24 14:52:23.549497 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> upos_city_main.ontime (SelectExecutor): Key condition: (column 1 in [8674, 8674])
[hadoop-dn08] 2021.03.24 14:52:23.549515 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> upos_city_main.ontime (SelectExecutor): MinMax index condition: (column 0 in [8674, 8674])
[hadoop-dn08] 2021.03.24 14:52:23.549567 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> upos_city_main.ontime (SelectExecutor): Selected 4 parts by date, 4 parts by key, 24 marks to read from 4 ranges
[hadoop-dn08] 2021.03.24 14:52:23.549707 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> upos_city_main.ontime (SelectExecutor): Reading approx. 186900 rows with 4 streams
[hadoop-dn08] 2021.03.24 14:52:23.549820 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hadoop-dn08] 2021.03.24 14:52:23.549862 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hadoop-dn08] 2021.03.24 14:52:23.601034 [ 23715 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AggregatingTransform: Aggregating
[hadoop-dn08] 2021.03.24 14:52:23.601057 [ 23715 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> Aggregator: Aggregation method: serialized
[hadoop-dn08] 2021.03.24 14:52:23.607573 [ 23715 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> AggregatingTransform: Aggregated. 177192 to 3068 rows (from 7.505 MiB) in 0.058 sec. (3072747.281 rows/sec., 130.144 MiB/sec.)
[hadoop-dn08] 2021.03.24 14:52:23.607592 [ 23715 ] {71192d3a-387d-471c-a42c-948cff297d92} <Trace> Aggregator: Merging aggregated data
[hadoop-dn08] 2021.03.24 14:52:23.607958 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Information> executeQuery: Read 186900 rows, 8.27 MiB in 0.060 sec., 3131595 rows/sec., 138.55 MiB/sec.
[hadoop-dn08] 2021.03.24 14:52:23.607976 [ 16558 ] {71192d3a-387d-471c-a42c-948cff297d92} <Debug> MemoryTracker: Peak memory usage (for query): 4.11 MiB.

首先，WHERE子句被自动优化成了PREWHERE子句:
其次，分区索引终于被启动了:
借助分区索引，这次查询只需要扫描4个分区目录，减少了113个分区:

多表关联

表名	数据量
customer	3千万
lineorder	4亿
part	2百万
supplier	2百万

运行结果分析：

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.
(三表联合查询) SELECT sum(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, P_BRAND FROM lineorder 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	118.16 million rows/s,254.14 MB/s
(四表联合查询,inner join调换顺序)SELECT toYear(l.LO_ORDERDATE) AS year, sum(l.LO_REVENUE l.LO_SUPPLYCOST) AS profit FROM lineorder 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 sec	1.02 billion rows, 21.81 GB	14.04 million rows/s., 300.97 MB/s

性能测试总结：

在count 方面，速度很快，消耗内存较大
在group by 方面，速度很快，消耗内存很
在 join 方面，性能较弱，消耗内存、CPU较大，不适合做多表关联查询
并发较小，官网查询建议100 Queries / second，所以不适合做业务型高并发查询

官方的性能测试对比报告参见：https://clickhouse.yandex/benchmark.html

ClickHouse分布式集群方案

集群是副本和分片的基础，它将ClickHouse的服务拓扑由单节点延伸到多个节点，但它并不像Hadoop生态的某些系统那样，要求所有节点组成一个单一的大集群。ClickHouse的集群配置非常灵活，用户既可以将所有节点组成一个单一集群，也可以按照业务的诉求，把节点划分为多个小的集群。在每个小的集群区域之间，它们的节点、分区和副本数量可以各不相同
在这里插入图片描述

方案一：MergeTree + Distributed

建表方式
本地表：数据存储在建表节点的本地
CREATE TABLE db.tb (date Date, ……) ENGINE = MergeTree(date, (date, hour, datetime), 8192)

分布式表：查询这个表，引擎自动把整个集群数据计算后返回
CREATE TABLE db.tb_all (date Date, ……) ENGINE = Distributed(bip_ck_cluster, ‘ck_cluster’, ‘test’, rand())"

架构解析

MergeTree + Distributed的分布式架构方案，利用的是Distributed表的特性+MergeTree表的特性，分布式表不存储数据，数据来自本地表，将分布式表的数据分为3个shard，每台节点存储三分之一的数据，用户查询的时候是从分布式表所在的节点聚合从Ck1，CK2，CK3的查询结果，然后返回用户，写入数据可以写入分布式表，当然这样的写入方式问题很多，一般是禁止写入分布式表的，那么选择写入本地表的化，需要将数据轮询或者其他方式，将数据分散写入Ck1，CK2，CK3，当然你也可以只写入其中一台，那么使用方式就是单机版的。
1：优势：架构简单，可以单机使用，可以分布式使用，关键在于表引擎的选择，并行的查询分布式表，性能非常棒
2：问题：
(1)本地表+分布式表，分布式表如果某个节点当机就会丢失数据，用户查询服务就会报错，如果节点磁盘损坏，那么数据将大概率丢失，无法恢复，即使恢复也会付出极大的成本
(2)对于查询节点的选择需要慎重的考虑，毕竟需要聚合所有查询节点的结果
在这里插入图片描述

方案二：MergeTree + Distributed+集群复制

考虑数据的安全性，设置了副本
架构解析
分布式架构2采用了架构1的特点和解决了架构1的问题，数据安全性得到了解决，集合CLickHouse集群的复制，有了副本，3个shard各自拥有三分之一的数据，每个shard有2个副本，数据一样。其中CK1的Shard有两副本，分别在CK1，CK2；CK2的shard也有两副本，分别在CK2，CK3；CK3的shard也是两副本，分别在CK1和CK3
1：优势：数据的安全性有了保障，每一个shard有两个副本；数据的查询的并行度没有改变，但是因为副本的存在，shard节点数据的查询选择性多了。即使CK1挂了，不影响集群的查询服务
2：问题：
每一个shard需要同时负责分片和副本的数据写入工作，它很有可能称为写入的单点瓶颈。
在这里插入图片描述

方案三：ReplicatedMergeTree + Distributed

建表方式
本地表：
CREATE TABLE db.tb (date Date, ……) ENGINE = ReplicatedMergeTree(’/clickhouse/db/tb/name’, ‘ck_name’, date, (date, hour, datetime), 8192)
分布式表：
CREATE TABLE db.tb_all (date Date, ……) ENGINE = Distributed(bip_ck_cluster, ‘test’, ‘test’, rand())"

架构解析
ReplicatedMergeTree + Distributed的架构把MergeTree换成了ReplicatedMergeTree，本质上是将副本的数据同步的策略，从基于Cluster的方式换成了基于复制表引擎+Zookeeper的方式，基于ReplicatedMergeTree + Distributed的架构方案，在查询并行度，数据的安全性，副本的安全性，数据的一致性上都考虑的比较好，也避免了社区提出的DistrDirMonitor的故障问题
(1)优点：ReplicatedMergeTree里，共享同一个ZK路径的表，会相互，注意是，相互同步数据，数据安全，查询性能不会有太大的问题
(2)问题：
1)需要注意：写本地表，读分布式表
2)结合业务及数据的特性及所需的机器的资源，合理的选择分布式表的建表的CK节点
在这里插入图片描述

ClickHouse分布式集群常见方案分析总结

基于ClickHouse的集群的常见方案，结合业界的架构方案，优质的选择是基于ReplicatedMergeTree + Distributed的集群架构方案，也是分布式高可用的集群架构方案，但是在使用该集群架构的过程中，需要注意：

写表的方式：写本地表，读分布式表
由于分布式表的逻辑简单，仅仅是转发请求，所以在转发安全性上，会有风险，并且rand的方式，可能会造成不均衡，业界建议，通过DNS轮训，写本地表，这样最保险和均衡
统一的建表，表管理入口,CK的分布式，完全依赖配置文件，即每个节点，都共享同样的配置文件，建表要区分集群，又要区分副本，建议写一个脚本来统一建表，或者开发一个可视化的页面，操作管理CK表
建议结合查询的负载均衡做，分布式查询的节点可以在每一个节点都建分布式表，查询的选择性更多

clickhouse应用场景

绝大多数请求都是用于读访问的
数据需要以大批次（大于1000行）进行更新，不是单行更新；或者根本没有更新操作
数据只是添加到数据库，没有必要修改
读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
表很“宽”，即表中包含大量的列
查询频率相对较低（通常每台服务器每秒查询数百次或更少）
对于简单查询，允许大约50毫秒的延迟
列的值是比较小的数值和短字符串（例如，每个>URL只有60个字节）
在处理单个查询时需要高吞吐量（每台服务器每秒>高达数十亿行）
不需要事务
数据一致性要求较低
每次查询中只会查询一个大表。除了一个大表，其>余都是小表
查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

Clickhouse 导入MySQL中的表定义和数据

--导入数据：
CREATE TABLE FactSaleOrders ENGINE = MergeTree ORDER BY OrderID AS SELECT * FROM mysql('10.42.134.136:4000', 'dw', 'FactSaleOrders', 'root', 'xyz');
 
CREATE TABLE FactSaleOrders
ENGINE = MergeTree
ORDER BY OrderID AS
SELECT *
FROM mysql('10.42.134.136:4000', 'dw', 'FactSaleOrders', 'root', 'xyz')

前提条件：
MySQL的主键必须为not null
10.42.134.136:4000 为MySQL的IP地址和端口号
dw 为MySQL的库，作为数据源的
FactSaleOrders 为MySQL的表，源表
root为MySQL的账号
xyz为MySQL的账号对应的密码账号需要具备远程连接访问的权限。
注意：
order by 后的字段大小写要和MySQL的大小写一样
主键列在MySQL中必须定义为NOT NULL
导入速度和mysql的列有关，若列较少导入的更快，和硬件的读取写入速度，网络带宽也有影响。
支持从TiDB和MySQL直接读取数据。