ClickHouse笔记-尚硅谷

1. 数据类型

1.1 整型

后面的数字都是表示位数，bit 位数

有符号
- Int8: 范围 [-128, 127]，对应 Java 里的 byte
- Int16: 范围 [-32768, 32767] 对应 Java 里的 short
- Int32: 范围 [-2147483648, 2147483647] 4个字节，对应 Java 里的 int
- Int64: 8 个字节，对应 Java 里的 long
无符号
- UInt8: 范围 [0, 255]
- UInt16: 范围 [0, 65535]
- UInt32: 范围 [0, 4294967295]
- UInt64

1.2 浮点型

Float32: 4 个字节，对应 Java 里的 float Float64: 8 个字节，对应 Java 里的 double

浮点型存在精度丢失，使用 Decimal 存储

1.3 布尔型

没有 true 和 false，一般使用 UInt8 来存储，取值限制 0 或 1

1.4 Decimal

不会四舍五入，用来保持精度，三种声明方式

Decimal32(s): 相当于 Decimal(9-s, s)，有效位数为 1–9
Decimal64(s): 相当于 Decimal(18-s, s)，有效位数为 1–18
Decimal128(s): 相当于 Decimal(38-s, s)，有效位数为 1–38

这里的 s 需要声明时指定，指的是小数部分保留几位

1.5 字符串

String: 任意长度，包括空字节
FixedString(N): 固定长度 N 的字符串，会在字符串末尾添加空字节来达到 N 字节长度。当读取到长度大于 N 的字符串的时候，会报错

1.6 枚举类型

包括 Enum8 和 Enum16，其中 Enum8 存的是 ‘String’ 和 Integer 的映射关系，Enum16 存的是 ‘String’ 和 ‘String’ 的映射关系

是一种空间上的优化，但是如果插入的数据不在设置的枚举值里面的话，会插入不进去，导致数据丢失

1.7 时间类型

Date: 接受 yyyy-MM-dd 的字符串
DateTime: 接受 yyyy-MM-dd HH:mm:ss 的字符串
DateTime64: 接受 yyyy-MM-dd HH:mm:ss.S 是字符串

1.8 数组类型

Array(T) 其中 T 可以是任意类型，但是不推荐使用多维数组。

不能在 MergeTree 表中存储多维数组

2. 建表语句

2.1 primary key

可选项，可以不指定

只提供数据的一级索引，不提供唯一约束，即可以出现相同 primary key 的数据

主键的设计主要依据查询 sql 中 where 条件

由于主键索引是稀疏索引，可以类比于跳表，根据指定的 index granularity（索引粒度）来进行快速查找。默认是 8192，基本上不需要修改这个值，除非主键会存在大量重复的值，几万行数据才会出现一个不同的主键值

2.2 partition by

可选项，可以不指定

如果不指定分区的话，默认数据都在 all 分区下。

分区就是分目录保存，里面有列文件 + 索引文件 + 表定义文件 + 校验文件等等

bin 文件：数据文件
mrk 文件：标记文件
- 标记文件在 idx索引文件和 bin数据文件之间起到了桥梁作用
- 以 mrk2 结尾的文件，表示该表启用了自适应索引间隔
primary.idx 文件：主键索引文件
minmax.create_time.idx：分区键的最大最小值
checksums.txt：校验文件
count.txt：保存数据条数

分区后，ck 会按照分区为单位进行并行处理

分区目录的命名规则如下: partitionID_MinBlockNum_MaxBlockNum_Level

partitionID
- 根据指定的分区字段生成
MinBlockNum
- 最小分区块的编号，每产生一个新的目录分区就会向上递增
- 如果向同一个分区去不断写数据，可能就会产生新的目录分区块，等待后台异步数据合并
MaxBlockNum
- 最大分区块的编号
Level
- 合并次数

2.3 order by

必选项

保证分区内数据按照指定字段有序保存 <===> 由于要保证主键索引是生效的，那么有序排序是必须的，否则稀疏索引就不生效

主键必须是 order by 指定的字段，同时必须是按照顺序来的。比如 order by (id, name)，那么主键就必须是 id 或者 (id, name)，不能是 name

2.4 二级索引

create table t_order(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime,
INDEX a total_amount Type minmax GRANULARITY 5
)engine = MergeTree()
partition by toYYYYMMDD(create_time)
primary key (id)
order by id

二级索引是在一级索引基础上创建的，GRANULARITY 的值就是二级索引对于一级索引粒度的粒度 <===> 一级索引按照 8192 行数据当成一个块的话，那么 GRANULARITY 5 就是说每 5 块再做一次二级索引

