我是靠谱客的博主 拉长汽车,这篇文章主要介绍ClickHouse学习笔记Click House入门数据类型表引擎SQL操作副本执行计划Click House高级Explain查看执行计划建表优化Click House语法优化规则查询优化数据一致性(重点!!!)物化视图,现在分享给大家,希望可以做个参考。

学习视频链接:
https://www.bilibili.com/video/BV1Yh411z7os/?spm_id_from=333.1007.top_right_bar_window_custom_collection.content.click&vd_source=05e91bcf9e12725fd6d21d00acc4851f

官方文档:
https://clickhouse.com/

部署推荐:128G服务器,100G内存,32线程内存。

Click House入门


使用过程中踩坑笔记:http://t.csdn.cn/elvDm


  • OLAP型数据库本就不应该执行删除、更新操作。
  • CH将数据划分为多个Partition,每个Partition进一步划分为多个Index Granularity(索引粒度),然后多个CPU核心分别处理器中的一部分实现并行数据处理。这种设计下,单条Query就能利用整机所有CPU,达到极致的并行处理能力,降低查询延时。
  • 对于高qps(Query per Second)的查询业务,ClickHouse并不是强项。
  • 不适合做初始存储适合存储宽表
  • 避免做join操作:
    • 简单原理:假设 table_A join table_B
    • table_B的表会先加载入内存,再逐条匹配table_A中的数据,不论大小表。

数据类型

整型

  • int8 - byte
  • int16 - short
  • int32 - int
  • int64 - long
  • 无符号
    • Uint8
    • Uint16
    • Uint32
    • Uint64

浮点型

建议不用double,进行运算时会精度丢失,一般用于数值比较小,不涉及大量计算统计的,精度要求不高的数据。

例:1-0.9 = 0.099999999……

  • Float32 - float
  • Float64 - double

布尔型

没有单独类型存储布尔值,可使用Uint8类型,取值限制为0、1

Decimal型

使用场景:一般金额字段、汇率、利率等字段为了保证小数点精度,都是用Decimal进行存储。

NULL

复制代码
1
2
3
NOTE: Using `Nullable` almost always negatively affects performance, keep this in mind when designing your databases.

如果要使用Null,可以定义字段中Null值为业务上没有意义的字段,比如:年龄的Null为-1。

表引擎

https://clickhouse.com/docs/zh/engines/table-engines/

表引擎(即表的类型)决定了:

复制代码
1
2
3
特别注意:引擎的命名大小写敏感。 命名方式:大驼峰:BigTableData
  • 数据的存储方式和位置,写到哪里以及从哪里读取数据。
  • 支持哪些查询以及如何支持。
  • 并发数据访问。
  • 索引的使用(如果存在)。
  • 是否可以执行多线程请求。
  • 数据复制参数。

TinyLog

  • 不支持索引
  • 没有并发控制
  • 一般保存少量的数据。

Memory

  • 优点:快
  • 缺点:服务器重启数据就会消失
  • 不支持索引

集成引擎

含义:将ClickHouse数据直接映射到外部引擎中,在ClickHouse上进行查询。

因此:ClickHouse这种方式并不拥有数据,只做查询。

MySql

复制代码
1
2
3
思考: 是不是可以直接把所有游戏表映射到CH中,进行查询?

https://clickhouse.com/docs/en/engines/table-engines/integrations/mysql

MergeTree

  • ClickHouse最强大的表引擎
  • 支持索引和分区
  • 有很多衍生引擎

????建表语句

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], ... INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2, ... PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]), PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]) ) ENGINE = MergeTree() ORDER BY expr // 必选项 [PARTITION BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ] [WHERE conditions] [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ] [SETTINGS name=value, ...]
复制代码
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE example_table ( d DateTime, a Int TTL d + INTERVAL 1 MONTH, b Int TTL d + INTERVAL 1 MONTH, c String )ENGINE = MergeTree PARTITION BY toYYYYMM(d) primary key(a) // 注意:主键不存在唯一约束!!! ORDER BY d;

PARTITION BY(可选)

????作用

降低扫描范围,优化查询速度。

????如果不填

只会使用一个分区-all。

????分区目录

以列文件+索引文件+表定义文件组成。

????并行

