PostgreSQL 中 PREPARE 和 EXECUTE 预编译语句

FreeGuideOnline 最新 2026-07-07

什么是预编译语句

在 PostgreSQL 中，PREPARE 和 EXECUTE 提供了一种预编译 SQL 语句的机制。你可以将一条带占位符的语句预先发送给数据库服务器，数据库会对其进行解析、分析和规划，生成一个持久化的执行计划。之后再通过 EXECUTE 命令，仅需提供参数就可以反复执行该语句，无需每次重新编译。这类似于其他数据库中“预处理语句（prepared statement）”的概念。

这种机制特别适合需要多次执行相同或相似查询的场景，能有效降低系统开销，并有助于防止 SQL 注入。

核心语法

创建预编译语句：PREPARE

PREPARE 用于定义一个命名的预编译语句，并指定参数类型。

PREPARE 语句名称 (参数类型1, 参数类型2, ...) AS
    SQL语句;
  • 语句名称：任意有效的标识符，在当前会话中唯一。
  • 参数类型：可选的参数列表，声明每个占位符的 PostgreSQL 数据类型（如 integer、text）。参数在 SQL 语句中使用 $1、$2……按位置引用。
  • SQL 语句：一条有效的 SELECT、INSERT、UPDATE、DELETE 或 VALUES 语句。

示例：准备一个按用户ID查询用户姓名的查询。

PREPARE get_user_name (integer) AS
    SELECT first_name, last_name
    FROM users
    WHERE user_id = $1;

执行预编译语句：EXECUTE

EXECUTE 用于运行之前定义好的预编译语句，并传入实际参数值。

EXECUTE 语句名称 (参数值1, 参数值2, ...);

参数值可以是具体的字面量、表达式或 NULL，其顺序和数量必须与 PREPARE 声明一致。

示例：执行上面创建的 get_user_name。

EXECUTE get_user_name(1001);
-- 等价于执行：SELECT first_name, last_name FROM users WHERE user_id = 1001;

删除预编译语句：DEALLOCATE

预编译语句只在当前数据库会话中存在，会话结束后自动消失。你也可以用 DEALLOCATE 手动释放。

DEALLOCATE [PREPARE] 语句名称;

示例：

DEALLOCATE get_user_name;

预编译语句的工作原理

当你执行 PREPARE 时，PostgreSQL 会完成以下步骤：

  1. 解析（Parse）：分析 SQL 语法，将字符串转化为解析树。
  2. 分析（Analyze）：进行语义检查，解析表名、列名等对象，判断语句类型和数据类型。
  3. 重写（Rewrite）：应用规则系统（如视图展开、行级安全策略等）。
  4. 规划（Plan）：生成一个或少量候选执行计划。与一次性即时查询不同，PREPARE 生成的计划会被缓存，供后续 EXECUTE 重用。

当你通过 EXECUTE 调用时，PostgreSQL 只需要：

  • 将提供的参数值代入计划中的占位符位置。
  • 直接执行已缓存好的计划。

因为跳过了编译和规划步骤，重复执行时性能会显著提升，尤其对于复杂查询。

使用场景与优势

1. 多次执行同一模式查询

如果你的应用程序需要频繁执行同一个查询，只是 WHERE 条件值不同，使用预编译语句可以避免重复解析和规划。

PREPARE update_salary (numeric, integer) AS
    UPDATE employees SET salary = $1 WHERE emp_id = $2;

-- 批量更新
EXECUTE update_salary(55000.00, 10);
EXECUTE update_salary(65000.00, 20);

2. 防止 SQL 注入

因为参数值是在编译之后才绑定到计划中的，其内容永远不会被当作 SQL 代码执行。这是预编译语句天然的抗注入特性，比手动拼接字符串安全得多。

PREPARE safe_login (text, text) AS
    SELECT * FROM accounts
    WHERE username = $1 AND password_hash = crypt($2, password_hash);

-- 即使传入带恶意字符的值，也只会被当作纯文本比较
EXECUTE safe_login('admin', 'wrong'' OR 1=1 --');

3. 减少网络开销

在客户端库（如 libpq、pgjdbc、psycopg2 等）中，预编译语句通常配合扩展查询协议使用，只需一次 Parse、一次 Bind/Execute，后续调用只需发送 Bind/Execute 消息，有效减少网络传输。

注意事项与常见陷阱

1. 执行计划可能与即时查询不同

PostgreSQL 在执行 PREPARE 时，还没有实际的参数值，因此会生成一个通用的执行计划。这个计划可能对所有参数值都不是最优的。例如，查询使用了带有数据倾斜的 WHERE 条件，通用计划可能选择全表扫描，而实际传入某个高选择性的值应该用索引扫描。

从 PostgreSQL 12 开始，可以通过设置 plan_cache_mode 参数来控制这一行为：

  • auto（默然）：前几次执行使用自定义计划（每次重新规划），当计划成本稳定后切换到通用计划。
  • force_custom_plan：永远为每次 EXECUTE 重新规划（类似即时查询）。
  • force_generic_plan：始终使用第一次 PREPARE 生成的通用计划。

示例：

SET plan_cache_mode = force_custom_plan;
EXECUTE get_user_name(1001);

2. 预编译语句仅在当前会话有效

PREPARE 创建的对象不能被其他会话共享，也不能存储在数据库中。每次新建连接都需要重新 PREPARE。因此它主要用于连接池环境下的同一会话复用，或通过客户端驱动自动管理。

3. 表结构变更影响

如果预编译语句引用的表或列在 PREPARE 之后发生了结构变化（如删除列、修改数据类型），后续 EXECUTE 可能会失败。需要先 DEALLOCATE 并重新 PREPARE。

4. 不适合一次性查询

如果一条查询只执行一次，预编译反而增加了额外步骤（Prepare + Execute），不如直接即时查询。仅当语句会被多次执行时才值得使用。

在客户端驱动中使用预编译语句

在实际开发中，你很少直接手写 PREPARE/EXECUTE SQL 命令，而是通过 PostgreSQL 客户端驱动提供的预编译语句接口。以下是一个常见的伪代码流程：

# 以 Python psycopg2 举例
cursor = conn.cursor()
cursor.execute("PREPARE my_plan (int) AS SELECT * FROM items WHERE id = $1")
cursor.execute("EXECUTE my_plan (%s)", (42,))

大多数驱动默认会使用 服务端预编译语句（通过扩展查询协议），并且会透明地缓存和复用语句句柄。你只需使用参数化查询：

cursor.execute("SELECT * FROM items WHERE id = %s", (42,))

驱动会在后台自动完成类似 PREPARE 和 EXECUTE 的工作，无需显式编写命令。

性能监控与对比

你可以使用 EXPLAIN ANALYZE 来观察预编译语句的执行计划，对比其与即时查询的差异。

PREPARE test_plan (integer) AS
    SELECT count(*) FROM large_table WHERE category = $1;

EXPLAIN ANALYZE EXECUTE test_plan(5);

注意观察执行计划中是否出现 “generic plan” 或 “custom plan” 字样的信息。

在 pg_stat_statements 扩展的统计视图中，预编译语句的调用也会被记录，可以通过 query 字段中的 PREPARE/EXECUTE 文本或规范化查询进行性能分析。

总结

  • PREPARE 和 EXECUTE 是 PostgreSQL 内置的预编译语句机制，可提升重复查询性能并防止注入。
  • 计划缓存可能带来计划质量陷阱，需关注 plan_cache_mode。
  • 预编译语句是会话本地对象，适合长连接或连接池复用。
  • 推荐通过客户端驱动参数化查询来间接使用预编译语句，兼顾易用性和性能。