ClickHouse 随笔

一、ClickHouse初级

1.1 ClickHouse入门

1.1.1 概念

ClickHouse 的全称是 Click Stream，Data WareHouse，简称 ClickHouse

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

补充. OLAP VS OLTP:

OLAP（On-Line Analytical Processing） ： 联机分析处理【ClickHouse、HBase ...】,查询功能强大 ！
OLTP（On-line Transaction Processing）： 联机事务处理【MySql、Oracle ...】,增删改功能强大 ！   

1.1.2 特点

1. 列式存储

行式存储：按照记录进行数据的存储（一行数据就是一条记录）【MySql、Oracle】

列式存储：按照字段进行数据的存储（一个字段就是一条记录）【ClickHouse、HBase】

其实我们在进行查询的时候就是按照*列式*进行查询的：select 字段1,字段2 from table_A;

所以说，列式存储的数据结构应用在查询的场景中最合适！！！

以下面的表为例：

Id	Name	Age
1	张三	18
2	李四	22
3	王五	34

行式存储：

采用行式存储时，数据在磁盘上的组织结构为：

优点：在想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，需要不停的查找进行磁头的寻址，或者全表扫描才行，遍历的很多数据都是不需要的。但是在进行增、删、改某条数据时，效率高。

列式存储：

采用列式存储时，数据在磁盘上的组织结构为：

这时想查所有人的年龄只需把年龄那一列拿出来就可以了。

但是若向表中插入某条数据时，需要磁头不停的寻址，效率低。

列式储存的好处：

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

2. DBMS的功能

几乎覆盖了标准SQL的大部分语法，包括 DDL和 DML，以及配套的各种函数、用户管理及权限管理、数据的备份与恢复

3. 多样化引擎

ClickHouse和MySQL类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类20多种引擎。

4. 高吞吐写入能力

ClickHouse采用类LSM Tree（Log Structured Merge Trees）的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写（相比较于随机写，效率快了6000倍），写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在HDD上也有着优异的写入性能。

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

HBase是依赖于时间戳来分辨新老版本，老版本数据不会删除，在最终的大合并时才会执行删除，ClickHouse和HBase一样的操作，但是大合并时性能下降，不对外提供服务。

5. 数据分区与线程级并行

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

所以，ClickHouse即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多cpu，就不利于同时并发多条查询。所以对于高QPS(Query Per Seconds：每秒查询率)的查询业务，ClickHouse并不是强项。

6. 性能对比

1）单表查询

2）关联查询

结论: ClickHouse像很多OLAP数据库一样，单表查询速度由于关联查询，而且ClickHouse的两者差距更为明显。

7. 对比HBase

Clickhouse和HBase都是列式存储，此处只做最简单的说明。

Clickhouse是纯粹的列式存储结构，而HBase是列祖式存储，存储结构是多维map结构。

更多详细内容请查看此链接：https://blog.youkuaiyun.com/u011487470/article/details/120879665

8. 缺点

根据前面涉及到的[5. 数据分区与线程级并行](# 5. 数据分区与线程级并行)可以得知：

①：ClickHouse是一个相当吃CPU的框架

②：QPS能力弱

③：join慢，应该避免（底层实现有缺陷），A join B【先把右表B加载到内存，然后和左表A逐一关联】，降低了性能。

所以在实际的开发场景中，ClickHouse不适合处理初始表的查询，适合于关联后的大表（宽表【都是join后的数据】）。

1.2 ClickHouse的安装

1.2.1 准备工作

1. 关闭防火墙

2. CentOS调大打开文件数限制

0. 前置知识

   [heather@hadoop102 ~]$ ulimit -a （用来显示当前的各种用户进程限制）
   core file size (blocks, -c) 0
   data seg size (kbytes, -d) unlimited
   scheduling priority (-e) 0
   file size (blocks, -f) unlimited
   pending signals (-i) 31772
   max locked memory (kbytes, -l) 64
   max memory size (kbytes, -m) unlimited
   open files (-n) 65536 打开的文件数量限制
   pipe size (512 bytes, -p) 8
   POSIX message queues (bytes, -q) 819200
   real-time priority (-r) 0
   stack size (kbytes, -s) 8192
   cpu time (seconds, -t) unlimited
   max user processes (-u) 131072 执行的最多的进程数量限制
   virtual memory (kbytes, -v) unlimited
   file locks (-x) unlimited

   

1. 在hadoop102的 /etc/security/limits.conf文件的末尾加入以下内容

   [heather@hadoop102 ~]$ sudo vim /etc/security/limits.conf
   * soft nofile 65536
   * hard nofile 65536
   * soft nproc 131072
   * hard nproc 131072
   

2. 在hadoop102的/etc/security/limits.d/20-nproc.conf文件的末尾加入以下内容==(会覆盖limits.conf文件中的nproc设置)==

   [heather@hadoop102 ~]$ sudo vim /etc/security/limits.d/20-nproc.conf
   * soft nofile 65536
   * hard nofile 65536
   * soft nproc 131072
   * hard nproc 131072
   

3. 执行同步操作

   [heather@hadoop102 ~]$ sudo xsync /etc/security/limits.conf 
   [heather@hadoop102 ~]$ sudo xsync /etc/security/limits.d/20-nproc.conf 
   

3. 安装依赖

[heather@hadoop102 ~]$ sudo yum install -y libtool

[heather@hadoop102 ~]$ sudo yum install -y *unixODBC* 

在hadoop103、hadoop104上执行以上操作。

4. CentOS取消SELINUX

SELINUX：S—>Security安全、E—>enhance增强

1. 查看Linux系统的增强安全策略

   [heather@hadoop102 ~]$ getenforce
   Permissive
   

2. 修改/etc/selinux/config中的SELINUX=disabled

   [heather@hadoop102 ~]$ sudo vim /etc/selinux/config
   SELINUX=disabled
   

3. 执行同步操作

   [heather@hadoop102 ~]$ sudo xsync /etc/selinux/config
   

4. 重启三台服务器

1.2.2 单机安装

1. 在/opt/software下创建clickhouse目录

[heather@hadoop102 software]$ mkdir clickhouse

2. 上传安装包

3. 同步安装包

[heather@hadoop102 software]$ xsync clickhouse

4. 安装与卸载

4.1 安装

[heather@hadoop102 clickhouse]$ sudo rpm -ivh *.rpm
    
在安装的时候会提示给默认用户（default user）设置一个登录密码：此密码存储位置在下面文件中，是加密显示的。也可以不设置此密码。
Password for default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.

sudo rpm -qa|grep clickhouse查看安装情况

ClickHouse的4个重要文件位置

bin/ ===> /usr/bin/

conf/ ===> /etc/clickhouse-server/

lib/ ===> /var/lib/clickhouse

log/ ===> /var/log/clickhouse

4.2 卸载

1. 查看安装的clickhouse

   [heather@hadoop102 clickhouse]# rpm -qa|grep clickhouse

   clickhouse-common-static-21.7.3.14-2.x86_64
   clickhouse-client-21.7.3.14-2.noarch
   clickhouse-common-static-dbg-21.7.3.14-2.x86_64
   clickhouse-server-21.7.3.14-2.noarch

2. 删除安装包

   yum remove -y clickhouse-common-static
   yum remove -y clickhouse-server
   yum remove -y clickhouse-client
   yum remove -y clickhouse-server-common

3. 删除配置文件

   rm -rf /var/lib/clickhouse
   rm -rf /etc/clickhouse-*
   rm -rf /var/log/clickhouse-server

5. 修改配置文件

[heather@hadoop102 clickhouse]$ sudo vim /etc/clickhouse-server/config.xml

1. 把 <listen_host>::</listen_host> 的注释打开，这样的话才能让ClickHouse被除本机以外的服务器访问

   下面对于ClickHouse的远程访问设置做详细说明：

   • 允许IP4和IP6源主机远程访问

     <listen_host>::</listen_host>
     

   • 仅允许IP4主机远程访问

     <listen_host>0.0.0.0</listen_host>
     

   • 仅允许本地访问

     <!-- Default values - try listen localhost on ipv4 and ipv6: -->
     <!--
     	<listen_host>::1</listen_host>
     	<listen_host>127.0.0.1</listen_host>
     -->
     

   • 应该如何设置

          <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
         <listen_host>::</listen_host> 
         <!-- Same for hosts with disabled ipv6: -->
         <!-- <listen_host>0.0.0.0</listen_host> 此注释不宜和<listen_host>::</listen_host> 同时放开，否则会端口占用异常-->
     
         <!-- Default values - try listen localhost on ipv4 and ipv6: -->
         <!--
         <listen_host>::1</listen_host>
         <listen_host>127.0.0.1</listen_host>
         -->
     

2. 分发配置文件

   [heather@hadoop102 clickhouse]$ sudo xsync /etc/clickhouse-server/config.xml
   

   在这个文件中，有ClickHouse的一些默认路径配置，比较重要的

   数据文件路径： /var/lib/clickhouse/

   日志文件路径：/var/log/clickhouse-server/clickhouse-server.log

6. 启动Server

[heather@hadoop102 clickhouse]$ sudo systemctl start clickhouse-server
安装包的启停命令：
    启动：sudo clickhouse start
    停止：sudo clickhouse stop
    查看：sudo clickhouse status
    重启：sudo clickhouse restart

7. 查看状态

[heather@hadoop102 clickhouse]$ sudo systemctl status
[heather@hadoop102 clickhouse]$ ps -ef | grep click

8. 关闭开机自启

只是测试学习环境关闭，生产环境不要关闭 ！！！

[heather@hadoop102 clickhouse]$ sudo systemctl disable clickhouse-server 

9. 使用Client连接Server

[heather@hadoop102 clickhouse]$ clickhouse-client -m

-m :可以在命令窗口输入多行命令

1.3 数据类型

1. 整型

固定长度的整型，包括有符号(正负符号)整型或无符号整型。

整型范围（-2^n-1~2^n-1-1）：

Int8 - [-128 : 127] byte 8bit

Int16 - [-32768 : 32767] short 16bit

Int32 - [-2147483648 : 2147483647] int 32bit

Int64 - [-9223372036854775808 : 9223372036854775807] long 64bit

无符号整型范围（0~2ⁿ-1）：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

使用场景：个数、数量、也可以存储型id。

2. 浮点型

Float32 – float

Float64 – double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

3. 布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 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标识小数位

使用场景： 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用Decimal进行存储。

5. 字符串

1. String

   字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

2. FixedString(N)

   固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。

   与String相比，极少会使用FixedString，因为使用起来不是很方便。

使用场景：名称、文字描述、字符型编码。 固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限。

6. 枚举类型

包括 Enum8 和 Enum16 类型。Enum 保存 ‘string’= integer 的对应关系。

Enum8 用 ‘String’= Int8 对描述。

Enum16 用 ‘String’= Int16 对描述。

1. 用法演示（创建一个带有一个枚举 Enum8(‘hello’ = 1, ‘world’ = 2) 类型的列）

   CREATE TABLE t_enum
   (
       x Enum8('hello' = 1, 'world' = 2)
   )
   ENGINE = TinyLog;
   

2. 这个 x 列只能存储类型定义中列出的值：'hello’或’world’或1或2。

   hadoop102 :) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');
   hadoop102 :) INSERT INTO t_enum VALUES (1);
   hadoop102 :) select * from t_enum;
   ┌─x─────┐
   │ hello │
   │ world │
   │ hello │
   │ hello │
   └───────┘
   

