两张大表JOIN跑了30秒——Doris的JOIN优化策略全解析

-- Broadcast Join示例（Doris自动选择）
SELECT e.event_time, e.event_name, e.amount,
       u.nickname, u.country, u.vip_level
FROM ods_user_event e           -- 大表：5000万行
JOIN dim_user u                  -- 小表：50万行
    ON e.user_id = u.user_id
WHERE e.event_time >= '2024-01-15';
 
-- Doris自动判断dim_user是小表 → 广播到所有BE
-- 每个BE在本地做JOIN，不需要大表移动数据
 
-- 手动指定Broadcast（当自动选择不对时）
SELECT /*+ SET_VAR(exec_mem_limit=8589934592) */
    e.*, u.nickname
FROM ods_user_event e
JOIN [broadcast] dim_user u ON e.user_id = u.user_id;

-- 步骤一：创建Colocate Group
-- 两张表必须有相同的分桶列、相同的桶数、相同的副本数
 
-- 用户行为事件表
CREATE TABLE ods_user_event (
    event_time DATETIME, user_id BIGINT,
    event_name VARCHAR(128), amount DECIMAL(12,2)
) DUPLICATE KEY(event_time, user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES ('colocate_with' = 'user_group');  -- 加入user_group
 
-- 用户维度表
CREATE TABLE dim_user (
    user_id BIGINT, nickname VARCHAR(128),
    country VARCHAR(32), vip_level TINYINT
) UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 16       -- 桶数必须相同！
PROPERTIES ('colocate_with' = 'user_group');  -- 同一个Group！

-- 步骤二：查询时自动走Colocate Join
SELECT e.event_time, e.event_name, e.amount,
       u.nickname, u.country
FROM ods_user_event e
JOIN dim_user u ON e.user_id = u.user_id  -- JOIN列=分桶列=Colocate列
WHERE e.event_time >= '2024-01-15';
 
-- EXPLAIN查看执行计划，会显示 'COLOCATE' 标记
-- 表示这个JOIN完全在本地执行，零网络传输
 
-- Colocate Join的三个前提条件：
-- ① 两张表在同一个Colocate Group
-- ② JOIN的列 = 分桶列
-- ③ 桶数、副本数相同
-- 任何一个条件不满足就会退化为Shuffle/Broadcast Join

实战经验：Colocate Join让查询从8秒降到800ms

在某元宇宙社交公司，最常用的查询模式是：用户行为事件表（ods_user_event，5000万行/天）JOIN用户维度表（dim_user，200万行）。

优化前：两张表没有配Colocate，Doris选的是Broadcast Join（把dim_user广播到所有BE）。200万行的维度表广播开销不算大，但数据量增长后响应时间到了8秒。

优化后：把两张表放入同一个Colocate Group，分桶列都是user_id，桶数都是16。JOIN变成Colocate Join后，完全不需要数据移动，响应时间降到800ms。

额外注意：Colocate要求两张表的桶数相同。如果事件表需要32个桶（数据量大），维度表只需要8个桶（数据量小），就没法Colocate了。这时候维度表也要设32个桶，虽然浪费了一些存储，但换来的JOIN性能提升非常值得。

四种JOIN策略的选择流程

# Doris选择JOIN策略的优先级
#
# 1. 先检查能不能Colocate Join
#    → 两表在同一Colocate Group且JOIN列=分桶列？
#    → 是 → Colocate Join（零网络，最快）
#
# 2. 再检查能不能Bucket Shuffle Join
#    → 左表的分桶列 = JOIN列？
#    → 是 → 只需要移动右表数据到左表所在BE
#
# 3. 再检查能不能Broadcast Join
#    → 右表的数据量 < broadcast_row_count_limit（默认1024MB）？
#    → 是 → 把右表广播到所有BE
#
# 4. 都不行 → Shuffle Join（两表都重分布，最慢）

面试高频追问

追问一：Colocate Join有什么限制？

三个主要限制：① 同组表的桶数和副本数必须相同——限制了灵活性；② 如果BE节点宕机导致Colocate Group的数据不均衡（Group Unstable状态），Doris会暂时退化为Shuffle Join，等数据恢复后再回到Colocate；③ 建表后不能修改分桶列，如果一开始没规划好Colocate Group就很难补救。所以Colocate要在建表阶段就规划好。

追问二：怎么判断应该用哪种JOIN策略？

用EXPLAIN查看执行计划。计划里会标注每个JOIN用的策略（BROADCAST/SHUFFLE/COLOCATE/BUCKET_SHUFFLE）。如果看到SHUFFLE，说明两表都在做数据重分布，考虑能不能用Colocate或调整分桶设计来优化。

追问三：Doris的JOIN能力和ClickHouse比怎么样？

Doris的JOIN能力远强于ClickHouse。ClickHouse的JOIN是在单节点上执行的（右表加载到内存），大表JOIN大表容易OOM。Doris的MPP架构让JOIN可以分布式执行，支持Colocate等优化策略。这是很多公司选Doris而不是ClickHouse的核心原因之一。

实战踩坑：Colocate Group状态Unstable

有一次某元宇宙社交公司的一台BE节点因为磁盘故障临时下线维修。下线后Doris发现Colocate Group的Tablet分布不均衡了（有些Tablet的副本缺失），自动把Group标记为Unstable状态。在Unstable状态下，所有Colocate Join退化为Shuffle/Broadcast Join，查询从800ms涨到5-8秒。

解决过程：① 先确认BE节点恢复后重新上线；② 等Doris自动进行Tablet均衡（Balance），把缺失的副本补回来；③ 均衡完成后Colocate Group自动恢复Stable状态，查询性能回到800ms。整个过程大约花了2小时。

教训：Colocate Join依赖BE节点的稳定性。生产环境建议至少3个BE节点+3副本，这样单个BE下线后Colocate Group有更大概率保持Stable。

总结

Doris JOIN优化面试讲清楚四种策略和优先级：Colocate（零Shuffle）> Bucket Shuffle（单表移动）> Broadcast（小表广播）> Shuffle（全量重分布）。核心优化方向是建表时规划好Colocate Group，把经常JOIN的表放在一起。能讲出Colocate Join的配置方法和Unstable退化的处理经验，面试官会觉得你是真正深度使用过Doris的人。

下一篇聊Doris的向量化执行引擎：为什么同样的SQL，开了向量化快3倍？

你们Doris的JOIN性能如何？用了Colocate Join吗？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长