ClickHouse学习心得二

欧几里得-Euclidean

已于 2024-03-22 09:14:37 修改

阅读量905

点赞数 12

文章标签： clickhouse 分布式数据库

于 2024-03-22 09:13:08 首次发布

本文链接：https://blog.csdn.net/qq_42969753/article/details/136844548

版权

本文详细介绍了ClickHouse数据库的查询执行计划查看、数据类型选择、表结构优化、写入删除策略、语法优化规则、查询性能提升技巧以及数据一致性维护方法，还包括MaterializeMySQL引擎的工作原理和常见问题排查。

摘要由CSDN通过智能技术生成

1.Explain查看执行计划

语法：EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

PLAN PLAN：用于查看执行计划，默认值

header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0
description 打印计划中各个步骤的描述，默认开启，默认值 1
actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0

AST ：用于查看语法树

SYNTAX：用于优化语法

PIPELINE：用于查看 PIPELINE 计划

header 打印计划中各个步骤的 head 说明，默认关闭;
graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看
actions 如果开启了 graph，紧凑打印打，默认开启。

2.建表优化

数据类型

时间字段的类型
- 虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好
空值类型
- Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引
- 使用字段默认值表示空，或者自行指定一个在业务中无意义的值，例如-1
分区和索引
- 一般是按天分区，一亿数据大概是10-30个分区
- 必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定
  - 通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列
  - 如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳

表参数

Index_granularity 是用来控制索引粒度的，默认是 8192
- 如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改

写入和删除优化

尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力
不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据
- 处理方式： “ Too many parts 处理 ” ：使用 WAL 预写日志,提高写入性能。 in_memory_parts_enable_wal 默认为 true 在服务器内存充裕的情况下增加内存配额,一般通过 max_memory_usage 来实现在服务器内存不充裕的情况下,建议将超出部分内容分配到系统硬盘上,但会降低执行速度,一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。
常见配置
- 配置项主要在 config.xml 或 users.xml 中，基本上都在 users.xml 里
存储
- ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。

3.ClickHouse 语法优化规则

COUNT 优化
- 调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows
消除子查询重复字段
- 子查询中有两个重复的 id 字段，会被去重
谓词下推
- 当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤
聚合计算外推
- EXPLAIN SYNTAX SELECT sum(UserID * 2) FROM visits_v1 //返回优化后的语句 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 //返回优化后的语句 SELECT sum(UserID) * 2, max(VisitID), UserID FROM visits_v1 GROUP BY UserID
删除重复的 order by key
删除重复的 limit by key
删除重复的 USING Key
标量替换
- 子查询只返回一行数据，在被引用的时候用标量替换
三元运算优化
- 开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数
- multiIf(cond1, then1, cond2, then2, cond3, then3, ..., else)

4.查询优化

单表查询

Prewhere 替代 where
- Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持*MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。
- 默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：使用常量表达式使用默认值为 alias 类型的字段包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询 select 查询的列字段和 where 的谓词相同使用了主键字段

数据采样

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略
SELECT Title,count(*) AS PageViews FROM hits_v1 SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数 WHERE CounterID =57 GROUP BY Title ORDER BY PageViews DESC LIMIT 1000

列裁剪与分区裁剪

避免使用 select * 操作
字段越少，消耗的 io 资源越少

orderby 结合 where、limit

避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

uniqCombined 替代 distinct

反例： select count(distinct rand()) from hits_v1; 正例： SELECT uniqCombined(rand()) from datasets.hits_v1

多表关联

用 IN 代替 JOIN
- 当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN
- insert into hits_v2 select a.* from hits_v1 a where a. CounterID in (select CounterID from visits_v1); #反例：使用 join insert into table hits_v2 select a.* from hits_v1 a left join visits_v1 b on a. CounterID=b. CounterID;
大小表 JOIN
- 多表 join 时要满足小表在右的原则
注意谓词下推
分布式表使用 GLOBAL
- 两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销
使用字典表
- 将一些需要关联分析的业务创建成字典表进行 join 操作，前提是字典表不宜太大，因为字典表会常驻内存
提前过滤
- 通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的

5.数据一致性

通过 Group by 去重
通过 FINAL 查询
- 在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑（例如数据去重，预聚合等）

6.物化视图

必须指定物化视图的 engine 用于数据存储
TO [db].[table]语法的时候，不得使用 POPULATE。
查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
物化视图的 alter 操作有些限制，操作起来不大方便
物化视图是一种特殊的数据表，可以用 show tables 查看

7.MaterializeMySQL 引擎

ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能
- MaterializeMySQL 同时支持全量和增量同步
- MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段
  - 其中，_version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1
DDL
- MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。如果 ClickHouse 不能解析某些 DDL 查询，该查询将被忽略
数据复制
- MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换： MySQL INSERT 查询被转换为 INSERT with _sign=1。 MySQL DELETE 查询被转换为 INSERT with _sign=-1。 MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1
索引转换
- ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组
SELECT 查询
- 如果在 SELECT 查询中没有指定_version，则使用 FINAL 修饰符，返回_version 的最大值对应的数据，即最新版本的数据。如果在 SELECT 查询中没有指定_sign，则默认使用 WHERE _sign=1，即返回未删除状态（_sign=1)的数据。

8.常见问题排查

分布式 DDL 某数据节点的副本不执行
- 重启该不执行的节点
数据副本表和数据不一致
- 在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show crete table xxxx 获取。表结构创建后，clickhouse 会自动从其他副本同步该表数据，验证数据量是否一致即可
副本节点全量恢复
- 清空异常副本节点的 metadata 和 data 目录。从另一个正常副本将 metadata 目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。执行 sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data 启动数据库
数据副本启动缺少 zk 表
- metadata 中移除该表的结构文件，如果多个表报错都移除 mv metadata/xxxxxx/xxxxxxxx.sql /tmp/ 启动数据库手工创建缺少的表，表结构从其他节点 show create table 获取。创建后会自动同步数据，验证数据是否一致。
ZK table replicas 数据未删除，导致重建表报错
- 从其他数据副本 cp 该 table 的 metadata sql 过来. 重启节点
Clickhouse 节点意外关闭
- 启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的建表 DDL 也会同步