数据库-GaussDB-基础篇-典型SQL调优点-多表JOIN案例

  • 某业务慢SQL示例：
explain analyse SELECT
a.gl_sr,
a.bs_st,
a.id_chk_no,
a.tt_tr_no,
a.bs_tl_no,
a.tt_it_no,
a.tt_dt,
count(a.gl_sr) countleng,
count(a.gl_sr) busleng,
a.im,
a.lsuid,
a.fby,
a.fbt,
a.svglsn,
a.svt,
b.ctn_amt,
b.ctn_tel,
b.daacnt,
b.mc,
d.note1,
d.idchno,
substring(d.elco,1,3) as ebservcode
from ix_log_busi_log a
LEFT JOIN ix_log_ele_log b ON a.gl_sr = b.gl_sr
LEFT JOIN ix_net_ins_appr_info info on a.id_chk_no=info.tsct_srlno
LEFT JOIN ix_log_cust_log c on a.svglsn = c.svglsn
LEFT JOIN ix_upm_men_info d on a.mc=d.mc
LEFT JOIN ix_log_com_log ii on a.gl_sr=ii.gl_sr
where a.tt_it_no='803102' and a.tt_tr_no='100857'
and a.tt_dt >= '20250801' and a.tt_dt <= '20260310' and a.im='Y'
group by a.gl_sr,a.bs_st,a.id_chk_no,a.tt_tr_no,a.bs_tl_no,a.tt_it_no,a.tt_dt,a.im,a.lsuid,a.fby,a.fbt,a.svglsn,a.svt,b.ctn_amt,b.ctn_tel,b.daacnt,b.mc,d.note1,d.idchno,substring(d.elco,1,3)
order by a.gl_sr;

表关联关系

主表 ix_log_busi_log a LEFT JOIN 5张表 ix_log_ele_log b ix_net_ins_appr_info info ix_log_cust_log cix_upm_men_info d ix_log_com_log ii
SQL关联顺序：((( (a LEFT JOIN b) LEFT JOIN info ) LEFT JOIN c ) INNER JOIN d ) LEFT JOIN ii

完整执行计划树（缩进代表父子层级，id 为算子编号）

1  -> Streaming (type: GATHER)
2    -> Sort
3      -> HashAggregate
4        -> Nested Loop Left Join (5, 23)
5          -> Hash Right Join (6, 8)
6            -> Streaming(REDISTRIBUTE)
7              -> Seq Scan on ib_log_comm_log ii
8            -> Hash
9              -> Hash Right Join (10,12)
10                -> Streaming(REDISTRIBUTE)
11                  -> Seq Scan on te_log_element_log b
12                -> Hash
13                  -> Streaming(REDISTRIBUTE)
14                    -> Nested Loop Left Join (15,22)
15                      -> Streaming(PART REDISTRIBUTE PART ROUNDROBIN)
16                        -> Hash Right Join (17,19)
17                          -> Streaming(REDISTRIBUTE)
18                            -> Seq Scan on te_network_inspect_approve_info info
19                          -> Hash
20                            -> Streaming(PART REDISTRIBUTE PART ROUNDROBIN)
21                              -> Index Scan using idx_ib_log_business_log_index1 on ib_log_business_log a
22                      -> Index Scan using pk_ib_log_custser_log on ib_log_custser_log c
23          -> Index Scan using gbl_menunum_idx on ib_upm_menu_info d
  • Predicate Information 谓词与 JOIN 条件：
5  -- Hash Right Join (6, 8)
Hash Cond: ((ii.gl_sr)::text = (a.gl_sr)::text)

9  -- Hash Right Join (10, 12)
Hash Cond: ((b.gl_sr)::text = (a.gl_sr)::text)
Skew Join Optimized by Statistic

16 -- Hash Right Join (17, 19)
Hash Cond: ((info.tsct_srlno)::text = (a.id_chk_no)::text)
Skew Join Optimized by Statistic

21 -- Index Scan using idx_ib_log_business_log_index1 on ib_log_business_log a
Index Cond: ((tt_it_no)::text = '803102'::text) AND ((tt_it_no)::text = '100857'::text)
Filter: ((tt_dt)::text >= '20250801'::text) AND ((tt_dt)::text <= '20260310'::text) AND ((im)::text = 'Y'::text)

22 -- Index Scan using pk_ix_log_cust_log on ix_log_cust_log c
Index Cond: ((a.svglsn)::text = (svglsn)::text)

