小编给大家分享一下PostgreSQL中执行sql的流程是什么，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

PG执行SQL的过程有以下几个步骤：
第一步，根据输入的SQL语句执行SQL Parse，进行词法和语法分析等，最终生成解析树；
第二步，根据解析树，执行查询逻辑/物理优化、查询重写，最终生成查询树；
第三步，根据查询树，生成执行计划；
第四步，执行器根据执行计划，执行SQL。

如前所述，PG的SQL Parse（解析）过程由函数pg_parse_query实现，在exec_simple_query函数中调用。
代码如下：

/**Dorawparsing(only).**Alistofparsetrees(RawStmtnodes)isreturned,sincetheremightbe*multiplecommandsinthegivenstring.**NOTE:forinteractivequeries,itisimportanttokeepthisroutine*separatefromtheanalysis&rewritestages.Analysisandrewriting*cannotbedoneinanabortedtransaction,sincetheyrequireaccessto*databasetables.So,werelyontherawparsertodeterminewhether*we'veseenaCOMMITorABORTcommand;whenweareinabortstate,other*commandsarenotprocessedanyfurtherthantherawparsestage.*/List*pg_parse_query(constchar*query_string){List*raw_parsetree_list;TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);if(log_parser_stats)ResetUsage();raw_parsetree_list=raw_parser(query_string);if(log_parser_stats)ShowUsage("PARSERSTATISTICS");#ifdefCOPY_PARSE_PLAN_TREES/*Optionaldebuggingcheck:passrawparsetreesthroughcopyObject()*/{List*new_list=copyObject(raw_parsetree_list);/*ThischecksbothcopyObject()andtheequal()routines...*/if(!equal(new_list,raw_parsetree_list))elog(WARNING,"copyObject()failedtoproduceanequalrawparsetree");elseraw_parsetree_list=new_list;}#endifTRACE_POSTGRESQL_QUERY_PARSE_DONE(query_string);returnraw_parsetree_list;}/**raw_parser*Givenaqueryinstringform,dolexicalandgrammaticalanalysis.**Returnsalistofraw(un-analyzed)parsetrees.Theimmediateelements*ofthelistarealwaysRawStmtnodes.*/List*raw_parser(constchar*str){core_yyscan_tyyscanner;base_yy_extra_typeyyextra;intyyresult;/*initializetheflexscanner*/yyscanner=scanner_init(str,&yyextra.core_yy_extra,ScanKeywords,NumScanKeywords);/*base_yylex()onlyneedsthismuchinitialization*/yyextra.have_lookahead=false;/*initializethebisonparser*/parser_init(&yyextra);/*Parse!*/yyresult=base_yyparse(yyscanner);/*Cleanup(releasememory)*/scanner_finish(yyscanner);if(yyresult)/*error*/returnNIL;returnyyextra.parsetree;}

重要的数据结构:SelectStmt结构体