3. 如果尝试保存任何其他值，ClickHouse 抛出异常

   hadoop102 :) insert into t_enum values('a')
       
   Exception on client:
   Code: 36. DB::Exception: Unknown element 'a' for enum: data for INSERT was parsed from query
   

4. 如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型

   hadoop102 :) SELECT CAST(x, 'Int8') FROM t_enum;
   ┌─CAST(x, 'Int8')─┐
   │               1 │
   │               2 │
   │               1 │
   │               1 │
   └─────────────────┘
   

   使用场景：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。但是实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题。所以谨慎使用。

7. 时间类型

目前ClickHouse 有三种时间类型

• Date接受年-月-日的字符串比如 ‘2019-12-16’
• Datetime接受年-月-日 时:分:秒的字符串比如 ‘2019-12-16 20:50:10’
• Datetime64接受年-月-日 时:分:秒.亚秒的字符串比如‘2019-12-16 20:50:10.66’

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

还有很多数据结构，可以参考官方文档：https://clickhouse.yandex/docs/zh/data_types/

8. 数组

Array(T)：由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。

1. 创建数组方式1，使用array函数

   array(T)	
   hadoop102 :) SELECT array(1, 2) AS x, toTypeName(x) ;
   

2. 创建数组方式2：使用方括号

   []
   hadoop102 :) SELECT [1, 2] AS x, toTypeName(x);
   

9. Nullable可为空

注意点

使用 Nullable 几乎总是对性能产生负面影响，在设计数据库时请记住这一点，

实际应用中，可用特殊数字、字符代替空值，如：-1、“null”等等。

1.4 表引擎

https://clickhouse.tech/docs/en/engines/table-engines/

表引擎是ClickHouse的一大特色。可以说， 表引擎决定了如何存储表的数据。包括：

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

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

1.4.1 TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。

如：

create table t_tinylog ( 
    id String, 
    name String
) engine=TinyLog;

1.4.2 Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

1.4.3 MergeTree(*)

ClickHouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（MergeTree）中的其他引擎，支持索引和分区，地位可以相当于innodb之于Mysql。 而且基于MergeTree，还衍生除了很多小弟，也是非常有特色的引擎。

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);
   

2. 插入数据

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

MergeTree其实还有很多参数(绝大多数用默认值即可)，但是案例中的三个参数是更加重要的，也涉及了关于MergeTree的很多概念，下面分别展开介绍 ！！！。

3. 执行查询

   hadoop102 :) select * from t_order_mt;
   Query id: e203ab7c-9639-4656-875e-bd63fa84e4db
   
   ┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
   │ 102 │ sku_002 │       600.00 │ 2020-06-01 12:00:00 │
   └─────┴─────────┴──────────────┴─────────────────────┘
   ┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
   │ 101 │ sku_001 │      1000.00 │ 2020-06-02 12:00:00 │
   │ 102 │ sku_002 │      2000.00 │ 2020-06-02 11:00:00 │
   │ 102 │ sku_002 │      2000.00 │ 2020-06-02 13:00:00 │
   │ 102 │ sku_002 │     12000.00 │ 2020-06-02 13:00:00 │
   │ 102 │ sku_004 │      2500.00 │ 2020-06-02 12:00:00 │
   └─────┴─────────┴─────────────┴─────────────────────┘
   
   

   

1.4.3.1 partition by 分区（可选）

1. 作用

和Hive一样，分区的目的主要是降低扫描的范围，优化查询速度。

2. 如果不写

只会使用一个分区。分区目录名：all。

3. 分区目录

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

Hive的分区也是分目录，Hive的目录在HDFS，ClickHouse的目录在本地磁盘。

4. 并行

分区后，面对设计跨分区的查询统计，ClickHouse就会以分区为单位进行并行处理。一个线程对应一个分区。

5. 数据写入与分区合并

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

optimize table xxxx final;

6. 示例演示

再次执行上面的插入操作 :

(101,'sku_001',1000.00,'2020-06-02 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-02 11:00:00'),
(102,'sku_004',2500.00,'2020-06-02 12:00:00'),
(102,'sku_002',2000.00,'2020-06-02 13:00:00'),
(102,'sku_002',12000.00,'2020-06-02 13:00:00'),
(102,'sku_002',600.00,'2020-06-01 12:00:00');

查看数据并没有纳入任何分区，需要手动optimize:

• （合并所有分区）

optimize table t_order_mt final;

• （合并指定分区）

optimize table t_order_mt partition '20200602' final;

1.4.3.2. primary key 主键（可选）

ClickHouse中的主键，和其他数据库不太一样，**它只提供了数据的一级索引，但是却不是唯一约束。**这就意味着是可以存在相同primary key的数据的。

主键的设定主要依据是查询语句中的where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity，避免了全表扫描。

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

稀疏索引：

稀疏索引的好处：用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

1.4.3.3. order by 排序（必选）

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。

order by是MergeTree中唯 一 一 个必填项，甚至比primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是order by字段的前缀字段。

比如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)

1.4.3.4 二级索引

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

二级索引就是对于一级索引的粒度的粒度。粒度更粗糙

1. 老版本使用二级索引前需要增加设置

   是否允许使用实验性的二级索引（v20.1.2.4开始，这个参数已被删除，默认开启）

   set allow_experimental_data_skipping_indices=1;
   

2. 创建测试表

   create table t_order_mt2(
       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, sku_id);
   

   其中GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。

3. 插入数据

   insert into  t_order_mt2 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');
   

4. 对比效果

   那么在使用下面语句进行测试，可以看出二级索引能够为非主键字段的查询发挥作用。

   [heather@hadoop102 lib]$ clickhouse-client  --send_logs_level=trace <<< 'select * from t_order_mt2  where total_amount > toDecimal32(900., 2)';
   

1.4.3.5 数据TTL

TTL即Time To Live，MergeTree提供了可以管理数据表或者列的生命周期的功能。

字段要求：不能是主键、必须是时间类型

更多的使用在实时的业务场景中。

1. 列级别TTL

1. 创建测试表

   create table t_order_mt3(
   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, sku_id);

   -- 订单总金额基于创建时间，10秒后过期

2. 插入数据==（注意：根据实际时间改变）==

   insert into  t_order_mt3 values
   (106,'sku_001',1000.00,'2021-08-16 10:50:50'),
   (107,'sku_002',2000.00,'2021-08-16 10:50:50'),
   (110,'sku_003',600.00,'2021-08-16 10:50:50');
   

3. 手动合并，查看效果 到期后，指定的字段数据归0

   optimize table t_order_mt3 final;
   select * from t_order_mt3;
   

2. 表级TTL

下面的这条语句是数据会在create_time 之后10秒丢失，满足条件的数据会被删除整条记录。

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。

能够使用的时间周期：

- SECOND

- MINUTE

- HOUR

- DAY

- WEEK

- MONTH

- QUARTER

- YEAR

3. 开发使用

TTL 规则的类型可以跟在每个 TTL 表达式之后。一旦满足表达式（到达当前时间），它会影响要执行的操作：

• DELETE - 删除过期数据行（默认操作）；
• TO DISK 'aaa'- 将过期数据移动到磁盘aaa；
• TO VOLUME 'bbb'- 将过期数据移动到磁盘bbb；
• GROUP BY - 聚合过期数据的行。

CREATE TABLE example_table
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

1.4.4 ReplaceingMergeTree

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

1. 去重时机

数据的去重只会在同一批次（新版本21版本）或合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

2. 去重范围

如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

所以ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

3. 案例演示

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()填入的参数为版本字段，重读数据保留版本字段值最大的。

   如果不填入字段，默认按照插入顺序保留最后一条。

   如果指定字段为时间戳类字段，那么保留时间戳最大的数据，和HBase一样。

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. 执行第一次查询

   hadoop102 :) select * from t_order_rmt;
   

   

4. 再次插入数据

   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');
   

5. 再次查询

   hadoop102 :) select * from t_order_rmt;
   

   

6. 手动合并

   OPTIMIZE TABLE t_order_rmt FINAL;
   

7. 在执行一次查询

   hadoop102 :) select * from t_order_rmt;
   

   

4. 总结

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

1.4.5 SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎SummingMergeTree

1. 聚合时机

只有在同一批次插入(新版本)或分片合并时才会进行聚合

2. 聚合范围

分区内

3. 案例演示

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 );

2. 插入数据

   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');
   

3. 执行第一次查询

   hadoop102 :) select * from t_order_smt;
   

   

4. 再次插入数据

   insert into  t_order_smt values　(101,'sku_001',2000.00,'2020-06-01 14:00:00');
   

5. 再次查询

   hadoop102 :) select * from t_order_smt;
   

   

