postgresql一条sql语句详细执行过程

数据中穿行
2 阅读10分钟

我将详细解析 PostgreSQL 中一条 SQL 语句的完整执行过程，揭示其与 MySQL 不同的架构设计。PostgreSQL 采用基于进程的架构，每个客户端连接对应一个后端进程。

一、整体架构概览

PostgreSQL 采用客户端/服务器进程模型，其核心组件分层如下：

客户端应用
     ↓
libpq（客户端库） → 网络协议
     ↓
Postmaster（主进程，端口监听）
     ↓
后端进程（Backend，每个连接一个）
     ↓
PostgreSQL 服务进程
├── 解析器（Parser）
├── 分析器（Analyzer）
├── 重写器（Rewriter）
├── 规划器（Planner）
├── 执行器（Executor）
└── 存储系统
    ├── 表/索引访问方法
    ├── 缓冲区管理器
    ├── 事务管理器
    └── 日志管理器

二、详细执行过程

以下以 SELECT * FROM users WHERE id = 1;为例，详细说明每个步骤。

阶段 1：连接建立与初始化

  1. 客户端连接请求

    • 客户端通过 TCP/IP 或 Unix Socket 连接到 PostgreSQL 的 5432 端口
    • 发送启动包，包含用户名、数据库名、字符集等参数

  2. Postmaster 进程接收连接

    // src/backend/postmaster/postmaster.c
int ServerLoop(void) {
    for (;;) {
        // 1. 接受新连接
        int port = PostPortNumber;
        int server_fd = StreamConnection(port);

        // 2. 身份验证
        if (!CheckConnectionAuth(server_fd, &port)) {
            close(server_fd);
            continue;
        }

        // 3. 创建后端进程
        BackendStartup(server_fd);
    }
}

  3. 后端进程创建

    • Postmaster 通过 fork()创建新的后端进程
    • 子进程继承父进程的内存空间，包括共享内存
    • 初始化进程状态，加载必要的库

  4. 后端初始化

    // src/backend/postmaster/postmaster.c
static void BackendInitialize(Port *port) {
    // 1. 设置进程标题
    init_ps_display(port->database_name, port->user_name);

    // 2. 初始化内存上下文
    MemoryContextInit();
    TopMemoryContext = AllocSetContextCreate(...);

    // 3. 加载GUC参数
    InitializeGUCOptions();

    // 4. 建立数据库连接
    InitPostgres(database_name, InvalidOid, username, InvalidOid, NULL);

    // 5. 进入命令循环
    PostgresMain(ac, av, port->database_name, port->user_name);
}

阶段 2：查询解析

  1. 读取SQL语句

    // src/backend/tcop/postgres.c
void PostgresMain(int argc, char *argv[], 
                  const char *dbname, const char *username) {
    for (;;) {
        // 1. 读取命令
        StringInfoData input_message;
        if (pq_getmessage(MyProcPort, &input_message, 0))
            break;

        // 2. 根据消息类型处理
        switch (input_message.data[0]) {
            case 'Q':  // 简单查询
                exec_simple_query(input_message.data + 1);
                break;
            case 'P':  // 扩展查询
                exec_extended_query(...);
                break;
        }
    }
}

  2. 词法分析（Flex）

    PostgreSQL 使用 Flex 生成词法分析器：

    /* src/backend/parser/scan.l */
/* 匹配SELECT关键字 */
[Ss][Ee][Ll][Ee][Cc][T]  { return SELECT; }

/* 匹配标识符 */
{identifier}  { yylval.str = pstrdup(yytext);
                return IDENT; }

/* 匹配数字 */
{decimal}  { yylval.ival = atoi(yytext);
             return ICONST; }

  3. 语法分析（Bison）

    PostgreSQL 使用 Bison 生成语法分析器，生成解析树：

    /* src/backend/parser/gram.y */
stmtblock:  stmtmulti
            { parsetree = $1; }
;

stmtmulti:  stmt ';'
            { $$ = list_make1($1); }
        |   stmtmulti stmt ';'
            { $$ = lappend($1, $2); }
;

stmt:  SelectStmt
        { $$ = $1; }
;