分区后,以分区为单位并行处理。

复制代码
1
2
官方建议以“天”为分区条件。
  • 分区不会加速查询。

文件含义

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<PartitionId_MinBlockNum_MaxBlockNum_Level> <分区值_最小分区块编号_最大分区块编号_合并层级> ????PartitionId 数据分区ID生成规则: 数据分区规则由分区ID决定,分区ID由PARTITION BY分区键决定。根据分区键字段类型,ID生成规则可分为: 未定义分区键: - 没有定义PARTITION BY,默认生成一个目录名为all的数据分区,所有数据均存放在all目录下。 整形分区键: - 分区键为整形,那么直接用该整形值的字符串形式作为分区ID。 日期类分区键: - 分区键为日期类型,或者可以转化成日期类型。 其他类型分区键: - String、Float类型等,通过128位的Hash算法取其Hash值作为分区ID。 ????MinBlockNum 最小分区块编号,自增类型,从1开始向上递增。每产生一个新的目录分区就香上递增一个数字。 ????MaxBlockNum 最大分区块编号,新创建的分区MinBlockNum等于MaxBlockNum的编号。 ????Level 合并的层级,被合并的次数。合并次数越多,层级值越大。

????bin文件:数据文件

????mrk:标记文件

????count.txt:存储数据条数

????data.bin

数据文件,表里面的数据存储在这里面

老版本中(21.7之前)是每一列存储一个.bin和.mrk2文件

????columns.txt

列的结构信息

????primary.idx

索引文件——>稀疏索引

????minmax_create_time.idx

分区键的最大最小值

数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区,不会纳入任何已有分区。写入后一段时间(10-15mins后),clickhouse会自动执行合并操作(也可以手动通过optimize执行合并),将临时分区的数据,合并到以后分区中。

复制代码
1
2
3
-- 加入了分区名表示仅对某个分区进行合并 optimize table 表名 [partition 分区名] final;

Primary Key(可选)

仅提供了一级索引,但不是唯一约束。

index granularity:索引粒度,指在稀疏索引中两个相邻索引对应数据的间隔。ClickHosue中的MergeTree默认是8192。官方不建议修改这个值,除非该列存在大量重复值,例如:一个分区中几万行才有一个不同数据。

Order By(必选)

分区内排序!!!

  • 由于稀疏索引的类二分查找的特点,所以必须排序。
  • 有序数据查询更加迅速。

要求:主键必须是order by字段的前缀字段!!!

例如:order by的字段是(id,sku_id),主键若只有一个,只能是id,不能是sku_id。

二级索引

在一级索引分块的基础上,再次进行汇总

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table t_order_mt2( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime, -- GRANULARITY 是设定二级索引对于一级索引粒度的分区粒度 -- 例如:一级索引:[0,5],[5,10],[10,15],[15,20]…… -- GRANULARITY 2 ----> 则二级索引:[0,10],[10,20]…… INDEX a total_amount TYPE min max GRANULARITY 5 )engine = MergeTree partition by toYYYYMMDD(create_time) primary key(id) order by(id,sku_id)

数据TTL

TTL:Time To Live,MergeTree提供了可以管理数据or的生命周期功能。

列级别TTL

TTL不能用于键列。

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#creating-a-table-with-ttl

Creating a table with TTL

复制代码
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE example_table ( d DateTime, a Int TTL d + INTERVAL 1 MONTH, b Int TTL d + INTERVAL 1 MONTH, c String ) ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d;

Adding TTL to a column of an existing table

复制代码
1
2
3
4
ALTER TABLE example_table MODIFY COLUMN c String TTL d + INTERVAL 1 DAY;

表级别TTL

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl

????过期可以移动数据到指定位置。

ReplacingMergeTree

相对于MergeTree,只是多一个去重的功能。根据order by的字段去重。

????去重时机

数据去重只会在合并的过程中出现。合并会在未知的时间在后台进行,所以无法预先做出计划,有一些数据可能仍未被处理。

????去重范围

去重只会在分区内部进行去重,不能执行跨分区的去重。

因此,ReplacingMergeTree能力有限,不能保证没有重复的数据出现。