6. 手动合并

   OPTIMIZE TABLE t_order_smt FINAL;
   

7. 再执行一次查询

   hadoop102 :) select * from t_order_smt;
   

   

4. 总结

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

5. 开发建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

6. 问题扩展

能不能直接执行以下SQL得到汇总值 ？

select total_amount from XXX where province_name=’’ and create_date=’xxx’

不行，可能会包含一些还没来得及聚合的临时明细

如果要是获取汇总值，还是需要使用sum进行聚合，这样效率会有一定的提高，但本身ClickHouse是列式存储的，效率提升有限，不会特别明显。

select sum(total_amount) from province_name=’’ and create_date=‘xxx’

1.4.6 集成外部引擎

https://clickhouse.tech/docs/zh/engines/table-engines/integrations/

1.5 SQL操作

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

1.5.1 Insert

基本与标准SQL（MySQL）基本一致

（1）标准

insert into [table_name] values(…),(….) 

（2）从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

1.5.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 =102;
   

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

1.5.3 查询操作

1.5.3.1 介绍

ClickHouse基本上与标准SQL 差别不大

• 支持子查询

• 支持CTE(Common Table Expression 公用表表达式 with 子句)

• 支持各种JOIN， 但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，ClickHouse也会视为两条新SQL

• 窗口函数(官方正在测试中…)

• 不支持自定义函数

• GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

  • with rollup ：上卷
  • with cube ： 多维分析
  • with total ： 总计

  维度是a,b
  rollup：上卷  
        group by a,b 
        group by a 
        group by 
  cube：多维分析
        group by  a,b 
        group by  a
        group by  b 
        group by 
  total：总计
        group by  a,b 
        group by 
                                      
  其实就是：      
  select xxxx  from xxx  group by a,b 
  union all
  select xxxx  from xxx  group by a
  union all
  select xxxx  from xxx  group by b 
  union all
  select xxxx  from xxx  
  

  https://clickhouse.tech/docs/en/sql-reference/statements/select/group-by/

  类似于Hive中的：

  https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

1.5.3.2 案例演示：

插入数据

hadoop102 :) alter table t_order_mt delete where 1=1;
insert into  t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'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 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

1. with rollup上卷：

从右至左去掉维度进行小计

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

2. with cube 多维度:

从右至左去掉维度进行小计，再从左至右去掉维度进行小计

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

3. with totals总计:

只计算合计

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

1.5.4 alter操作

同MySQL的修改字段基本一致

1.5.4.1 新增字段

alter table tableName  add column  newcolname  String after col1;

1.5.4.2 修改字段类型

alter table tableName modify column newcolname String;

1.5.4.3 删除字段

alter table tableName  drop column  newcolname;

1.5.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

更多支持格式参照：

https://clickhouse.tech/docs/en/interfaces/formats/

1.6 副本机制

副本的目的主要是保障数据的高可用性，即使一台ClickHouse节点宕机，那么也可以从其他服务器获得相同的数据。

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/

1.6.1 副本写入流程

1.6.2 配置步骤

1. 启动zookeeper集群

2. 在hadoop102的/etc/clickhouse-server/config.d目录下创建一个名为metrika.xml的配置文件,内容如下：

​ 注：也可以不创建外部文件，直接在config.xml中指定

<?xml version="1.0"?>
<yandex>
	<zookeeper-servers>
	    <node index="1">
	        <host>hadoop102</host>
	        <port>2181</port>
	    </node>
	    <node index="2">
	        <host>hadoop103</host>
	        <port>2181</port>
	    </node>
	    <node index="3">
	        <host>hadoop104</host>
	        <port>2181</port>
	    </node>
	</zookeeper-servers>
</yandex>

3. 同步到hadoop103和hadoop104上

sudo /home/heather/bin/xsync /etc/clickhouse-server/config.d/metrika.xml

4. 在 hadoop102的/etc/clickhouse-server/config.xml中增加

<zookeeper incl="zookeeper-servers" optional="true" />
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

5. 同步到hadoop103和hadoop104上

sudo /home/heather/bin/xsync /etc/clickhouse-server/config.xml

​ 分别在hadoop102和hadoop103上启动ClickHouse服务

​ 注意：因为修改了配置文件，如果以前启动了服务需要重启

[heather@hadoop102|3 ~]$ sudo clickhouse restart

​ 注意：我们演示副本操作只需要在hadoop102和hadoop103两台服务器即可，上面的操作，我们hadoop104可以你不用同步，我们这里为了保证集群中资源的一致性，做了同步。

6. 在hadoop102和hadoop103上分别建表

   副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表

   • hadoop102

     create table t_order_rep2 (
         id UInt32,
         sku_id String,
         total_amount Decimal(16,2),
         create_time  Datetime
      ) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102')
        partition by toYYYYMMDD(create_time)
        primary key (id)
        order by (id,sku_id);
     

   • hadoop103

     create table t_order_rep2 (
         id UInt32,
         sku_id String,
         total_amount Decimal(16,2),
         create_time  Datetime
      ) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_103')
        partition by toYYYYMMDD(create_time)
        primary key (id)
        order by (id,sku_id);
     

   • 参数解释

     ReplicatedMergeTree 中，

     第一个参数是分片的zk_path一般按照： /clickhouse/table/{shard}/{table_name} 的格式写，如果只有一个分片就写01即可。

     第二个参数是副本名称，相同的分片副本名称不能相同。

7. 在hadoop102上执行insert语句

   insert into t_order_rep2 values
   (101,'sku_001',1000.00,'2020-06-01 12:00:00'),
   (102,'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');
   

8. 在hadoop103上执行select，可以查询出结果，说明副本配置正确

   select * from t_order_rep2;
   

   

9. 在hadoop103上执行插入语句，再次进行测试hadoop102是否可查。

   insert into t_order_rep2 values (106,'sku_001',1000.00,'2020-06-01 12:00:00');
   

1.7 分片集群

副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。

要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过Distributed表引擎把数据拼接起来一同使用。

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

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

示例：3分片2副本共6个节点

1.7.1 集群写入流程

1.7.2 集群读取流程

1.7.3 分片2副本共6个节点集群配置

配置的位置还是在之前的/etc/clickhouse-server/config.d/metrika.xml，内容如下

注：也可以不创建外部文件，直接在config.xml的<remote_servers>中指定

因为一共有3台机器，所以设置如下：

<yandex>
	<remote_servers>
		<gmall_cluster> <!-- 集群名称--> 
			<shard>  <!--集群的第一个分片-->
				<internal_replication>true</internal_replication>
				<!--该分片的第一个副本-->
			    <replica>    
			        <host>hadoop102</host>
			        <port>9000</port>
			     </replica>
			     <!--该分片的第二个副本-->
			     <replica> 
			        <host>hadoop103</host>
			        <port>9000</port>
			     </replica>
			</shard>

			  <shard>  <!--集群的第二个分片-->
			     <internal_replication>true</internal_replication>
			     <replica>    <!--该分片的第一个副本-->
			        <host>hadoop103</host>
			     	<port>9000</port>
			     </replica>
			     <replica>    <!--该分片的第二个副本-->
			        <host>hadoop104</host>
			        <port>9000</port>
			     </replica>
			  </shard>

			  <shard>  <!--集群的第三个分片-->
			     <internal_replication>true</internal_replication>
			     <replica>     <!--该分片的第一个副本-->
			        <host>hadoop104</host>
			        <port>9000</port>
			     </replica>
			     <replica>    <!--该分片的第二个副本-->
			        <host>hadoop102</host>
			        <port>9000</port>
			     </replica>
			  </shard>
		</gmall_cluster>
	</remote_servers>
</yandex>

1.7.4 配置三节点版本集群及副本

1.7.4.1 集群及副本规划

2个分片，只有第一个分片有副本

1.7.4.2 配置步骤

1. 创建metrika-shard.xml文件

在hadoop102的/etc/clickhouse-server/config.d目录下创建metrika-shard.xml文件

注：也可以不创建外部文件，直接在config.xml的<remote_servers>中指定

<?xml version="1.0"?>
<yandex>
    <!-- <clickhouse_remote_servers> 老版本 20版本--> 
	<remote_servers>    <!-- 新版本 21版本--> 
		<gmall_cluster> <!-- 集群名称--> 
			<shard>         <!--集群的第一个分片-->
				<internal_replication>true</internal_replication>
			    <replica>    <!--该分片的第一个副本-->
			        <host>hadoop102</host>
			        <port>9000</port>
			    </replica>
			    <replica>    <!--该分片的第二个副本-->
			        <host>hadoop103</host>
			        <port>9000</port>
			    </replica>
			</shard>

			<shard>  <!--集群的第二个分片-->
			    <internal_replication>true</internal_replication>
			    <replica>    <!--该分片的第一个副本-->
			        <host>hadoop104</host>
			        <port>9000</port>
			    </replica>
			</shard>
		</gmall_cluster>
	</remote_servers>

	<zookeeper-servers>
		<node index="1">
			<host>hadoop102</host>
		 	<port>2181</port>
		</node>
		<node index="2">
		 	<host>hadoop103</host>
		  	<port>2181</port>
		</node>
		<node index="3">
		  	<host>hadoop104</host>
		  	<port>2181</port>
		</node>
	</zookeeper-servers>

	<macros>
		<shard>01</shard>   <!--不同机器放的分片数不一样-->
		<replica>rep_1_1</replica>  <!--不同机器放的副本数不一样-->
	</macros>
</yandex>

2. 同步metrika-shard.xml文件

将hadoop102的metrika-shard.xml同步到103和104

[heather@hadoop102 ~]$ sudo xsync /etc/clickhouse-server/config.d/metrika-shard.xml

3. 修改metrika-shard.xml宏配置

