导语：同学，你也不想你根本不懂ClickHouse，却赶鸭子上架使用的事情被其他人知道吧？

写在前面：本文旨在让原先有一定SQL基础的人快速简单了解ClickHouse的（关键）概念/特性，侧重于使用方面的介绍比较而非原理/实现挖掘。文章算是个人摘录学习 理解，主要参考资料为ClickHouse官方（英文）文档（毕竟绝对权威）,写于2023年5月，请注意时效性。

简要介绍

ClickHouse是一个用于联机分析处理(OLAP)的列式数据库管理系统(DBMS)，拥有着及其卓越的查询速度。OLAP是一种面向分析的处理，用于处理大量的数据并支持复杂的分析和查询操作。诸如BI系统等重数据分析的场景，都应重点考虑使用OLAP数据库，而其中ClickHouse又是OLAP数据库星海中最璀璨的一颗星。

ClickHouse的场景特点

纵使ClickHouse有千般万好，但是能真正契合系统需求的数据库，才是最合适的。因此，在我们正式迈进ClickHouse使用大门之前，我想有必要先识其长短。

上图截自ClickHouse官方文档，与其说这是olap的场景，不妨说是ClickHouse的常见场景。其中我觉得有必要指出的是：

1.”查询相对较少”，这意味着ClickHouse并发查询能力不强（官方建议每秒最多查询100次），原因在于对于每条查询，ClickHouse都会尽可能动用服务器的CPU、内存资源等，而不同于MySQL单条SQL是单线程的，资源消耗更不可控（当然ClickHouse本身也有相关参数可以配置查询消耗的资源情况）。

2.”结果适合于单个服务器的RAM中”，结合上面所说，每条查询都会消耗ClickHouse不少的（内存）资源，因此不要无脑join大表，否则Memorylimitexceeded警告。

在开始更有意义的赞美之前，让我再对ClickHouse进行一些”自由的批评”：

1.尽管ClickHouse与mysql等数据库一样支持标准SQL语法（甚至兼容了mysql的G语法）以及窗口函数等，但是相关子查询暂未支持，但将来会实现。

2.稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。（稀疏索引只存储非零值，因此在进行点查询时需要遍历整个索引才能找到对应的行，这会导致点查询的性能较低）

ClickHouse基础

连接及数据格式

连接方式

ClickHouse提供了HTTP和TCP以及gRPC三种方式的接口，非常方便，其中ClickHouse-client是基于TCP方式的，不同的client和服务器版本彼此兼容。

以HTTP接口方式访问时，需注意使用GET方法请求时是默认readonly的。换句话说，若要作修改数据的查询，只能使用POST方法。

此外，除了上述的接口形式，ClickHouse甚至支持了MySQL wire通讯协议，生怕像我一样的MySQL boy难以上手。简单的配置之后，就能轻松使用mysqlclient连接ClickHouse服务器，颇有import pytorch as tf之感（这何尝不是一种语言层面的ntr）。不过也有一些限制，不支持prepared查询以及某些数据类型会以字符串形式发送。同样命运的还有PostgreSQL。

当然，更常见的使用方式还是各语言实现的client库。如今ClickHouse的生态早已成熟，无论是各类编程语言亦或是常见的InfrastructureProducts（怎么翻都别扭干脆贴原文，后同）（如kafka、k8s、grafana等），都有现成的库将其结合起来使用。

数据格式

ClickHouse支持丰富的输入/输出格式，简单来说就是TSV、CSV、JSON、XML、Protobuf、二进制格式以及一些Hadoop生态下常见的数据格式。此外ClickHouse本身也有一些模式推断相关的函数，能从文件/hdfs等数据源推断出表的结构，算是个有趣的功能。

数据类型

常用的：

整型：追求极致性能的ClickHouse，自然是会在字节维度上锱铢必较的，整型类型的可选范围为(U)Int8到Int256，当然讲究兼容的ClickHouse也是允许你定义BIGINT、BOOL、INT4之类的，会对应到相应的字节数类型上。什么，你还要像mysql那样定义展示宽度（11）？对不起，做不到.jpg。

浮点数：Float32⬄FLOAT、Float64⬄DOUBLE，需注意计算可能出现Inf和NaN。

Bool：内部等同于UInt8。

String：字节数没有限制，与LONGTEXT，MEDIUMTEXT，TINYTEXT，TEXT，LONGBLOB，MEDIUMBLOB，TINYBLOB，BLOB，VARCHAR，CHAR同义。

