clickhouse学习笔记(二)
2024-07-10 17:23:26 # clickhouse # 学习笔记 #clickhouse

clickhouse学习笔记(二)

EXPLAIN查看执行计划

1
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]

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

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

AST ：用于查看语法树;

SYNTAX：用于优化语法;

PIPELINE：用于查看 PIPELINE 计划。

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

查看执行计划

1
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;

alt text
可以来查看执行的顺序

1
2
3
4
EXPLAIN header = 1, actions = 1, description = 1
SELECT number
FROM system.numbers
LIMIT 10

alt text
更加详细的查看执行计划

AST语法树

1
EXPLAIN AST SELECT number from system.numbers limit 10;

alt text

只做了解

语法优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);
//查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);

//这里的语法优化会返回下面这个内容:
//SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu')) FROM numbers(10) 

//开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;
//再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);

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

建表优化

数据类型

时间字段类型

在clickhouse中能不用String就不用String，DateTime函数不许与转换，执行效率高

1
2
3
4
5
6
7
8
9
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);

空值存储类型

最好不使用null，使用-1或者其他默认值来代替null

分区和索引

分区一般是按照天分区

单表1亿数据，分区大概10-30个最好

必须指定order by的索引列，索引列一般是查询中经常被当作筛选条件的属性会放进来，可以是单一维度，也可以是组合维度，满足查询频率大的在前的原则

表参数

索引粒度建议不要更改

如果表不是必须要保留全量的历史数据建议指定TTL

写入和删除优化

当写入过快报错：

1
2
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

in_memory_parts_enable_wal默认为true，使用wal预写日志，提高写入性能

常见配置

主要在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/

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等），默认值100，推荐150（不够再加）~300。
max_threads 设置单个查询所能使用的最大cpu个数，默认是cpu核数。
内存资源
配置 描述
max_memory_usage 此参数在 users.xml 中，表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，比如128G内存的机器，设置为100GB。
max_bytes_before_external_group_by 一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 ClickHouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议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，这样不管多大的分区表都可以删除。

Clickhuose语法优化规则

官网有准备的测试数据，可以使用官网的测试数据进行测试

Count优化

当使用count()/count(*)的时候，而不是count(字段)，且没有 where 条件，就会直接使用 system.tables 的 total_rows

消除子查询的重复字段

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
EXPLAIN SYNTAX SELECT 
   a.UserID,
   b.VisitID,
   a.URL,
   b.UserID
   FROM
   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

谓词下推

当group by中有having子句，但是没有with cube，with rollup或者with totals的时候，having过滤会先下推到where去提前过滤，然后再group by，提高性能

示例

1
2
3
4
5
6
7
EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';

//返回优化语句
SELECT UserID
FROM hits_v1
WHERE UserID = \'8585742290196126178\'
GROUP BY UserID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXPLAIN SYNTAX
SELECT *
FROM 
(
    SELECT UserID
    FROM visits_v1
)
WHERE UserID = '8585742290196126178'

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

聚合计算外推

1
2
3
4
5
6
7
EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

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

聚合函数消除

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
XPLAIN 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

s删除重复的limit by key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BY
    VisitID,
    VisitID
LIMIT 10


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

删除重复的Using key

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
    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 (UserID) 指定了连接条件，即两个表 hits_v1 和 visits_v1 都有一个名为 UserID 的列，连接时使用这个列进行匹配。

三元运算优化

1
2
3
4
5
6
7
8
9
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

查询优化

单表查询

Prewhere替代where

Prewhere只支持MergeTree引擎的表，当查询列多余筛选列的时候使用prewhere可十倍提高性能

数据采样

1
2
3
4
5
6
SELECT Title,count(*) AS PageViews 
FROM hits_v1
SAMPLE 0.1         #代表采样10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

随机采样百分之10的数据

列裁剪和分区裁剪

数据量太大的时候避免使用select * 的操作，查询的性能会跟随字段越少，消耗的io越少，资源越高

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
反例：
select * from datasets.hits_v1;
正例：
select WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID
from datasets.hits_v1;

分区裁剪是指定where分区，只读取需要的分区

orderby结合where和limit

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

1
2
3
4
5
6
7
8
9
10
#正例：
SELECT UserID,Age
FROM hits_v1        
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000

