一串烤肉

主题

官方文档 ​

https://clickhouse.com/docs/zh/

简介

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

什么是OLAP场景 ​

  • 绝大多数是读请求

  • 数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。

  • 已添加到数据库的数据不能修改。

  • 对于读取，从数据库中提取相当多的行，但只提取列的一小部分。

  • 宽表，即每个表包含着大量的列

  • 查询相对较少(通常每台服务器每秒查询数百次或更少)

  • 对于简单查询，允许延迟大约50毫秒

  • 列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)

  • 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)

  • 事务不是必须的

  • 对数据一致性要求低

  • 每个查询有一个大表。除了他以外，其他的都很小。

  • 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

同类产品性能对比

官方数据

安装部署 ​

直接安装

ClickHouse可以在任何具有x86_64，AArch64或PowerPC64LE CPU架构的Linux，FreeBSD或Mac OS X上运行。

参考官方文档

docker安装

服务器或者本机安装docker省略，可以根据自己的操作系统自行搜索

bash
# server端安装
docker pull clickhouse/clickhouse-server:21.3.20.1
# client端安装
docker pull clickhouse/clickhouse-client:21.3.20.1
# 启动命令，确保 $HOME/Data/clickhouse 该目录在当前用户已经构建 确保 --volume 不出问题
docker run -p 8123:8123 -p 9000:9000 -d --name clickhouse-server --ulimit nofile=262144:262144 --volume=$HOME/Data/clickhouse:/var/lib/clickhouse clickhouse/clickhouse-server:21.3.20.1

数据类型 ​

整形

clickhouse中的整型不像其他数据库中区分int，short，long等等这些类型，而 是统一表示固定长度的整数，包括有符号整型和无符号整型。统一定义为Int，后面带上数字表示占用的字节数

整型范围：

  • Int8 [-128:127] 占用8个字节，对应java中的byte

  • Int16 [-32768:32767] 占用16个字节，对应java中的short

  • Int32 [-2147483648:2147483647] 占用32个字节，对应java中的int

  • Int64 [-9223372036854775808:9223372036854775807] 占用64个字节，对 应java中的long

无符号整型范围

  • UInt8 [0:255]

  • UInt16 [0:65535]

  • UInt32 [0:4294967295]

  • UInt64 [0:18446744073709551615]

boolean布尔型

clickhouse中没有定义表示true和false的布尔类型数据，通常都是直接使用 UInt8

浮点型

  • Float32 - float

  • Float64 - double

官方建议尽量使用整型来存储数据，将固定精度的数字转换成为整数值。例如时间用毫秒为单位保存。这是因为使用浮点型有精度丢失问题。例如执行 select 1-0.9 得到的结果将是 0.09999999999999998 而不是0.1。

浮点型一般用于数据值比较小，不设计大量的统计计算，精度要求也不高的场景。例如保存商品的重量。但是对于精度要求比较高的金额，就极不建议使用浮点型。而应该用Decimal型。

Decimal型

有符号的浮点数，可以在加、减和乘法运算过程中保持精度。对于除法，最低有效数字将被抛弃(不进行四舍五入)。通常有三种声明: Decimal32(s)、 Decimal64(s)、Decimal128(s)。后面的s表示小数点后的数字位数。前面的32，64, 128表示浮点精度，决定可以有多少个十进制数字(包含小数位)，也就代表不同的取值范围。

数据在底层会采用与自身位宽相同的有符号整数存储。而现代CPU不支持128位 的数字，因此Decimal128上的操作需要由软件来进行模拟。所以Decimal128的运 算速度会明显慢于Decimal32\Decimal64。也就是说尽量少用Decimal128。

字符型

clickhouse的字符型数据使用String进行声明。这个字符串可以是任意长度的。 他可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他数据库中 的VARCHAR、BLOB、CLOB等类型。

clickhouse中没有编码的概念。字符串可以是任意的字节集，按他们原本的方式 进行存储和输出。对于不同的编码文本，clickhouse会有不同处理字符串的函数。 比如 length函数可以计算字符串包含的字节数组的长度，而lengthUTF8函数是假 设字符串以UTF-8编码，计算的字符串包含的Unicode字符的长度。