/*----------------------*SelectStatement**A"simple"SELECTisrepresentedintheoutputofgram.ybyasingle*SelectStmtnode;soisaVALUESconstruct.Aquerycontainingset*operators(UNION,INTERSECT,EXCEPT)isrepresentedbyatreeofSelectStmt*nodes,inwhichtheleafnodesarecomponentSELECTsandtheinternalnodes*representUNION,INTERSECT,orEXCEPToperators.Usingthesamenode*typeforbothleafandinternalnodesallowsgram.ytostickORDERBY,*LIMIT,etc,clausevaluesintoaSELECTstatementwithoutworrying*whetheritisasimpleorcompoundSELECT.*----------------------*/typedefenumSetOperation{SETOP_NONE=0,SETOP_UNION,SETOP_INTERSECT,SETOP_EXCEPT}SetOperation;typedefstructSelectStmt{NodeTagtype;/**Thesefieldsareusedonlyin"leaf"SelectStmts.*/List*distinctClause;/*NULL,listofDISTINCTONexprs,or*lcons(NIL,NIL)forall(SELECTDISTINCT)*/IntoClause*intoClause;/*targetforSELECTINTO*/List*targetList;/*thetargetlist(ofResTarget)*/List*fromClause;/*theFROMclause*/Node*whereClause;/*WHEREqualification*/List*groupClause;/*GROUPBYclauses*/Node*havingClause;/*HAVINGconditional-expression*/List*windowClause;/*WINDOWwindow_nameAS(...),...*//**Ina"leaf"noderepresentingaVALUESlist,theabovefieldsareall*null,andinsteadthisfieldisset.Notethattheelementsofthe*sublistsarejustexpressions,withoutResTargetdecoration.Alsonote*thatalistelementcanbeDEFAULT(representedasaSetToDefault*node),regardlessofthecontextoftheVALUESlist.It'suptoparse*analysistorejectthatwherenotvalid.*/List*valuesLists;/*untransformedlistofexpressionlists*//**Thesefieldsareusedinboth"leaf"SelectStmtsandupper-level*SelectStmts.*/List*sortClause;/*sortclause(alistofSortBy's)*/Node*limitOffset;/*#ofresulttuplestoskip*/Node*limitCount;/*#ofresulttuplestoreturn*/List*lockingClause;/*FORUPDATE(listofLockingClause's)*/WithClause*withClause;/*WITHclause*//**Thesefieldsareusedonlyinupper-levelSelectStmts.*/SetOperationop;/*typeofsetop*/boolall;/*ALLspecified?*/structSelectStmt*larg;/*leftchild*/structSelectStmt*rarg;/*rightchild*//*EventuallyaddfieldsforCORRESPONDINGspechere*/}SelectStmt;

重要的结构体:Value

/*----------------------*Valuenode**ThesameValuestructisusedforfivenodetypes:T_Integer,*T_Float,T_String,T_BitString,T_Null.**Integralvaluesareactuallyrepresentedbyamachineinteger,*butbothfloatsandstringsarerepresentedasstrings.*UsingT_Floatasthenodetypesimplyindicatesthat*thecontentsofthestringlooklikeavalidnumericliteral.**(BeforePostgres7.0,weusedadoubletorepresentT_Float,*butthatcreatesloss-of-precisionproblemswhenthevalueis*ultimatelydestinedtobeconvertedtoNUMERIC.SinceValuenodes*areonlyusedintheparsingprocess,notforruntimedata,it's*bettertousethemoregeneralrepresentation.)**Notethataninteger-lookingstringwillgetlexedasT_Floatif*thevalueistoolargetofitinan'int'.**Nulls,ofcourse,don'tneedthevaluepartatall.*----------------------*/typedefstructValue{NodeTagtype;/*tagappropriately(eg.T_String)*/unionValUnion{intival;/*machineinteger*/char*str;/*string*/}val;}Value;#defineintVal(v)(((Value*)(v))->val.ival)#definefloatVal(v)atof(((Value*)(v))->val.str)#definestrVal(v)(((Value*)(v))->val.str)

实现过程本节暂时搁置，先看过程执行的结果，函数pg_parse_query返回的结果是链表List，其中的元素是RawStmt，具体的结构需根据NodeTag确定（这样的做法类似于Java/C++的多态）。
测试数据

