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

问题描述

MySQL JDBC Driver 将这两个属性定义为:

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?

推荐答案

首先，区分客户端和服务器准备的语句很重要.

First, it's important to distinguish between client and server prepared statements.

客户端准备好的语句是模拟"准备好的语句.这意味着 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()

会在日志中显示:

255 Query  select 42
255 Query  select 43

查询"表示，在协议层面，发送一个COM_QUERY命令，后面的语句字符串.

The "query" indicates that, on the protocol level, a COM_QUERY command is sent with the statement string following.

服务器准备好的语句是真"准备好的语句，这意味着查询文本被发送到服务器，被解析，并且占位符和结果信息被返回给客户端.这就是您在设置 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 ?")，它将返回相同的 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 JDBC 选项cachePrepStmts 将以这种方式缓存准备好的语句(客户端和服务器准备好的语句)以及缓存语句的可准备性".MySQL 中有一些语句在服务器端是不可准备的.如果驱动程序认为可能的话，它会尝试在服务器上准备一条语句，如果准备失败，则回退到客户端准备好的语句.由于需要往返服务器，因此此检查很昂贵.该选项还将缓存此检查的结果.

The MySQL JDBC 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 Driver中cachePrepStmts和useServerPrepStmts有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！