还有一个固定长度的字符串类型FixedString(N)，这个N就是要声明的字节数。 如果字符串包含的字节数不足N，将会对字符串末尾进行空字节填充。如果字符串包含的字节数大于N，将会抛出异常。可以用来保存一些例如手机号码、IP地址这一类 等长的规范数据。在实际开发中使用比较少。

枚举类型

包含Enum8和Enum16两种类型。Enum保存'string'=integer的对应关系。在 clickhouse中，尽管用户使用的是字符串常量，但所有罕有Enum数据类型的操作都是按照韩包含整数的值来执行的。这在性能方便比使用String数据类型更有效。 Enum后面的8和16也是对应的整数值integer的位宽。

数组类型

类型声明: array(T) 。表示一个由T类型元素组成的数组。T可以是任意类型，甚至也可以是数组类型。但是不建议使用多位数组，clickhouse对多维数组的支持有限。例如在MergeTree引擎中就不能存储多维数组。

示例：

sql
:)SELECT array(1,2) AS x,toTypeName(x)


┌─x─────┬─toTypeName(array(1,2))─┐
│[1,2]  │Array(UInt8)            │
└───────┴────────────────────────┘

时间类型

时间类型是每个数据库都要处理的类型。clickhouse的时间类型声明相对简单很 多。在clickhouse中有三种时间类型

  • Date 可以接受一个 年-月-日 格式的字符串。例如 '2021-10-13'。

  • Datetime 可以接受一个 年-月-日 时:分:秒 格式的字符串。例如'2021-10-13 20:50:10'。

  • Datatime64 可以接受一个 年-月-日 时:分:秒.毫秒 格式的字符串。例如 '2021- 10-13 20:50:10.232'。

可为空类型

绝大部分的基础类型都可以通过在前面添加一个Nullable()声明来允许接受Null空值。例如Nullable(Int8)类型的列可以存储Int8类型的值，没有值的行将存储 NULL。

Nullable类型字段不能包含在表索引中。并且使用Nullable几乎总是对性能产生负面影响，在设计数据库时要尽量避免使用Nullable。例如对于字符串，可以用空字符代替Null。而对于整型数据，可以用无业务意义的数字例如-1来表示Null

其他

clickhouse中还设计了很多非常有特色的数据类型，例如Geo,Map,Tuple,UUID 等类型。具体参见官方文档。

基础 ​

表引擎介绍 ​

clickhouse中的表引擎很多，有好几十种。整体可以分为四类。

  • MergeTree 合并树家族: 这是适用于高负载任务的最通用同时功能最强大的表引擎。这一类引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 是clickhouse默认的也是最为重要的引擎。

  • Log 日志系列: 具有最小功能的轻量级引擎。用于快速写入许多小表(最多约100 万行)，并在以后整体读取这些数据。例如常用的滚动日志。例如TinyLog引擎，以列文件的形式保存在磁盘上，不支持索引，没有并发控制，通常只用于练习。Integration Engines 集成引擎:用于与其他的数据存储与处理系统集成的引擎。通常可用来简化一些ETL的工作。例如同样有MySQL的表引擎，将对表的查 询语句转发到远程MySQL数据库中。另外，可以看到，clickhouse支持的集成表引擎比库引擎丰富很多。

  • **Special Engines 特别引擎:**用于其他特定功能的引擎。比如使用内存表、字典表等。

这其中，我们最应该关注的是MergeTree合并树家族，后续关于clickhouse表的分享也都基于MergeTree引擎。

MergeTree（合并树） ​

适用于高负载,最强大的表引擎

同构后续的后台数据处理,快速插入数据,然后应用规则在后台合并这些部分

支持数据复制、分区、辅助数据跳过索引以及其他引擎不支持的其他功能

种类

  • MergeTree

    • ReplacingMergeTree

    • SummingMergeTree

    • AggregatingMergeTree

    • CollapsingMergeTree

    • VersionedCollapsingMergeTree

    • GraphiteMergeTree

    • ReplicatedReplacingMergeTree

基于MergeTree引擎的建表语句:

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|TODISK'xxx'|TOVOLUME'xxx'],...]
[SETTINGS name=value,...]

对于MergeTree，SETTINGS中的大部分蚕食都有了默认值，可以不用设置。重点关注的就是以下几个配置。partition by 分区键，primary key 主键以及 order by 排序键。

partition by 分区键

分区键的作用

