10. Doris 系列第10篇：数据查询全攻略｜Join/子查询/窗口函数，从基础到高级实战

适合人群：大数据开发、Doris查询调优工程师、数仓分析师、BI工程师

核心价值：吃透Doris 2.x数据查询核心能力，掌握Join算法选型、子查询优化、多维聚合、窗口函数实战，解决查询慢、资源浪费、语法报错等问题

系列说明：本文是Doris进阶系列第10篇，承接上篇物化视图，聚焦数据查询全场景，基于Doris 2.x最新架构，覆盖从基础查询到高级分析的所有核心知识点，全程纯生产干货，兼顾原理、实操与调优，看完直接落地。

一、开篇核心：Doris查询能力，撑起企业级OLAP分析

数据查询是Doris的核心场景，也是OLAP数据库的核心竞争力。Doris 2.x的查询能力已达到企业级水平，能够高效支撑：简单点查、复杂多表Join、多维聚合分析、窗口序列分析等全场景需求。

本文将从四大核心模块展开，层层递进，帮你掌握Doris查询的底层逻辑与实战技巧：

• 分布式Join：算法选型与优化，减少网络开销；

• 子查询：语法支持与自动优化，降低使用成本；

• 多维聚合：Grouping Sets/Cube/Rollup，适配BI报表场景；

• 窗口函数：全场景支持与性能优化，搞定排名、移动计算等需求。

二、分布式Join：实现与优化（查询性能的核心关键）

Join是多表关联查询的基础，Doris支持多种分布式Join算法，核心设计思想是：减少网络Shuffle、充分利用数据分布特性，根据表大小、分桶策略、统计信息自动选择最优算法，无需手动干预（可通过Hint调整）。

2.1 Doris支持的Join类型（语法与兼容性）

Doris兼容MySQL常用Join语法，部分类型暂不支持，具体如下表（截至Doris 2.2版本）：

SQL语法	Doris支持情况	核心说明
INNER JOIN（内连接）	✅ 支持	默认Join类型，只返回两表匹配的数据
LEFT/RIGHT [OUTER] JOIN（左/右外连接）	✅ 支持	左连接返回左表所有数据，右表匹配不到则为NULL；右连接反之
FULL OUTER JOIN（全外连接）	❌ 不支持（截至2.2）	社区已提交PR，后续版本将支持，当前可通过UNION ALL模拟
CROSS JOIN（交叉连接）	✅ 支持（需显式写CROSS JOIN）	笛卡尔积，会产生大量数据，慎用（如两表各10万行，结果100亿行）
SEMI JOIN / ANTI JOIN（半连接/反连接）	✅ 支持（通过子查询改写）	SEMI JOIN对应WHERE a IN (SELECT b FROM t2)；ANTI JOIN对应WHERE a NOT IN (SELECT b FROM t2)

2.2 核心Join执行算法（物理实现，重点掌握）

Doris会根据表的大小、分桶策略、统计信息，自动选择最优的Join算法，不同算法的适用场景、原理差异较大，下面逐一拆解（生产必学）。

（1）Broadcast Join（广播Join，最常用）

✅ 实现原理

将右表全量广播（复制）到左表所在的所有BE节点，每个BE节点在本地完成左表与右表的Join，无需跨节点数据传输。

🔍 触发条件（满足其一即可）

• 右表预估大小 < broadcast_join_threshold（默认100MB，单位：字节）；

• 手动通过Hint强制指定：/*+ SET_VAR(broadcast_join_threshold=1073741824) */（将阈值改为1GB，强制广播）。

⚙️ 优缺点

• 优点：无网络Shuffle，执行速度快，资源开销低，适合大表Join小维表；

• 缺点：右表过大会导致内存溢出（OOM），因为右表需要全量加载到每个BE的内存中。

📌 生产最佳实践

维度表（如user_dim用户维表、product_dim商品维表）通常数据量较小（<1GB），天然适合作为右表，走Broadcast Join，这是生产中最常见的Join场景。

-- 示例：事实表（大表）Join维表（小表），自动走Broadcast Join
SELECT 
    l.order_id, l.user_id, u.city, l.amount
FROM fact_order l
LEFT JOIN dim_user u ON l.user_id = u.user_id;
-- 若dim_user < 100MB，自动广播右表，本地Join

（2）Shuffle Join（重分区Join，通用型）

✅ 实现原理

对参与Join的两张表，按Join Key做Hash Shuffle（重分区），将相同Join Key的数据分发到同一个BE节点，然后在每个BE节点本地完成Join。

🔍 触发条件

两表预估大小均大于broadcast_join_threshold（默认100MB），无法走Broadcast Join时，自动触发Shuffle Join。

⚙️ 优缺点

