常用DDL操作

在集群操作下，必要要加上 **on CLUSTER ‘all-replicated’**，clickhouse中对更新数据很有限，尽量不要操作更新数据。

建表语句

CREATE TABLE 库名.表名 on CLUSTER  'all-replicated'(1)
(
    `id` String,
    元数据  类型
    ...... .....，
    `create_time` DateTime64(3) COMMENT '创建时间',
    INDEX 二级索引名称 元数据 TYPE minmax(2) GRANULARITY(3) 4,
)
ENGINE(4) = ReplicatedMergeTree(5)('zookeeper中的路径，一般用：/应用名/库名/table/{shard}/表名'(6),'{replica}'(7))
PARTITION BY(8) toYYYYMMDD(create_time)
PRIMARY KEY(9) id
ORDER BY(10) 排序字段
SETTINGS index_granularity = 8192(11),index_granularity_bytes=0(12);

(1) on CLUSTER ‘all-replicated’：表示在集群下的所有节点创建

(2) minmax：二级索引中的范围过索引，常见的Set、Bloom Filter Types、minmax等

(3) GRANULARITY：每个索引块由颗粒（granule）组成，上述 GRANULARITY为4，索引的数据=index_granularity * 4 = 4*8192 = 32768

(4) ENGINE：表引擎,上面使用的ReplicatedMergeTree是副本合并树(MergeTree)引擎，与普通的MergeTree人区别多了一个副本(备份)功能，常用的表引擎有MergeTree、TinyLog、Memory等

(5) {shard}:分片通配符，集群会获取有多少个主分区节点(适合大数据量，至少以亿为单位数据)，并在zookeeper上创建对应的表元素文件同步集群的表元素

(6) {replica}：副本通配符集群会获取有主分区下有多少副本节点，并在zookeeper上创建对应的表元素文件同步集群的表元素，如果其中一个zookeeper中表的元素与实表的元素对不上，会产生锁表(只读)，以防止副本之前的数据混乱。

(7) toYYYYMMDD()：格式化日期，例如toYYYYMMDD(‘2023-08-03 06:55:27’) 得到 2023-08-03

(8) PARTITION BY：分区，一般用天分区，一同一张表中分区不宜过多，难以维护，以一张表中1亿条数据为例，建议20-30个分区最优。

(9) PRIMARY KEY：主键，优先选择高基数的元数据，必须遵从最左原则，也就是基数越高应该在最左边。主键索引采用的稀疏主索引，使用二分查询法来定位数据所在快

(10) ORDER BY：排序键，排序字段必须包含主键字段，例如上述主健是id，所以排序字段为：id,create_time

(11) index_granularity = 8192：显式设置为其默认值8192。这意味着对于每一组8192行，主索引将有一个索引条目

(15) index_granularity_bytes=0：设置为0表示禁止自适应索引粒度

删除表数据

DROP

删除表结构和表中数据

1 2	#如果集群一定要带 on CLUSTER 'all-replicated' drop table 表名 on CLUSTER 'all-replicated

TRUNCATE

删除表中的数据

1 2	#如果集群一定要带 on CLUSTER 'all-replicated' TRUNCATE 表名 on CLUSTER 'all-replicated

表优化操作

二级索引(调数索引)

二级索引根据分区、组、快中的记录的最大值与最小值，最好用于dataTime类型的高基数的字段或者，可以有效加速查询速度。

(1) 建表时创建二级索引

在创建表元素中加下列语句，参考创建表语句

1	INDEX 二级索引名称元数据 TYPE minmax(2) GRANULARITY(3) 4,

(2) 对已有的表增加二级索引

1
2
3

alter table 表名 [on CLUSTER  'all-replicated'] add index 索引名(表元素) type 类型 granularity 大小;
例如
alter table test on CLUSTER  'all-replicated'  add index index_create(create_time) type minmax  granularity 4;

对已存在的表新增二级索引，需要手动物化索引

1
2
3