Date：取值范围[1970-01-01,2149-06-06]（当前）。

DateTime：具体到秒的时间。可以指定时区，如DateTime('Asia/Shanghai')，如不指定将使用ClickHouse服务器的时区设置。

时区仅用作以文本形式输入输出数据时的转换（所以时区函数是没有计算cost的），实际以unix timestamp存储。因此，如果插入数据时写211046400和1976-09-09 00:00:00是等效的（时区为东八区的话）。

array：定义方式为array(T)，下标起始为1，可以定义多维数组。数组元素最大可为一百万个。数组内的元素类型需兼容，不兼容将抛出异常。可通过sizeN-1快速获得对应第N维的长度。

Tuple：定义方式为Tuple(arg1 type1,arg2 type2…)。后续可通过类似a.b的方式获取对应的值。元组间的比较为依次比较各元素大小。

Nullable：可用Nullable修饰一个类型，使其允许包含NULL值，代价是，被修饰的列无法作为表的索引项。同时，为了存储Nullable值，ClickHouse还会额外使用一个带有NULL掩码的文件来区分列的默认值与NULL值，会在存储空间以及性能上造成额外负担。

也正是因为特殊对待了Nullable的字段，可以用`字段名`.null（这个值将返回1或0标识是否为空值）快速找到对应字段为null的行。

总之，能用业务逻辑来区分空值，就尽量不要定义Nullable字段。

AggregateFunction：黑魔法，用法是AggregateFunction(func,types_of_argument..)，如AggregateFunction(uniq,UInt64)。目前只支持uniq，anyIf和quantiles聚合函数。

可以配合xx-State函数得到中间状态，通过xx-Merge函数得到结果。好处就是可以将计算状态序列化到表里，减少数据存储量。通常是通过物化视图实现的。

SimpleAggregateFunction：类似于AggregateFunction类型，支持更多的聚合函数，且无需应用xx-Merge和xx-State函数来得到值。

不常用的（我觉得）：

Decimal：

P-精度。有效范围：[1:76]，决定可以有多少个十进制数字（包括分数）。

S-规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。

FixedString(N)：顾名思义，需注意N为字节，当字段的字节数刚好与指定的N相等时最高效，适合存一些明确的枚举。超过会抛出异常。

UUID：配合generateUUIDv4函数食用更佳。

Date32：范围为有符号32位整数，表示相对1970-01-01的的天数。

DateTime64：时间范围[1900-01-01 00:00:00,2299-12-31 23:59:59.99999999]。但不同于DateTime会与String自动转换，需借助诸如toDateTime64之类的时间处理函数。

枚举：有Enum8和Enum16两种类型，将预定字符串与整型数字关联。插入枚举值之外的值将抛出异常，枚举值不能直接跟数字作比较。

LowCardinality：用法是LowCardinality(data_type)，data_type的可选类型为String，FixedString，Date，DateTime及除Decimal外的数字类型。

即将所在列的不同值映射到一个较短的编码，当少于10000个不同的值时ClickHouse可以进行更高效的数据存储和处理。比枚举类型有更高的性能和灵活性。

域（Domain）：域是出于使用户易用等目的，在不修改原类型底层表示的情况下为基础类型添加了部分特性的类型，用户不能自定义域。目前有IPV4和IPV6两个类型，用途可顾名思义。

Nested：定义方式为Nested(name1Type1,Name2Type2,…)，如DistrictNested(ProvinceString,CityString)，后续就可以通过District.City访问具体值，将得到数组对象。（重生之我在DB定义结构体）

flatten_nested设为0（非默认值）可以无限套娃Nested类型。ALTER命令操作Nested类型会受限。

地理位置：包含了Point、Ring、Polygon、MultiPolygon四种类型，即Tuple(Float64,Float64)，Array(Point)，Array(Ring)，Array(Polygon)。其中Polygon的表示方式为首元素为最外层轮廓的点集合，其余元素视作多边形的”洞”。

字典：定义方式Map(key,value)，key可为String,Integer,LowCardinality,FixedString,UUID,Date,DateTime,Date32,Enum，value类型任意，包括Map本身。取数时写法也与各大编程语言相同，当key不存在时默认返回类型的零值，也支持a.keys和a.values这样的语法。（Re：从零开始的异世界DB写Map生活）

SQL语句

ClickHouse支持的SQL语句如上所示，内容太多了。。只简单挑些重点看下，先留个坑。

SELECT

小技巧：

