【数据库测试】【SQL查询处理】查询优化-计划树节点

最新推荐文章于 2025-12-04 15:25:15 发布

原创最新推荐文章于 2025-12-04 15:25:15 发布 · 948 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

文章目录

1 控制节点
2 物化节点
- 2.1 物化节点 - Material
3 扫描节点
4 连接节点

SQL语句执行流程中分为多个节点，计划树相关节点如下

控制节点	物化节点	扫描节点	连接节点
用于处理特殊情况的节点，用于实现特殊的执行流程	特点：能够缓存执行结果到辅组存储中，一次性生成所有结果元组	用于扫描表登对象以从中获取元组	对应关系代数中的连接操作，可以实现多种连接方式
Result节点 Append节点 RecursiveUnion节点 …	Material节点 Sort节点 Group节点 Aggregate节点 Unique节点 …	顺序扫描节点（Seq Scan）索引扫描节点（Index Scan）位图扫描节点（BitMap Scan） TID扫描节点（TID Scan）覆盖索引扫描节点（Index Only Scan） BitmapAnd/BitMapOr节点 …	嵌套循环节点（NestLoop Join）归并连接节点（Merge Join） Hash连接节点（Hash Join） …

1 控制节点

1.1 分组节点

1.1.1 分组节点 - Group

$\qquad$ group节点通常group by的实现方式，它需要输入数据按照__分组__列进行排序。
$\qquad$ 无需再磁盘上执行外部合并排序是hash计划作为首选技术的主要原因，它能够改善查询的运行时间。在输入已经排序操作的时候仍然可以看到旧的GroupAggregate执行
原理：通过排序的方式进行分组，然后执行聚集
适用情况：适合数据基本有序的情况

set max_parallel_workers_per_gather=0;
explain analyze select L_ORDERKEY,count(L_PARTKEY) from LINEITEM group by l_orderkey ;

在这里插入图片描述

1.1.2 分组节点 - Aggregate

$\qquad$ 聚合函数（aggregate function）接收一系列值并产生一个单独的输出。常见的聚合函数有：AVG、COUNT、EVERY、MIN、MAX、SUM、VARIANCE，为了计算某个聚集，通常需要读取所有行，然后通过聚集节点计算出结果。执行含有聚集函数的group by操作，该节点能够实现三种执行策略：

Plain：不分组的聚集计算
Sorted：下层节点提供好排序的元组，类似Group的分组方法，然后进行聚集计算
Hash：首先对下层节点提供的未排序元组进行分组，然后进行计算

explain analyze select max(L_PARTKEY) from LINEITEM;
explain analyze select avg(L_PARTKEY) from LINEITEM;
explain analyze select count(L_PARTKEY) from LINEITEM;

在这里插入图片描述

#以下数据为TPCC测试表数据
\d bmsql_customer
selct count(c_w_id) from bmsql_customer;
set max_parallel_workers_per_gather=0;
--对主键使用聚集函数
explain analyze selct count(c_w_id) from bmsql_customer;
--对btree索引列使用聚集函数
explain analyze selct count(c_last) from bmsql_customer;

在这里插入图片描述

1.1.3 分组节点 - Group Aggregate

原理：通过排序的方式进行分组，然后执行聚集
适用情况：适合数据基本有序的情况

1.1.4 分组节点 - Hash Aggregate

$\qquad$ HashAggregate节点接收一组节点同时输出一系列派生数据在存储桶（buckets）中
，这些类型操作转换通常用于计算唯一值（Unique）、结果分组（Group）以及联合（Union），这种方式进行散列操作的主要值能够避免对这些值进行排序，排序在执行阶段中是昂贵的代价。有时候使用group by或者distinct操作计算聚合，也会使用一个HashAggregate计算输出。

原理：通过hash算法，把相同值hash到同一桶里面，然后求聚集
适用情况：适合数据无序的情况
说明：可以通过enable_sort = off 间接指示优化器适用hashagg，但并非100%有效，也可以使用hashagg相关hint

