查询优化

1 整体流程 1.1 执行流程 应用->驱动：通过驱动的api建立连接，并执行SQL语句 驱动->服务端：通过libpq协议封装SQL语句，发送至服务端 服务端 postgres线程：接收SQL语句，调用exec_siple_query()开始处理SQL语句 SQL引擎 解析器：通过flex进行词法解析，通过yacc进行语法分析，生成解析树 分析器：对解析树进行语义分析：包括检查表是否存在等，生成查询树 重写器： 计划期：根据查询树生成执行计划 执行器：自顶向下执行各个算子，执行过程中会调用存储引擎统一接口 INSERT / UPDATE / DELETE ExecModifyTable 算子：EexecInsert / ExecUpdate / ExecDelete EexecInsert ExecMaterializeSlot ExecBRInsertTriggers /* ROW INSERT 触发器 */ ExecWithCheckOptions /* 检查约束 */ heap_insert /* 存储引擎统一接口 */ ExecInsertIndexTuples ExecARInsertTriggers /* AFTER ROW 触发器 */ ExecProcessReturning SELECT ExecIndexScan IndexNext() index_getnext() tid = index_getnext_tid(scan) index_fetch_heap(scan) heap_hot_search_buffer() PageGetItem()

1 语法解析概述 1.1 语法解析的目的 语法解析的目的 用户向数据发送以下SQL语句时： CREATE TABLE t1 (c1 INT, c2 TEXT); INSERT INTO t1 VALUES (1, 'data1'); DELETE FROM t1 WHERE c1 = 1; 从用户的角度，掌握SQL语法后，我们很容易理解上述SQL的含义。 从PostgreSQL的角度，上述语句知识一串字符串，需要根据SQL语法的规则，从字符串中解析表名、列名、数据等关键信息。 语法解析的流程 此处将语法解析的步骤简要分为以下4个阶段： 定义语法：PostgreSQL开发人员需要根据SQL标准，制定一些类语法规则。比如： 当用户执行CREATE TABLE ..时，表示用户需要创建表 当用户执行CREATE TABLE tablename ..时，表示用户要创建的表名叫tablename 发布语法：PostgreSQL会在官网上发布其定义的各种语法 构造语句：当开发人员想让数据执行某些操作时，需根据数据库定义的语法，构造SQL语句，再让数据库执行。比如： 当用户想创建一个名为t1的表，需要构造SQL语句CREATE TABLE t1 .. 解析语法：PostgreSQL接收SQL语句后，根据语法规则，可解析SQL想要执行什么操作，生成1个结构体，结构中存储了SQL语句的关键信息，比如： 处理CREATE TABLE t1 ..，结构体中，将有1个变量表示表名，该变量的值将被复制为t1 执行语法：在解析用户操作后，生成一个结构体，即根据结构体，开始执行各种操作 1.2 语法解析的类型 PostgreSQL支持上百种语法，本文主要介绍以下4个常用的语法： CREATE TABLE .. SELECT .. FROM .. INSERT INTO .. DELETE FROM .. 2 解析语法 [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ [ AS ] output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] -- from_item [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] -- with_query with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete ) TABLE [ ONLY ] table_name [ * ] 参考 http://www.postgres.cn/docs/9.3/sql-select.html ...

