暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PolarDB-PG的Prepared Statement使用与监控

PolarDB-PG的Prepared Statement使用与监控

PolarDB 2025-01-20
90

关于 PolarDB PostgreSQL 版

PolarDB PostgreSQL 版（简称 PolarDB-PG）是一款阿里云自主研发的云原生关系型数据库产品，100% 兼容 PostgreSQL，高度兼容 Oracle 语法；采用基于 Shared-Storage 的存储计算分离架构，具有极致弹性、毫秒级延迟、HTAP 、Ganos 全空间数据处理能力和高可靠、高可用、弹性扩展等企业级数据库特性。同时，PolarDB PostgreSQL 版具有大规模并行计算能力，可以应对 OLTP 与 OLAP 混合负载。

简介

Prepared Statement（预备语句）是 PostgreSQL 数据库中执行查询或更新操作的方法，支持将 SQL 语句和参数分别传递给数据库。它的使用分为两个阶段：PREPARE
命令创建一个 Prepared Statement，只需要调用一次；EXECUTE
命令执行 Prepared Statement，可以反复调用，且每次可以带不同的参数。

使用方法

下面在 psql 和 JDBC 客户端中使用具体的 SQL 示例来介绍如何使用 Prepared Statement。

首先使用 psql 客户端连接到 PostgreSQL 数据库，准备一张含有 id 和 name 这两个列的 users 表，并向表中插入三行数据。

postgres=# create table users (id int, name text);
CREATE TABLE
postgres=# insert into users values(1, 'Kevin');
INSERT 0 1
postgres=# insert into users values(2, 'Russell');
INSERT 0 1
postgres=# insert into users values(3, 'Stephen');
INSERT 0 1

复制

PREPARE 阶段：如下是带有一个参数的 Prepared Statement，我们将其命名为 get_user_by_id，它的语义是将传入的参数值与 users 表中 id 列的值进行比较，查找出满足条件的数据。其中 表示参数占位符，如果有更多参数，则可以使用2、$3 来表示，以此类推。

postgres=# prepare get_user_by_id as select * from users where id = $1;
PREPARE

复制

EXECUTE 阶段：使用 execute get_user_by_id(<id>)
的方式执行上一步创建的 Prepared Statement，其中  可以替换为不同的参数。如下所示，不同的参数值可以查询出不同的结果。

postgres=# execute get_user_by_id(1);
 id | name
----+-------
  1 | Kevin
(1 row)

postgres=# execute get_user_by_id(2);
 id |  name
----+---------
  2 | Russell
(1 row)

postgres=# execute get_user_by_id(3);
 id |  name
----+---------
  3 | Stephen
(1 row)

复制

DEALLOCATE 命令：使用deallocate get_user_by_id
命令可以删除前面创建的 Prepared Statement。

如果不显式执行 deallocate 操作，Prepared Statement 会在当前数据库会话退出时自动释放。

postgres=# deallocate get_user_by_id;
DEALLOCATE

复制

在具体的业务场景中，通常会更多地使用 JDBC 来连接 PostgreSQL 数据库并使用 Prepared Statement，代码示例如下。

  1. PREPARE 阶段：用指定的 SQL 语句新建一个 PreparedStatement 对象，其中 '?' 为参数占位符

  2. 设置参数值：通过 PreparedStatement 对象的 set 方法将占位符 '?' 替换为具体的参数值

  3. EXECUTE 阶段：通过 PreparedStatement 对象的 execute/executeQuery/executeUpdate 等方法来执行查询或更新操作

  4. DEALLOCATE 操作：执行 PreparedStatement 对象的 close 方法，就实现了 deallocate 操作

// 连接到数据库
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/mydb", "username", "password");

// 准备SQL语句
String sql = "SELECT * FROM users WHERE id = ?";

// 创建预处理语句对象
PreparedStatement pstmt = conn.prepareStatement(sql);

// 设置参数值
int userId = 1;
pstmt.setInt(1, userId);

// 执行查询
ResultSet rs = pstmt.executeQuery();

// 处理结果集
while (rs.next()) {
    // do something with the results
}

// 自动DEALLOCATE
pstmt.close();