testdb=#--单位信息testdb=#droptableifexistst_dwxx;ues('Y有限公司','1002','北京市海淀区');insertintot_dwxx(dwmc,dwbh,dwdz)values('Z有限公司','1003','广西南宁市五象区');NOTICE:table"t_dwxx"doesnotexist,skippingDROPTABLEtestdb=#createtablet_dwxx(dwmcvarchar(100),dwbhvarchar(10),dwdzvarchar(100));CREATETABLEtestdb=#testdb=#insertintot_dwxx(dwmc,dwbh,dwdz)values('X有限公司','1001','广东省广州市荔湾区');INSERT01testdb=#insertintot_dwxx(dwmc,dwbh,dwdz)values('Y有限公司','1002','北京市海淀区');INSERT01testdb=#insertintot_dwxx(dwmc,dwbh,dwdz)values('Z有限公司','1003','广西南宁市五象区');INSERT01testdb=#--个人信息testdb=#droptableifexistst_grxx;NOTICE:table"t_grxx"doesnotexist,skippingDROPTABLEtestdb=#createtablet_grxx(dwbhvarchar(10),grbhvarchar(10),xmvarchar(20),nlint);CREATETABLEinsertintot_grxx(dwbh,grbh,xm,nl)values('1002','903','王五',43);testdb=#testdb=#insertintot_grxx(dwbh,grbh,xm,nl)values('1001','901','张三',23);INSERT01testdb=#insertintot_grxx(dwbh,grbh,xm,nl)values('1002','902','李四',33);INSERT01testdb=#insertintot_grxx(dwbh,grbh,xm,nl)values('1002','903','王五',43);INSERT01testdb=#--个人缴费信息testdb=#droptableifexistst_jfxx;NOTICE:table"t_jfxx"doesnotexist,skippingDROPTABLEtestdb=#createtablet_jfxx(grbhvarchar(10),nyvarchar(10),jefloat);CREATETABLEtestdb=#testdb=#insertintot_jfxx(grbh,ny,je)values('901','201801',401.30);insertintot_jfxx(grbh,ny,je)values('901','201802',401.30);insertintot_jfxx(grbh,ny,je)values('901','201803',401.30);insertintot_jfxx(grbh,ny,je)values('902','201801',513.30);insertintot_jfxx(grbh,ny,je)values('902','201802',513.30);insertintot_jfxx(grbh,ny,je)values('902','201804',513.30);insertintot_jfxx(grbh,ny,je)values('903','201801',372.22);insertintot_jfxx(grbh,ny,je)values('903','201804',372.22);testdb=#insertintot_jfxx(grbh,ny,je)values('901','201801',401.30);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('901','201802',401.30);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('901','201803',401.30);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('902','201801',513.10);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('902','201802',513.30);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('902','201804',513.30);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('903','201801',372.22);INSERT01testdb=#insertintot_jfxx(grbh,ny,je)values('903','201804',372.22);INSERT01testdb=#--获取pidtestdb=#selectpg_backend_pid();pg_backend_pid----------------1560(1row)--用于测试的查询语句testdb=#selectt_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.jetestdb-#fromt_dwxx,t_grxx,t_jfxxtestdb-#wheret_dwxx.dwbh=t_grxx.dwbhtestdb-#andt_grxx.grbh=t_jfxx.grbhtestdb-#andt_dwxx.dwbhIN('1001','1002')testdb-#orderbyt_grxx.grbhtestdb-#limit8;dwmc|grbh|xm|ny|je-----------+------+------+--------+--------X有限公司|901|张三|201801|401.3X有限公司|901|张三|201802|401.3X有限公司|901|张三|201803|401.3Y有限公司|902|李四|201801|513.1Y有限公司|902|李四|201802|513.3Y有限公司|902|李四|201804|513.3Y有限公司|903|王五|201801|372.22Y有限公司|903|王五|201804|372.22(8rows)

结果分析

