如何优化ClickHouse中以太坊转账表的SELECT查询执行速度？

算力阿峰

2026-6-15

ClickHouse以太坊转账表查询性能优化方案

环境与表结构

使用ClickHouse 24.11，当前表结构如下：

CREATE TABLE ethereum.transfers
(
    `blockNumber` UInt64,
    `transactionIndex` UInt32,
    `transactionHash` LowCardinality(FixedString(66)) CODEC(ZSTD(3)),
    `timestamp` UInt32,
    `address` LowCardinality(Nullable(FixedString(42))),
    `from_` LowCardinality(Nullable(FixedString(42))),
    `to_` LowCardinality(Nullable(FixedString(42))),
    `logIndex` Nullable(UInt32),
    INDEX transfers_multi_address (address, from_, to_) TYPE set(100) GRANULARITY 4,
    INDEX transfers_from_block_skip (from_, blockNumber) TYPE bloom_filter GRANULARITY 4,
    INDEX transfers_to_block_skip (to_, blockNumber) TYPE bloom_filter GRANULARITY 4,
    PROJECTION transfers_address_search
    (
        SELECT *
        ORDER BY
            from_,
            to_,
            blockNumber,
            transactionIndex
    )
)
ENGINE = MergeTree
PRIMARY KEY blockNumber
ORDER BY (blockNumber, transactionIndex)
SETTINGS index_granularity = 8192, min_rows_for_wide_part = 1000000, min_rows_for_compact_part = 10000, min_bytes_for_compact_part = 10000000

目标查询

需要执行的查询有两种写法：

写法一：OR条件单查询

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE (from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205' or to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205') and blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500

写法二：UNION ALL拆分查询

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134

UNION ALL

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500;

性能问题现状

当前查询返回1296行，耗时0.290秒，但处理了6385万行数据（总表数据量为1.0868亿行）：

SELECT count(*)
FROM transfers

Query id: 46c6e414-ec2f-457b-9028-09e76dba1426

   ┌───count()─┐
1. │ 108681712 │ -- 1.0868亿行

执行计划分析

执行计划显示，UNION ALL的第二个分支（to_条件查询）未有效利用索引，扫描全部13272个颗粒，仅通过bloom_filter跳过部分颗粒：

┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Union                                                                                                                                                              │
 2. │   Filter                                                                                                                                                           │
 3. │     ReadFromMergeTree (transfers_address_search)                                                                                                                   │
 4. │     Indexes:                                                                                                                                                       │
 5. │       PrimaryKey                                                                                                                                                   │
 6. │         Keys:                                                                                                                                                      │
 7. │           from_                                                                                                                                                    │
 8. │           blockNumber                                                                                                                                              │
 9. │         Condition: and((blockNumber in (-Inf, 21405134]), (from_ in ['0xda8cf15bc458a7b13735e7d04495f8cda53f3205', '0xda8cf15bc458a7b13735e7d04495f8cda53f3205'])) │
10. │         Parts: 12/12                                                                                                                                               │
11. │         Granules: 12/13258                                                                                                                                         │
12. │   Expression (Project names)                                                                                                                                       │
13. │     Limit (preliminary LIMIT (without OFFSET))                                                                                                                     │
14. │       Sorting (Sorting for ORDER BY)                                                                                                                               │
15. │         Expression ((Before ORDER BY + Projection))                                                                                                                │
16. │           Expression                                                                                                                                               │
17. │             ReadFromMergeTree (ethereum.transfers)                                                                                                                 │
18. │             Indexes:                                                                                                                                               │
19. │               PrimaryKey                                                                                                                                           │
20. │                 Keys:                                                                                                                                              │
21. │                   blockNumber                                                                                                                                      │
22. │                 Condition: (blockNumber in (-Inf, 21405134])                                                                                                       │
23. │                 Parts: 15/15                                                                                                                                       │
24. │                 Granules: 13272/13272                                                                                                                              │
25. │               Skip                                                                                                                                                 │
26. │                 Name: transfers_multi_address                                                                                                                      │
27. │                 Description: set GRANULARITY 4                                                                                                                     │
28. │                 Parts: 15/15                                                                                                                                       │
29. │                 Granules: 13272/13272                                                                                                                              │
30. │               Skip                                                                                                                                                 │
31. │                 Name: transfers_to_block_skip                                                                                                                      │
32. │                 Description: bloom_filter GRANULARITY 4                                                                                                            │
33. │                 Parts: 9/15                                                                                                                                        │
34. │                 Granules: 7782/13272                                                                                                                               │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

优化思路

拆分投影适配分支查询：当前transfers_address_search投影仅能高效支持from_前缀查询，拆分创建两个专属投影，让两个查询分支都能匹配投影直接读取有序数据，避免全局扫描和排序：
```
PROJECTION transfers_from_search
(
    SELECT *
    ORDER BY from_, blockNumber DESC, transactionIndex ASC
),
PROJECTION transfers_to_search
(
    SELECT *
    ORDER BY to_, blockNumber DESC, transactionIndex ASC
)
```
替换跳过索引类型提升精准度：将bloom_filter类型的transfers_from_block_skip和transfers_to_block_skip改为set类型，避免bloom_filter的误判问题，同时降低索引粒度（如设为1），提升跳过效率：
```
INDEX transfers_from_block_skip (from_, blockNumber) TYPE set(100) GRANULARITY 1,
INDEX transfers_to_block_skip (to_, blockNumber) TYPE set(100) GRANULARITY 1
```

强制指定投影避免执行计划误选：在UNION ALL的两个分支中显式指定对应投影，确保ClickHouse使用最优执行路径：

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers FINAL PROJECTION transfers_from_search
WHERE from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134

UNION ALL

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers FINAL PROJECTION transfers_to_search
WHERE to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500;

调整排序键适配高频查询：如果地址类查询是核心场景，可将排序键改为(blockNumber, from_, to_, transactionIndex)，让主键索引更好地支持from_/to_的过滤逻辑，平衡范围查询和地址查询的性能。
添加分区键减少扫描范围：按blockNumber做分区（例如每10万区块一个分区），让ClickHouse先过滤掉不符合blockNumber<=21405134的分区，直接减少需要扫描的Part数量。
物化视图预存高频数据：若该地址查询是高频需求，可创建物化视图专门存储该地址的转账记录，或按from_/to_维度做预聚合，权衡存储成本和查询效率。

内容的提问来源于stack exchange，提问作者sirjay

如何优化ClickHouse中以太坊转账表的SELECT查询执行速度？

环境与表结构 #

目标查询 #

写法一：OR条件单查询 #

写法二：UNION ALL拆分查询 #

性能问题现状 #

执行计划分析 #

优化思路 #