分区键的作用主要是降低数据扫描的范围，优化查询速度。例如，按天进行了分区，当查询的where条件中指定了日期条件，就只需要去扫描对应日期的数据，而不用进行全表扫描了。使用分区后，涉及到跨分区的查询操作，clickhouse 将会以分区为单位进行并行处理。在clickhouse中这是一个可选项，如果不填，相当于只用一个分区。

分区合并 ​

MergeTree引擎底层使用一种类似于LSM树的结构来保存数据。任何一次对数据的修改都会临时产生一个分区，而不会修改已有的分区。写入后的某个时刻， clickhouse会在后台自动执行合并操作。这个这个间隔时间是未知的，大概在 10~15分钟左右。如果等不及，也可以执行手动合并。合并指令

# optimizetablet_stockfinal;

# 注意 table_name 为实体表名称
optimize table <table_name> on CLUSTER <cluster_name> final;

order by 排序键

order by 排序键指定分区内的数据按照哪些字段排序进行有序保存。这是 MergeTree中唯一的一个必填项。

数据有序保存对于clickhouse底层的数据处理是相当重要的，在海量数据场景 下，实现快速检索、去重、汇总等计算都离不开数据有序性的支持。这里需要注意 的是，clickhouse的数据是分区内局部有序的。实际上这也比较好理解，因为 clickhouse对于数据的处理就是以分区作为最小维度的。

分区键的设置对于主键也是有影响的。在clickhouse中，如果不设置表的主键，他就会以排序键来对数据进行检索等数据处理。这里要注意的是，如果设置主键，主键必须是order by的前缀字段。例如order by 排序键设置为(id,sku_id)，那么主键只能是 id 或者是 (id,sku_id)。

primary key 主键

主键的作用是为了加快数据检索的。clickhouse中的主键与其他数据库有点不太 一样，他并不要求主键的数据具有唯一性。

我们之前已经看到，在clickhouse的metadata文件中保存的关于t_stock表的sql 语句。而在那个sql文件当中，clickhouse在我们自定义的建表语句之后，加了一个默认的参数 index granularity，指定了值是8192。这是clickhouse中主键的一个重要作用。

TTL 数据存活时间

TTL即Time To Live。 可以用来指定行存储的持续时间。MergeTree可以针对表或者列声明数据存活时间。设置TTL需要指定一个表达式来表示数据的存活时间。表达式中必须存在至少一个表示时间的Date或DateTime类型的列。比如 TTL date + INTERVAL 1 DAY 。也就是说，存活时间必须跟数据相关联。

列级TTL

可以在列上直接声明TTL规则。 例如下面的语句就可以声明total_amount字段的存活时间为create_time创建时间后的10秒钟。

CREATE TABLE example_table (
 d DateTime,
 a Int TTL d + INTERVAL 1 MONTH,
 b Int TTL d + INTERVAL 1 MONTH,
 c String
) ENGINE=MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

当列中的值过期时，clickhouse会将他们替换成该列数据类型的默认值。如果某个数据块中列的所有值都过期了，那么clickhouse会从文件系统中的数据块中直接删除这一列。

列式TTL不能用于主键。

表级TTL

设置表级TTL时，除了设置一个过期表达式之外，还可以配置一个数据移除规则。

