Clickhouse基础和基本优化

一、ClickHouse的特点

列式存储

行式存储:
好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。

行式存储在插入操作只需要直接在数据末尾添加一行数据，具备较好的性能。

列式存储
➢ 对于列的聚合，计数，求和等统计操作原因优于行式存储。
➢ 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
➢ 由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于 cache 也有了更大的发挥空间。

列式存储在查询操作具有很好的性能表现，非常适合一次插入多次读取的大数据场景

高吞吐写入能力

ClickHouse采用类 LSM Tree 的结构，数据写入后定期在后台 Compaction 。通过类 LSM tree的结构， ClickHouse 在数据导入时全部是顺序 append 写，写入后数据段不可更改，在后台compaction 时也是多个段 merge sort 后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在 HDD 上也有着优异的写入性能。

类似HBase的Compaction，删除数据时不会立即将数据删除，通过给数据进行标记，在Compaction阶段才会将数据真正删除，通过version控制数据的版本问题。
类似Kafka的顺序磁盘写，大大提高了数据写入效率。

官方公开benchm ark 测试显示能够达到 50MB 200MB/s 的写入吞吐能力，按照每行
100Byte 估算，大约相当于 50W 200W 条 /s 的写入速度。

数据分区与线程级并行

ClickHouse将数据划分为多个 partition ，每个 partition 再进一步划分为多个 indexgranularity 索引粒度 ，然后通过多个 CPU 核心分别处理其中的一部分来实现并行数据处理。在这种设计下， 单条 Query 就能利用整机所有 CPU 。 极致的并行处理能力，极大的降低了查询延时。

CK单条Query就能够使用整机的CPU，因此CK的瓶颈也多数存在于CPU，因此在数仓架构中CK大多数用来存储宽表。

表引擎的使用

表引擎是Clinckhouse的一大特色，表引擎决定了车如何存储表的数据包括：
》数据的存储方式和位置，数据的写入和读取位置
》支持的查询方式
》并发数据访问
》索引的使用（如果存在）
》是否可以执行多线程请求
》数据的复制参数

表引擎的使用方式必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。
特别注意：引擎的名称大小写敏感

TinyLog
以列式的形式保存在磁盘，不支持索引，没有并发控制。一般保存少量数据的小表。

一般不在生产环境使用，可以用于平时练习测试
create table t_tinylog（id String，name String）engine=TinyLog；

Memory
内存引擎，数据以未压缩的原始形式直接保存在内存中，服务器重启数据就会消失，读写操作不回阻塞，不支持索引。简单查询下有非常好的性能表现。

在需要使用非常高的性能并且数据量不是很大（大约一亿行）的场景下使用。

MergeTree

CK中最强大的表引擎当属MergeTree引擎以及该系列中的其它引擎，支持索引和分区，相当于Innodb之于Mysql。

1.建表语句

create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

CK中主键没有唯一性限制

2.插入数据

insert into t_order_mt values
(101,'sku_001',1000.00,'2020 06 01 12:00:00') ,
(102,'sku_002',2000.00,'2020 06 01 11:00:00'),
(102,'sku_004',2500.00,'2020 06 01 12:00:00'),
(102,'sku_002',2000.00,'2020 06 01 13:00:00')
(102,'sku_002',12000.00,'2020 06 01 13:00:00')
(102,'sku_002',600.00,'2020 06 02 12:00:00')

3. 执行select

select * from t_order_mt;

4. 参数选项

partition by 分区(可选)
分区的目的是降低全表扫描的范围，优化查询速度。

如果没有使用分区，默认使用一个分区,目录名称为all

分区目录:
MergeTree是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

并行:
对表进行分区后，面对涉及跨分区的查询统计,CK会以分区为单位进行处理。

数据写入和分区合并:
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。
写入后的某个时刻（大概 10 15 分钟后）， ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中。

当再次执行上边相同的插入操作时，表中数据不回立马进行合并

执行手动合并操作后，表中数据才能进行合并。

optimize table t_order_mt final;

执行合并操作后，存储在磁盘的原有分区数据不回立即删除，等到自动合并时间触发后,才会将这些过期数据删除。

primary key 主键(可选)
ClickHouse中的主键，和其他数据库不太一样， 它只提供了数据的一级索引，但是却不
是唯一约束。 这就意味着是可以存在相同 primary key的数据的。
主键的设定主要依据是查询语句中的where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity, 避免了全表扫描。