select取最终列时，可以使用COLUMNS表达式来以re2的正则表达式语法查找匹配的列，如COLUMNS(‘a’)可以匹配aa，ab列，效果类似python的re.search方法，查询大宽表的时，这个功能还是非常好用的。

此外，配合APPLY(<func>)，EXCEPT(col_name..)，REPLACE(<expr>ascol_name)这三个语法糖，有时能大大简化SQL，如：

SELECT COLUMNS(‘_w’) EXCEPT(‘test’) APPLY(max) from my_table

就能迅速找出带_w且不带test的列，并计算他们的最大值。（想想有时只需要简单分析部分列，却要施法吟唱半天）

有时需要对单独某个查询设置特殊配置时，也可在语句最后直接加上SETTINGS xx，这样配置就只会对本次查询生效。

ARRAY JOIN：

用于生成一个新表，该表具有包含该初始列中的每个单独数组元素的列，而其他列的值将被重复显示。单行变多行的经典操作。空数组将不包含在结果中，LEFT ARRAY JOIN则会包含。

可同时ARRAY JOIN多个数组，这种情况下得到的结果并非笛卡尔积。也可以ARRAY JOIN Nested类型。

DISTINCT：

如果需要只对某几列去重，需用DISTINCTON(column1,column2..)，否则视作对全部列去重。DISTINCT子句是先于ORDER BY子句执行的。

与不使用聚合函数而对某些列进行GROUPBY相比，结果一般是相同的，但使用DISTINCT时，已处理的数据块会立马输出，而无需等待整个查询执行完成。

INTERSECT、UNION、EXCEPT：

将两个查询进行交并补，列数等信息需匹配。重复行多时INTERSECT DISTINCT效果更好。

FROM：

可在数据源名后加上FINAL修饰符，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。只适用于MergeTree-引擎族。使用FINAL修饰符的SELECT查询启用了并发执行，但仍比不带FINAL的查询更慢，一是因为这会在查询执行过程中合并数据，二是FINAL会额外读取主键列。多数情况下不推荐使用，通常可以通过假设MergeTree的后台进程还未生效（引擎部分再谈），并使用聚合函数来达到同样效果。

此外不同于很多数据库在你缺失相关参数时给个错误，ClickHouse在很多地方都做了默认参数的设置。比如在你不指定FROM子句时，默认从system.one表查询，以及支持select count()（会倾向于选取最小的列进行计数）这样的写法。不过这好不好嘛，还是智者见智仁者见仁，在不理解的情况下被暗戳戳地坑一把也是可能的。

Join：

除了支持标准的SQL JOIN类型，还支持ASOF JOIN，常用于根据时间序列不完全匹配地join多个表，比如用来匹配用户事件活动记录。

涉及到分布式表的join:

当使用普通JOIN时，将查询发送到远程服务器。在每个服务器上单独形成右表。

当使用GLOBAL … JOIN时，首先请求者服务器运行一个子查询来计算正确的表。此临时表将传递到每个远程服务器，并使用传输的临时数据对其运行查询。

当运行JOIN操作时，与查询的其他阶段相比，执行顺序没有进行优化。JOIN操作会在WHERE过滤和聚合之前运行。

同样的join操作在子查询中又会再次执行一次，要避免这种情况可以考虑使用Join这个表引擎。

默认情况下，ClickHouse使用哈希联接算法。 ClickHouse取右表并在内存中为其创建哈希表。（所以一个很重要的最佳实践是join表时把小表放在右表）在达到某个内存消耗阈值后，ClickHouse会回退到合并联接算法。

INSERT INTO

插入数据时会对写入的数据进行一些处理，按照主键排序，按照分区键对数据进行分区等。所以如果在写入数据中包含多个分区的混合数据时，将会显著的降低INSERT的性能。为了避免这种情况：

• 数据总是以尽量大的batch进行写入，如每次写入100,000行。
• 数据在写入ClickHouse前预先的对数据进行分组。

在以下的情况下，性能不会下降：

• 数据总是被实时的写入。
• 写入的数据已经按照时间排序。

也可以异步的、小规模的插入数据，这些数据会被合并成多个批次，然后安全地写入到表中。这是通过设置async_insert来实现的，异步插入的方式只支持HTTP协议，并且不支持数据去重。

CREATE

Materialized（物化视图）

创建语法：

CREATE MATERIALIZED VIEW [IF NOT exists] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT …

创建不带TO [db].[table]的物化视图时，必须指定ENGINE–用于存储数据的表引擎。

使用TO [db].[table]创建物化视图时，不得使用POPULATE。