#反例：
SELECT UserID,Age
FROM hits_v1        
ORDER BY Age DESC

不要构建虚拟列

1
2
3
4
5
反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
正例：拿到Income和Age后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;

uniqCombined替代distinct

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

使用物化视图

详见后文

其他注意事项

  1. 配置join_use_nulls

    这个是配置了如果当join没有join上的话返回的不是null而是默认值

    1
    2
    3
    4
    SET join_use_nulls = 1;
    SELECT ...
    FROM hits_v1 AS a
    LEFT JOIN visits_v1 AS b ON a.UserID = b.UserID;

  2. 关闭虚拟内存

    当物理内存和虚拟内存的数据进行交换的时候会导致查询变慢，资源允许的话关闭虚拟内存

  3. 批量写入时先排序

    当批量写入数据之前最好对导入的数据先排序，会更快的使clickhouse进行合并

多表关联

准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#创建小表
CREATE TABLE visits_v2 
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

#创建join结果表：避免控制台疯狂打印数据
CREATE TABLE hits_v2 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;

这里的第二个语句中的where 1 = 0是一个永久的假条件，目的是直接按照hits_v1的表结构创建hits_v2，避免控制台打印数据，这样就可以只创建表结构，但是不插入数据了

用IN代替JOIN

当多表联查的时候，查询的数据仅仅从一张表出，那么就可以使用in而不使用join

1
2
3
4
5
6
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

clickhouse中不管是left join还是right join还是inner join都是小表必须在右边

1
2
3
insert into table hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID;

分布式表使用global

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

使用字典表

可以在clickhouse中创建一个外部表的形式来创建一个表

比如我们现在有一张表在mysql中，我们需要在clickhouse中创建一个这个mysql的外部表，关联这个外部表做处理

示例

在mysql中执行下面的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE dic_test_db;

USE dic_test_db;

CREATE TABLE loc_info
(
    local_id INT UNSIGNED NOT NULL PRIMARY KEY,
    local_name VARCHAR(255) NOT NULL
);

INSERT INTO loc_info (local_id, local_name) VALUES
(1, 'Location A'),
(2, 'Location B'),
(3, 'Location C');

在clickhouse中执行下面的操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE DICTIONARY dic_loc_info
(
    local_id UInt64,
    local_name String
)
PRIMARY KEY local_id
SOURCE(MYSQL(
    HOST 'mysql_host'        -- MySQL服务器地址
    PORT 3306                -- MySQL服务器端口
    USER 'mysql_user'        -- MySQL用户名
    PASSWORD 'mysql_password' -- MySQL用户密码
    DB 'dic_test_db'         -- MySQL数据库名
    TABLE 'loc_info'         -- MySQL表名
))
LIFETIME(MIN 300 MAX 3600)  -- 设置字典数据的最小和最大刷新时间（秒）
LAYOUT(HASHED());            -- 使用哈希布局

这里的字典数据的刷新时间根据业务需求来定

查询的时候需要在clickhouse中使用dictGet函数来进行查询数据

1
SELECT dictGet('dic_loc_info', 'local_name', toUInt64(1)) AS location_name;

这个查询将返回 local_id 为1的记录的 local_name。

这样我们就在clickhouse中创建了一个外部的字典表

字典数据通常被加载到内存中，用于小型且频繁访问的数据。这可以节省存储空间和I/O操作，提高查询效率。

数据一致性(重点)

我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂数据不一致的情况。
在某些对一致性非常敏感的场景，通常有以下几种解决方案。

准备数据：

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 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;

# 写入 1000万 测试数据
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);

# 修改前 50万 行数据，修改内容包括 name 字段和 create_time 版本号字段
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);

# 统计总数
SELECT COUNT() FROM test_a;

