GaussDB数据库--SQL执行计划详解

GaussDB-详解

如SQL执行计划概述节中所说，EXPLAIN会显示执行计划，但并不会实际执行SQL语句。EXPLAIN ANALYZE和EXPLAIN PERFORMANCE两者都会实际执行SQL语句并返回执行信息。在这一节将具体表明执行计划及执行信息。

执行计划

以如下SQL语句为例：

SELECT d, avg(a::numeric(7, 2)) FROM t_distinct GROUP BY d; SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2; 复制代码

执行EXPLAIN的输出为：

1. gaussdb=#  explain select d, avg(a::numeric(7, 2)) from t_distinct group by d;
2. id |                     operation                     | E-rows | E-width | E-costs
3. ----+---------------------------------------------------+--------+---------+---------
4.   1 | ->  Row Adapter                                   |     20 |      40 | 14.52
5.   2 |    ->  Vector Streaming (type: GATHER)            |     20 |      40 | 14.52
6.   3 |       ->  Vector Hash Aggregate                   |     20 |      40 | 13.59
7.   4 |          ->  Vector Streaming(type: REDISTRIBUTE) |     20 |       8 | 13.33
8.   5 |             ->  Seq Scan on t_distinct         |     20 |       8 | 13.01
9. (5 rows)
10. gaussdb=#  EXPLAIN SELECT * FROM t1,t2 WHERE t1.c1 = t2.c2;
11.                             QUERY PLAN                             
12. -------------------------------------------------------------------
13. Hash Join  (cost=23.73..341.30 rows=16217 width=180)
14.    Hash Cond: (t1.c1 = t2.c2)
15.    ->  Seq Scan on t1  (cost=0.00..122.17 rows=5317 width=76)
16.    ->  Hash  (cost=16.10..16.10 rows=610 width=104)
17.          ->  Seq Scan on t2  (cost=0.00..16.10 rows=610 width=104)
18. (5 rows)

复制代码

执行计划字段解读（横向）：

• id：执行算子节点编号。
  
• operation：具体的执行节点算子名称。 Vector前缀的算子是指向量化执行引擎算子。
  Streaming是一个特殊的算子，实现了分布式架构的核心数据shuffle功能，Streaming共有三种形态，分别对应了分布式结构下差别的数据shuffle功能：   
  
  
  
  • Streaming(type: GATHER)：作用是coordinator从DN网络数据。
    
  • Streaming(type: REDISTRIBUTE)：作用是DN根据选定的列把数据重分布到全部的DN。
    
  • Streaming(type: BROADCAST)：作用是把当前DN的数据广播给其他全部的DN。
    
  
  
• E-rows：每个算子估算的输出行数。
  
• E-memory：DN上每个算子估算的内存利用量，只有DN上执行的算子会显示。某些场景会在估算的内存利用量后利用括号显示该算子在内存资源充足下可以自动扩展的内存上限。必要开启enable_dynamic_workload参数后开启内存利用量估算，且存在估算值大于0的算子时才会显示该字段。
  
• E-width：每个算子输出元组的估算宽度。
  
• E-costs：每个算子估算的执行代价。   
  
  
  
  • E-costs是优化器根据成本参数界说的单元来权衡的，习惯上以磁盘页面抓取为1个单元，其他开销参数将参照它来设置。
    
  • 每个节点的开销（E-costs值）包罗它的全部子节点的开销。
    
  • 开销只反映了优化器关心的问题，并没有把结果行传递给客户端的时间思量进去。固然这个时间大概在实际的总时间里占据相当紧张的分量，但是被优化器忽略了，因为它无法通过修改规划来改变。
    
  
  

执行计划层级解读（纵向）：

• 第一层：Seq Scan on t2 表扫描算子，用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从buffer大概磁盘上读上来输送给上层节点参与盘算。
  
• 第二层：Hash Hash算子，作用是把下层盘算输送上来的算子盘算hash值，为后续hash join操纵举行数据准备。
  
• 第三层：Seq Scan on t1 表扫描算子，用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从buffer大概磁盘上读上来输送给上层节点参与hash join盘算。
  
• 第四层：Hash Join join算子，紧张作用是将t1表和t2表的数据通过hash join的方式连接，并输出结果数据。
  

• 第一层：Seq Scan on t_distinct 表扫描算子，用Seq Scan的方式扫描表t_distinct。这一层的作用是把表t_distinct的数据从buffer大概磁盘上读上来输送给上层节点参与盘算。
  
