#### 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,必要还会创建临时文件。 ```