• 优点：可处理任意大小的表，无内存溢出风险（内存不足时会自动Spill到磁盘）；

• 缺点：网络I/O开销高，Shuffle阶段通常占用查询总时间的60%+，是性能瓶颈。

⚠️ 生产避坑

Shuffle Join的性能瓶颈在网络传输，尽量避免大表之间的Shuffle Join；若必须使用，可调整Shuffle并发度、增大BE内存，减少Spill到磁盘的频率。

（3）Colocation Join（协同定位Join，Doris特色，性能最优）

✅ 实现原理

若两张表满足“分桶键相同 + 分区策略一致”，则相同Bucket（分桶）的数据会被分配到同一个BE节点，Join时无需跨节点Shuffle，直接在本地完成Join，是性能最优的Join算法。

🔍 启用条件（必须同时满足）

1. 建表时指定同一个Colocation Group（协同组）；

2. 两表的分桶键相同、分桶数一致；

3. 两表的分区策略一致（如均为RANGE分区，分区键相同）。

🔧 建表示例（生产实战）

-- 表1：事实表t1，指定Colocation Group为group1
CREATE TABLE t1 (
    dt DATE COMMENT '分区键',
    k1 BIGINT COMMENT '分桶键',
    value INT COMMENT '值'
) ENGINE=OLAP
DUPLICATE KEY(dt, k1)
PARTITION BY RANGE(dt) (
    PARTITION p202501 VALUES [('2025-01-01'), ('2025-02-01'))
)
DISTRIBUTED BY HASH(k1) BUCKETS 10
PROPERTIES("colocate_with" = "group1");  -- 指定协同组

-- 表2：事实表t2，与t1同协同组、同分桶键、同分区策略
CREATE TABLE t2 (
    dt DATE COMMENT '分区键',
    k1 BIGINT COMMENT '分桶键',
    value INT COMMENT '值'
) ENGINE=OLAP
DUPLICATE KEY(dt, k1)
PARTITION BY RANGE(dt) (
    PARTITION p202501 VALUES [('2025-01-01'), ('2025-02-01'))
)
DISTRIBUTED BY HASH(k1) BUCKETS 10
PROPERTIES("colocate_with" = "group1");  -- 与t1同组

🔍 验证方法

执行EXPLAIN SELECT ... FROM t1 JOIN t2 ON t1.k1 = t2.k1;，若计划中出现COLOCATE字样，说明成功启用Colocation Join。

✅ 性能收益

相比Shuffle Join，Colocation Join可减少100%的网络Shuffle，查询速度提升3~10倍，是大事实表之间Join的首选方案。

（4）Bucket Shuffle Join（桶级Shuffle，Doris 2.0+ 新增）

✅ 实现原理

介于Shuffle Join和Colocation Join之间，当两表分桶数一致，但不满足Colocation Join的其他条件（如分区策略不同）时，仅Shuffle缺失的Bucket数据，而非全表Shuffle，减少网络传输量。

🎯 适用场景

两表分桶数一致，但分区策略不同，无法启用Colocation Join，且两表均较大，不适合Broadcast Join的场景。

✅ 性能收益

比普通Shuffle Join减少50%+的网络流量，执行效率显著提升。

2.3 生产Join优化建议（按场景分类，直接套用）

业务场景	优化建议	补充说明
维表（小表）Join事实表（大表）	确保维表大小 < 100MB，让查询自动走Broadcast Join	若维表略大于100MB，可通过Hint调大broadcast_join_threshold
大事实表之间Join	设计相同分桶键，启用Colocation Join	建表时提前规划分桶键，确保两表分桶数、协同组一致
Join Key数据倾斜（部分Key数据量极大）	使用SKEW JOIN Hint（实验性），或预处理打散倾斜Key	Hint示例：/*+ SKEW_JOIN(t1, t2, join_key) */
多表Join（3表及以上）	按表大小升序排列（小表在左），利于RBO优化选择最优Join顺序	小表先Join，减少后续Join的数据量

三、子查询（Subquery）：语法支持与自动优化

Doris全面支持SQL子查询，且会自动对其子查询进行优化（如去关联化、转换为Join），无需用户手动改写，降低使用成本。但需注意部分子查询场景暂不支持，避免踩坑。

3.1 支持的子查询类型（生产常用）

子查询类型	示例	支持情况	补充说明
标量子查询	SELECT (SELECT max(id) FROM t2) FROM t1;	✅ 支持	子查询返回单个值，可作为列使用
IN子查询	SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);	✅ 支持	自动转换为LEFT SEMI JOIN，提升效率
EXISTS子查询	SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);	✅ 支持	自动转换为LEFT SEMI/ANTI JOIN，避免嵌套循环
关联子查询	SELECT * FROM t1 WHERE id > (SELECT avg(id) FROM t2 WHERE t2.dt = t1.dt);	✅ 支持	需通过去关联化（Decorrelation）优化，转换为Join
FROM子查询（派生表）	SELECT * FROM (SELECT id FROM t1) t;	✅ 支持	自动将外层谓词下推到子查询，减少扫描数据量