复制代码
1
2
3
4
5
6
7
8
9
10
create table t_order_rmt( in UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime )engine = ReplacingMergeTree(create_time) -- 这里create_time为版本字段 partition by toYYYYMMDD(create_time) primary key(id) order by(id,sku_id);

ReplacingMergeTree()填入的参数为版本字段,重复数据保留版本字段值最大的。如果不填版本字段,默认按照插入顺序保留最后一条。

  • 新版本会在插入数据的时候进行一次去重
  • 实际上利用order by字段作为唯一键
  • 去重不能跨分区
  • 只有同一批插入or合并分区才会进行去重
  • 认定重复的数据,保留版本字段值最大的
  • 如果版本字段相同,则按照插入数据的顺序保留最后一笔

SummingMergeTree

????使用场景: 对于不查询明细,只关心以维度进行汇总聚合结果的场景。

????预聚合功能:①分区内聚合;②分片合并时才会聚合。

复制代码
1
2
3
4
5
6
7
8
9
10
create table t_order_rmt( in UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime )engine = SummingMergeTree(total_amount) -- 这里total_amount为聚合字段 partition by toYYYYMMDD(create_time) primary key(id) order by(id,sku_id); -- 根据这两个字段进行group by,预聚合
  • 以SummingMergeTree()中指定的列作为汇总数据列;
  • 可以填写多列必须数字列,如果不填,以所有**非维度列(除order by以外的所有字段)**且为数字列的字段为汇总数据列
  • 以order by的列为准,作为维度列
  • 其他的列按插入顺序保留第一行
  • 不在一个分区的数据不会被聚合
  • 只有在同一批次插入or分片合并时才会进行聚合

????开发建议:设计聚合表的话,唯一键值、流水号可以去掉,所有字段全部是维度、度量或者时间戳。

SQL操作

Update 和 Delete

这类操作被称为Mutation查询(可变查询),可以看作Alter的一种。

  • 不支持事务。
  • 每次修改or删除都会导致放弃目标数据的原有分区,重新建立分区。
  • 建议尽量做批量更改,不要频繁小数据的操作(例如:个别字段的修改)。

Update

复制代码
1
2
alter table t_order_smt update total_amount = toDecimal32(2000.00,2) where id =102;

Delete

复制代码
1
2
alter table t_order_smt delete where sku_id = 'sku_001';

实现高性能Update或delete的思路

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table A( a XXX, b XXX, c XXX, _sign UInt8, _version UInt32 ) -- 更新:插入一条新的数据,_version + 1 -- 查询:where version = max_version -- 删除:_sign 0 表示未删除,1表示已删除 -- 查询:where sign = 0 and version = max_version -- 问题:时间久了,数据会膨胀 -- 需要解决:类似合并机制,清理过期数据

查询

  • 尽量避免使用JOIN:Join操作无法使用缓存,所以即使是两次相同的JOIN语句,Click House也会是为两条新的SQL。
  • 不支持自定义函数。

multiIf

聚合函数

KEY words:with rollup, with cube, with totals

复制代码
1
2
3
4
-- 这两个语句等同 SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day); SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;

假设维度是a,b

  • rollup:上卷,从右到左,维度增加

    • group by null
    • group by a
    • group by (a,b)
  • cube:多维分析

    • group by null
    • group by a
    • group by b
    • group by (a,b)
  • totals:总计

    • group by null
    • group by (a,b)

alter操作

  • 因为是列式存储,对字段的修改会更方便

导出数据

  • 用的比较少,因为存储的是宽表,可以直接用于BI表

https://clickhouse.com/docs/en/interfaces/formats

复制代码
1
2
clickhouse-client --query "select * from t_order_mt where create_time = '2020-06-01'" --format CSVWithNames> [导出地址]

副本

  • 目的:主要是保障数据的高可用性,即使一台CH节点宕机,那么也可以从其他服务器获得相同数据。

副本写入流程

  • 没有主从数据库的概念
  1. client—写入数据—》CH-a—提交写入日志—》zookeeper-cluster—收到写入日志—》CH-b;
  2. CH-a—提交写入日志的同时—》从目标副本下载新数据—传给—》CH-b

执行计划

https://clickhouse.com/docs/en/sql-reference/statements/explain/#explain-types


Click House高级