CREATE TABLE example_table (
d DateTime,
a Int
)
ENGINE=MergeTree 
PARTITION BY toYYYYMM(d) 
ORDER BY d 
TTL d + INTERVAL 1 MONTH[DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

  • Delete - 删除过期的行 默认行为

  • TO DISK 'aaa' - 将数据块移动到磁盘'aaa'

  • TO VOLUME 'bbb' - 将数据块移动到卷 'bbb' GROUP BY - 聚合过期的行

后面的where 可以指定哪些过期的行为会被删除或聚合(不适用于数据移动)。

SAMPLE BY 数据抽样

数据抽样同样用于大数据分析，可以极大提升数据分析的性能。采样修饰符只能用在MergeTree的表中才有效，并且抽样表达式指定的列，必须包含在主键中。进 行了采样声明后，就可以在查询时进行采样查询。

表声明时指定了采样规则

SAMPLE BY intHash32(UserID)

查询时指定采样效率

SELECT Title,count(*)AS PageViews
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数 4 
WHERE CounterID=57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

这个采样查询是在满足条件的结果集中随机抽取10%的数据。

ReplacingMergeTree(重点) ​

ReplacingMergeTree。这个表引擎与MergeTree的不同之处在于他会删除排序值相同的重复项。这个去重的功能在实际开发中还是经常会要用到的。

但是要注意，ReplactingMergeTree的数据去重只会在数据合并期间进行。对应之前数据合并的示例就比较容易理解。正常情况下，数据合并是在后台一个不确定的时间进行，这个时间是无法预先规划的。当然如果确实需要，可以使用optimize 语句手动发起合并，但是这显然是不建议的，因为optimize语句会引发数据的大量读写，会严重影响数据库的性能。

所以，ReplacingMergeTree适用于在后台清除重复的数据用来节省空间，但是他并不保证没有重复的数据出现。也就是说他只保证数据的最终一致，而不能保证强一致。

使用ReplacingMergeTree的建表指令如下

CREATE TABLE [IF NOT EXISTS][db.]table_name[ON CLUSTER cluster] 
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)ENGINE=ReplacingMergeTree([ver]) [PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr] [SETTINGS name=value,...]

基本上跟MergeTree是差不多的。最大的区别就在于ReplactingMergeTree需要指定一个参数ver。这个参数就表示版本列。类型必须是UInt*,Date或者 DateTime。这是个可选的参数。

这个参数是用来指定数据去重的规则。可以想象，当数据集中出现了多条重复的数据，ReplacingMergeTree会在这一批重复数据中保存版本列的数据最大的那一条数据，而其他数据则标记为过期。如果没有指定版本列，则会默认保留最后插入的那一条数据。

对于ReplactingMergeTree需要注意的几个重点:

  • ReplacingMergeTree是按照order by 指定的排序键作为判断重复的标准。

  • 他的去重只限定在一个分区中，不能跨区去重。

  • 对于判断为重复的数据，保留版本字段最大的一条数据，如果没有指定版本值或者版本值也有重复的，就会保留最后插入的一条数据。

  • ReplacingMergeTree并不能始终保证数据是完全去重的。数据去重只会发生在同一批插入数据以及后台数据合并这两个时机。

数据修改 ​

数据修改对应update和delete操作。clickhouse也提供了这两个操作的能力，但是在clickhouse中，对数据的修改和删除是非常"重"的操作，因为对应的目标数据需要放弃原有的分区，重建新的临时分区，然后还要进行大量的合并。在语句执行过程中，只是将原有的数据打上逻辑上的删除标记，然后新增数据放入新的分区。直到触发分区合并的时候，才会删除旧的数据。频繁的update和delete操作会加大服务器的负担。

在clickhouse中，数据变更操作被称为Mutation查询，他被作为alter指令的一部分，即对表进行变更。实际上，你会发现，在官方文档上SQL部分都没有列出 UPDATE和DELETE语句。通常情况下，这类Mutation操作都要交由运维人员来完成。普通用户更多的只需要关注数据的查询与分析，尽量避免不必要的数据变更操作。

-- 删除操作
alter table t_stock delete where sku_id='sku_001';
-- 修改操作
alter tablet_stock update total_amount=toDecimal132(2000.00,2) where id=2;

数据查询 ​

  • 支持子查询

  • 支持各种JOIN查询。但是不建议使用。因为JOIN操作无法使用缓存。并且 clickhouse执行join操作的方式是将后面的表全部加载到内存中执行，优化不是很好。表很大时性能影响非常明显。

  • 支持With关键字创建临时表。

函数

表函数

表函数是clickhouse非常有特色的一类函数。顾名思义，就是可以像表一样使用的函数。

例如numbers函数，可以生成一组连续的整数

select * from numbers(10); --产生一组0~10的整数
select * fromnumbers(10,20); --从10开始，产生20个整数
select toDate('2021-01-01')+number as d from numbers(365); --产生2021-01-01 ~ 2021-12-31的日期序列

聚合函数

也就是group by 之后进行聚合。clickhouse提供了大量的聚合函数，除了 count，min，max，sum等这些常见的聚合函数外，甚至还包括了corr皮尔逊相关系数，rankCorr斯皮尔曼相关系数，simpleLinearRegression一维线性回归等机器学习中常用的聚合函数，功能相当的丰富。具体可以查看官方文档。