INDEX 索引名字指定字段 Type minmax GRANULARITY N 其中主要用到的二级索引类型是 minmax

2.5 TTL

支持某列或整张表的 TTL

create table t_order(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time + interval 10 SECOND,
create_time DateTime
)engine = MergeTree()
partition by toYYYYMMDD(create_time)
primary key (id)
order by id

需要注意的是，基于某个字段（create_time）来设置过期，那么这个字段不能是主键，同时必须是日期类型

如果要修改已存在的表的列的 TTL 的话，可以通过下面的 sql

1	alter table xxx MODIFY COLUMN col String TTL create_time + INTERVAL 10 SECOND

如果要修改表的 TTL 的话，可以通过下面的 sql

1	alter table xxx MODIFY TTL create_time + INTERVAL 10 SECOND

2.6 optimize

插入的数据会在后台异步合并，如果要手动触发的话，可以执行下面的命令，但是也不保证会立即执行，依旧是异步

1	optimize table xxx final

同时可以指定某个分区进行合并

1	optimize table xxx partition '20210925' final

3. 表引擎

3.1 TinyLog

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

3.2 Memory

内存引擎

3.3 MergeTree

支持索引和分区

3.3.1 ReplacingMergeTree

增加去重功能。然而去重只会在合并的时候实现，合并的时间又是不确定的，所以会存在某些数据仍未被处理的情况

ReplacingMergeTree(c1) 可以指定按照某个列来进行判断，即按照 order by 指定的列进行去重时，按照 c1 取最大的那条数据，如果不指定的话，就按照插入顺序来去重保留

同时去重只在分区内去重，不能跨分区去重。所以可以理解为去重来节约存储空间，并不能解决数据重复问题。

如果说要跨分区来保证查询不重复，可以通过 argMax 函数来实现

3.3.2 SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。

SummingMergeTree(c1,c2) 指定的列必须是数字列，如果不填，会以所有非维度列（order by 指定的是维度列）且为数字列的字段进行聚合

只能在分区内聚合，并且只能在数据合并的时候进行聚合，所以在写 sql 的时候还是得写上 sum

3.3.3 AggregationMergeTree

对比上面的sum，AggregationMergeTree 可以提供更多的聚合函数

4. SQL 操作

4.1 Update 和 Delete

不支持事务操作。通过 alter 来间接实现更新删除

1
2
3

alter table xxx delete where id = 101;

alter table xxx update total_amount = 100 where od = 102;

这些操作都是比较重的操作，不要频繁进行这些修改动作。重指的是语句会分两步执行，第一步是进行增加数据增加分区，第二步在旧分区行上面打上逻辑删除标识。当进行数据合并的时候，会进行真正的数据操作。

可以通过在建表时添加版本号或者类似的标记字段等，来实现 “插入实现更新” 这个功能，也可以通过 AggregationMergeTree 这个引擎

1
2
3

create table xxx {
colName     SimpleAggregateFunction(anyLast, Nullable (UInt32))
}engine = ReplicatedAggregatingMergeTree()

不过这个建表语句可能更适合更新某一个字段，即现在有 c1,c2 两个字段，然后可能单独去更新 c2 这个字段，那么插入数据之后结果如下

1
2
3

c1  c2
v1  v2
null v2'

在查询的时候加上 final，会自动去获取最新的不为 null 的值，返回结果就是 v1, v2'，通过插入来实现更新，但是这样的话，会导致结果里出现 null 值，而 ck 在存储时会单独建立一个文件来记录列中的 null 值信息，会影响一定的存储和查询效率

5. 副本和分片

副本是为了保证集群的高可用，但是每台服务器实际上必须容纳全量的数据，没有解决数据的横向扩容的问题

通过分片将完成的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎将数据拼接起来使用。在实际使用上，分布式表引擎会一定程度上降低查询性能以及在使用上会有一些复杂性，那么可以只使用副本，而不适用分片的方式。

create table t_order_local on cluster cluster_emr (
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time + interval 10 SECOND,
create_time DateTime
)engine =ReplicatedMergeTree('/clickhouse/tables/personas/t_order_local/{shard}',
    '{replica}')
partition by toYYYYMMDD(create_time)
primary key (id)
order by id

通过上面的语句，会在集群下的每台机器上都创建一张 t_order_local 表，那么在写入数据的时候，可以先去在 system.clusters 这个表里拿到集群下的所有机器 ip，然后在执行 sql 的时候对 ip 进行随机抽取，比如 rand 或者轮询的方式吧，到不同的机器上执行 sql 来缓解机器压力

分布式表并不存储真正的数据，做的也近乎是上面的 ip 轮询的一个功能