SelectStmt:  select_clause opt_sort_clause
            { $$ = makeNode(SelectStmt);
              $$
->targetList = $1;
              $$->sortClause = $2; }
;

  4. 生成原始解析树

    解析器输出一个原始解析树（Raw Parse Tree），这是一个 C 结构体树：

    // src/include/nodes/parsenodes.h
typedef struct SelectStmt {
    NodeTag     type;          // 节点类型标识
    List       *distinctClause;
    IntoClause *intoClause;
    List       *targetList;    // 目标列表 (要查询的列)
    List       *fromClause;    // FROM子句
    Node       *whereClause;   // WHERE条件
    List       *groupClause;
    Node       *havingClause;
    List       *windowClause;
    // ... 其他字段
} SelectStmt;

// 我们的查询会生成类似结构：
SelectStmt {
    targetList: {ResTarget {val: ColumnRef {fields: {"*"}}}}},
    fromClause: {RangeVar {relname: "users", ...}},
    whereClause: {A_Expr {
        kind: AEXPR_OP,
        name: {"="},
        lexpr: ColumnRef {fields: {"id"}},
        rexpr: A_Const {val: {ival: 1}}
    }}
}

阶段 3：语义分析

  1. 分析器（Analyzer）转换

    // src/backend/parser/analyze.c
List *parse_analyze(RawStmt *parseTree, const char *sourceText,
                    Oid *paramTypes, int numParams) {
    // 1. 创建分析状态
    ParseState *pstate = make_parsestate(NULL);

    // 2. 转换原始解析树为查询树
    Query *query = transformTopLevelStmt(pstate, parseTree);

    // 3. 语义检查
    parse_analyze_varparams(pstate, parseTree, ...);

    return query;
}

// 转换SELECT语句
static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt) {
    Query *qry = makeNode(Query);
    qry->commandType = CMD_SELECT;

    // 处理FROM子句
    qry->rtable = transformFromClause(pstate, stmt->fromClause);

    // 处理目标列表
    qry->targetList = transformTargetList(pstate, stmt->targetList);

    // 处理WHERE子句
    qry->jointree = makeFromExpr(pstate->p_joinlist,
                                 transformWhereClause(pstate, 
                                                      stmt->whereClause));
    return qry;
}

  2. 名称解析与类型检查

    • 检查表名、列名是否存在
    • 解析列的数据类型
    • 检查表达式的类型兼容性
    • 处理隐式类型转换

  3. 生成查询树（Query Tree）

    // src/include/nodes/parsenodes.h
typedef struct Query {
    NodeTag        type;
    CmdType        commandType;    // SELECT, INSERT, UPDATE, DELETE
    List          *rtable;         // 范围表（表、子查询等）
    List          *targetList;     // 目标列表
    FromExpr      *jointree;       // FROM/WHERE子句的连接树
    List          *sortClause;     // ORDER BY
    // ... 其他字段
} Query;

阶段 4：查询重写

  1. 规则系统与视图展开

    // src/backend/rewrite/rewriteHandler.c
List *QueryRewrite(Query *parsetree) {
    List *rewritten = NIL;

    // 1. 非SELECT语句的重写（规则）
    if (parsetree->commandType != CMD_SELECT)
        rewritten = RewriteQuery(parsetree, NIL);
    else
        rewritten = list_make1(parsetree);

    // 2. 视图展开
    List *new_queries = NIL;
    foreach(lc, rewritten) {
        Query *q = (Query *) lfirst(lc);
        new_queries = lappend(new_queries, 
                              fireRIRrules(q, NIL));
    }

    return new_queries;
}

  2. 视图展开示例

    如果查询涉及视图，重写器会将其展开为对基表的查询：

    -- 原始查询
SELECT * FROM user_view WHERE id = 1;

-- 视图定义
CREATE VIEW user_view AS 
SELECT id, name, email FROM users WHERE active = true;

-- 重写后
SELECT id, name, email FROM users 
WHERE active = true AND id = 1;

阶段 5：查询优化（规划器）

这是 PostgreSQL 最复杂的部分，规划器生成最优的执行计划。

  1. 标准规划器流程

    // src/backend/optimizer/plan/planner.c