Explain查看执行计划

复制代码
1
2
EXPLAIN [AST | SYNTAX |PLAN|PIPELINE] [setting = value,...] select ...

Syntax

跑语句之前可以使用这个语法查看,用于优化语法;

Pipeline

用于查看Pipeline计划

建表优化

数据类型

时间字段的类型

建表时能用数值型或日期时间类型表示的字段就不要用字符串,虽然ClickHouse底层将DateTime存储为时间戳Long类型,但不建议存储Long类型,因为DateTime不需要经过函数转换,执行效率高、可读性好

复制代码
1
2
3
4
5
6
7
8
9
10
create table test( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Int32 )engine = ReplacingMergeTree(create_time) partition by toYYYYMMDD(toDate(create_time)) -- 需要转换一次,否则报错 primary key (id) order by (id,sku_id)

空值存储

  • click house中的空值位Nullable类型,官方指出,Nullable类型几乎总是会拖累性能
    • 原因:存储Nullable列时需要创建一个额外的文件存储Null的标记,并且Nullable列无法被索引。
    • 注:除极端情况,应直接使用默认值表示空,或者自定指定一个在业务中无意义的值来代替。

分区和索引

  • 一般选择按天分区
  • 1亿条数据一般选择30个左右的分区。
  • 索引:order by(a,b,c) 从左到右优先索引,高频查询的字段放在前面。
  • 基数特别大的不适合做索引列。
    • 基数大的列:该列的数据去重后和数据记录数越接近基数越大。
    • 为什么基数大不适合做索引:基数过大,查找时越需要依次遍历,则失去了索引意义。

表参数

  • Index_granularity是用来控制索引粒度的,默认是8192,如非必须不建议调整。
  • 如果表中不是必须保留全量历史数据,建议制定TTL(生存时间值)可以免去手动处理过期历史数据的麻烦,TTL也可以通过Alter table随时修改。

写入和删除优化

  • 尽量不要执行单条或小批量删除和插入操作,这样会产生小分区文件,给后台Merge任务带来巨大压力;
  • 不要一次写入太多分区,或数据写入太快:
    • 数据写入太快会导致Merge速度跟不上而报错,一般建议每秒钟发起2-3次写入操作,每次操作写入2w-5w条数据(具体情况依据服务器性能而定)
  • 写入太快会报错:
    • “Too many parts”处理:
      1. 使用WAL预写日志,提高写入性能。
      2. 降低写入频率。
    • ”Memory limit“处理:

常见配置

????一般CH的瓶颈出现在CPU压力过大内存不足IO压力过大

  • 配置项主要在config.xml 和 users.xml
    • config.xml:服务端配置,用户的默认配置路径指向users.xml
      • 设定之后无法被覆盖
      • https://clickhouse.com/docs/en/operations/server-configuration-parameters/
    • users.xml:
      • https://clickhouse.com/docs/en/operations/settings/settings-users

CPU资源

配置描述
background_pool_size后台线程池的大小,merge线程就是在该线程池中执行,该线程池不仅仅是给merge线程使用的,默认值16,允许的前提下建议改成CPU个数的2倍(一核可以虚拟化成两线程)
background_schedule_pool_size执行后台任务的线程数,默认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此参数在users.xml中,表示单词Query占用内存最大值,该值可以设置的比较大,这样可以提升集群查询的上限。保留一点给OS,比如128G内存的机器,设置为100G。
max_bytes_before_external_group_by一般按照max_memory_usage的一半设置内存,当group使用内存超过阈值后会刷新到磁盘进行。因为Click house聚合分为两个阶段:查询并建立中间数据、合并中间数据,结合上一项,建议50GB。
max_bytes_before_external_sort当 order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序),如果不设置该值,那么当内存不够时直接抛错,设置了该值 order by可以正常完成,但是速度相对存内存来说肯定要慢点(实测慢的非常多,无法接受)。
max_table_size_to_drop此参数在 config.xml 中,应用于需要删除表或分区的情况,默认是50GB,意思是如果删除50GB以上的分区表会失败。建议修改为0,这样不管多大的分区表都可以删除。

存储

