应用场景：
- 绝大多数请求都是用于读访问的
- 数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
- 数据只是添加到数据库，没有必要修改
- 读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
- 表很“宽”，即表中包含大量的列
- 查询频率相对较低（通常每台服务器每秒查询数百次或更少）
- 对于简单查询，允许大约50毫秒的延迟
- 列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
- 在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
- 不需要事务
- 数据一致性要求较低
- 每次查询中只会查询一个大表。除了一个大表，其余都是小表
- 查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小
ClickHouse本身的限制：
- 不支持真正的删除/更新支持 不支持事务（期待后续版本支持）
- 不支持二级索引
- 有限的SQL支持，join实现与众不同
- 不支持窗口功能
- 元数据管理需要人工干预维护
ClickHouse有多快
ClickHouse底层设计
数据表引擎
表引擎（即表的类型）决定了：
- 数据的存储方式和位置，写到哪里以及从哪里读取数据
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用（如果存在）。
- 是否可以执行多线程请求。
- 数据复制参数。
MergeTree
- MergeTree
- ReplacingMergeTree
- SummingMergeTree
- AggregatingMergeTree
- CollapsingMergeTree
- VersionedCollapsingMergeTree
- GraphiteMergeTree
最小功能的轻量级引擎
- TinyLog
- StripeLog
- Log
与其他的数据存储与处理系统集成的引擎
- Kafka
- MySQL
- HDFS
其他特定功能的引擎
- Distributed
- MaterializedView
- Dictionary
- Merge
- File
- Null
- Set
- Join
- URL
- View
- Memory
- Buffer
clickhouse为什么如此快
- 优秀的执行引擎以及存储引擎，clickhouse是基于列式存储的，使用了向量化的执行引擎
- 稀疏索引，clickhouse不仅有基于分区的过滤，还有基于列级别的稀疏索引，这样在进行条件查询的时候可以过滤到很多不需要扫描的块
- 存储执行耦合，避免了网络的开销，CPU的极致压榨加上SSD的加持，每秒的数据传输对于网络带宽的压力是非常大的
clickhouse如何完成一次完整的select
- clickhouse的表分为两种，本地表和分布式表
- 分布式表是一个逻辑上的表，不存储数据的只是做一个路由使用，一般在查询的时候都是直接使用分布式表，分布式表引擎会将我们的查询请求路由本地表进行查询，然后进行汇总最终返回给用户
clickhouse如何完成一次插入
- clickhouse的插入是批量的，它不能够像传统的mysql那样频繁的单条记录插入，没有内存表，不进行日志的记录。
- clickhouse写入的时候是直接落盘的, 在落盘之前会对数据进行排序以及必要的拆分(如不同分区的数据会拆分成多个文件夹)，如果使用的是ReplicatedMergeTree引擎还需要与zookeeper进行交互，最终会有线程在后台把数据(文件夹)进行合并(merge)，将小文件夹合并生成大文件夹方便查询的时候进行读取(小文件会影响查询性能)。
clickhouse支持分区的过滤和列级别的稀疏索引
- 传统基于hdfs的olap引擎都是不支持索引的，基本的数据过滤只能支持分区进行过滤，这样会扫描处理很多不必要的数据。
- clickhouse的基础索引是使用了和kafka一样的稀疏索引，索引粒度默认是8192，即每8192条数据进行一次记录，这样对于1亿的数据只需要记录12207条记录，二分查找+遍历也可以快速的索引到指定的数据
常见的引擎(MergeTree家族)
- (Replicated)MergeTree：该引擎为最简单的引擎，存储最原始数据不做任何的预计算，任何在该引擎上的select语句都是在原始数据上进行操作的，常规场景使用最为广泛，其他引擎都是该引擎的一个变种。
- (Replicated)SummingMergeTree：该引擎拥有“预计算(加法)”的功能。实现原理：在merge阶段把数据加起来(对于需要加的列需要在建表的时候进行指定)，对于不可加的列，会取一个最先出现的值。
- (Replicated)ReplacingMergeTree：该引擎拥有“处理重复数据”的功能。使用场景：“最新值”，“实时数据”。
- (Replicated)AggregatingMergeTree：该引擎拥有“预聚合”的功能。使用场景：配合”物化视图”来一起使用，拥有毫秒级计算UV和PV的能力。
- (Replicated)CollapsingMergeTree：该引擎和ReplacingMergeTree的功能有点类似，就是通过一个sign位去除重复数据的。需要注意的是，上述所有拥有"预聚合"能力的引擎都在"Merge"过程中实现的，所以在表上进行查询的时候SQL是需要进行特殊处理的。如SummingMergeTree引擎需要自己sum(), ReplacingMergeTree引擎需要使用时间+版本进行order by + limit来取到最新的值，由于数据做了预处理，数据量已经减少了很多，所以查询速度相对会快非常多。
- 推荐使用(*)MergeTree引擎，该引擎是clickhouse最核心的组件，也是社区优化的重点
最佳实践
- 实时写入使用本地表，不要使用分布式表
数据导入
order by
order by是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理
MergeTree 提供了可以管理数据表或者列的生命周期的功能
- 涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段
- 给列的加上生命周期到时间后会自动清除数据
- 表的生命周期
ReplacingMergeTree
- 和 MergeTree 的不同之处在于它会删除排序键值相同的重复项
- 去重只会在分区内部进行去重，不能执行跨分区的去重。
- 数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，但是它不保证没有重复的数据出现。
- 保存的数据为版本字段值最大的,如果版本字段相同则按插入顺序保留最后一个。
SummingMergeTree
- 合并 SummingMergeTree 表的数据片段时，会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。
- columns 包含了将要被汇总的列的列名的元组。可选参数。所选的列必须是数值类型，并且不可位于主键中。
- 不再一个分区里的数据不会聚合
- 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
MySQL和ClickHouse中数据类型的对应关系
| MySQL | ClickHouse |
|---|---|
| UNSIGNED TINYINT | UInt8 |
| TINYINT | Int8 |
| UNSIGNED SMALLINT | UInt16 |
| SMALLINT | Int16 |
| UNSIGNED INT, UNSIGNED MEDIUMINT | UInt32 |
| INT, MEDIUMINT | Int32 |
| UNSIGNED BIGINT | UInt64 |
| BIGINT | Int64 |
| FLOAT | Float32 |
| DOUBLE | Float64 |
| DATE | Date |
| DATETIME, TIMESTAMP | DateTime |
| BINARY | FixedString |
数据类型
- 整型：UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64 范围U开头-2N/2~2N-1;非U开头0～2^N-1
- 枚举类型：Enum8,Enum16 Enum(‘hello’=1,‘test’=-1),Enum是有符号的整型映射的，因此负数也是可以的
- 字符串型：FixedString(N),String N是最大字节数，不是字符长度，如果是UTF8字符串，那么就会占3个字节，GBK会占2字节;String可以用来替换VARCHAR,BLOB,CLOB等数据类型
- 时间类型：Date
- 数组类型：Array(T) T是一个基本类型，包括arry在内，官方不建议使用多维数组
- 元组：Tuple
- 结构：Nested(name1 Type1,name2 Type2,…) 类似一种map的结
物化列
DROP TABLE IF EXISTS test.m2;
CREATE TABLE test.m2 ( a MATERIALIZED ( b + 1 ), b UInt16 ) ENGINE = Memory;
INSERT INTO test.m2 ( b ) VALUES( 1 );
SELECT * FROM test.m2;
SELECT a,b FROM test.m2;
语法优化 EXPLAIN SYNTAX
EXPLAIN SYNTAX SELECT UserID FROM hits_test GROUP BY UserID HAVING UserID =
'8585742290196126178';
数据采样
SELECT Title,count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 100;
避免构建虚拟列
尽量别用as创建一列新列,例如 select a/b as t from test
物化视图
- 物化视图是把查询的结果和相应的引擎存入到磁盘或内存中,类似形成了一张新的表,但是可以对表中数据进行重新组织.
- 使用物化视图好处，可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能
CREATE MATERIALIZED VIEW hits_mv
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID))
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2022-03-20'
GROUP BY UserID,EventDate;
ClickHouse实时同步MySQL数据的两种方式
-
使用JDBC方式同步
- 使用Canal组件完成binlog的解析和数据同步；
- Canal-Server进程会伪装成MySQL的slave，使用MySQL的binlog同步协议完成数据同步；
- Canal-Adapter进程负责从canal-server获取解析后的binlog，并且通过jdbc接口写入到ClickHouse； 优点：Canal组件原生支持；缺点：Canal-Adpater写入时源表和目的表一一对应，灵活性不足；需要维护两个Canal组件进程；
-
Kafka+ClickHouse物化视图方式同步
- Canal-Server完成binlog的解析，并且将解析后的json写入Kafka；
- Canal-Server可以根据正则表达式过滤数据库和表名，并且根据规则写入Kafka的topic；
- ClickHouse使用KafkaEngine和Materialized View完成消息消费，并写入本地表； 需要维护Kafka和配置规则；ClickHouse需要新建相关的视图、Kafka Engine的外表等；
参考地址