ClickHouse SQL 的十项优化规则

众所周知，ClickHouse 的 SQL 优化规则是基于RBO(Rule Based Optimization)的，那么你知道都有哪些优化规则吗 ？

接下来的内容，是我在去年年底整理的十项优化规则，不一定全，仅做抛砖引玉之用。如果各位有补充，欢迎私信我。

1. COUNT 优化 :

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

EXPLAIN
SELECT count()
FROM test_x


Query id: d255fb14-7160-4f1a-9148-9810494d792d


┌─explain──────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))          │
│   MergingAggregated                                  │
│     ReadFromPreparedSource (Optimized trivial count) │
└──────────────────────────────────────────────────────┘

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

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

EXPLAIN
SELECT count(id)
FROM test_x


Query id: 170b10db-88d7-45a1-ae8a-8d683707b635


┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                   │
│   Aggregating                                                                 │
│     Expression (Before GROUP BY)                                              │
│       SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│         ReadFromStorage (MergeTree)                                           │
└───────────────────────────────────────────────────────────────────────────────┘

2. 消除子查询重复字段:

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

EXPLAIN SYNTAX
SELECT
    a.id,
    b.name,
    a.price,
    b.id
FROM id_join_tb1 AS a
LEFT JOIN 
(
    SELECT
        id,
        id,
        name,
        time
    FROM join_tb1
) AS b USING (id)


Query id: 6879ecc6-8579-4f01-964c-9eab4b15687a


┌─explain───────────────┐
│ SELECT                │
│     id,               │
│     name,             │
│     price,            │
│     b.id              │
│ FROM id_join_tb1 AS a │
│ ALL LEFT JOIN         │
│ (                     │
│     SELECT            │
│         id,           │
│         name          │
│     FROM join_tb1     │
│ ) AS b USING (id)     │
└───────────────────────┘

3. 谓词下推:

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

EXPLAIN SYNTAX
SELECT name
FROM join_tb1
GROUP BY name
HAVING name = ''


Query id: 6eb2f8eb-2e29-43ae-9414-5914b921a622


┌─explain─────────┐
│ SELECT name     │
│ FROM join_tb1   │
│ WHERE name = '' │
│ GROUP BY name   │
└─────────────────┘

同样的，子查询也支持谓词下推，例如下面语句的 WHERE id = 10:

EXPLAIN SYNTAX
SELECT *
FROM 
(
    SELECT id
    FROM id_join_tb1
)
WHERE id = 10


Query id: 44a3e084-4b8a-4847-9909-ec34c8d8be74


┌─explain──────────────┐
│ SELECT id            │
│ FROM                 │
│ (                    │
│     SELECT id        │
│     FROM id_join_tb1 │
│     WHERE id = 10    │
│ )                    │
│ WHERE id = 10        │
└──────────────────────┘

再来一个例子:

EXPLAIN SYNTAX
SELECT *
FROM 
(
    SELECT *
    FROM 
    (
        SELECT id
        FROM id_join_tb1
    )
    UNION ALL
    SELECT *
    FROM 
    (
        SELECT id
        FROM id_join_tb1
    )
)
WHERE id = 10


Query id: a807c968-a4b9-4f84-a80d-48c8385d2206


┌─explain──────────────────┐
│ SELECT id                │
│ FROM                     │
│ (                        │
│     SELECT id            │
│     FROM                 │
│     (                    │
│         SELECT id        │
│         FROM id_join_tb1 │
│         WHERE id = 10    │
│     )                    │
│     WHERE id = 10        │
│     UNION ALL            │
│     SELECT id            │
│     FROM                 │
│     (                    │
│         SELECT id        │
│         FROM id_join_tb1 │
│         WHERE id = 10    │
│     )                    │
│     WHERE id = 10        │
│ )                        │
│ WHERE id = 10            │
└──────────────────────────┘

4. 聚合计算外推:

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

EXPLAIN SYNTAX
SELECT sum(id * 2)
FROM join_tb1


Query id: 027a5dce-fa57-447a-9615-888881069d61


┌─explain────────────┐
│ SELECT sum(id) * 2 │
│ FROM join_tb1      │
└────────────────────┘

