ClickHouse笔记

列式存储数据库（DBMS），使用 C++
语言编写，主要用于在线分析处理查询（OLAP）

ClickHouse 的特点

 1.列式存储
 2.DBMS 的功能几乎覆盖了标准 SQL 的大部分语法，包括 DDL 和 DML，以及配套的各种函数，用户管

理及权限管理，数据的备份与恢复
3.多样化引擎：合并树、日志、接口和其他四大类 20 多种引擎。
4.高吞吐写入能力
类 LSM Tree的结构，定期在后台 Compaction，顺序 append 写，50MB-200MB/s 的写入吞吐能力，按照每行
100Byte 估算，大约相当于 50W-200W 条/s 的写入速度
5.数据分区与线程级并行
partition 再进一步划分为多个 index granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理单条 Query 就能利用整机所有 CPU

数据类型
1.整型范围（-2（n-1）次方_{2（n-1）-1），无符号整型范围（0}2（n-1）次方）
2.浮点型
3.布尔型：没有单独的类型来存储布尔值。UInt8 类型，取值限制为 0 或 1
4.Decimal 型
5.字符串
String,FixedString(N)固定长度 N 的字符串
6.枚举类型
Enum8 和 Enum16 对应 Int8和 Int16
7.时间类型
Date 接受年-月-日、Datetime 接受年-月-日 时:分:秒、Datetime64 接受年-月-日 时:分:秒.亚秒
8.数组

表引擎
注意：引擎的名称大小写敏感

1.TinyLog：列文件的形式保存在磁盘上，不支持索引，没有并发控制，是和少量数据，一般可用于平时测试
2.Memory：未压缩的原始形式直接保存在内存当中，不支持索引，简单查询性能高（超过10G/s））,上限大概 1 亿行）
3.MergeTree
中最强大的表引擎,以及该系列，支持索引和分区
3.1：partition by 分区(可选) 如果不填只使用一个分区
列文件+索引文件+表定义文件组成的
以分区为单位并行处理
数据先写入临时分区，大概10-15分钟后，自动执行合并（optimize 手动方式）将临时分区的数据合并到已有的分区
3.2primary key 主键(可选)
只提供了数据的一级索引，可以存在相同 primary key 的数据
where条件中使用主键，使用二分查找定位到对应的index granularity，避免全部扫描
index granularity(索引粒度)，默认8192，除非存在大量重复值，否则不建议修改
3.3 order by（必选）
分区内的数据按照哪些字段顺序进行有序保存
主键必须是 order by 字段的前缀字段
3.4二级索引
GRANULARITY N 是设定二级索引对于一级索引粒度的粒度
3.5 数据 TTL
管理数据表或者列的生命周期的功能

ReplacingMergeTree

存储特性完全继承 MergeTree，只是多了一个去重的功能，去重只保证最终去重，但是不保证没有重复数据的出现
去重时机：数据的去重只会在合并的过程中出现
去重范围：分区内部进行去重
去重保留规则：重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。
结论：
	实际上是使用 order by 字段作为唯一键
	去重不能跨分区
	只有同一批插入（新版本）或合并分区时才会进行去重
	认定重复的数据保留，版本字段值最大的
	如果版本字段相同则按插入顺序保留最后一笔

SummingMergeTree

适合场景：不查询明细，只关心以维度进行汇总聚合结果的场景，预聚合”的引擎
总结：
以 SummingMergeTree（）中指定的列作为汇总数据列 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
以 order by 的列为准，作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合
只有在同一批次插入(新版本)或分片合并时才会进行聚合

SQL 操作
mysql支持的sql，ck基本都支持
1.insert
2.Update 和 Delete
Mutation 语句是一种很“重”的操作，而且不支持事务。每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区
3.查询操作
ClickHouse 基本上与标准 SQL 差别不大
4.alter
副本
1.副本写入流程
client写入数据到ck-a,
提交写入日志-zk-cluster
收到写入日志到ck-b
ck-b到目标副本下载新数据

 注意：副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表
 建表语句中的 ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_103')
 第一个参数是分片的 zk_path 一般按照：/clickhouse/table/{shard}/{table_name}，第二个参数是副本名称，相同的分片副本名称不能相同

分片集群
1. 分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用。
Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据

Distributed（集群名称，库名，本地表名，分片键）
分片键必须是整型数字，所以用 hiveHash 函数转换，也可以 rand()

注意：ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分
片，避免降低查询性能以及操作集群的复杂性

2. 集群读取流程
客户端发送读取命令：有限选择errors_count小的副本，errors_count 相同的有随机、顺序、随机（优先第一顺位）、host名称近似等四种选择方式