23 -- Index Scan using gbl_menunum_idx on ib_upm_menu_info d
Index Cond: ((a.mc)::text = (mc)::text)

Memory Information内存使用信息

所有 Hash 算子内存占用极低，哈希表全部驻留内存，无磁盘溢出；Sort 算子内存充足，没有大量磁盘排序，本次查询数据量不大。

Coordinator Query Peak Memory:
Query Peak Memory: 13MB

Datanode:
Max Query Peak Memory: 1MB
Min Query Peak Memory: 1MB

2 -- Sort
Sort Method: quicksort  Memory: 25kB ~ 26kB
Sort Method: quicksort  Disk: 1024kB ~ 0kB

8 -- Hash
Max Buckets: 32768  Max Batches: 1
Max Memory Usage: 2kB
Min Buckets: 32768  Min Batches: 1
Min Memory Usage: 1kB

12 -- Hash
Max Buckets: 32768  Max Batches: 1
Max Memory Usage: 2kB
Min Buckets: 32768  Min Batches: 1
Min Memory Usage: 1kB

19 -- Hash
Max Buckets: 32768  Max Batches: 1
Max Memory Usage: 2kB
Min Buckets: 32768  Min Batches: 1
Min Memory Usage: 1kB

用户自定义性能剖面

这部分是 CN 协调节点下发任务、建立 DN 连接、序列化执行计划、下发 SQL 命令的开销。整体耗时很小，集群任务下发效率正常。

Plan Node Id: 1  Track name: coordinator get datanode connection
(actual time=[0.090, 0.090], calls=[1, 1])

Plan Node Id: 1  Track name: Coordinator check and update node definition
(actual time=[0.004, 0.004], calls=[1, 1])

Plan Node Id: 1  Track name: Coordinator serialize plan
(actual time=[3.276, 3.276], calls=[1, 1])

Plan Node Id: 1  Track name: Coordinator send query id with sync
(actual time=[1.027, 1.027], calls=[1, 1])

Plan Node Id: 1  Track name: Coordinator send begin command
(actual time=[0.003, 0.003], calls=[1, 1])

Plan Node Id: 1  Track name: Coordinator start transaction and send query
(actual time=[0.218, 0.218], calls=[1, 1])

Plan Node Id: 2  Track name: Datanode start up stream thread
(actual time=[0.292, 0.352], calls=[1, 1])

  • Query Summary查询整体汇总：

  • Planner runtime 203.281ms：6 表关联，优化器枚举多种 JOIN 组合、统计信息代价计算，生成分布式执行计划耗时。

  • Datanode executor run time 约 1029ms：整条 SQL 主要耗时在 DN 端，包含多层 Hash Join、多次跨节点 Streaming 重分布、索引查找。CN 本身只负责调度、最后汇总，计算压力很小。

  • Total runtime：1094.893ms，SQL 从提交到全部结果返回总耗时约 1.09 秒。

Datanode executor start time [dn_6009_6010_6011_6012, dn_6001_6002_6003_6004]: [51.450 ms ,52.551 ms]
Datanode executor run time [dn_6005_6006_6007_6008, dn_6013_6014_6015_6016]: [1029.770 ms,1030.450 ms]
Datanode executor end time [dn_6009_6010_6011_6012, dn_6001_6002_6003_6004]: [0.249 ms,0.370 ms]

Coordinator executor start time: 0.744 ms
Coordinator executor run time: 1094.029 ms
Coordinator executor end time: 0.089 ms

Planner runtime: 203.281 ms
Plan size: 19608 byte
Query Id: 73464969339729871
Total runtime: 1094.893 ms

核心原理与调优理解

  1. JOIN 执行顺序

    从内向外逐层叠加表，一次只新增一张表：

    a left

    join info【plan id 16~21】 → 中间集【a+info】

    A+info left join c【plan id 14~22】→中间集【a+info+c】a

    +info+c left join b【plan id 9~22】 →中间集【a+info+c+b】a

    +info+c+b left join ii 【plan id 5~22】→中间集【a+info+c+b+ii】a

    +info+c+b+ii left join d【plan id 4~23】 →总结果集合【a+info+c+b+ii+d】

    d 表是最后一步才关联，由最外层 Nested Loop Left Join 驱动。外表每一行，都要去d表做索引 lookup。

  2. 分布式重分布关键点

    每一层 Hash Join 使用的关联键不同：id_chk_no → gl_sr。

    每更换 JOIN key，就会触发Streaming REDISTRIBUTE，跨 DN 网络传输中间结果。多层 JOIN 会叠加网络开销，这是多表大关联 SQL 最主要的性能瓶颈。

  3. 优势

    a表前置索引过滤，提前缩小数据集；c、d 表使用索引扫描，单行 lookup 效率高；所有 Hash Join 哈希表都在内存，无磁盘溢出。

  4. 潜在风险

    最外层 NL 左连接 d 表：如果中间结果行数膨胀，d表索引查找次数会线性增加，性能快速恶化。多表连续关联，多层重分布，数据量变大后网络 IO 会急剧上涨。

  5. 下推视角总结

    内层部分算子可以下推到 DN 本地执行；但因为多层 JOIN 的关联字段不断变化，需要多次重分布，不能全程把所有计算全部留在 DN。分布式调优的核心目标就是减少跨节点重分布次数，尽量让计算在 DN 本地完成，减少 CN 汇聚的数据量。