ClickHouse不支持设置多数据目录,为了提升数据IO性能,可以挂在虚拟券组,一个券组绑定多块物理磁盘提升读写性能,多数据查询性能场景SSD会比普通机械硬盘快2-3倍。

Click House语法优化规则

  • CH的SQL优化规则是基于RBO(Rule Based Optimization)

COUNT优化

在调用count函数时,如果使用的是count() or count(*),且没有where条件,则会直接使用system.tables 的 total_rows。

复制代码
1
2
3
4
5
6
7
explain syntax select count(*) from zs_game_role zgr -- 结果 SELECT count() FROM zs_game_role AS zgr

消除子查询的重复字段

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
explain syntax select a.role_id, a.member_id, a.main_game_id from zs_game_role b left join ( select role_id, role_id, member_id, main_game_id, role_id from zs_game_role zgr ) as a using (role_id) limit 3 -- 结果 SELECT a.role_id, a.member_id, a.main_game_id FROM zs_game_role AS b ALL LEFT JOIN ( SELECT role_id, member_id, main_game_id FROM zs_game_role AS zgr ) AS a USING (role_id) LIMIT 3

谓词下推

  • 原则:能提前过滤自动提前过滤
  • 当 group by 有 having 子句,但是没有 with cube、with rollup 或者 with totals 修饰的时候,having 过滤会下推到 where 提前过滤
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
explain syntax select * from( select role_id from zs_game_role zgr ) as a WHERE a.role_id = 20 -- 结果 SELECT role_id FROM ( SELECT role_id FROM zs_game_role AS zgr WHERE role_id = 20 ) AS a WHERE role_id = 20
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
explain syntax SELECT * FROM ( select role_id from zs_game_role zgr union all select role_id from zs_game_role zgr ) as a WHERE a.role_id = 20 -- 结果 SELECT role_id FROM ( SELECT role_id FROM zs_game_role AS zgr WHERE role_id = 20 UNION ALL SELECT role_id FROM zs_game_role AS zgr WHERE role_id = 20 ) AS a WHERE role_id = 20

聚合计算外推

复制代码
1
2
3
4
5
6
7
8
9
10
explain syntax SELECT sum(role_id * 2) from zs_game_role zgr -- 结果 SELECT sum(role_id) * 2 FROM zs_game_role AS zgr

聚合函数消除

如果对聚合键,也就是group by key 使用 min、max、any聚合函数,则将函数消除。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
explain syntax SELECT sum(member_id * 2), max(game_id), any(role_id) from zs_game_role zgr group by role_id -- 结果 SELECT sum(member_id) * 2, max(game_id), role_id FROM zs_game_role AS zgr GROUP BY role_id

删除重复的order by key

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
explain syntax SELECT role_id, member_id, main_game_id from zs_game_role zgr order by role_id, role_id , member_id , member_id -- 结果 SELECT role_id, member_id, main_game_id FROM zs_game_role AS zgr ORDER BY role_id ASC, member_id ASC

删除重复的 limit by key、using key

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
explain syntax SELECT role_id, member_id, main_game_id from zs_game_role zgr limit 3 by role_id , member_id , role_id , member_id -- 结果:会优化 SELECT role_id, member_id, main_game_id FROM zs_game_role AS zgr LIMIT 3 BY role_id, member_id
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
explain syntax SELECT zgr2.role_id, zgr2.member_id, zgr2.main_game_id from zs_game_role zgr left join zs_game_role zgr2 on zgr.role_id = zgr2.role_id limit 3 by zgr.role_id , zgr.member_id , zgr2.role_id , zgr2.member_id -- 结果:不会优化 SELECT zgr2.role_id, zgr2.member_id, zgr2.main_game_id FROM zs_game_role AS zgr ALL LEFT JOIN zs_game_role AS zgr2 ON role_id = zgr2.role_id LIMIT 3 BY role_id, member_id, zgr2.role_id, zgr2.member_id

标量替换

标量:写死的值

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
explain syntax with ( SELECT count() from zs_game_role zgr3 ) as total_num SELECT zgr.role_id, zgr.member_id, SUM(zgr.main_game_id) / total_num as a from zs_game_role zgr group by zgr.role_id, zgr.member_id order by zgr.role_id, zgr.member_id limit 5 -- 结果:with的内容替换成一个标量值了 WITH identity(_CAST(0, 'Nullable(UInt64)')) AS total_num SELECT role_id, member_id, sum(main_game_id) / total_num AS a FROM zs_game_role AS zgr GROUP BY role_id, member_id ORDER BY role_id ASC, member_id ASC LIMIT 5

