likes
comments
collection
share

PostgreSQL技术问答33 - Prepare预备执行本文探讨了Postgres中，一种性能和安全优化的数据操作技

作者站长头像
站长
· 阅读数 31

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是在Postgres中，一个用于性能和安全优化的功能和机制： Prepare，也被称为预备执行。

什么是Prepare

Prepare的中文意为预备的意思。在Postgres中，它是一种执行SQL语句的机制。在一般情况下，数据库系统收到一个SQL语句后，将直接对其进行解析和执行。而Prepare顾名思义，将这个执行模式分为两个阶段，先进行准备(Preparse)，然后再进行执行(Execute)。

为什么要这样设计？

主要有两点考虑，就是性能和安全。

先来说性能。完整的Prepare使用的生命周期，包括两个阶段。准备阶段是基于一个SQL语句，在数据库服务端创建一个执行对象，系统将会为其执行进行相关的准备和优化，包括解析、分析等工作；在执行阶段，就可以省略这些过程，直接使用参数进行执行了。显然，这种机制非常适合于大批量基本模式相同只是参数有差异的执行操作，比如批量的插入和更新操作，频繁的类似的信息查询等等的应用场景。

另一个好处就是安全性了。所有的Prepare语句都是参数化的，它可以直接利用数据库系统内部的参数检查机制，来抵抗和削弱SQL注入攻击。从而能够提供更安全的SQL语句执行方式。另外，通过封装一些调用过程和内容的复杂性，对于调用只呈现一个简单的调用界面和参数列表，可以减少攻击面。

从业务功能角度而言，预备执行方式，并不是明显和唯一的实现方式。和标准执行方式相比，也会带来一些操作和管理方面的复杂性。所以，在实际的应用环境中，开发者和数据运维人员，应当仔细的评估其在具体场景中所带来的性能和安全方面的优势，合理配置和使用。基本的原则就是在数据库中，如果有非常大量频繁的数据查询或者操作(如物联网传感器数据采集接口)，就可以考虑这种执行方式，它就会在长期的执行过程中，累计可观的执行效率和收益。

如何操作

Postgres中，提供了相关的语句和指令，来实现Prepare的操作和机制。Prepare的标准形式是：

PREPARE name [ ( data_type [, ...] ) ] AS statement

在这个标准语法结构中：

  • Prepare：是关键字，表示要声明和创建一个Prepare的对象
  • name： 是Prepare对象名称，后续Execute语句可以使用
  • data_type： 可选的执行参数类型，这里没有参数名称，按照顺序调用
  • AS： 结构关键字
  • statement: 实际执行所使用的SQL语句，包括Select、Insert、UPdate、Delete、Merge和Values等

随后，在实际进行操作的时候，使用Execute语言，来执行这个Prepare对象：

EXECUTE name(parameter_values);

这里的name就是前一步设置的对象名称。执行过程调用可以接受参数，并在实际执行语句中，进行对位的替换。Postgree预备语句可以选择指定相应的参数数据类型列表。当未指定参数的数据类型或将其声明为unknown时，将从首次引用该参数的上下文中推断出类型(如果可能）。

举个例子

下面是官方技术文档中的例子：

-- 声明阶段
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);

-- 执行预备语句
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

-- 可选参数类型
PREPARE usrrptplan (int) AS
SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2;

EXECUTE usrrptplan(1, current_date);

可以看到，虽然在执行计划时，原有的直接执行SQL语句的逻辑，已经被修改称为类似函数调用，输入参数的方式，但实际上这些参数的类型声明是可以选择或者忽略的，它们会按照参数的次序注入调用过程。

作为一个更完整的应用生命周期，我们可能还需要对当前的预备执行对象进行执行信息查询、检查存在和销毁等操作，具体操作命令如下：

-- 搜索已经存在的预备语句， 使用系统视图 pg_prepared_statements
defaultdb=> SELECT * FROM pg_prepared_statements;
    name    |                           statement                            |         prepare_time          | parameter_types | from_sql | generic_plans | custom_plans 
    
