ClickHouse-语法优化规则

最新推荐文章于 2024-01-10 07:11:42 发布

魔笛Love

最新推荐文章于 2024-01-10 07:11:42 发布

阅读量674

点赞数 1

文章标签：服务器 sql 数据库

本文链接：https://blog.csdn.net/clearlxj/article/details/121851690

版权

ClickHouse 语法优化规则

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

准备测试用表

1)上传官方的数据集

将 visits_v1.tar 和 hits_v1.tar 上传到服务器,解压到 clickhouse 数据路径下

## 解压到 clickhouse 数据路径
sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse
sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse

## 修改所属用户
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets

## 建表语句如下
ATTACH TABLE hits_v1
(
    ...
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192

2)重启 clickhouse-server

sudo clickhouse restart

3)执行查询

clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"
8873898

clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"
1676861

注意:官方的 tar 包,包含了建库、建表语句、数据内容,这种方式不需要手动建库、建表,最方便。

hits_v1 表有 130 多个字段, 880 多万条数据

visits_v1 表有 180 多个字段, 160 多万条数据

COUNT 优化

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

EXPLAIN SELECT count()FROM datasets.hits_v1;
┌─explain──────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))          │
│   MergingAggregated                                  │
│     ReadFromPreparedSource (Optimized trivial count) │
└──────────────────────────────────────────────────────┘

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

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

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                   │
│   Aggregating                                                                 │
│     Expression (Before GROUP BY)                                              │
│       SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│         ReadFromMergeTree                                                     │
└───────────────────────────────────────────────────────────────────────────────┘

消除子查询重复字段

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

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

┌─explain───────────────┐
│ 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 提前过滤。having是在group by之后执行的，where是在group by之前执行的，having下推就相当于将having变成了where。

例如下面的查询,HAVING name 变成了 WHERE name,在 group by 之前过滤:

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';
┌─explain──────────────────────────────┐
│ SELECT UserID                        │
│ FROM hits_v1                         │
│ WHERE UserID = '8585742290196126178' │
│ GROUP BY UserID                      │
└──────────────────────────────────────┘

子查询也支持谓词下推:

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

┌─explain──────────────────────────────────┐
│ SELECT UserID                            │
│ FROM                                     │
│ (                                        │
│     SELECT UserID                        │
│     FROM visits_v1                       │
│     WHERE UserID = '8585742290196126178' │
│ )                                        │
│ WHERE UserID = '8585742290196126178'     │
└──────────────────────────────────────────┘

再来一个复杂例子:

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

┌─explain──────────────────────────────────────┐
│ SELECT UserID                                │
│ FROM                                         │
│ (                                            │
│     SELECT UserID                            │
│     FROM                                     │
│     (                                        │
│         SELECT UserID                        │
│         FROM visits_v1                       │
│         WHERE UserID = '8585742290196126178' │
│     )                                        │
│     WHERE UserID = '8585742290196126178'     │
│     UNION ALL                                │
│     SELECT UserID                            │
│     FROM                                     │
│     (                                        │
│         SELECT UserID                        │
│         FROM visits_v1                       │
│         WHERE UserID = '8585742290196126178' │
│     )                                        │
│     WHERE UserID = '8585742290196126178'     │
│ )                                            │
│ WHERE UserID = '8585742290196126178'         │
└──────────────────────────────────────────────┘

聚合计算外推

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

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

┌─explain────────────────┐
│ SELECT sum(UserID) * 2 │
│ FROM visits_v1         │
└────────────────────────┘

聚合函数消除

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

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

┌─explain──────────────┐
│ SELECT               │
│     sum(UserID) * 2, │
│     max(VisitID),    │
│     UserID           │
│ FROM visits_v1       │
│ GROUP BY UserID      │
└──────────────────────┘

删除重复的 order by key

例如下面的语句,重复的聚合键 id 字段会被去重:

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

┌─explain──────────────┐
│ SELECT               │
│     ...              │
│ FROM visits_v1       │   
│ ORDER BY             │
│     UserID ASC,      │
│     VisitID ASC      │
└──────────────────────┘

删除重复的 limit by key

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

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

┌─explain──────────────┐
│ SELECT               │
│     ...              │
│ FROM visits_v1       │   
│ LIMIT 3 BY VisitID   │
│ LIMIT 10             │
└──────────────────────┘

删除重复的 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)

┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     UserID,                                 │
│     UserID,                                 │
│     VisitID,                                │
│     URL,                                    │
│     b.UserID                                │
│ FROM hits_v1 AS a                           │
│ ALL LEFT JOIN visits_v1 AS b USING (UserID) │
└─────────────────────────────────────────────┘

标量替换

标量：对于表来说只有一行数据，或者只有一个值。

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

┌─explain─────────────────────────────────────────────────────────────────────────┐
│ WITH identity(CAST(0, 'UInt64')) AS total_disk_usage                            │
│ SELECT                                                                          │
│     (sum(bytes_on_disk AS bytes) / total_disk_usage) * 100 AS table_disk_usage, │
│     table                                                                       │
│ FROM system.parts                                                               │
│ GROUP BY table                                                                  │
│ ORDER BY table_disk_usage DESC                                                  │
│ LIMIT 10                                                                        │
└─────────────────────────────────────────────────────────────────────────────────┘

三元运算优化

如果开启了 optimize_if_chain_to_multiif 参数,三元运算符会被替换成 multiIf 函数,例如:

EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu'))
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1

┌─explain─────────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu') │
│ FROM numbers(10)                                                    │
│ SETTINGS optimize_if_chain_to_multiif = 1                           │
└─────────────────────────────────────────────────────────────────────┘

魔笛Love

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
ClickHouse-语法优化规则

ClickHouse 语法优化规则ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization),下面是一些优化规则准备测试用表1)上传官方的数据集将 visits_v1.tar 和 hits_v1.tar 上传到服务器,解压到 clickhouse 数据路径下## 解压到 clickhouse 数据路径sudo tar -xvf hits_v1.tar -C /var/lib/clickhousesudo tar -xvf visits_v1.ta
复制链接

扫一扫