• 第二层：Vector Streaming(type: REDISTRIBUTE) Shuffle算子，此处REDISTRIBUTE类型的Shuffle算子作用是DN根据选定的列把数据重分布到全部DN。
  
• 第三层：Vector Hash Aggregate 聚合算子，作用是把下层盘算输送上来的算子做聚合操纵(group by)。
  
• 第四层：Vector Streaming (type: GATHER) Shuffle算子，此处GATHER类型的Shuffle算子作用是把数据从DN汇聚到CN。
  
• 第五层：Row Adapter 存储格式转化算子，紧张作用是把内存中列式格式数据转为行式数据，以便客户端展示。
  

最顶层算子为Data Node Scan时，必要设置enable_fast_query_shipping为off才能看到具体的执行计划，如下计划：

gaussdb=#  explain select c1,count(1) from t1 group by c1;                     QUERY PLAN                     -------------------------------------------------- Data Node Scan  (cost=0.00..0.00 rows=0 width=0)    Node/s: All datanodes (2 rows) 复制代码

设置enable_fast_query_shipping参数之后，执行计划显示如下：

1. gaussdb=#  set enable_fast_query_shipping=off;
2. SET
3. gaussdb=#  explain select c1,count(1) from t1 group by c1;
4. id |          operation           | E-rows | E-width | E-costs
5. ----+------------------------------+--------+---------+---------
6.   1 | ->  Streaming (type: GATHER) |     20 |      12 | 14.23
7.   2 |    ->  HashAggregate         |     20 |      12 | 13.30
8.   3 |       ->  Seq Scan on t1     |     20 |       4 | 13.13
9. (3 rows)

复制代码

执行计划中的紧张关键字阐明：

• 表访问方式   
  
  
  
  • Seq Scan 全表顺序扫描。
    
  • Index Scan 优化器决定利用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取开销高很多，但是因为并非全部表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。利用两层规划的原因是，上层规划节点在读取索引标识出来的行位置之前，会先将它们按照物理位置排序，如允许以最小化独立抓取的开销。
    如果在WHERE语句中的存在多个字段上都有索引，那么优化器大概会利用索引的AND或OR的组合。
    索引扫描可以分为以下几类，差异在于索引的排序机制。
         
    
    
    
    • Bitmap Index Scan 利用位图索引抓取数据页。
      
    • Index Scan using index_name 利用简朴索引搜索，该方式按照索引键的顺序在索引表中抓取数据。该方式最常用于在大数据量表中只抓取少量数据的情况，大概通过ORDER BY条件匹配索引顺序的查询，以减少排序时间。
      
    • Index-Only Scan 当必要的全部信息都包罗在索引中时，仅索引扫描便可获取全部数据，不必要引用表。
      
    
    
  • Bitmap Heap Scan 从其他操纵创建的位图中读取页面，过滤掉不符合条件的行。位图堆扫描可避免随机I/O，加速读取速度。
    
  • TID Scan 通过TupleID扫描表。
    
  • Index Ctid Scan 通过Ctid上的索引对表举行扫描。
    
  • CTE Scan CTE对子查询的操纵举行评估并将查询结果临时存储，相当于一个临时表。CTE Scan算子对该临时表举行扫描。
    
  • Foreign Scan 从远程数据源读取数据。
    
  • Function Scan 获取函数返回的结果集，将它们作为从表中读取的行并返回。
    
  • Sample Scan 查询并返回采样数据。
    
  • Subquery Scan 读取子查询的结果。
    
  • Values Scan 作为VALUES命令的一部分读取常量。
    
  • WorkTable Scan 工作表扫描。在操纵中心阶段读取，通常是利用WITH RECURSIVE声明的递归操纵。
    
  
  
• 表连接方式   
  
  
  
  • Nested Loop 嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上发起要有索引。
    
  • (Sonic) Hash Join 哈希连接，适用于数据量大的表连接方式。优化器利用两个表中较小的表，利用连接键在内存中创建hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所利用hash表结构差别，不影响执行的结果集。
    
  • Merge Join 归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行归并连接时，并不必要再排序，此时归并连接的性能优于哈希连接。
    
  
  