三元运算的优化

  • 嵌套的if会优化为multiIf的语句

查询优化

单表查询

Prewhere替代where

​ Prewhere 和where 语句的作用相同,用来过滤数据。不同之处在于 prewhere 只支持MergeTree 族系列引擎的表,首先会读取指定的列数据,来判断数据过滤,等待数据过滤之后再读取 select 声明的列字段来补全其余属性
当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能,Prewhere会自动优化执行过滤阶段的数据读取方式,降低io操作
​ 在某些场合下,prewhere 语句比 where 语句处理的数据量更少性能更高。

  • 大部分场景where会自动优化为prewhere

某些场景不会自动转换成prewhere:

  • 使用常量表达式
  • 使用默认值为alias类型的字段
  • 包含了arrayJoin、golbalIn、golbalNotIn或indexHint的查询
  • select查询的列字段和where的谓词完全相同
  • 使用了主键字段(order by字段)

数据采样

通过采样运算可极大提升数据分析的性能。

复制代码
1
2
3
4
5
6
7
8
9
10
select Title,count(*) as pageViews from table SAMPLE 0.1 where countID = XX group by XX order by XX

列裁剪与分区裁剪

  • 列裁剪:其实就是避免使用select * ,筛选出需要的字段
  • 分区裁剪:避免使用select * ,在where中使用partition by字段,选择分区

orderby结合where、limit

千万以上的数据集进行order by查询时需要搭配where条件和limit语句一起使用。

  • 因此,尽量不要order by单独使用

避免构建虚拟列

  • 虚拟列:原表中不存在的列,计算出来的列,如:
复制代码
1
2
3
4
5
6
7
select a, b, a+b -- 虚拟列 from table

虚拟列非常消耗资源,浪费性能。

uniqCombined替代distinct

  • uniqCombined:近似去重,但是精度不会很低,差别很小

性能可提升10倍以上,uniqCombined底层采用类似HyperLogLog算法实现。

不建议对千万以上的对准确度没有要求的数据进行精确去重,使用近似去重,例如:精确活跃率1千万,近似去重:0.999千万。

物化视图

视图:保存的是SQL的操作逻辑。

物化视图:不仅保存SQL的操作逻辑,还保存操作过后的结果。

其他注意事项

查询熔断

为了避免因个别慢查询引起的服务雪崩问题,除了可以为单个查询设置超时以外,还可以配置周期熔断,在一个查询周期内,如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询。

关闭虚拟内存

物理内存和虚拟内存的数据交换,会导致查询变慢,资源允许的情况下关闭虚拟内存。

配置join_use_nulls

为每一个账户添加join_use_nulls配置,左表中的一条记录在右表中不存在,右表的相应字段会返回该字段相应数据类型的默认值,而不是标准SQL中的Null值。

批量写入时先排序

批量写入数据时,必须控制每个批次的数据中涉及到的分区的数量,在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多,会导致 CIick House 无法及时对新导入的数据进行合并,从而影响查询性能。

关注CPU

CPU一般在50%左右会出现查询波动,达到70%会出现大范围的查询超时,CPU是关键指标,要非常关注

多表关联

CH的JOIN:

  • 原理:右表加载到内存,再匹配;
  • 为什么JOIN不行:因为1;
  • 非要使用,如何使用:
    • 能过滤先过滤,特别是右表;
    • 右表放小表;
    • 特殊场景可以考虑使用字典表;
    • 可以替换的话,利用IN替换JOIN
复制代码
1
2
3
-- 建表的时候,想要复制表结构: create table XXX as select * from XXXX where 1 = 0; -- 条件不成立,数据永远不会写进来

Join原理

A join B,将B表全部加载到内存中,A表中的数据会逐条匹配内存中的B表。

用IN代替JOIN

  • 当多表联查时,查询的数据仅从其中一张表出时,可以考虑从IN操作而不是JOIN
复制代码
1
2
select table_a.* from table_a where table_a.count_id in (select count_id from table_b);