rankCorr计算的是斯皮尔曼相关系数。是用来计算两个数据列的相关 性。这是一个-1到1之间的值。值为1，表示两个数据列完全正相关，即第一个列中较大的值对应的第二个列中的值就越大。而-1表示两个 数据列负相关，即第一个列中较大的值对应第二个列中的值就越小。 例如，如果一个公司在多年发展过程中，投入越多，收入也越多，也就是投入与收入的相关系数大，那就可以认为这个公司是一个非常好的公司。在统计学中，这是一个很重要的指标。

另外，在group by操作上，clickhouse还支持with rollup\with cube\with totals，来进行统计聚合。

-- 按gourp by 的顺序，从右至左逐个去掉维度进行聚合。依次按照 (id,sku_id),(id),()分 组，对total_amount进行求和
select id,sku_id,sum(total_amount)from t_stock group by id,sku_id with rollup;
-- 按照goup by 的字段，互相组合进行聚合
select id,sku_id,sum(total_amount)from t_stock group by id,sku_id with cube;
-- 只按照group by的全字段，以及所有数据一起聚合。只按照 (id,sku_id),()两个分组进行 求和
select id,sku_id,sum(total_amount)from t_stock group by id,sku_id with totals;

进阶 ​

分布式表 ​

在clickhouse中，可以通过水平切分的方式，将完整的数据集切分成不同的分片。这些分片只保存一部分数 据，分布在不同的节点上。然后再通过Distributed表引擎将数据拼接起来作为一个完整的表使用。Distributed表引擎本身不存储数据，就有点像ShardingSphere与MySQL，只是一个中间件，通过分布式逻辑表来写入、分发、路由操作多台节点不 同分片的分布式数据。

之前分析过，clickhouse的单机性能是很强的。很多企业在实际运用过程中，会配置副本机制来做高可用，但是通常不会做分片，这样可以降低查询时的性能消耗。

使用Distributed表引擎创建分布式表

CREATE TABLE t_stock_distributed on cluster logs 
(
 `id` UInt32,
 `sku_id` String,
 `total_amount` Decimal(16, 2),
 `create_time` DateTime
)ENGINE=Distributed(logs,default,t_stock_local,hiveHash(sku_id));

在Destributed引擎中，需要指定几个参数，依次是: 集群名称、库名、本地表名、分片键。其中分片键必须是整型数字，可以使用rand()来返回一个随机数字，也可以是id这样一个int字段，如果id的数据分布不够均匀，也可以使用intHash64() 函数进行一下散列。而对于sku_id，他是string类型的，所以要用 hiveHash函数转换一下。如果是随机分片可以用rand()。

后续就可以像使用一个普通表一样使用这个分布式表了。数据会分开存储到不同分片的t_stock_local表当中。

查询优化 ​

查看耗时SQL统计

SELECT
    user,
    client_hostname AS host,
    client_name AS client,
    formatDateTime(query_start_time, '%T') AS started,
    query_duration_ms / 1000 AS sec,
    formatReadableSize(memory_usage) AS Mem_usage,
    result_rows AS RES_CNT,
    result_bytes / 1048576 AS RES_MB,
    read_rows AS R_CNT,
    round(read_bytes / 1048576) AS R_MB,
    written_rows AS W_CNT,
    round(written_bytes / 1048576) AS W_MB,
    substring(query, 1, 100) AS query
FROM system.query_log
WHERE (positionCaseInsensitive(query, '%system.%') = 0) AND (event_date >= today()) AND (event_time >= (now() - 7200))
ORDER BY query_duration_ms DESC
LIMIT 10

查看执行计划

执行计划是进行查询调优的重要参考。clickhouse中，可以使用explain语句很方便的查看SQL语句的执行计划。完整的explain使用语法如下:

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE][setting = value,...]SELECT... [FORMAT ...]

  • AST: 查看抽象语法树

  • 支持查看所有类型的语句，不光是SELECT语句。

explain ast select number from system.numbers limit 10
  • SYNTAX:查询优化后的SQL语句
EXPLAIN SYNTAX SELECT * FROM system.numbers AS a,system.numbers AS b, system.numbers AS c;
  • PLAN: 用于查看执行计划

