三月 26, 2024
摘要:在本教程中,您将学习如何在 PostgreSQL 中缓存查询计划。
目录
介绍
通常,在应用程序中编写 SQL 有两种风格。请看以下示例:
String sql = "SELECT first_name, last_name FROM employees"
+ " WHERE employee_id = " + employeeId;
ResultSet rs = conn.executeQuery(sql);
这种风格的 SQL 语句会发生硬解析。硬解析是基于 SQL 语句来构建执行计划。这是很大的开销;它要检查 SQL 的所有部分;考虑所有索引;考虑所有连接顺序等。硬解析非常耗费资源。
我们也可以使用绑定参数,如下:
PreparedStatement st = conn.prepareStatement(
"SELECT first_name, last_name FROM employees"
+ " WHERE employee_id = ?");
st.setInt(1, employeeId);
ResultSet rs = st.executeQuery();
绑定参数(也称为动态参数或绑定变量)是将数据传递到数据库的另一种方法。您无需将值直接放入 SQL 语句中,只需使用占位符?
,并使用单独的 API 调用提供实际值。绑定变量让优化器对实际值“不可见”,从而生成一个通用计划,以后可以缓存和重用。
使用绑定参数允许 PostgreSQL 以软解析的方式来执行一条语句。软解析会搜索、查找和使用缓存的执行计划。它会做一些小的检查,比如访问权限,但执行计划可以按原样重复使用。这是一个相当快的操作。
参数化查询
PostgreSQL 没有共享的查询计划缓存,但它为预备语句提供了一个可选的查询计划缓存。这意味着开发人员可以选择使用,启用或关闭查询计划缓存的预备语句。但请注意,当预备好的语句关闭时,缓存会被删除。
下面列举说明了如何在各种语言中使用此功能。
-
C
原生的 C API 提供了 PQexecParams 函数,该函数允许在预备期间使用绑定参数(而不是
PQprepare
)。 -
Java
PostgreSQL JDBC 驱动程序通过
PGStatement
和PGConnection
上面的非标准方法setPrepareThreshold
,来控制服务器端预备行为。请注意,默认设置为 5,这意味着前 4 次执行将会在预备期间实际使用绑定参数,而后面的执行则不会。计数器对每个
PreparedStatement
实例会重新开始计数。 -
Python
Python 的 Cursor.execute() 接受绑定参数作为可选参数。如果提供这些值,它会在规划期间使用。有关详细信息,请参阅文档。
总结
显式使用绑定参数,可同时解决三个问题:
(1)SQL注入。
(2)执行计划缓存的命中率。
(3)能够自行控制以利用直方图统计。
提示:
如果列值分布不均匀,则列的直方图统计最有用。
对于分布均匀的列,通常将非重复值的数量除以表中的行数就足够了。此方法在使用绑定参数时也有效。
动态 SQL 会自动为条件过滤器提供优化的执行计划。
执行计划重用也有一些缺点。执行计划缓存使用 SQL 字符串作为键值。相同的 SQL 字符串,相同的执行计划。这意味着优化器必须规划好重用。它不能去假定用于后续执行的绑定值的任何内容。智能逻辑方法使这一点变得非常明显。在规划重用时,以下条件无法被优化掉:
(last_name = :name OR :name IS NULL)
查询使用命名的绑定变量以提高可读性。所有可能的过滤表达式都被静态编码在语句中。每当不需要一个过滤条件时,您只需使用NULL
来替代一个检索项:它会通过OR
逻辑禁用该条件。
除此以外,优化器在规划重用时无法使用列的直方图统计。