PlannedStmt *standard_planner(Query *parse, const char *query_string, 
                              int cursorOptions, ParamListInfo boundParams) {
    // 1. 预处理
    Query *query = preprocess_query(parse, query_string);

    // 2. 生成所有可能的访问路径
    RelOptInfo *rel = make_one_rel(root, joinlist);

    // 3. 从路径中创建最佳计划
    Plan *plan = create_plan(root, rel);

    // 4. 后处理
    plan = set_plan_references(root, plan);

    return makeNode(PlannedStmt, ...);
}

  2. 代价估算与统计信息

    // src/backend/optimizer/path/costsize.c
void cost_seqscan(Path *path, PlannerInfo *root,
                  RelOptInfo *baserel, ParamPathInfo *param_info) {
    // 计算顺序扫描的代价
    Cost startup_cost = 0;
    Cost run_cost = 0;

    // CPU代价
    run_cost += cpu_tuple_cost * baserel->tuples;

    // IO代价
    if (baserel->pages > 0) {
        double spc_seq_page_cost = get_tablespace_page_cost();
        run_cost += spc_seq_page_cost * baserel->pages;
    }

    path->startup_cost = startup_cost;
    path->total_cost = startup_cost + run_cost;
}

  3. 索引扫描代价计算

    // src/backend/optimizer/path/costsize.c
void cost_index(IndexPath *path, PlannerInfo *root,
                double loop_count) {
    // 索引扫描的代价包括：
    // 1. 索引页读取代价
    double index_pages = ceil(indexSelectivity * index->pages);
    startup_cost += index_pages * random_page_cost;

    // 2. 堆（表）页读取代价
    double heap_pages = ceil(indexSelectivity * baserel->pages);
    run_cost += heap_pages * random_page_cost;

    // 3. CPU处理代价
    run_cost += cpu_index_tuple_cost * tuples_fetched;
}

  4. 连接顺序优化

    PostgreSQL 使用动态规划算法确定最佳连接顺序：

    // src/backend/optimizer/path/joinpath.c
static void standard_join_search(PlannerInfo *root, int levels_needed) {
    // 动态规划表
    List *joinrels[levels_needed + 1];

    // 初始化：单表
    for (int i = 0; i < levels_needed; i++) {
        joinrels[1] = initial_rels[i];
    }

    // 动态规划：合并表集合
    for (int k = 2; k <= levels_needed; k++) {
        for (int r = 1; r < k; r++) {
            for each r_rel in joinrels[r] {
                for each s_rel in joinrels[k-r] {
                    // 尝试所有可能的连接方式
                    try_hashjoin_path(root, r_rel, s_rel, ...);
                    try_mergejoin_path(root, r_rel, s_rel, ...);
                    try_nestloop_path(root, r_rel, s_rel, ...);
                }
            }
        }
    }
}

  5. 生成执行计划树

    // src/include/nodes/plannodes.h
typedef struct Plan {
    NodeTag    type;
    double     startup_cost;  // 启动代价
    double     total_cost;    // 总代价
    int        plan_rows;     // 估计行数
    List      *targetlist;    // 目标列
    List      *qual;          // 过滤条件
    struct Plan *lefttree;    // 左子树
    struct Plan *righttree;   // 右子树
    // ... 其他字段
} Plan;