set enable_hashagg=on;
set max_parallel_workers_per_gather=0;
set work_mem to '64MB';
explain analyze select L_ORDERKEY from LINEITEM group by l_orderkey order by l_orderkey;

在这里插入图片描述

1.2 排序节点

1.2.1 排序节点 - Sort

$\qquad$ sort节点会出现在向查询中插入order by语句的时候，预期能够容纳于内存中，排序操作既可以使用快速排序算法（quicksort Memory） 在内存中运行。还可以交换至磁盘上以便使用，这种情况称为外部合并排序算法（external sort disk）。
原理：明确对数据进行排序。不可能完全消除明确的排序，但是关闭排序选项可以让优化器在存在其它方法的时候优先选择其它方法。比如关闭sort，就可以禁止mergejoin。
适用情况：输出结果是有序的
相关参数：enable_sort=on/off

外部合并排序
排序使用快速排序还是外部合并排序取决于work_mem大小，以下示例以TPCH 100MB测试数据lineitem表进行说明

set log_temp_files=0;  #开启临时文件统计
explain analyze select L_ORDERKEY from LINEITEM order by L_PARTKEY;

在这里插入图片描述

快速排序

set work_mem='64MB';
set log_temp_files=0;
explain analyze select L_ORDERKEY from LINEITEM order by L_PARTKEY;

在这里插入图片描述

1.3 去重节点

1.3.1 去重节点 - Unique

$\qquad$ unique节点需要将一组经过排序的行作为输入，并输出中排除所有重复行。它可以出现在使用distinct以及当union被用于在其输出中消除重复行的时候。其输出将会拥有与输入同样的排序顺序。

select distinct(c_d_id) from bmsql_customer limit 5;
explain analyze select distinct(c_d_id) from bmsql_customer limit 5;
set enable_hashagg=off;
select distinct(c_d_id) from bmsql_customer limit 5;
explain analyze select distinct(c_d_id) from bmsql_customer limit 5;

在这里插入图片描述

1.4 集合操作节点

1.4.1 集合操作节点 - Append

$\qquad$ Append节点被用于产生某些类型的union合并
原理：Append处理过程会逐个处理子计划（forwards or backwards），当一个子计划返回了所有的结果后，会接着执行链表中的下一个子计划，直到链表中的所有子计划都执行完

explain analyze select * from customer where c_mktsegment='BUILDING' union select * from customer where c_mktsegment='AUTOMOBILE';

在这里插入图片描述

1.5 其它节点

1.5.1 其它节点 - Limit

$\qquad$ 对于查询的限定是建立在返回一组数据行的现有扫描之上的，查询执行的方式。查询计划的起点是顶部的节点，并向其子节点要求提供所需要的信息，它是一个自顶向下的执行模型，节点只会按照需求生成输出。

#此项中对lineitem表顺序扫描600572行，但是优化达到10行限制后，那就是被要求生成的所有顺序扫描节点
set max_parallel_workers_per_gather=0;
explain analyze select L_ORDREKEY from LINEITEM limit 10;

在这里插入图片描述

1.5.2 其它节点 - offset

$\qquad$ 当查询中被添加offset时，基础扫描所生成的前几行数据将会被丢弃

select L_ORDERKEY from LINEITEM limit 5 offset 2;
select L_ORDERKEY from LINEITEM limit 5;
select L_ORDERKEY from LINEITEM limit 7;
explain analyze select L_ORDERKEY from LINEITEM limit 5 offset 2;

在这里插入图片描述

1.5.3 其它节点 - Result

$\qquad$ 有时候一个节点仅需要返回一个通过声明所计算的结果，结果节点基本上都是处理单个值而不是行集时快速通过节点，它们可以被用于折叠，由此去优化可被计算一次的where子句部分。

explain analyze select 1;

在这里插入图片描述

2 物化节点

2.1 物化节点 - Material

