字节跳动基于ClickHouse优化实践之“多表关联查询”

更多技术交流、求职机会、试用福利，欢迎关注字节跳动数据平台微信公众号，回复【1】进入官方交流群

相信大家都对大名鼎鼎的ClickHouse有一定的了解了，它强大的数据分析性能让人印象深刻。但在字节大量生产使用中，发现了ClickHouse依然存在了一定的限制。例如：

• 缺少完整的upsert和delete操作
• 多表关联查询能力弱
• 集群规模较大时可用性下降（对字节尤其如此）
• 没有资源隔离能力

因此，我们决定将ClickHouse能力进行全方位加强，打造一款更强大的数据分析平台。本篇将详细介绍我们是如何加强ClickHouse多表关联查询能力。

大宽表的局限

数据分析的发展历程，可以看作是不断追求分析效率和分析灵活的过程。分析效率是非常重要的，但是并不是需要无限提升的。1秒返回结果和1分钟返回结果的体验是天壤之别，但是0.1秒返回结果和1秒返回结果的差距就没那么大了。因此，在满足了一定时效的情况下，分析的灵活性就显得额外重要了。

起初，数据分析都采用了固定报表的形式，格式更新频率低，依赖定制化的开发，查询逻辑是写死的。对于业务和数据需求相对稳定、不会频繁变化的场景来说固定报表确实就足够了，但是以如今的视角来看，完全固定的查询逻辑不能充分发挥数据的价值，只有通过灵活的数据分析，才能帮助业务人员化被动为主动，探索各数据间的相关关系，快速找到问题背后的原因，极大地提升工作效率。

后面，基于预计算思想的cube建模方案被提出。通过将数据ETL加工后存储在cube中，保证领导和业务人员能够快速得到分析结果基础上，获得了一定的分析灵活性。不过由于维度固定，以及数据聚合后基本无法查询明细数据，依然无法满足Adhoc这类即席查询的场景需求。

近些年，以ClickHouse为代表的具备强大单表性能的查询引擎，带来了大宽表分析的风潮。所谓的大宽表，就是在数据加工的过程中，将多张表通过一些关联字段打平成一张宽表，通过一张表对外提供分析能力。基于ClickHouse单表性能支撑的大宽表模式，既能提升分析时效性又能提高数据查询和分析操作的灵活性，是目前非常流行的一种模式。

然而大宽表依然有它的局限性，具体有：

• 生成每一张大宽表都需要数据开发人员不小的工作量，而且生成过程也需要一定的时间
• 生成宽表会产生大量的数据冗余

刚才有提到，数据分析的发展历程可以看作是不断追求分析效率和分析灵活的过程，那么大宽表的下一个阶段呢？如果ClickHouse的多表关联查询能力足够强，是不是连“将数据打平成宽表”这个步骤也可以省略，只需要维护好对外服务的接口，任何业务人员的需求都现场直接关联查询就可以了呢？

如何强化多表关联查询能力的？ClickHouse 的执行模式相对比较简单，其基本查询模式分为 2 个阶段：

ByteHouse 进行多表关联的复杂查询时，采用分 Stage 的方式，替换目前 ClickHouse的2阶段执行方式。将一个复杂的 Query 按照数据交换情况切分成多个 Stage，Stage 和 Stage 之间通过 exchange 完成数据的交换，单个 Stage 内不存在数据交换。Stage 间的数据交换主要有以下三种形式：

• 按照单（多）个 key 进行 Shuffle
• 由 1 个或者多个节点汇聚到一个节点 (我们称为 gather)
• 同一份数据复制到多个节点(也称为 broadcast 或者说广播)
  单个 Stage 执行会继续复用 ClickHouse 的底层的执行方式。

按照不同的功能切分不同的模块，设计目标如下：

各个模块约定好接口，尽量减少彼此的依赖和耦合。一旦某个模块有变动不会影响别的模块，例如 Stage 生成逻辑的调整不影响调度的逻辑。

模块采用插件的架构，允许模块根据配置灵活支持不同的策略。

根据数据的规模和分布，ByteHouse支持了多种关联查询的实现，目前已经支持的有：

• Shuffle Join，最通用的 Join
• Broadcast Join，针对大表 Join 小表的场景，通过把右表广播到左表的所有 worker 节点来减少左表的传输
• Colocate Join，针对左右表按照 Join key 保持相通分布的场景，减少左右表数据传输

Join 算子通常是 OLAP 引擎中最耗时的算子。如果想优化 Join 算子，可以有两种思路，一方面可以提升 Join 算子的性能，例如更好的 Hash Table 实现和 Hash 算法，以及更好的并行。另一方面可以尽可能减少参与 Join 计算的数据。