副本和分片在 ck 里面都是平等的，即没有主从概念

副本之间会自己进行同步，即副本1 和副本2 会自己同步，不需要分布式表来执行同步这件事

在读取数据的时候，会优先选择 error_count 小的副本，如果相同，就会随机、按顺序等等方式。比如网络 io 报错，那么 error_count ++

6. Explain 查看执行计划

1 2	explain [ AST \| SYNTAX \| PLAN \| PIPELINE ] select ...

PLAN: 查看执行计划，默认值

— AST: 查看语法树

SYNTAX: 查看 ck 基于给定的 sql 进行优化后的 sql 结果

只能查看 select 语句

打开三元表达式的优化

1	set optimize_if_chain_to_multiif=1

再去查看sql 的优化语法，如果有三元表达式，就会展示优化后的语法 ==> 会使用 multiif 这个函数

7. 表相关优化

7.1 数据类型

7.1.2 时间

能不存 string 就不存 string，ck 会自动转换。

同时，虽然 ck 底层将 DateTime 存储为时间戳 Long 类型，但是不建议存 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率更高

7.1.3 空值

Nullable 类型几乎总是会影响性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 Null 的标记，并且 Nullable 列无法被索引，可以去使用字段的默认值，而不是去写一个 null

7.2 分区和索引

分区一般按天分区

索引通过 order by 来指定，通常需要满足查询频率高的列在前面的原则

7.3 表参数

index_granularity 索引粒度，可以看情况调整

如果表中不需要保证全量数据，可以去添加 TTL 来自动过期历史数据

7.4 写入和删除优化

批量进行插入和删除操作，避免产生大量的小文件
不要一次性写入过多数据，否则因合并速度赶不上写入速度而报错

8. CPU 参数

在 clickhouse 安装目录下，服务端相应的配置在 config.xml 文件中，客户端相应的配置在 users.xml

配置	描述
backgroud_pool_size	后台线程池大小，默认值是 16。条件允许的前提下可以改成 cpu 的个数 * 2
backgroud_schedule_pool_size	执行后台任务的线程数，默认值是 16，条件允许的前提下可以改成 cpu 的个数 * 2
max_concurrent_queries	每秒最大处理的请求数，默认值是 100。可以改成 150 - 300
max_threads	设置单个查询所能使用的最大 cpu 数，默认是 cpu 的个数
max_insert_threads	针对 insert select，设置并发线程数，默认是 1

9. 内存参数

配置	描述
max_memory_usage	单次查询占用内存的最大值
max_bytes_before_external_group_by	一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行 <===> clickhouse 聚合分两个阶段：查询并建立中间数据、合并中间数据。所以这一项是上一项的一半内存
max_bytes_before_external_sort	xx

10. ck 中的系统表

某些 sql 如果执行时间超过 30s，那么客户端就会报错，因为掉连接了，此时可以通过查询 system.processes 来查看当前后台正在执行的sql，同时拿到对应的 query_id。

根据 query_id 可以在 query_log 里查到历史查询的 sql 记录，以及是否成功、对应的报错原因、使用资源等等

查询clickhouse 表的总行数、压缩前的大小、压缩后的大小、压缩率
select
    sum(rows) as totalRows,
    formatReadableSize(sum(data_uncompressed_bytes)) as data_uncompressed,
    formatReadableSize(sum(data_compressed_bytes)) as data_compressed,
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) as data_compressed_rate
from system.parts
where database = 'rpt' and table = 'test_parquet_local4';
按分区来查询clickhouse 表的总行数、压缩前的大小、压缩后的大小、压缩率
select
    `partition` as par,
    sum(rows) as totalRows,
    formatReadableSize(sum(data_uncompressed_bytes)) as data_uncompressed,
    formatReadableSize(sum(data_compressed_bytes)) as data_compressed,
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) as data_compressed_rate
from system.parts
where database = 'rpt' and table = 'test_parquet_local4'
group by `partition`;
通过 processes 表可以拿到当前正在执行的 sql，以及 query_id，通过下面 sql 可以停止指定的查询
kill query where query_id = '55edd68a-5a45-4b39-b34a-d82b31f63cec';

11. zk

如果在遇到无法创建表（由于表结构已存在或者主键已存在等原因）、无法删除表（表不存在或超出内存无法删除）等等，可以直接在 zk 客户端处理

进入到机器后 sh $ZOOKEEPER_HOME/bin/zkCli.sh 可以启动 zk 客户端

从根目录开始查看文件
ls /

一直往下查看，可以看到库下面有哪些表
ls /clickhouse/tables/rpt

删除库下的某个表
deleteall /clickhouse/tables/rpt/test_par2_local