具体实现：当向SELECT中指定的表插入数据时，插入数据的一部分被这个SELECT查询转换，结果插入到视图中。

ClickHouse 中的物化视图更像是插入触发器。 如果视图查询中有一些聚合，则它仅应用于一批新插入的数据。对源表现有数据的任何更改（如更新、删除、删除分区等）都不会更改物化视图。

ClickHouse 中的物化视图在出现错误时没有确定性行为。这意味着已经写入的块将保留在目标表中，但出现错误后的所有块则不会写入。

如果指定POPULATE，则在创建视图时将现有表数据插入到视图中，就像创建一个CREATE TABLE … AS SELECT …一样。否则，查询仅包含创建视图后插入表中的数据。不建议使用POPULATE，因为在创建视图期间插入表中的数据不会插入其中。

SELECT查询可以包含DISTINCT、GROUP BY、ORDER BY、LIMIT……请注意，相应的转换是在每个插入数据块上独立执行的。 例如，如果设置了GROUP BY，则在插入期间聚合数据，但仅在插入数据的单个数据包内。数据不会被进一步聚合。例外情况是使用独立执行数据聚合的ENGINE，例如SummingMergeTree。

在物化视图上执行ALTER查询有局限性，因此可能不方便。如果物化视图使用构造TO [db.]name，你可以DETACH视图，为目标表运行ALTER，然后ATTACH先前分离的视图。

视图看起来与普通表相同。 例如，它们列在SHOW TABLES查询的结果中。

ALTER

UPDATE

没错，update操作被置于ALTER操作下，这意味着ClickHouse的update操作不像oltp数据库那般轻量级，应尽量避免使用。是通过mutation来实现的。

Mutations(突变)

用来操作表数据的ALTER查询是通过一种叫做“突变”的机制来实现的，最明显的是ALTER TABLE … DELETE和ALTER TABLE … UPDATE。它们是异步的后台进程，类似于MergeTree表的合并，产生新的“突变”版本的数据part（后面会诠释这个概念）。

对于*MergeTree表，通过重写整个数据part来执行突变。没有原子性——一旦突变的part准备好，part就会被替换，并且在突变期间开始执行的SELECT查询将看到来自已经突变的part的数据，以及来自尚未突变的part的数据。

突变完全按照它们的产生顺序排列，并按此顺序应用于每个part。突变还与“INSERT INTO”查询进行排序:在提交突变之前插入表中的数据将被突变，而在此之后插入的数据将不会被突变。注意，突变不会以任何方式阻止插入。

突变查询在添加突变条目后立即返回(对于复制表是到ZooKeeper，对于非复制表到文件系统)。突变本身使用系统配置文件来配置异步执行。要跟踪突变的进程，可以使用system.mutations表。成功提交的变异将继续执行，即使ClickHouse服务器重新启动。没有办法回滚突变一旦提交，但如果突变卡住了，可以使用KILL MUTATION阻止突变的执行。

完成突变的条目不会立即删除(保留条目的数量由finished_mutations_to_keep存储引擎参数决定)。

DELETE

删除的行会被立即标记为已删除，并将自动从所有后续查询中过滤掉。数据清理在后台异步发生。此功能仅适用于 MergeTree 表引擎系列。这就是ClickHouse的轻量级删除

原理：当执行DELETE时，ClickHouse 仅保存一个掩码，其中每一行都被标记为“现有”或“已删除”。 掩码实现为一个隐藏的_row_exists系统列，所有可见行该列存储为 True，删除的行存储为False。仅当一个数据part中部分行被删除了，这个字段才会出现。

DELETE操作实际上是被翻译成ALTER TABLE update _row_exists = 0 WHERE …的mutation操作。

引擎

数据库引擎

Atomic

ClickHouse的默认数据库引擎，支持非阻塞的DROP TABLE、RENAME TABLE和具有原子性的EXCHANGE TABLE操作。

DROP TABLE时只会将表标记为已删除，并且把元数据移到/clickhouse_path/metadata_dropped/，然后通知后台线程稍后删除，这个延迟时间可指定，也可设为同步删除。

Lazy

在最后一次访问之后，只在内存中保存expiration_time_in_seconds秒。只能用于*Log表。它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔。

PostgreSQL、MySQL、SQLite

……用于在ClickHouse与上述三种数据库间交（tou）换(jia)数据。其中不能在MySQL引擎上执行RENAME、CREATETABLE和ALTER来修改表的结构。

另外还有几个实验性的引擎，不谈。

表引擎

表引擎（即表的类型）决定了：

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数。

