ClickHouse-入门

1. OLTP和OLAP

1.1 OLTP

  • 联机事务处理系统，全称：Online Transaction Processing
  • 是对数据库的增删改查操作
  • 对事物支持，要求响应时间快，
  • 主要面向业务开发，数据量相对较少，GB级别
  • Mysql，Oracle

1.2 OLAP

  • 联机分析处理系统，全称：Online Analytical Processing
  • 支持复杂的分析查询，做出响应的决策作用
  • 可操作的查询数据量非常大，可以到TB级别
  • 主要面向风控，BI，数字大屏等方面
  • Hive，ClickHouse

2. 列式存储

2.1 行存储

​ 传统的OLTP数据库基本都是行存储，一行数据连续存储的磁盘中，适合随机的增删改查操作，可以通过添加索引加快查询效率。如果需要查询全部记录的某个字段，需要读取全部行记录，效率低下

2.2 列式存储

​ 以列为单位存储数据，比如存储一批数据，先批量存储第一列字段，再存储第二列…，查询时，只会扫描需要涉及到的列，不会全表扫描，可以大大减少磁盘IO，列式存储在聚合统计方面性能远远优于行存储，不过更新成本会比行存储更高，一般适合海量数据的读多写少的场景，适用于OLAP

3. ClickHouse简介

是2016年俄罗斯Yandex开源的列式存储数据库，近几年在OLAP领域广泛应用。

官网：https://clickhouse.com/

官方中文文档：https://clickhouse.com/docs/zh/

GitHub: https://github.com/ClickHouse/ClickHouse

官方提供的性能和查询能力评估地址：https://benchmark.clickhouse.com/

3.1 特点

  • 安装，使用，维护简单，不依赖Hadoop生态
  • 对列的聚合统计等操作性能强大，在HBase，BigTable，Cassandra，HyperTable这些系统中，分析查询能力在每秒几十万行的吞吐量，ClickHouse可以达到每秒几亿行的吞吐能力
  • 有专门的数据压缩算法和专用编解码器针对数据进行压缩，节省存储空间
  • 扩展性强，支持数千节点的集群水平扩展
  • 支持常用的SQL语法和函数

4. 安装

4.1 Docker安装

1
2
3
4
5
6
7
docker run -d --name clickhouse --ulimit nofile=262144:262144 \
-p 8123:8123 -p 9000:9000 -p 9009:9009 --privileged=true \
-e CLICKHOUSE_USER=root \
-e CLICKHOUSE_PASSWORD=aacopy.cn \
-v /home/dockerdata/clickhouse/log:/var/log/clickhouse-server \
-v /home/dockerdata/clickhouse/data:/var/lib/clickhouse \
clickhouse/clickhouse-server:23
  • 8123：http端口
  • 9000：tcp端口
  • 9009：同步端口

4.2 可视化工具

使用DBeaver连接clickhouse，需要设置驱动包，可以根据maven里的依赖jar进行添加

5. 数据类型

5.1 整型

固定长度，分为有符号和无符号整型

  • 有符号
    • Int8 -> [-128 ~ 127]
    • Int16 -> [-32768 ~ 32767]
    • Int32 -> [-2147483648 ~ 2147483647]
    • Int64 -> [-9223372036854775808 ~ 9223372036854775807]
    • Int128 -> [-170141183460469231731687303715884105728 ~ 170141183460469231731687303715884105727]
    • Int256 -> [-57896044618658097711785492504343953926634992332820282019728792003956564819968 ~ 57896044618658097711785492504343953926634992332820282019728792003956564819967]
  • 无符号
    • UInt8 -> [0 ~ 255]
    • UInt16 -> [0 ~ 65535]
    • UInt32 -> [0 ~ 4294967295]
    • UInt64 -> [0 ~ 18446744073709551615]
    • UInt128 -> [0 ~ 340282366920938463463374607431768211455]
    • UInt256 -> [0 ~ 115792089237316195423570985008687907853269984665640564039457584007913129639935]

5.2 浮点型

存在进度丢失问题

  • Float32
  • Float64

5.3 Decimal

用于高精度数值运算

Decimal(P, S)

P：精度（precise），表示总位数，有效范围：[1:38]，整数部分+小数部分

S：规模（Scale），有效范围：[0：P]，表示小数位数

例如：Decimal(10, 2)，表示整数部分为8位，小数部分为2位

5.4 UUID

通用唯一标识符(UUID)是一个16字节的数字，用于标识记录

5.5 FixedString

FixedString(N)，固定长度 N 的字符串（N 必须是严格的正自然数）

