Java技术债务Java技术债务

  •  首页
  •  分类
  •  归档
  •  标签
  • 博客日志
  • 资源分享
  •  友链
  •  关于本站
注册
登录

ClickHouse之建表优化和底层语法优化(谓词下推等)

ClickHouse,数据库

文章目录

  • 一 建表优化
  • 二 CK底层语法优化

一 建表优化

1 数据类型

1.1 时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串,全 String 类型在以 Hive 为中心的数仓建设中常见,但 ClickHouse 环境不应受此影响。

虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型,但不建议存储 Long 类型, 因为 DateTime 不需要经过函数转换处理,执行效率高、可读性好。

create table t_type2(
   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);

1.2 空值存储类型

官方已经指出Nullable类型几乎总是会拖累性能,因为存储Nullable列时需要创建一个
额外的文件来存储 NULL 的标记,并且 Nullable 列无法被索引。因此除非极特殊情况,应直
接使用字段默认值表示空,或者自行指定一个在业务中无意义的值(例如用-1 表示没有商品
ID)。

CREATE TABLE t_null(
    x Int8, 
    y Nullable(Int8)
) ENGINE TinyLog;

INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT x + y FROM t_null;

官网说明:https://clickhouse.tech/docs/zh/sql-reference/data-types/nullable/

2 分区和索引

分区粒度根据业务特点决定,不宜过粗或过细。一般选择按天分区,也可以指定为 Tuple(), 以单表一亿数据为例,分区大小控制在 10-30 个为最佳。

必须指定索引列,ClickHouse 中的索引列即排序列,通过 order by 指定,一般在查询条 件中经常被用来充当筛选条件的属性被纳入进来;可以是单一维度,也可以是组合维度的索 引;

通常需要满足高级列在前、查询频率大的在前原则;
基数特别大的不适合做索引列, 如用户表的 userid 字段;
通常筛选后的数据满足在百万以内为最佳。

比如官方案例的 hits_v1 表:

......
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate, intHash32(UserID))
......

visits_v1 表:

......
    PARTITION BY toYYYYMM(StartDate)
    ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) 
......

3 表参数

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

4 写入和删除优化

(1)尽量不要执行单条或小批量删除和插入操作,这样会产生小分区文件,给后台 Merge 任务带来巨大压力
(2)不要一次写入太多分区,或数据写入太快,数据写入太快会导致 Merge 速度跟不 上而报错,一般建议每秒钟发起 2-3 次写入操作,每次操作写入 2w~5w 条数据(依服务器 性能而定)

写入过快的报错信息:

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).
Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)
exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888
bytes), maximum: 9.31 GiB

处理方式:

“ Too many parts 处理 ” :使用 WAL 预写日志,提高写入性能。
in_memory_parts_enable_wal 默认为 true
在服务器内存充裕的情况下增加内存配额,一般通过 max_memory_usage 来实现

在服务器内存不充裕的情况下,建议将超出部分内容分配到系统硬盘上,但会降低执行速度,一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数
来实现。

5 常见配置

配置项主要在 config.xml 或 users.xml 中, 基本上都在 users.xml 里
➢ config.xml的配置项
https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/
➢ users.xml的配置项
https://clickhouse.tech/docs/en/operations/settings/settings/

5.1 CPU 资源

5.2 内存资源

5.3 存储

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

虚拟券组:将多个物理磁盘虚拟化一个磁盘组,对外提供读和写

二 CK底层语法优化

1 count优化

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

EXPLAIN SELECT count()FROM datasets.hits_v1;
Union
  Expression (Projection)
   Expression (Before ORDER BY and SELECT)
     MergingAggregated
       ReadNothing (Optimized trivial count)

注意 Optimized trivial count ,这是对 count 的优化。

如果 count 具体的列字段,则不会使用此项优化:

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
Union
  Expression (Projection)
   Expression (Before ORDER BY and SELECT)
     Aggregating
       Expression (Before GROUP BY)
        ReadFromStorage (Read from MergeTree)

2消除子查询重复字段

下面语句子查询中有两个重复的 id 字段,会被去重:

EXPLAIN SYNTAX SELECT
  a.UserID,
  b.VisitID,
  a.URL,
  b.UserIDFROM
  hits_v1 AS a
  LEFT JOIN (
   SELECT
     UserID,
     UserID as HaHa,
     VisitID
   FROM visits_v1) AS b
  USING (UserID)
  limit 3;