index granularity 直接翻译的话就是索引粒度，指在 稀疏索引 中两个相邻索引对应数据的间隔。 ClickHouse 中的 MergeTree 默认是 8 192 。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

order by(必选)
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理（比如去重和汇总）。

要求：主键必须是order by 字段的前缀字段。
比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者 (id,sku_id),不能为sku_id。
因为索引的添加跟主键字段有关，如果使用sku_id为索引字段,但order by id,sku_id使用id进行排序，索引将会失效。

order by 是必选项，因为CK采用稀疏索引，因此在使用二分法使用索引时，如果索引不是有序的，那么将无法使用二分法查找数据。

二级索引
目前在ClickHouse 的官网上二级索引的功能 在 v 20.1.2.4 之前 是被标注为实验性的 ，在
这个版本之后默认是开启的 。

假设我们的数据从1-10000，其中存在多个重复值1,通过一级索引将数据分为[1,1] [1,1] [1,10] [10,10000]，那么当我们需要查找值为15的数据时，需要从头到尾遍历索引，寻址4次找到[10,10000]索引。
二级索引是粒度更大的索引，其粒度指的是多个一级索引相聚合,假设二级索引粒度为3，则数据索引分为[1,10],[10,10000]…在寻址时提高查询速率。

ReplacingMergeTree

ReplacingMergeTree是 MergeTree的一个变种，它存储特性完全继承 MergeTree，只是多了一个 去重 的功能。 尽管 MergeTree可以设置主键，但是 primary key其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

(1) 去重时机
**数据的去重只会在合并的过程中出现。**合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。
(2) 去重范围
如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

案例:

1. 创建表

create table t_order_rmt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime
) engine=ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id)

ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留 最后一条。

2. 插入数据

insert into t_order_rmt values
(101,'sku_001',1000.00,'2020 06 01 12:00:00') ,
(102,'sku_002',2000.00,'2020 06 01 11:00:00'),
(102,'sku_004',2500.00,'2020 06 01 12:00:00'),
(102,'sku_002',2000.00,'2020 06 01 13:00:00')
(102,'sku_002',12000.00,'2020 06 01 13:00:00')
(102,'sku_002',600.00,'2020 06 02 12:00:00')

3. 执行第一次查询

select * from t_order_

4. 手动合并后，再次执行查询

➢ 实际上是使用 order by 字段作为唯一键
➢ 去重不能跨分区
➢ 只有 同一批插入（新版本）或 合并分区 时 才会进行去重
➢ 认定重复的数据保留，版本字段值最大的
➢ 如 果版本字段相同则 按插入顺序 保留最后一笔

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree

1. 建表并插入数据

create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime
) engine=SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );

insert into t_order_smt values
(101,'sku_ 001',1000.00,'2020 06 01 12:00:00')
(102,'sku_002',2000.00,'2020 06 01 11:00:00'),
(102,'sku_004',2500.00,'2020 06 01 12:00:00'),
(102,'sku_002',2000.00,'2020 06 01 13:00:00'),
(102,'sku_002',12000.00,'2020 06 01 13:00:00'),
(102,'sku_002',600.00,'2020 06 02 12:00:00');

2. 执行查询
   
3. 执行合并后再次查询
   

➢ 以 SummingMergeTree（）中指定的列作为汇总数据 列
➢ 可以填写多列必 须数字列，如果不填，以所有非维度列且为数 字列的字段 为汇总数
据列
➢ 以 order by 的列为准，作为维度列
➢ 其他的列 按插入顺序 保留第一行
➢ 不在一个分区的数据不会被聚合
➢ 只有在同一批次插入 (新版本 )或分片合并时才会进行聚合

二、SQL操作

基本上来说传统关系型数据库（以MySQL为例）的 SQL语句， ClickHouse基本 都 支持这里只介绍 ClickHouse与标准 SQL MySQL）不一致的地方。

1.Insert

基本与标准SQL MySQL）基本一致
（1 标准
insert into [table_name] values(…),(….)
（2 从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]

2.Update 和 Delete

ClickHouse提供了 Delete和 Update的能力，这类操作被称为 Mutation查询，它可以看做 Alter 的一种。

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样， Mutation语句是一种很“重”的操作，而且不支持事务。

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