3.2 不支持的子查询场景（避坑重点）

⚠️ 核心禁忌：NOT IN子查询中包含NULL值，会导致语义歧义（NULL与任何值比较结果都是NULL），Doris不支持此类场景，建议用NOT EXISTS替代。

-- ❌ 不支持：NOT IN 包含NULL值
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);  -- 若t2.id有NULL，结果异常

-- ✅ 推荐替代方案：NOT EXISTS
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

3.3 子查询核心优化技术（Doris自动完成）

Doris的查询优化器会自动对於子查询进行优化，无需用户手动干预，核心优化技术有3种：

（1）去关联化（Decorrelation）：解决关联子查询效率低的问题

核心逻辑：将依赖外层表的关联子查询，转换为Join + Group By的形式，避免嵌套循环，提升执行效率。

-- 原始关联子查询（效率低，嵌套循环）
SELECT * FROM orders o 
WHERE o.amount > (SELECT AVG(amount) FROM orders o2 WHERE o2.user_id = o.user_id);

-- Doris自动优化后（去关联化，转换为Join）
SELECT o.* 
FROM orders o
JOIN (SELECT user_id, AVG(amount) AS avg_amt FROM orders GROUP BY user_id) tmp
  ON o.user_id = tmp.user_id
WHERE o.amount > tmp.avg_amt;

（2）Semi/Anti Join转换：提升IN/EXISTS子查询效率

核心逻辑：将IN子查询转换为LEFT SEMI JOIN，将NOT EXISTS子查询转换为LEFT ANTI JOIN，充分利用Join优化器（如Broadcast Join），提升执行效率。

-- 原始IN子查询
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

-- 自动转换为LEFT SEMI JOIN
SELECT t1.* FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;

（3）谓词下推（Predicate Pushdown）：减少子查询扫描数据量

核心逻辑：对FROM子查询（派生表），将外层WHERE条件下推到子查询内部，提前过滤无效数据，减少子查询的扫描量和计算量。

-- 原始查询（外层条件未下推，子查询扫描全表）
SELECT * FROM (SELECT id, name FROM users) u WHERE u.id > 100;

-- Doris自动优化后（谓词下推，子查询仅扫描id>100的数据）
SELECT id, name FROM users WHERE id > 100;

四、多维聚合分析：Grouping Sets / Cube / Rollup

Doris支持标准SQL的多维聚合扩展（Grouping Sets、Cube、Rollup），主要用于BI报表、数据下钻分析场景，能够快速生成多维度聚合结果，无需编写多个独立查询。

4.1 语法支持（生产实战示例）

以sales表（包含city、category、sales字段）为例，展示三种多维聚合的用法：

（1）Grouping Sets：指定多个分组组合

手动指定需要聚合的分组维度组合，灵活控制聚合结果，适合自定义多维报表。

-- 示例：同时按（city）、（category）、（无分组，全局聚合）三个维度聚合
SELECT city, category, SUM(sales) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((city), (category), ());
-- 结果包含3类数据：按城市聚合、按分类聚合、全局聚合

（2）Cube：所有可能的维度组合

自动生成所有维度的组合（共2^N种，N为维度数），适合全维度下钻分析，维度不宜过多（≤3个）。

-- 示例：city和category两个维度，生成所有4种组合（(city,category)、(city)、(category)、()）
SELECT city, category, SUM(sales) AS total_sales
FROM sales
GROUP BY CUBE(city, category);

（3）Rollup：层级聚合（自上而下）

按维度的层级关系，生成自上而下的聚合结果，适合层级报表（如年→月→日、区域→城市）。

-- 示例：按year→month→day层级聚合，生成4种组合
SELECT year, month, day, SUM(sales) AS total_sales
FROM sales
GROUP BY ROLLUP(year, month, day);
-- 组合顺序：(year,month,day) → (year,month) → (year) → ()

4.2 执行原理

1. 逻辑计划阶段：将多维聚合语句展开为多个GROUP BY语句，通过UNION ALL合并结果；

2. 物理计划阶段：共享Scan（扫描）和部分Agg（聚合）计算，避免重复扫描基表，提升效率；

3. 结果标识：使用GROUPING()函数区分NULL值——NULL是真实数据，还是聚合后的汇总标记（1表示该维度未参与聚合，0表示参与聚合）。

