哪里有做企业网站的/万网创始人

我们前面分析完视图后，这里再继续分析一下物化视图，其实现原理是不相同的，需要注意，物化视图等于是将返回的结果集缓存起来，而视图是查询重写，结果需要重新进行计算。

create materialized view matvt1 as select * from t1

语法解析部分

主流程如下：

exec_simple_query
--> pg_parse_query--> raw_parser--> base_yyparse
--> pg_analyze_and_rewrite--> parse_analyze--> transformStmt--> transformCreateTableAsStmt--> transformStmt   // 对查询语句进行语义分析,将其转换为查询树Query--> pg_rewrite_query
--> pg_plan_queries

定义物化视图的语法如下：

/*******************************************************************************		QUERY :*				CREATE MATERIALIZED VIEW relname AS SelectStmt******************************************************************************/
CreateMatViewStmt:CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data{CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);  ctas->query = $7;ctas->into = $5;ctas->relkind = OBJECT_MATVIEW;ctas->is_select_into = false;ctas->if_not_exists = false;/* cram additional flags into the IntoClause */$5->rel->relpersistence = $2;$5->skipData = !($8);$$ = (Node *) ctas;}

创建物化视图的语法，抽象语法树表示CreateTableAsStmt，创建物化视图的流程与CREATE TABLE AS 相同，等于新创建一个表（UNLOGGED TABLE），保存查询到的结果集。可以看到SELECT INTO，与CREATE TABLE AS也是用此进行表示。

/* ----------------------*		CREATE TABLE AS Statement (a/k/a SELECT INTO)** A query written as CREATE TABLE AS will produce this node type natively.* A query written as SELECT ... INTO will be transformed to this form during* parse analysis.* A query written as CREATE MATERIALIZED view will produce this node type,* during parse analysis, since it needs all the same data.** The "query" field is handled similarly to EXPLAIN, though note that it* can be a SELECT or an EXECUTE, but not other DML statements.* ----------------------*/
typedef struct CreateTableAsStmt
{NodeTag		type;Node	   *query;			/* the query (see comments above) */IntoClause *into;			/* destination table */ObjectType	relkind;		/* OBJECT_TABLE or OBJECT_MATVIEW */bool		is_select_into; /* it was written as SELECT INTO */bool		if_not_exists;	/* just do nothing if it already exists? */
} CreateTableAsStmt;/** IntoClause - target information for SELECT INTO, CREATE TABLE AS, and* CREATE MATERIALIZED VIEW** For CREATE MATERIALIZED VIEW, viewQuery is the parsed-but-not-rewritten* SELECT Query for the view; otherwise it's NULL.  (Although it's actually* Query*, we declare it as Node* to avoid a forward reference.)*/
typedef struct IntoClause
{NodeTag		type;RangeVar   *rel;			/* target relation name */List	   *colNames;		/* column names to assign, or NIL */char	   *accessMethod;	/* table access method */List	   *options;		/* options from WITH clause */OnCommitAction onCommit;	/* what do we do at COMMIT? */char	   *tableSpaceName; /* table space to use, or NULL */Node	   *viewQuery;		/* materialized view's SELECT query */bool		skipData;		/* true for WITH NO DATA */
} IntoClause;

执行部分——创建物化视图

主流程如下：

exec_simple_query
--> pg_parse_query
--> pg_analyze_and_rewrite
--> pg_plan_queries
--> PortalStart
--> PortalRun--> PortalRunUtility    // Execute a utility statement inside a portal.--> ProcessUtility--> standard_ProcessUtility--> ProcessUtilitySlow/** 执行步骤:*         1. 创建表，准备存储结果集*         2. 查询重写(物化视图中的查询语句)*         3. 生成查询的执行计划*         4. 执行获取查询语句的结果集*/--> ExecCreateTableAs  // Create the tuple receiver object and insert info it will need-->  CreateIntoRelDestReceiver  // 结果集输入到IntoRel中，新建的表中--> QueryRewrite--> pg_plan_query--> standard_planner--> subquery_planner--> grouping_planner--> query_planner--> make_one_rel--> create_plan--> create_scan_plan--> CreateQueryDesc  /* Create a QueryDesc, redirecting output to our tuple receiver */--> ExecutorStart--> ExecutorRun--> standard_ExecutorRun// 1. 建表--> intorel_startup--> create_ctas_internal    //Actually create the target table--> DefineRelation // 建表--> heap_create_with_catalog--> heap_create--> StoreViewQuery  // Use the rules system to store the query for the view.--> UpdateRangeTableOfViewParse --> DefineViewRules--> DefineQueryRewrite  // Set up the ON SELECT rule.--> InsertRule // 插入的规则，重写为新的物化表，并不是源表--> SetMatViewPopulatedState// 2. 执行查询语句，结果集存入物化的表中--> ExecutePlan--> ExecScan    // 扫描获取tuple--> ExecScanFetch--> SeqNext--> table_beginscan--> intorel_receive  // receive one tuple--> table_tuple_insert  // 将查询到的tuple slot插入到创建的表中--> heapam_tuple_insert--> ExecFetchSlotHeapTuple--> tts_buffer_heap_materialize--> heap_copytuple--> tts_buffer_heap_get_heap_tuple--> heap_insert // 插入到表中，找到指定的page，插入tuple。--> heap_prepare_insert--> RelationPutHeapTuple    --> ExecutorEnd--> PortalDrop