1. 删除操作

alter table t_order_smt delete where sku_id ='sku_001';

2. 修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id

由于操作比较“重”，所以Mutation语句分两步执行，同步执行的部分其实只是进行**新增数据新增分区和并把旧分区打上逻辑上的失效标记。**直到 触发 分区合并的时候，才会删除旧数据释放磁盘空间 ，一般不会开放这样的功能给用户，由管理员完成。

CK通过复制分区数据并对其进行修改，将旧分区进行标记，等到下次数据合并的时候才会完成数据资源的释放。

3.查询操作

ClickHouse基本上与标准 SQL 差别不大
➢ 支持子查询
➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句 )
➢ 支持各种 JOIN 但是 JOIN操作无法使用缓存，所以即使是两次相同的 JOIN语句，
ClickHouse也会视为两条新 SQL
➢ 窗口函数 (官方 正在测试中 …)
➢ 不支持自定义函数
➢ GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

CK会将join中右表数据存储到内存中，然后再进行join。

(1) 插入数据

hadoop102 :) alter table t_order_mt delete where 1=
insert into t_order_mt values
(101,'sku_ 001',1000.00,'2020 06 01 12:00:00')
(10 1 ,'sku_ 002',2000.00,'2020 06 01 12:00:00')
(103,'sku_ 004',2500.00,'2020 06 01 12:00:00')
(104,'sku_002',2000.00,'2020 06 01 12:00:00')
(105,'sku_003',600.00,'2020 06 02 12:00:00'),
(106,'sku_001',1000.00,'2020 06 04 12:00:00'),
(107,'sku_002',2000.00,'2020 06 04 12:00:00'),
(108,'sku_004',2500.00,'2020 06 04 1 2:00:00'),
(109,'sku_002',2000.00,'2020 06 04 12:00:00'),
(110,'sku_003',600.00,'2020 06 01 12:00:00');

(2) with rollup 从右至左去掉维度进行小计(上卷)

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with rollup;

第一个表相当于group by id,sku_id;
第二个表相当于group by id
依次从左到右递减

(3) with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with cube;

第一个相当于group by id,sku_id
第二个相当于group by id
第三个相当于group by sku_id

(4) with totals: 只计算合计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by
id,sku_id with totals;

4.alter操作

同MySQL的修改字段基本一致
1 新增字段

alter table tableName add column newcolname String after col1

2 修改字段类型

alter table tableName modify column newcolname String

3 删除字段

alter table tableName drop column newcolname

5.导出数据

clickhouse client query "select * from t_order_mt where
create_time='2020 06 01 12:00:00'" format CSVWithNames>/opt/module/data/rs1.csv

点击查看更多支持格式参照

三、基于表的分布式集群

CK不同于传统的Master/Slave模式的分布式一主多从集群，其拥有两种基于表的分布式集群，分别为分片集群和副本集群

副本集群是将数据副本分布在多个节点上，以提高数据的可靠性和容错性。在副本集群中，每个节点都存储着相同的数据副本，当一个节点发生故障时，系统会自动将请求路由到其他节点上，从而保证数据的可用性和查询性能。副本集群适用于需要高可用性和数据保护的应用场景，如金融、电商等。

分片集群是将数据分片存储在多个节点上，以提高数据的并行处理能力和查询性能。在分片集群中，一个表可以被分成多个分片，每个分片可以被存储在不同的节点上，从而实现数据的分布式存储和查询。分片集群适用于需要大规模数据存储和查询的应用场景，如大数据分析、数据挖掘等。

需要注意的是，副本集群和分片集群并不是互斥的，它们可以结合使用，以达到更高的可靠性和性能。例如，在一个分片集群中，每个分片都可以配置多个副本，从而实现数据的高可用性和容错性。

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

Distributed表引擎本身不存储数据， 有点类似于 MyCat之于MySql，成为一种中间件通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

值得注意的是:
ClickHouse的集群是表级别的 实际企业中 大部分做了高可用 但是没有用分片，避免降低查询性能以及操作集群的复杂性。

集群写入流程（ 3分片 2副本共 6个节点）