1 概述 1.1 语义分析的目的 语义分析的目的 上文提到，PostgreSQL接收SQL语句时，会生成1个语法解析树，语法解析树存储了SQL语法中的关键信息。比如，在语法INSERT INTO t1 VALUES(1, 'data1')中，解析结果可说明： 这是一条用于插入数据的INSERT语法 数据将插入t1表中 要插入的数据是(1, 'data1') … 但是，数据库执行时，还需要更多的关键信息才行，比如，在INSERT INTO t1 VALUES(1, 'data1')的语法树中，PostgreSQL需要获取一些关键信息： 数据库中是否有表叫t1 t1表有几列，每列的的数据类型是什么 语法中的值(1, 'data1')是否符合t1表的定义 … 1.2 本文的目标 本文以几个常见且简单的语法为例，介绍语义分析的详细功能 CREATE TABLE .. SELECT .. FROM .. INSERT INTO .. DELETE FROM .. 1.3 语义分析的调用关系 exec_simple_query pg_parse_query pg_analyze_and_rewrite parse_analyze 1.1 整体流程 // 语义分析 exec_simple_query pg_parse_query pg_analyze_and_rewrite parse_analyze transformTopLevelStmt transformOptionalSelectInto transformStmt transformInsertStmt exec_simple_query() pg_parse_query() for (;;): pg_analyze_and_rewrite() Query *query = parse_analyze(Node *parsetree) List *querytree_list = pg_rewrite_query(query) pg_plan_queries() PortalRun() 1.2 分析器 Query *parse_analyze(Node *parseTree) ParseState *pstate = make_parsestate() Query *query = query = transformTopLevelStmt(pstate, parseTree) query = transformStmt(pstate, parseTree) /* 对各种类型语法进行语义分析 */ Query *result switch nodeTag(parseTree) { case T_InsertStmt: result = transformInsertStmt(pstate, (InsertStmt *) parseTree) case T_DeleteStmt: result = transformDeleteStmt(pstate, (DeleteStmt *) parseTree) case T_UpdateStmt: result = transformUpdateStmt(pstate, (UpdateStmt *) parseTree) case T_SelectStmt: SelectStmt *n = (SelectStmt *) parseTree; if n->valuesLists: transformValuesClause(pstate, n) else if n->op == SETOP_NONE: result = transformSelectStmt(pstate, n) case T_DeclareCursorStmt: ... case T_ExplainStmt: ... case T_CreateTableAsStmt: ... default: result = makeNode(Query) result->commandType = CMD_UTILITY result->utilityStmt = (Node *) parseTree } return result; 1.3 SELECT 分析 Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt) Query *qry = makeNode(Query) if stmt->withClause: qry->cteList = transformWithClause(pstate, stmt->withClause) transformFromClause(pstate, stmt->fromClause) qry->targetList = transformTargetList(pstate, stmt->targetList) markTargetListOrigins(pstate, qry->targetList) Node *qual = transformWhereClause(pstate, stmt->whereClause) qry->havingQual = transformWhereClause(pstate, stmt->havingClause) qry->sortClause = transformSortClause(pstate, stmt->sortClause, &qry->targetList) qry->groupClause = transformGroupClause(pstate, stmt->groupClause, &qry->groupingSets, &qry->targetList, qry->sortClause) qry->distinctClause = transformDistinctOnClause(pstate, stmt->distinctClause, &qry->targetList, qry->sortClause) qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset) qry->limitCount = transformLimitClause(pstate, stmt->limitCount) qry->windowClause = transformWindowDefinitions(pstate, pstate->p_windowdefs, &qry->targetList) qry->jointree = makeFromExpr(pstate->p_joinlist, qual) if pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual: parseCheckAggregates(pstate, qry) assign_query_collations(pstate, qry) return qry

重写功能 -- 创建2个表 CREATE TABLE t1 (c1 INT, c2 TEXT); CREATE TABLE t2 (c1 INT, c2 TEXT); -- 创建1条重写规则 -- 触发条件：向t1表写入数据时 -- 执行操作：将SQL语句重写为：同时向t2写入相同数据 CREATE OR REPLACE RULE r1 AS ON INSERT TO t1 DO ALSO INSERT INTO t2 VALUES (new.c1, new.c2); -- 向t1写入一条数据 INSERT INTO t1 VALUES (1, 'abc'); -- 验证t2也写入了一条数据 SELECT * FROM t2; DROP TABLE t1,t2; 重写规则 语法 定义重写规则的语法如下： CREATE [ OR REPLACE ] RULE name AS ON { SELECT | INSERT | UPDATE | DELETE } TO table_name [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } 约束 ...