1. hadoop103

   [heather@hadoop103 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml
   
   <macros>
           <shard>01</shard>   <!--不同机器放的分片数不一样-->
           <replica>rep_1_2</replica>  <!--不同机器放的副本数不一样-->
   </macros>
   

2. hadoop104

   [heather@hadoop103 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml
   
   <macros>
           <shard>02</shard>   <!--不同机器放的分片数不一样-->
           <replica>rep_2_1</replica>  <!--不同机器放的副本数不一样-->
   </macros>
   

4. 修改config.xml文件

在hadoop102上修改/etc/clickhouse-server/config.xml文件

5. 同步config.xml文件

同步/etc/clickhouse-server/config.xml文件到103和104

[heather@hadoop102 ~]$ sudo xsync /etc/clickhouse-server/config.xml

6. 重启服务

重启三台服务器上的ClickHouse服务

[heather@hadoop102 clickhouse-server]$ sudo clickhouse restart
[heather@hadoop102 clickhouse-server]$ ps -ef |grep click

7. 案例演示

1. 在hadoop102上执行建表语句

   • 会自动同步到hadoop103和hadoop104上
   • 集群名字要和配置文件中的一致
   • 分片和副本名称从配置文件的宏定义中获取

   create table st_order_mt on cluster gmall_cluster(
   id UInt32,
   sku_id String,
   total_amount Decimal(16,2),
   create_time Datetime
   ) engine = ReplicatedMergeTree(‘/clickhouse/tables/{shard}/st_order_mt’,‘{replica}’)
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id,sku_id);

2. 可以到hadoop103和hadoop104上查看表是否创建成功

   

8. 创建Distribute 分布式表

在hadoop102上创建Distribute 分布式表

create table st_order_mt_all2 on cluster gmall_cluster(

  id UInt32,
  												
  sku_id String,
  												
  total_amount Decimal(16,2),
  												
  create_time Datetime

)engine = Distributed(gmall_cluster,default, st_order_mt,hiveHash(sku_id));

参数含义：

​ Distributed（集群名称，库名，本地表名，分片键）

​ 分片键必须是整型数字，所以用hiveHash函数转换，也可以rand()

9. 插入测试数据

在hadoop102上插入测试数据：数据插入到分布式表中

insert into st_order_mt_all2 values
(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(202,'sku_002',2000.00,'2020-06-01 12:00:00'),
(203,'sku_004',2500.00,'2020-06-01 12:00:00'),
(204,'sku_002',2000.00,'2020-06-01 12:00:00'),
(205,'sku_003',600.00,'2020-06-02 12:00:00');

10. 查询数据

1. 分布式表

   SELECT *  FROM st_order_mt_all2;
   

2. 本地表

   select * from st_order_mt;
   

3. 观察数据的分布情况

   

1.7.5 项目为了节省资源，就使用单节点，不用集群

不需要求改文件引用，因为已经使用集群建表了，如果改为引用metrika-shard.xml的话，启动会报错。我们以后用的时候只启动102即可。

二、ClickHouse高级

1. Explain 20.6版本之后才有的
2. 优化：建表时、RBO优化规则、使用过程中做的优化（单表查询、多表关联）、建表参数优化：cpu、内存、建议值
3. 特性：物化视图、20.8之后的物化Mysql

2.1 Explain查看执行计划

在clickhouse 20.6版本之前要查看SQL语句的执行计划需要设置日志级别为trace才能可以看到，并且只能真正执行sql，在执行日志里面查看。在20.6版本引入了原生的执行计划的语法。在20.6.3版本成为正式版本的功能。

本文档基于目前较新稳定版21.7.3.14。

2.1.1 基本语法

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

1. PLAN ：用于查看执行计划，默认值。
   • header ： 打印计划中各个步骤的head说明，默认关闭，默认值0。
   • description ：打印计划中各个步骤的描述，默认开启，默认值1。
   • actions ：打印计划中各个步骤的详细信息，默认关闭，默认值0。
2. AST ：用户查看语法树。
3. SYNTAX ：用于优化语法。
4. PIPELINE ：用于查看PIPELINE计划。
   • header ：打印计划中各个步骤的head说明，默认关闭，默认值0。
   • graph ：用DOT图形语言描述管道图，默认关闭，需要查看相关的图形时需要配合graphviz查看。
   • actions ：如果开启了graph，紧凑打印，默认开启。

注意：PLAN 和 PIPELINE还可以进行额外的显示设置，如上参数所示 ！

2.1.2 案例实操

2.1.2.0 MySql中的EXPLAIN

Explain可以模拟优化器执行SQL查询语句，从而知道MySql是如何处理你的SQL语句的，分析查询语句或者是表结构的性能瓶颈。

1. 作用

• 表的读取顺序 [id字段](# - id)
• 数据读取操作的操作类型[select_type字段](# - select_type)
• 哪些索引可以使用
• 那些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

2. 如何使用

1. Explain + SQL语句

2. 执行计划包含的信息：

3. 各个字段的信息

- id

select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

• id相同，执行顺序由上至下。

  

• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

  

• id相同不同，遵循以上规律。

  

- select_type

1. 一共有6种类型

id	1	2	3	4	5	6
select_type	SIMPLE	PRIMARY	SUBQUERY	DERIVED	UNION	UNIION RESULT

2. 6种类型的含义

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

• **SIMPLE：**简单的select查询，查询中不包含子查询或者UNION。

• **PRIMARY：**查询中若包含任何复杂的子部分，最外层查询则被标记为此。

• **SUBQUERY：**在select或where列表中包含了子查询。

• DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），并递归执行这些子查询，把结果放在临时表中。

• UNION：若第二个select出现在UNION之后，则被标记为UNION；

  ​ 若UNION包含在from子句的子查询中，外层select将被标记为：DERIVED。

• UNIION RESULT：从UNION表获取结果的select。

- table

- type

- possible_keys

- key

- key_len

- ref

- rows

- Extra

2.1.2.1 新版本使用 EXPLAIN

可以再安装一个20.6以上版本，或者直接在官网的在线demo，选择高版本进行测试。

官网在线测试链接：https://play.clickhouse.tech/?file=welcome

1. 查看PLAN

1. 简单查询

   explain plan select arrayJoin([1,2,3,null,null]);
   

   ┌─explain───────────────────────────────────────────────────────────────────┐
   │ Expression ((Projection + Before ORDER BY)) │
   │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
   │ ReadFromStorage (SystemOne) │
   └─────────────────────────────────────────────────────────────────────────

2. 复杂SQL的执行计划

   explain select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,table order by database,cnt desc limit 2 by database;
   

   ┌─explain────────────────────────────────────────────────────────────────────
   │ Expression (Projection) │
   │ LimitBy │
   │ Expression (Before LIMIT BY) │
   │ MergingSorted (Merge sorted streams for ORDER BY) │
   │ MergeSorting (Merge sorted blocks for ORDER BY) │
   │ PartialSorting (Sort each block for ORDER BY) │
   │ Expression (Before ORDER BY) │
   │ Aggregating │
   │ Expression (Before GROUP BY) │
   │ Filter (WHERE) │
   │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
   │ ReadFromStorage (SystemParts) │
   └─────────────────────────────────────────────────────────────────────────

3. 打开全部的参数的执行计划

   EXPLAIN header=1, actions=1,description=1 SELECT number from system.numbers limit 10;
   

   ┌─explain───────────────────────────────────────────────────────────────────┐
   │ Expression ((Projection + Before ORDER BY)) │
   │ Header: number UInt64 │
   │ Actions: INPUT :: 0 -> number UInt64 : 0 │
   │ Positions: 0 │
   │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
   │ Header: number UInt64 │
   │ Limit (preliminary LIMIT) │
   │ Header: number UInt64 │
   │ Limit 10 │
   │ Offset 0 │
   │ ReadFromStorage (SystemNumbers) │
   │ Header: number UInt64 │
   └─────────────────────────────────────────────────────────────────────────

2. AST语法树

EXPLAIN AST SELECT number from system.numbers limit 10;

┌─explain──────────────────────────────────── ┐
│ SelectWithUnionQuery (children 1) │
│ ExpressionList (children 1) │
│ SelectQuery (children 3) │
│ ExpressionList (children 1) │
│ Identifier number │
│ TablesInSelectQuery (children 1) │
│ TablesInSelectQueryElement (children 1) │
│ TableExpression (children 1) │
│ Identifier system.numbers │
│ Literal UInt64_10 │
└────────────────────────────────────────── ┘

3. SYNTAX语法优化

//先做一次查询

SELECT number = 1 ? ‘hello’ : (number = 2 ? ‘world’ : ‘heather’) FROM numbers(10);

//查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? ‘hello’ : (number = 2 ? ‘world’ : ‘heather’) FROM numbers(10);

//开启三元运算符优化

SET optimize_if_chain_to_multiif = 1;

//再次查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? ‘hello’ : (number = 2 ? ‘world’ : ‘heather’) FROM numbers(10);

//返回优化后的语句

SELECT multiIf(number = 1, ‘hello’, number = 2, ‘world’, ‘xyz’)

FROM numbers(10)

4. 查看PIPELINE

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;

//打开其他参数

EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;

2.1.2.2 老版本使用 EXPLAIN

clickhouse-client -h 主机名 --send_logs_level=trace <<< "sql" > /dev/null

其中，send_logs_level参数指定日志等级为trace，<<<将SQL语句重定向至clickhouse-client进行查询，> /dev/null将查询结果重定向到空设备吞掉，以便观察日志。

注意：

1、通过将ClickHouse的服务日志，设置到DEBUG或者TRACE级别，才可以变相实现EXPLAIN查询的作用。

2、需要真正的执行SQL查询，CH才能打印计划日志，所以如果表的数据量很大，最好借助LIMIT子句，减小查询返回的数据量。

2.2 建表优化

2.2.1 数据类型

2.2.1.1 时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串，全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_time)

partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错

primary key (id)

order by (id, sku_id);

正确的示例

create table t_type2(

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);

2.2.1.2 空值存储类型

MySql中的空值：null

Hive中的空值：\N

ClickHouse中的空值：Nullable

官方已经指出Nullable类型几乎总是会拖累性能：

①：因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记；

②：并且Nullable列无法被索引。

因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值开发经验：（例如用-1表示没有商品ID）。

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog;
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x + y FROM t_null;

查看存储的文件：（没有权限就用root用户）

2.2.2 分区和索引

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

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

比如官方案例的hits_v1表：

……
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
……

visits_v1表：

……
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
……

2.2.3 表参数

1. Index_granularity是用来控制索引粒度的，默认是8192，如非必须不建议调整。

2. 如果表中不是必须保留全量历史数据，建议指定TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过alter table语句随时修改。[参考ClickHouse初级文档1.4.3.5 数据TTL](# 1.4.3.5 数据TTL)

2.2.4 写入和删除优化

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

写入过快报错，报错信息：

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304). Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 bytes), maximum: 9.31 GiB

处理方式：

“ Too many parts 处理 ” ：使用WAL预写日志，提高写入性能。默认开启。
in_memory_parts_enable_wal 默认为 true
在服务器内存充裕的情况下增加内存配额，一般通过max_memory_usage来实现
在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过max_bytes_before_external_group_by、max_bytes_before_external_sort参数来实现。

2.2.5 常见配置

配置项主要在config.xml或users.xml中， 基本上都在users.xml里==【etc/clickhouse-server】==

1. config.xml的配置项 : 服务端的配置

   https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/

2. users.xml的配置项

   https://clickhouse.tech/docs/en/operations/settings/settings/

如下所示的所有的配置都是建立在8核16线程的机器上为例进行演示

2.2.5.1 CPU资源

配置	描述
background_pool_size	后台线程池的大小，merge线程就是在该线程池中执行，该线程池不仅仅是给merge线程用的，默认值16，允许的前提下建议改成cpu个数的2倍（线程数）。
background_schedule_pool_size	执行后台任务（复制表、Kafka流、DNS缓存更新）的线程数。默认128。
background_distributed_schedule_pool_size	设置为分布式发送执行后台任务的线程数，默认16，建议改成cpu个数的2倍（线程数）。
max_concurrent_queries	最大并发处理的请求数(包含select,insert等)，默认值100，推荐150(不够再加)~300。
max_threads	设置单个查询所能使用的最大cpu个数，默认是cpu核数

2.2.5.2 内存资源

配置	描述
max_memory_usage	此参数在users.xml 中,表示单次Query占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。 保留一点给OS，比如128G内存的机器，设置为100GB。
max_bytes_before_external_group_by	一般按照max_memory_usage的一半设置内存，当group使用内存超过阈值后会刷新到磁盘进行。 因为clickhouse聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议50GB。
max_bytes_before_external_sort	当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
max_table_size_to_drop	此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除50GB以上的分区表会失败。建议修改为0，这样不管多大的分区表都可以删除，不进行校验。

2.2.5.3 IO / 存储

ClickHouse不支持设置多数据目录，为了提升数据io性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景SSD会比普通机械硬盘快2-3倍。

2.3 ClickHouse 语法优化规则

ClickHouse 的 SQL 优化规则是基于RBO(Rule Based Optimization)，下面是一些优化规则。

此类优化规则Hive中也有，是基于CBO的(Cost Based Optimization)。

2.3.1 准备测试用表

1. 上传官方的数据集

其实也可以不用上传数据，直接在官网进行操作亦可。

https://play.clickhouse.tech/?file=welcome

将visits_v1.tar和hits_v1.tar上传到虚拟机，解压到clickhouse数据路径下

// 解压到clickhouse数据路径

sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse

sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse

//修改所属用户

sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets

sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets

2. 重启clickhouse-server

sudo clickhouse restart

3. 执行查询

clickhouse-client --query “SELECT COUNT(*) FROM datasets.hits_v1”

clickhouse-client --query “SELECT COUNT(*) FROM datasets.visits_v1”

注意：官方的tar包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。
hits_v1表有130多个字段，880多万条数据
visits_v1表有180多个字段，160多万条数据

2.3.2 COUNT() 优化

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

explain plan select count() from datasets.hits_v1;

Union

Expression (Projection)

​ Expression (Before ORDER BY and SELECT)

​ MergingAggregated

​ ReadNothing (Optimized trivial count)

注意 Optimized trivial count ，这是对 count 的优化。

如果 count 具体的列字段，则不会使用此项优化：

explain plan 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)

2.3.3 消除子查询重复字段

下面语句子查询中有两个重复的 UserID 字段，会被去重 ：

所以在定义SQL时如果业务中需要查询重复字段，需要深刻考虑SQL的写法。

2.3.4 谓词下推

当group by有having子句，但是没有with cube、with rollup 或者with totals修饰的时候，having过滤会下推到where提前过滤。

1. HAVING UserID变成了WHERE UserID，在group by之前过滤：

2. 子查询也支持谓词下推：

3. 复杂的子查询

2.3.5 聚合计算外推

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

EXPLAIN SYNTAX

SELECT sum(UserID * 2)

FROM visits_v1;

//返回优化后的语句

SELECT sum(UserID) * 2

FROM visits_v1;

2.3.6 聚合函数消除

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

2.3.7 删除重复的 order by key

例如下面的语句，重复的聚合键 UserID、VisitID 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
ORDER BY
    UserID ASC,
    UserID ASC,
    VisitID ASC,
VisitID ASC;

//返回优化后的语句：
select
	……
FROM visits_v1
ORDER BY 
    UserID ASC,
VisitID ASC;

2.3.8 删除重复的 limit by key

例如下面的语句，重复声明的 VisitID 字段会被去重：

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BY
    VisitID,
    VisitID
LIMIT 10;

//返回优化后的语句：
select
	……
FROM visits_v1
LIMIT 3 BY VisitID
LIMIT 10;

2.3.9 删除重复的 USING Key

例如下面的语句，重复的关联键 UserID 字段会被去重：

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

//返回优化后的语句：
SELECT 
    UserID,
    UserID,
    VisitID,
    URL,
    b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN visits_v1 AS b USING (UserID);

2.3.10 标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换，

例如下面语句中的 total_disk_usage 字段：

EXPLAIN SYNTAX
WITH 
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

//返回优化后的语句：
WITH CAST(0, \'UInt64\') AS total_disk_usage
SELECT 
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

2.3.11 三元运算优化

如果开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数，在SQL语句中定义仅仅对本次查询有效。

例如：

EXPLAIN SYNTAX 
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'heather') 
FROM numbers(10) 
settings optimize_if_chain_to_multiif = 1;

//返回优化后的语句：
SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'heather\')
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1

2.4 查询优化

2.4.1 单表查询

[ClickHouse的 缺点](# 7. 缺点)，通过前面接触到的对ClickHouse的认识来说，此框架在多表关联时存在短板，所以在实际的开发中，我们更多的使用ClickHouse的单表查询功能，宽表。

1. Prewhere替代where

Prewhere和where语句的作用相同，用来过滤数据。不同之处在于prewhere只支持 MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select 声明的列字段来补全其余属性。

当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作，在某些场合下，prewhere语句比where语句处理的数据量更少性能更高。

底层自动优化方案：having ——> where ——> prewhere

关闭where自动转prewhere(默认情况下， where条件会自动优化成prewhere)

set optimize_move_to_prewhere=0; 关闭prewhere==【不推荐关闭】==

# 使用where
explain syntax 
select WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID, 
    CounterClass, 
    OS, 
    UserAgent, 
    URL, 
    Referer, 
    URLDomain, 
    RefererDomain, 
    Refresh, 
    IsRobot, 
    RefererCategories, 
    URLCategories, 
    URLRegions, 
    RefererRegions, 
    ResolutionWidth, 
    ResolutionHeight, 
    ResolutionDepth, 
    FlashMajor, 
    FlashMinor, 
    FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';

默认情况，我们肯定不会关闭where自动优化成prewhere，但是某些场景即使开启优化，也不会自动转换成prewhere，需要手动指定prewhere：

• 使用常量表达式
• 使用默认值为alias类型的字段
• 包含了arrayJOIN，globalIn，globalNotIn或者indexHint的查询
• select查询的列字段和where的谓词相同
• 使用了主键字段

使用总结：实际开发中，手动指定prewhere更合适 ！！！

2. 数据采样

通过采样运算可极大提升数据分析的性能。

Spark中的采样方法：sample()方法；

Hive中的采样方法：分桶

ClickHouse中的采样：SAMPLE

SELECT Title,count(*) AS PageViews 
FROM hits_v1
SAMPLE 0.1         #代表采样10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000;

采样修饰符只有在MergeTree engine表中才有效，且在创建表时需要指定采样策略。

3. 列裁剪与分区裁剪

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

反例：【推荐在官网测试，数据量太大，浪费时间】
select * from datasets.hits_v1;
正例：
select WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID
from datasets.hits_v1;

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

分区字段：EventDate
select WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID
from datasets.hits_v1
where EventDate='2014-03-23';

4. orderby 结合 where、limit

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

#正例：
SELECT UserID,Age
FROM hits_v1        
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000;
== 结果：
Elapsed: 0.012 sec. Processed 8.19 thousand rows, 106.50 KB (684 thousand rows/s., 8.90 MB/s.) 

#反例：
SELECT UserID,Age
FROM hits_v1        
ORDER BY Age DESC;
== 结果：
Elapsed: 0.553 sec. Processed 8.87 million rows, 79.87 MB (16.04 million rows/s., 144.35 MB/s.) 

5. 避免构建虚拟列

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

反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
== 结果：
Elapsed: 0.282 sec. Processed 8.87 million rows, 17.75 MB (31.50 million rows/s., 63.00 MB/s.)

正例：拿到Income和Age后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;
== 结果：
Elapsed: 0.147 sec. Processed 8.87 million rows, 17.75 MB (60.46 million rows/s., 120.92 MB/s.)

6. 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

7. 使用物化视图

物化视图 区别于 视图，这是两个级别的概念。视图仅仅保存SQL的执行逻辑，物化视图不仅仅保存SQL执行逻辑，同时还把SQL执行的结果保存下来。

[参考2.6 物化视图章节](# 2.6 物化视图)

8. 其他注意事项

1. 查询熔断

为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

2. 关闭虚拟内存

物理内存和虚拟内存的数据交换（IO操作），会导致查询变慢，资源允许的情况下关闭虚拟内存。

3. 配置join_use_nulls

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

4. 批量写入时先排序

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

5. 关注CPU

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

• 加资源
• 优化SQL语句

2.4.2 多表关联

1. 准备表和数据

ClickHouse官方提供的两张表hits_v1和visits_v1由于数据量太大，我们只使用一部分数据。

#创建小表
CREATE TABLE visits_v2 
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

CREATE TABLE hits_v2 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;(条件不成立，只会创建表结果，不会导入数据)

2. 用 IN 代替 JOIN

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

#正例：使用in，耗费时长：4秒
insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from visits_v1);
== Elapsed: 4.278 sec. Processed 5.41 million rows, 5.14 GB (1.26 million rows/s., 1.20 GB/s.) 

#反例：使用join，耗费时长：43秒
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a.CounterID=b.CounterID;
== Elapsed: 43.285 sec. Processed 10.55 million rows, 8.47 GB (243.75 thousand rows/s., 195.61 MB/s.)

3. 大小表JOIN

多表join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse中无论是Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

这一点和Hive正好相反，Hive在join时要满足小表在左的原则，新版本中已经通过OBC进行了调优，没有了要求。

1. 小表在右

   select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID;
   == 耗时40秒
   

2. 大表在右

   select a.* from visits_v2 b left join hits_v1 a on a. CounterID=b. CounterID;
   == 机器直接卡死
   

4. 谓词下推（版本差异）

谓词：过滤的字段。

ClickHouse在join查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能差异）

#反例
insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID
where a.EventDate = '2014-03-17';

#正例
insert into hits_v2
select a.* from (
    select * from 
    hits_v1 
    where EventDate = '2014-03-17'
) a left join visits_v2 b on a.CounterID=b.CounterID;

5. 分布式表使用GLOBAL

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

6. 使用字典表

将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典表会常驻内存。

7. 提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。

2.5 数据一致性（重点）

查询CK手册发现，即便对数据一致性支持最好的Mergetree，也只是保证最终一致性：

https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/replacingmergetree/

ReplacingMergeTree¶

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂数据不一致的情况。

在某些对一致性非常敏感的场景，通常有以下几种解决方案。

2.5.1 准备测试表和数据

1. 创建表

   CREATE TABLE test_a(
     user_id UInt64,
     score String,
     deleted UInt8 DEFAULT 0,
     create_time DateTime DEFAULT toDateTime(0)
   )ENGINE= ReplacingMergeTree(create_time)
   ORDER BY user_id;
   

   字段含义：

   user_id 是数据去重更新的标识;

   create_time 是版本号字段，每组数据中 create_time 最大的一行表示最新的数据;

   deleted 是自定的一个标记位，比如 0 代表未删除，1 代表删除数据。

2. 向表中插入1000万条数据

   INSERT INTO TABLE test_a(user_id,score)
   WITH(
     SELECT ['A','B','C','D','E','F','G']
   )AS dict
   SELECT number AS user_id, dict[number%7+1] FROM numbers(10000000);
   

3. 修改前 50万 行数据，修改内容包括 name 字段和 create_time 版本号字段

   INSERT INTO TABLE test_a(user_id,score,create_time)
   WITH(
     SELECT ['AA','BB','CC','DD','EE','FF','GG']
   )AS dict
   SELECT number AS user_id, dict[number%7+1], now() AS create_time FROM numbers(500000);
   

4. 统计总数

   SELECT COUNT() FROM test_a;
   10500000
   

   还未触发分区合并，所以还未去重。

2.5.2 手动 OPTIMIZE 去重

在写入数据后，立刻执行OPTIMIZE强制触发新写入分区的合并动作。但是OPTIMIZE执行时服务器不对外提供任何服务，此时，服务器对外停止运行。【实际开发时不推荐使用】

OPTIMIZE TABLE test_a FINAL;

语法：OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID ‘partition_id’] [FINAL] [DEDUPLICATE [BY expression]]

默认按照order by字段去重。

2.5.3 通过 Group by 去重

group by + 标记字段 ——> 去重 ！！！此种方式牺牲效率换取数据的唯一性 。

1. 执行去重的查询

   SELECT
     user_id ,
     argMax(score, create_time) AS score, 
     argMax(deleted, create_time) AS deleted,
     max(create_time) AS ctime 
   FROM test_a 
   GROUP BY user_id
   HAVING deleted = 0;
   

   函数说明：

   • argMax(field1,field2)：按照field2的最大值获取field1的值

   当我们更新数据时，会写入一行新的数据，例如上面的语句中，通过查询最大的create_time得到修改后的score字段值。

2. 创建视图，方便测试

   CREATE VIEW view_test_a AS
   SELECT
     user_id ,
     argMax(score, create_time) AS score, 
     argMax(deleted, create_time) AS deleted,
     max(create_time) AS ctime 
   FROM test_a 
   GROUP BY user_id
   HAVING deleted = 0;
   

3. 插入重复数据，再次查询

   #再次插入一条数据
   INSERT INTO TABLE test_a(user_id,score,create_time) VALUES(0,'AAAA',now())
   
   #再次查询
   SELECT *
   FROM view_test_a
   WHERE user_id = 0;
   

4. 删除数据测试

   #再次插入一条标记为删除的数据
   INSERT INTO TABLE test_a(user_id,score,deleted,create_time) VALUES(0,'AAAA',1,now());
   
   #再次查询，刚才那条数据看不到了
   SELECT *
   FROM view_test_a
   WHERE user_id = 0;
   

这行数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合表级别的 TTL 最终将物理数据删除。

2.5.4 通过 FINAL 查询

在查询语句后增加FINAL修饰符，这样在查询的过程中将会执行Merge的特殊逻辑（例如数据去重，预聚合等）。

但是这种方法在早期版本基本没有人使用，因为在增加 FINAL之后，我们的查询将会变成一个单线程的执行过程，查询速度非常慢。

在v20.5.2.7-stable版本中，FINAL查询支持多线程执行，并且可以通过max_final_threads 参数控制单个查询的线程数。但是目前读取part部分的动作依然是串行的。

FINAL查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最终的查询时间，所以还要结合实际场景取舍。

参考链接：https://github.com/ClickHouse/ClickHouse/pull/10463

使用hits_v1表进行测试：

分别安装了20.4.5.36 和 21.7.3.14 两个版本的ClickHouse进行对比。

2.5.4.1 老版本测试

（1）普通查询语句

select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100;

（2）FINAL查询

select * from visits_v1 FINAL WHERE StartDate = '2014-03-17' limit 100;

先前的并行查询变成了单线程。

2.5.4.2 新版本测试

（1）普通语句查询

select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100 settings max_threads = 2;

查看执行计划：

explain pipeline select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100 settings max_threads = 2;

(Expression)                   
ExpressionTransform × 2        
  (SettingQuotaAndLimits)      
    (Limit)                    
    Limit 2 → 2                
      (ReadFromMergeTree)      
      MergeTreeThread × 2 0 → 1

明显将由2个线程并行读取 part 查询。

（2）FINAL查询

select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100  settings max_final_threads = 2;

查询速度没有普通的查询快，但是相比之前已经有了一些提升,查看 FINAL 查询的执行计划：

explain pipeline select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100  settings max_final_threads = 2;

(Expression)                          
ExpressionTransform × 2               
  (SettingQuotaAndLimits)             
    (Limit)                           
    Limit 2 → 2                       
      (ReadFromMergeTree)             
      ExpressionTransform × 2         
        CollapsingSortedTransform × 2 
          Copy 1 → 2                  
            AddingSelector            
              ExpressionTransform     
                MergeTree 0 → 1       

从CollapsingSortedTransform这一步开始已经是多线程执行，但是读取 part 部分的动作还是串行。

2.6 物化视图

ClickHouse的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。区别于视图。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select，就是create一个table as select的写法。

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表join之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）

2.6.1 概述

1. 物化视图与普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

2. 优缺点

优点：

查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：

它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

3. 基本语法

也是create语法，会创建一个隐藏的目标表来保存视图数据。也可以TO 表名，保存到一张显式的表。没有加TO表名，表名默认就是 .inner.物化视图名。

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

1. 创建物化视图的限制
   • 必须指定物化视图的engine 用于数据存储
   • TO [db].[table]语法的时候，不得使用POPULATE。
   • 查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
   • 物化视图的 alter 操作有些限制，操作起来不大方便。
   • 若物化视图的定义使用了TO [db.]name 子语句，则可以将目标表的视图 卸载 DETACH 再装载 ATTACH
2. 物化视图的数据更新
   • 物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
   • POPULATE 关键字决定了物化视图的更新策略：
     • 若有POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
     • 若无POPULATE 则物化视图在创建之后没有数据，只会创建表。只有在向源表中插入新数据或向视图表中导入历史数据才会有真实数据。
     • clickhouse 官方并不推荐使用POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。
   • 物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留
   • 物化视图是一种特殊的数据表，可以用show tables 查看
   • 物化视图数据的删除：
   • 物化视图的删除：

2.6.2 案例实操

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。

1. 准备测试用表和数据

1. 建表

   #建表语句 
   CREATE TABLE hits_test
   (
       EventDate Date, 
       CounterID UInt32, 
       UserID UInt64, 
       URL String, 
       Income UInt8
   )
   ENGINE = MergeTree()
   PARTITION BY toYYYYMM(EventDate)
   ORDER BY (CounterID, EventDate, intHash32(UserID))
   SAMPLE BY intHash32(UserID)
   SETTINGS index_granularity = 8192;
   

2. 导入数据

   INSERT INTO hits_test 
       SELECT 
       EventDate,
       CounterID,
       UserID,
       URL,
       Income 
   FROM hits_v1 
   limit 10000;
   

2. 创建物化视图

#建表语句
CREATE MATERIALIZED VIEW hits_mv
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID))
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= ‘2014-03-20’