1. 客户端向目标表的Distributed表引擎所在节点发送写入请求。
2. 接收到请求的节点将数据进行分片，然后将每个分片的数据分别发送给对应的副本节点。
3. 副本节点接收到数据后，将数据写入本地磁盘，如果该分片设置了多个副本，那么当前副本节点还需要将分片数据发送到对应节点，并将写入成功的确认消息返回给主节点。
4. 主节点在接收到所有副本节点的写入确认消息后，将写入操作标记为完成，并向客户端返回写入成功的响应。

在上述流程中，每个分片节点都只负责处理自己分片的数据，因此可以并行处理多个分片的写入请求，从而提高写入性能。如果某个分片节点写入失败，则主节点会将该分片节点从可用节点列表中移除，并将数据发送给其他可用的分片节点进行写入。如果所有分片节点都写入失败，则写入操作失败，主节点将向客户端返回写入失败的响应。

集群读取流程（ 3分片 2副本共 6个节点）

1. 客户端向目标表的Distributed表引擎所在节点发送读取请求。
2. 接收到请求的节点将请求发送给对应的分片节点。
3. 分片节点从本地磁盘读取数据，并将数据返回给主节点。
4. 主节点将所有分片节点返回的数据进行合并，并将合并后的结果返回给客户端。

errors_count为数据读取失败次数

CK通过在节点创建本地表，在创建一张分布式表管理对应节点的本地表。

四、建表优化

数据类型

时间类型字段

建表时能用数值型或日期时间型表示的字段就不要用字符串，全String 类型在以 Hive为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。

虽然ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。

