ClickHouse——语法优化、查询优化

作者: 小波同学 | 来源:发表于2023-03-04 00:33 被阅读0次

ClickHouse索引查询优化
ClickHouse最近点查询优化
MySQL性能调优
《高性能Mysql》-查询优化
MySQL（五）｜《千万级大数据查询优化》第二篇：查询性能优化（
mysql优化
mysql查询优化02-查看执行计划
MySQL优化系列8-MySQL的执行计划介绍
单表的访问方法
Day2:MySQL慢查询基础-查询慢原因

一、ClickHouse语法优化规则

ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization)，下面是一些优化规则。

1.1、准备测试用表

下载官方数据集 hits_v1.tar和visits_v1.tar

[root@aliyun /]# curl -O https://datasets.clickhouse.tech/hits/partitions/hits_v1.tar
[root@aliyun /]# curl -O https://datasets.clickhouse.tech/visits/partitions/visits_v1.tar

解压到ClickHouse数据路径

[root@aliyun /]# sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse
[root@aliyun /]# sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse

授权

[root@aliyun /]# sudo chown -R 777 /var/lib/clickhouse/data/datasets
[root@aliyun /]# sudo chown -R 777 /var/lib/clickhouse/metadata/datasets

重启clickhouse-server

[root@aliyun /]# sudo clickhouse restart

注意：官方的tar包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。
hits_v1表有130多个字段，880多万条数据
visits_v1表有180多个字段，160多万条数据

1.2、COUNT 优化

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

explain select count() from datasets.hits_v1;

注意：Optimized trivial count ，这是对 count 的优化。如果 count 具体的列字段，则不会使用此项优化：

explain select count(CounterID) from datasets.hits_v1;

说明：由于使用 count() 和 count(1) 底层会自动优化为 count() ，所有 count() 和 count(1) 也可以进行自动优化；

EXPLAIN syntax SELECT count(*) FROM datasets.hits_v1;
SELECT count()
FROM datasets.hits_v1;

EXPLAIN  syntax SELECT count(1) FROM datasets.hits_v1;
SELECT count()
FROM datasets.hits_v1;

1.3、消除重复字段

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

EXPLAIN SYNTAX
SELECT a.UserID,
       b.VisitID,
       a.URL,
       b.UserID
FROM datasets.hits_v1 AS a
         LEFT JOIN (
    SELECT UserID,
           UserID as HaHa,
           VisitID
    FROM datasets.visits_v1) AS b
                   USING (UserID)
limit 3;


//优化后结果
SELECT
    UserID,
    VisitID,
    URL,
    b.UserID
FROM datasets.hits_v1 AS a
ALL LEFT JOIN
(
    SELECT
        UserID,
        VisitID
    FROM datasets.visits_v1
) AS b USING (UserID)
LIMIT 3

1.4、谓词下推

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

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


//优化后
SELECT UserID
FROM datasets.hits_v1
WHERE UserID = '8585742290196126178'
GROUP BY UserID

子查询也支持谓词下推：

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


//优化后效果
SELECT UserID
FROM (SELECT UserID
      FROM datasets.visits_v1
      WHERE UserID = '8585742290196126178')
WHERE UserID = '8585742290196126178'

再来一个复杂的例子：

EXPLAIN SYNTAX
SELECT *
FROM (SELECT *
      FROM (SELECT UserID
            FROM datasets.visits_v1)
      UNION ALL
      SELECT *
      FROM (SELECT UserID
            FROM datasets.visits_v1))
WHERE UserID = '8585742290196126178';


//优化后效果
SELECT UserID
FROM
(
    SELECT UserID
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
        WHERE UserID = '8585742290196126178'
    )
    WHERE UserID = '8585742290196126178'
    UNION ALL
    SELECT UserID
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
        WHERE UserID = '8585742290196126178'
    )
    WHERE UserID = '8585742290196126178'
)
WHERE UserID = '8585742290196126178'；

1.5、聚合计算外推

聚合函数内的计算，会外推，例如：

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM datasets.visits_v1;


//优化后效果
SELECT sum(UserID) * 2
FROM datasets.visits_v1

1.6 聚合函数消除

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

