列式数据库ClickHouse入门

简介

ClickHouse一款用于联机分析(OLAP)的列式数据库，由俄罗斯搜索引擎公司开发并开源。

OLAP场景关键特征

大多数是读请求
数据大批量(>1000 rows)写入
不修改已添加的数据
每次查询都从数据库中读取大量的行，但只需少量列
宽表，每个表包含大量列
较少的查询
事务不少必须的
对数据一致性要求低
但查询高吞吐量（每个服务器每秒高达数10亿行）
每一个查询除了一个大表外都很小
查询结果明显小于源数据

架构

核心特性

完备的DBMS功能

使用关系模型描述数据并提供了传统数据库的基本功能。
支持DDL、DML、权限控制、数据备份和恢复、分布式管理

列式存储和数据压缩

为了让查询更快，可以减少数据扫描范围和数据传输时的大小
ClickHouse采用列式存储结构，
对于一个user表来说，会有很多自动，但只查询其中2个字段时select id,name from user，mysql来说扫描全表，而对列式存储来说，只会读取这二列数据，有效的减少了数据扫描范围，提高了IO效率
压缩算法的本质是对重复数据进行编码转换，从而减少数据长度，ClickHouse默认使用LZ4压缩算法

向量化执行引擎

向量化执行，需要使用CPU的SIMD(Single Instruction Multiple Data)指令，单条指令操作多条数据，通过数据并行提高性能的一种方式，它的原理是在CPU寄存器层面实现数据的并行操作。

多样化的表引擎

合并树	日志	集成	特别
MergeTree	Tinylog	Kafka	Distributed
SummingMergeTree	StripeLog	Mysql	Dictionary
AggregatingMergeTree	Log	JDBC	Merge
CollapsingMergeTree	ODBC	ODBC	File
VersionCollapsingMergeTree		HDFS	Set
GragphieMergeTree			Join
			URL
			View
			MaterializedView
			Memory
			Buffer

分布式和多线程

向量化执行通过数据级并行的方式提升性能
多线程处理通过线程级并行的方式提升性能
支持分区-纵向扩展，利用多线程
支持分片-横行扩展，利用分布式原理

多主架构

Multi-Master多主架构，集群中各个节点角色对等，客户端访问任何一个节点都可以得到相同的效果。

Log表引擎

Log用于快读写入小表（1百万行），然后全部读出的场景

不支持分区、索引
不支持delete、update
数据顺序append磁盘
不支持原子性写
insert会阻塞select

性能排序：

tinylog:性能最低，不支持并发读，查询性能差，适合暂存中间数据
striplog:支持并发读，所有列存在一个文件中
log:支持并发读，每个列单独存文件

特殊表引擎

memory

跟mysql内存表类型，数据存在内存中，重启数据会丢失

buffer

满足指定条件将会flush到磁盘
Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

create table test.buffer_to_memory_1 as test.memory_1 engine = Buffer(test, memory_1, 16, 10 ,100, 1000, 1000000, 10000000, 100000000);
插入到buffer表中的数据可能以不同的顺序和不同的块写入目标表中
满足所有min_，或者任一个max_则会将数据刷到memory_1表中

file

直接存在文件中

合并树表引擎

MergeTree

支持分区
存储有序
主键索引
数据ttl
稀疏索引

主键数据可以重复

create table test_1( id UInt16, create_time Date )ENGINE=MergeTree() PARTITION BY toYYYYMMDD(create_time) ORDER BY (id,create_time) PRIMARY KEY (id,create_time) TTL create_time + INTERVAL 1 MONTH

PRIMARY KEY 没有定义的话使用ORDER BY字段作为主键
toYYYYMMDD(create_time) 按天分区数据
TTL create_time + INTERVAL 1 MONTH 数据保存一个月

ReplacingMergeTree

为了解决MergeTree相同主键无法去重的问题，引入了ReplacingMergeTree
但也需要强制optimize
optimize table test_re
存在的问题

optimize是后台动作，无法满足业务及时查询需求
当分片时，数据在不同分片，无法实现去重功能

更多被用于确保数据最终去重

CollapingMergeTree

进一步完善ReplacingMergeTree，增加字段Sign
create table test_2( id UInt16, ViewNum UInt16, create_time Date, Sign Int8 )ENGINE=CollapingMergeTree(Sign) GROUP BY ID;
Sign=1数据有效，Sign=-1数据被删除

新增数据：INSERT INTO TEST_2(...,1)
删除数据：INSERT INTO TEST_2(...,-1)

在多线程写入的情况下，-1的记录先被写入，会导致无法正常折叠

需要改写sql
SELECT id,sum(ViewNum * Sign) FROM test_2 GROUP BY id HAVING sum(Sign)>0;

VersionedCollapsingMergeTree

为了解决CollapingMergeTree乱序写入无法正常折叠问题
新增一列Version版本号
主键相同、Version相同、Sign相反的行，在Compaction时会被删除
create table test_3( id UInt16, ViewNum UInt16, create_time Date, Sign Int8, Version UInt8 )ENGINE=VersionedCollapsingMergeTree(Sign,Version) GROUP BY ID;

同样需要改写sql
SELECT id,sum(ViewNum * Sign) FROM test_3 GROUP BY id HAVING sum(Sign)>0;

SummingMergeTree

可以对主键列进行预聚合，在后台合并时，会将主键相同的行进行sum求和
降低了磁盘存储空间，加速数据查询

只有在后台合并时才会聚合数据，所以查询sql还是要带GROUP BY
预聚合时，会对除主键列进行sum求和，如果列不是数值型，则会随机选一行中的值

AggregatingMergeTree

也是预聚合引擎的一种
SummingMergeTree对非主键列进行sum聚合
AggregatingMergeTree则可以指定各种聚合函数

配合物化视图使用

-- 创建明细表
CREATE TABLE test.visits(
    UserID UInt64,
    CounterID UInt8,
    StartDate Date,
    Sign Int8
)ENGINE CollapsingMergeTree(Sign)
ORDER BY UserID;

-- 创建物化视图
CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate)
AS SELECT
   CounterID,
   StartDate,
   sumState(Sign) AS Visits,
   uniqState(UserID) AS Users
FROM test.visits GROUP BY CounterID, StartDate;

-- 插入数据到明显表中
INSERT INTO test.visits VALUES (1, 1, '2020-08-11', 1), (2, 11, '2020-08-11', 1), (3, 12, '2020-08-11', 1);

-- 查询物化视图
SELECT
   StartDate,
   sumMerge(Visits) as Visits,
   uniqMerge(Users) as Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;