MergeTree系列

MergeTree系列的引擎是ClickHouse中最核心的引擎，提供了列式存储、自定义分区、稀疏主键索引和二级跳数索引等功能。基于MergeTree的引擎都在部分特定用例下添加了额外的功能，而且通常是在后台执行额外的数据操作来实现的。缺点是这些引擎相对笨重，如果需要许多小表来存一些临时数据，可以考虑Log系列引擎。

MergeTree

主要特点：

1. 存储按主键排序。
2. 指定了分区键时，会截取分区数据，增加查询效率。
3. 支持数据采样。

完整语句参考：

重要参数说明：

ORDER BY：排序键

如果没有用PRIMARY KEY明确定义主键，那么该键将被当做主键。

如果不需要排序，可以使用ORDERBY tuple()。

排序键包含多列时，查询时走索引依然遵循最左匹配规则。

PARTITION BY：分区键

大多数情况下，不需要分使用区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与ORDER BY表达式不同）。永远也别使用过细粒度的分区键。

要按月分区，可以使用表达式toYYYYMM(date_column)。

PRIMARY KEY：主键

大部分情况下不需要再专门指定一个PRIMARY KEY子句。ClickHouse不要求主键唯一。

INDEX：跳数索引

后面介绍。

存储细节：

不同分区的数据会被分成不同的片段(part，后同)，ClickHouse在后台合并数据片段以便更高效存储。

数据片段可以以Wide或Compact格式存储。在Wide格式下，每一列都会在文件系统中存储为单独的文件，在Compact格式下所有列都存储在一个文件中。Compact格式可以提高插入量少插入频率频繁时的性能。

每个数据片段被逻辑的分割成颗粒（granules）。颗粒是ClickHouse中进行数据查询时的最小不可分割数据集。ClickHouse不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记，ClickHouse会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse都会存储类似标记。

颗粒的大小通过表引擎参数index_granularity（默认8192）和index_granularity_bytes（10Mb）控制。颗粒的行数的在[1,index_granularity]范围中，这取决于行的大小。如果单行的大小超过了index_granularity_bytes设置的值，那么一个颗粒的大小会超过index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。

详谈主键与索引：

主键的选择：

稀疏索引使得ClickHouse可以处理极大量的行，因为大多数情况下，这些索引常驻于内存。

长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响SELECT查询的性能。

可以使用ORDER BY tuple()语法创建没有主键的表。在这种情况下ClickHouse根据数据插入的顺序存储。如果在使用INSERT…SELECT时希望保持数据的排序，可以设置max_insert_threads=1。

主键与排序键不同的情况：

ClickHouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元组的前缀。

当使用SummingMergeTree和AggregatingMergeTree引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分两种：维度和度量。典型的查询会通过任意的GROUP BY对度量列进行聚合并通过维度列进行过滤。由于SummingMergeTree和AggregatingMergeTree会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法。但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断的更新。

在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。

部分单调序列：

如一个月中的天数。它们在一个月的范围内形成一个单调序列，但如果扩展到更大的时间范围它们就不再单调了,这就是一个部分单调序列。如果用户使用部分单调的主键创建表，ClickHouse同样会创建一个稀疏索引。当用户从这类表中查询数据时，ClickHouse会对查询条件进行分析。如果用户希望获取两个索引标记之间的数据并且这两个标记在一个月以内，ClickHouse可以在这种特殊情况下使用到索引，因为它可以计算出查询参数与索引标记之间的距离。

如果查询参数范围内的主键不是单调序列，那么ClickHouse无法使用索引。

ClickHouse在任何主键代表一个部分单调序列的情况下都会使用这个逻辑。（这个故事告诉我们为什么默认主键和排序键相同）

跳数索引：

示例：INDEX a(u64*i32,s) TYPE minmax GRANULARITY 3。复合列上也能创建。

*MergeTree系列的表可以指定跳数索引。跳数索引是指数据片段按照粒度分割成小块后，将上述SQL的granularity_value数量的小块组合成一个大的块，对这些大块写入索引信息，这样有助于使用where筛选时跳过大量不必要的数据，减少SELECT需要读取的数据量。

Projection：

投影（projection）类似于物化视图，但存储在分区目录，即与原表的数据分区在同一个分区目录下。可通过投影定义语句SELECT <column list expr> [GROUP BY] <group keys expr> [ORDER BY] <expr>生成。使用可能还需要配置一些参数。

