ClickHouse基础

简介

ClickHouse是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库（DBMS），使用 C++语言编写，主要用于在线分析处理查询（OLAP，Online Analytical Processing），专注于海量数据查询，其速度非常快，每秒处理数据量上亿级，不支持事务；

ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个 index granularity(索引粒度)，且使用多核并行处理，能极大的提升查询效率，但在高 qps 的查询业务下 ClickHouse 会比较吃力。

• 前置条件

  由于 ClickHouse 比较耗费系统资源，需要修改系统文件句柄和最大进程数来使其性能极致发挥。

  还需要禁用掉 SELinux（美国国家安全局提供的一个安全系统） ，在安装 k8s 前置条件中有相同示例，参考配置即可。

• 安装

  可参考官网在生产环境安装 ClickHouse，比如使用 yum、apt-get、离线静态资源或者 docker，这里为了方便学习，直接使用 docker-compose 安装。

数据类型

类型	格式	说明
整型	Int8	大小占 8 位，整型范围-2n-1 ~ 2n-1-1，即-128~127
	Int16	16 位 ，-32768 ~ 32767
	Int32	-2147483648 ~ 2147483647
	Int63	-9223372036854775808 ~ 9223372036854775807
正整型	UInt8	0 ~ 255
	UInt16	0 ~ 65535
	UInt32	0 ~ 4294967295
	UInt64	0 ~ 18446744073709551615
浮点型	Float32	计算无法保证精度；大概取值范围-3.4e+38 ~ 3.4e+38
	Float64	计算无法保证精度；-1.797e+308 ~ 1.797e+308
布尔型	无	可使用 UInt8 类型，取值限制为 0 或 1
Decimal	Decimal32	Decimal 能保证小数计算的精度。最多保留 9 位有效数字，因为 32 位用 2 进制越为 21 亿，相当于 10 进制的 10 位。为了保证精度和符号，因此定义为 9 位；他相当于 Decimal(9-s,s)，其中 s 表示小数位
	Decimal64	最多保留 18 位有效数字；相当于 Decimal(18-s,s)
	Decimal128	相当于 Decimal(38-s,s)
字符串	String	可任意长度或空
时间	Date	接收的字符格式为 yyyy-MM-dd
	DateTime	格式 yyyy-MM-dd hh:mm:ss
	DateTime64	秒数精度更高，如 2024-12-16 20:50:10.66
数组	Array	数组能存任意类型，同一个数组中类型需一致

引擎

• 数据库引擎

  ClickHouse 默认使用Atomic数据库引擎，支持非阻塞查询。它也支持多种数据库引擎，以 MySQL 为例：

  -- MySQL 数据库引擎会将语句发送到 MySQL 服务器中执行
  create database db_name
  engine = MySQL('host:port', ['database' | database], 'user', 'password')

• 表引擎

  数据库引擎通常默认就够了，表引擎才是重点，ClickHouse 在建表时必须指定表引擎，它决定了表的存储方式，引擎的名称大小写敏感。

  表引擎	描述
  TinyLog	生产不会用；以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，主要方便学习测试用
  Memory	使用场景较少；内存引擎，数据以未压缩的原始形式直接保存在内存当中，适合数据量不大又需要非常高性能的场景
  MergeTree	ClickHouse 中最强大的表引擎，支持索引和分区

MergeTree

示例如下：

-- 建表并指定表引擎
create table t_user
(
    id          UInt32,
    area_id     String,
    user_name   String,
    height      Decimal(7, 2)  TTL create_time + interval 10 second,  -- 列级别生命周期，详见参数说明
    create_time Datetime,
    -- 二级索引，在索引的基础上进一步细分；5 表示分为 5 份，minmax 表示记录最大值和最小值
    -- 在查询时，二级索引的范围能更快定位到数据的位置
    index my_index area_id type minmax granularity 5
) engine = MergeTree
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, area_id);
-- 插入数据
insert into t_user values (1, '10086', 'zs', 180.00, '2024-11-01 12:00:00');

MergeTree 参数说明：

• partition by （可选）

  根据指定的分区算法对数据分区，能提高查询速度。如果不填，数据只会存入一个分区。设置分区后，数据的排序、汇总等操作只会在自己所在的分区中进行，不同分区互不影响。

• primary key（可选）

  和和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。主键通常建议使用整形，因为 MergeTree 会对其进行进行某种形式的二分查找。

• order by（必须）

  order by 设定了各个分区的数据按照哪些字段排序，他是必须的，当用户不设置主键的情况下， order by 的字段是各项处理的首选，也被作为索引，因为有序数据能极大的提升查询速度。

• TTL（可选）

  TTL 即 Time To Live，管理数据表或者列的生命周期。TTL 后面必须传递日期。日期单位有 second、minute、hour、day、week、month、quarter、year;

  示例中为列生命周期，当前时间若到达给定时间，这列的所有数据项的值都会变为默认值（即示例中所有的 height 数据值都会重置为 0.00）！

  若插入数据后查询失败可能是合并树还没有真正将数据合并到主目录，需要手动合并：

  sql > optimize table t_user final; -- 手动合并，