复制

Prepared Statement的优势

与直接执行简单查询（Simple Query）相比，Prepared Statement 的主要优势如下。

性能优势：

  1. 节省解析器开销：Prepared Statement 在PREPARE
    阶段将 SQL 语句预编译一次，后续多次执行EXECUTE
    的过程中无需再次进入语法解析器相关逻辑，节省了 SQL 解析成本，对于比较复杂的 SQL 尤为显著。

  2. 节省优化器开销：多次EXECUTE
    过程可以复用 Plan Cache 中缓存的执行计划，节省了优化器生成执行计划的开销，EXECUTE
    的次数越多，收益就越明显。

  3. 节省 Plan Cache 内存占用：如果有大量的连接执行相同的 Prepared Statement，那么不同连接会共享 GPC（Global Plan Cache）中缓存的执行计划，GPC 中需要缓存的执行计划数量就更少。与之相比，如果直接执行 SQL 查询，只要 SQL 中的查询条件稍有变化，就无法共享执行计划，从而增加了 GPC 需要缓存的执行计划数量。连接数越多，连接之间的 Prepared Statement 越相似，收益就越明显（PolarDB-PG 的 1.1.28 和 2.0.14.13.0 之后的版本支持 GPC）。

其他优势：

  1. 简化代码：使用 Prepared Statement 可以让代码更加简洁易读，因为预编译的 SQL 语句可以分离出来，不需要写在代码中，提高应用程序的可维护性。

  2. 支持多种数据类型：Prepared Statement 支持包括字符串、数字、日期等多种数据类型，可以更加灵活地处理数据。

  3. 防止 SQL 注入攻击：使用 Prepared Statement 可以避免 SQL 注入攻击，因为预编译的参数会被当作参数传递，而不是 SQL 语句的一部分。

Prepared Statement使用误区

前面介绍了 Prepared Statement 的基本用法和它的各项优势，然而 Prepared Statement 并非随意使用就能发挥出优势，如果使用不当，可能无法获得预期的性能收益。

从 PolarDB for PostgreSQL 的客户对 Prepared Statement 的使用情况来看，最常见的问题如下：

  1. 完全不使用 Prepared Statement，即所有的查询都是简单查询。这样每一次执行查询时都需要进行 SQL 解析，如果 SQL 比较复杂，则有较大的解析代价，解析阶段可能消耗较多内存。
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM users WHERE id = 1");

复制
  1. 使用了 Prepared Statement，但是没有实现参数化，即没有使用占位符 '?' 来表示可变的参数。比如下面的例子，两次查询时 id 列上的值不同，但是没有使用占位符来代替具体的数字，PostgreSQL 内核会认为两个 SQL 不相同，第二次执行 SQL 时无法复用第一次执行 SQL 生成的语法树和执行计划。
// 第一次使用，id = 1
String sql = "SELECT * FROM users WHERE id = 1";
PreparedStatement pstmt = conn.prepareStatement(sql);

// 第二次使用，id = 2
String sql = "SELECT * FROM users WHERE id = 2";
PreparedStatement pstmt = conn.prepareStatement(sql);

复制
  1. 使用了参数化的 Prepared Statement，但是参数化不完全。下面的例子相比上面的例子已经有了进步，id 这个列上使用了占位符来表示参数，但是 name 这个列上的不同值没有使用占位符将其定义为可变参数，与上一条同理，这两次执行也会被 PostgreSQL 内核认定为两个不同的 SQL，导致无法复用语法树和执行计划。
// 第一次使用，name = 'Kevin'
String sql = "SELECT * FROM users WHERE id = ? AND name = 'Kevin'";
PreparedStatement pstmt = conn.prepareStatement(sql);

// 第二次使用，id = 2
String sql = "SELECT * FROM users WHERE id = ? AND name = 'Russell'";
PreparedStatement pstmt = conn.prepareStatement(sql);

复制

以上例子中的正确 Prepared Statement 用法如下，在 id 和 name 列上都定义参数，每次执行之前通过 set 方法设置参数值，然后再执行查询。

String sql = "SELECT * FROM users WHERE id = ? AND name = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