#设置更新点,该时间点之前的数据可以另外通过 #insert into select …… 的方式进行插入
GROUP BY UserID,EventDate;

##或者可以用下列语法，表A可以是一张mergetree表
CREATE MATERIALIZED VIEW 物化视图名 TO 表A
AS SELECT FROM 表B;

#不建议添加populate关键字进行全量更新。

3. 导入增量数据

#导入增量数据
INSERT INTO `hits_test` 
SELECT 
    EventDate,
    CounterID,
    UserID,
    URL,
    Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;

#查询物化视图
SELECT * FROM hits_mv;

4. 导入历史数据

#导入增量数据
INSERT INTO `hits_mv`
SELECT
    UserID,
    EventDate,
    count(URL) as ClickCount,
    sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate;

#查询物化视图
SELECT * FROM hits_mv;

不同批次导入到物化视图中的数据不会立即合并，数据存在重复问题。数据合并参考[2.5 数据一致性（重点）](# 2.5 数据一致性（重点）)。

2.7 MaterializeMySQL引擎

2.7.1 概述

MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件，我们需要用到类似 Canal 、Maxwell、FlinkCDC这样的第三方中间件，无疑增加了系统的复杂度。

ClickHouse 20.8.2.3版本新增加了 MaterializeMySQL 的 database 引擎，该 database 能映射到 MySQL 中的某个 database，并自动在 ClickHouse 中创建对应的 ReplacingMergeTree。ClickHouse服务作为MySQL副本，读取Binlog并执行DDL和DML请求，实现了基于MySQL Binlog机制的业务数据库实时同步功能。

2.7.1.1 特点

1. MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。

2. MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。
   其中， _version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1。

   目前 MaterializeMySQL 支持如下几种 binlog 事件:

• MYSQL_WRITE_ROWS_EVENT: _sign = 1，_version ++
• MYSQL_DELETE_ROWS_EVENT: _sign = -1，_version ++
• MYSQL_UPDATE_ROWS_EVENT: 新数据 _sign = 1
• MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE等。

2.7.1.2 使用细则

1. DDL查询
   MySQL DDL查询被转换成相应的ClickHouse DDL查询（ALTER, CREATE, DROP, RENAME）。如果ClickHouse不能解析某些DDL查询，该查询将被忽略。
2. 数据复制
   MaterializeMySQL不支持直接插入、删除和更新查询，而是将DDL语句进行相应转换：
   MySQL INSERT查询被转换为INSERT with _sign=1。
   MySQL DELETE查询被转换为INSERT with _sign=-1。
   MySQL UPDATE查询被转换成INSERT with _sign=1和INSERT with _sign=-1。
3. SELECT查询
   如果在SELECT查询中没有指定_version，则使用FINAL修饰符，返回_version的最大值对应的数据，即最新版本的数据。
   如果在SELECT查询中没有指定_sign，则默认使用WHERE _sign=1，即返回未删除状态（_sign=1)的数据。