Explain 查看执行计划
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, …]
SELECT … [FORMAT …]

建表优化

1.时间字段的类型：DateTime 不需要经过函数转换处理，执行效率高、可读性好
2.空值存储类型：接使用字段默认值表示空，或者自行指定一个在业务中无意义的值，不使用Nullable
3.分区和索引：
	按天分区以单表一亿数据为例，分区大小控制在 10-30 个为最佳
	必须指定索引列，ClickHouse 中的索引列即排序列，高级列在前、查询频率大的在前原则，常筛选后的数据满足在百万以内为最佳
4.表参数：建议指定 TTL
5. 写入和删除优化
	尽量不要执行单条或小批量删除和插入操作
	不要一次写入太多分区，或数据写入太快每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w

常见配置

CPU
background_pool_size：允许的前提下建议改成 cpu 个数的 2 倍（线程数），merge线程在该池中使用
background_schedule_pool_size：执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默 认 128，建议改成 cpu 个数的 2 倍（线程数）
background_distributed_schedule_pool_size：分布式发送执行后台任务的线程数，默认 16，建议改成 cpu个数的 2 倍（线程数）
max_concurrent_queries：最大并发处理的请求数(包含 select,insert 等)，默认值 100，推荐 150(不够再加)~300。
max_threads：设置单个查询所能使用的最大 cpu 个数，默认是 cpu 核数

内存
max_memory_usage：表示单次 Query 占用内存最大值
max_bytes_before_external_group_by： max_memory_usage 的一半设置内存
max_table_size_to_drop：建议修改为 0，这样不管多大的分区表都可以删除。否则超过默认值50G的分区删除时报错

存储
ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组

ClickHouse 语法优化规则：SQL 优化规则是基于 RBO(Rule Based Optimization)，

1.在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows，
2.消除子查询重复字段
3.谓词下推
4.聚合计算外推： sum(UserID * 2)-》 sum(UserID) * 2
5.聚合函数消除
6.删除重复的 order by key
7.删除重复的 limit by key
8.删除重复的 USING Key
9.标量替换
10.三元运算优化：optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf

查询优化

1.单表查询
    Prewhere（只支持mergeTree系列） 替代 where，当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能
2.数据采样
	采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略
3.列裁剪与分区裁剪
4.orderby 结合 where、limit
5.避免构建虚拟列
6. uniqCombined 替代 distinct：接受 2%左右的数据误差
7.使用物化视图
8。其他注意事项
	查询熔断：避免因个别慢查询引起的服务雪崩的问题
	关闭虚拟内存
	配置 join_use_nulls，leftjoin 右表不存在返回该类型的默认值
	批量写入时先排序
	关注 CPU

多表关联

1.用 IN 代替 JOIN：查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN
2.大小表 JOIN：小表在右的原则
3.分布式表使用 GLOBAL：
	两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求

的那个节点查询一次，并将其分发到其他节点上，否则就会查询放大，N2次方（N为分布式表的分片数量）
4.使用字典表
5.提前过滤

数据一致性

即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性：
解决方案：
1.手动 OPTIMIZE
2.通过 Group by 去重
3. 通过 FINAL 查询：FINAL 查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最

终的查询时间，所以还要结合实际场景取舍

物化视图

普通视图不保存数据，保存的仅仅是查询语句，物化视图则是把查询的结果根据相应的引擎存入到了磁盘

或内存中

优点：快
缺点：场景有限，一张表物化视图多，耗费资源

基本语法
  也是 create 语法，会创建一个隐藏的目标表来保存视图数据。也可以 TO 表名，保存到

一张显式的表。没有加 TO 表名，表名默认就是 .inner.物化视图名
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT …

使用限制

  1.必须指定物化视图的 engine 用于数据存储
  2.TO [db].[table]语法的时候，不得使用 POPULATE。
  3.查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
  4.物化视图的 alter 操作有些限制，操作起来不大方便。
  5.若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图 卸载DETACH 再装载 ATTACH

物化视图的数据更新

  （1）物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
  （2）POPULATE 关键字决定了物化视图的更新策略
  （3）物化视图不支持同步删除
  （4）物化视图是一种特殊的数据表，可以用 show tables 查看

MaterializeMySQL 引擎
该 database 能映 射 到 MySQL 中 的 某 个 database ， 并 自 动 在 ClickHouse 中 创 建 对 应 的
ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请
求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能

 特点
	1.MaterializeMySQL 同时支持全量和增量同步
	2.为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段。