clickhouse

什么是ClickHouse

ClickHouse是俄罗斯的Yandex于2016年开源的⼀个⽤于联机分析(OLAP:Online Analytical Processing)的列式数据 库管理系统(DBMS:Database Management System)，简称CH , 主要⽤于在线分析处理查询（OLAP），能够使⽤ SQL查询实时⽣成分析数据报告。

ClickHouse是⼀个完全的列式数据库管理系统，允许在运⾏时创建表和数据库，加载数据和运⾏查询，⽽⽆需重 新配置和重新启动服务器，⽀持线性扩展，简单⽅便，⾼可靠性，容错。

它⽐较快

ClickHouse 快的原因

四个⽅⾯

1. 它的数据剪枝能⼒⽐较强，分区剪枝在执⾏层，⽽存储格式⽤局部数据表示，就可以更细粒度地做⼀些数据的 剪枝。它的引擎在实际使⽤中应⽤了⼀种现在⽐较流⾏的 LSM ⽅式。

2. 它对整个资源的垂直整合能⼒做得⽐较好，并发 MPP+ SMP 这种执⾏⽅式可以很充分地利⽤机器的集成资 源。它的实现⼜做了很多性能相关的优化，它的⼀个简单的汇聚操作有很多不同的版本，会根据不同 Key 的 组合⽅式有不同的实现。对于⾼级的计算指令，数据解压时，它也有少量使⽤

3. ClickHouse 是⼀套完全由 C++ 模板 Code 写出来的实现，代码还是⽐较优雅的。

4. ClickHouse是⼀个完全的列式数据库

什么是OLAP

联机分析处理OLAP是⼀种软件技术，它使分析⼈员能够迅速、⼀致、交互地从各个⽅⾯观察信息，以达到深⼊理解数据的⽬的。它具有FASMI(Fast Analysis of Shared Multidimensional Information)，即共享多维信息的快速分析的特征

OLAP展现在⽤户⾯前的是⼀幅幅多维视图。

维的层次 观察数据的某个特定⻆度

维的成员 数据项在某维中位置的描述

度量 多维数组的取值

钻取 改变维的层次，变换分析的粒度

切⽚和切块 关⼼度量数据在剩余维上的分布 维只有两个 切⽚三个或以上 切块

旋转 在表格中重新安排维的放置

ClickHouse特征

真正的列式数据库管理系统

在⼀个真正的列式数据库管理系统中，除了数据本身外不应该存在其他额外的数据。

它允许在运⾏时创建表和数据库、加载数据和运⾏查询，⽽⽆需重新配置或重启服务。

数据压缩

ClickHouse提供了两种数据压缩⽅式供我们选择:LZ4和ZSTD。

数据的磁盘存储

ClickHouse被设计⽤于⼯作在传统磁盘上的系统，它提供每GB更低的存储成本，但如果有可以使⽤SSD和内存，它也会合理的利⽤这些资源。

多核⼼并⾏处理

ClickHouse会使⽤服务器上⼀切可⽤的资源，从⽽以最⾃然的⽅式并⾏处理⼤型查询。

多服务器分布式处理

在ClickHouse中，数据可以保存在不同的shard上，每⼀个shard都由⼀组⽤于容错的replica组成，查询可以并⾏地在所有shard上进⾏处理。这些对⽤户来说是透明的

⽀持SQL

ClickHouse⽀持基于SQL的声明式查询语⾔

⽀持的查询包括 GROUP BY，ORDER BY，IN，JOIN以及⾮相关⼦查询。

不⽀持窗⼝函数和相关⼦查询。

向量引擎

为了⾼效的使⽤CPU，数据不仅仅按列存储，同时还按向量(列的⼀部分)进⾏处理，这样可以更加⾼效地使⽤CPU。

实时的数据更新