# 10500000

  1. 手动进行OPTIMIZE

    不推荐这种方式，每次插入新数据的时候都手动合并一次

    1
    OPTIMIZE TABLE test_a FINAL;

  2. 通过group by去重

    1. 执行去重的查询 
      1
      2
      3
      4
      5
      6
      7
      8
        SELECT
        user_id ,
        argMax(score, create_time) AS score, 
        argMax(deleted, create_time) AS deleted,
        max(create_time) AS ctime 
      FROM test_a 
      GROUP BY user_id
      HAVING deleted = 0;
      argMax(field1，field2):按照 field2 的最大值取 field1 的值。

    当我们更新数据时，会写入一行新的数据，例如上面语句中，通过查询最大的 create_time 得到修改后的score字段值。

    1. 创建视图，方便测试

      视图不存储数据，只存储操作，当调用视图的时候也就是把一个复杂操作重新执行一遍

      1
      2
      3
      4
      5
      6
      7
      8
      9
      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 
      GROUP BY user_id
      HAVING deleted = 0;

    2. 插入重复数据，再次查询

      1
      2
      3
      4
      5
      6
      7
       #再次插入一条数据
      INSERT INTO TABLE test_a(user_id,score,create_time) VALUES(0,'AAAA',now())

      #再次查询
      SELECT *
      FROM view_test_a
      WHERE user_id = 0;

    3. 删除数据测试

      1
      2
      3
      4
      5
      6
      7
      #再次插入一条标记为删除的数据
      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;

这行数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合 表级别的 TTL 最终将物理数据删除。

其实这种方式就是拿insert去模仿更新数据和删除数据的操作，这种方式是比较推荐的

  1. 通过FINAL查询

不使用final查询的情况：
alt text

这个执行计划显示查询将由2个线程并行读取数据，读取操作是并行进行的，因此能够加快读取速度。

使用final查询的情况：
alt text

MergeTree 0 → 1:

从MergeTree表读取数据，但这一部分是串行执行的。

这个执行计划显示在执行 FINAL 查询时，数据读取阶段仍然是串行的，但是在后续的数据处理阶段（如表达式转换和数据合并排序）是并行的。

这就是为什么 FINAL 查询比普通查询慢，因为它涉及更多的数据处理步骤，而且读取数据时没有并行化。

物化视图

它就是一张表，它也像是一张时刻在预计算的表。物化视图不会随着基础表的变化而变化，所以它也称为快照

物化视图和普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句
普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表

优缺点

优点：查询快
缺点：占资源

基本语法

1
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

限制：

  • 必须指定物化视图的engine
  • 生产环境不使用POPULATE，这个会把源表中的所有数据都insert到视图中。如果想把数据都导入到视图中还不如使用insert把数据都导入进去

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#建表语句 
CREATE TABLE hits_test
(
    EventDate Date, 
    CounterID UInt32, 
    UserID UInt64, 
    URL String, 
    Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
1
2
3
4
5
6
7
8
9
INSERT INTO hits_test 
    SELECT 
    EventDate,
    CounterID,
    UserID,
    URL,
    Income 
FROM hits_v1 
limit 10000;

创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#建表语句 
CREATE MATERIALIZED VIEW hits_mv 
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) 
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2014-03-20' #设置更新点,该时间点之前的数据可以另外通过							      	 #insert into select …… 的方式进行插入
GROUP BY UserID,EventDate;

##或者可以用下列语法，表A可以是一张mergetree表
CREATE MATERIALIZED VIEW 物化视图名 TO 表A 
AS SELECT FROM 表B;

#不建议添加populate关键字进行全量更新

这里我们刚刚创建了物化视图是没有数据的，物化视图是需要增量后面的数据进入视图，也可以使用insert into 的方式导入历史数据

导入增量数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#导入增量数据
INSERT INTO hits_test 
SELECT 
    EventDate,
    CounterID,
    UserID,
    URL,
    Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;

#查询物化视图
SELECT * FROM hits_mv;

alt text

导入历史数据

1
2
3
4
5
6
7
8
9
10
11
12
13
#导入历史数据
INSERT INTO hits_mv
SELECT
    UserID,
    EventDate,
    count(URL) as ClickCount,
    sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate

#查询物化视图
SELECT * FROM hits_mv;

alt text

MaterializeMysql引擎

现在我们很多场景，为了增加数据的实时性，使用binlog的形式把mysql写入到clickhouse做后面的数据计算处理。clickhouse增加列MaterializeMysql的database粒度的引擎，这个database可以映射到mysql上的一个database，在clickhouse创建对应的ReplacingMergeTree。ck作为mysql的副本，读取binlog执行DDL和DML，数据库实时同步

上一页
2024-07-10 17:23:26 # clickhouse # 学习笔记 #clickhouse
下一页