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算法不同算法的适用场景、原理差异较大下面逐一拆解生产必学。1Broadcast Join广播Join最常用✅ 实现原理将右表全量广播复制到左表所在的所有BE节点每个BE节点在本地完成左表与右表的Join无需跨节点数据传输。 触发条件满足其一即可右表预估大小 broadcast_join_threshold默认100MB单位字节手动通过Hint强制指定/* SET_VAR(broadcast_join_threshold1073741824) */将阈值改为1GB强制广播。⚙️ 优缺点优点无网络Shuffle执行速度快资源开销低适合大表Join小维表缺点右表过大会导致内存溢出OOM因为右表需要全量加载到每个BE的内存中。 生产最佳实践维度表如user_dim用户维表、product_dim商品维表通常数据量较小1GB天然适合作为右表走Broadcast Join这是生产中最常见的Join场景。-- 示例事实表大表Join维表小表自动走Broadcast JoinSELECTl.order_id,l.user_id,u.city,l.amountFROMfact_order lLEFTJOINdim_user uONl.user_idu.user_id;-- 若dim_user 100MB自动广播右表本地Join2Shuffle 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到磁盘的频率。3Colocation Join协同定位JoinDoris特色性能最优✅ 实现原理若两张表满足“分桶键相同 分区策略一致”则相同Bucket分桶的数据会被分配到同一个BE节点Join时无需跨节点Shuffle直接在本地完成Join是性能最优的Join算法。 启用条件必须同时满足建表时指定同一个Colocation Group协同组两表的分桶键相同、分桶数一致两表的分区策略一致如均为RANGE分区分区键相同。 建表示例生产实战-- 表1事实表t1指定Colocation Group为group1CREATETABLEt1(dtDATECOMMENT分区键,k1BIGINTCOMMENT分桶键,valueINTCOMMENT值)ENGINEOLAPDUPLICATEKEY(dt,k1)PARTITIONBYRANGE(dt)(PARTITIONp202501VALUES[(2025-01-01),(2025-02-01)))DISTRIBUTEDBYHASH(k1)BUCKETS10PROPERTIES(colocate_withgroup1);-- 指定协同组-- 表2事实表t2与t1同协同组、同分桶键、同分区策略CREATETABLEt2(dtDATECOMMENT分区键,k1BIGINTCOMMENT分桶键,valueINTCOMMENT值)ENGINEOLAPDUPLICATEKEY(dt,k1)PARTITIONBYRANGE(dt)(PARTITIONp202501VALUES[(2025-01-01),(2025-02-01)))DISTRIBUTEDBYHASH(k1)BUCKETS10PROPERTIES(colocate_withgroup1);-- 与t1同组 验证方法执行EXPLAIN SELECT ... FROM t1 JOIN t2 ON t1.k1 t2.k1;若计划中出现COLOCATE字样说明成功启用Colocation Join。✅ 性能收益相比Shuffle JoinColocation Join可减少100%的网络Shuffle查询速度提升3~10倍是大事实表之间Join的首选方案。4Bucket Shuffle Join桶级ShuffleDoris 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实验性或预处理打散倾斜KeyHint示例/* SKEW_JOIN(t1, t2, join_key) */多表Join3表及以上按表大小升序排列小表在左利于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优化转换为JoinFROM子查询派生表SELECT * FROM (SELECT id FROM t1) t;✅ 支持自动将外层谓词下推到子查询减少扫描数据量3.2 不支持的子查询场景避坑重点⚠️ 核心禁忌NOT IN子查询中包含NULL值会导致语义歧义NULL与任何值比较结果都是NULLDoris不支持此类场景建议用NOT EXISTS替代。-- ❌ 不支持NOT IN 包含NULL值SELECT*FROMt1WHEREidNOTIN(SELECTidFROMt2);-- 若t2.id有NULL结果异常-- ✅ 推荐替代方案NOT EXISTSSELECT*FROMt1WHERENOTEXISTS(SELECT1FROMt2WHEREt2.idt1.id);3.3 子查询核心优化技术Doris自动完成Doris的查询优化器会自动对於子查询进行优化无需用户手动干预核心优化技术有3种1去关联化Decorrelation解决关联子查询效率低的问题核心逻辑将依赖外层表的关联子查询转换为Join Group By的形式避免嵌套循环提升执行效率。-- 原始关联子查询效率低嵌套循环SELECT*FROMorders oWHEREo.amount(SELECTAVG(amount)FROMorders o2WHEREo2.user_ido.user_id);-- Doris自动优化后去关联化转换为JoinSELECTo.*FROMorders oJOIN(SELECTuser_id,AVG(amount)ASavg_amtFROMordersGROUPBYuser_id)tmpONo.user_idtmp.user_idWHEREo.amounttmp.avg_amt;2Semi/Anti Join转换提升IN/EXISTS子查询效率核心逻辑将IN子查询转换为LEFT SEMI JOIN将NOT EXISTS子查询转换为LEFT ANTI JOIN充分利用Join优化器如Broadcast Join提升执行效率。-- 原始IN子查询SELECT*FROMt1WHEREidIN(SELECTidFROMt2);-- 自动转换为LEFT SEMI JOINSELECTt1.*FROMt1LEFTSEMIJOINt2ONt1.idt2.id;3谓词下推Predicate Pushdown减少子查询扫描数据量核心逻辑对FROM子查询派生表将外层WHERE条件下推到子查询内部提前过滤无效数据减少子查询的扫描量和计算量。-- 原始查询外层条件未下推子查询扫描全表SELECT*FROM(SELECTid,nameFROMusers)uWHEREu.id100;-- Doris自动优化后谓词下推子查询仅扫描id100的数据SELECTid,nameFROMusersWHEREid100;四、多维聚合分析Grouping Sets / Cube / RollupDoris支持标准SQL的多维聚合扩展Grouping Sets、Cube、Rollup主要用于BI报表、数据下钻分析场景能够快速生成多维度聚合结果无需编写多个独立查询。4.1 语法支持生产实战示例以sales表包含city、category、sales字段为例展示三种多维聚合的用法1Grouping Sets指定多个分组组合手动指定需要聚合的分组维度组合灵活控制聚合结果适合自定义多维报表。-- 示例同时按city、category、无分组全局聚合三个维度聚合SELECTcity,category,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city),(category),());-- 结果包含3类数据按城市聚合、按分类聚合、全局聚合2Cube所有可能的维度组合自动生成所有维度的组合共2^N种N为维度数适合全维度下钻分析维度不宜过多≤3个。-- 示例city和category两个维度生成所有4种组合(city,category)、(city)、(category)、()SELECTcity,category,SUM(sales)AStotal_salesFROMsalesGROUPBYCUBE(city,category);3Rollup层级聚合自上而下按维度的层级关系生成自上而下的聚合结果适合层级报表如年→月→日、区域→城市。-- 示例按year→month→day层级聚合生成4种组合SELECTyear,month,day,SUM(sales)AStotal_salesFROMsalesGROUPBYROLLUP(year,month,day);-- 组合顺序(year,month,day) → (year,month) → (year) → ()4.2 执行原理逻辑计划阶段将多维聚合语句展开为多个GROUP BY语句通过UNION ALL合并结果物理计划阶段共享Scan扫描和部分Agg聚合计算避免重复扫描基表提升效率结果标识使用GROUPING()函数区分NULL值——NULL是真实数据还是聚合后的汇总标记1表示该维度未参与聚合0表示参与聚合。-- 示例用GROUPING()区分NULL来源SELECTcity,category,GROUPING(city)ASgrp_city,-- 1city未参与聚合0参与聚合GROUPING(category)ASgrp_category,SUM(sales)AStotal_salesFROMsalesGROUPBYCUBE(city,category);4.3 性能考量生产选型建议实现方式基表扫描次数适用场景注意事项单次Cube/Grouping Sets1次维度数≤3聚合结果集可控维度过多≥4会导致结果集爆炸引发OOM多次独立GROUP BY查询N次N为维度组合数维度多、结果集大避免OOM扫描次数多总耗时可能更高 生产替代方案对高频的Cube/Grouping Sets查询建议预先创建物化视图同步Rollup或异步MV将预聚合结果持久化查询时直接命中MV大幅提升效率。五、分析函数窗口函数Window FunctionDoris全面支持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([PARTITIONBYpartition_expr]-- 窗口分区类似GROUP BY分区内独立计算[ORDERBYorder_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执行模型排序阶段按PARTITION BY ORDER BY对数据进行排序这是窗口函数的性能瓶颈滑动计算维护窗口内的计算状态如Sum、Count避免重复计算向量化支持Doris 2.0 对SUM、ROW_NUMBER、LAG、LEAD等常见窗口函数实现了向量化加速提升执行效率。2性能关键点生产调优重点常见问题优化方案大Partition排序慢单分区数据量过大确保PARTITION BY列是高基数列避免单分区数据量过大必要时拆分分区无界窗口UNBOUNDED内存占用高慎用于大表若必须使用调大BE内存避免OOMRANGE窗口含时间维度计算慢使用DATETIME类型列避免字符串类型转换提前对时间列建立索引3典型应用场景生产实战示例-- 场景1用户行为序列分析获取用户上一次访问时间SELECTuser_id,event_time,LAG(event_time,1)OVER(PARTITIONBYuser_idORDERBYevent_time)ASlast_visit_timeFROMuser_log;-- 场景2销售排行榜按销售额排名相同销售额排名相同SELECTproduct_id,sales,RANK()OVER(ORDERBYsalesDESC)ASsales_rankFROMdaily_sales;-- 场景37日移动平均计算每日销售额的7日均值含当日SELECTdt,revenue,AVG(revenue)OVER(ORDERBYdt RANGEBETWEENINTERVAL6DAYPRECEDINGANDCURRENTROW)ASma7_revenueFROMsales_daily;六、高级功能对比与选型指南生产快速决策结合前面的四大模块整理生产中常见查询功能的选型建议帮你快速决策避免踩坑功能适用场景性能提示Broadcast Join大表 小维表关联维表1GB控制维表大小避免OOM可通过Hint调整阈值Colocation Join同分桶、同分区策略的大事实表关联建表时提前规划分桶键和协同组物化视图固定聚合、多表Join的高频查询替代运行时Cube/Join大幅提升查询速度