优化HINT

/*+ set(enable_stream_operator off) set(enable_remote_param on) set(enable_hashjoin off) leading(((a ii))b) */

Hint 说明：

  1. set(enable_stream_operator off)：关闭流式算子，控制分布式重分布行为

  2. set(enable_remote_param on)：开启远程参数，支持参数下推到 DN

  3. set(enable_hashjoin off)：关闭 HashJoin，强制走 NestedLoop 嵌套循环

  4. leading(((a ii))b) ：强制指定 JOIN 驱动顺序，固定 A 表作为驱动表，控制表关联顺序，改变优化器自主选择的 join 次序

-> Nested Loop Left Join
    -> Nested Loop Left Join
        -> Nested Loop Left Join
            -> Nested Loop Left Join
                -> Nested Loop Left Join
                    -> Data Node Scan on "ix_log_busi_log" REMOTE_TABLE_QUERY_
                    -> Data Node Scan on ix_log_com_log REMOTE_TABLE_QUERY_
                -> Data Node Scan on ix_log_ele_log REMOTE_TABLE_QUERY_
            -> Data Node Scan on ix_net_ins_appr_info REMOTE_TABLE_QUERY_
        -> Data Node Scan on ix_log_cust_log REMOTE_TABLE_QUERY_
    -> Data Node Scan on ix_upm_men_info REMOTE_TABLE_QUERY_

Total runtime: 77.799 ms

优化前后对比

  1. 优化前总耗时：1094.893ms

  2. Hint 优化后总耗时：77.799ms

    性能提升巨大，从 1.09 秒下降到 78ms 左右。

原理解读

  • 耗时对比

    • 优化前：1094.893ms

    • Hint 优化后：77.799ms

      性能提升巨大，从 1.09 秒下降至 78ms 左右。

  • 优化前执行特点

    优化器自主选择多层 HashJoin + 多次Streaming REDISTRIBUTE跨节点重分布。每更换一次 JOIN Key，就需要在 DN 之间搬迁中间结果，网络传输开销是主要瓶颈。

  • Hint 优化后的原理

    • 关闭 HashJoin，全部改为 NestedLoop 嵌套循环；

    • 通过leading强制指定驱动表顺序，A 表作为主驱动，并且把过滤条件下推到 DN 执行（REMOTE_TABLE_QUERY）；

    • 大量查询逻辑下推到 DN 本地执行，减少跨节点数据重分布，大幅减少网络传输的数据量；

    • 只将少量最终结果汇总到 CN，降低 CN 汇聚压力。

  • Hint 的风险与注意事项

    • Hint 属于人工干预优化器，是应急调优手段，不是永久最优方案。当表数据量、数据分布发生变化时，Hint 可能产生很差的执行计划；

    • 多条 leading 写在一起容易出现 hint 冲突，冲突的 hint 会被优化器直接丢弃，写 hint 要精简；

    • NestedLoop 只适合小结果集场景。如果 A 表过滤后返回行数变多，NL 性能会快速恶化，大数据量场景 HashJoin 更加合适。

  • 分布式下推调优总结

    分布式 SQL 调优的核心就是尽量把计算下推到 DN 本地，减少跨 DN 的重分布、减少网络传输，不要把大量中间数据全部汇聚到 CN。

    优化器自动生成的计划不一定最优，在业务稳定、数据分布变化小的场景，可以使用 Hint 固定 JOIN 顺序与算子类型；但生产环境使用 Hint 需要持续观察数据变化，防止后续出现性能回退。

posted @ 2026-09-27 12:46  打印helloworld  阅读(1)  评论(0)    收藏  举报