大小表JOIN

多表 join 时要满足小表在右的原则,右表关联时被加载到内存中与左表进行比较, Click House 中无论是 Left join 、 Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在,所以右表必须是小表。

注意谓词下推(版本差异)

  • 尽量在join之前进行过滤

ClickHouse 在join查询时不会主动发起谓词下推的操作,需要每个子查询提前完成过滤操作,需要注意的是,是否执行谓词下推,对性能影响差别很大(新版本中已经不存在此问题,但是需要注意谓词的位置的不同依然有性能的差异)

分布式表使用GLOBAL

  • 查询放大:两张分布式表进行JOIN的时候,两张表的N各节点互相发起查询,变成N*N次

两张分布式表上的IN和JOIN之前必须加上GLOBAL关键字,右表只会在接收查询请求的那个节点查询一次,并将其分发到其他节点上。如果不加GLOBAL关键字的话,每个节点都会单独发起一次对右表的查询,而右表又是分布式表,就导致右表一共会被查询N²次(N是该分布式表的分片数量),这就是查询放大,会带来很大开销。

使用字典表

  • 可以是自己创建的表,也可以是外部文件。

将一些需要关联分析的业务创建成字典表进行join操作,前提是字典表不宜太大,因为字典表会常驻内存

提前过滤

通过增加逻辑过滤可以减少数据扫描,达到提高执行速度、降低内存消耗的目的。

数据一致性(重点!!!)

  • 查询CH手册发现,即便对数据一致性支持最好的MergeTree,也只是保持最终一致性
  • 数据一致性:
    • replacingMergeTree不能保证查询时没重复,只能保证最终一致性;
    • 解决:
      • 手动optimize,生产环境不推荐;
      • 通过sql实现去重:group by -->高级一点用法,加标记字段
      • 使用final:
        • 20.5之后,final可以是多线程,但是读取part是串行的;
      • 重复一点无所谓:特定业务条件下;

准备数据

建表

复制代码
1
2
3
4
5
6
7
8
9
create table test_a( user_id UInt64, score String, deleted UInt8 DEFAULT 0, create_time DateTime DEFAULT toDateTime(0) )ENGINE = ReplacingMergeTree(create_time) order by user_id;

写入数据

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
INSERT into table test_a(user_id, score) with( select ['A', 'B', 'C', 'D', 'E', 'F', 'G'] )as dict select number as user_id, dict[number%7 + 1] from numbers(10000000);

修改前50W行数据,修改内容包括name字段和create_time版本号字段

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
INSERT into table test_a (user_id, score, create_time) with( select ['AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG']) as dict select number as user_id, dict[number%7 + 1], NOW() as create_time from numbers(500000);

手动OPTIMIZE(生产中一般不会使用)

复制代码
1
2
optimize table test_a final

通过GroupBY去重

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT user_id, argMax(score, create_time) as score, argMax(deleted, create_time) as deleted, max(create_time) as ctime from test_a ta group by user_id having deleted = 0;

创建视图,方便测试

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create view view_test_a as SELECT user_id, argMax(score, create_time) as score, argMax(deleted, create_time) as deleted, max(create_time) as ctime from test_a ta group by user_id having deleted = 0;

插入数据,再次查询

复制代码
1
2
3
4
5
6
7
8
insert into table test_a(user_id,score,create_time) values(0,'AAAA',now()); SELECT * FROM view_test_a WHERE user_id = 0; >>> 结果 id user_id score create_time 0 AAAA 0 2022-09-13 15:43:38.000

“删除”测试数据

复制代码
1
2
3
4
5
6
insert into table test_a (user_id,score,deleted,create_time) values(0,'AAAA',1,now()); select * from view_test_a WHERE user_id = 0; >>> 结果:无数据

通过FINAL查询

在查询语句后增加Final修饰符,这样在查询的过程中将会执行Merge的特殊逻辑(如:数据去重,预聚合等)。

在v20.5.2.7-stable版本之后,FINAL查询支持多线程执行,并且可以通过max_final_threads参数控制单个查询的线程数。但是目前读取part部分的动作依然是串行的。