5. 聚合函数消除:

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

EXPLAIN SYNTAX
SELECT
    sum(id * 2),
    max(name),
    max(id)
FROM join_tb1
GROUP BY id


Query id: 4d72f7fa-5146-4365-adc4-260566f5f414


┌─explain──────────┐
│ SELECT           │
│     sum(id) * 2, │
│     max(name),   │
│     id           │
│ FROM join_tb1    │
│ GROUP BY id      │
└──────────────────┘

6. 删除重复的 group by key

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

EXPLAIN SYNTAX
SELECT *
FROM join_tb1
ORDER BY
    id ASC,
    id ASC,
    name ASC,
    name ASC


Query id: 3fc0267a-9bf7-4811-b384-4a9e90517bbf


┌─explain───────┐
│ SELECT        │
│     id,       │
│     name,     │
│     time      │
│ FROM join_tb1 │
│ ORDER BY      │
│     id ASC,   │
│     name ASC  │
└───────────────┘

7. 删除重复的 limit by key

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

EXPLAIN SYNTAX
SELECT *
FROM join_tb1
LIMIT 3 BY
    name,
    name
LIMIT 10


Query id: e87a0ed9-66b4-49c7-b6ea-b5c8ad3d7901


┌─explain─────────┐
│ SELECT          │
│     id,         │
│     name,       │
│     time        │
│ FROM join_tb1   │
│ LIMIT 3 BY name │
│ LIMIT 10        │
└─────────────────┘

8. 删除重复的 USING Key

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

EXPLAIN SYNTAX
SELECT
    a.id,
    a.id,
    b.name,
    a.price,
    b.id
FROM id_join_tb1 AS a
LEFT JOIN join_tb1 AS b USING (id, id)


Query id: d0917046-71da-469e-b738-14d947bf53e3


┌─explain────────────────────────────────┐
│ SELECT                                 │
│     id,                                │
│     id,                                │
│     name,                              │
│     price,                             │
│     b.id                               │
│ FROM id_join_tb1 AS a                  │
│ ALL LEFT JOIN join_tb1 AS b USING (id) │
└────────────────────────────────────────┘

9. 标量替换

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


Query id: a9c7431f-cd51-4a85-9fba-b6301578a8cd


┌─explain────────────────────────────────────────────────────────┐
│ WITH identity(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                                                       │
└────────────────────────────────────────────────────────────────┘

10. 三元运算优化

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

EXPLAIN SYNTAX 
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') 
FROM numbers(10) 
settings optimize_if_chain_to_multiif = 1


Query id: fd5cde0f-a73f-4763-b823-42f9367f658b


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

这些规则你都 Get 到了吗?

作者：朱凯，ClickHouse贡献者之一，ClickHouse布道者，资深架构师，《ClickHouse原理解析与应用实战》《企业级大数据平台构建：架构与实现》作者，

十多年IT从业经验，对大数据领域主流技术与解决方案有深入研究，擅长分布式系统的架构设计与整合。曾主导过多款大数据平台级产品的规划、设计与研发工作，一线实战经验丰富。

RECOMMEND

推荐阅读

《ClickHouse原理解析与应用实战》

这是一本可帮助读者深度理解并全面掌握ClickHouse运行原理并进行实践开发的工具书，涵盖了ClickHouse的时代背景、发展历程、核心概念、基础功能、运行原理、实践指导等多个维度的内容，尤其是在ClickHouse最核心的部分——MergeTree表引擎与分布式方面，书中对其实现原理和应用技巧进行了详细解读。

扫码关注【华章计算机】视频号

每天来听华章哥讲书

更多精彩回顾

书讯 | 6月书讯 | 初夏，正好读新书

资讯 | 《数据安全法》表决通过！最新解读来了

书单 | 8本书助你零基础转行数据分析岗

干货 | 阿里云官方出品：全面总结阿里云云原生架构方法论与实践经验

收藏 | 手把手教你利用JS给图片打马赛克

上新 | 【新书速递】深入浅出联邦学习

赠书 | 【第59期】架构师成长必读书

点击阅读全文购买