ClickHouse使用总结

ClickHouse使用总结

简介

ClickHouse是俄罗斯最大的搜素引擎Yandex于2016年开源的列式数据库管理系统，使用C++ 语言编写, 主要应用于OLAP场景。 国内使用的公司有：阿里云，腾讯，微博，虎牙等。

相关链接:

• 什么是ClickHouse?
• 什么是OLAP?
• ClickHouse的用户

使用理由

在大数据量的情况下，能以很低的延迟返回查询结果。

笔者注： 在单机亿级数据量的场景下可以达到毫秒级的查询性能，单机能处理百亿的数据量， 聚合、计数、求和等统计操作的性能是MySQL的100倍。

相关链接:

• ClickHouse的性能

主要特点

1. 列式储存
2. 数据压缩
3. 支持大部分标准sql
4. 支持分区（类似分表）
5. 使用稀疏索引作为索引的主要实现
6. 支持分布式
7. 可以在牺牲精度的前提下，快速的返回一个近似结果

缺点：

1. 没有完整的事务支持。
2. 缺少高频率，低延迟的修改或删除已存在数据的能力。
3. 稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。
4. 没有自增类型

使用总结

-- 班级表
CREATE TABLE room
(
    `id` UInt8,
    `name` String
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192;

-- 学生表
CREATE TABLE student
(
    `id` UInt64,
    `create_time` DateTime64(3),
    `name` String,
    `room_id` UInt8,
    INDEX create_time_idx create_time TYPE minmax GRANULARITY 5
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
ORDER BY id
SETTINGS index_granularity = 8192;

合理设置分区

1. 使用MySQL时，分表是应对大数据量的常用方案，因为Innodb引擎在单表数据达500W后性能急剧下降。
2. clickhouse的性能是随着数据量线性下降，没有MySQL的问题，分区是为了降低查询时需要扫描的数据量，在查询时更精准的找到数据。
3. 分区时设置的规则/字段，最好和where/order by/group by相关。
4. 分区不是分的越多越好，在分区列表中查找分区，最大时间复杂度是n。
5. 每个分区实际上是一个目录，结构如下所示，目录名根据建表指定的规则生成

# student每个分区的目录结构
student/  # 表名
├── 20220101  # 分区名，2022-01-01的YYYYMMDD格式
│   ├── id.bin # 数据文件
│   ├── id.mrk3 # 标记文件，存储了主键和字段列的映射
│   ├── create_time.bin
│   ├── create_time.mrk3
│   ├── name.bin
│   ├── name.mrk3
│   ├── room_id.bin
│   ├── room_id.mrk3
│   ├── minmax_create_time.idx # 索引文件
│   ├── primary.idx # 主键索引文件
│   ├── skp_idx_create_time_idx.idx2 # 跳数索引文件
│   └── skp_idx_create_time_idx.mrk3 # 跳数索引和主键的映射
└── 20220102

合理设置索引

1. 索引会占额外的物理空间，数据变化时索引也要变化，降低了增删改的性能
2. 适合索引的列时出现在where语句中的列，或者关联表的列
3. order by、group by经常的字段需要建立索引
4. 基数较小的类，索引效果差，没必要建立索引
5. 不要给表中的每一个字段都设置索引，不是越多越好
6. 创建联合索引时，字段不要太多，最多5个
7. 联合索引一定要考虑最左前缀匹配原则
8. 大文本，大对象不要建立索引
9. 在选择索引列的时候，可以指定某些列的一部分，没必要用字段的全值

合理设置字段类型

-- 
select * from student where unit_id = 1;

上面的语句，字段unit_id的类型为UInt16时，查询时间比UInt8多一倍。

合理使用子查询

-- 案例一
select * from student limit 100000000,10;

-- 案例二（更快）
select * 
from student
where id >= (select id from student limit 100000000,1) 
limit 10;

合理使用近似计算

-- 结果准确但是，耗时久
select count(`id`) from student;
-- 结果不精确，但是耗时短，
select uniq(count(`id`)) from student;

Count()函数的参数中指定字段名

-- 案例一
select count() from student where group_id=10;

-- 案例二（更快）
select count('group_id') from student where group_id=10;

踩坑记录

1. 分区字段，在表创建后不能更改
2. 数据量达50亿后，表不能直接删除，需要更改配置文件
3. 突然断电重启, 容易导致clickhouse不能正常启动，需要更改配置文件
4. 数据一致性问题，如：高频率插入数据的情况下，连续执行两次相同的查询，结果会不一样
5. JOIN 查询尤其慢，最好考虑用多子查询，In查询会快很多。