#### 3.1概覽
1. 解析器，根據SQL語句生成一顆語法解析數(parse tree)
2. 分析器，對語法解析樹進行語義分析，生成一顆查詢樹(query tree)
3. 重寫器，按照規則系統中存在的規則對查詢樹進行改寫
4. 計劃器，基於查詢樹生成一顆執行效率最高的計劃樹(plan tree)
5. 執行器，按照計劃樹中的順序訪問表和索引，執行相應查詢

##### 解析器

```
#/pgdata/pgsql/include/postgresql/server/nodes/parsenodes.h
typedef struct SelectStmt
{
        NodeTag         type;

/*
         * 這些欄位只會在SelectStmts “葉節點”中使用.
         */
        List       *distinctClause; /* NULL, list of DISTINCT ON exprs, or
                                                                 * lcons(NIL,NIL) for all (SELECT DISTINCT) */
        IntoClause *intoClause;         /* target for SELECT INTO */
        List       *targetList;         /* the target list (of ResTarget) */
        List       *fromClause;         /* the FROM clause */
        Node       *whereClause;        /* WHERE qualification */
        List       *groupClause;        /* GROUP BY clauses */
        Node       *havingClause;       /* HAVING conditional-expression */
        List       *windowClause;       /* WINDOW window_name AS (...), ... */

/*
         * 在一個表示值列表的葉節點中，上面的欄位全都為空，而這個欄位會被設定
         * 注意，這個子列表中的元素都沒有ResTarget的修飾的表示式型別
         * 還需注意，無論值列表的上下文是什麼，列表元素都可能為DEFAULT（表示一個SetToDefault節點）
         * 由分析階段覺得是否合法並拒絕
         */
        List       *valuesLists;        /* 未轉換的表示式列表 */

/*
         * 這些欄位會同時在SelectStmts葉節點與SelectStmts上層節點中使用
         */
        List       *sortClause;         /* sort clause (a list of SortBy's) */
        Node       *limitOffset;        /* # of result tuples to skip */
        Node       *limitCount;         /* # of result tuples to return */
        List       *lockingClause;      /* FOR UPDATE (list of LockingClause's) */
        WithClause *withClause;         /* WITH clause */

/*
         * 這些欄位只會在上層的SelectStmts中出現
         */
        SetOperation op;                        /* type of set op */
        bool            all;                    /* ALL specified? */
        struct SelectStmt *larg;        /* left child */
        struct SelectStmt *rarg;        /* right child */
        /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
```

##### 分析器
分析器對解析器產出的語法解析樹進行語義分析，併產出一顆查詢樹

```
#/pgdata/pgsql/include/postgresql/server/nodes/parsenodes.h
/*
*解析與分析過程會將所有的語法轉換為一顆查詢樹，供重寫器與計劃器用於進一步的處理，功能語句（即不可最佳化的語句）會設定utilityStmt欄位，而Query的結構本身基本上是空的。
* DECLARE CURSOR 是一個特例，它的形式與Select類似，但原始的DeclareCursorStmt會被放在utilityStmt欄位中
*計劃過程會將查詢樹轉換為一顆計劃樹，計劃樹的根節點是一個PlannedStmt結構
*執行樹不會用到查詢樹結構
*/
typedef struct Query
{
        NodeTag         type;

CmdType         commandType;    /* select|insert|update|delete|utility */

QuerySource querySource;        /* where did I come from? */

uint32          queryId;                /* query identifier (can be set by plugins) */

bool            canSetTag;              /* do I set the command result tag? */

Node       *utilityStmt;        /* non-null if commandType == CMD_UTILITY */

int                     resultRelation; /* rtable index of target relation for
                                                                 * INSERT/UPDATE/DELETE; 0 for SELECT */

bool            hasAggs;                /* has aggregates in tlist or havingQual */
        bool            hasWindowFuncs; /* has window functions in tlist */
        bool            hasTargetSRFs;  /* has set-returning functions in tlist */
        bool            hasSubLinks;    /* has subquery SubLink */
        bool            hasDistinctOn;  /* distinctClause is from DISTINCT ON */
        bool            hasRecursive;   /* WITH RECURSIVE was specified */
        bool            hasModifyingCTE;        /* has INSERT/UPDATE/DELETE in WITH */
        bool            hasForUpdate;   /* FOR [KEY] UPDATE/SHARE was specified */
        bool            hasRowSecurity; /* rewriter has applied some RLS policy */

List       *cteList;            /* WITH list (of CommonTableExpr's) */

List       *rtable;                     /* list of range table entries */
        FromExpr   *jointree;           /* table join tree (FROM and WHERE clauses) */

List       *targetList;         /* target list (of TargetEntry) */

OverridingKind override;        /* OVERRIDING clause */

OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */

List       *returningList;      /* return-values list (of TargetEntry) */

List       *groupClause;        /* a list of SortGroupClause's */

List       *groupingSets;       /* a list of GroupingSet's if present */

Node       *havingQual;         /* qualifications applied to groups */

List       *windowClause;       /* a list of WindowClause's */

List       *distinctClause; /* a list of SortGroupClause's */

List       *sortClause;         /* a list of SortGroupClause's */

Node       *limitOffset;        /* # of result tuples to skip (int8 expr) */
        Node       *limitCount;         /* # of result tuples to return (int8 expr) */

List       *rowMarks;           /* a list of RowMarkClause's */

Node       *setOperations;      /* set-operation tree if this is top level of
                                                                 * a UNION/INTERSECT/EXCEPT query */

List       *constraintDeps; /* a list of pg_constraint OIDs that the query
                                                                 * depends on to be semantically valid */

List       *withCheckOptions;   /* a list of WithCheckOption's, which are
                                                                         * only added during rewrite and therefore
                                                                         * are not written out as part of Query. */

/*
         * The following two fields identify the portion of the source text string
         * containing this query.  They are typically only populated in top-level
         * Queries, not in sub-queries.  When not set, they might both be zero, or
         * both be -1 meaning "unknown".
         */
        int                     stmt_location;  /* start location, or -1 if unknown */
        int                     stmt_len;               /* length in bytes; 0 means "rest of string" */
} Query;
```

#####  重寫器

在PostgreSQL中，檢視是基於規則系統實現的，當使用CREATE VIEW命令定義一個檢視時，PG就會建立相應的規則，並儲存到系統目錄中。

``` sql
--檢視是基於規則系統實現的。
create view employess_list
  as select e.id,e.name,d.name as department
    from employess as 3,departments as d where e.department_id=d.id;
select * from employees_list;
--當執行一個包含改檢視的查詢時，解析器會建立一顆語法樹
select * from employees_list;
--在該階段，重寫器會基於pg_rule中儲存的檢視規則將rangetable節點重寫為一顆查詢子樹，與子查詢相對應。
```

#####  計劃器與執行器

在PostgreSQL中，計劃器是完全基於代價估計的，它不支援基於規則的最佳化與提示。
>  Postgresql不支援sql中的提示(hint),想在查詢使用提示，可以考慮pg_hint_plan擴充套件。

``` sql
--eg
explan select * from tbl_a where id<300 order by data;
--當處理一個查詢時，執行器會使用預先分配的記憶體空間，比如temp_buffers和work_mem,必要還會建立臨時檔案。
```

【讀書筆記】《PostgreSQL指南-內幕探索》-3.1-解析器分析器等介紹

相關文章