EXPLAIN SYNTAX
SELECT sum(UserID * 2),
       max(VisitID),
       max(UserID)
FROM datasets.visits_v1
GROUP BY UserID;


//返回优化后的语句
SELECT sum(UserID) * 2,
       max(VisitID),
       UserID
FROM datasets.visits_v1
GROUP BY UserID

1.7、删除重复的 order by key

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


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

1.8、删除重复的 limit by key

例如下面的语句，重复声明的 name 字段会被去重

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


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

1.9、删除重复的 USING Key

例如下面的语句，重复的关联键 id 字段会被去重：

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


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

1.10、标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的 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 table
ORDER BY table_disk_usage DESC
LIMIT 10

1.11、三元运算优化

如果开启了 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；

二、查询优化

ClickHouse在做SQL查询时要尽量遵循的原则

1.大表在左，小表在右，否则会造成右表加载数据量太大，大量消耗内存资源；
2.如果join的右表为大表，则需要将右表写成子查询，在子查询中将右表的条件都加上，并进行列裁剪，这样可以有效减少数据加载；
3.where条件中只放左表的条件，如果放右表的条件将在下推阶段右表条件不会生效，将右表条件放到join的子查询中去。

select ...
from t_all
join ( -- 右表本身直接走本地表
  select ...
  from t_local
  where t_local.filter = xxx -- 尽可能手动将条件放在子查询中
)
where
  t_local.f = xxx -- 当前版本不支持自动下推到JOIN查询中，需要手动修改
  and t_all.f in (
    select ... from xxx -- 若能将子查询作为筛选条件更佳
  )

2.1 单表查询优化

2.1.1 Prewhere替代where

Prewhere和where语句的作用相同，用来过滤数据。不同之处在于prewhere只支持 MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select 声明的列字段来补全其余属性。

当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。

在某些场合下，prewhere语句比where语句处理的数据量更少性能更高。

EXPLAIN SYNTAX
select WatchID,
    JavaEnable,
    Title,
    GoodEvent,
    EventTime,
    EventDate,
    CounterID,
    ClientIP,
    ClientIP6,
    RegionID,
    UserID,
    CounterClass,
    OS,
    UserAgent,
    URL,
    Referer,
    URLDomain,
    RefererDomain,
    Refresh,
    IsRobot,
    RefererCategories,
    URLCategories,
    URLRegions,
    RefererRegions,
    ResolutionWidth,
    ResolutionHeight,
    ResolutionDepth,
    FlashMajor,
    FlashMinor,
    FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';

自动优化 from datasets.hits_v1  PREWHERE UserID = '3198390223272470366';

set optimize_move_to_prewhere=0; 关闭自动优化后不会转变 PREWHERE 默认情况，我们肯定不会关闭where自动优化成prewhere，但是某些场景即使开启优化，也不会自动转换成prewhere，需要手动指定prewhere：

使用常量表达式
使用默认值为alias类型的字段
包含了arrayJOIN，globalIn，globalNotIn或者indexHint的查询
select查询的列字段和where的谓词相同
使用了主键字段

2.1.2 数据采样

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

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 engine表中才有效，且在创建表时需要指定采样策略。

2.1.3 列裁剪与分区裁剪

数据量太大时应避免使用select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的io资源越少，性能就会越高。

反例：
select * from datasets.hits_v1;
正例：
select WatchID,
    JavaEnable,
    Title,
    GoodEvent,
    EventTime,
    EventDate,
    CounterID,
    ClientIP,
    ClientIP6,
    RegionID,
    UserID
from datasets.hits_v1;

分区裁剪就是只读取需要的分区，在过滤条件中指定。

select WatchID,
    JavaEnable,
    Title,
    GoodEvent,
    EventTime,
    EventDate,
    CounterID,
    ClientIP,
    ClientIP6,
    RegionID,
    UserID
from datasets.hits_v1
where EventDate='2022-08-23';

2.1.4 orderby 结合 where、limit

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

#正例：
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

2.1.5 避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

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

2.1.6 uniqCombined替代distinct

性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用uniqExact精确去重。不建议在千万级不同数据上执行distinct去重查询，改为近似去重uniqCombined

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