SQL 引擎 1 概述 1.1 生成计划的目的 上文提到，在语法分析、语义分析之后，PostgreSQL已获取执行所需的详细信息。计划生成主要针对一些复杂的SQL语句，主要是SQL语句，计划器主要告诉PostgreSQL，如何更高效地执行复杂的SQL语句： 示例1 假设，有1个表，表上有1个索引，表中有1000万条数据 CREATE TABLE t1 (c1 INT, c2 TEXT); CREATE INDEX i1 ON t1(c1); INSERT INTO t1 .. -- 假设1000万条数据 当用户执行以下SQL语句时，数据库需要从t1表中，依次扫描所有的数据，此时，索引并没有什么作用： SELECT * FROM t1; 当用户执行以下数据时，数据有2种执行方案： 语句 SELECT * FROM t1 WHERE c1 = 10000; 方案1：依次扫描t1表所有数据，然后找到c1=10000的几行 方案2：先扫描索引i1，找到c1=10000的几行数据所在的位置，再去t1表中指定位置读取数据 显然，上述示例中，方案2的执行速度更快，数据库需要根据一定的策略，选择哪种执行方案。这便是计划器的职责。 1.2 生成计划的场景 SQL语法 CREATE TABLE t1 (c1 INT, c2 TEXT); INSERT INTO t1 VALUES (1, 'data-1'), (2, 'data-2'); ANALYZE; SELECT * FROM pg_statistic WHERE starelid = 't1'::regclass; 1.3 计划的简单表示 1.4 算子的分类 共40个 ...