ClickHouse⽀持在表中定义主键。为了使查询能够快速在主键中进⾏范围查找，数据总是以增量的⽅式有序的存储 在MergeTree中。因此，数据可以持续不断地⾼效的写⼊到表中，并且写⼊的过程中不会存在任何加锁的⾏为。

索引

按照主键对数据进⾏排序，这将帮助ClickHouse在⼏⼗毫秒以内完成对数据特定值或范围的查找。

适合在线查询

在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到⽤户的⻚⾯中。

⽀持近似计算

ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进⾏加速的⽅法：

1. ⽤于近似计算的各类聚合函数

2. 基于数据的部分样本进⾏近似查询。

3. 不使⽤全部的聚合条件，通过随机选择有限个数据聚合条件进⾏聚合。

⽀持数据复制和数据完整性

ClickHouse使⽤异步的多主复制技术。当数据被写⼊任何⼀个可⽤副本后，系统会在后台将数据分发给其他副本， 以保证系统在不同副本上保持相同的数据。

ClickHouse性能

优点

1，为了⾼效的使⽤CPU，数据不仅仅按列存储，同时还按向量进⾏处理

2，数据压缩空间⼤，减少IO；处理单查询⾼吞吐量每台服务器每秒最多数⼗亿⾏

3，索引⾮B树结构，不需要满⾜最左原则；只要过滤条件在索引列中包含即可；即使在使⽤的数据不在索引中，由于各种并⾏处理机制ClickHouse全表扫描的速度也很快；

4，写⼊速度⾮常快，50-200M/s，对于⼤量的数据更新⾮常适⽤。

缺点

1. 不⽀持事务，不⽀持真正的删除/更新；

2. 不⽀持⾼并发，官⽅建议qps为100，可以通过修改配置⽂件增加连接数，但是在服务器⾜够好的情况下；

3. 不⽀持真正的删除/更新⽀持 不⽀持事务

4. 不支持二级索引

5. 有限的支持sql,join实现与众不同

6. 不支持窗口功能,

7. 元数据需要人工干预维护

8. sql满足日常80%语法,新版支持类sqljoin,性能不好

9. 尽量做1000条以上的批量写入,避免逐行insert,或者小批量的insert,update,delete操作,因为clickhouse底层会不断的做异步的数据合并,会影响查询新能,在这个做实时数据写入的时候尽量要避开

10. clickhouse块是因为采用了并行处理机制,即使一个查询也会用去服务器一半 的cpu去执行,所以clickhouse 不能支持高并发的使用场景,默认但查询使用cpu和数作为服务器核数的一般,安装时会自动识别服务器的和核数,可以通过配置文件修改该参数

相关优化

1.关闭虚拟内存,物理内存和虚拟内存的数据交换会导致查询变慢

2.为每一个账户添加join_user_nulls配置,左表中的一条记录在右表中不存在,右表的相应子弹会返回该字段相应数据类型的默认值,而不是标准sql中的null值

3.join操作时一定把数据量小的表放在右边,因为clickhouse的join总是把右表中的数据拿到左表汇总到左表中查询是否存在,所以右表必须是小表

4.批量写入数据时,必须控制每个批次的数据涉及到分区的数量,,在写入之前最好对需要导入的数据进行排序,无序的数据涉及分区太多会导致clickhouse无法对及时新导入的数据进行合并,从而影响查询性能

5.减少join时的左右表的数据量,必要时可以提前对某张表进行聚合操作,减少数据条数,有些时候,先group by 再查询比反过来查询的时间更短

6clickhouse的分布式性能不如物理表高,建表分区字段值不宜过多,反之数据导入过程中磁盘可能会被打满

7.cpu一般在50%左右会出现查询波动，达到70%会出现⼤范围的查询超时，CPU是最关键的指标，要⾮常关注

性能情况