// 我们的查询可能生成以下计划树：
IndexScan {
    indexqual: id = 1
    indexid: users_pkey
    ↓
SeqScan on users

阶段 6：查询执行

  1. 执行器初始化

    // src/backend/executor/execMain.c
void ExecutorStart(QueryDesc *queryDesc, int eflags) {
    // 1. 创建执行状态
    EState *estate = CreateExecutorState();

    // 2. 初始化计划状态
    queryDesc->planstate = ExecInitNode(queryDesc->plantree,
                                        estate, eflags);

    // 3. 初始化结果元组槽
    queryDesc->tupDesc = ExecTypeFromTL(queryDesc->tlist, false);
}

  2. 执行引擎 - 火山模型

    PostgreSQL 使用经典的火山模型（迭代器模型），每个节点实现三个方法：

    // src/backend/executor/execProcnode.c
TupleTableSlot *ExecProcNode(PlanState *node) {
    // 调用具体节点的执行函数
    return node->ExecProcNode(node);
}

// 示例：顺序扫描节点的执行
static TupleTableSlot *ExecSeqScan(SeqScanState *node) {
    TableScanDesc scandesc = node->ss_currentScanDesc;
    HeapTuple tuple;

    // 获取下一个元组
    tuple = heap_getnext(scandesc, ScanDirectionForward);

    if (tuple == NULL) {
        // 扫描结束
        return ExecClearTuple(node->ss_ScanTupleSlot);
    }

    // 将元组存储到槽中
    ExecStoreHeapTuple(tuple, node->ss_ScanTupleSlot, ...);

    return node->ss_ScanTupleSlot;
}

  3. 索引扫描执行

    // src/backend/executor/nodeIndexscan.c
static TupleTableSlot *ExecIndexScan(IndexScanState *node) {
    for (;;) {
        // 1. 从索引获取下一个TID（元组ID）
        tid = index_getnext_tid(node->iss_ScanDesc,
                                node->iss_Direction);

        if (tid == NULL)
            break;  // 没有更多匹配

        // 2. 通过TID从堆（表）中获取元组
        tuple = heap_fetch(node->ss.ss_currentRelation,
                           node->iss_ScanDesc->xs_snapshot,
                           &tid, ...);

        // 3. 可见性检查（MVCC）
        if (!HeapTupleSatisfiesVisibility(tuple, snapshot, buffer))
            continue;

        // 4. 应用过滤条件
        if (!ExecQual(node->indexqual, econtext))
            continue;

        // 找到匹配的元组
        ExecStoreHeapTuple(tuple, slot, buffer);
        return slot;
    }

    return ExecClearTuple(slot);
}

  4. 可见性检查（MVCC）

    PostgreSQL 通过多版本并发控制处理事务隔离：

    // src/backend/access/heap/heapam_visibility.c
bool HeapTupleSatisfiesVisibility(HeapTuple tuple,
                                  Snapshot snapshot,
                                  Buffer buffer) {
    // 检查元组对当前快照是否可见

    // 获取元组的事务ID
    TransactionId xmin = HeapTupleHeaderGetXmin(tuple->t_data);
    TransactionId xmax = HeapTupleHeaderGetXmax(tuple->t_data);

    // 规则1：已提交的事务且对当前快照可见
    if (TransactionIdIsInProgress(xmin))
        return false;  // 插入事务仍在进行

    if (TransactionIdDidCommit(xmin)) {
        if (!XidInMVCCSnapshot(xmin, snapshot)) {
            // 插入已提交且不在快照的活跃事务列表中
            if (!(tuple->t_data->t_infomask & HEAP_XMAX_INVALID) &&
                TransactionIdIsInProgress(xmax))
                return false;  // 被未提交的事务删除

            return true;  // 可见
        }
    }

    return false;
}

阶段 7：结果返回

  1. 结果格式化

    // src/backend/tcop/dest.c
void printtup(TupleTableSlot *slot, DestReceiver *self) {
    // 1. 获取元组描述符
    TupleDesc typeinfo = slot->tts_tupleDescriptor;

    // 2. 格式化每一列
    for (int i = 0; i < typeinfo->natts; i++) {
        Datum attr = slot_getattr(slot, i + 1, &isnull);

        if (isnull) {
            pq_sendstring(&buf, "\N");  // 发送NULL
        } else {
            // 将Datum转换为字符串
            char *output = OutputFunctionCall(&typeinfo->attrs[i], attr);
            pq_sendcountedtext(&buf, output, strlen(output), false);
        }
    }

    // 3. 发送到网络
    pq_putmessage('D', buf.data, buf.len);
}

  2. 网络传输

    PostgreSQL 使用自定义的前端/后端协议：

    • 结果以消息流的形式发送
    • 每条消息以类型字节开头
    • 数据行以 'D' 消息发送
    • 命令完成以 'C' 消息发送

  3. 事务提交

    // src/backend/access/transam/xact.c
void CommitTransaction(void) {
    // 1. 记录提交日志
    XactLogCommitRecord();

    // 2. 更新CLOG（事务提交日志）
    TransactionIdCommit(xid);

    // 3. 释放锁
    LockReleaseAll(DEFAULT_LOCKMETHOD, true);

    // 4. 清理资源
    AtCommit_Memory();
    AtCommit_Notify();

    // 5. 设置事务状态
    s->state = TRANS_COMMIT;
}

三、UPDATE/DELETE 语句的额外步骤

对于写操作，PostgreSQL 使用 HOT（Heap-Only Tuple）优化：

// src/backend/access/heap/heapam.c
HTSU_Result heap_update(Relation relation, ItemPointer otid,
                        HeapTuple newtup, CommandId cid,
                        Snapshot crosscheck, bool wait) {
    // 1. 标记旧元组为过期
    oldtup->t_data->t_infomask |= HEAP_UPDATED;
    oldtup->t_data->t_ctid = newtup->t_self;
    
    // 2. 检查是否可以使用HOT
    if (HeapSatisfiesHOT(relation, oldtup, newtup)) {
        // HOT更新：新元组放在同一页
        newtup->t_data->t_infomask2 |= HEAP_HOT_UPDATED;
        newtup->t_data->t_infomask |= HEAP_ONLY_TUPLE;
        
        // 更新同一页面，无需更新索引
        PageAddItem(page, (Item) newtup->t_data, newtup->t_len, ...);
    } else {
        // 非HOT更新：需要更新索引
        RelationGetIndexList(relation);
    }
    
    // 3. 写入WAL日志
    XLogBeginInsert();
    XLogRegisterData((char *) &xlrec, SizeOfHeapUpdate);
    XLogInsert(RM_HEAP_ID, XLOG_HEAP_UPDATE);
}

四、WAL（预写日志）机制

// src/backend/access/transam/xlog.c
XLogRecPtr XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata) {
    // 1. 保留插入位置
    START_CRIT_SECTION();
    
    // 2. 复制日志记录到WAL缓冲区
    cur_insert = Insert->CurrBytePos;
    copy = XLogCopy(rdata, cur_insert);
    
    // 3. 更新LSN
    XLogCtl->Insert->CurrBytePos += copy->len;
    
    // 4. 检查是否触发刷写
    if (XLogNeedsFlush(copy->StartPos)) {
        XLogFlush(copy->EndPos);
    }
    
    END_CRIT_SECTION();
    
    return copy->EndPos;
}

