StarRocks 技术分析：广播连接 & 行列转换

1
2
3
4
5

左表（大表）                右表（小表）
分片1  分片2  分片3         整张表
  ↓      ↓      ↓           ↓  ↓  ↓
Node1  Node2  Node3  ← 广播到每个节点
  └────JOIN   JOIN   JOIN────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 方式一：使用 [broadcast] hint（推荐）
SELECT /*+ SET_VAR(broadcast_row_limit=15000000) */
    o.order_id,
    c.customer_name
FROM orders o
JOIN [broadcast] customer c
    ON o.customer_id = c.id;

-- 方式二：使用 broadcast join hint（旧版）
SELECT
    o.order_id,
    c.customer_name
FROM orders o
JOIN customer c [broadcast]
    ON o.customer_id = c.id;

1
2
3
4
5
6

-- 控制广播阈值（右表行数超过此值则不允许广播）
SET broadcast_row_limit = 15000000;  -- 默认 1500万行

-- 查看执行计划，确认是否走广播
EXPLAIN SELECT ...;
-- 在 PLAN 中看到 BROADCAST 字样即为广播连接

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

PLAN FRAGMENT 1
  OUTPUT EXPRS:
  PARTITION: RANDOM

  STREAM DATA SINK
    EXCHANGE ID: 02
    BROADCAST          ← 右表被广播

  1:OlapScanNode
     TABLE: customer

1
2

视图内部：A JOIN B  →  已经是 1:N 关联
外层查询：再 JOIN C 或子查询  →  变成 1:N:M，数值被重复累加

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 视图内部已聚合
CREATE VIEW v_order_summary AS
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

-- ❌ 外层使用视图时又 JOIN 了原始明细 → total_amount 被重复展开
SELECT v.customer_id, v.total_amount, o.order_date
FROM v_order_summary v
JOIN orders o ON v.customer_id = o.customer_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 视图内部 orders JOIN order_items（1:N 关系）
CREATE VIEW v_order_detail AS
SELECT o.order_id, o.customer_id, oi.product_id, oi.qty
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

-- ❌ 再套一层视图引用，且又关联其他表 → 乘数效应叠加
CREATE VIEW v_customer_report AS
SELECT d.customer_id, c.name, SUM(d.qty) AS total_qty
FROM v_order_detail d
JOIN customer c ON d.customer_id = c.id
GROUP BY d.customer_id, c.name;

1
2
3
4
5

-- ❌ dim_table.type_id 存在重复值，广播后每条大表记录匹配多条
SELECT a.id, b.value
FROM big_table a
JOIN [broadcast] dim_table b ON a.type_id = b.type_id;
-- 结果：数据行数 = 大表行数 × 右表重复数，数值严重虚高

1
2

EXPLAIN SELECT * FROM your_complex_view WHERE ...;
-- 重点关注：JOIN 顺序、是否存在多个 AGGREGATE 节点、数据流向

1
2
3
4
5
6

-- 检查视图中间层是否已产生重复
SELECT key_col, COUNT(*) AS cnt
FROM (/* 视图内部SQL，逐段拆出来 */) t
GROUP BY key_col
HAVING cnt > 1;
-- 有结果 → 该层已产生重复行，问题在此层或更内层

1
2
3
4
5
6

-- 广播表关联键唯一性检查
SELECT type_id, COUNT(*) AS cnt
FROM dim_table
GROUP BY type_id
HAVING COUNT(*) > 1;
-- 有结果 → 右表不唯一 → 广播后必然膨胀

1
2
3
4
5
6

-- 快速判断结果集是否有重复
SELECT
    COUNT(*)                  AS total_rows,
    COUNT(DISTINCT order_id)  AS distinct_orders
FROM v_your_view;
-- 两值不一致 → 有重复行，数值必然偏大

1
2
3
4
5
6

-- ✅ 广播前用子查询去重，确保关联键唯一
SELECT a.id, b.value
FROM big_table a
JOIN [broadcast] (
    SELECT DISTINCT type_id, value FROM dim_table
) b ON a.type_id = b.type_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- ✅ 视图内完成聚合，外层直接使用，不再关联原始明细
CREATE VIEW v_customer_stats AS
SELECT
    o.customer_id,
    SUM(oi.amount)     AS total_amount,
    COUNT(o.order_id)  AS order_cnt
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id;