可以指定五个参数

  • header: 打印各个执行步骤的header说明。默认0

    • description: 打印执行步骤。默认1

    • indexes: 显示索引使用情况。默认0。只对MergeTree表引擎有用。

    • actions: 打印执行计划的详细信息。默认0.

    • json: 以JSON格式打印执行计划。默认0.

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
EXPLAIN json = 1,description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
  • PIPELINE: 用于查看pipeline计划

可以指定三个参数:

  • header: 打印各个步骤的header信息。默认0
    • graph: 打印以DOT图形语法描述的结果。默认0 compact: 如果开启了graph，紧凑打印行。默认开启。
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

高性能查询优化建议 ​

1、选择合适的表引擎

虽然MergeTree是clickhouse中最为常用的表引擎，但是也不意味着MergeTree 适合所有的场景。

2、建表时不要使用Nullable

Nullable类型虽然在处理空值问题时非常简单好用，但是，官方已经指出 Nullable类型几乎总是会拖累性能，所以要尽量少用。在实际项目中，尽量使用字 段的默认值表示空，或者自行指定一个在业务中无意义的值。

这是因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且 Nullable列无法被索引。

3、合适的划分分区和索引

实际使用中，Partition by分区基本上是必须的。在划分分区时，通常建议按天分区。如果自行分区的话，单分区的数据最好不要超过一百万行。

4、数据变更优化

对clickhouse数据的增删改操作都会产生新的临时分区，会给MergeTree带来额 外的合并任务。因此，数据变更操作不宜太频繁，这样会产生非常多的临时分区。 一次操作的数据也不能太快。临时分区写入过快会导致Merge速度跟不上而报错。

官方一般建议一秒钟发起一次左右的写入操作，每次操作写入的数据量保持在 2W~5W之间。具体根据服务器性能而定。

5、使用Prewhere替代where

clickhouse还提供了一个Prewhere关键字。他的用法与where基本上一样的。但是不同之处在于prewhere只支持*MergeTree系列引擎。他会先读取指令的列数 据，来判断数据过滤。等待数据过滤完成之后再读取select声明的列字段来补全其他 属性。与之对比，where语句是读取整行各个列的数据，再进行过滤，IO性能明显 下降。

默认情况下，clickhouse就会使用prewhere语句代替where。

注意：prewhere只能查只读字段，既不会在发生修改的数据，如果数据被修改，会查询出旧数据

explain syntax select WatchID from datasets.hits_v1 where UserID='3198390223272470366';
-- 优化为
select WatchID from datasets.hits_v1 prewhere UserID='3198390223272470366';

6、指定列和分区

首先 在数据量很大时，应该避免使用select *。 而应该指定具体需要查询的列名。并且列应该越少越好。

这很好理解，因为clickhouse是以列来存储数据的。查询的列越少，需要读取的 文件就越少。消耗的IO资源减少了，性能自然就提高了。

然后 在查询分区表时，应该尽量在where条件中指定分区键的查询条件。

clickhouse中的分区实际上对应一个本地目录。指定分区键的查询条件，同样可以减少查询所需要遍历的数据文件，减少IO资源消耗。

7、避免构建虚拟列

如非必要，尽量直接使用clickhouse中的表已有的列，不要将计算结果构建成不存在的虚拟列。这样会非常消耗资源，浪费性能。通常情况下，都可以在数据进入 clickhouse之前进行处理。

-- 反例:构建除了一个虚拟的IncRate列
SELECT Income, Age, Income/Age as IncRate FROM datasets.hits_v1;
-- 正例:拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income, Age FROM datasets.hits_v1;

8、用IN代替JOIN

clickhouse支持使用JOIN进行关联查询，但是他的实现机制是将后面的表全部加载到内存中，然后跟前表数据在内存中进行合并。

当表的数据比较大时，对内存的消耗是非常大的。所以通常情况下，可以用IN代 替JOIN。如果非要用JOIN时，也要尽量把大表写在前面，小表写在后面。这跟 mysql建议用小表驱动大表恰好相反。

例如下面两个语句的查询逻辑基本上是一样的。但是查询耗时差距却非常大。

select a.CounterID from datasets.hits_v1 a wherea.CounterID in (select CounterID from datasets.visits_v1) limit 100;
--结果100rowsinset.Elapsed:0.047sec.Processed262.02thousandrows,1.05 MB (5.55 million rows/s., 22.21 MB/s.)
select a.CounterID fromdatasets.hits_v1 a left join datasets.visits_v1 b on a. CounterID=b.CounterID limit 100;
--结果100rowsinset.Elapsed:0.156sec.Processed1.68millionrows,6.71MB (10.74 million rows/s., 42.98 MB/s.)