• 运算符   
  
  
  
  • sort 对结果集举行排序。
    
  • filter EXPLAIN输出显示WHERE子句看成一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件，而且只输出符合条件的行。因为有WHERE子句，预计的输出行数降低了。不过，扫描仍将必须访问全部 10000 行，因此开销没有降低，实际上还增加了（确切的说，通过10000 * cpu_operator_cost）以反映检查WHERE条件的额外CPU时间。
    
  • LIMIT LIMIT限定了执行结果的输出记录数。如果增加了LIMIT，那么不是全部的行都会被检索到。
    
  • Append 归并子操纵的结果。
    
  • Aggregate 将查询行产生的结果举行组合。可以是GROUPBY、UNION、SELECT DISTINCT子句等函数的组合。
    
  • BitmapAnd 位图的AND操纵，通过该操纵构成匹配更复杂条件的位图。
    
  • BitmapOr 位图的OR操纵，通过该操纵构成匹配更复杂条件的位图。
    
  • Gather 将并行线程的数据汇总。
    
  • Group 对行举行分组，以举行GROUP BY操纵。
    
  • GroupAggregate 聚合GROUP BY操纵的预排序行。
    
  • Hash 对查询行举行散列操纵，以供父查询利用。通常用于执行JOIN操纵。
    
  • HashAggregate 利用哈希表聚合GROUP BY的结果行。
    
  • Merge Append 以保留排序顺序的方式对子查询结果举行组合，可用于组合表分区中已排序的行。
    
  • ProjectSet 对返回的结果集执行函数。
    
  • Recursive Union 对递归函数的全部步骤举行并集操纵。
    
  • SetOp 集合运算，如INTERSECT或EXCEPT。
    
  • Unique 从有序的结果集中删除重复项。
    
  • HashSetOp
    一种用于INTERSECT或EXCEPT等集合操纵的计谋，利用Append来避免预排序的输入。
    
  • LockRows 锁定有问题的行以制止其他查询写入，但允许读。
    
  • Materialize 将子查询的结果存储在内存里，以方便父查询快速访问获取。
    
  • Result 在不举行扫描的情况下返回一个值。
    
  • WindowAgg 窗口聚合函数，一般由OVER语句触发。
    
  • Merge 归并操纵。
    
  • StartWith Operator 条理查询算子，用于执行递归查询操纵。
    
  • Rownum 对查询结果的行编号举行条件过滤。通常出现在rownum子句里。
    
  • Row Adapter 行执行引擎，将向量化引擎转换成行引擎。
    
  • Vector Adapter 向量化执行引擎，将行引擎转换成向量化引擎。
    
  • Index Cond 索引扫描条件。
    
  • Unpivot 转置算子。
    
  
  
• 分区剪枝相关信息   
  
  
  
  • Iterations 分区迭代算子对一级分区的迭代次数。如果显示PART则为动态剪枝场景。
    例如：Iterations：4表现迭代算子必要遍历4个一级分区。Iterations：PART表现遍历一级分区个数必要由分区键上的参数条件决定。
    
     
  
  
  
  • Selected Partitions 一级分区剪枝的结果，m..n表现m到n号分区被剪枝选中，多个不连续的分区由逗号连接。
    例如：Selected Partitions: 2..4,7表现2、3、4、7四个分区被选中。
    
  
  
• 其他关键字   
  
  
  
  • Partitioned 对具体分区的操纵。
    
  • Partition Iterator 分区迭代器，通常代表子查询是对分区的操纵。
    
  • InitPlan 非相关子计划。
    
  • Remote Query 下推到数据节点上的查询语句。
    
  • Exec Nodes 具体执行计划的节点。
    
  • Data Node Scan on 阐明语句已下推给DN执行。
    
  
  

• VectorXXX算子为向量化执行引擎算子，与平凡算子运算方式一致，在此不再一一摆列阐明。
  

执行信息

在SQL调优过程中经常必要执行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE检察SQL语句实际执行信息，通过对比实际执行与优化器估算之间的差别来为优化提供依据。EXPLAIN PERFORMANCE相对于EXPLAIN ANALYZE增加了每个DN上的执行信息。
以如下SQL语句为例：

1. select count(1) from t1;

复制代码

执行EXPLAIN PERFORMANCE输出为：