1 概述 1.1 总接口 main PostmasterMain ServerLoop for (;;) select # 接收TCP连接 BackendStartup fork_process # fork postgres进程，用于处理SQL # postgres子进程执行： BackendRun # postgres子进程 BackendRun PostgresMain for (;;) ReadCommand exec_simple_query pg_parse_query # 语法解析 pg_analyze_and_rewrite # 语义分析 查询重写 pg_plan_queries # 计划生成 CreatePortal PortalDefineQuery PortalStart PortalRun PortalRunSelect ExecutorRun # 计划执行 standard_ExecutorRun ExecutePlan for (;;) ExecProcNode ExecSeqScan # SELECT 计划执行 SeqNext heap_beginscan heap_getnext PortalRunMulti # DDL执行 PortalRunUtility ProcessUtility standard_ProcessUtility ExecDropStmt # DROP TABLE ProcessUtilitySlow DefineRelation # CREATE TABLE ProcessQuery ExecutorStart ExecutorRun # 计划执行 standard_ExecutorRun ExecutePlan for (;;) ExecProcNode ExecModifyTable ExecInsert # INSERT 计划执行 heap_insert ExecUpdate # UPDATE 计划执行 heap_update ExecDelete # DELETE 计划执行 heap_delete ExecutorFinish ExecutorEnd PortalDrop exec_simple_query pg_plan_queries pg_plan_query planner standard_planner subquery_planner pull_up_subqueries preprocess_qual_conditions grouping_planner query_planner make_one_rel set_base_rel_pathlists # 查找所有Scan类型 set_rel_pathlist set_plain_rel_pathlist create_seqscan_path create_index_paths get_index_paths generate_bitmap_or_paths create_tidscan_paths get_cheapest_fractional_path create_plan create_plan_recurse create_scan_plan 1.2 算子类型 TupleTableSlot *ExecProcNode(PlanState *node) /* 控制算子 */ ExecResult((ResultState *) node); ExecModifyTable((ModifyTableState *) node); ExecAppend((AppendState *) node); ExecMergeAppend((MergeAppendState *) node); ExecRecursiveUnion((RecursiveUnionState *) node); /* 扫描算子 */ ExecSeqScan((SeqScanState *) node); ExecSampleScan((SampleScanState *) node); ExecIndexScan((IndexScanState *) node); ExecIndexOnlyScan((IndexOnlyScanState *) node); ExecBitmapHeapScan((BitmapHeapScanState *) node); ExecTidScan((TidScanState *) node); ExecSubqueryScan((SubqueryScanState *) node); ExecFunctionScan((FunctionScanState *) node); ExecValuesScan((ValuesScanState *) node); ExecCteScan((CteScanState *) node); ExecWorkTableScan((WorkTableScanState *) node); ExecForeignScan((ForeignScanState *) node); ExecCustomScan((CustomScanState *) node); /* 连接算子 */ ExecNestLoop((NestLoopState *) node); ExecMergeJoin((MergeJoinState *) node); ExecHashJoin((HashJoinState *) node); /* 物化算子 */ ExecMaterial((MaterialState *) node); ExecSort((SortState *) node); ExecGroup((GroupState *) node) ExecAgg((AggState *) node); ExecWindowAgg((WindowAggState *) node) ExecUnique((UniqueState *) node); ExecHash((HashState *) node); ExecSetOp((SetOpState *) node) ExecLockRows((LockRowsState *) node); ExecLimit((LimitState *) node); 2 算子 2.1 ExecSort /* 主要流程 */ TupleTableSlot *ExecSort(SortState *node) Tuplesortstate *tuplesortstate = node->tuplesortstate if !node->sort_Done: PlanState *outerNode = outerPlanState(node) tuplesortstate = tuplesort_begin_heap(ExecGetResultType(outerNode), numCols, sortColIdx, sortOperators) for (;;) /* 子节点扫描tup */ slot = ExecProcNode(outerNode) tuplesort_puttupleslot(tuplesortstate, slot) tuplesort_performsort(tuplesortstate) slot = node->ss.ps.ps_ResultTupleSlot tuplesort_gettupleslot(tuplesortstate, ScanDirectionIsForward(dir), slot) return slot /* 1 准备 */ Tuplesortstate *tuplesort_begin_heap(TupleDesc tupDesc, int nkeys, AttrNumber *attNums, Oid *sortOperators, Oid *sortCollations, bool *nullsFirstFlags) Tuplesortstate *state; state->comparetup = comparetup_heap state->copytup = copytup_heap; state->writetup = writetup_heap state->readtup = readtup_heap state->sortKeys = palloc0(nkeys * sizeof(SortSupportData)) for i in range(nkeys): SortSupport sortKey = state->sortKeys + i sortKey->ssup_attno = attNums[i] /* 2 扫描存储 */ void tuplesort_puttupleslot(Tuplesortstate *state, TupleTableSlot *slot) SortTuple stup COPYTUP(state, &stup, (void *) slot) puttuple_common(state, &stup) { switch state->status: case TSS_INITIAL: if state->memtupcount >= state->memtupsize: grow_memtuples(state) state->memtuples[state->memtupcount++] = *tuple if state->bounded: make_bounded_heap(state) inittapes(state) { int maxTapes = tuplesort_merge_order(state->allowedMem) + 1 tapeSpace = (int64) maxTapes *TAPE_BUFFER_OVERHEAD /* 创建临时文件 */ PrepareTempTablespaces() state->tapeset = LogicalTapeSetCreate(maxTapes) state->mergenext = palloc0(maxTapes * sizeof(int)) state->mergeavailslots = palloc0(maxTapes * sizeof(int)) ... ntuples = state->memtupcount state->memtupcount = 0 for j = 0; j < ntuples; j++ SortTuple stup = state->memtuples[j] tuplesort_heap_insert(state, &stup, 0, false) ... state->status = TSS_BUILDRUNS } dumptuples(state, false) { while state->memtupcount >= state->memtupsize: /* = writetup_heap */ WRITETUP(state, state->tp_tapenum[state->destTape], &state->memtuples[0]) tuplesort_heap_siftup(state, true) if state->memtupcount == 0 || state->currentRun != state->memtuples[0].tupindex: markrunend(state, state->tp_tapenum[state->destTape]) state->currentRun++ if state->memtupcount == 0: break selectnewtape(state) } case TSS_BOUNDED: if COMPARETUP(state, tuple, &state->memtuples[0]) <= 0: free_sort_tuple(state, tuple) else: free_sort_tuple(state, &state->memtuples[0]) tuplesort_heap_siftup(state, false) tuplesort_heap_insert(state, tuple, 0, false) case TSS_BUILDRUNS: if COMPARETUP(state, tuple, &state->memtuples[0]) >= 0: tuplesort_heap_insert(state, tuple, state->currentRun, true) else: tuplesort_heap_insert(state, tuple, state->currentRun + 1, true) dumptuples(state, false) } /* 3 排序 */ void tuplesort_performsort(Tuplesortstate *state) switch state->status: case TSS_INITIAL: /* 可在内存存放所有tup */ if state->memtupcount > 1: if state->onlyKey != NULL: qsort_ssup(state->memtuples, state->memtupcount, state->onlyKey) else: qsort_tuple(state->memtuples, state->memtupcount, state->comparetup, state) state->current = 0; state->status = TSS_SORTEDINMEM case TSS_BOUNDED: /* 使用堆排消除多余tup*/ sort_bounded_heap(state) state->status = TSS_SORTEDINMEM case TSS_BUILDRUNS: dumptuples(state, true) /* 将内存中所有元组写到持久化存储tape */ mergeruns(state) /* 合并执行 */ 2.2 tup读写 writetup_heap(Tuplestorestate *state, void *tup) MinimalTuple tuple = (MinimalTuple) tup