4. 索引转换
   ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。
   ClickHouse只有一个物理顺序，由ORDER BY子句决定。如果需要创建新的物理顺序，请使用物化视图。

2.7.2 案例实操

2.7.2.1 MySQL开启binlog和GTID模式

1. 确保 MySQL 开启了 binlog 功能，且格式为 ROW

   打开/etc/my.cnf,在[mysqld]下添加：

   server-id=1 
   log-bin=mysql-bin
   binlog_format=ROW
   

2. 开启GTID模式

   如果如果clickhouse使用的是20.8 prestable之后发布的版本，那么MySQL还需要配置开启GTID模式, 这种方式在mysql主从模式下可以确保数据同步的一致性(主从切换时)。

   gtid-mode=on
   enforce-gtid-consistency=1    # 设置为主从强一致性
   log-slave-updates=1   # 记录日志
   

   GTID 是 MySQL 复制增强版，从 MySQL 5.6 版本开始支持，目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一ID和序号，我们可以不用关心 MySQL 集群主从拓扑结构，直接告知 MySQL 这个 GTID 即可。

3. 重启MySQL

   sudo systemctl restart mysqld
   

2.7.2.2 准备MySQL表和数据

1. 在 MySQL 中创建数据表并写入数据

   CREATE DATABASE testck;
   
   CREATE TABLE `testck`.`t_organization` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `code` int NOT NULL,
     `name` text DEFAULT NULL,
     `updatetime` datetime DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY (`code`)
   ) ENGINE=InnoDB;
   
   INSERT INTO testck.t_organization (code, name,updatetime) VALUES(1000,'Realinsight',NOW());
   INSERT INTO testck.t_organization (code, name,updatetime) 
   VALUES(1001, 'Realindex',NOW());
   INSERT INTO testck.t_organization (code, name,updatetime) VALUES(1002,'EDT',NOW());
   

