SRE文档

深浅模式

1.explain ​

官当

方法一（20.6之前版本）：

在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看

sql
clickhouse-client -u xxxx --password xxxxxx --send_logs_level=trace <<< 'your query sql' > /dev/null；
clickhouse-client -u xxxx --password xxxxxx --send_logs_level=trace <<< 'your query sql' > /dev/null；

方法二（20.6与20.6之后版本）：explain SQL

sql
比如：

explain plan <sql语句>;
比如：

explain plan <sql语句>;

1.1 explain类型组成 ​

AST ​

用于查看语法树,不常使用

SYNTAX ​

查询优化后的 SQL 语句(使用较多)

QUERY TREE ​

PLAN

用于查看执行计划，可以指定五个参数

参数描述
header打印计划中各个步骤的 header 说明，默认 0（关闭）
description打印计划中各个步骤的描述，默认 1（开启）
indexes显示索引使用情况，默认 0（关闭）
actions打印计划中各个步骤的详细信息，默认 0（关闭）
json以 JSON 格式打印执行计划的详细信息，默认 0（关闭）
sql
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

PIPELINE ​

用于查看 pipeline 计划

  • header：打印计划中各个步骤的 header 说明，默认 0（关闭）；
  • graph：用 DOT 图形语言描述管道图，默认 0（关闭）；
  • compact：如果开启了 graph，紧凑打印行，默认 1（开启）；

1.2 语法 ​

sql
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

1.3 案例 ​

1.plan ​

sql
explain [plan] select arrayJoin([1,2,3,null,null]);

explain select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,table order by database,cnt desc limit 2 by database;

explain plan header=1, actions=1,description=1 select number from system.numbers limit 10;
explain [plan] select arrayJoin([1,2,3,null,null]);

explain select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,table order by database,cnt desc limit 2 by database;

explain plan header=1, actions=1,description=1 select number from system.numbers limit 10;

2.syntax ​

sql
-- 先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 第一次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 返回优化后的语句
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'xyz')) FROM numbers(10);

-- 开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;

-- 再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 返回优化后的语句
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz') FROM numbers(10);
-- 先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 第一次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 返回优化后的语句
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'xyz')) FROM numbers(10);

-- 开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;

-- 再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);

-- 返回优化后的语句
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz') FROM numbers(10);

3.pipeline ​

sql
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;

--打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;

--打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;

2. 建表优化 ​

2.1 数据类型 ​

1.时间类型字段 ​

  • ClickHouse 中建表时不建议使用string
  • ClickHouse 中建表时日期、时间字段最好设置成对应的 Date 、Datetime 类型，避免后续需要经过函数转换处理，执行效率高、可读性好
sql
create table t_type2(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 create_time Datetime
) engine =ReplacingMergeTree(create_time)
 partition by toYYYYMMDD(create_time) --不需要转换
 primary key (id)
 order by (id, sku_id);
create table t_type2(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 create_time Datetime
) engine =ReplacingMergeTree(create_time)
 partition by toYYYYMMDD(create_time) --不需要转换
 primary key (id)
 order by (id, sku_id);

2.空值类型字段 ​

  • ClickHouse 中使用 Nullable 来设置字段可以有空值，但是会拖累性能

  • 存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，增加消耗；Nullable 列无法被索引

  • 生产上建议：使用字段默认值或者在业务中无意义的值来表示空值

2.2 分区和索引 ​

  • 建表时要考虑设置分区，避免全表扫描，一般选择按天分区，如果不按天分区，则建议以单表一亿数据为例，分区大小控制在 10-30 个为最佳

  • 建表时必须指定 order by 字段，即索引、排序列；在指定多个索引列时将查询频率大的字段放最前面，然后基数特别大的字段不适合做索引列，如用户表的 userid 字段，通常筛选后的数据满足在百万以内为最佳

2.3 表参数 ​

  • Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整
  • 指定 TTL：对于不必须保存历史全量数据的表，建议指定对应的 TTL，可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改

2.4 写入和删除优化 ​

  • 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力

  • 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）

  • 写入过快报错(Too many parts 和 Memory limit)处理：

    • “Too many parts 处理”：使用 WAL 预写日志，提高写入性能，in_memory_parts_enable_wal 默认为 true
    • 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现
    • 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现

3. ck查询优化 ​

官当

1.索引优化：clickhouse 的存储结构决定对于大数据量查询时，使用主键索引能够精确的找到所需的数据块，减少不必要的数据块扫描，这样更够极大的提高查询效率。将用户pin作为一级索引，将时间作为二级索。

2.填充有空值的字段：对于一些表字段，若存在空值，则可以考虑使用无业务场景意义的字符进行填充。

3.主键查询：减少查询字段：将select * 改为查询关键字段，select operate_time,id。

4.多线程：多条活动数据，多线程查询出活动和奖励的相关信息。

5.分区存储：将每7天数据放入一个分区中，查询时候根据不同时间查询不同分区的数据。

6.count优化：因为clickhouse对每个表的数据量，在底层文件中提供了预数据。所以能直接使用count()则避免使用count(col_name)。

7.聚合外推：如将sum(money * 2) 变成 sum(money) * 2。对数据库来说，后者的计算量明显少一点。

8.高级函数：ClickHouse中有很多很好用的函数。如：使用multiIf()替代多重case when，对于版本数据的获取使用argMax()函数，而非用子查询关联取最大值**。**

https://blog.csdn.net/weixin_44480009/article/details/139922122