java 如何使用 MySQL 准备好的语句缓存？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/222019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use MySQL prepared statement caching?
提问by Chei
How do i take advantage of MySQL's ability to cache prepared statements? One reason to use prepared statements is that there is no need to send the prepared statement itself multiple times if the same prepared statement is to be used again.
我如何利用 MySQL 缓存准备好的语句的能力？使用预准备语句的一个原因是，如果要再次使用相同的预准备语句，则无需多次发送预准备语句本身。
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb" +
"?cachePrepStmts=true", "user", "pass");
for (int i = 0; i < 5; i++) {
PreparedStatement ps = conn.prepareStatement("select * from MYTABLE where id=?");
ps.setInt(1, 1);
ps.execute();
}
conn.close()
When running the above Java example I see 5 pairs of Prepare and Execute commands in the mysqld log file. Moving the ps assignment outside of the loop results in a single Prepare and 5 Execute commands of course. The connection parameter "cachePrepStmts=true" doesn't seem to make any difference here.
When running a similar program using Spring and Hibernate the number of Prepare commands sent (1 or 5) depends on whether the cachePrepStmts connection parameter is enabled. How does Hibernate execute prepared statements to take advantage of the cachePrepStmts setting? Is it possible to mimic this using pure JDBC?
I was running this on MySQL Server 4.1.22 and mysql-connector-java-5.0.4.jar
运行上述 Java 示例时，我在 mysqld 日志文件中看到 5 对 Prepare 和 Execute 命令。当然，将 ps 分配移到循环之外会导致单个 Prepare 和 5 个 Execute 命令。连接参数“cachePrepStmts=true”在这里似乎没有任何区别。
当使用 Spring 和 Hibernate 运行类似的程序时，发送的 Prepare 命令的数量（1 或 5）取决于是否启用了 cachePrepStmts 连接参数。Hibernate 如何执行准备好的语句以利用 cachePrepStmts 设置？是否可以使用纯 JDBC 来模拟这一点？
我在 MySQL Server 4.1.22 和 mysql-connector-java-5.0.4.jar 上运行它
回答by PEELY
You should prepare your statement only once, outside of the loop, and then bind the parameters in the loop. This is why prepared statements have bind parameters - so you can reuse the prepared statement.
您应该只在循环之外准备一次语句，然后在循环中绑定参数。这就是准备好的语句具有绑定参数的原因——因此您可以重用准备好的语句。
Hibernate does exactly this, treating all SQL as a prepared statement behind the scenes, though you can obviously abuse this if you use literal rather than bind parameters.
Hibernate 正是这样做的，将所有 SQL 视为幕后准备好的语句，但如果您使用文字而不是绑定参数，显然您可以滥用这一点。
回答by Keith Lawrence
Is it possible to mimic this using pure JDBC?
是否可以使用纯 JDBC 来模拟这一点？
Is this not in fact what you've done by moving your prepared statement call out of the loop?
这实际上不是您通过将准备好的语句调用移出循环所做的工作吗？
I may be misunderstanding the way the MySQL cache works, but does the log file necessarily report the cache's work? It may be that Spring or Hibernate has its own intermediate cache that checks the prepared statements against those sent earlier. It might be that that you're seeing when you run the program with Spring. That would mean doing a bit of tracing with your system to see if the mysqld log is just reporting the statements it's been sent, regardless of how it deals with them.
我可能误解了 MySQL 缓存的工作方式，但日志文件一定报告缓存的工作吗？可能 Spring 或 Hibernate 有自己的中间缓存，可以根据之前发送的语句检查准备好的语句。这可能是您在使用 Spring 运行程序时看到的。这意味着对您的系统进行一些跟踪，以查看 mysqld 日志是否仅报告它已发送的语句，而不管它如何处理它们。
回答by kosoant
You should prepare the statement outside the loop.
您应该在循环外准备语句。
Connection conn = DatabaseUtil.getConnection();
PreparedStatement stmtUpdate = conn.prepareStatement("UPDATE foo SET bar=? WHERE id = ?");
for(int id=0; id<10; id++){
stmtUpdate.setString(1, "baz");
stmtUpdate.setInt(2, id);
int rows = stmtUpdate.executeUpdate();
// Clear parameters for reusing the preparedStatement
stmtUpdate.clearParameters();
}
conn.close();
I don't know about mysql caching prepared statements, but this is the way JDBC prepared statements are supposed to be reused.
我不知道 mysql 缓存准备好的语句，但这是应该重用 JDBC 准备好的语句的方式。
回答by Sab
You also need to set the statement cache size on the connection instance. I assume the default cache size is 0. Hence nothing would be cached.
您还需要在连接实例上设置语句缓存大小。我假设默认缓存大小为 0。因此不会缓存任何内容。
回答by Sab
First, your PreparedStatement is recreated in the loop, so the JDBC driver is allowed to discard the prepared data. So you asked for the ugly behaviour, and so you got it.
首先，您的 PreparedStatement 在循环中重新创建，因此允许 JDBC 驱动程序丢弃准备好的数据。所以你要求丑陋的行为，所以你得到了它。
And then, PreparedStatement in MySQL are a chapter on its own. To have real caching, you have to request it explicitly via a connection property.
然后，MySQL 中的 PreparedStatement 本身就是一章。要进行真正的缓存，您必须通过连接属性显式请求它。
So you have to set the "cachePrepStmts" property to "true" to get caching on prepared statements. By default, that property is set to false.
因此，您必须将“cachePrepStmts”属性设置为“true”才能缓存准备好的语句。默认情况下，该属性设置为 false。
@see the MySQL manual for your MySQL version for details
@有关详细信息，请参阅 MySQL 版本的 MySQL 手册
