ClickHouse v21.7
2021-08-11 01:26:32 5 举报AI智能生成
v21.7版本clickhouse学习导图 参考官网、尚硅谷资料
作者其他创作
大纲/内容
俄罗斯开源、C++编写、面向OLAP、能使用SQL实时查询的列式存储数据库
本文大体上基于20210730左右版本整理
简介
对于列的聚合，计数，求和等统计操作原因优于行式存储。
格式相同，便于压缩
列式存储
支持大部分标准SQL
支持DBMS功能
不同表按需设置存储引擎
多样化存储引擎
采用类似LSM Tree，append写入，定期合并
高吞吐能力
partition进一步划分多个index granularity（索引粒度）
多个cpu分别处理其中一部分
数据划分多个partition
单个Query就可以使用所有cpu，极致执行效率
优点
不利于qps高的查询，即不适于并行查询
缺点
数据分区与线程并行
单表查询快于多表查询
性能
特点
子主题
关键目录（默认）
安装
固定长度的整型，包括有符号整型或无符号整型。整型范围（-2n-1~2n-1-1）：Int8 - [-128 : 127]Int16 - [-32768 : 32767]Int32 - [-2147483648 : 2147483647]Int64 - [-9223372036854775808 : 9223372036854775807]无符号整型范围（0~2n-1）：UInt8 - [0 : 255]UInt16 - [0 : 65535]UInt32 - [0 : 4294967295]UInt64 - [0 : 18446744073709551615]使用场景： 个数、数量、也可以存储型 id。
整型
Float32 - floatFloat64 – double建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。
浮点型
没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。
布尔型
Decimal 型
1）String字符串可以任意长度的。它可以包含任意的字节集，包含空字节。2）FixedString(N)固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。
字符串
包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。Enum8 用 'String'= Int8 对描述。Enum16 用 'String'= Int16 对描述。
枚举类型
目前 ClickHouse 有三种时间类型Date 接受年-月-日的字符串比如 ‘2019-12-16’ Datetime 接受年-月-日 时:分:秒的字符串比如 ‘2019-12-16 20:50:10’ Datetime64 接受年-月-日 时:分:秒.亚秒的字符串比如‘2019-12-16 20:50:10.66’日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
时间类型
Array(T)：由 T 类型元素组成的数组。T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在 MergeTree 表中存储多维数组。
数组
数据类型
数据存储的方式和位置
支持哪些查询以及如何支持
是否支持并发访问
索引的使用
是否支持多线程
数据复制的参数
表引擎决定表的存储方式
以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。
TinyLog
内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。
读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过 10G/s）。
Memory
最强大的表引擎
支持索引和分区，地位可以相当于 innodb 之于 Mysql
是降低扫描的范围，优化查询速度，不填只有一个分区
分区目录：MergeTree 是以列文件+索引文件+表定义文件组成的在设置分区后这些文件会保存到不同的分区目录中
并行：对于跨分区的统计，ClickHouse以分区为单位并行处理
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。
写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中
数据写入与分区合并
partition by 分区(可选)
提供了数据的一级索引，但是却不是唯一约束。即可以存在相同 primary key
设定的主要依据是查询的where条件，定位到对应的 index granularity，避免全表扫描
primary key 主键(可选)
设定了分区内的数据按照哪些字段顺序进行有序保存。
MergeTree 中唯一一个必填项，不设置主键的情况，很多处理会依照 order by 的字段进行处理(比如去重，汇总)
order by（必选）
三个关键参数
20.1.2.4 之前是被标注为实验性的，后续版本默认开启
其中 GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。
二级索引
TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能。
列级别 TTL
表级别 TTL
级别
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
支持的周期
涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。
注意
数据 TTL
其他参数
MergeTree/*MergeTree
ReplacingMergeTree 是 MergeTree 的一个变种，存储特性继承 MergeTree，具有去重的功能。
可以借助ReplacingMergeTree去除重复数据
概览
的去重只会在合并的过程中出现，（新版本在插入过程中会先对插入部分去重）
合并会在未知的时间在后台进行，无法预先作出计划。有一些数据可能仍未被处理。
去重的时机
分区内去重，不能执跨分区去重。
order by 字段作为唯一键去重
去重范围
ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
适用范围
ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。（版本一般为时间字段）
如果不填版本字段，默认按照插入顺序保留最后一条。
保留字段
ReplacingMergeTree
一种能够“预聚合”的引擎
不查询明细，只关心以维度进行汇总聚合结果的场景。
相对普通的MergeTree节省存储空间，提高查询效率
适用场景
在同一批次插入(新版本)或分片合并时才会进行聚合
聚合时机
同分区数据聚合
order by 的列为key，作为维度列，聚合其他列
若未指定聚合其他的列按插入顺序保留第一行
如果没有指定聚合的列，所有非维度且为数字的列均会被聚合
聚合范围
由于聚合的时机可能会包含一些还没来得及聚合的临时明细我们无法直接获取汇总值，在使用的时候还是需要sum()
SummingMergeTree
主要类别
表引擎
传统关系型数据库（以 MySQL 为例）的 SQL 语句，ClickHouse 基本都支持此处只会着重介绍不一致的地方
概述
基本与标准 SQL（MySQL）基本一致
Insert
ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种
随可以实现修改和删除，但和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。
“重”的原因主要是每次修改/者删除都会导致放弃目标数据的原有分区，重建新分区。所以在必须删改的情况下尽量做批量的变更，不要进行频繁小数据的操作。
删改操作
Update/Delete
ClickHouse 基本上与标准 SQL 差别不大
支持子查询
支持 CTE(Common Table Expression 公用表表达式 with 子句)
支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，ClickHouse 也会视为两条新 SQL
从右至左去掉维度进行小计
with rollup
从右至左去掉维度进行小计，再从左至右去掉维度进行小计
with cube
只计算合计
with totals
GROUP BY 操作增加了 with rollup\\with cube\\with total 用来计算小计和总计。
窗口函数(官方正在测试中...)
不支持自定义函数
支持操作
Select
与Mysql修改字段基本一致
新增字段
修改字段类型
删除字段
操作
Alter操作
导出数据
SQL操作
Data Replication
副本的目的主要是保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据。
client写入数据到clickhouse-a
clickhouse-a向kafka 集群提交写入日志
clickhouse-b从kafka集群收到写入日志
clickhouse-b从目标副本（clickhouse-a）下载新的数据
副本写入流程
官网
配置步骤
副本只能同步数据，不能同步表结构，我们需要在每台有备份的服务器上手动建表
使用注意事项
分片的 zk_path
一般按照：/clickhouse/table/{shard}/{table_name} 的格式
如果只有一个分片就写 01 即可
第一个参数
副本名称
相同的分片副本名称不能相同。
第二个参数
参数说明
建表语句
副本虽然能够提高数据的可用性，降低丢失风险。
但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。
局限
副本
解决数据水平切分的问题，需要引入分片的概念。
通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用。
Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
客户端向Distributed 表发起写请求（（3 分片 2 副本共 6 个节点）
优化方案
Distributed 将数据写入分片的一个副本
其他的副本从该副本拉取数据
Distributed 将数据写入分片的一个副本 一共需要写三个分片各一个副本 ，一共三个副本数据
true
Distributed 将数据写入所有分片的所有副本
总共需要写三分片，每个分片两副本，一共六个副本数据
false
internal_replication
集群写入流程
客户端向Distributed 表发起读请求（（3 分片 2 副本共 6 个节点）
优先选择errors_count小的副本
errors_count相同根据随机、顺序、随机（优先第一顺位）、Host相近等方式选择
errors_count
集群读取流程
见官网
配置方法
ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性。
分片集群
入门
基本语法
用于查看执行计划，默认值
打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
header
打印计划中各个步骤的描述，默认开启，默认值 1；
description
打印计划中各个步骤的详细信息，默认关闭，默认值 0。
actions
额外参数
PLAN
用于查看语法树
AST
用于优化语法
SYNTAX
用于查看 PIPELINE 计划
用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看；
graph
如果开启了 graph，紧凑打印打，默认开启
PIPELINE
参数含义
EXPLAIN AST SELECT number from system.numbers limit 10;
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);
SYNTAX 语法优化
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
案例
可在官网进行简单测试
Explain查看执行计划
能用数值或日期类型表示的字段就不用字符串
ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型DateTime 不需要经过函数转换处理，执行效率高、可读性好。
时间子段的类型
官方：Nullable 类型几乎总是会拖累性能因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。
此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值
空值存储类型
根据业务特点，不宜过粗或过细，一般按天分区，可以使用 Tuple()分区
分区
clickhouse的order by字段为索引列，必须要指定
一般是查询中充当筛选条件的列
可以是单一维度，也可以是组合维度，一般是高级、查询频率高的在前
一般基数特别大的不适合作为索引列
索引
分区/索引
控制索引粒度，默认是 8192，如非必须不建议调整
Index_granularity
如果不需要保留所有历史数据，可是设置数据失效时间
免去手动过期历史数据的麻烦
TTL
表参数
尽量不要执行单条或小批量删除和插入操作会产生小分区文件，给后台Merge 任务带来巨大压力
不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）
写入/删除优化
建表优化
官方文档
config.xml
users.xml
配置文件
后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，
默认值 16，允许的前提下建议改成 cpu 个数的 2 倍（线程数）。
background_pool_size
执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。
默 认 128，建议改成 cpu 个数的 2 倍（线程数）。
background_schedule_pool_size
设置为分布式发送执行后台任务的线程数，默认 16
建议改成 cpu个数的 2 倍（线程数）。
background_distributed_schedule_pool_size
max_concurrent_queries
设置单个查询所能使用的最大 cpu 个数
默认是 cpu 核数
max_threads
cpu资源
可以设置的比较大，这样可以提升集群查询的上限。
保留一点给 OS，比如 128G 内存的机器，设置为 100GB。
max_memory_usage
当 group 使用内存超过阈值后会刷新到磁盘进行。
一般按照 max_memory_usage 的一半设置内存
clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。
max_bytes_before_external_group_by
当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)
如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
max_bytes_before_external_sort
此参数在 config.xml 中，表示删除超过该大小的分区表会失败
默认50G，建议修改为0 ，即可以删除任意大小的分区表
max_table_size_to_drop
内存资源
ClickHouse 不支持设置多数据目录
但是，可以挂在虚拟券组，一个劝阻绑定多块物理磁盘提升数据io性能
多数查询场景SSD会比普通机械硬盘快2-3倍
多目录
存储
配置说明
常见配置
clickhouse的SQL优化是基于RBO(Rule Based Optimization)
使用count() 或者 count(*)，且没有 where 条件时，直接使用system.tables 的 total_rows
EXPLAIN SELECT count() FROM datasets.hits_v1;
COUNT优化
子查询中有两个重复的字段，会被去重（即使是重命名成其他字段）
注意关键词：子查询、重复字段
消除子查询重复
当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。
EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';
子查询也支持谓词下推
谓词下推
聚合函数内的计算外推
EXPLAIN SYNTAX SELECT sum(UserID * 2) FROM visits_v1
聚合计算外推
对聚合键，group by key 使用 min、max、any 聚合函数，则将函数消除
聚合函数消除
去重重复的聚合键
删除重复的orderby key
删除重复的limit by key
一般人不会犯的错误，犯了就转行吧
删除重复的 USING Key
如果子查询只返回一行数据，在被引用的时候用标量替换
标量替换
开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10) settings optimize_if_chain_to_multiif = 1;
三元运算优化
语法优化规则
Prewhere 和 where 语句的作用相同，都是用来过滤数据
prewhere 只支持*MergeTree 族系列引擎的表
首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
不同之处
查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。
默认情况下我们会开启prewhere 并且 会自动优化成prewhere
使用常量表达式
使用默认值为 alias 类型的字段
包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
select 查询的列字段和 where 的谓词相同
使用了主键字段
但是在某些情况下需要我们手动指定prewhere
Prewhere 替代 where
通过采样运算可极大提升数据分析的性能
采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。
数据采样
选择需要的分区、列减少io
列裁剪与分区裁剪
减少数据量
orderby 结合 where、limit
虚拟列非常消耗资源浪费性能
可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。
避免构建虚拟列
会有2%左右的误差，在允许的情况下可以使用这个
而Count(distinct )会使用 uniqExact精确去重。
uniqCombined 替代 distinct
后面介绍
物化视图
为单个查询设置超时时间
配置周期熔断，在一个周期内，限制用户查询次数
查询熔断
物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。
关闭虚拟内存
为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。
配置 join_use_nulls
无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。
批量写入时先排序
在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时
关注 CPU变化
其他
查询优化
单表查询
当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN
用in代替on
要满足小表在右的原则
右表关联时被加载到内存中与左表进行比较
ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
大小表关联
旧版本不会对join进行谓词下推，所以每个子查询应先过滤
新版本会优化
两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上
果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次
分布式表使用GLOBAL
用字典替换join操作
字典常驻内存，所以字典表不宜太大
使用字典表
减少数据扫描
提前过滤
多表关联
官方：对数据一致性支持最好的Mergetree也仅仅支持最终一致性
数据的去重只会在分区批量写入或数据合并的时候进行，但是后台合并时间不确定
我们无法针对此现象做处理，导致部分数据存在重复
当然我们可以用OPTIMIZE手动出发合并，但是会触发大量数据读写，不建议
原因
对于ReplacingMergeTree、SummingMergeTree 此类聚合表引擎，会出现短暂数据不一致的情况
在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作。
触发大量数据读写，不建议
OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]
语法
手动 OPTIMIZE
我们可以增加一个deleted标记数据是否删除比如 0 代表未删除，1 代表删除数据。
argMax(field1，field2):按照 field2 的最大值取 field1 的值。
数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合 表级别的 TTL 最终将物理数据删除。
通过 Group by 去重
查询语句后加FINAL修饰符会执行 Merge 的特殊逻辑（例如数据去重，预聚合等）。
早期版本此修饰符会将查询变成单线程，查询速度慢
新版本（20.5.2.7-stable+）支持多线程，可通过max_final_threads 参数进行控制单个查询线程数量，但是目前读取part部分依旧串行
通过 FINAL 查询
解决办法
数据一致性
ClickHouse 的物化视图是一种查询结果的持久化，用户查起来跟表没有区别
创建的过程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。
物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）
普通视图不保存数据，保存的仅仅是查询语句
查询的时候还是从原表读取数据
可以理解为一个子查询
普通视图
物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中
对数据重新进行了组织，可以堪称是一张新表
查询速度快
因为都计算好了，数据量少
本质是流式数据的使用场景，累加式的技术，所以需要用历史数据进行驱虫、去核
而如果一张表加了很多物化视图，在写入表的时候视图也会相应的写入，会消耗更多的机器资源，如带宽沾满、存储增加
优缺点
create 语法，会创建一个隐藏的目标表来保存视图数据。
也可以 TO 表名，保存到一张显式的表。
没有加 TO 表名，表名默认就是 .inner.物化视图名
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
必须指定物化视图的 engine 用于数据存储
POPULATE 关键字决定了物化视图的更新策略：
TO [db].[table]语法的时候，不得使用 POPULATE。
物化视图的 alter 操作有些限制，操作起来不大方便
若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图 卸载DETACH 再装载 ATTACH
限制
物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
若有POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table ... as
若无POPULATE 则物化视图在创建之后没有数据
ClickHouse 官方并不推荐使用populated，因为在创建视图过程中插入表中的数据并不会写入视图，会造成数据的丢失。
POPULATE决定了物化视图的更新策略
源表删除视图数据还在
物化视图不支持同步删除
物化视图是一种特殊的数据表，可以用 show tables 查看，可以删除视图数据、可以删除视图
物化视图的更新
物化视图与普通视图的差别
ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎
该引擎能映射到Mysql的某个database，并自动在ClickHouse中建立对应的 ReplacingMergeTree
原理类似canal、Maxwell，ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。
MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步MySQL 中的表和数据，之后则会通过 binlog 进行增量同步
此处可以类比我们处理数据一致性的时候加的deleted标记
_version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增类比create_time字段
_sign 则用于标记是否被删除，取值 1 或 者 -1。类比deleted标签
MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段。
_sign = 1，_version ++
MYSQL_WRITE_ROWS_EVENT
_sign = -1，_version ++
MYSQL_DELETE_ROWS_EVENT
新数据 _sign = 1
MYSQL_UPDATE_ROWS_EVENT
支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。
MYSQL_QUERY_EVENT
目前支持四种binlog事件
MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询
DDL 查询
MySQL INSERT 查询被转换为 INSERT with _sign=1
MySQL DELETE 查询被转换为 INSERT with _sign=-1
MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。
MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换
数据复制
如果在 SELECT 查询中没有指定_version，则使用FINAL 修饰符，返回_version 的最大值对应的数据，即最新版本的数据。
如果在 SELECT 查询中没有指定_sign，则默认使用 WHERE _sign=1，即返回未删除状态（_sign=1)的数据。
SELECT 查询
ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。
ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。
索引转换
使用细则
server-id=1 log-bin=mysql-binbinlog_format=ROW
gtid-mode=onenforce-gtid-consistency=1 # 设置为主从强一致性log-slave-updates=1 # 记录日志
开启GTID模式
sudo systemctl restart mysqld
重启Mysql
mysql配置
set allow_experimental_database_materialize_mysql=1;
开启 ClickHouse 物化引擎
4 个参数分别是 MySQL 地址、databse、username 和 password。
ClickHouse 中创建 MaterializeMySQL 数据库
use test_binlog;show tables;
查看对应表
查看 ClickHouse 的数据
创建复制管道
clickhouse配置
查询过程中中增加 _sign 和 _version 虚拟字段，可以帮助我们理解底层数据插入原理
在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign = -1 的数据过滤掉;
对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。
查询小技巧
Mysql删除表ClickHouse也会删除表
删除表
Mysql新建表，ClickHouse也会新建表
新建表
MaterializeMySQL 引擎
使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有报错，见注释
问题
解决办法：重启该不执行的节点。
分布式 DDL 某数据节点的副本不执行
由于某个数据节点副本异常，导致两数据副本表不一致，某个数据副本缺少表，需要将两个数据副本调整一致。
在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show crete table xxxx 获取。
表结构创建后，clickhouse 会自动从其他副本同步该表数据，验证数据量是否一致即可。
数据副本表和数据不一致
某个数据副本异常无法启动，需要重新搭建副本
清空异常副本节点的 metadata 和 data 目录。
从另一个正常副本将 metadata 目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。
执行 sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data
启动数据库
副本节点全量恢复
某个数据副本表在 zk 上丢失数据，或者不存在，但是 metadata 元数据里存在，导致启动异常
metadata 中移除该表的结构文件，如果多个表报错都移除
mv metadata/xxxxxx/xxxxxxxx.sql /tmp/
手工创建缺少的表，表结构从其他节点 show create table 获取。
创建后会自动同步数据，验证数据是否一致。
数据副本启动缺少 zk 表
但是 zk 里面针对某个 clickhouse 节点的 table meta 信息未被删除（低概率事件）
从其他数据副本 cp 该 table 的 metadata sql 过来.
重启节点。
ZK table replicas 数据未删除，导致重建表报错
在大量 insert 数据的情况下，某个节点意外宕机
数据写入不受影响、数据查询不受影响、建表 DDL 执行到异常节点会卡住副本机制，读写不受影响
现象
启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的建表 DDL 也会同步。
Clickhouse 节点意外关闭
常见问题排查
其他问题
常见问题
进阶
Clickhouse会将运行式的状态记录到众多系统表中（system.*）中，但是直接从中监控数据有些许不足
过于底层，不直观，理想是可视化
ClickHouse只记录了自己的指标，有时候我们还关注zk、服务器IO、cpu等
不足之处
目前流行Prometheus + Grafana的组合方式
集成多框架，包括各种服务器的负载
Prometheus收集各类系统运行指标
Grafana负责可视化
Clickhouse从20.1.2.4内置对接Prometheus功能，可视为Prometeus的Endpoint服务，自动将metrics、envents、asynchronous_metrics三张系统表发送Prometheus
参考案例
百度
监控模板
监控
Data Backup
sudo mkdir -p /var/lib/clickhouse/shadow/
如果目录存在，先清空目录下的数据
创建用于存放备份数据的目录 shadow
echo -n 'alter table t_order_mt freeze' | clickhouse-client
执行备份命令
#创建备份存储路径sudo mkdir -p /var/lib/clickhouse/backup/
#拷贝数据到备份路径sudo cp -r /var/lib/clickhouse/shadow/ /var/lib/clickhouse/backup/my-backup-name
#为下次备份准备，删除 shadow 下的数据sudo rm -rf /var/lib/clickhouse/shadow/*
将备份数据保存到其他路径
手动
可以实现自动化备份
这玩意得找好对应版本，不然用不了
配置丰富
不过这玩意一半丢给运维，自己搞死脑细胞
工具链接
clickhouse-backup
备份
sudo cp -rlbackup/my-backup-name/1/store/cb1/cb176503-cd88-4ea8-8b17-6503cd888ea8/* data/default/t_order_mt/detached/
注意：仅拷贝分区目录，注意目录所属的用户要是 clickhouse
将备份复制到 detached 目录
echo 'alter table t_order_mt attach partition 20200601' | clickhouse-client
执行 attach
查看数据
恢复
备份/恢复方式
备份/恢复
监控及备份
0 条评论
回复 删除
下一页