五、执行流程可视化

客户端请求
    ↓
Postmaster（接收连接，fork后端进程）
    ↓
后端进程初始化
    ↓
读取SQL语句 → 协议解析
    ↓
原始解析（词法/语法分析，生成原始解析树）
    ↓
语义分析（名称解析、类型检查，生成查询树）
    ↓
查询重写（规则/视图展开）
    ↓
查询优化（规划器）
    ├── 预处理
    ├── 路径生成
    ├── 代价估算
    ├── 连接顺序优化
    └── 生成执行计划树
    ↓
查询执行（执行器）
    ├── 初始化执行状态
    ├── 火山模型迭代执行
    ├── 存储引擎访问
    │   ├── 缓冲区管理器
    │   ├── 堆/索引访问
    │   ├── MVCC可见性检查
    │   └── 锁管理
    └── 返回元组
    ↓
结果格式化与网络传输
    ↓
客户端接收结果

六、与MySQL的关键差异

  1. 进程 vs 线程模型

    • PostgreSQL：每个连接一个进程
    • MySQL：每个连接一个线程（InnoDB）

  2. MVCC实现

    • PostgreSQL：在表数据中存储多版本
    • MySQL：在Undo Log中构造多版本

  3. 存储引擎

    • PostgreSQL：统一存储引擎，可扩展访问方法
    • MySQL：插件式存储引擎

  4. 优化器

    • PostgreSQL：基于遗传算法的复杂查询优化
    • MySQL：基于动态规划的优化器

  5. WAL实现

    • PostgreSQL：完整的WAL，支持时间点恢复
    • MySQL：Redo/Undo Log分离

这个执行过程展示了 PostgreSQL 如何将声明式的 SQL 转换为底层操作，其模块化设计和丰富的扩展性使其成为企业级应用的强大选择。

avatar
文章
阅读
粉丝