1. gaussdb=# explain performance select count(1) from t1;
2. id |             operation              |    A-time     | A-rows | E-rows | E-distinct | Peak Memory  | E-memory | A-width | E-width | E-costs
3. ----+------------------------------------+---------------+--------+--------+------------+--------------+----------+---------+---------+---------
4.   1 | ->  Aggregate                      | 9.326         |      1 |      1 |            | 14KB         |          |         |       8 | 209.10
5.   2 |    ->  Streaming (type: GATHER)    | 9.281         |      2 |      2 |            | 80KB         |          |         |       8 | 209.10
6.   3 |       ->  Aggregate                | [5.981,6.491] |      2 |      2 |            | [13KB, 13KB] | 1MB      |         |       8 | 209.01
7.   4 |          ->  Seq Scan on public.t1 | [2.553,2.909] |  20000 |  20000 |            | [15KB, 15KB] | 1MB      |         |       0 | 184.00
8. (4 rows)
10.           Memory Information (identified by plan id)         
11. --------------------------------------------------------------
12. Coordinator Query Peak Memory:
13.          Query Peak Memory: 0MB
14. DataNode Query Peak Memory
15.          datanode1 Query Peak Memory: 2MB
16.          datanode2 Query Peak Memory: 0MB
17.    1 --Aggregate
18.          Peak Memory: 14KB, Estimate Memory: 64MB
19.    2 --Streaming (type: GATHER)
20.          Peak Memory: 80KB, Estimate Memory: 64MB
21.    3 --Aggregate
22.          datanode1 Peak Memory: 13KB, Estimate Memory: 1024KB
23.          datanode2 Peak Memory: 13KB, Estimate Memory: 1024KB
24.    4 --Seq Scan on public.t1
25.          datanode1 Peak Memory: 15KB, Estimate Memory: 1024KB
26.          datanode2 Peak Memory: 15KB, Estimate Memory: 1024KB
27. (15 rows)
29. Targetlist Information (identified by plan id)
30. ------------------------------------------------
31.    1 --Aggregate
32.          Output: count((count(1)))
33.    2 --Streaming (type: GATHER)
34.          Output: (count(1))
35.          Node/s: All datanodes
36.    3 --Aggregate
37.          Output: count(1)
38.    4 --Seq Scan on public.t1
39.          Output: c1, c2, c3, c4, c5
40.          Distribute Key: c1
41. (10 rows)
43.                                          Datanode Information (identified by plan id)                                         
44. ------------------------------------------------------------------------------------------------------------------------------
45.    1 --Aggregate
46.          (actual time=9.326..9.326 rows=1 loops=1)
47.          (Buffers: 0)
48.          (CPU: ex c/r=-17813058098842432, ex row=2, ex cyc=-35626116197684864, inc cyc=71252232399791904)
49.    2 --Streaming (type: GATHER)
50.          (actual time=8.628..9.281 rows=2 loops=1)
51.          (Buffers: 0)
52.          (CPU: ex c/r=53439174298738384, ex row=2, ex cyc=106878348597476768, inc cyc=106878348597476768)
53.    3 --Aggregate
54.          datanode1 (actual time=5.980..5.981 rows=1 loops=1)
55.          datanode2 (actual time=6.491..6.491 rows=1 loops=1)
56.          datanode1 (Buffers: shared hit=85)
57.          datanode2 (Buffers: shared hit=84)
58.          datanode1 (CPU: ex c/r=-35622581151734248, ex row=10078, ex cyc=-359004372847177760768, inc cyc=71252232395610160)
59.          datanode2 (CPU: ex c/r=-35622525572390744, ex row=9922, ex cyc=-353446698729260974080, inc cyc=71252232398542704)
60.    4 --Seq Scan on public.t1
61.          datanode1 (actual time=0.018..2.553 rows=10078 loops=1)
62.          datanode2 (actual time=0.017..2.909 rows=9922 loops=1)
63.          datanode1 (Buffers: shared hit=85)
64.          datanode2 (Buffers: shared hit=84)
65.          datanode1 (CPU: ex c/r=35629651228376004, ex row=10078, ex cyc=359075625079573381120, inc cyc=359075625079573381120)
66.          datanode2 (CPU: ex c/r=35629706809278324, ex row=9922, ex cyc=353517950961659543552, inc cyc=353517950961659543552)
67. (22 rows)
69.                            User Define Profiling                           
70. ---------------------------------------------------------------------------
71. Plan Node id: 2  Track name: coordinator get datanode connection
72.         coordinator1: (time=0.019 total_calls=1 loops=1)
73. Plan Node id: 2  Track name: Coordinator serialize plan
74.         coordinator1: (time=1.059 total_calls=1 loops=1)
75. Plan Node id: 2  Track name: Coordinator send begin command
76.         coordinator1: (time=0.003 total_calls=1 loops=1)
77. Plan Node id: 2  Track name: Coordinator start transaction and send query
78.         coordinator1: (time=0.045 total_calls=1 loops=1)
79. (8 rows)
81.                         ====== Query Summary =====                        
82. --------------------------------------------------------------------------
83. Datanode executor start time [datanode1, datanode2]: [0.421 ms,0.450 ms]
84. Datanode executor run time [datanode1, datanode2]: [6.002 ms,6.528 ms]
85. Datanode executor end time [datanode2, datanode1]: [0.027 ms,0.028 ms]
86. Remote query poll time: 0.000 ms, Deserialze time: 0.000 ms
87. System available mem: 8222310KB
88. Query Max mem: 8310784KB
89. Query estimated mem: 2048KB
90. Coordinator executor start time: 0.181 ms
91. Coordinator executor run time: 9.340 ms
92. Coordinator executor end time: 0.052 ms
93. Planner runtime: 0.421 ms
94. Plan size: 3122 byte
95. Query Id: 72339069014648468
96. Total runtime: 9.657 ms
97. (14 rows)

