在一次执行之后关闭PreparedStatement --这是一个设计缺陷吗？

Question

我查看了不同的地方，并听到了许多可疑的声明，从PreparedStatement到Statement，无论是在任何地方，都应该比Statement更好，即使只是为了性能方面的好处；一直到声称PreparedStatement应该只用于批处理语句，而不是其他任何东西。

然而，在我所关注的(主要是在线)讨论中，似乎有一个盲点。让我提出一个具体的情况。

我们有一个带有DB连接池的EDA设计的应用程序.事件来了，有些需要持之以恒，有些则不需要坚持。有些是人为生成的(例如，每X分钟更新一次/重置一次)。有些事件是按顺序来处理的，但是其他类型的事件(也需要持久化)可以(并且将)并发处理。

除了那些人工生成的事件之外，需要持久性的事件是如何到达的，这是没有结构的。

这个应用程序是在很久以前(大约2005年)设计的，支持多个DBMSes。典型的事件处理程序(需要持久化)：

• 从池中获得连接
• 准备sql语句
• 执行准备语句
• 处理结果集，如果适用，请关闭它
• 封闭式准备报表
• 如果必要的话，准备一份不同的陈述，并以同样的方式处理。
• 返回到池的连接

如果事件需要批处理，则只准备语句一次，并使用addBatch/executeBatch方法。这是一个明显的性能效益，这些情况与这个问题无关。

最近，我收到了一种意见，即准备(解析)语句、执行语句一次并关闭一次的整个想法本质上是对PreparedStatement**，的误用，无论使用的是服务器语句还是客户端准备的语句，以及典型的DBMSes (Oracle、DB2、MSSQL、MySQL、DBMSes等等)，都没有带来任何性能上的好处。甚至不会将这样的语句提升到准备好的语句缓存(或者至少它们的默认JDBC驱动程序/数据源不会).**。

此外，我还必须在MySQL上测试开发环境中的某些场景，而且连接器/J使用分析器似乎也符合这一想法。对于所有未批处理的准备语句，调用close()打印：

PreparedStatement created, but used 1 or fewer times. It is more efficient to prepare statements once, and re-use them many times

由于前面概述的应用程序设计选择，让PreparedStatement实例缓存保存连接池中任何事件使用的每个SQL语句听起来是个糟糕的选择。

有人能进一步阐述这一点吗？是逻辑“准备-执行(一旦)-close”有缺陷的，本质上是气馁的？

P.S.为连接器/J显式指定useUsageAdvisor=true和cachePrepStmts=true，并使用useServerPrepStmts=true或useServerPrepStmts=false，对于每个非批处理的SQL语句，在对PreparedStatement实例调用close()时，仍然会引起关于效率的警告。

Answer 1

逻辑准备-执行一次关闭有缺陷，本质上是气馁吗？

我不认为这本身就是个问题。给定的SQL语句需要在某一时刻“准备”，无论是显式地(使用PreparedStatement)还是“动态”(使用语句)。如果我们对只执行一次的事情使用PreparedStatement而不是语句，可能会产生更多的开销，但是所涉及的开销不太可能很大，特别是如果您引用的声明是正确的：

典型的DBMSes (Oracle、DB2、MSSQL、MySQL、Derby等)甚至不会将这样的语句提升到准备好的语句缓存(或者至少它们的默认JDBC驱动程序/数据源不会)。

令人气馁的是这样一种模式：

for (int thing : thingList) {
    PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ");
    ps.setInt(1, thing);
    ps.executeUpdate();
    ps.close();
}

因为PreparedStatement只使用过一次，而且一次又一次地准备相同的SQL语句。(即使这样，如果SQL语句及其执行计划确实被缓存的话，这也不是什么大不了的事情。)更好的方法是

PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ");
for (int thing : thingList) {
    ps.setInt(1, thing);
    ps.executeUpdate();
}
ps.close();

..。或者更好，用“用资源来尝试”.

try (PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ")) {
    for (int thing : thingList) {
        ps.setInt(1, thing);
        ps.executeUpdate();
    }
}

注意，即使不使用批处理，这也是正确的。SQL语句仍然只准备了一次并多次使用。

Answer 2

正如其他人已经指出的，最昂贵的部分是解析数据库中的语句。一些数据库系统(这在很大程度上依赖于DB --我将主要为Oracle发言)可能会受益，如果该语句已经在共享池中进行了解析。(在甲骨文术语中，这被称为软解析，它比硬解析更便宜--一个新语句的解析)。即使只使用准备好的语句一次，也可以从软解析中获益。

因此，重要的任务是给数据库一个重用语句的机会。典型的反例是根据Hibernate中的集合处理IN列表。以语句结尾，如

 .. FROM T WHERE X in (?,?,?,  … length based on the size of the collection,?,? ,?,?)

如果集合的大小不同，则不能重用此语句。

获得运行应用程序产生的SQL查询频谱的一个很好的起点是(通过Oracle) V$SQL视图。使用连接池用户筛选PARSING_SCHEMA_NAME，并检查SQL_TEXT和执行数。

应避免出现两种极端情况：

• 在查询文本中传递参数(ID)(这是众所周知的)和
• 对于不同的访问路径重用语句。

后者的一个例子是使用提供的参数执行对表的有限部分的索引访问的查询，而如果没有参数，则应该处理所有记录(全表扫描)。在这种情况下，创建两个不同的语句无疑是没有问题的(因为对这两个语句的解析会导致不同的执行计划)。

Answer 3

PreparedStatements更可取，因为不管您是否以编程方式创建一个查询，都需要一个；在内部，每次运行查询时数据库都会创建一个查询--通过编程创建一个查询只会给您一个句柄。每次创建和丢弃PreparedStatement并不会增加使用Statement的开销。

数据库需要大量的精力来创建一个(语法检查、解析、权限检查、优化、访问策略等等)。在随后的执行中，重用可以绕过这一努力。

与其丢弃它们，不如尝试以一种可以重用的方式编写查询(如忽略空输入参数)：

where someCol = coalesce(?, someCol)

因此，如果将参数设置为null (即“未指定的”)，则条件成功)

或者，如果每次都必须构建查询，那么将对PreparedStatements的引用保存在生成的查询是关键的Map中，并在遇到问题时重用它们。使用WeakHashMap映射实现，以防止内存耗尽。