5.6 String

任意长度的字符串

5.7 Date

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

5.7 DateTime

时间戳类型。用四个字节（无符号的）存储 Unix 时间戳）。允许存储与日期类型相同的范围内的值,值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]

5.8 Enum

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

Enum8 用 'String'= Int8 对描述

Enum16 用 'String'= Int16 对描述

5.9 布尔值

Bool

5.10 其他

可以看官方文档：https://clickhouse.com/docs/zh/sql-reference/data-types/

5.11 与Mysql的对比

ClickHouse Mysql
UInt8 UNSIGNED TINYINT
Int8 TINYINT
UInt16 UNSIGNED SMALLINT
Int16 SMALLINT
UInt32 UNSIGNED INT, UNSIGNED MEDIUMINT
Int32 INT, MEDIUMINT
UInt64 UNSIGNED BIGINT
Int64 BIGINT
Float32 FLOAT
Float64 DOUBLE
Date DATE
DateTime DATETIME, TIMESTAMP
FixedString CHAR
String VARCHAR、BLOB、CLOB、TEXT

6. SQL语法

6.1 建表

1
2
3
4
5
6
7
CREATE TABLE table_test.user (
    id Int64,
    name String,
    create_time DateTime
) 
ENGINE = MergeTree()
ORDER BY (create_time)
  • ENGINE：建表必须指定引擎类型，引擎决定数据的存储方式，位置，查询方式

6.2 查看表结构

1
DESCRIBE table_test.user

6.3 查询数据

1
SELECT * FROM table_test.user

6.4 插入数据

1
INSERT INTO table_test.user VALUES(1, 'admin', 2023-02-20 16:37:01)

6.5 更新数据

1
ALTER TABLE table_test.user UPDATE name = 'admin' WHERE id = 1

6.6 删除数据

1
ALTER TABLE table_test.user DELETE WHERE id = 1

6.7 更新和删除的说明

  • 旧版本的clickhouse是不支持更新和删除操作的
  • 不支持事物
  • 更新和删除操作都是异步的，执行完语句后，不会立即执行，大概会在15分钟之内执行
  • SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 10可以查询数据是否已经执行
  • clickhouse每次更新或者删除，都会废弃目标数据所在的分区，并重新建一个新的分区，所以代价高，效率低
  • 批量操作比循环单条操作效率更高

7. 分区分片

7.1 分区

  • 把一张表数据分成N个区，不通的区数据会在不同的文件中，分区后还是同一张表，数据处理还是由clickhouse自己完成处理
  • 使用MergeTree在创建表时，加上PARTITION BY 按照表中某些字段进行分区，例如：partition by toYYYYMMDD(create_time)，按创建时间的天分区
  • 查询时，指定分区键可以尽可能的减少读取的数据量
  • 新插入的数据会先放在临时分区中，只有在合并分区时（15分钟内），才将数据转到各自符合的分区
  • 手动合并分区：optimize table xxx final
1
2
3
4
5
6
7
8
9
CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

7.2 分段

  • 分段把数据库横向扩展到多个物理节点上，把原来单节点分区中的数据分散到各个分片中。clickshouse会通过distributed表引擎把数据拼接起来使用

  • 分片功能可以让clickhouse支持分布式集群部署

  • 可以通过多节点的并行查询，提高查询效率

  • Distributed表引擎

    • 主要用于分布式场景
    • 本身不存储数据，数据存储在各个分片上，但可以在多个服务器上进行分布式查询
    • 可以通过路由到集群中的各个节点，需要其他表引擎配合使用
    • 创建分布式表需要创建分布式表（xxx）和本地表（xxx_local），查询的时候只需要查分布式表就可以

  • 创建分布式表sql

1
ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])
  • 分布式引擎参数
    • cluster - 集群名称
    • database - 远程数据库名
    • table - 远程数据表名
    • sharding_key - (可选) 分片key，会依据分⽚key的规则，将数据分布到各个节点的本地表
    • policy_name - (可选) 规则名，它会被用作存储临时文件以便异步发送数据

7.3 副本

  • 类似与mysql的主从架构，clickhouse存在两个相同数据表在不同的节点上，通过数据备份保障数据安全

  • 只有 MergeTree 系列里的表可支持副本

    • ReplicatedMergeTree
    • ReplicatedSummingMergeTree
    • ReplicatedReplacingMergeTree
    • ReplicatedAggregatingMergeTree
    • ReplicatedCollapsingMergeTree
    • ReplicatedVersionedCollapsingMergeTree
    • ReplicatedGraphiteMergeTree

  • 副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复制表和非复制表。

  • 不依赖分片，每个分片有它自己的独立副本。

  • 数据的复制是异步的，数据先插入到执行语句的节点上，再异步同步到其他节点

  • 默认情况下，insert只会等待一个副本写入成功就返回，如果需要等待所有副本都写入，需要配置insert_quorum，性能会降低

  • 副本的同步，需要借助zk，通过zk监听事件

