查询参数数据类型和性能

最近，我可以帮助客户解决一个“神秘”的性能问题。问题是查询参数类型选择不当，导致查询性能不佳。在处理了这个客户的案例后，我意识到查询参数的数据类型的含义并没有被普遍理解，这就是我决定写这篇文章的原因。

什么是查询参数?

在SQL中，我们经常会遇到不同常量值的查询，例如

SELECT val FROM large WHERE id = 42;
SELECT val FROM large WHERE id = 4711;
SELECT val FROM large WHERE id = 1001;

将它们视为同一查询的不同实例

SELECT val FROM large WHERE id = $1;

这里，$1是任意值的占位符。这样的占位符称为查询参数（也称为绑定变量）。当您执行查询时，您必须提供一个实际参数值作为参数的参数。

使用占位符有以下几个优点：

·数据库可以一次解析和优化语句，并使用不同的参数值多次重用执行计划以加快处理速度 (阅读本文以了解在 PostgreSQL 中是如何完成的)

·将查询和参数分开可以防止SQL 注入的可能性，这是对数据库应用程序的主要威胁。

在PostgreSQL中支持的查询参数

PostgreSQL前端/后端协议 提供了两种发送 SQL 语句的方式:

·简单查询协议将完整的SQL 语句作为单个字符串发送

·扩展查询协议将SQL 语句及其参数分开：

o“Prepare”消息发送带有占位符$1的语句

o“Bind” 消息发送参数值

o“Execute”消息执行查询

请注意，您只能将查询参数用于常量。不能对表、列或模式名称等标识符使用参数。此外，参数仅支持SELECT、INSERT、UPDATE和DELETE。

预处理语句

当准备语句时，可以为其指定名称，以便使用不同的参数值执行相同的语句。这称为预处理语句，每个数据库客户机API都为此提供支持。

在Java中使用JDBC，其工作原理如下：

java.sql.PreparedStatement stmt =
    conn.prepareStatement("SELECT * FROM large WHERE id = ?");
stmt.setLong(1, 42L);
java.sql.ResultSet rs = stmt.executeQuery();

占位符总是?, 使用set*`方法来指定第一个参数和值。

在PostgreSQL中，还可以在SQL中使用预处理语句。PREPARE创建一个

prepared statement语句，然后EXECUTE执行它：

PREPARE stmt(bigint) AS
   SELECT val FROM large WHERE id = $1;


EXECUTE stmt(42);

查询参数数据类型和类型转换

您可能已经注意到，在上述两种情况下，都存在与参数相关联的数据类型：Java中的long和SQL中的bigint。但是，您不必指定数据类型。然后PostgreSQL将尝试推断数据类型本身。这种“非类型化值”在PostgreSQL中由数据类型“unknown”表示。

但PostgreSQL还将考虑对未知以外的数据类型进行类型转换。例如，PostgreSQL中没有用于比较bigint和numeric的相等运算符：

SELECT o.oprname
FROM pg_operator AS o
   JOIN pg_amop AS ao
      ON o.oid = ao.amopopr
   JOIN pg_am AS a
      ON a.oid = ao.amopmethod
WHERE * an equality operator as used in B-tree indexes */
      ao.amopstrategy = 3 AND a.amname = 'btree'
  AND (o.oprleft, o.oprright)
      = ('bigint'::regtype, 'numeric'::regtype);


 oprname 
═════════
(0 rows)

不过，您可以将bigint与numeric进行比较，因为PostgreSQL会隐式地将bigint转换为numeric，因为后者是数字的首选数据类型。您可以在文档中找到控制这些转换的确切规则。这些规则相当复杂，因为PostgreSQL支持函数和运算符的重载。

查询参数类型对性能影响的示例

为了说明这一点，让我们考虑一下这个简单的示例：

CREATE TABLE large (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   val double precision NOT NULL
);


INSERT INTO large (val)
SELECT random()
FROM generate_series(1, 100000);


VACUUM (ANALYZE) large;

然后我们编写一个Java程序，用上面的代码片段查询数据库，但我们用三种不同的方式设置参数。使用[auto_explain](https://www.postgresql.org/docs/current/auto-explain.html) 扩展，我们在每种情况下捕获服务器上的执行计划。

使用integer作为参数类型

参数被设置为

stmt.setInt(1, 42);

执行计划结果是：

Query Text: SELECT * FROM large WHERE id = $1
Index Scan using large_pkey on large  (...)
                        (actual time=0.013..0.013 rows=1 loops=1)
  Index Cond: (id = 42)
  Buffers: shared hit=3

这很好，因为使用setInt将参数标记为integer类型，并且有一个相等运算符来比较integer和bigint。

使用unknown作为参数类型

参数被设置为java.sql.Types.OTHER

stmt.setObject(1, 42);

执行计划结果是：

Query Text: SELECT * FROM large WHERE id = $1
Index Scan using large_pkey on large  (...)
                        (actual time=0.005..0.006 rows=1 loops=1)
  Index Cond: (id = 42)
  Buffers: shared hit=3

这同样有效，因为类型为unknown的参数被推断为具有与id相同的类型，即bigint。

使用numeric 作为参数类型

参数设置为

stmt.setBigDecimal(1, java.math.BigDecimal.valueOf(42));

得到的执行计划是

Query Text: SELECT * FROM large WHERE id = $1
Seq Scan on large  (...)
                   (actual time=0.012..11.123 rows=1 loops=1)
  Filter: ((id)::numeric = '42'::numeric)
  Rows Removed by Filter: 99999
  Buffers: shared hit=935

令人惊讶的是，PostgreSQL 使用了顺序扫描，并且性能要差得多。原因是JDBC驱动映射java.math.BigDecimal到numeric，所以参数有数据类型numeric。正如我们在上面看到的，bigint没有和numeric相等的运算符，因此 PostgreSQL 将两者都转换为numeric。这在上面的执行计划中清楚的看到。由于主键索引是在“ id”上定义的，而不是在“ id::numeric”上，所以在这种情况下不能使用它。

如何避免查询参数数据类型出现问题

综上所述，我们可以吸取以下教训：

·理想情况下，为查询参数选择正确的数据类型。 

在上面的示例中，这意味着使用setLong，因为 JDBC 驱动程序讲数据类型long映射到bigint。

·如有疑问，请选择一个无类型参数（类型unknown）并让 PostgreSQL 推断正确的数据类型。

有时这两种策略都不起作用，因为您的宿主语言中没有与 PostgreSQL 数据类型匹配的数据类型——而 PostgreSQL 猜错了。一个例子是这个查询：

SELECT id, name FROM person
WHERE birthday <= current_timestamp - $1;

我们想提供一个像“1 year”或“ 1-0”这样的间隔。Java 没有间隔的数据类型，所以我们必须发送一个unknown参数。但是，PostgreSQL 推断我们的意思是运算符

timestamp with time zone - timestamp with time zone

而不是

timestamp with time zone - interval

所以我们最终会收到错误消息，“1 year”不是有效的时间戳。在这种情况下，解决方案是在语句中添加显式类型转换：

SELECT id, name FROM person
WHERE birthday <= current_timestamp - CAST ($1 AS interval);

所以我们必须用第三点来修改上面的列表：

·如果自动类型解析失败，则向语句添加显式类型转换。

结论

很容易忽略查询参数的数据类型对查询性能很重要的事实。为了避免问题，要么不指定类型让 PostgreSQL 做正确的选择事情，要么明确指定正确的类型。

点击“阅读原文”查看原文内容