如何使用MySQL准备的语句缓存？ [英] How to use MySQL prepared statement caching?

问题描述

我如何利用MySQL缓存准备好的语句的能力？
使用准备好的语句的一个原因是，如果再次使用相同的准备好的语句，则不需要多次发送准备好的语句本身。

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.

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()

运行上述Java示例I请参阅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上运行这个

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

推荐答案

您还需要在连接实例上设置语句高速缓存大小。我假设默认缓存大小为0.因此，不会缓存。

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.

这篇关于如何使用MySQL准备的语句缓存？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！