1.优化大表JOIN
(1) 使用广播JOIN（Broadcast Join）
适用于小表join大表 当一个表较小（通常小于1GB）时，可以通过将小表广播到所有计算节点来优化
SELECT /*+ BROADCAST(dim_table) */
f.order_id, f.user_id, d.user_name
FROM fact_orders f
JOIN dim_table d
ON f.user_id = d.user_id
WHERE f.order_time > '2023-01-01';
(2) 使用 Colocate Join(CG)
适用于大表join大表
前提:分桶键和数量必须一致,副本数一致
底层原理:同一 CG 内的表数据分片（Tablet）会分布在相同的 BE（Backend）节点上，确保相同分桶键的数据在物理上相邻存储, 那么当 Join 的列是分桶键时，StarRocks 可以直接在本地节点进行 Join，无需跨节点传输数据。
例如：
- 表 A 和 表 B 的分桶键均为
user_id，且属于同一 CG。 - 对于
user_id = 100的数据，表 A 的 Bucket 0 和表 B 的 Bucket 0 均存储在 BE 节点 A 上，Join 操作直接在节点 A 完成
两次映射机制
- 分桶键到分桶编号：通过哈希分桶键并取模分桶数，确定数据所属的分桶。
- 分桶编号到 BE 节点：通过固定映射策略（如 Round Robin）将分桶分配到具体的 BE 节点
当需要频繁JOIN两个大表时，使用Colocate Join 可以减少数据传输：
-- 创建Colocate组
CREATE COLOCATE GROUP group1;
-- 创建表时指定Colocate组，确保JOIN键分布一致
CREATE TABLE orders (
order_id BIGINT,
user_id BIGINT,
order_time DATETIME,
amount DECIMAL(10, 2)
) ENGINE=OLAP
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 8
PROPERTIES (
"colocate_with" = "group1"
);
CREATE TABLE users (
user_id BIGINT,
user_name VARCHAR(50),
register_time DATETIME
) ENGINE=OLAP
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 8
PROPERTIES (
"colocate_with" = "group1"
);
(3) 优化 JOIN 条件
在JOIN前增加过滤条件，减少JOIN的数据量
SELECT f.order_id, f.user_id, d.user_name
FROM fact_orders f
JOIN (
SELECT user_id, user_name
FROM dim_table
WHERE region = 'ASIA')
d ON f.user_id = d.user_id
WHERE f.order_time > '2023-01-01';
(4) 使用物化视图预聚合
-- 创建物化视图
CREATE MATERIALIZED VIEW mv_order_summary
DISTRIBUTED BY HASH(user_id)
REFRESH ASYNC
AS SELECT
user_id,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM orders
GROUP BY user_id;
-- 使用物化视图进行JOIN
SELECT
m.user_id,
u.user_name,
m.order_count,
m.total_amount
FROM mv_order_summary m
JOIN users u
ON m.user_id = u.user_id;
(5) 合理设计JOIN顺序
使用/*+ LEADING */ 提示指定JOIN顺序
SELECT /*+ LEADING(f d1 d2) */
f.order_id, d1.user_name, d2.product_name
FROM fact_orders f
JOIN dim_users d1
ON f.user_id = d1.user_id
JOIN dim_products d2
ON f.product_id = d2.product_id
WHERE f.order_time > '2023-01-01';