2.1.7 其他注意事项

1、查询熔断
为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。
2、关闭虚拟内存
物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。
3、配置join_use_nulls
为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值。
4、批量写入时先排序
批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能。
5、关注CPU
cpu一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，cpu是最关键的指标，要非常关注。

2.2 多表查询优化

2.2.1 准备测试数据

#创建小表
CREATE TABLE datasets.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 datasets.visits_v1
limit 10000;

#创建join结果表：避免控制台疯狂打印数据
CREATE TABLE datasets.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 datasets.hits_v1
where 1 = 0;

2.2.2 用 IN 代替 JOIN

当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是JOIN

#正例：使用join
insert into hits_v2
select a.*
from datasets.hits_v1 a
where a. CounterID in (select CounterID from datasets.visits_v1);

#反例：使用join
insert into table hits_v2
select a.*
from datasets.hits_v1 a
         left join datasets.visits_v1 b on a. CounterID = b. CounterID;

2.2.3 大小表JOIN

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

小表在右

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

反例：大表在右

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

2.2.4 注意谓词下推（版本差异）

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

1、having会自动优化为prewhere

Explain syntax
select a.* from datasets.hits_v1 a left join datasets.visits_v2 b on a. CounterID=b. CounterID
having a.EventDate = '2014-03-17';

优化后的语句：

SELECT a.* 
FROM datasets.hits_v1 AS a 
ALL LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID 
PREWHERE a.EventDate = '2014-03-17';

2、尽量在join之前进行过滤
子查询里where：

insert into datasets.hits_v2
select a.* from (
select * from 
datasets.hits_v1 
where EventDate = '2014-03-17'
) a left join datasets.visits_v2 b on a. CounterID=b. CounterID;

join完再where：

insert into datasets.hits_v2
select a.* from datasets.hits_v1 a left join datasets.visits_v2 b on a. CounterID=b. 
CounterID
where a.EventDate = '2014-03-17';

2.2.5 分布式表使用GLOBAL

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

2.2.6 使用字典表&提前过滤

将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典表会常驻内存；通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。

参考：
https://blog.csdn.net/qq_40378034/article/details/120473116

https://www.cnblogs.com/wdh01/p/16877602.html

https://www.cnblogs.com/wdh01/p/16877668.html

https://www.cnblogs.com/wdh01/p/16877825.html

https://www.cnblogs.com/wdh01/p/16877866.html

https://www.cnblogs.com/chuijingjing/p/17087160.html

ClickHouse索引查询优化
索引简介 clickhouse主键即索引，不同于mysql，clickhouse主键可以重复。 clickhous...
ClickHouse最近点查询优化
方案一暴力全表扫描方案二使用geohash进行表分区，然后搜索最近的geohash，最后扫描...
MySQL性能调优
MYSQL查询语句优化 mysql的性能优化包罗甚广：索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬...
《高性能Mysql》-查询优化
优化性能需要查询优化、索引优化、库表结构优化这三辆马车齐头并进。这篇文章主要围绕查询优化，要对查询进行优化首先需要...
MySQL（五）｜《千万级大数据查询优化》第二篇：查询性能优化（
MySQL优化一般是需要索引优化、查询优化、库表结构优化三驾马车齐头并进。本章节开始讲查询优化。一、为什么查询速...
mysql优化
MYSQL优化为查询缓存优化你的查询 EXPLAIN你的SELECT查询。根据结果给出分析相应的查询优化当只要...
mysql查询优化02-查看执行计划
查询优化处理查询优化的3个阶段解析器:将sql变成解析树预处理器:检查解析树的语法是否正确如表或字段是否存在...
MySQL优化系列8-MySQL的执行计划介绍
备注:测试数据库版本为MySQL 8.0 一.使用EXPLAIN优化查询 1.1 Explain语法及概述语法:...
单表的访问方法
MySQL Server 在对一条查询语句进行语法解析之后，就会将其交给优化器来优化先建立一个表： const ...
Day2:MySQL慢查询基础-查询慢原因
说在前面：查询优化、索引优化、库表结构优化是查询性能优化的三驾马车。完成一个完整的查询生命周期，查询需要在...