------------+----------------------------------------------------------------+-------------------------------+-----------------+----------+---------------+-------------- 

query_city | PREPARE query_city(int) AS select * from gcode where pid = $1; | 2024-08-15 08:22:10.251255+00 | {integer}       | t        |             0 |            1

(1 row)

-- 检查存在性
SELECT 1 FROM pg_prepared_statements WHERE name = 'my_prepared_stmt' limit 1;

-- 销毁预备语句 
DEALLOCATE my_prepared_stmt;

Prepare实际是如何工作的

其实，Prepare执行准备好的语句，也是有其执行规划和优化策略的。可以分为通用计划和自定义计划。通用计划在所有执行中都是相同的，而自定义计划则可能使用该调用中给定的参数值来进行特定的调整。自定义计划只对有参数的预备语句有效。没有参数的预备语句，将只使用通用计划。所以我们后面的讨论，都是针对有参数的预备执行。开发者可以根据实际情况，来通过一些参数来控制这些计划的选择和策略。

例如，在默认情况下（即，当plan_cache_mode设置为auto时）服务器将自动选择对具有参数的预处理语句使用通用计划还是自定义计划。当前的规则是，前五次执行使用自定义计划，并计算这些计划的平均估计成本。然后创建一个通用计划，并将其估计成本与平均自定义计划成本进行比较。如果通用计划的成本没有比平均自定义计划成本高出太多，以至于重复重新规划看起来更可取，则后续执行将使用通用计划。

当然，开发者可以手动的进行设置。plan_cache_mode通过分别设置为force_generic_plan或来强制服务器使用通用或自定义计划force_custom_plan。此设置主要用于通用计划的成本估算由于某种原因严重偏离的情况，即使其实际成本远高于自定义计划，也可以选择它。

开发者也可以使用Explain来检查预备语句的执行计划，来评估执行的代价和效果：

EXPLAIN EXECUTE name(parameter_values);

有哪些限制和需要注意的问题

首先可以看到Prepare的实现过程并没有那么简单直接，而是先创建数据库对象，然后在后续过程中，进行调用和操作，这给开发和应用过程就会带来一些复杂性。

需要特别注意的问题是：准备好的语句仅在当前数据库会话期间有效。会话结束时，准备好的语句将被丢弃，因此必须在新的会话中重新创建才能再次使用。这意味着单个准备好的语句不能被多个同时运行的数据库客户端使用；但是，每个客户端都可以创建自己的准备好的语句来使用。笔者觉得，这看起来是一个在实际应用中可能会比较大的限制，所以不适合作为通用的Web应用数据操作技术来使用，因为在实际的Web应用中，通常使用数据库连接池的技术来维护这些会话，不能保证预备语句总是可用。

尽管Prepare语句的主要目的是避免对SQL语句进行重复的解析分析和规划，但是，如果发生下面的几种情况之一，Postgres也会在执行前强制进行重新分析和规划：

  • 相关数据库对象发生定义(DDL)更改
  • 自上次使用的Prepare语句以来其规划器统计信息已更新
  • search_path的值在使用后发生变化

最后就是性能的收益。当使用单个会话执行大量类似语句时，准备好的语句可能具有最大的性能优势。如果语句的规划或重写很复杂（例如，如果查询涉及多个表的连接或需要应用多个规则），则性能差异将特别明显。如果语句的规划和重写相对简单，但执行成本相对较高，则准备好的语句的性能优势将不那么明显。

所以，Prepare技术，在一般的Web应用开发中，可能合适的使用场景并不多。但可能在日常数据维护的场景中，特别时使用脚本程序来进行数据操作的情况中，有一些应用的机会(单一会话，大量数据操作)。

小结

本文探讨了Postgres中，一种性能和安全优化的数据操作技术方案：Prepare（预备执行）。包括其基本的运行方式和原理，操作过程，一些技术方面的限制和需要注意的问题等等，并讨论了其适合的应用场景。

转载自:https://juejin.cn/post/7404747823226306614
评论
请登录