火山引擎在行为分析场景下的 ClickHouse JOIN 优化

动手点关注 干货不迷路 👇

1. 背景

火山引擎增长分析 DataFinder 基于 ClickHouse 来进行行为日志的分析，ClickHouse 的主要版本是基于社区版改进开发的字节内部版本。主要的表结构：

事件表：存储用户行为数据，以用户 ID 分 shard 存储。

--列出了主要的字段信息
  CREATE TABLE tob_apps_all
  (
      `tea_app_id`                UInt32,  --应用ID
      `device_id`             String DEFAULT '', --设备ID
      `time`                  UInt64,--事件日志接受时间
      `event`                 String,--事件名称
      `user_unique_id`        String,--用户ID
      `event_date`            Date , --事件日志日期，由time转换而来
      `hash_uid`              UInt64 --用户ID hash过后的id，用来join降低内存消耗
  )│
  ```

用户表：存储用户的属性数据，以用户 ID 分 shard 存储。

--列出了主要的字段信息
CREATE TABLE users_unique_all
(
    `tea_app_id`            UInt32,            --应用ID
    `user_unique_id`        String DEFAULT '', -- 用户ID
    `device_id`             String DEFAULT '', -- 用户最近的设备ID
    `hash_uid`              UInt64,--用户ID hash过后的id，用来join降低内存消耗
    `update_time`           UInt64,--最近一次更新时间
    `last_active_date`      Date  --用户最后活跃日期
)

设备表：存储设备相关的数据，以设备 ID 分 shard 存储。

--列出了主要的字段信息
CREATE TABLE devices_all
(
    `tea_app_id`            UInt32,            --应用ID
    `device_id`             String DEFAULT '', --设备ID    
    `update_time`           UInt64,            --最近一次更新时间
    `last_active_date`      Date  --用户最后活跃日期
)

业务对象表：存储业务对象相关的数据，每个 shard 存储全量的数据。

--列出了主要的字段信息
CREATE TABLE rangers.items_all
(
    `tea_app_id`            UInt32,
    `hash_item_id`          Int64,
    `item_name`             String, --业务对象名称。比如商品
    `item_id`               String, --业务对象ID。比如商品id 1000001
    `last_active_date`      Date
)

1.1 业务挑战

随着接入应用以及应用的 DAU 日益增加，ClickHouse 表的事件量增长迅速；并且基于行为数据需要分析的业务指标越来越复杂，需要 JOIN 的表增多；我们遇到有一些涉及到 JOIN 的复杂 SQL 执行效率低，内存和 CPU 资源占用高，导致分析接口响应时延和错误率增加。

2. 关于 Clickhouse 的 JOIN

在介绍优化之前，先介绍一下基本的 ClickHouse JOIN 的类型和实现方式。

2.1 分布式 JOIN

SELECT 
    et.os_name, 
    ut.device_id AS user_device_id
FROM tob_apps_all AS et 
ANY LEFT JOIN 
(
    SELECT 
        device_id, 
        hash_uid
    FROM users_unique_all 
    WHERE (tea_app_id = 268411) AND (last_active_date >= '2022-08-06')
) AS ut ON et.hash_uid = ut.hash_uid
WHERE (tea_app_id = 268411) 
AND (event = 'app_launch') 
AND (event_date = '2022-08-06')

基本执行过程：

1. 一个 Clickhouse 节点作为 Coordinator 节点，给每个节点分发子查询，子查询 sql（tob_a