FINAL查询最终的性能和很多因素相关,列字段的大小、分区的数量等等都会影响到最终的查询时间,所以还要结合实际场景取舍。

参考链接:https://github.com/ClickHouse/ClickHouse/pull/10463

复制代码
1
2
3
4
5
6
7
8
explain pipeline SELECT * from test_a ta WHERE create_time > toDateTime(0); >>>结果 (Expression) ExpressionTransform × 6 (ReadFromMergeTree) MergeTreeThread × 6 01

物化视图

  • 视图:保存的是SQL的操作逻辑。

  • 物化视图:不仅保存SQL的操作逻辑,还保存操作过后的结果,结果根据相应的引擎存到磁盘或内存中。

​ ClickHouse 的物化视图是一种查询结果的持久化,它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别,它就是一张表,它也像是一张时刻在预计算的表,创建的过程它是用了一个特殊引擎,加上后来 as select,就是 create一个table as select的写法。

​ “查询结果集”的范围很宽泛,可以是基础表中部分数据的一份简单拷贝,也可以是多表join之后产生的结果或其子集,或者原始数据的聚合指标等等。所以,物化视图不会随着基础表的变化而变化,所以它也称为快照(snapshot)。

优缺点

优点:查询速度快,要是把物化视图这些规则全部写好,它比原数据查询快了很多,总的行数少了,因为都预计算好了。

缺点:它的本质是一个流式数据的使用场景,是累加式的技术,所以如果要用历史数据做去重、去核这样的分析,在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图,在写这张表的时候,就会消耗很多机器的资源,比如数据带宽占满、存储一下子增加了很多

基本语法

​ 创建时会创建一个隐藏的目标表来保存视图数据。也可以TO表明,保存到一张显式的表。没有加TO表名,表名默认就是.inner.物化视图名。

复制代码
1
2
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
  • [POPULATE]:添加之后,在创建视图时会遍历历史数据,会增加服务器负载,如果要历史数据,使用INSERT INTO写入数据。

  • 在创建没有TO [db].[table]的物化视图时,您必须指定ENGINE- 用于存储数据的表引擎。

  • 使用TO [db].[table]创建物化视图时,不得使用POPULATE.

  • 物化视图的实现方式如下:向 中指定的表中插入数据时,插入的SELECT部分数据通过该SELECT查询进行转换,并将结果插入到视图中。

  • 查询语句可以包含下面的子句:DISTINCT , GROUP BY , ORDER BY , LIMIT……

准备数据

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table test_a_test( user_id UInt64, score String, deleted UInt8 DEFAULT 0, create_time Date )ENGINE = MergeTree() partition by toYYYYMM(create_time) order by (create_time, intHash32(user_id)) sample by intHash32(user_id) SETTINGS index_granularity = 8192; insert into test_a_test select * FROM test_a limit 10000;

创建视图

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create materialized view test_mview engine = SummingMergeTree Partition by toYYYYMM(create_time) order by (create_time, intHash32(user_id)) as SELECT user_id, create_time, count(score), sum(deleted) from test_a_test ta WHERE create_time >= toDate(0) group by user_id,create_time ; show tables; >>>结果 .inner_id.5bfba660-812e-49ec-885f-3fa63e16f2f4 -- 默认存储数据的表格 test_a test_a_test test_mview view_test_a

插入数据

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT * from test_mview;-- 第一次查询结果为空 insert into test_a_test select * FROM test_a limit 10; SELECT * from test_mview;-- 插入后在查询有10条新增数据 select * from `.inner_id.068a0cde-c260-4fee-b902-c7f74cc4f194`; -- 自动创建的表中也有数据

导入历史数据(重点!!!)

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
insert into test_mview -- 将物化视图的逻辑再写一遍 SELECT user_id, create_time, count(score), sum(deleted) from test_a_test ta WHERE create_time >= toDate(0) group by user_id,create_time ;

最后

以上就是拉长汽车最近收集整理的关于ClickHouse学习笔记Click House入门数据类型表引擎SQL操作副本执行计划Click House高级Explain查看执行计划建表优化Click House语法优化规则查询优化数据一致性(重点!!!)物化视图的全部内容,更多相关ClickHouse学习笔记Click内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(91)

评论列表共有 0 条评论

立即
投稿
返回
顶部