ck 官网： ​​https://clickhouse.com/​​

docker 安装ck参考： ​​https://hub.docker.com/r/clickhouse/clickhouse-server/​​

1. 简介

1. 简单介绍

ClickHouse 是俄罗斯的Yandex于2016 年开源的列式存储数据库(DBMS: Database Management System), 使用c++ 语言编写，主要用于在线分析处理查询(OLAP: Online Analytical Processing)。

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

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

2. 特点

  1. 列式存储

以下面的表格为例：

id

name

age

1

张三

18

2

李四

17

3

王五

20

1》采用行式存储时，数据在磁盘的存储结构可能如下：

好处是查询某个人的所有属性时，可以通过一次磁盘查找加顺序读取就可以。但是想查所有人的年龄时，就需要不停的找，或者全表扫描才行。

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

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

3》 列式存储优点

对于列的聚合、计数、求和等统计操作原因优于行式存储

由于某一列的数据类型是相同的，对数据存储上更容易压缩；节省空间，对cache 也有更好

4》 高吞吐写入能力

ck 采用类似LSMTree(Log Structured Merge Tree, 是一种分层，有序，面向磁盘的数据结构，其核心思想是充分了利用了，磁盘批量的顺序写要远比随机写性能高出很多) 的结构， 数据写入后定期在后台Compaction。 通过类LSM Tree的结构，CK在数据导入时全部是顺序append 写，写入后数据段不可更改，在后台compaction后也是多个段merge sort后顺序写回磁盘。

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

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

所以，ck 即使对于大量数据的查询也能够化整为零平行处理。 但是有一个弊端就是对于单条查询使用多CPU， 就不利于同时并发多条查询。 所以对于高qps的查询业务，ck 并不是强项。

2. 安装

基于docker 直接安装

1》 安装clickhouse-server

docker run -d --name clickhouse-server1 --ulimit nofile=262144:262144 clickhouse/clickhouse-server

2》进入容器

docker exec -it clickhouse-server1 bash

3》在容器内部进行测试: 连接到服务之后查看数据库

root@25f56c441b1a:/# clickhouse-client 
ClickHouse client version 21.12.3.32 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.

25f56c441b1a :) show databases;

SHOW DATABASES

Query id: 3fa99c37-25c1-44a9-b372-f4b5995965b7

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────────────┘

4 rows in set. Elapsed: 0.001 sec. 

25f56c441b1a :) select 1-1

SELECT 1 - 1

Query id: d3351080-3fe1-4023-bb56-8d2842385ce8

┌─minus(1, 1)─┐
│           0 │
└─────────────┘

1 rows in set. Elapsed: 0.001 sec.

3. 数据类型

  1. 整型

固定长度的整型，包括有符号整型和无符号整型。

整型范围： -2^(n-1) ~ 2^(n-1)-1

Int8、Int16、Int32、Int64

无符号整型范围：(o ~ 2^n-1)

UInt8、UInt16、UInt32、UInt64

  1. 浮点型

Float32 - float

Float64 - double

  1. 布尔类型

没有单独的类型来存储布尔值，建议使用UInt8， 取值限制为[0 | 1]

  1. Decimal

有符号的浮点数，可在加减乘除算法中保持精度。有如下三种声明：

Decimal(P,S), Decimal32(S), Decimal64(S), Decimal128(S)
P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）
S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。
  1. 字符串

1.String 字符串可以任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。

2.FixedString(N) ：定长字符串，类似于mysql的char 类型。不同的是：

  • 如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。 mysql是填空格，且查询时删除空格。当做数据查询时，ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE子句，则须要手动添加空字节以匹配FixedString的值。

SELECT * FROM FixedStringTable WHERE a = 'b\0' # 我们存的定长2的单个b，匹配需要自己加空字节

  • 如果字符串包含的字节数大于​​N​​​,将抛出​​Too large value for FixedString(N)​​异常。
  1. 枚举类型

包括Enum8 和 Enum16 类型。 Enum 保存 'string'= integer 的对应关系。在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。

Enum8 用 'String'= Int8 对描述。

Enum16 用 'String'= Int16 对描述。

--- 建表
CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog
--- 插入
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
--- 查询
SELECT * FROM t_enum

┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
--- 查询转为int
SELECT CAST(x, 'Int8') FROM t_enum

┌─CAST(x, 'Int8')─┐
│               1 │
│               2 │
│               1 │
└─────────────────┘

---查询中创建枚举值
SELECT toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))

┌─toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2)                              │
└──────────────────────────────────────────────────────┘
  1. uuid 类型

要生成UUID值，ClickHouse提供了 generateuidv4 函数。 如果插入时不指定UUID值，UUID值将用零填充(00000000-0000-0000-0000-000000000000)

CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
8. 日期和时间

Date 日期：用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。值的范围: [1970-01-01, 2149-06-06]。

Datetime： 接收年月日 时分秒的字符串

Datetime64 接收年月日时分秒亚秒的字符串， 比如： 的字符串比如‘2019-12-16 20:50:10.66

  1. 其他

其他还有数组类型、Map类型、Geo、Tuple元组类型。

4.表引擎

表引擎决定了如何存储表的数据。包括：

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