//返回优化语句: 
SELECT
   UserID,
   VisitID,
   URL,
   b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN
(
   SELECT
       UserID,
       VisitID
   FROM visits_v1
) AS b USING (UserID)
LIMIT 3

3 谓词下推

当 group by 有 having 子句,但是没有 with cube、with rollup 或者 with totals 修饰的时 候,having 过滤会下推到 where 提前过滤。例如下面的查询,HAVING name 变成了 WHERE name,在 group by 之前过滤:

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 
GROUP BY UserID HAVING UserID = '8585742290196126178';

//返回优化语句
SELECT UserID
FROM hits_v1
WHERE UserID = \'8585742290196126178\' GROUP BY UserID

子查询也支持谓词下推:

EXPLAIN SYNTAX
SELECT *
FROM
(
   SELECT UserID
   FROM visits_v1
)
WHERE UserID = '8585742290196126178'

//返回优化后的语句 SELECT UserID FROM
(
   SELECT UserID
   FROM visits_v1WHERE UserID = \'8585742290196126178\'
)
WHERE UserID = \'8585742290196126178\'

4 聚合计算外推

聚合函数内的计算,会外推,例如:

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

//返回优化后的语句 
SELECT sum(UserID) * 2 FROM visits_v1

5 聚合函数消除

如果对聚合键,也就是 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

6 删除重复的 order by key

重复的聚合键 id 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
ORDER BY
   UserID ASC,
   UserID ASC,
   VisitID ASC,
   VisitID ASC

//返回优化后的语句: select
    ......
FROM visits_v1
ORDER BY
   UserID ASC,
   VisitID ASC

7 删除重复的 limit by key

重复声明的 name 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BY
        VisitID,
      VisitID
LIMIT 10

//返回优化后的语句:select ......
FROM visits_v1
LIMIT 3 BY VisitID
LIMIT 10

8 删除重复的 USING Key

重复的关联键 id 字段会被去重:

EXPLAIN SYNTAX
SELECT
   a.UserID,
   a.UserID,
   b.VisitID,
   a.URL,
   b.UserID
FROM hits_v1 AS a
LEFT JOIN visits_v1 AS b USING (UserID, UserID)

//返回优化后的语句: 
SELECT
   UserID,
   UserID,
   VisitID,
   URL,
   b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN visits_v1 AS b USING (UserID)

注意:如果USING里是不同表相同字段是不会被删除的比如:a.id b.id

9 标量替换

如果子查询只返回一行数据,在被引用的时候用标量替换,例如下面语句中的total_disk_usage 字段:

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 CAST(0, \'UInt64\') AS total_disk_usage SELECT
   (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
   table
FROM system.parts
GROUP BY tableORDER BY table_disk_usage DESC
LIMIT 10

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 multiIf(number = 1, \'hello\', number = 2, \'world\', \'atguigu\') FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1;

在此次查询语句加上settings optimize_if_chain_to_multiif = 1;只是影响本次查询,并不会影响其他查询

一 建表优化

1 数据类型

1.1 时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串,全 String 类型在以 Hive 为中心的数仓建设中常见,但 ClickHouse 环境不应受此影响。

虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型,但不建议存储 Long 类型, 因为 DateTime 不需要经过函数转换处理,执行效率高、可读性好。

create table t_type2(
   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);

1.2 空值存储类型

官方已经指出Nullable类型几乎总是会拖累性能,因为存储Nullable列时需要创建一个
额外的文件来存储 NULL 的标记,并且 Nullable 列无法被索引。因此除非极特殊情况,应直
接使用字段默认值表示空,或者自行指定一个在业务中无意义的值(例如用-1 表示没有商品
ID)。

CREATE TABLE t_null(
    x Int8, 
    y Nullable(Int8)
) ENGINE TinyLog;

INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT x + y FROM t_null;

官网说明:https://clickhouse.tech/docs/zh/sql-reference/data-types/nullable/

2 分区和索引

分区粒度根据业务特点决定,不宜过粗或过细。一般选择按天分区,也可以指定为 Tuple(), 以单表一亿数据为例,分区大小控制在 10-30 个为最佳。

必须指定索引列,ClickHouse 中的索引列即排序列,通过 order by 指定,一般在查询条 件中经常被用来充当筛选条件的属性被纳入进来;可以是单一维度,也可以是组合维度的索 引;

通常需要满足高级列在前、查询频率大的在前原则;
基数特别大的不适合做索引列, 如用户表的 userid 字段;
通常筛选后的数据满足在百万以内为最佳。

比如官方案例的 hits_v1 表:

......
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate, intHash32(UserID))
......

visits_v1 表:

......
    PARTITION BY toYYYYMM(StartDate)
    ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) 
......

3 表参数

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

4 写入和删除优化

(1)尽量不要执行单条或小批量删除和插入操作,这样会产生小分区文件,给后台 Merge 任务带来巨大压力
(2)不要一次写入太多分区,或数据写入太快,数据写入太快会导致 Merge 速度跟不 上而报错,一般建议每秒钟发起 2-3 次写入操作,每次操作写入 2w~5w 条数据(依服务器 性能而定)

写入过快的报错信息:

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).
Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)
exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888
bytes), maximum: 9.31 GiB

