ClickHouse实际使用篇

发表于 2021-12-15 更新于 2021-12-21 分类于大数据

深一步理解ClickHouse优化方式

Explain

执行语法

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

PLAN 查看执行计划
    header 打印计划中各个步骤的head说明
    description 打印计划中各个步骤的描述
    actions 打印计划中各个步骤的详细信息
AST 用于查看语法树
SYNTAX 用于优化语法
PIPELINE 用于查看PIPELINE计划
    header 打印计划中各个步骤的head说明
    graph 用DOT图形语言描述管道图,需要查看相关的图形需要配合graphviz查看；
    actions 如果开启了graph,紧凑打印

实例

简单查询
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 header=1, actions=1,description=1 SELECT number from 
system.numbers limit 10;

AST 语法树
EXPLAIN AST SELECT number from system.numbers limit 10;

SYNTAX 语法优化
初始查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM 
numbers(10);
查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (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', 'xyz')
FROM numbers(10)

查看PIPELINE
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;

建表优化

数据类型

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

空值存储类型
    Nullable类型会拖累性能,存储Nullable列需要创建一个额外的文件存储NULL的标记
    Nullable列无法被索引
    最好使用默认值表示空,或定义一个业务无意义的值
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;

分区索引

1
2
3

单表1亿数据下,分区大小控制在10-30个为最佳
CK中索引列即排序列,通过order by指定
基数特别大的不适合作为索引列,通过筛选后数据满足百万以内为最佳

表参数

1
2
3

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

写入删除优化

尽量不要执行单条或小批量删除和插入操作,会产生小分区,带来Merge任务压力
不要一次写入太多分区或写入数据太快,写入太快会导致Merge速度跟不上报错
建议每秒2-3次写入操作,每次操作2w-5w数据(按服务器性能而定)

如果出现报错
服务器内存充裕的情况下增加内存配额,一般通过max_memory_usage来实现
服务器内存不充裕的情况下,建议将超出部分内容分配到系统硬盘上,但会降低执行速度
    max_bytes_before_external_group_by
    max_bytes_before_external_sort

常见配置

config.xml 的配置项
    https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/
users.xml 的配置项
    https://clickhouse.tech/docs/en/operations/settings/settings/

语法优化规则

测试用表

1
2
3

下载官方提供的测试数据表
hits
visits

Count优化

1 2	因为有提前的count文件,所以只要不用具体字段其实就是直接读取的count文件

消除子查询重复字段

1	对于子查询中重复字段会进行去除操作

谓词下推

1
2
3

having会下推到where处,进行提前过滤
子查询也会进行下推操作
Union all加where条件过滤会进行下推操作

聚合计算外推

1	聚合函数内的计算操作,会放在外面计算

聚合函数消除

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

删除重复的order by

1	重复的排序字段会自动消除

删除重复的limit by key

1	重复的声明字段会被消除

删除重复的using key

1	重复的关联键会被消除

标量替换

1	当子查询只返回一行数据,那么在被使用时会被标量进行替代

三元运算优化

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;

查询优化

单表查询

#Prewhere替代where
关闭where自动转prewhere(默认情况下,where条件会自动优化成prewhere)
set optimize_move_to_prewhere=0;

select *
from table
prewhere expr;

以下情况需要手动指定prewhere
    使用了常量表达式
    使用了默认值为alias类型的字段
    包含了arrayJOIN,globalIn,globalNotIn或者indexHint的查询
    select查询的列字段和where的谓词相同
    使用了主键字段

---

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

---

#列裁剪与分区裁剪
字段越少,消耗IO资源越少,性能越高

---

#orderby结合where和limit
查询搭配限制条件查看数据

---

#避免构建虚拟列
最好不要在表内进行计算虚拟列
select a,b,a/b as c;
可以构造实际字段进行额外存储

---

#uniqCombined替代distinct
uniqCombined底层采用类似HyperLogLog算法实现,能接收 2%左右的数据误差,可直接使用这种去重方式提升查询性能
count(distinct)会使用uniqExact精确去重

---

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

---

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

---

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

---

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

---

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

多表关联

#数据准备
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;

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;

---

#IN代替JOIN
多表关联时,查询的数据仅从其中一张表出时,可以使用IN操作
insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from 
visits_v1);

---

#大小表JOIN
多表JOIN时要满足小表在右的原则

---

#注意谓词下推
CK在JOIN查询时不会发起谓词下推,需要提前在子查询完成过滤操作

---

#分布式表使用GLOBAL
两张分布式表的IN和JOIN之前必须加上GLOBAL
右表只会在接收查询请求的那个节点查询一次,并分发到其他节点
否则每个节点都会单独发起一次对右表的查询,右边又是分布式表
导致右表会被查询N^2次,造成查询放大

---

#使用字典表
将需要关联分析的业务创建为字典表进行join操作,字典表不宜太大

---

#提前过滤
增加逻辑过滤可以减少数据扫描

数据一致性

ReplacingMergeTree

与MergeTree的区别
    会删除排序键值相同的重复项
数据去重只会在数据合并期间进行,合并会在后台一个不确定的时间进行
即使可以调用OPTIMEZE语句发起计划外的合并,但会引发对数据的大量读写

不保证没有重复的数据出现

测试
创建测试表
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;

写入测试数据
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行数据
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(1) FROM test_a;
数量为10500000,没有触发分区合并,所以未去重

手动OPTIMIZE
OPTIMIZE TABLE test_a FINAL;

可以通过FINAL关键词进行查询
select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100 
settings max_final_threads = 2;
FINAL查询支持多线程执行,并且可以通过max_final_threads参数控制单个查询的线程数