对于结果集中如何存入物化的新表中，可查看dest.c、createas.c等源码，查询到的结果可以按照需求发送到不同的地方，可查看下面的枚举，可以看到有个DestIntoRel的值，即使将结果send to relation。

/* ----------------*		CommandDest is a simplistic means of identifying the desired*		destination.  Someday this will probably need to be improved.** Note: only the values DestNone, DestDebug, DestRemote are legal for the* global variable whereToSendOutput.   The other values may be used* as the destination for individual commands.* ----------------*/
typedef enum
{DestNone,					/* results are discarded */DestDebug,					/* results go to debugging output */DestRemote,					/* results sent to frontend process */DestRemoteExecute,			/* sent to frontend, in Execute command */DestRemoteSimple,			/* sent to frontend, w/no catalog access */DestSPI,					/* results sent to SPI manager */DestTuplestore,				/* results sent to Tuplestore */DestIntoRel,				/* results sent to relation (SELECT INTO) */DestCopyOut,				/* results sent to COPY TO code */DestSQLFunction,			/* results sent to SQL-language func mgr */DestTransientRel,			/* results sent to transient relation */DestTupleQueue				/* results sent to tuple queue */
} CommandDest;

其中还有一个非常重要的函数需要列出来CreateIntoRelDestReceiver，查询返回的结果输入到IntoClause节点指定的表中。

/** CreateIntoRelDestReceiver -- create a suitable DestReceiver object** intoClause will be NULL if called from CreateDestReceiver(), in which* case it has to be provided later.  However, it is convenient to allow* self->into to be filled in immediately for other callers.*/
DestReceiver *
CreateIntoRelDestReceiver(IntoClause *intoClause)
{DR_intorel *self = (DR_intorel *) palloc0(sizeof(DR_intorel));self->pub.receiveSlot = intorel_receive;self->pub.rStartup = intorel_startup;self->pub.rShutdown = intorel_shutdown;self->pub.rDestroy = intorel_destroy;self->pub.mydest = DestIntoRel;self->into = intoClause;/* other private fields will be set during intorel_startup */return (DestReceiver *) self;
}typedef struct
{DestReceiver pub;			/* publicly-known function pointers */IntoClause *into;			/* target relation specification *//* These fields are filled by intorel_startup: */Relation	rel;			/* relation to write to */ObjectAddress reladdr;		/* address of rel, for ExecCreateTableAs */CommandId	output_cid;		/* cmin to insert in output tuples */int			ti_options;		/* table_tuple_insert performance options */BulkInsertState bistate;	/* bulk insert state */
} DR_intorel;

最后我们看一下系统表pg_class、pg_rewrite中的相关信息：

-- 物化视图matvt1
postgres@postgres=# select oid,relname,relkind,relhasrules from pg_class where relname='matvt1';
-[ RECORD 1 ]-------
oid         | 16391
relname     | matvt1
relkind     | m
relhasrules | t
-- 表t1
postgres@postgres=# select oid,relname,relkind,relhasrules,relrewrite from pg_class where relname='t1';
-[ RECORD 1 ]------
oid         | 16384     -- 表OID
relname     | t1        -- 表名
relkind     | r         -- 表示是普通表
relhasrules | f         -- 表是否定义了规则
relrewrite  | 0
-- 查看系统表pg_rewrite，查看插入的规则
postgres@postgres=# select * from pg_rewrite order by oid desc limit 1;
-[ RECORD 1 ]
oid        | 16394
rulename   | _RETURN
ev_class   | 16391
ev_type    | 1
ev_enabled | O
is_instead | t
ev_qual    | <>
ev_action  | ({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("a" "b")} :rtekind 0 :relid 16391 :relkind m :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("a" "b")} :rtekind 0 :relid 16391 :relkind m :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias <> :eref {ALIAS :aliasname t1 :colnames ("a" "b")} :rtekind 0 :relid 16384 :relkind r :rellockmode 1 :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 1 :location 42} :resno 1 :resname a :ressortgroupref 0 :resorigtbl 16384 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 2 :location 42} :resno 2 :resname b :ressortgroupref 0 :resorigtbl 16384 :resorigcol 2 :resjunk false}) :override 0 :onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <>})

物化视图与普通视图不同的地方在于，创建物化视图时，要建立一张物理表存储查询语句的结果集。