> 作者：chirpyli
>
> 來源：[恆生LIGHT雲社群](https://developer.hs.net/thread/1886)

子查詢的邏輯優化內容很多，我們先以 `select * from t1 where id in (select a from t2);`這條SQL語句為例分析PostgreSQL對子連線的邏輯優化過程。

在往下講之前，先區別一下子連線與子查詢，因為程式碼中分別會對子連線和子查詢進行上拉操作。怎麼區別呢？通常，以範圍表的方式存在的稱為子查詢，以表示式的方式存在的稱為子連線。實際上也可以通過子句所處的位置來區分子連線和子查詢，出現在FROM關鍵字之後的子句時子查詢語句，出現在WHERE等約束條件中的是子連線。

我們先通過執行計劃進行觀察分析，資料庫通過 `Hash Semi Join`對子連線進行了邏輯優化。

```sql
--檢視執行計劃
postgres=# explain select * from t1 where id in (select a from t2);
                          QUERY PLAN               
---------------------------------------------------------------
 Hash Semi Join  (cost=1.09..2.21 rows=4 width=8)
   Hash Cond: (t1.id = t2.a)
   ->  Seq Scan on t1  (cost=0.00..1.06 rows=6 width=8)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)
```

#### 解析層分析

我們先分析子連線在解析層的表示（以這條語句為例，其他子連線形式類似）：

```c++
 /* A SubLink represents a subselect appearing in an expression, and in some
 * cases also the combining operator(s) just above it. */
typedef struct SubLink
{
	Expr		xpr;
	SubLinkType subLinkType;	/* see above */
	int			subLinkId;		/* ID (1..n); 0 if not MULTIEXPR */
	Node	   *testexpr;		/* outer-query test for ALL/ANY/ROWCOMPARE */
	List	   *operName;		/* originally specified operator name */
	Node	   *subselect;		/* subselect as Query* or raw parsetree */
	int			location;		/* token location, or -1 if unknown */
} SubLink;
```

語法定義如下：

```c++
// 匹配 where id in (select a from t2);
where_clause:
			WHERE a_expr							{ $$ = $2; }
			| /*EMPTY*/								{ $$ = NULL; }
		;
// 匹配 id in (select a from t2);
a_expr:		c_expr									{ $$ = $1; }
			| a_expr IN_P in_expr
				{
					/* in_expr returns a SubLink or a list of a_exprs */
					if (IsA($3, SubLink))
					{
						/* generate foo = ANY (subquery) */
						SubLink *n = (SubLink *) $3;
						n->subLinkType = ANY_SUBLINK;
						n->subLinkId = 0;
						n->testexpr = $1;   // 具體到上面的SQL語句是id column
						n->operName = NIL;		/* show it's IN not = ANY */
						n->location = @2;
						$$ = (Node *)n;
					}
					else
					{
						/* generate scalar IN expression */
						$$ = (Node *) makeSimpleA_Expr(AEXPR_IN, "=", $1, $3, @2);
					}
				}
// 匹配 (select a from t2)
in_expr:	select_with_parens
				{
					SubLink *n = makeNode(SubLink);
					n->subselect = $1;
					/* other fields will be filled later */
					$$ = (Node *)n;
				}
			| '(' expr_list ')'						{ $$ = (Node *)$2; }
		;
```

我們知道 `"x IN (select)", convert to "x = ANY (select)"`這個轉換是等價的。在解析階段，會進行 `IN`轉 `ANY`的處理，在呼叫過程如下：`transformSelectStmt`->`transformWhereClause`->`transformExpr`->`transformSubLink`。在 `transformSublink`中進行這個轉換處理：

```c++
static Node *transformSubLink(ParseState *pstate, SubLink *sublink)
{
	  Node	   *result = (Node *) sublink;
	  Query	   *qtree;

// 省略中間程式碼......
		/* If the source was "x IN (select)", convert to "x = ANY (select)".*/
		if (sublink->operName == NIL)
			sublink->operName = list_make1(makeString("="));

// 省略中間程式碼......

return result;
}  
```

理解了子連線在解析層的表示後，我們重點分析一下邏輯優化階段的處理。

#### 邏輯優化

邏輯優化的基礎是關係代數的等價變換，等價推理。有點類似於中學時數學中的簡便運算思想。最容易理解的優化思想是：先選擇後投影，比如選擇下推的目的是減少中間計算結果從而達到優化的效果。在PG中，查詢優化器的程式碼在 `src/backend/optimizer`目錄下：

```c++
optimizer
├── geqo  // 遺傳演算法，在連線較多的情況下會選擇該演算法，閾值可調整，預設geqo_threshold=12
├── path  // 物理優化
├── plan  // 優化器入口
├── prep  // 邏輯優化相關
│   ├── prepjointree.c  // 處理連線操作
│   ├── prepqual.c      // 處理選擇條件
│   ├── preptlist.c     // 處理投影相關
│   └── prepunion.c     // 處理集合操作  
└── util  // 公共函式
```

邏輯優化的內容有很多，這裡我們只分析子連線的邏輯優化（子連線上拉），將子連線轉為SEMI-JOIN，使得子連線中的子查詢有機會與父查詢語句進行合併優化。我們看一下具體的下面的執行計劃。可以看到將子查詢轉為了SEMI-JOIN的形式。

```sql
postgres=# explain select * from t1 where id in (select cid from school);
                            QUERY PLAN                    
------------------------------------------------------------------
 Hash Semi Join  (cost=1.09..2.21 rows=4 width=8)
   Hash Cond: (t1.id = school.cid)
   ->  Seq Scan on t1  (cost=0.00..1.06 rows=6 width=8)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on school  (cost=0.00..1.04 rows=4 width=4)
```

我們關閉 `enable_hashjoin`再看一下執行計劃：

```sql
postgres=# set enable_hashjoin=off;
SET
postgres=# explain select * from t1 where id in (select cid from school);
                             QUERY PLAN                     
--------------------------------------------------------------------
 Merge Semi Join  (cost=2.22..2.30 rows=4 width=8)
   Merge Cond: (t1.id = school.cid)
   ->  Sort  (cost=1.14..1.15 rows=6 width=8)
         Sort Key: t1.id
         ->  Seq Scan on t1  (cost=0.00..1.06 rows=6 width=8)
   ->  Sort  (cost=1.08..1.09 rows=4 width=4)
         Sort Key: school.cid
         ->  Seq Scan on school  (cost=0.00..1.04 rows=4 width=4)
```

關閉hash join後走的是merge join執行計劃，我們再把enable_mergejoin關閉：

```sql
postgres=# set enable_mergejoin =off;
SET
postgres=# explain select * from t1 where id in (select cid from school);
                            QUERY PLAN                    
------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..2.47 rows=4 width=8)
   Join Filter: (t1.id = school.cid)
   ->  Seq Scan on t1  (cost=0.00..1.06 rows=6 width=8)
   ->  Materialize  (cost=0.00..1.06 rows=4 width=4)
         ->  Seq Scan on school  (cost=0.00..1.04 rows=4 width=4)
```

我們看到了邏輯優化後的結果，這裡因為我自己構造的資料量很少，所以結果可能不是很明顯，當資料量很大時結果對比會非常明顯。我們再看一個例子：

```sql
postgres=# explain select * from t1 where id in (select cid from school where students > t1.id);
                          QUERY PLAN                  
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..4.23 rows=3 width=8)   /* 這裡掃描節點的下層是子執行計劃 */
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on school  (cost=0.00..1.05 rows=1 width=4)
           Filter: (students > t1.id)
```

沒有被優化為Semi Join。也就是說並不是所有的子連線都可以被上拉優化。是否被上拉取決於上拉後是否保持等價變換（正確性），有很多情況是不能被上拉的，另一個是上拉優化後是否執行效率更高，並不是所有的情況上拉後執行效率更高，具體的我們分析一下原始碼。

#### 原始碼分析

查詢樹生成執行計劃是在 `planner`呼叫完成，`planner`會呼叫 `standard_planner`，我們繼續往下看：

```c++
PlannedStmt *standard_planner(Query *parse, const char *query_string, int cursorOptions, ParamListInfo boundParams)
{
	  PlannedStmt *result;
    // 省略很多程式碼......
  
    /* primary planning entry point (may recurse for subqueries) */
    root = subquery_planner(glob, parse, NULL, false, tuple_fraction);  // 看這裡

final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);
    best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);

top_plan = create_plan(root, best_path);  //建立計劃
    // 省略很多程式碼......
  	return result; 
}

PlannerInfo *subquery_planner(PlannerGlobal *glob, Query *parse, PlannerInfo *parent_root, bool hasRecursion, double tuple_fraction)
{
    PlannerInfo *root;
    // 省略中間程式碼......
    /* Look for ANY and EXISTS SubLinks in WHERE and JOIN/ON clauses, and try
    * to transform them into joins.  Note that this step does not descend
    * into subqueries; if we pull up any subqueries below, their SubLinks are
    * processed just before pulling them up.*/
    if (parse->hasSubLinks)
      pull_up_sublinks(root);   // 子連線上拉優化，可以看到下面子查詢也有上拉優化，區別是：子連線可以認為是出現在表示式中的子查詢。
 
    pull_up_subqueries(root);   //  子查詢上拉

if (parse->setOperations)
      flatten_simple_union_all(root);

// 後面是其他優化，包括表示式優化等，這裡不再列出.......
}
```

我們知道了子連線上拉優化的呼叫關係以及位置後具體分析一下其處理過程：

```c++
/* Attempt to pull up ANY and EXISTS SubLinks to be treated as	semijoins or anti-semijoins. */
void pull_up_sublinks(PlannerInfo *root)
{
	elog_node_display(NOTICE, "optimizer-before", root->parse->jointree, true);		// 可通過增加日誌列印出優化前後的變化

Node	   *jtnode;
	Relids		relids;

jtnode = pull_up_sublinks_jointree_recurse(root,(Node *) root->parse->jointree,&relids);

if (IsA(jtnode, FromExpr))
		root->parse->jointree = (FromExpr *) jtnode;
	else
		root->parse->jointree = makeFromExpr(list_make1(jtnode), NULL);

elog_node_display(NOTICE, "optimizer-after", root->parse->jointree, true);
}
```

`pull_up_sublinks`核心實現是在 `pull_up_sublinks_jointree_recurse`中完成的，我們繼續往下看：

```c++
Node *pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode, Relids *relids)
{
    if (jtnode == NULL)
      *relids = NULL;
    else if (IsA(jtnode, RangeTblRef)) 
	{
      int	varno = ((RangeTblRef *) jtnode)->rtindex;
      *relids = bms_make_singleton(varno);        /* jtnode is returned unmodified */
    } 
    else if (IsA(jtnode, FromExpr)) 
    {       // 重點看這段程式碼，子連線上拉，需要重寫FromExpr
      FromExpr   *f = (FromExpr *) jtnode;
      List	   *newfromlist = NIL;
      Relids		frelids = NULL;
      FromExpr   *newf;
      Node	   *jtlink;
      ListCell   *l;

/* First, recurse to process children and collect their relids */
      foreach(l, f->fromlist)   // 對應處理SQL語句中的 from t1 
      {
        Node	   *newchild;
        Relids		childrelids;

newchild = pull_up_sublinks_jointree_recurse(root,lfirst(l),&childrelids);
        newfromlist = lappend(newfromlist, newchild);   
        frelids = bms_join(frelids, childrelids);
      }
      /* Build the replacement FromExpr; no quals yet */
      newf = makeFromExpr(newfromlist, NULL);     // 需要重寫where id in (select cid from school); 條件表示式，所以這裡先賦NULL
      /* Set up a link representing the rebuilt jointree */
      jtlink = (Node *) newf;
	 // 重寫where qual。需要重寫成類似select * from t1 semi-join (select cid from school) as any_subquery where t1.id=any_subquery.cid這樣的，注意沒有semi-join這個語法。
      newf->quals = pull_up_sublinks_qual_recurse(root, f->quals,&jtlink, frelids, NULL, NULL);

*relids = frelids;
      jtnode = jtlink;
    }
    else if (IsA(jtnode, JoinExpr))
    {
       // 省略部分程式碼......
    }
    // 省略部分程式碼......
    return jtnode;
}
```

難點在 `pull_up_sublinks_qual_recurse`的處理上，即怎麼重寫條件表示式。子連線有多種，我們只分析上面的語句，其他的程式碼相似，這裡不再列出。

```c++
Node *pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,Node **jtlink1, Relids available_rels1,Node **jtlink2, Relids available_rels2)
{
	if (node == NULL)
		return NULL;
	if (IsA(node, SubLink))
	{
		SubLink    *sublink = (SubLink *) node;
		JoinExpr   *j;
		Relids		child_rels;

/* Is it a convertible ANY or EXISTS clause? */
		if (sublink->subLinkType == ANY_SUBLINK)    // 重點分析這種情況，其他情況類似
		{
			if ((j = convert_ANY_sublink_to_join(root, sublink,available_rels1)) != NULL)
			{
				/* Yes; insert the new join node into the join tree */
				j->larg = *jtlink1;
				*jtlink1 = (Node *) j;
				/* Recursively process pulled-up jointree nodes */
				j->rarg = pull_up_sublinks_jointree_recurse(root,j->rarg,&child_rels);

/* Now recursively process the pulled-up quals.  Any inserted joins can get stacked onto either j->larg or j->rarg,depending on which rels they reference. */
				j->quals = pull_up_sublinks_qual_recurse(root,j->quals,&j->larg,available_rels1,&j->rarg,child_rels);
				/* Return NULL representing constant TRUE */
				return NULL;
			}
			if (available_rels2 != NULL &&(j = convert_ANY_sublink_to_join(root, sublink,available_rels2)) != NULL)
			{
				/* Yes; insert the new join node into the join tree */
				j->larg = *jtlink2;
				*jtlink2 = (Node *) j;
				/* Recursively process pulled-up jointree nodes */
				j->rarg = pull_up_sublinks_jointree_recurse(root,j->rarg,&child_rels);
				j->quals = pull_up_sublinks_qual_recurse(root,j->quals,&j->larg,available_rels2,&j->rarg,child_rels);
				/* Return NULL representing constant TRUE */
				return NULL;
			}
		}
		else if (sublink->subLinkType == EXISTS_SUBLINK)
		{
        // 省略程式碼.......
		}
		/* Else return it unmodified */
		return node;
	}
	if (is_notclause(node))
	{
		/* If the immediate argument of NOT is EXISTS, try to convert */
		SubLink    *sublink = (SubLink *) get_notclausearg((Expr *) node);
    // 省略程式碼......

/* Else return it unmodified */
		return node;
	}
	if (is_andclause(node))
	{
		/* Recurse into AND clause */
    // 省略程式碼......
	}
	/* Stop if not an AND */
	return node;
}
```

還需要再思考一個問題，是所有的情況都可以進行上拉優化嗎，顯然不是的，前面的例子中已經看到了，那進行上拉優化需要那些限制條件呢？有3條：

- 子連線中的子查詢不能使用其父查詢中的任何Var型別變數
- 比較表示式（testexpr）中必須包含父查詢中的任意一個Var型別變數
- 比較表示式中不能包含任何的易失函式

關於易失函式：PG中的函式有3種穩定性級別（VOLATILE、STABLE、IMMUTABLE），其中VOLATILE函式輸入同樣的引數會返回不同的結果，查詢優化器通常不對含有VOLATILE函式的表示式進行優化，子連線不能提升。

```c++
// convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join
JoinExpr *convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,Relids available_rels)
{
	JoinExpr   *result;
	Query	   *parse = root->parse;
	Query	   *subselect = (Query *) sublink->subselect;
	Relids		upper_varnos;
	int			rtindex;
	ParseNamespaceItem *nsitem;
	RangeTblEntry *rte;
	RangeTblRef *rtr;
	List	   *subquery_vars;
	Node	   *quals;
	ParseState *pstate;

//ANY型別的子連線,子查詢不能依賴父查詢的任何變數,否則不能上拉
	if (contain_vars_of_level((Node *) subselect, 1))
		return NULL;

//子查詢的testexpr變數中必須含有父查詢的某些Vars,否則不能上拉(join)
	upper_varnos = pull_varnos(root, sublink->testexpr);
	if (bms_is_empty(upper_varnos))
		return NULL;

/* However, it can't refer to anything outside available_rels.*/
	if (!bms_is_subset(upper_varnos, available_rels))
		return NULL;

// 組合操作符和左側的表示式不能是易變(volatile)的,比如隨機數等
	if (contain_volatile_functions(sublink->testexpr))
		return NULL;

/* Create a dummy ParseState for addRangeTableEntryForSubquery */
	pstate = make_parsestate(NULL);

// 子連線上拉後,子查詢變成了上層的RTE,在這裡構造,pull up the sub-select into upper range table.
	nsitem = addRangeTableEntryForSubquery(pstate,subselect, makeAlias("ANY_subquery", NIL),false,false);
	rte = nsitem->p_rte;
	parse->rtable = lappend(parse->rtable, rte);
	rtindex = list_length(parse->rtable);

/* Form a RangeTblRef for the pulled-up sub-select.*/
	rtr = makeNode(RangeTblRef);
	rtr->rtindex = rtindex;

/* Build a list of Vars representing the subselect outputs.*/
	subquery_vars = generate_subquery_vars(root,subselect->targetList,rtindex);

/* Build the new join's qual expression 構造新的join的條件表示式*/
	quals = convert_testexpr(root, sublink->testexpr, subquery_vars);

/* And finally, build the JoinExpr node.*/
	result = makeNode(JoinExpr);
	result->jointype = JOIN_SEMI;		// 變換為半連線
	result->isNatural = false;
	result->larg = NULL;		/* caller must fill this in */
	result->rarg = (Node *) rtr;
	result->usingClause = NIL;
	result->quals = quals;
	result->alias = NULL;
	result->rtindex = 0;		/* we don't need an RTE for it */

return result;
}
```

#### 後記

這裡只借助一條SQL語句分析了一下其邏輯優化的過程，其中還有很多內容並沒有講，比如EXIST型別子連線，相關子連線與非相關子連線的邏輯優化情況，這個可以結合執行計劃和原始碼自行去分析，後面還會補充這方面的內容，這裡不再展開。另外需要注意的是，有的子連線會提升為子查詢，有的不會，（比如，ANY型別子連線一般會提升為子查詢，EXIST型別子連線不會，直接提升為表與表的連線方式）。

針對上面的問題，舉個例子：`select * from ttt where exists (select id from teacher where ttt.id > id);`

```sql
postgres=# explain (costs false) select * from ttt where exists (select id from teacher where ttt.id > id);
              QUERY PLAN      
--------------------------------------
 Nested Loop Semi Join
   Join Filter: (ttt.id > teacher.id)
   ->  Seq Scan on ttt
   ->  Materialize
         ->  Seq Scan on teacher
```

會被優化成類似 `select * from ttt semi join teacher where ttt.id > id`的形式。

我們再舉個例子：`select * from ttt where id > any (select id from teacher);`ANY子連線優化結果：

```sql
postgres=# explain (costs false) select * from ttt where id > any (select id from teacher);
              QUERY PLAN      
--------------------------------------
 Nested Loop Semi Join
   Join Filter: (ttt.id > teacher.id)
   ->  Seq Scan on ttt
   ->  Materialize
         ->  Seq Scan on teacher
```

在 `pull_up_sublink`階段，會將其優化為類似 `select * from ttt semi join (select id from teacher) as ANY_subquery where ttt.id > ANY_subquery.id`的形式。也就是說將ANY型別的子連線轉換為了子查詢的形式，這裡只做了一半，後一半是子查詢優化，也就是會將子查詢進行上拉。這一優化過程後面文章再繼續分析子查詢優化 `pull_up_subquery`。所以說，子連線的優化學習還沒有結束，後面還要繼續分析學習子查詢的邏輯優化過程。

***

想向技術大佬們多多取經？開發中遇到的問題何處探討？如何獲取金融科技海量資源？

[恆生LIGHT雲社群](https://developer.hs.net/)，由恆生電子搭建的金融科技專業社群平臺，分享實用技術乾貨、資源資料、金融科技行業趨勢，擁抱所有金融開發者。

掃描下方小程式二維碼，加入我們！

![](https://developer.hs.net/storage/attachments/2021/12/20/vzjJlveZdvxcz6rUjIBaPMbRK36iL8jCjtMU5h97.png)

【資料庫】查詢優化之子連線優化

相關文章