create table t_type2(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Int32
) engine =ReplacingMergeTree(create_
partition by toYYYYMMDD( toDate(create_time) 需要转换一次 ，否则报错
primary key (id)
order by (id, sku_id);

空值存储类型
官方已经指出Nullable 类型几乎总是会拖累性能 ，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值 （例如用 1 表示没有商品ID ）。

在存储Nullable字符串类型时，可以使用空字符串表示Nullable类型；在存储Int类型时可以使用无意义的值表示空值。

分区和索引

分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区 ，也可以指定为以单表一亿数据为例，分区大小控制在 10-30个为最佳。

必须指定索引列，ClickHouse中的 索引列即排序列 ，通过 order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、 查询频率大的在前 原则；还有基数特别大的不适合做索引列，如用户表的 userid字段；通常 筛选后的数据满足在百万以内为最佳 。

ClickHouse中不建议使用基数大的列作为索引列，主要是因为基数大的列会导致索引占用较大的内存空间，进而影响查询性能和系统稳定性。基数大的列通常指的是具有大量不同取值的列，例如时间戳、UUID等。这些列的取值范围很大，而且取值分布比较均匀，因此索引会占用大量的内存空间。

另外，基数大的列作为索引列还会导致写入性能下降。因为每次写入操作都需要更新索引，而基数大的列会导致索引更新的开销较大。如果频繁进行写入操作，就会导致系统的写入性能下降。

稀疏索引是指只对部分行进行索引的索引方式，相对于密集索引而言，它能够减少索引占用的空间。但是，对于大基数的列作为索引列，稀疏索引的效果并不好。因为大基数的列中有很多重复的值，这会导致索引的覆盖率较低，即索引所覆盖的行数较少，从而导致查询效率低下。此时，使用稠密索引或者其他索引方式可能更加适合。

表参数

Index_granularity是用来控制索引粒度的 默认是 8192 如非必须不建议调整。如果表中不是必须保留全量历史数据，建议指定TTL （生存时间值），可以免去手动过期历史数据的麻烦， TTL 也可以通过 alter table 语句随时修改。

写入和删除优化

（1 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge任务带来巨大压力
（2 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge速度跟不上而报错，一般建议每秒钟发起 2-3次写入操作，每次操作写入 2w~5w条数据（依服务器性能而定）

处理方式：
“Too many parts 处理 ：使用 WAL预写日志，提高写入性能 。
默认开启WAL预写日志:in_memory_parts_enable_wal 默认为 true

在服务器内存充裕的情况下增加内存配额，一般通过max_memory_usage来实现
在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、 max_bytes_before_external_sort参数来实现 。

五、语法优化规则

COUNT 优化

CK在调用count 函数时 如果使用的是 count() 或者count(*)且没有 where 条件 则会直接使用 system.tables 的 total_rows:

EXPLAIN SELECT count()FROM datasets.hits_v1

Union
	Expression (Projection)
		Expression (Before ORDER BY and SELECT)
			MergingAggregated
				ReadNothing (Optimized trivial count)

CK在表数据文件中维护了一个count()结果文件，其中存储了该表的count()值，在需要使用时能够直接查看而不需要再次计算。

如果使用的是count(column_name)则不能够使用该优化

EXPLAIN SELECT count( CounterID FROM datasets hits_v1
Union
	Expression (Projection)
		Expression (Before ORDER BY and SELECT)
			Aggregating
				Expression (Before GROUP BY)
					ReadFromStorage (Read from MergeTree)

消除子查询重复字段

EXPLAIN SYNTAX 
	SELECT
		a.UserID,
		b.VisitID,
		a.URL,
		b.UserID
	FROM
		hits_v1 AS a
	LEFT JOIN (
		SELECT
			UserID,
			UserID as HaHa ,
			VisitID
		FROM visits_v1) AS b
	USING (UserID)
	limit 3;

语句优化后:

SELECT
	UserID,
	VisitID,
	URL,
	b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN
	SELECT
		UserID,
		VisitID
	FROM visits_v1) AS b 
USING (UserID)
LIMIT 3

和其他传统的数据库不同，CK中不能够同时查询出相同的两个列，这在查询时提高了一定的效率，但是也存在一些需求上的问题。

谓词下推

当group by有 having子句，但是没有 with cube、 with rollup 或者 with totals修饰的时候， having过滤会下推到 where提前过滤 。例如下面的查询， HAVING name变成了 WHERE name，在 group by之前过滤

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID =
'8585742290196126178'
返回的优化语句
SELECT UserID
FROM hits_v1
WHERE UserID = \'8585742290196126178\'
GROUP BY UserID

子查询和一些复制的查询也是拥有谓词下推优化的，将一些过滤操作提前进行，减少数据量后再执行之后的查询语句。

聚合计算外推

聚合函数内的计算会外推 例如

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1
返回优化后的语句
SELECT sum(UserID) * 2
FROM visits_v1

聚合函数消除

如果对聚合键，也就是group by key 使用 min、 max、 any 聚合函数，则将函数消除，
例如：

EXPLAIN SYNTAX
SELECT
	sum(UserID * 2),
	max(VisitID),
	max(UserID)
FROM visits_v1
GROUP BY UserID
返回优化后的语句
SELECT
	sum(UserID) * 2,
	max(VisitID),
	UserID
FROM visits_v1
GROUP BY UserID

Prewhere替代 where

prewhere和 where语句的作用相同用来过滤数据。不同之处在于 prewhere 只支持*MergeTree 族系列引擎的表 ，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
当查询列明显多于筛选列时使用Prewhere 可十倍提升查询性能， Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。
在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

where语句会将符合条件的数据行先取出，之后再根据select语句的选择字段进行过滤；
prewhere语句能够将先将符合条件的列取出，之后再根据select语句选择字段进行结果的补充完成过滤；

关闭 where 自动转 prewhere 默认情况下， where 条件会自动优化成 prewhere
set optimize_move_to_prewhere =0;
# 使用 where
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate
from datasets.hits_v1 where UserID='3198390223272470366'
# 使用 pre where 关键字
select WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate
from datasets.hits_v1 pre where UserID='3198390223272470366'

默认情况，我们肯定不会关闭where 自动优化成 prewhere ，但是某些场景即使开启优化，也不会自动转换成 prewhere ，需要手动指定 prewhere
⚫ 使用常量表达式
⚫ 使用默认值为 alias 类型的字段
⚫ 包含了 arrayJOIN globalIn globalNotIn 或者 indexHint 的查询
⚫ select 查询的列字段和 where 的谓词相同
⚫ 使用了主键字段

列裁剪与分区裁剪

数据量太大时应避免使用select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的 io 资源越少，性能就会越高。

分区裁剪就是只读取需要的分区在过滤条件中指定 。

实际生产环境中，因为资源的限制，查询语句中需要使用什么字段就查询什么字段，放置多余的数据造成的资源损失。

orderby 结合 where、 limit

千万以上数据集进行order by查询时需要搭配 where条件和 limit语句一起使用。

正例：
SELECT UserID,Age
FROM hits_v1
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000
反例：
SELECT UserID,Age
FROM hits_v1
ORDER BY Age DESC

避免构建虚拟列

如非必须不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者 在 表中构造实际字段进行额外存储。

正例:
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
正例：拿到 Income 和 Age 后， 考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;

虚拟列，表中不存再的虚拟字段，由用户查询语句构成，例如上边的Income/Age，该字段需要消耗大量的资源。

join_use_nulls和批量写入排序

应为每一个账户添加join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL中的 Null。

CK中Null值会极大拖累性能，Null值不能够构建索引并且还需要特别的创建一个文件对Null值进行标记。

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

CK中的主键能够提供一级索引，但是却不能够进行唯一性约束，CK中Order by是建表语句中必选关键词，CK采用稀疏索引，要求数据具有顺序性。

uniqCombined替代 distinct

性能可提升10倍以上 uniqCombined底层采用类似 HyperLogLog算法实现 能接收 2%左右的数据误差可直接使用这种去重方式提升查询性能。 Count (distinct)会使用 uniqExact精确去重

不建议在千万级不同数据 上执行 distinct去重查询，改为近似去重 uniqCombined

反例：
select count(distinct rand()) from hits_v1;
正例：
SELECT uniqCombined( rand (())) from datasets. hits_v1

CK不建议使用Distinct对数据进行去重:
DISTINCT需要对所有数据进行排序和去重操作，当处理大量数据时，会导致性能问题。
DISTINCT操作会占用较多的内存，如果内存不足，可能会导致任务失败。
tip:
CK中的索引数据列是有序的，但是使用DISTINCT操作会导致数据重新排序，因此会降低查询性能。ClickHouse提供了更高效的去重方法，例如使用合适的数据分区和使用DistinctMergeTree引擎等，这些方法可以更有效地去重数据，提高查询性能。

Hive也同样不建议使用distinct对数据进行去重:
在Hive中，DISTINCT操作会转换为MapReduce任务，而MapReduce任务的启动和关闭需要一定的时间，会增加查询的响应时间。
相比之下，使用GROUP BY关键字可以更有效地去重，因为它可以在MapReduce任务中进行本地去重，从而减少了数据的传输量和排序操作。因此，当处理大量数据时，建议使用GROUP BY来进行去重操作，而不是使用DISTINCT。

六、JOIN规则和优化

什么是Map join
Map Join是一种优化技术，它可以将小表加载到内存中，然后将大表的每一行数据与小表进行JOIN操作，从而提高查询性能。

1.将小表加载到内存中，生成一个哈希表。
2.扫描大表，并将每一行数据的JOIN列的值作为Key，将整行数据作为Value，存储在内存中的哈希表中。
3.对于每一行大表数据，将其JOIN列的值作为Key，在内存中的哈希表中查找是否有匹配的小表数据。
4.如果有匹配的小表数据，则将两张表的数据进行JOIN操作，并输出结果。

Map Join的优点是可以将小表加载到内存中，避免了大表的全表扫描，从而提高查询性能。但是，Map Join也有一些限制：

1.小表必须能够全部加载到内存中，否则无法使用Map Join。
2.JOIN条件必须是等值JOIN，否则无法使用Map Join。
3.Hive需要在运行时自动判断是否使用Map Join，因此在某些情况下，可能无法使用Map Join。

和Map Join类似，但ClickHouse中无论是 Left join 、 Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在 所以要求右表必须是小表。

因此在大表join大表时，CK表现极其不稳定，因为将大表进行缓存可能出现内存溢出。所以CK是一个不适合Join的数据库。

用 IN 代替 JOIN

当多表联查时查询的数据仅从其中一张表出时 可考虑用 IN 操作而不是 JOIN

insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from
visits _v1

反例：使用 join
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a. CounterID=b.
CounterID;

分布式表使用 GLOBAL

两张分布式表 上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字 右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上 。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N ² 次（ N是该分布式表的 分片 数量），这就是 查询放大 ，会带来 很大开销 。

在表进行分片后，如果使用join，需要将分片数据分别进行缓存和Join，因此需要GLOBAL对数据进行公布。文章来源地址https://www.toymoban.com/news/detail-592270.html

到了这里，关于Clickhouse基础和基本优化的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！