复制代码

上述示例中显示执行信息分为以下7个部分：

• 以表格的形式将计划显示出来，包罗有11个字段，分别是：id、operation、A-time、A-rows、E-rows、E-distinct、Peak Memory、E-memory、A-width、E-width和E-costs。此中计划类字段（id、operation以及E开头字段）的含义与执行EXPLAIN时的含义一致，请参见执行计划末节中的阐明。A-time、A-rows、E-distinct、Peak Memory、A-width的含义阐明如下：   
  
  
  
  • A-time：表现当前算子执行完成时间，一般DN上执行的算子的A-time是由[]括起来的两个值，分别表现此算子在全部DN上完成的最短时间和最长时间。
    
  • A-rows：表现当前算子的实际输出元组数。
    
  • E-distinct：表现hashjoin算子的distinct估计值。
    
  • Peak Memory：此算子在每个DN上执行时利用的内存峰值。
    
  • A-width：表现当前算子每行元组的实际宽度，仅对于重内存利用算子会显示，包罗：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，此中(Vec)HashJoin盘算的宽度是其右子树算子的宽度，会显示在其右子树上。
    
  
  
• Predicate Information (identified by plan id)： 这一部分紧张显示的是静态信息，即在整个计划执行过程中不会变的信息，紧张是一些join条件和一些filter信息。
  
• Memory Information (identified by plan id)： 这一部分显示的是整个计划中会将内存的利用情况打印出来的算子的内存利用信息，紧张是Hash、Sort算子，包罗算子峰值内存（peak memory），控制内存（control memory），估算内存利用（operator memory），执行时实际宽度（width），内存利用自动扩展次数（auto spread num），是否提前下盘（early spilled），以及下盘信息，包罗重复下盘次数（spill Time(s)），内外表下盘分区数（inner/outer partition spill num），下盘文件数（temp file num），下盘数据量及最小和最大分区的下盘数据量（written disk IO [min, max] ）。
  
• Targetlist Information (identified by plan id) 这一部分显示的是每一个算子输出的目标列。
  
• DataNode Information (identified by plan id)： 这一部分会将各个算子的执行时间、CPU、buffer的利用情况全部打印出来。
  
• User Define Profiling 这一部分显示的是CN和DN、DN和DN建连的时间，以及存储层的一些执行信息。
  
• ====== Query Summary =====： 这一部分紧张打印总的执行时间和网络流量，包罗了各个DN上初始化和竣事阶段的最大最小执行时间、CN上的初始化、执行、竣事阶段的时间，以及当前语句执行时体系可用内存、语句估算内存等信息。
  

NOTICE:

• A-rows和E-rows的差异表现了优化器估算和实际执行的偏差度。一般来说，偏差越大，优化器天生的计划越不可信，人工干预调优的必要性越大。
  
• A-time中的两个值偏差越大，表明此算子的盘算偏斜（在差别DN上执行时间差异）越大，人工干预调优的必要性越大。
  
• Max Query Peak Memory经常用来估算SQL语句耗费内存，也被用来作为SQL语句调优时运行态内存参数设置的紧张依据。一般会以EXPLAIN ANALYZE或EXPLAIN PERFORMANCE的输出作为进一步调优的输入。
  

更多详情请参考GaussDB 文档中心：https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。