处理方式:

“ Too many parts 处理 ” :使用 WAL 预写日志,提高写入性能。
in_memory_parts_enable_wal 默认为 true
在服务器内存充裕的情况下增加内存配额,一般通过 max_memory_usage 来实现

在服务器内存不充裕的情况下,建议将超出部分内容分配到系统硬盘上,但会降低执行速度,一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数
来实现。

5 常见配置

配置项主要在 config.xml 或 users.xml 中, 基本上都在 users.xml 里
➢ config.xml的配置项
https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/
➢ users.xml的配置项
https://clickhouse.tech/docs/en/operations/settings/settings/

5.1 CPU 资源

5.2 内存资源

5.3 存储

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

虚拟券组:将多个物理磁盘虚拟化一个磁盘组,对外提供读和写

二 CK底层语法优化

1 count优化

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

EXPLAIN SELECT count()FROM datasets.hits_v1;
Union
  Expression (Projection)
   Expression (Before ORDER BY and SELECT)
     MergingAggregated
       ReadNothing (Optimized trivial count)

注意 Optimized trivial count ,这是对 count 的优化。

如果 count 具体的列字段,则不会使用此项优化:

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
Union
  Expression (Projection)
   Expression (Before ORDER BY and SELECT)
     Aggregating
       Expression (Before GROUP BY)
        ReadFromStorage (Read from MergeTree)

2消除子查询重复字段

下面语句子查询中有两个重复的 id 字段,会被去重:

EXPLAIN SYNTAX SELECT
  a.UserID,
  b.VisitID,
  a.URL,
  b.UserIDFROM
  hits_v1 AS a
  LEFT JOIN (
   SELECT
     UserID,
     UserID as HaHa,
     VisitID
   FROM visits_v1) AS b
  USING (UserID)
  limit 3;
//返回优化语句: 
SELECT
   UserID,
   VisitID,
   URL,
   b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN
(
   SELECT
       UserID,
       VisitID
   FROM visits_v1
) AS b USING (UserID)
LIMIT 3

3 谓词下推

当 group by 有 having 子句,但是没有 with cube、with rollup 或者 with totals 修饰的时 候,having 过滤会下推到 where 提前过滤。例如下面的查询,HAVING name 变成了 WHERE name,在 group by 之前过滤:

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 
GROUP BY UserID HAVING UserID = '8585742290196126178';

//返回优化语句
SELECT UserID
FROM hits_v1
WHERE UserID = '8585742290196126178' GROUP BY UserID

子查询也支持谓词下推:

EXPLAIN SYNTAX
SELECT *
FROM
(
   SELECT UserID
   FROM visits_v1
)
WHERE UserID = '8585742290196126178'

//返回优化后的语句 SELECT UserID FROM
(
   SELECT UserID
   FROM visits_v1WHERE UserID = '8585742290196126178'
)
WHERE UserID = '8585742290196126178'

4 聚合计算外推

聚合函数内的计算,会外推,例如:

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

//返回优化后的语句 
SELECT sum(UserID) * 2 FROM visits_v1

5 聚合函数消除

如果对聚合键,也就是 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

6 删除重复的 order by key

重复的聚合键 id 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
ORDER BY
   UserID ASC,
   UserID ASC,
   VisitID ASC,
   VisitID ASC

//返回优化后的语句: select
    ......
FROM visits_v1
ORDER BY
   UserID ASC,
   VisitID ASC