如指定了Group by子句则投影的引擎将变为AggregatingMergeTree，同时所有的聚合函数变为AggregateFunction。指定了ORDER BY子句则会使用对应的key作为主键。更多示例可参考：2021年ClickHouse最王炸功能来袭，性能轻松提升40倍。

简单来说，跟物化视图的区别可以看作是——不用再显式定义一个物化视图了，对应用层屏蔽了基础数据和统计数据的区别。两类数据你都直接查原表即可。

并发访问：

MergeTree引擎也是MVCC（多版本并发控制）的。

列与表的TTL：

设置TTL即设置数据的过期时间，当列的TTL过期时，ClickHouse会将数据替换成对应数据类型的默认值，当该列所有数据都过期时，该列的数据将会被删除。（列式数据库，小子！）主键列不可指定。

当表的TTL过期时，过期行会被操作（删除或转移），还可通过WHERE和GROUP BY条件指定符合条件的行。GROUP BY表达式必须是表主键的前缀。

数据副本

MergeTree系列的引擎的表都支持数据副本，只需在引擎名前加上Replicated。

ReplacingMergeTree

该引擎和MergeTree的不同之处在于它会删除排序键值相同的重复项，适用于在后台清除重复的数据以节省空间。但只会在数据合并期间进行，而合并会在后台一个不确定的时间进行。虽然可以调用OPTIMIZE语句发起计划外的合并，但须知OPTIMIZE语句会引发对数据的大量读写。

SummingMergeTree

当合并SummingMergeTree表的数据片段时，ClickHouse会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的sum值。即便如此，当需要聚合数据时仍应该使用sum函数来聚合，因为后台合并的时间是不确定的。

对于AggregateFunction 类型的列，ClickHouse 根据对应函数表现为AggregatingMergeTree引擎的聚合。

而对于Nested类型的列，ClickHouse会将第一列视作key，其他列视作values进行聚合。

AggregatingMergeTree

将一个数据片段内所有具有相同排序键的行替换成一行，这一行会存储一系列聚合函数的状态。引擎使用AggregateFunction和SimpleAggregateFunction类型来处理所有列。可以看做SummingMergeTree是AggregatingMergeTree的特化（表现上而言）。

可以使用AggregatingMergeTree表来做增量数据的聚合统计，包括物化视图的数据聚合。

要插入数据，需使用带有-State-聚合函数的INSERT SELECT语句。从AggregatingMergeTree表中查询数据时，需使用GROUP BY子句并且要使用与插入时相同的聚合函数，但后缀要改为-Merge。

CollapsingMergeTree

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行将会被保留。

Sign为1和-1的行应按照一定的顺序写入，合并相当取决于记录的一致性，否则实现不了预期的折叠效果（即先Sign=1后Sign=-1），聚合统计时也应考虑上Sign字段对结果的影响。可以使用Final修饰符强制进行折叠而不聚合，但是效率低下。

此外，插入时Sign=1和Sign=-1的记录应该在两次insert语句中分别插入，以保证他们在不同的数据片段（part），否则也不会执行合并操作。

个人觉得，难用（其实我想说没用），或者是我没找到正确的打开方式。

VersionedCollapsingMergeTree

顾名思义，是上面那位的兄弟，只不过多了一个Version列，允许以多个线程的任何顺序插入数据。Version列有助于正确折叠行，即使它们以错误的顺序插入。

当ClickHouse合并数据部分时，它会删除具有相同主键和版本但Sign值不同的一对行。

当ClickHouse插入数据时，它会按主键对行进行排序。 如果Version列不在主键中，ClickHouse将其隐式添加到主键作为最后一个字段并使用它进行排序。

由于ClickHouse具有不保证具有相同主键的所有行都将位于相同的结果数据片段中，甚至位于相同的物理服务器上的特性，以及上面说的数据合并时机的不确定性，所以想要最终的数据还是免不了group by等聚合操作。

GraphiteMergeTree

该引擎用来对Graphite类型数据进行瘦身及汇总。如果不需要对Graphite数据做汇总，那么可以使用任意的表引擎；但若需要，那就采用GraphiteMergeTree引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

Log引擎系列

共同特点：

• 数据存储在磁盘上。
• 写入时将数据追加在文件末尾。
• 支持并发访问数据时上锁。（执行insert语句时，表会被上写锁）
• 不支持突变操作。（参见alter）
• 不支持索引。（表明范围查询效率不高）
• 非原子地写入数据。

各引擎差异：

Log引擎为表中的每一列使用不同的文件。StripeLog将所有的数据存储在一个文件中。因此StripeLog引擎在操作系统中使用更少的描述符，但是Log引擎提供更高的读性能。两者都支持并发的数据读取。