\1. 单个查询吞吐量：如果数据被放置在page cache中，则⼀个不太复杂的查询在单个服务器上⼤约能够以2-10GB／s（未压缩）的速度进⾏处理（对于简单的查询，速度可以达到30GB／s）。如果数据没有在page cache中的话，那么速度将取决于你的磁盘系统和数据的压缩率。例如，如果⼀个磁盘允许以400MB／s的速度读取数据，并且数据压缩率是3，则数据的处理速度为1.2GB/s。这意味着，如果你是在提取⼀个10字节的列，那么它的处理速度⼤约是1-2亿⾏每秒。对于分布式处理，处理速度⼏乎是线性扩展的，但这受限于聚合或排序的结果不是那么⼤的情况下。

\2. 处理短查询的延时时间：数据被page cache缓存的情况下，它的延迟应该⼩于50毫秒(最佳情况下应该⼩于10 毫秒)。 否则，延迟取决于数据的查找次数。延迟可以通过以下公式计算得知： 查找时间（10 ms） * 查询的列的数量 * 查询的数据块的数量。

\3. 处理⼤量短查询：ClickHouse可以在单个服务器上每秒处理数百个查询（在最佳的情况下最多可以处理数千个）。但是由于这不适⽤于分析型场景。建议每秒最多查询100次。

\4. 数据写⼊性能：建议每次写⼊不少于1000⾏的批量写⼊，或每秒不超过⼀个写⼊请求。当使⽤tab-separated格式将⼀份数据写⼊到MergeTree表中时，写⼊速度⼤约为50到200MB/s。如果您写⼊的数据每⾏为1Kb，那么写⼊的速度为50，000到200，000⾏每秒。如果您的⾏更⼩，那么写⼊速度将更⾼。为了提⾼写⼊性能，您可以使⽤多个INSERT进⾏并⾏写⼊，这将带来线性的性能提升。

count: 千万级别，500毫秒，1亿 800毫秒 2亿 900毫秒 3亿 1.1秒

group: 百万级别 200毫⽶，千万 1秒，1亿 10秒，2亿 20秒，3亿 30秒

join：千万-10万 600 毫秒， 千万 -百万：10秒，千万-千万 150秒

\1. MySQL单条SQL是单线程的，只能跑满⼀个core，ClickHouse相反，有多少CPU，吃多少资源，所以⻜快；

\2. ClickHouse不⽀持事务，不存在隔离级别。ClickHouse的定位是分析性数据库，⽽不是严格的关系型数据库。

\3. IO⽅⾯，MySQL是⾏存储，ClickHouse是列存储，后者在count()这类操作天然有优势，同时，在IO⽅⾯， MySQL需要⼤量随机IO，ClickHouse基本是顺序IO。

有⼈可能觉得上⾯的数据导⼊的时候，数据肯定缓存在内存⾥了，这个的确，但是ClickHouse基本上是顺序IO。对IO基本没有太⾼要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了（数据库⾥太少⻅了，⼤多数都是IO不够⽤）。

基本概念

列式存储

相⽐于⾏式存储，列式存储在分析场景下有着许多优良的特性。

分析场景中往往需要读⼤量⾏但是少数⼏个列

列存模式下，只需要读取参与计算的列即可，极⼤的减低了IO cost，加速了查询。

列存往往有着⾼达⼗倍甚⾄更⾼的压缩⽐，节省了⼤量的存储空间，降低了存储成本。

更⾼的压缩⽐意味着更⼩的data size，从磁盘中读取相应数据耗时更短。

可以针对不同列类型，选择最合适的压缩算法。

⾼压缩⽐，意味着同等⼤⼩的内存能够存放更多数据，系统cache效果更好。

向量化(算法)

ClickHouse不仅将数据按列存储，⽽且按列进⾏计算

对每⼀⾏数据都要调⽤相应的函数，函数调⽤开销占⽐⾼；

存储层按列存储数据，在内存中也按列组织，但是计算层按⾏处理，⽆法充分利⽤CPU cache的预读能⼒，造成CPU Cache miss严重；

按⾏处理，⽆法利⽤⾼效的SIMD指令；

