MySQL JDBC驱动程序中的cachePrepStmts和useServerPrepStmts有什么区别 [英] What's the difference between cachePrepStmts and useServerPrepStmts in MySQL JDBC Driver

问题描述

MySQL JDBC驱动程序将这两个属性定义为：

The MySQL JDBC Driver defines these two properties as:

• useServerPrepStmts - 如果服务器支持它们，则准备语句？

• useServerPrepStmts - Use server-side prepared statements if the server supports them?

cachePrepStmts - 如果驱动程序缓存客户端预准备语句的PreparedStatements的解析阶段，则服务器端的
适用性的检查准备和服务器端准备
语句本身？

cachePrepStmts - Should the driver cache the parsing stage of PreparedStatements of client-side prepared statements, the "check" for suitability of server-side prepared and server-side prepared statements themselves?

客户端准备语句一种重用 PreparedStatements 对象的方法？

Is the client-side prepared statement a way to reuse the PreparedStatements objects?

如果 useServerPrepStmts 已启用，因为MySQL没有

If the useServerPrepStmts is enabled, what is exactly being cached, since MySQL doesn't have an execution plan cache anyway?

推荐答案

首先，重要的是

客户端准备的语句被模拟准备语句。这意味着SQL语句字符串在客户端上被标记化，并且在将语句发送到服务器以执行之前，任何占位符都被替换为文字值。每次执行时都会向服务器发送一条完整的SQL语句。您可以使用常规日志来调查此工作原理。例如

Client prepared statements are "emulated" prepared statements. This means that the SQL statement string is tokenized on the client side and any placeholders are replaced with literal values before sending the statement to the server for execution. A complete SQL statement is sent to the server on every execution. You can use the general log to investigate how this works. e.g.

以下代码：

ps=conn.prepareStatement("select ?")
ps.setInt(1, 42)
ps.executeQuery()
ps.setInt(1, 43)
ps.executeQuery()

会显示在日志中：

would show in the log:

255 Query  select 42
255 Query  select 43

< 表示在协议级别上，发送带有以下语句字符串的 COM_QUERY 命令。

服务器准备语句是true预准备语句，这意味着查询文本发送到服务器，解析并将占位符和结果信息返回给客户端。这是当设置 useServerPrepStmts = true 时得到的。语句字符串只能通过 COM_STMT_PREPARE 调用发送到服务器一次（记录此处）。每个执行通过发送一个 COM_STMT_EXECUTE 与准备的语句句柄和字面值来替换占位符。

Server prepared statements are "true" prepared statements meaning that the query text is sent to the server, parsed, and placeholder and result information is returned to the client. This is what you get when setting useServerPrepStmts=true. The statement string is only ever sent to the server one time with a COM_STMT_PREPARE call (documented here). Each execution is performed by sending a COM_STMT_EXECUTE with the prepared statement handle and the literal values to substitute for the placeholders.

为了与客户端准备示例进行比较，我们可以使用类似的代码块（但是此时已启用服务器预处理语句）：

To contrast with the client prepared example, we can use a similar block of code (but this time with server prepared statements enabled):

ps2=conn2.prepareStatement("select ?")
ps2.setInt(1, 42)
ps2.executeQuery()
ps2.setInt(1, 43)
ps2.executeQuery()

日志将显示：

254 Prepare    select ?
254 Execute    select 42
254 Execute    select 43

语句在执行前准备好。日志对我们有利，并显示执行的完整语句，但事实上，只有占位符值从客户端发送到服务器为每个执行。

You can see that the statement is prepared before being executed. The log is doing us a favor and showing the complete statement for the execution but, in fact, only the placeholder values are sent from client to server for each execution.

许多连接池会跨连接使用缓存预处理语句，这意味着如果调用 conn.prepareStatement（select？ / code>，它将在使用相同语句字符串的连续调用上返回相同的 PreparedStatement 实例。

Many connection pools will cache prepared statements across uses of a connection meaning that if you call conn.prepareStatement("select ?"), it will return the same PreparedStatement instance on successive calls with the same statement string. This is useful to avoid preparing the same string on the server repeatedly when connections are returned to the pool between transactions.

MySQL选项 cachePrepStmts

The MySQL option cachePrepStmts will cache prepared statements in this way (both client and server prepared statements) as well as cache the "preparability" of a statement. There are some statements in MySQL that are not preparable on the server side. The driver will try to prepare a statement on the server if it believes it to be possible and, if the prepare fails, fall back to a client prepared statement. This check is expensive due to requiring a round-trip to the server. The option will also cache the result of this check.

希望这有助于。

这篇关于MySQL JDBC驱动程序中的cachePrepStmts和useServerPrepStmts有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！