[xdb@localhost~]$gdb-p1560GNUgdb(GDB)RedHatEnterpriseLinux7.6.1-100.el7Copyright(C)2013FreeSoftwareFoundation,Inc....(gdb)bpg_parse_queryBreakpoint1at0x84c6c9:filepostgres.c,line615.(gdb)cContinuing.Breakpoint1,pg_parse_query(query_string=0x1a46ef0"selectt_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je\nfromt_dwxxinnerjoint_grxxont_dwxx.dwbh=t_grxx.dwbh\ninnerjoint_jfxxont_grxx.grbh=t_jfxx.grbh\nwheret_dwxx.dwbhIN('1001','100"...)atpostgres.c:615615if(log_parser_stats)(gdb)n618raw_parsetree_list=raw_parser(query_string);(gdb)620if(log_parser_stats)(gdb)638returnraw_parsetree_list;(gdb)p*(RawStmt*)(raw_parsetree_list->head.data->ptr_value)$7={type=T_RawStmt,stmt=0x1a48c00,stmt_location=0,stmt_len=232}(gdb)p*((RawStmt*)(raw_parsetree_list->head.data->ptr_value))->stmt$8={type=T_SelectStmt}#转换为实际类型SelectStmt(gdb)p*(SelectStmt*)((RawStmt*)(raw_parsetree_list->head.data->ptr_value))->stmt$16={type=T_SelectStmt,distinctClause=0x0,intoClause=0x0,targetList=0x1a47b18,fromClause=0x1a48900,whereClause=0x1a48b40,groupClause=0x0,havingClause=0x0,windowClause=0x0,valuesLists=0x0,sortClause=0x1afd858,limitOffset=0x0,limitCount=0x1afd888,lockingClause=0x0,withClause=0x0,op=SETOP_NONE,all=false,larg=0x0,rarg=0x0}#设置临时变量(gdb)set$stmt=(SelectStmt*)((RawStmt*)(raw_parsetree_list->head.data->ptr_value))->stmt#查看结构体中的各个变量#------------------->targetList(gdb)p*($stmt->targetList)$28={type=T_List,length=5,head=0x1a47af8,tail=0x1a48128}#targetList有5个元素,分别对应t_dwxx.dwmc,t_grxx.grbh,t_grxx.xm,t_jfxx.ny,t_jfxx.je#先看第1个元素(gdb)set$restarget=(ResTarget*)($stmt->targetList->head.data->ptr_value)(gdb)p*$restarget->val$25={type=T_ColumnRef}(gdb)p*(ColumnRef*)$restarget->val$26={type=T_ColumnRef,fields=0x1a47a08,location=7}(gdb)p*((ColumnRef*)$restarget->val)->fields$27={type=T_List,length=2,head=0x1a47a88,tail=0x1a479e8}(gdb)p*(Node*)(((ColumnRef*)$restarget->val)->fields)->head.data->ptr_value$32={type=T_String}#fields链表的第1个元素是数据表,第2个元素是数据列(gdb)p*(Value*)(((ColumnRef*)$restarget->val)->fields)->head.data->ptr_value$37={type=T_String,val={ival=27556248,str=0x1a47998"t_dwxx"}}(gdb)p*(Value*)(((ColumnRef*)$restarget->val)->fields)->tail.data->ptr_value$38={type=T_String,val={ival=27556272,str=0x1a479b0"dwmc"}}#其他类似#------------------->fromClause(gdb)p*(Node*)($stmt->fromClause->head.data->ptr_value)$41={type=T_JoinExpr}(gdb)set$fromclause=(JoinExpr*)($stmt->fromClause->head.data->ptr_value)(gdb)p*$fromclause$42={type=T_JoinExpr,jointype=JOIN_INNER,isNatural=false,larg=0x1a484f8,rarg=0x1a48560,usingClause=0x0,quals=0x1a487d0,alias=0x0,rtindex=0}#------------------->whereClause(gdb)p*(Node*)($stmt->whereClause)$44={type=T_A_Expr}(gdb)p*(FromExpr*)($stmt->whereClause)$46={type=T_A_Expr,fromlist=0x1a48bd0,quals=0x1a489d0}#------------------->sortClause(gdb)p*(Node*)($stmt->sortClause->head.data->ptr_value)$48={type=T_SortBy}(gdb)p*(SortBy*)($stmt->sortClause->head.data->ptr_value)$49={type=T_SortBy,node=0x1a48db0,sortby_dir=SORTBY_DEFAULT,sortby_nulls=SORTBY_NULLS_DEFAULT,useOp=0x0,location=-1}#------------------->limitCount(gdb)p*(Node*)($stmt->limitCount)$50={type=T_A_Const}(gdb)p*(Const*)($stmt->limitCount)$51={xpr={type=T_A_Const},consttype=0,consttypmod=216,constcollid=0,constlen=8,constvalue=231,constisnull=16,constbyval=false,location=0}

看完了这篇文章，相信你对“PostgreSQL中执行sql的流程是什么”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！