ClickHouse实现了向量执⾏引擎（Vectorized execution engine），对内存中的列式数据，⼀个batch调⽤⼀次SIMD指令（⽽⾮每⼀⾏调⽤⼀次），不仅减少了函数调⽤次数、降低了cache miss，⽽且可以充分发挥SIMD指令的并⾏能⼒，⼤幅缩短了计算耗时。向量执⾏引擎，通常能够带来数倍的性能提升。

SIMD是采⽤⼀个指令流处理多个数据流。

Intel处理器实现的MMXTM、SSE（Streaming SIMD Extensions）、SSE2及SSE3扩展指令集，都能在单个时钟周期内处理多个数据单元。也就是说我们现在⽤的单核计算机基本上都属于SIMD机器。

表

表名和表结构

分⽚

ClickHouse的集群由分⽚ ( Shard ) 组成，⽽每个分⽚⼜通过副本 ( Replica ) 组成。

ClickHouse的1个节点只能拥有1个分⽚，也就是说如果要实现1分⽚、1副本，则⾄少需要部署2个服务节点

分⽚只是⼀个逻辑概念(类似于Hbase中的region的概念,表的范围数据)，其物理承载还是由副本承担的。

分区

ClickHouse⽀持PARTITION BY⼦句，在建表时可以指定按照任意合法表达式进⾏数据分区操作，⽐如通过toYYYYMM()将数据按⽉进⾏分区、toMonday()将数据按照周⼏进⾏分区、对Enum类型的列直接每种取值作为⼀个分区等。

类似于hive中的分区表

副本

数据存储副本,在集群模式下实现⾼可⽤

引擎

就是表的类型,不同的表有不同的特点

服务启动

systemctl status/start/stop clickhouse-server

客户端连接

clickhouse-client [--host=leetom]

⼊⻔操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
CREATE DATABASE IF NOT EXISTS chtest; --使⽤默认库引擎

默认情况下，ClickHouse使⽤的是原⽣的数据库引擎Ordinary(在此数据库下可以使⽤任意类型的表引擎，在绝⼤多数情况下都只需使⽤默认的数据库引擎)。当然也可以使⽤Lazy引擎和MySQL引擎，⽐如使⽤MySQL引擎，可以直接在ClickHouse中操作MySQL对应数据库中的表。假设MySQL中存在⼀个名为clickhouse的数据库，可以使⽤ 下⾯的⽅式连接MySQL数据库。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

CREATE DATABASE mysql_db ENGINE = MySQL('192.168.10.88:3306','leejerry', 'root', '123456');

mysql数据库引擎的库下是不⽀持创建表。

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]( name1 [type1][DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1], name2 [type2][DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2], ...) ENGINE = engine

create table test1(id Int32,name String) engine=Memory; --内存引擎表
insert into test1(id,name) values(110,'goudaner'); -- 字符串不能使用双引号
select * from test1

创建了⼀张内存表，即使⽤Memory引擎。数据只会被保存在内存中，在服务重启时数据会丢失。⼀般应⽤于中间表或者临时表。

分布式DDL操作

默认情况下，CREATE、DROP、ALTER、RENAME操作仅仅在当前执⾏该命令的server上⽣效

在集群环境下，可以使⽤ON CLUSTER语句，这样就可以在整个集群发挥作⽤。

create table if not exists user_cluster on cluster news_ck_cluster(
id Int32,
name String) engine=Distributed(news_ck_cluster,default user_local,id)

Distributed表引擎的定义形式如下所示

Distributed(cluster_name, database_name, table_name[, sharding_key])

cluster_name：集群名称，与集群配置中的⾃定义名称相对应。

database_name：数据库名称

table_name：表名称,映射到每台服务器中的表名称。

sharding_key：可选的，⽤于分⽚的key值，在数据写⼊的过程中，分布式表会依据分⽚key的规则，将数据分布到各个节点的本地表。

--创建本地表
create table if not exists use