必须显示地在创建表时定义该表使用的引擎，以及引擎使用的相关参数。

1. TinyLog

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

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

CREATE TABLE t_tinylog
(
    `id` String,
    `name` String
)
ENGINE = TinyLog

Query id: d4ac3598-dfa7-483b-9f08-b31514d7071d

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.3 revision 54452.

Ok.

0 rows in set. Elapsed: 0.006 sec. 

25f56c441b1a :) show create table t_tinylog;

SHOW CREATE TABLE t_tinylog

Query id: 1085fc7f-b8f9-4b9c-8f6c-587d6b78f839

┌─statement─────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_tinylog
(
    `id` String,
    `name` String
)
ENGINE = TinyLog │
└───────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

2. memory

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

3. MergeTree(合并树)

ck中最强大的引擎就是MergeTree以及该系列中的其他引擎。支持索引和分区，低位相当于mysql的innodb。

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)
  1. 插入语句
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')
  1. 查询
ac9033c7171a :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 12fcf25a-b344-48b1-879c-21f9380e9272

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

6 rows in set. Elapsed: 0.002 sec.

1. partition by 分区(可选)

1》作用：降低扫描的范围，优化查询速度

2》默认：使用一个分区

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

4》并行：分区后，面对涉及跨分区的查询统计，ck会以分区为单位并行出咯

5》数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻(大概10-15分钟后)，ck会自动执行合并操作(当然可以手动通过optimize 执行)，把临时分区的数据合并到已有分区

optimize table xxx final;

测试：

再次插入数据：

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

再次查询：(发现没有进入分区)

ac9033c7171a :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: 25dff719-964c-4416-827c-cbb8e9f42754

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.004 sec.

optimize 后再次查询

ac9033c7171a :) optimize table t_order_mt final;

OPTIMIZE TABLE t_order_mt FINAL

Query id: ef8da207-8575-4806-a14e-584d94456de1

Ok.

0 rows in set. Elapsed: 0.003 sec. 

ac9033c7171a :) select * from t_order_mt;

SELECT *
FROM t_order_mt

Query id: a92090be-36c3-4e7c-b727-cc46929897e3

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

12 rows in set. Elapsed: 0.002 sec.

2. primary key 主键(可选)

ck的主键和其他数据库不太已有，只提供了数据的一级索引，但是却不是唯一约束。意味着可以存在相同primary key的数据。

主键的设定主要依据是查询语句中的where条件。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity(索引粒度，指在稀疏索引中两个相邻索引对应数据的建个，ck默认的mergeTree是8192)，避免了全表扫描。

稀疏索引：简单的说就是间接性的建立索引。 比如1,2,3,4,5,6 建立索引1,3,6。好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行点扫描。

3. order by (必选)

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

要求：主键必须是order by字段的前缀字段。比如order by字段是(id, sku_id), 那么主键必须是id 或者 (id, sku_id)

4. 数据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)

2》插入数据

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2022 08 05 07:20:30'),
(107,'sku_002',2000.00,'2022 08 05 07:20:30'),
(110,'sku_003',600.00,'2022 08 06 19:20:30')

3》手动合并

optimize table t_order_mt3 final;

4》查询

查询后106、107 两行的total_amount 属性变为0

5》表级TTL

alter table t_order_mt4 modify ttl create_time + interval 10 second

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

插入数据:

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2022 08 05 11:29:30')

查询： 当超过创建时间10s后，整条数据消失

4. ReplacingMergeTree

ReplacingMergeTree是MergeTree 的一个变种，它存储特性完全继承MergeTree，只是多了去重功能。MergeTree对主键不做唯一约束，这个是可以做到基于主键去重。会根据ORDER BY所声明的表达式去重

  1. 去重时机：只会出现在合并的过程中。
  2. 去重范围：如果经过了分区，只会在分区内部去重，不会跨分区(也就是只能保证通分区没有重复数据)
  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传入的参数为版本字段，重复数据保留版本字段值最大的。不填版本字段，默认按插入顺序保留最后一条。

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》查询

ac9033c7171a :) select * from t_order_rmt

SELECT *
FROM t_order_rmt

Query id: da9a22da-6d49-4ecc-b279-b6e9bf023c3e

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

4 rows in set. Elapsed: 0.002 sec.

5. SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景可以用该引擎。可以理解为预聚合。会根据ORDER BY所声明的表达式对指定字段sum

也是只针对当前分区进行聚合，且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 )

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》执行查询

ac9033c7171a :) select * from t_order_smt;

SELECT *
FROM t_order_smt

Query id: 5f4f0695-93a5-4393-8fec-0523f4374dc8

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id───┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_ 001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002  │        16000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_004  │         2500 │ 2020-06-01 12:00:00 │
└─────┴──────────┴──────────────┴─────────────────────┘

补充： OLAP和OLTP区别

联机事务处理（On-Line Transaction Processing，OLTP） - 传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，记录即时的增、删、改、查，比如在银行存取一笔款，就是一个事务交易
联机分析处理（On-Line Analytical Processing，OLAP） - OLAP即联机分析处理，是数据仓库的核心部心，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果

【当你用心写完每一篇博客之后,你会发现它比你用代码实现功能更有成就感!】