TinyLog引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TinyLog引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。

Log

Log与TinyLog的不同之处在于，”标记” 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。Log引擎适用于临时数据。

StripeLog

需要写入许多小数据量（小于一百万行）的表的场景下使用这个引擎。

写数据

StripeLog引擎将所有列存储在一个文件中。对每一次Insert请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。

ClickHouse 为每张表写入以下文件：

• data.bin— 数据文件。
• index.mrk— 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。

StripeLog引擎不支持ALTER UPDATE和ALTER DELETE操作。

读数据

带标记的文件使得 ClickHouse 可以并行的读取数据。这意味着SELECT请求返回行的顺序是不可预测的。

TinyLog

此表引擎通常使用场景：一次写入数据，然后根据需要多次读取。

查询在单个流中执行。该引擎适用于相对较小的表（最多约 1,000,000 行）。如果你有很多小表，使用这个表引擎是有意义的，因为它比日志引擎更简单（需要打开的文件更少）。

与外部系统集成的引擎

正如上面提到的ClickHouse对mysql等数据库的＂支持＂，实际上在表引擎上也提供了与外部系统的多种集成方式，如下所示。具体不再介绍，有需要可以去官网了解。

其他特殊引擎：

Distributed

分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。

创建语法：

也可使用AS语法使得分布式表指向本地表。

分布式引擎参数

• cluster– 服务为配置中的集群名
• database– 远程数据库名
• table– 远程数据表名
• sharding_key– (可选) 分片key
• policy_name– (可选) 规则名，它会被用作存储临时文件以便异步发送数据

settings中可进行一些分布式设置。

数据不仅在远程服务器上读取，而且在远程服务器上进行部分处理。例如，对于带有 GROUP BY的查询，数据将在远程服务器上聚合，聚合函数的中间状态将被发送到请求者服务器。然后将进一步聚合数据。

集群：

集群是通过服务器配置文件来配置的。集群名称不能包含点号。

配置了副本后，读取操作会从每个分片里选择一个可用的副本。可配置负载平衡算法。 如果跟服务器的连接不可用，则会尝试短超时的重连。如果重连失败，则选择下一个副本，依此类推。如果跟所有副本的连接尝试都失败，则尝试用相同的方式再重复几次。

要查看集群信息，可通过system.clusters表。

写入数据：

向集群写数据的方法有两种：

一，自已指定要将哪些数据写入哪些服务器，并直接在每个分片上执行写入。这是最灵活的解决方案 – 你可以使用任何分片方案，对于复杂业务特性的需求，这可能是非常重要的。这也是最佳解决方案，因为数据可以完全独立地写入不同的分片。

二，在分布式表上执行 INSERT。（哒咩，不推荐）在这种情况下，分布式表会跨服务器分发插入数据。为了写入分布式表，必须要配置分片键（最后一个参数）。当然，如果只有一个分片，则写操作在没有分片键的情况下也能工作，因为这种情况下分片键没有意义。

数据是异步写入的。对于分布式表的 INSERT，数据块只写本地文件系统。之后会尽快地在后台发送到远程服务器。

如果在 INSERT 到分布式表时服务器节点丢失或重启（如，设备故障），则插入的数据可能会丢失。如果在表目录中检测到损坏的数据分片，则会将其转移到broken子目录，并不再使用。

关于分片：

分片可在配置文件中定义‘internal_replication’参数。

此参数设置为true时，写操作只选一个正常的副本写入数据。如果分布式表的子表是复制表(*ReplicaMergeTree)，请使用此方案。换句话说，这其实是把数据的复制工作交给实际需要写入数据的表本身而不是分布式表。

若此参数设置为false（默认值），写操作会将数据写入所有副本。实质上，这意味着要分布式表本身来复制数据。这种方式不如使用复制表的好，因为不会检查副本的一致性，并且随着时间的推移，副本数据可能会有些不一样。

选择将一行数据发送到哪个分片的方法是，首先计算分片表达式，然后将这个计算结果除以所有分片的权重总和得到余数。该行会发送到那个包含该余数的从’prev_weight’到’prev_weights weight’的前闭后开区间对应的分片上，其中 ‘prev_weights’ 是该分片前面的所有分片的权重和，‘weight’ 是该分片的权重。

分片表达式可以是由常量和表列组成的任何返回整数表达式。

下面的情况，需要关注分片方案：