// 设置参数值
pstmt.setInt(1, 1);
pstmt.setString(2, "Kevin");

// 执行查询
ResultSet rs = pstmt.executeQuery();

// 再次设置参数值
pstmt.setInt(1, 2);
pstmt.setString(2, "Russell");

// 再次执行查询
ResultSet rs = pstmt.executeQuery();

// ……

复制

Prepared Statement的监控

前文提到了 Prepared Statement 的常见使用误区，那么有没有一种简单快捷的办法确认我们对 Prepared Statement 的使用是否合理呢？PolarDB-PG 从 2.0.14.13.0 版本开始支持polar_feature_utils.polar_advisor_utils_stats_view
视图，通过该视图能够快速发现 Prepared Statement 相关的不恰当使用点，并能够辅助客户优化业务逻辑。

polar_feature_utils.polar_advisor_utils_stats_view
视图有多个指标，我们这里只关心 Prepared Statement 有关的指标。

  • SimpleProtocolExecCount 表示简单查询的执行次数，如果该指标偏高，说明有较多 SQL 未使用 Prepared Statement，可以考虑改用 Prepared Statement。

  • UnparameterizedStmtCount 表示未参数化的 Prepared Statement 数量，UnparameterizedStmtExecCount 表示未参数化的 Prepared Statement 执行次数。如果这两个指标有一个偏高，就说明有较多的 Prepared Statement 没有使用参数，应该考虑在其中设置参数。

  • UnnamedStmtCount 表示未命名的 Prepared Statement 数量，UnnamedStmtExecCount 表示未命名的 Prepared Statement 执行次数。如果这两个指标有一个偏高，则说明每次使用完 Prepared Statement 以后就将其 close，应该考虑减少 Prepared Statement 使用过程中的 close 次数，只有在真正使用结束时才进行 close。

下面给出了一个使用示例：

  • SimpleProtocolExecCount 的数值为 72，相较于其他几项指标是比较低的，说明可以改造为 Prepared Statement 的简单查询数量不多，基本可以忽略。

  • UnparameterizedStmtCount 和 UnparameterizedStmtExecCount 这两个指标显著高于其他几个指标，说明使用了较多的非参数化 Prepared Statement，需要重点进行参数化改造。

  • UnnamedStmtCount 和 UnnamedStmtExecCount 这两个指标的数值为 1000+，相对于简单查询的数量来说比较高，但是相比非参数化指标又不算太高，对于追求极致性能的场景可以考虑进行改造。

postgres=# select name, value from polar_feature_utils.polar_advisor_utils_stats_view;
                 name                 | value
--------------------------------------+-------
 SimpleProtocolExecCount              |    72
 UnnamedStmtCount                     |  1152
 UnnamedStmtExecCount                 |  1152
 UnparameterizedStmtCount             | 12071
 UnparameterizedStmtExecCount         | 30614

复制

polardbpostgresqlsql注入sql优化sql数据库
文章转载自PolarDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

明明想执行的SQL是DELETE、UPDATE，但为什么看到的是SELECT（FDW的实现原理解析）
小满未满、
376次阅读
2025-03-19 23:11:26
PostgreSQL初/中/高级认证考试（3.15）通过考生公示
开源软件联盟PostgreSQL分会
345次阅读
2025-03-20 09:50:36
Oracle SQL 执行计划分析与优化指南
Digital Observer
253次阅读
2025-04-01 11:08:44
IvorySQL 4.4 发布 - 基于 PostgreSQL 17.4，增强平台支持
通讯员
219次阅读
2025-03-20 15:31:04
命名不规范，事后泪两行
xiongcc
200次阅读
2025-03-13 14:26:08
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
199次阅读
2025-04-07 12:14:29
云和恩墨钟浪峰：安全生产系列之SQL优化安全操作
墨天轮编辑部
187次阅读
2025-03-31 11:08:20
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
169次阅读
2025-04-09 15:33:27
PG vs MySQL 执行计划解读的异同点
进击的CJR
141次阅读
2025-03-21 10:50:08
版本发布｜ IvorySQL 4.4 发布
IvorySQL开源数据库社区
129次阅读
2025-03-13 09:52:33