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