7 删除重复的 limit by key

重复声明的 name 字段会被去重:

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BY
        VisitID,
      VisitID
LIMIT 10

//返回优化后的语句:select ......
FROM visits_v1
LIMIT 3 BY VisitID
LIMIT 10

8 删除重复的 USING Key

重复的关联键 id 字段会被去重:

EXPLAIN SYNTAX
SELECT
   a.UserID,
   a.UserID,
   b.VisitID,
   a.URL,
   b.UserID
FROM hits_v1 AS a
LEFT JOIN visits_v1 AS b USING (UserID, UserID)

//返回优化后的语句: 
SELECT
   UserID,
   UserID,
   VisitID,
   URL,
   b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN visits_v1 AS b USING (UserID)

注意:如果USING里是不同表相同字段是不会被删除的比如:a.id b.id

9 标量替换

如果子查询只返回一行数据,在被引用的时候用标量替换,例如下面语句中的total_disk_usage 字段:

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 CAST(0, 'UInt64') AS total_disk_usage SELECT
   (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
   table
FROM system.parts
GROUP BY tableORDER BY table_disk_usage DESC
LIMIT 10

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 multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu') FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1;

在此次查询语句加上settings optimize_if_chain_to_multiif = 1;只是影响本次查询,并不会影响其他查询

完
  • 本文作者:Java技术债务
  • 原文链接: https://cuizb.top/myblog/article/1639667835
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。
阅读全文
Java技术债务

Java技术债务

Java技术债务
Java技术债务
热门文章
  1. ClickHouse使用过程中的一些查询优化(六)2003
  2. MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据764
  3. MySQL主从同步原理458
  4. 线程池的理解以及使用414
  5. Spring Cloud Gateway整合nacos实战(三)409
分类
  • Java
    30篇
  • 设计模式
    27篇
  • 数据库
    20篇
  • Spring
    18篇
  • MySQL
    13篇
  • ClickHouse
    11篇
  • Kubernetes
    10篇
  • Redis
    9篇
  • Docker
    8篇
  • SpringBoot
    7篇
  • JVM
    6篇
  • Linux
    5篇
  • Spring Cloud
    5篇
  • 多线程
    5篇
  • Netty
    4篇
  • Kafka
    4篇
  • 面经
    4篇
  • Nginx
    3篇
  • JUC
    3篇
  • 随笔
    2篇
  • 分布式
    1篇
  • MyBatis
    1篇
  • 报错合集
    1篇
  • 生活记录
    1篇
  • 源码
    1篇
  • 性能优化
    1篇

最新评论

  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-05-06
    Java技术债务:@capture 一起探讨学习,服务器被黑很正常,及时做好备份以及做好防护
  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-04-13
    capture:我的刚上线两天,网站里就两篇文章也被攻击了,纳闷
  • Java常用集合List、Map、Set介绍以及一些面试问题2022-01-18
    Java技术债务:HashSet和TreeSet 相同点:数据不能重复 不同点: 1、底层存储结构不同; HashSet底层使用HashMap哈希表存储 TreeSet底层使用TreeMap树结构存储 2、唯一性方式不同 HashSet底层使用hashcode()和equal()方法判断 TreeSet底层使用Comparable接口的compareTo判断的 3、HashSet无序,TreeSet有序
  • undefined2021-12-14
    Java技术债务:如果不指定线程池,CompletableFuture会默认使用ForkJoin线程池,如果同一时间出现大量请求的话,会出现线程等待问题,建议使用自定义线程池。。。
  • undefined2021-12-02
    you:很好,对于小白相当不错了,谢谢
  • CSDN
  • 博客园
  • 程序猿DD
  • 纯洁的微笑
  • spring4all
  • 廖雪峰的官方网站
  • 猿天地
  • 泥瓦匠BYSocket
  • crossoverJie
  • 张先森个人博客
  • 越加网

© 2021-2022 Java技术债务 - Java技术债务 版权所有
总访问量 0 次 您是本文第 0 位童鞋
豫ICP备2021034516号
Java技术债务 豫公网安备 51011402000164号

微信公众号

Java技术债务
Java技术债务

专注于Spring,SpringBoot等后端技术探索

以及MySql数据库开发和Netty等后端流行框架学习

日志
分类
标签
RSS

有不足之处也希望各位前辈指出