1
ENGINE = ReplicatedMergeTree('{zk_path}', '{replica}')

8. 表引擎

8.1 MergeTree

​ Clickhouse 中最强大的表引擎 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎，MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

1
2
3
4
5
6
7
8
9
CREATE TABLE table_test.user (
    id Int64,
    name String,
    create_time DateTime
) 
ENGINE = MergeTree()
ORDER BY (id, create_time)
partition by toYYYYMMDD(create_time)
primary key (id)

特点：

  • 数据按主键排序，可以通过稀疏索引加快检索
  • ORDER BY指定排序键，可以指定多个。
  • 主键可以重复，默认情况下主键跟排序键（ ORDER BY ）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY ，如果要单独指定主键。PRIMARY KEY必须是ORDER BY的前缀字段，例如：ORDER BY (CounterID, EventDate)，主键可以定义为（CounterID）或（CounterID, EventDate）
  • 可以使用分区和副本

8.2 ReplacingMergeTree

  • 该引擎和 MergeTree 的不同之处在于它会删除排序键（ORDER BY）值相同的重复项，若排序字段为两个，则两个字段都相同时才会去重。
  • 如果是有多个分区，只在分区内部进⾏删除，跨分区不删除重复项。
  • 数据的去重只会在数据合并期间进行。
  • 合并时间不确定，一般在15分钟内（实际测试发现，并不是15分钟内，有时候几个小时才合并一次）。
  • 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
1
ENGINE = ReplacingMergeTree([ver])

参数ver为版本列，类型为 UInt*, Date 或 DateTime。可选参数。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：

  • 如果 ver 列未指定，保留最后一条。
  • 如果 ver 列已指定，保留 ver 值最大的版本。

8.3 SummingMergeTree

  • 该引擎会把排序键（ORDER BY）相同的行中每一个数值类型的列数据汇总后合并成一行
  • 可以节省存储空间，并加快数据查询速度
  • 推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失有价值的数据。
  • 在设计聚合统计表时，合并字段都为各个维度，度量或者时间戳的字段，其他明细字段可以不添加
  • 对于非汇总合并的字段，在汇总后，只会保留第一次插入时的数据
1
ENGINE = SummingMergeTree([columns])
  • columns - 包含了将要被汇总的列的列名的元组。可选参数。 所选的列必须是数值类型，并且不可位于主键中。
  • 如果没有指定 columns，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 建表
CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

-- 插入数据
INSERT INTO summtt Values(1,1),(1,2),(2,1)

-- 数据查询
SELECT key, sum(value) FROM summtt GROUP BY key

-- 查询结果
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

查询要使用聚合函数 sum 和 GROUP BY 子句，因为ClickHouse 会按片段合并数据，单个片段内的数据是不完整的

9. 优化查询

9.1 执行计划

和mysql一样，在查询语句前加上explain，可以添加参数header，actions，打印更详细的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
explain header = 1, actions = 1 SELECT id, unique_id, app_id, send_time  FROM  msg_station_detail_a msda WHERE msda.app_id = 1 AND msda.deleted = 'N';

Expression ((Projection + Before ORDER BY))
Actions: INPUT :: 0 -> app_id Int64 : 0
         INPUT :: 1 -> id Int64 : 1
         INPUT :: 2 -> unique_id Int64 : 2
         INPUT :: 3 -> send_time DateTime : 3
Positions: 1 2 0 3
  Filter (WHERE)
  Filter column: and(equals(app_id, 1), equals(deleted, 'N')) (removed)
  Actions: INPUT : 0 -> equals(app_id, 1) UInt8 : 0
           INPUT :: 1 -> app_id Int64 : 1
           INPUT :: 2 -> id Int64 : 2
           INPUT :: 3 -> unique_id Int64 : 3
           INPUT :: 4 -> send_time DateTime : 4
           INPUT : 5 -> deleted String : 5
           COLUMN Const(String) -> 'N' String : 6
           FUNCTION equals(deleted :: 5, 'N' :: 6) -> equals(deleted, 'N') UInt8 : 7
           FUNCTION and(equals(app_id, 1) :: 0, equals(deleted, 'N') :: 7) -> and(equals(app_id, 1), equals(deleted, 'N')) UInt8 : 6
  Positions: 1 2 3 4 6
    SettingQuotaAndLimits (Set limits and quota after reading from storage)
      ReadFromMergeTree
      ReadType: Default
      Parts: 3
      Granules: 146