2. 创建第二张表

   CREATE TABLE `testck`.`t_user` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `code` int,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB;
   
   INSERT INTO testck.t_user (code) VALUES(1);
   

2.7.2.3 开启ClickHouse物化引擎

set allow_experimental_database_materialize_mysql=1;

2.7.2.4 创建复制管道

1. ClickHouse中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('hadoop1:3306','testck','root','000000');

​ 其中 4 个参数分别是 MySQL地址、databse、username 和 password。

2. 查看ClickHouse的数据

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;

2.7.2.5 修改数据

1. 在 MySQL 中修改数据:

update t_organization set name = CONCAT(name,'-v1') where id = 1

2. 查看clickhouse日志可以看到binlog监听事件，查询clickhouse

select * from t_organization;

2.7.2.6 删除数据

1. MySQL删除数据:

DELETE FROM t_organization where id = 2;

2. ClicKHouse，日志有 DeleteRows 的 binlog 监听事件，查看数据：

select * from t_organization;

3. 在刚才的查询中增加 _sign 和 _version 虚拟字段

select *,_sign,_version from t_organization order by _sign desc,_version desc;

在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign = -1 的数据过滤掉；

对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。

select * from t_organization
等同于
select * from t_organization final where _sign = 1

2.7.2.7 删除表

1. 在mysql执行删除表

drop table t_user;

2. 此时在clickhouse处会同步删除对应表，如果查询会报错

show tables;
select * from t_user;
DB::Exception: Table scene_mms.scene doesn't exist.. 

3. mysql新建表，clickhouse可以查询到