$\qquad$ Material节点用于缓存子节点结果，对于需要重复多次扫描的子节点（特别是扫描结果每次都相同时）可以减少执行的代价
适用情况：结果在子查询中会被多次使用
GUC参数:enable_material=on/off

explain select * from NATION,CUSTOMER;

在这里插入图片描述

3 扫描节点

3.1 扫描节点 - 顺序扫描（Seq Scan）

全表扫描也称为顺序扫描（sequence scan），全表扫描就是把表的所有数据块从头到尾读一遍，然后筛选出符合条件的数据库。
可以通过 "set enable_seqscan=on/off"语句来临时启用或禁止全表扫描。

3.1 扫描节点 - 索引扫描（Index Scan）

索引扫描适用于：数据量比较大而且选中结果比较少的情况，比如10000条以上记录，选择率在1-20%的场景
开启/关闭参数：enable_indexscan=on/off

create table t2(no int,price numeric,name text);
insert into t2 values (generate_series(1,10000),random()*5,md5(random()::text));
create index idx_no1 on t2(no);
explain analyze select * from t2 where no='12345';

在这里插入图片描述

3.1 扫描节点 - 位图扫描（Bitmap Scan）

Bitmap Scan是Bitmap Index Scan和Bitmap Heap Scan的组合。先通过Bitmap Index Scan索引扫描，在内存中创建一个位图表，每个BIT表示一个与过滤条件有关的页面。此页面有可能有数据未1，不可能有数据为0。然后再通过Bitmap Heap Scan表扫描，对创建好的位图表指针对应的页面进行顺序扫描，排除不符合的记录（Recheck Cond），返回需要的结果。
开启/关闭参数：enable_bitmapscan=on/off

3.1 扫描节点 - 覆盖索引扫描（Index Only Scan）

允许直接从索引得到元组，覆盖索引扫描要求查询中的某个表所需要数据均可以从这个表上的同一个索引的页面获得
开启/关闭参数：enable_indexonlyscan=on/off

create table t1(id int);
insert into t1 select generate_series(1,10000);
create index idx_t1 on t1(id);
explain analyze select * from t1 where id='12345';

在这里插入图片描述

4 连接节点

4.1 连接节点 - 嵌套循环连接（Nested Loop Join）

在这里插入图片描述

原理：扫描每一条外表的数据（m条数据），然后和内表所有的记录（n条数据）进行连接，时间复杂度是o（mn）
适用情况：数据量不大的情况，外表较小、内表有索引
开启/关闭参数：enable_nestloop=on/off

4.2 连接节点 - 哈希连接（Hash Join）

在这里插入图片描述

原理：对内表建立hash表，扫描所有内表数据到各个hash桶，然后一行行扫描外表数据，对外表数据进行hash，hash到某个桶里，然后跟这个桶里的数据进行连接。
适用情况：数据分布比较随机无序，重复值不是特别多的情况。内表数据较少，能够完全存放于work_mem性能最佳
开启/关闭参数：enable_hashjoin=on/off

create table t1(id int,score int);
insert into t1 select id,(random()*100)::INTEGER as score from generate_series(1,10000) as id;
create table t2(no int,name text);
insert into t2 values (generate_series(1,10000),md5(random()::text));
explain select * from t1 join t2 on t1.id=t2.no;

在这里插入图片描述

4.3 连接节点 - 归并连接（Merge Join）

在这里插入图片描述

原理：先对两个表的数据进行排序，然后再做连接
适用情况：两个表的数据都是基本有序，返回的结果是排序的
开启/关闭参数：enable_mergejoin=on/off

create table t1(id int);
create table t2(no int,price numeric,name text);
insert into t1 select generate_series(1,10000);
insert into t2 values (generate_series(1,10000),random()*5,md5(random()::text));
explain select * from t1 join t2 on t1.id=t2.no where t1.id < 100 and t2.no < 10 order by t1.id;

在这里插入图片描述