使用 ​

构建实体表 ​

示例，以下是建立合并树表，注意该表建立在你的集群的各个分片上

CREATE TABLE default.order on cluster default
(
    `id` UInt64,
    `order_no` String,
    `order_state` Int8,
    `order_price` Decimal(18, 2),
    `is_delete` Int8,
    `create_user` String,
    `update_user` String,
    `create_time` DateTime,
    `update_time` DateTime,
    `ts` DateTime,
    INDEX order_no_idx order_no TYPE minmax GRANULARITY 32,
    INDEX create_time_idx create_time TYPE minmax GRANULARITY 32
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/default/order', '{replica}', ts)
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192

第二步建立分布式表

示例，重点 ENGINE = Distributed('default', 'default', 'maintenance_bill_p', sipHash64(service_no))，DTS任务同步时，一定要同步到分布式表中

CREATE TABLE default.order_cluster on cluster default
as order
ENGINE = Distributed('default', 'default', 'order', sipHash64(order_no))

查询时的注意事项

由于合并树的合并并不是实时合并，所以查询到的结果会有一定的延时，数据量不是很大的情况，例如少于1000万，可以是用关键字final来进行优化，也可以使用高阶用法，模拟update和delete操作

CK与ES对比 ​

引用：https://www.cnblogs.com/xionggeclub/p/15100707.html

优点：

1、ClickHouse写入吞吐量大，单服务器日志写入量在50MB到200MB/s，每秒写入超过60w记录数，是ES的5倍以上。

2、查询速度快，官方宣称数据在pagecache中，单服务器查询速率大约在2-30GB/s；没在pagecache的情况下，查询速度取决于磁盘的读取速率和数据的压缩率。。

3、ClickHouse比ES服务器成本更低。一方面ClickHouse的数据压缩比比ES高，相同数据占用的磁盘空间只有ES的1/3到1/30，节省了磁盘空间的同时，也能有效的减少磁盘IO；另一方面ClickHouse比ES占用更少的内存，消耗更少的CPU资源。。

4、相比ES，ClickHouse稳定性更高，运维成本更低。ES中不同的Group负载不均衡，有的Group负载高，会导致写Rejected等问题，需要人工迁移索引；在ClickHouse中通过集群和Shard策略，采用轮询写的方法，可以让数据比较均衡的分布到所有节点。ES中一个大查询可能导致OOM的问题；ClickHouse通过预设的查询限制，会查询失败，不影响整体的稳定性。ES需要进行冷热数据分离，ClickHouse按天分partition，一般不需要考虑冷热分离，特殊场景用户确实需要冷热分离的，数据量也会小很多，ClickHouse自带的冷热分离机制就可以很好的解决。

5、ClickHouse采用SQL语法，比ES的DSL更加简单，学习成本更低。

缺点：

1、由于是列式数据库，无法像ES一样提供全文检索功能。

2、无法动态添加字段，需要提前定义好表schema。

3、日志无法长期保存，历史数据需定期清理下线，如果有保存历史数据需求，需要通过迁移数据，采用ClickHouse_copier或者复制数据的方式实现。

4、ClickHouse查询速度快，能充分利用集群资源，但是无法支持高并发查询，默认配置qps为100。

5、Clickhouse并不适合许多小数据高频插入，批量写入日志会有一定延迟。

进阶 ​

CK的存储原理：clickhouse为什么这么快

一些常用的语句 ​

注意：集群环境下，除了 select 和 insert，其余的alter操作都要加 on cluster <集群>

删除表 ​

DROP TABLE  IF EXISTS default.test_bill on cluster default

创建表 ​

create table xxx on cluster default  (id int ,,,,,,,,  );

加字段 ​

注意：先加本地表，再加分布式表

-- 先执行   receive_bill_p 为本地表
ALTER table default.xxx on CLUSTER default add COLUMN business_order_no String;


-- 再执行   receive_bill 为分布式表
ALTER table default.xxx_cluster on CLUSTER default add COLUMN business_order_no String

Released under the MIT License.

Copyright © 2019-present Evan You