删除标记使用

#查询语句
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函数,根据create_time时间取最新的数据
#如果最新的删除标记是1,那么就查询不到数据
#这样可以做到不实际删除数据的效果(更新效果也实现了)

#创建视图,方便测试
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;

#更新操作,插入重复数据,再次查询
INSERT INTO TABLE test_a(user_id,score,create_time)
VALUES(0,'AAAA',now());

#可以看到数据是更新了的
SELECT *
FROM view_test_a
WHERE user_id = 0;

#删除操作
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将物理数据删除

物化视图

优缺点

优:
    查询快,计算逻辑提前写好,总数据行数少
缺:
    一张表加了大量物化视图,会导致服务器突然带宽占满,存储增加

语法

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

限制
必须指定物化视图的ENGINE用于数据存储
TO[db.]name语法时,不得使用POPULATE
查询语句可以包含子句(DISTINCT,GROUP BY,ORDER BY,LIMIT)
物化视图的alter操作有限制,不方便操作
物化视图定义使用了TO[db.]name,则可以将目标表的视图卸载再装载

更新
物化视图创建好之后,若源表被写入新数据则物化视图也会同步更新
POPULATE关键字决定了物化视图的更新策略
    若有POPULATE则在创建视图的过程会将源表已经存在的数据一并导入,类似于create table ... as
    若无POPULATE则物化视图在创建之后没有数据,只会在创建只有同步之后写入源表的数据
    Clickhouse官方并不推荐使用POPULATE,因为在创建物w化视图的过程中同时写入的数据不能被插入物化视图
物化视图不支持同步删除,若源表的数据不存在(删除了)则物化视图的数据仍然保留
物化视图是一种特殊的数据表,可以用show tables查看
物化视图数据的删除
物化视图的删除

实操

#建表语句
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;

#导入数据
INSERT INTO hits_test 
SELECT 
    EventDate,
    CounterID,
    UserID,
    URL,
    Income 
FROM hits_v1 
limit 10000;

#建立物化视图
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'
GROUP BY UserID,EventDate;
##或者可以用下列语法,表A可以是一张mergetree表
CREATE MATERIALIZED VIEW 物化视图名 TO 表A
AS SELECT FROM 表B;
#不建议添加populate关键字进行全量更新

#增量数据导入
INSERT INTO hits_test 
SELECT 
    EventDate,
    CounterID,
    UserID,
    URL,
    Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;

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

MaterializeMySQL引擎

概述

ClickHouse20.8.2.3版本新增加了MaterializeMySQL的Database引擎
该Database能映射到MySQL中的某个Database,并自动在ClickHouse中创建对应的ReplacingMergeTree
ClickHouse服务做为MySQL副本,读取Binlog并执行DDL和DML请求
实现了基于MySQL Binlog机制的业务数据库实时同步功能

---

#特点
1.MaterializeMySQL同时支持全量和增量同步
    在Database创建之初会全量同步MySQL中的表和数据
    之后则会通过binlog进行增量同步
2.MaterializeMySQL Database为其所创建的每张ReplacingMergeTree自动增加了_sign和_version字段
    _version用作ReplacingMergeTree的ver版本参数
    每当监听到insert,update和delete事件时在databse内全局自增
    而_sign则用于标记是否被删除,取值1或者-1
    目前MaterializeMySQL支持如下几种binlog事件:
        MYSQL_WRITE_ROWS_EVENT: _sign = 1, _version ++
        MYSQL_DELETE_ROWS_EVENT: _sign = -1, _version ++
        MYSQL_UPDATE_ROWS_EVENT: 新数据 _sign = 1
        MYSQL_QUERY_EVENT: 支持 CREATE TABLE,DROP TABLE,RENAME TABLE等
        
---

#使用细则
1.MySQL DDL查询被转换成相应的ClickHouse DDL查询
    如果ClickHouse不能解析某些DDL查询,该查询将被忽略
2.MaterializeMySQL不支持直接插入,删除和更新查询,而是将DDL语句进行相应转换
    MySQL INSERT 查询被转换为 INSERT with _sign=1
    MySQL DELETE 查询被转换为 INSERT with _sign=-1
    MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1
3.如果在SELECT查询中没有指定_version,则使用FINAL修饰符,返回_version 的最大值对应的数据,即最新版本的数据
    如果在SELECT查询中没有指定_sign,则默认使用WHERE _sign=1,即返回未删除状态(_sign=1)的数据
4.ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组

实操

# 确保MySQL开启了binlog功能,且格式为ROW
server-id=1 
log-bin=mysql-bin
binlog_format=ROW

# 开启GTID模式(主从一致性)
gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志

#MySQL数据
CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `code` int NOT NULL,
    `name` text DEFAULT NULL,
    `updatetime` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`code`)
) ENGINE=InnoDB;

INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1002,'EDT',NOW());

CREATE TABLE `testck`.`t_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `code` int,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);

#开启CK物化引擎
set allow_experimental_database_materialize_mysql=1;

#创建复制管道
CREATE DATABASE test_binlog ENGINE = 
MaterializeMySQL('hadoop1:3306','testck','root','000000');
    MySQL地址,DataBase,Username,Password

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;

#修改MySQL数据
update t_organization set name = CONCAT(name,'-v1') where id = 1;

#查看CK内数据
select * from t_organization;

#删除MySQL数据
DELETE FROM t_organization where id = 2;

#查看CK内虚拟字段
select *,_sign,_version from t_organization order by _sign 
desc,_version desc;

常见问题参考

阿里云ClickHouse文档