• 使用需要特定键连接数据（ IN 或 JOIN ）的查询。如果数据是用该键进行分片，则应使用本地 IN 或 JOIN 而不是 GLOBAL IN 或 GLOBAL JOIN，这样效率更高。
• 使用大量服务器，但有大量小查询，为了使小查询不影响整个集群，让单个客户的数据处于单个分片上是有意义的。或者你可以配置两级分片：将整个集群划分为层，一个层可以包含多个分片。单个客户的数据位于单个层上，根据需要将分片添加到层中，层中的数据随机分布。然后给每层创建分布式表，再创建一个全局的分布式表用于全局的查询。

Dictionary

可以将字典数据展示为一个ClickHouse的表。需要在XML配置文件中定义字典。官网文档语焉不详，更多介绍可见https://blog.csdn.net/vkingnew/article/details/106973674。

（不太好用的亚子）

Merge

本身不存储数据，但可用于同时从任意多个其他的表中读取数据。 读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。

创建语法：

如果tables_regexp命中了Merge 表本身，也不会真正引入，以免循环引用，但创建两个表递归读取对方数据是可行的。

Merge引擎的一个典型应用是可以像使用一张表一样使用大量的TinyLog表。

Executable和ExecutablePool

这两个引擎用于关联脚本和具体表，表中的数据将由执行脚本后生成。脚本被放在”users_scripts”目录下。创建表时不会立即调用脚本，脚本将在表被查询时调用。

刚开始感觉这个引擎没什么用，为什么我不直接单独跑脚本把数据收集好之后再将它们插入表呢？转念想到脚本代码仓库里的几百个（无名）脚本及对应的（无名）表，瞬间感觉这功能还怪有用的。（查找表对应的生成脚本）

（当然，我没用过，等你去用）

应用及可能的坑点

应用

ClickHouse典型应用场景主要包括以下几个方面：

1. 大数据存储和分析：ClickHouse能够高效地存储和处理海量数据，支持PB级别的数据存储和分析，可以快速地处理大规模数据分析和数据挖掘任务。
2. 实时数据分析和查询：ClickHouse支持实时查询和分析，具有高速的数据读取和计算能力，可以在秒级别内返回查询结果，适用于需要快速响应数据查询和分析的业务场景。
3. 日志处理和分析：ClickHouse能够高效地处理日志数据，支持实时的日志分析和查询，可以帮助企业快速地发现和解决问题。
4. 业务智能分析：ClickHouse支持复杂的数据分析和计算，可以进行高级的数据挖掘和机器学习算法，帮助企业进行业务智能分析和决策。

总的来说，ClickHouse适用于需要处理大规模数据和实时查询的业务场景，例如数据报表、日志分析、业务智能分析、广告平台等。

其他要说的

part与partition：

这两个概念，我觉得是ClickHouse文档中容易搞混的一点，特别中文文档中出现的谜之概念『片段、片块、部分、部件、分片』，如果不是原先就对ClickHouse有较深刻的认识，可能一时反应不过来具体指代的是什么。关于这两者的区别，在这个链接及页面内的链接中有较好的阐述。

关注ClickHouse版本：

ClickHouse的官方中文文档相对英文文档，内容要稍微落后些（你说跟俄文比如何？阿巴阿巴）。比如中文文档中说ClickHouse不支持窗口函数，但英文文档中表示已经支持；中文文档中没有projection的介绍；中文文档中表示ClickHouse使用ZooKeeper维护元数据，然而在英文文档中表示使用ClickHouse Keeper维护元数据；等等等等。同样的，你的生产环境的ClickHouse版本也许与ClickHouse最新版有不小差距，所以在你考虑使用某个功能时，记得先看下当前版本是否已经支持。

关于ZooKeeper：

如上所述，ZooKeeper是ClickHouse常见版本的信息协调者。然而实际上一些行为日志也会存在其上，表的一些schema信息也会在上面做校验。而on cluster等操作也是依赖此实现的，在数据量较大时可能会有一些意外的阻塞情况发生，所以不要太依赖ClickHouse的on cluster等会依赖ZooKeeper的操作，能拿到具体节点的情况下，到每个节点上单独执行是更稳妥的。作为国内ClickHouse的布道者，宇宙条已经替大家踩过相关的坑了（当然我们团队也踩了一次）。

此外ClickHouse本身引擎对子查询的SQL优化效率不高，应尽量避免复杂的子查询语句。否则这些”cool cooler coolest”的SQL，在集群负载压力逐渐上来之后，可能会变成半夜里响个不停的业务告警通知。