Runtime Filter 在一些场景特别是事实表 join 维度表的星型模型场景下会有比较大的效果。因为这种情况下通常事实表规模比较大，而大部分过滤条件都在维度表上，事实表可能要全量 join 维度表。Runtime Filter 的作用是通过在 Join 的 probe 端（就是左表）提前过滤掉那些不会命中 Join 的输入数据来大幅减少 Join 中的数据传输和计算，从而减少整体的执行时间。以下图为例:

改善后的效果以SSB 100G测试集为例，不把数据打成大宽表的情况下，分别使用 ClickHouse 22.2.3.1版本和ByteHouse 2.0.1版本，在相同硬件环境下进行测试。（无数据表示无法返回结果或超过60s）

可以看到大多数测试中，ClickHouse都会发生报错无法返回结果的情况，而ByteHouse能够稳定的在1s内跑出结果。只看SSB的多表测试有些抽象，下面从两个具体的case来看一下优化后的效果:

Case1：Hash Join 右表为大表

经过优化后，query 执行时间从17.210s降低至1.749s。

lineorder 是一张大表，通过 shuffle 可以将大表数据按照 join key shuffle 到每个 worker 节点，减少了右表构建的压力。

SELECT
    sum(LO_REVENUE) - sum(LO_SUPPLYCOST) AS profit
FROM 
    customer
INNER JOIN
(
    SELECT
    LO_REVENUE,
    LO_SUPPLYCOST,
    LO_CUSTKEY
    from
    lineorder
    WHERE toYear(LO_ORDERDATE) = 1997 and toMonth(LO_ORDERDATE) = 1
) as lineorder
ON LO_CUSTKEY = C_CUSTKEY
WHERE C_REGION = 'AMERICA'

Case 2：5张表 Join（未开启runtime filter）

经优化后，query 执行时间从8.583s降低至4.464s。

所有的右表可同时开始数据读取和构建。为了和现有模式做对比，ByteHouse这里并没有开启 runtime filter，开启 runtime filter 后效果会更快。

SELECT                                                                                                                                              
    D_YEAR,                                                                                                                                         
    S_CITY,                                                                                                                                         
    P_BRAND,                                                                                                                                        
    sum(LO_REVENUE) - sum(LO_SUPPLYCOST) AS profit                                                                                                  
FROM ssb1000.lineorder                                                                                                                              
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT C_CUSTKEY                                                                                                                                
    FROM ssb1000.customer                                                                                                                           
    WHERE C_REGION = 'AMERICA'                                                                                                                      
) AS customer ON LO_CUSTKEY = C_CUSTKEY                                                                                                             
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        D_DATEKEY,                                                                                                                                  
        D_YEAR                                                                                                                                      
    FROM date                                                                                                                             
    WHERE (D_YEAR = 1997) OR (D_YEAR = 1998)                                                                                                        
) AS dates ON LO_ORDERDATE = toDate(D_DATEKEY)                                                                                                      
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        S_SUPPKEY,                                                                                                                                  
        S_CITY                                                                                                                                      
    FROM ssb1000.supplier                                                                                                                           
    WHERE S_NATION = 'UNITED STATES'                                                                                                                
) AS supplier ON LO_SUPPKEY = S_SUPPKEY                                                                                                             
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        P_PARTKEY,                                                                                                                                  
        P_BRAND                                                                                                                                     
    FROM ssb1000.part                                                                                                                               
    WHERE P_CATEGORY = 'MFGR#14'                                                                                                                    
) AS part ON LO_PARTKEY = P_PARTKEY                                                                                                                 
GROUP BY                                                                                                                                            
    D_YEAR,                                                                                                                                         
    S_CITY,                                                                                                                                         
    P_BRAND                                                                                                                                         
ORDER BY                                                                                                                                            
    D_YEAR ASC,                                                                                                                                     
    S_CITY ASC,                                                                                                                                     
    P_BRAND ASC                                                                                                                                     
SETTINGS enable_distributed_stages = 1, exchange_source_pipeline_threads = 32      

经过多表关联查询能力的增强，ByteHouse能够更加全面的支撑各类业务，用户可以根据场景选择是否将数据打成大宽表，均能获得非常良好的分析体验。
之所以ByteHouse在多表关联场景表现如此出色，其中一大原因就是因为字节自研了查询优化器，弥补了社区ClickHouse的一大不足。

立即跳转火山引擎BytHouse官网了解详情！