alter tabel 表名 [on CLUSTER  'all-replicated'] MATERIALIZE INDEX 索引名称
#例如
ALTER TABLE test MATERIALIZE INDEX index_create;

(3) 删除二级索引

1
2
3

alter table 表名 [on CLUSTER  'all-replicated'] DROP index 索引名称;
#例如
alter table test on CLUSTER  'all-replicated' DROP index index_create;

优化表

由于clickhouse的机制，当数据插入后不会直接合并到已有分区中，会创建已知已知临时分区，clickhouse会在后台某个时刻自动合并和处理数据。

显示声明clickhouse优化表中数据，需要使用 optimize

1
2
3

optimize table 表名 [on CLUSTER  'all-replicated']
#例如
optimize table test on CLUSTER  'all-replicated'

*注意：应当在空闭时使用上述语句，因为该操作会进行大量的重构，可能会影响业务查询。

系统表中的清理

系统表中会记录大量操作日志，当占用大量存储空间时，可以适当进行删除日志，常用系统日志有如下：

1. system.metric_log
`system.metric_log`表包含了`system.metrics`和`system.events`表中度量值的历史记录，定期刷新到磁盘。

2. system.query_log
ClickHouse的system.query_log表包含有关已执行查询的信息，例如开始时间、处理持续时间和错误消息。

3. system.query_thread_lo
ClickHouse的system.query_thread_log表包含有关执行查询的线程的信息，例如线程名称、线程开始时间和查询处理持续时间。

4. system.trace_log
ClickHouse的system.trace_log表包含采样查询分析器收集的堆栈跟踪。当trace_log服务器配置部分被设置时，ClickHouse会创建此表

5. system.part_log
ClickHouse的system.part_log表仅在指定part_log服务器设置时创建。此表包含有关MergeTree系列表中数据部分发生的事件的信息

6. system.crash_log
ClickHouse的system.crash_log表包含有关致命错误堆栈跟踪的信息。该表默认不存在于数据库中，仅在发生致命错误时才创建。

7. system.text_log
ClickHouse的system.text_log表包含日志条目。可以通过text_log.level服务器设置将进入此表的日志级别进行限制，

不建议直接删除数据，可以采用ddl方式删除表的分区,达到删除数据，避免删除行数据时，索引或者存储文件发生大量变更，clickhouse对数据的更新操作非常困难，因此不建议删除行或者更新行数据

以system.trace_log为例

(1) 查询trace_log表的分区

1	select * from system.parts where database='system' and table='trace_log'

(2) 删除指定的分区

1
2
3

alter table system.trace_log on CLUSTER  'all-replicated' drop partition '分区名称'
#例如，系统日志一般按月分
alter table system.trace_log on CLUSTER  'all-replicated' drop partition '202306'

分析操作

explain

查询sql执行计划

1
2
3

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

例如：explain select now();

➢ PLAN：用于查看执行计划，默认值。

header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0
description 打印计划中各个步骤的描述，默认开启，默认值 1
actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0

➢ AST ：用于查看语法树

➢ SYNTAX：用于优化语法

➢ PIPELINE：用于查看 PIPELINE 计划。

header 打印计划中各个步骤的 head 说明，默认关闭
graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看
actions 如果开启了 graph，紧凑打印打，默认开启

打印当前sql执行的日志

当大量日志记录时，难以找到执行sql执行计划时，可以使用下列命令打印sql语句执行日志信息。

clickhouse-client -h localhost --send_logs_level=trace  <<< " sql语句 "  >  /dev/null

#常用参数解释
-h：连接的ip地址，localhost表示本机或本地
--send_logs_level：表示设置当前这些执行sql的日志打印级别，新增已经默认是info

1
2
3

#执行时出现上面异常错误，说明clickhouse-client 登录有密码, 请加上--password 密码
例如
clickhouse-client -h localhost --password 123456--send_logs_level=trace  <<< " selec * from test where id = '1' "  >  /dev/null