1 介绍JOIN 分类 INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN SELF JOIN CROSS JOIN 2 使用JOIN CREATE TABLE t1 (a1 INT, b1 TEXT); INSERT INTO t1 VALUES (1, 'u1'), (2, 'u2'), (3, 'u3'); CREATE TABLE t2 (b2 TEXT, c2 TEXT); INSERT INTO t2 VALUES ('u1', 'u1-1'), ('u1', 'u1-2'), ('u2', 'u2-1'), ('u4', 'u4-1'); -- INNER JOIN SELECT a1, b1, b2, c2 FROM t1 INNER JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 -- LEFT JOIN SELECT a1, b1, b2, c2 FROM t1 LEFT JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 3 | u3 | | -- RIGHT JOIN SELECT a1, b1, b2, c2 FROM t1 RIGHT JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 | | u4 | u4-1 -- FULL JOIN SELECT a1, b1, b2, c2 FROM t1 FULL JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 3 | u3 | | | | u4 | u4-1 -- SELF JOIN SELECT a1, b1, b2, c2 FROM t1 SELF JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; -- CROSS JOIN SELECT a1, b1, b2, c2 FROM t1 CROSS JOIN t2 -- can't use 'ON' ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 1 | u1 | u2 | u2-1 1 | u1 | u4 | u4-1 2 | u2 | u1 | u1-1 2 | u2 | u1 | u1-2 2 | u2 | u2 | u2-1 2 | u2 | u4 | u4-1 3 | u3 | u1 | u1-1 3 | u3 | u1 | u1-2 3 | u3 | u2 | u2-1 3 | u3 | u4 | u4-1

# 安装nodejs yum install -y nodejs # 安装claude npm install -g @anthropic-ai/claude-code # 验证 claude --version cd /data/postgresql17 # 5. 启动 Claude Code 并进行首次认证 # 运行后，终端会提供一个链接，在浏览器中完成账号授权即可 claude # 6. (可选但推荐) 生成项目记忆文件 # 在 Claude 交互界面中，输入以下命令，它会分析项目并生成 CLAUDE.md 文件 /init