ClickHouse SQL 的十项优化规则
ClickHouse SQL 的十项优化规则
Nauu
发布于 2021-07-07 15:18:24
发布于 2021-07-07 15:18:24
众所周知，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 到了吗?
好了，今天的分享就到这里吧，原创不易，如果这篇文章对你有帮助，欢迎 点赞、转发、在看 三连击
欢迎大家扫码关注我的公众号和视频号:
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2021-06-02，如有侵权请联系 cloudcommunity@tencent.com 删除
本文分享自 ClickHouse的秘密基地 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！
评论
登录后参与评论
推荐阅读