CREATE TABLE `testck`.`t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO testck.t_user (code) VALUES(1);

#ClickHouse查询
show tables;
select * from t_user;

2.8 CK 常见问题排查

2.8.1 分布式DDL某数据节点的副本不执行

1. **问题：**使用分布式ddl执行命令create table on cluster xxxx 某个节点上没有创建表，但是client返回正常，查看日志有如下报错。

   <Error> xxx.xxx: Retrying createReplica(), because some other replicas were created at the same time
   

2. **解决办法：**重启该不执行的节点。

2.8.2 数据副本表和数据不一致

1. **问题：**由于某个数据节点副本异常，导致两数据副本表不一致，某个数据副本缺少表，需要将两个数据副本调整一致。

2. 解决办法：

   • 在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过show crete table xxxx获取。

   • 表结构创建后，clickhouse会自动从其他副本同步该表数据，验证数据量是否一致即可。

2.8.3 副本节点全量恢复

1. **问题：**某个数据副本异常无法启动，需要重新搭建副本。

2. 解决办法：

   • 清空异常副本节点的metadata和data目录。

   • 从另一个正常副本将metadata目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。

   • 执行sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data

   • 启动数据库。

2.8.4 数据副本启动缺少zk表

1. **问题：**某个数据副本表在zk上丢失数据，或者不存在，但是metadata元数据里存在，导致启动异常，报错：

   Can’t get data for node /clickhouse/tables/01-02/xxxxx/xxxxxxx/replicas/xxx/metadata: node doesn’t exist (No node): Cannot attach table  xxxxxxx
   

2. 解决办法：

   • metadata中移除该表的结构文件，如果多个表报错都移除

   • mv metadata/xxxxxx/xxxxxxxx.sql /tmp/

   • 启动数据库

   • 手工创建缺少的表(CK此时会向ZK中写入一份)，表结构从其他节点show create table获取。

   • 创建后会自动同步数据，验证数据是否一致。

2.8.5 ZK table replicas数据未删除，导致重建表报错

1. **问题：**重建表过程中，先使用drop table xxx on cluster xxx ,各节点在clickhouse上table已物理删除，但是zk里面针对某个clickhouse节点的table meta信息未被删除（低概率事件），因zk里仍存在该表的meta信息，导致再次创建该表create table xxx on cluster, 该节点无法创建表(其他节点创建表成功)，报错：

   Replica /clickhouse/tables/01-03/xxxxxx/xxx/replicas/xxx already exists..
   

2. 解决办法：

   • 从其他数据副本cp该table的metadata sql过来.

   • 重启节点。

2.8.6 Clickhouse节点意外关闭

1. **问题：**模拟其中一个节点意外宕机，在大量insert数据的情况下，关闭某个节点。

2. **现象：**数据写入不受影响、数据查询不受影响、建表DDL执行到异常节点会卡住，报错：

   Code: 159. DB::Exception: Received from localhost:9000. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000565925 is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 1 unfinished hosts (0 of them are currently active), they are going to execute the query in background.
   

3. **解决办法：**启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的建表DDL也会同步

2.8.7 其他问题参考

https://help.aliyun.com/document_detail/162815.html?spm=a2c4g.11186623.6.652.312e79bd17U8IO

三、ClickHouse监控及备份

3.1 ClickHouse监控概述

ClickHouse 运行时会将一些个自身的运行状态记录到众多系统表中( system.*)。所以我们对于 ClickHouse 自身的一些运行指标的监控数据，也主要来自这些系统表。

但是直接查询这些系统表会有一些不足之处：

• 这种方式太过底层，不够直观，我们还需要在此之上实现可视化展示；

• 系统表只记录了ClickHouse自己的运行指标，有些时候我们需要外部系统的指标进行关联分析，例如 ZooKeeper、服务器 CPU、IO 等等。

现在Prometheus + Grafana的组合比较流行，安装简单易上手，可以集成很多框架，包括服务器的负载, 其中 Prometheus 负责收集各类系统的运行指标; Grafana 负责可视化的部分。

ClickHouse 从 v20.1.2.4 开始，内置了对接 Prometheus 的功能，配置的方式也很简单,可以将其作为 Prometheus 的 Endpoint 服务，从而自动的将 metrics、events 和 asynchronous_metrics 三张系统的表的数据发送给 Prometheus。

3.2 Prometheus & Grafana的安装

Prometheus下载地址：https://prometheus.io/download/

Grafana下载地址：https://grafana.com/grafana/download

3.2.1 安装Prometheus

Prometheus基于Golang编写，编译后的软件包，不依赖于任何的第三方依赖。只需要下载对应平台的二进制包，解压并且添加基本的配置即可正常启动Prometheus Server。

3.2.1.1 上传并解压安装包

1. 上传prometheus-2.26.0.linux-amd64.tar.gz到虚拟机的/opt/software/promethues目录下

2. 解压到/opt/module/promethues目录下

   [heather@hadoop102 promethues]$ tar -zxvf prometheus-2.26.0.linux-amd64.tar.gz -C /opt/module/promethues/
   

3. 修改目录名

   [heather@hadoop102 ~] cd /opt/module/promethues/
   [heather@hadoop102 promethues] mv prometheus-2.26.0.linux-amd64 prometheus-2.26
   

3.2.1.2 修改配置文件promethues.yml

[heather@hadoop102 prometheus-2.26]$ sudo vim prometheus.yml

在scrape_configs配置项下添加配置（yml文件注意缩进）：

scrape_configs:
  
  - job_name: 'prometheus'
    static_configs:
    - targets: ['hadoop102:9090']

  #添加ClickHouse监控配置
  - job_name: clickhouse-1
    static_configs:
      - targets: ['hadoop102:9363']

配置说明：

1. global配置块：控制Prometheus服务器的全局配置

• scrape_interval：配置拉取数据的时间间隔，默认为1分钟。
• evaluation_interval：规则验证（生成alert）的时间间隔，默认为1分钟。

2. rule_files配置块：规则配置文件
3. scrape_configs配置块：配置采集目标相关， prometheus监视的目标。Prometheus自身的运行信息可以通过HTTP访问，所以Prometheus可以监控自己的运行数据。

• job_name：监控作业的名称
• static_configs：表示静态目标配置，就是固定从某个target拉取数据
• targets：指定监控的目标，其实就是从哪儿拉取数据。Prometheus会从http://hadoop102:9090/metrics上拉取数据。

Prometheus是可以在运行时自动加载配置的。启动时需要添加：--web.enable-lifecycle

3.2.1.3 启动Promethues Server

[heather@hadoop102 prometheus-2.26 ]$ nohup ./prometheus --config.file=prometheus.yml > ./prometheus.log 2>&1 &

• 浏览器输入：http://hadoop102:9090/
• 点击 Status，选中Targets：

prometheus是up状态，表示安装启动成功。

3.2.2 安装Grafana

3.2.2.1 上传并解压安装包

1. 将grafana-7.5.2.linux-amd64.tar.gz上传至/opt/software/grafana目录下

2. 解压到/opt/module/grafana目录下

   [heather@hadoop102 grafana]$ tar -zxvf grafana-7.5.2.linux-amd64.tar.gz -C /opt/module/grafana
   

3. 修改目录名

   [heather@hadoop102 grafana]$ mv grafana-7.5.2.linux-amd64 grafana-7.5.2
   

3.2.2.2 启动Grafana

[heather@hadoop102 grafana-7.5.2]$ nohup ./bin/grafana-server web > ./grafana.log 2>&1 &

打开web：http://hadoop102:3000,默认用户名和密码：admin

3.3 ClickHouse配置

3.3.1 修改配置文件

编辑/etc/clickhouse-server/config.xml，打开如下配置：

<prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>

        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
        <status_info>true</status_info>
  </prometheus>

如果有多个ClickHouse节点，分发配置。

3.3.2 重启ClickHouse

sudo clickhouse restart

Float64 – double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

使用场景：一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量。

3.3.3 访问web查看

浏览器打开: http://hadoop102:9363/metrics

看到信息说明ClickHouse开启Metrics服务成功。

3.4 Grafana集成Prometheus

3.4.1 添加数据源Prometheus

1. 点击配置，点击Data Sources：

2. 点击添加按钮：

   

3. 找到Prometheus，点击Select

   

4. 配置Prometheus Server地址：

   

5. 点击下方的Save&Test：

   

6. 出现绿色的提示框，表示与Prometheus正常联通：

   

7. 点击Back返回即可，可以看到Data Sources页面，出现了添加的Prometheus:

   

3.4.2 添加监控

手动一个个添加Dashboard比较繁琐，Grafana社区鼓励用户分享Dashboard，通过https://grafana.com/dashboards网站，可以找到大量可直接使用的Dashboard模板。

Grafana中所有的Dashboard通过JSON进行共享，下载并且导入这些JSON文件，就可以直接使用这些已经定义好的Dashboard。

1. 点击左侧 ”+”号，选择import：

   

2. 上传JSON文件：

   

3.5 备份及恢复

官网：https://clickhouse.tech/docs/en/operations/backup/

3.5.1 手动实现备份及恢复

ClickHouse允许使用 ALTER TABLE … FREEZE PARTITION … 查询以创建表分区的本地副本。 这是利用硬链接(hardlink)到 /var/lib/clickhouse/shadow/ 文件夹中实现的，所以它通常不会因为旧数据而占用额外的磁盘空间。 创建的文件副本不由ClickHouse服务器处理，所以不需要任何额外的外部系统就有一个简单的备份。防止硬件问题，最好将它们远程复制到另一个位置，然后删除本地副本。

3.5.1.1 创建备份路径

创建用于临时存放备份数据的目录shadow

[heather@hadoop102 ~ ]$ sudo mkdir -p /var/lib/clickhouse/shadow/

如果目录存在，先清空目录下的数据，在清空之前，请参考[3.5.1.3 将备份数据保存到其他路径](# 3.5.1.3 将备份数据保存到其他路径)

创建目录后，修改目录归属为heather:heather

3.5.1.2 执行备份命令

备份命令只会备份数据，不会备份创建表的语句，所以需要提前备份创建表语句。

echo -n 'alter table t_order_mt freeze' | clickhouse-client

3.5.1.3 将备份数据保存到其他路径

创建备份存储路径

[heather@hadoop102 ~ ]$ sudo mkdir -p /var/lib/clickhouse/backup/

拷贝数据到备份路径

[heather@hadoop102 ~ ]$ sudo cp -r /var/lib/clickhouse/shadow/ /var/lib/clickhouse/backup/my-backup-name

为下次备份准备，删除shadow下的数据

[heather@hadoop102 ~ ]$ sudo rm -rf /var/lib/clickhouse/shadow/*

3.5.1.4 恢复数据

1. 模拟删除备份过的表

echo ' drop table t_order_mt ' | clickhouse-client

1. 重新创建表

cat t_order_mt.sql | clickhouse-client

3. 将备份复制到detached目录

[heather@hadoop102 ~ ]$ sudo cp -rl backup/my-backup-name/1/store/cb1/cb176503-cd88-4ea8-8b17-6503cd888ea8/* data/default/t_order_mt/detached/

ClickHouse使用文件系统硬链接来实现即时备份，而不会导致ClickHouse服务停机（或锁定）。这些硬链接可以进一步用于有效的备份存储。在支持硬链接的文件系统（例如本地文件系统或NFS）上，将cp与-l标志一起使用（或将rsync与–hard-links和–numeric-ids标志一起使用）以避免复制数据，导致数据重复。

注意：仅拷贝分区目录，注意目录所属的用户要是clickhouse

4. 执行attach

echo 'alter table t_order_mt attach partition 20200601' | clickhouse-client

5. 查看数据

echo 'select count() from t_order_mt' | clickhouse-client

3.5.2 使用clickhouse-backup

上面的过程，我们可以使用Clickhouse的备份工具clickhouse-backup帮我们自动化实现。

工具地址：https://github.com/AlexAkulov/clickhouse-backup/

3.5.2.1 上传并安装

将clickhouse-backup-1.0.0-1.x86_64.rpm上传至/opt/software/目录下，安装：

[heather@hadoop102 software]$ sudo rpm -ivh clickhouse-backup-1.0.0-1.x86_64.rpm

3.5.2.2 配置文件

[heather@hadoop102 ~]$ cat /etc/clickhouse-backup/config.yml

3.5.2.3 创建备份

1. 查看可用命令

[heather@hadoop102  ~]$ clickhouse-backup help

2. 显示要备份的表

[heather@hadoop102  ~]$ clickhouse-backup tables

3. 创建备份

[heather@hadoop1 ~]$ sudo clickhouse-backup create

4. 查看现有的本地备份

[heather@hadoop102  ~]$ sudo clickhouse-backup list

备份存储在中/var/lib/clickhouse/backup/BACKUPNAME。备份名称默认为时间戳，但是可以选择使用–name标志指定备份名称。备份包含两个目录：一个“metadata”目录，其中包含重新创建架构所需的DDL SQL语句；以及一个“shadow”目录，其中包含作为ALTER TABLE … FREEZE操作结果的数据。

3.5.2.4 从备份恢复数据

1. 模拟删除备份过的表

echo 'drop table t_order_rmt' | clickhouse-client

2. 从备份还原

sudo clickhouse-backup restore 2021-07-25T23-14-50

• –schema参数：只还原表结构。
• –data参数：只还原数据。
• –table参数：备份（或还原）特定表。也可以使用一个正则表达式，例如，针对特定的数据库：–table=dbname.*。

3.5.2.5 其他说明

1. API文档：https : //github.com/AlexAkulov/clickhouse-backup#api
2. 注意事项：切勿更改文件夹/var/lib/clickhouse/backup的权限，可能会导致数据损坏。
3. 远程备份
   • 较新版本才支持，需要设置config里的s3相关配置
   • 上传到远程存储：sudo clickhouse-backup upload xxxx
   • 从远程存储下载：sudo clickhouse-backup download xxxx
   • 保存周期： backups_to_keep_local，本地保存周期，单位天
     backups_to_keep_remote，远程存储保存周期，单位天，0均表示不删除