ReplacingMergeTree

ReplacingMergeTree 完全继承 MergeTree，只是多了一个去重的功能。

数据的去重只会在合并的过程中出现，合并时机无法预知，在此期间可能会查询到不合理数据。有分区的情况下，只会在各个分区中去重，不能跨区去重。

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

-- 建表并指定表引擎
create table t_user_rmt
(
    id          UInt32,
    area_id     String,
    user_name   String,
    height      Decimal(7, 2),
    create_time Datetime
) engine = ReplacingMergeTree(create_time) -- 填入的参数为版本号，重复项保留版本字段最大值；如果不填版本字段，默认按照插入顺序保留最后一条
    primary key (id)
    -- order by 既用于排序，又可作为去重维度；
    -- 这里表示当 id 和 area_id相同时，他们为重复数据；
    order by (id, area_id);

-- 插入数据
insert into t_user_rmt values (1, '10086', 'zs', 180.00, '2024-11-01 12:00:00');
insert into t_user_rmt values (1, '10086', 'zs', 190.00, '2024-11-01 13:00:00');
insert into t_user_rmt values (2, '10086', 'zs', 180.00, '2024-11-02 12:00:00');
-- 查询数据
select * from t_user_rmt;
-- 手动合并，然后再次查询，观察数据变化
optimize table t_user_rmt final;

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。若使用 MergeTree 汇总，在存储空间、临时聚和的开销比较大。

-- 建表并指定表引擎
create table t_user_smt
(
    id           UInt32,
    area_id      String,
    user_name    String,
    total_amount Decimal(16,2),
    height       Decimal(7, 2),
    create_time  Datetime
) engine = SummingMergeTree(total_amount) -- 汇总合并数的参数必须是数值列，可以指定多个列
    primary key (id)
    -- order by 既用于排序，又可作为聚集维度；
    -- 这里表示当 id 和 area_id相同时，他们被聚和到一起；
    order by (id, area_id);

-- 插入数据
insert into t_user_smt values (1, '10086', 'zs', 100.00, 180.00, '2024-11-01 12:00:00');
insert into t_user_smt values (2, '10086', 'ls', 150.00, 180.00, '2024-11-01 12:00:00');
insert into t_user_smt values (2, '10086', 'ls', 180.00, 180.00, '2024-11-01 13:00:00');
insert into t_user_smt values (3, '10086', 'ww', 200.00, 180.00, '2024-11-02 12:00:00');
-- 查询数据
select * from t_user_smt;
-- 手动合并，然后再次查询，观察数据变化
optimize table t_user_smt final;

SQL 操作

ClickHouse 不擅长数据的更新和删除操作，其他操作和标准 SQL 非常相似；虽然提供了更新和修改操作，但是会建新分区并放弃原有分区，建议批量修改或更新，数据量太小会频繁操作分区，得不偿失。

可使用ClickHouse 内置函数操作数据。

对于分组查询，ClickHouse 支持多维度的小计和总计。

查询

-- 查询结果由 3 部分组成：根据 id + area_id 分组汇总、根据 id 分组汇总、直接汇总总额；
-- 相当于【从右至左】去掉维度分别进行小计，n 个维度，则结果集为 n + 1 个部分
select id, area_id, sum(total_amount) from t_user_smt group by id, area_id with rollup;
-- 先根据 id + area_id 分组汇总，再根据 id 分组汇总，然后根据 area_id 分组汇总，最后直接汇总总额；
-- 相当于根据维度的【所有子集】分别小计，n 个维度，则结果集为 2^n 部分
select id, area_id, sum(total_amount) from t_user_smt group by id, area_id with cube;
-- 根据维度分组，然后计算总额，不会单独进行小计，结果集只包含2个部分：分组部分、总额部分
select id, area_id, sum(total_amount) from t_user_smt group by id, area_id with totals;

修改

alter table t_user_smt update total_amount = toDecimal32(350.00,2) where id = 1;

删除

alter table t_user_smt  delete where id = 2;

ddl

-- 新增字段
alter table tableName add column newcolname String after col1;
-- 修改字段类型
alter table tableName modify column newcolname String;
-- 删除字段
alter table tableName drop column newcolname;

建表优化

1. 时间类型：建议 ClickHouse 中时间字段不要用字符串；
2. 空值类型：空字段值会影响性能，建议指定一个无意义的默认值表示空（如 -1 表示没有 id）；
3. 分区：一般按天分区，也可以不分（数据量不大），区中数据量在一千万左右；
4. 索引列（order by）：基数特别大的不适合做索引列（如用户 id），查询频率越大越靠前；
5. 表参数：对于不必保留全量数据的，建议指定 TTL（生存时间值）；
6. 写入和删除：尽量不要执行单条或小批量删除和插入操作；建议每次操作写入 2w~5w 条数据，每秒写入 2~3 次；