-- 外层直接用聚合结果，不再 JOIN 原始表
SELECT cs.customer_id, c.name, cs.total_amount
FROM v_customer_stats cs
JOIN [broadcast] customer c ON cs.customer_id = c.id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- ✅ 用 CTE 拍平逻辑，每一层清晰可见，避免视图套视图
WITH
order_agg AS (
    -- 第一层：先在订单粒度聚合
    SELECT order_id, customer_id, SUM(amount) AS order_total
    FROM orders
    GROUP BY order_id, customer_id
),
customer_agg AS (
    -- 第二层：再在客户粒度聚合
    SELECT customer_id, SUM(order_total) AS total
    FROM order_agg
    GROUP BY customer_id
),
enriched AS (
    -- 第三层：最后广播关联维度表
    SELECT ca.customer_id, c.name, ca.total
    FROM customer_agg ca
    JOIN [broadcast] customer c ON ca.customer_id = c.id
)
SELECT * FROM enriched;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- ✅ 右表有重复时，先取每个 key 的最新/最优一条
WITH dedup_dim AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY type_id ORDER BY updated_at DESC) AS rn
    FROM dim_table
)
SELECT a.id, b.value
FROM big_table a
JOIN [broadcast] (SELECT * FROM dedup_dim WHERE rn = 1) b
    ON a.type_id = b.type_id;

1
2
3
4
5
6
7

SELECT
    student,
    MAX(CASE WHEN subject = '语文' THEN score END) AS `语文`,
    MAX(CASE WHEN subject = '数学' THEN score END) AS `数学`,
    MAX(CASE WHEN subject = '英语' THEN score END) AS `英语`
FROM student_scores
GROUP BY student;

1
2
3
4
5
6
7

SELECT
    student,
    MAX(IF(subject = '语文', score, NULL)) AS `语文`,
    MAX(IF(subject = '数学', score, NULL)) AS `数学`,
    MAX(IF(subject = '英语', score, NULL)) AS `英语`
FROM student_scores
GROUP BY student;

1
2
3
4
5
6
7

SELECT
    department,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS `1月`,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS `2月`,
    SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS `3月`
FROM sales
GROUP BY department;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# Python 示例：动态生成行转列 SQL
import starrocks_connector as conn

# Step1: 查出所有科目
subjects = conn.query("SELECT DISTINCT subject FROM student_scores")

# Step2: 动态拼接 CASE WHEN
case_clauses = ",\n".join([
    f"MAX(CASE WHEN subject = '{s}' THEN score END) AS `{s}`"
    for s in subjects
])

sql = f"""
SELECT student, {case_clauses}
FROM student_scores
GROUP BY student
"""
result = conn.query(sql)

1
2
3
4
5
6

SELECT student, '语文' AS subject, `语文` AS score FROM student_wide
UNION ALL
SELECT student, '数学' AS subject, `数学` AS score FROM student_wide
UNION ALL
SELECT student, '英语' AS subject, `英语` AS score FROM student_wide
ORDER BY student, subject;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    t.student,
    v.subject,
    v.score
FROM student_wide t
CROSS JOIN LATERAL (
    VALUES
        ('语文', t.`语文`),
        ('数学', t.`数学`),
        ('英语', t.`英语`)
) AS v(subject, score);

1
2
3
4
5
6
7

-- 假设 scores 列为 ARRAY 类型
SELECT
    student,
    subject,
    score
FROM student_array_table,
LATERAL unnest(subjects, scores) AS t(subject, score);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 展开多个维度列（月份 + 指标类型）
SELECT
    department,
    month,
    metric_type,
    value
FROM sales_wide t
CROSS JOIN LATERAL (
    VALUES
        ('2024-01', '销售额', t.sales_jan),
        ('2024-01', '成本',   t.cost_jan),
        ('2024-02', '销售额', t.sales_feb),
        ('2024-02', '成本',   t.cost_feb)
) AS v(month, metric_type, value)
WHERE value IS NOT NULL;

1
2
3
4
5
6
7
8

-- 1. 确认右表满足广播阈值
SELECT COUNT(*) FROM dim_table;  -- 确保行数在 broadcast_row_limit 以内

-- 2. 适当调大阈值（谨慎，需评估内存）
SET broadcast_row_limit = 20000000;

-- 3. 查看实际执行计划
EXPLAIN COSTS SELECT ...;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 创建同步物化视图（自动维护）
CREATE MATERIALIZED VIEW mv_score_pivot
AS
SELECT
    student,
    MAX(IF(subject = '语文', score, NULL)) AS score_chinese,
    MAX(IF(subject = '数学', score, NULL)) AS score_math,
    MAX(IF(subject = '英语', score, NULL)) AS score_english
FROM student_scores
GROUP BY student;

-- 查询时自动命中物化视图
SELECT student, score_chinese, score_math FROM student_scores;