-- 示例：用GROUPING()区分NULL来源
SELECT 
    city,
    category,
    GROUPING(city) AS grp_city,  -- 1：city未参与聚合；0：参与聚合
    GROUPING(category) AS grp_category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY CUBE(city, category);

4.3 性能考量（生产选型建议）

实现方式	基表扫描次数	适用场景	注意事项
单次Cube/Grouping Sets	1次	维度数≤3，聚合结果集可控	维度过多（≥4）会导致结果集爆炸，引发OOM
多次独立GROUP BY查询	N次（N为维度组合数）	维度多、结果集大，避免OOM	扫描次数多，总耗时可能更高

💡 生产替代方案

对高频的Cube/Grouping Sets查询，建议预先创建物化视图（同步Rollup或异步MV），将预聚合结果持久化，查询时直接命中MV，大幅提升效率。

五、分析函数（窗口函数，Window Function）

Doris全面支持ANSI SQL窗口函数，用于排名、移动平均、累计计算、序列分析等场景，广泛应用于业务报表、用户行为分析、销售趋势分析等，Doris 2.0+ 对常见窗口函数实现了向量化加速，性能大幅提升。

5.1 支持的窗口函数类别（生产常用）

函数类别	常用函数示例	核心用途
排名函数	ROW_NUMBER(), RANK(), DENSE_RANK()	对数据进行排名（如销售排行榜）
聚合函数	SUM(), AVG(), COUNT(), MIN(), MAX()	窗口内聚合（如累计销售额、移动平均）
偏移函数	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()	获取窗口内指定位置的数据（如上一次访问时间）
分布函数	NTILE(), PERCENT_RANK()	数据分桶、百分比排名（部分函数为实验性支持）

5.2 窗口定义语法（核心格式）

窗口函数的核心是“窗口定义”，通过OVER()子句指定窗口的分区、排序和范围，语法格式如下：

function(...) OVER (
    [PARTITION BY partition_expr]  -- 窗口分区（类似GROUP BY，分区内独立计算）
    [ORDER BY order_expr [ASC|DESC]]  -- 分区内排序
    [window_frame]  -- 窗口帧（指定窗口范围）
)

窗口帧（Window Frame）类型（重点）

窗口帧用于指定窗口内的计算范围，Doris支持两种类型，默认使用RANGE帧：

• ROWS：物理行范围（基于行的位置），示例：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（当前行及前2行）；

• RANGE：逻辑值范围（基于排序字段的数值），示例：RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW（当前行及前7天的数据）；

• 默认值：RANGE UNBOUNDED PRECEDING（从分区起始行到当前行，即累计窗口）。

5.3 执行原理与性能优化

（1）执行模型

1. 排序阶段：按PARTITION BY + ORDER BY对数据进行排序（这是窗口函数的性能瓶颈）；

2. 滑动计算：维护窗口内的计算状态（如Sum、Count），避免重复计算；

3. 向量化支持：Doris 2.0+ 对SUM、ROW_NUMBER、LAG、LEAD等常见窗口函数实现了向量化加速，提升执行效率。

（2）性能关键点（生产调优重点）

常见问题	优化方案
大Partition排序慢（单分区数据量过大）	确保PARTITION BY列是高基数列，避免单分区数据量过大；必要时拆分分区
无界窗口（UNBOUNDED）内存占用高	慎用于大表；若必须使用，调大BE内存，避免OOM
RANGE窗口含时间维度，计算慢	使用DATETIME类型列，避免字符串类型转换；提前对时间列建立索引

（3）典型应用场景（生产实战示例）

-- 场景1：用户行为序列分析（获取用户上一次访问时间）
SELECT 
    user_id,
    event_time,
    LAG(event_time, 1) OVER (
        PARTITION BY user_id 
        ORDER BY event_time
    ) AS last_visit_time
FROM user_log;

-- 场景2：销售排行榜（按销售额排名，相同销售额排名相同）
SELECT 
    product_id,
    sales,
    RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM daily_sales;

-- 场景3：7日移动平均（计算每日销售额的7日均值，含当日）
SELECT 
    dt,
    revenue,
    AVG(revenue) OVER (
        ORDER BY dt 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS ma7_revenue
FROM sales_daily;

六、高级功能对比与选型指南（生产快速决策）

结合前面的四大模块，整理生产中常见查询功能的选型建议，帮你快速决策，避免踩坑：

功能	适用场景	性能提示
Broadcast Join	大表 + 小维表关联（维表<1GB）	控制维表大小，避免OOM；可通过Hint调整阈值
Colocation Join	同分桶、同分区策略的大事实表关联	建表时提前规划分桶键和协同组
物化视图	固定聚合、多表Join的高频查询	替代运行时Cube/Join，大幅提升查询速度