9.2 sql优化建议

在执行的sql前加上explain syntax，clickhouse会提供优化后的sql

1
2
3
4
5
6
7
8
9
10
explain SYNTAX SELECT id, unique_id, app_id, send_time  FROM  msg_station_detail_a msda WHERE msda.app_id = 1 AND msda.deleted = 'N';

SELECT
    id,
    unique_id,
    app_id,
    send_time
FROM msg_station_detail_a AS msda
PREWHERE app_id = 1
WHERE (app_id = 1) AND (deleted = 'N')

9.3 建表优化

  • 建表时，选择合适的表引擎，一般大数据量的统计查询使用MergeTree，根据具体的场景使用ReplacingMergeTree，SummingMergeTree等
  • 分区粒度不易过细，一张表最好在100个分区以内，分区过多反而影响查询效率
  • 排序order by列，排序列会加入索引，where条件中使用频率高的字段排在前面

9.4 查询SQL优化

  • 减少查询字段

    只查询必要的字段，会减少IO消耗

  • prewhere

    使用MergeTree引擎查询时，prewhere会先读取查询条件列来过滤数据，然后在根据select的字段补全所有数据，当查询的字段比条件字段多的时候，查询效率会有很大的提升

  • JOIN操作一定要把数据量小的表放在右边，无论是Left Join 、Right Join还是Inner Join，右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表

  • 避免大量的小批量数据，插入更新操作，会导致分区过多，每次插入1条，产生了一个分区，大量写入产生大量临时分区和合并操作浪费资源

  • 批量写入数据时，控制每个批次的数据中涉及到的分区的数量，无序的数据导致涉及的分区太多

  • 单sql查询可以压榨CPU ，但并发多条查询则不是很强，一个分区查询占据一个CPU，业务需要查询表的多个分区可以多个CPU并行处理

10. springboot集成

mybatisplus支持clickhouse的增删改查操作，和mysql使用方式基本一致

  • 添加依赖
1
2
3
4
5
6
7
8
9
10
11
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.1.55</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.4.0</version>
</dependency>
  • 添加配置
1
2
3
4
5
6
7
spring:
  datasource:
    url: jdbc:clickhouse://${quectel.clickhouse.server}:${quectel.clickhouse.port}/quectel_message
    username: ${quectel.clickhouse.uname}
    password: ${quectel.clickhouse.pwd}
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
  • 其它的操作和MySQL一样，通过代码生成器，生成增删改查代码
  • 插入操作注意事项
    • 插入操作中，mybatisplus是一条一条插入的，插入效率特别低，大批量数据插入时，不建议使用mybatis方式插入
    • 建议自己写批量插入操作的代码，测试单条插入和批量查询效率在万倍差距
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
public Integer insertMsgAllBatch(List<MsgAllDO> msgAllDOS) {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        try {
            DataSource chDataSource = dataSource.getDataSource("message_clickhouse");
            connection = chDataSource.getConnection();
            connection.setAutoCommit(false);
            prepareStatement = connection.prepareStatement(INSERT_ALL_SQL);
            for (MsgAllDO msgAllDO : msgAllDOS) {
                prepareStatement.setLong(1, msgAllDO.getId());
                prepareStatement.setLong(2, msgAllDO.getSendId());
                prepareStatement.setLong(3, msgAllDO.getMsgId());
                prepareStatement.setLong(4, msgAllDO.getAppId());
                prepareStatement.setString(5, msgAllDO.getGroupCode());
                prepareStatement.setLong(6, msgAllDO.getModelId());
                prepareStatement.setString(7, msgAllDO.getSummary());
                prepareStatement.setString(8, msgAllDO.getSender());
                prepareStatement.setString(9, msgAllDO.getReceiver());
                prepareStatement.setString(10, msgAllDO.getUserId());
                prepareStatement.setString(11, msgAllDO.getTenantId());
                prepareStatement.setString(12, msgAllDO.getChannel());
                prepareStatement.setString(13, msgAllDO.getState());
                prepareStatement.setString(14, msgAllDO.getTrigger());
                prepareStatement.setTime(15, new java.sql.Time(msgAllDO.getSendTime().getTime()));
                prepareStatement.addBatch();
            }
            int[] ints = prepareStatement.executeBatch();
            connection.commit();
            return ints.length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(prepareStatement != null) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }