当前位置： 首页 > news >正文

关于 PreparedStatement

news 2025/6/30 7:07:49

Mysql 层面的语法也支持 prepare
这个确实第一次见

  • PREPARE prepares a statement for execution (see Section 13.5.1, “PREPARE Statement”).
  • EXECUTE executes a prepared statement (see Section 13.5.2, “EXECUTE Statement”).
  • DEALLOCATE PREPARE releases a prepared statement (see Section 13.5.3, “DEALLOCATE PREPARE Statement”).
mysql> PREPARE stmt1 FROM 'SELECT * FROM words where id = ?';
Query OK, 0 rows affected (0.00 sec)
Statement preparedmysql> SET @i=1;
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt1 USING @i;
Empty set (0.01 sec)mysql> SET @i=2;
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt1 USING @i;
+----+------+
| id | word |
+----+------+
|  2 | 123  |
+----+------+
1 row in set (0.00 sec)mysql> deallocate prepare stmt1;
Query OK, 0 rows affected (0.00 sec)mysql> EXECUTE stmt1 USING @i;
ERROR 1243 (HY000): Unknown prepared statement handler (stmt1) given to EXECUTE

useServerPrepStmts=true

useServerPrepStmts

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

Default: false

Since version: 3.1.0

如果我们没有在 jdbc 参数中声明这个参数、即使我们在代码中使用了 PreparedStatement 实际上是毫无作用的

    try (PreparedStatement psts = conn.prepareStatement("delete from words where id = ?")) {psts.setInt(1, 1);psts.execute();stopwatch.stop();System.out.println("stopwatch = " + stopwatch.elapsed(TimeUnit.MILLISECONDS));psts.setInt(1, 10);psts.execute();}

抓包看看

在这里插入图片描述

mysql general log:

2020-05-04T13:06:07.883131Z	   15 Connect	root@localhost on test using TCP/IP
2020-05-04T13:06:07.885668Z	   15 Query	/* mysql-connector-java-8.0.16 (Revision: 34cbc6bc61f72836e26327537a432d6db7c77de6) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-05-04T13:06:07.905021Z	   15 Query	SET character_set_results = NULL
2020-05-04T13:06:07.929557Z	   15 Query	delete from words where id = 1
2020-05-04T13:06:07.934906Z	   15 Query	delete from words where id = 10
2020-05-04T13:06:07.940645Z	   15 Quit

当我们使用 useServerPrepStmts=true 之后

在这里插入图片描述在这里插入图片描述
在这里插入图片描述

log

2020-05-04T13:15:55.611149Z	   16 Connect	root@localhost on test using TCP/IP
2020-05-04T13:15:55.615655Z	   16 Query	/* mysql-connector-java-8.0.16 (Revision: 34cbc6bc61f72836e26327537a432d6db7c77de6) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-05-04T13:15:55.640086Z	   16 Query	SET character_set_results = NULL
2020-05-04T13:15:55.672345Z	   16 Prepare	delete from words where id = ?
2020-05-04T13:15:55.676355Z	   16 Execute	delete from words where id = 1
2020-05-04T13:15:55.681600Z	   16 Execute	delete from words where id = 10
2020-05-04T13:15:55.681949Z	   16 Close stmt
2020-05-04T13:15:55.687659Z	   16 Quit

cachePrepStmts

客户端 connection 级别缓存预编译的 sql

@Test
@SneakyThrows
public void testPreCompile() {String connectString = "jdbc:mysql://localhost/test?user=root&password=toor&useLocalSessionState=true&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true";Class.forName("com.mysql.jdbc.Driver").newInstance();try (Connection conn = DriverManager.getConnection(connectString)) {Stopwatch stopwatch = Stopwatch.createStarted();try (PreparedStatement psts = conn.prepareStatement("delete from words where id = ?")) {psts.setInt(1, 1);psts.execute();stopwatch.stop();System.out.println("stopwatch = " + stopwatch.elapsed(TimeUnit.MILLISECONDS));psts.setInt(1, 10);psts.execute();}// 上面的stmt关闭之后，再次执行try (PreparedStatement psts = conn.prepareStatement("delete from words where id = ?")) {psts.setInt(1, 100);psts.execute();}}// 上面的connection关闭之后，再次执行try (Connection conn = DriverManager.getConnection(connectString)) {try (PreparedStatement psts = conn.prepareStatement("delete from words where id = ?")) {psts.setInt(1, 66);psts.execute();}}}

在这里插入图片描述

2020-05-04T15:17:32.921041Z	   19 Connect	root@localhost on test using TCP/IP
2020-05-04T15:17:32.929561Z	   19 Query	/* mysql-connector-java-8.0.16 (Revision: 34cbc6bc61f72836e26327537a432d6db7c77de6) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-05-04T15:17:32.949986Z	   19 Query	SET character_set_results = NULL
2020-05-04T15:17:32.983173Z	   19 Prepare	delete from words where id = ?
2020-05-04T15:17:32.990498Z	   19 Execute	delete from words where id = 1
2020-05-04T15:17:32.997115Z	   19 Execute	delete from words where id = 10
2020-05-04T15:17:32.997566Z	   19 Reset stmt
2020-05-04T15:17:32.997725Z	   19 Execute	delete from words where id = 100
2020-05-04T15:17:33.003682Z	   19 Quit
2020-05-04T15:17:33.009206Z	   20 Connect	root@localhost on test using TCP/IP
2020-05-04T15:17:33.009643Z	   20 Query	/* mysql-connector-java-8.0.16 (Revision: 34cbc6bc61f72836e26327537a432d6db7c77de6) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2020-05-04T15:17:33.010569Z	   20 Query	SET character_set_results = NULL
2020-05-04T15:17:33.011244Z	   20 Prepare	delete from words where id = ?
2020-05-04T15:17:33.011475Z	   20 Execute	delete from words where id = 66
2020-05-04T15:17:33.114892Z	   20 Quit

我们直接来看看它是怎么存储的

在这里插入图片描述

在这里插入图片描述

既然是缓存、肯定涉及到缓存的个数以及单个缓存值的大小

prepStmtCacheSize & prepStmtCacheSqlLimit

prepStmtCacheSize 默认值为 25 注意这里是一个 connection 下最多缓存 25 个预编译的 Statement

prepStmtCacheSqlLimit 默认是 256 单纯就是 sql 的长度

在这里插入图片描述

源码

在这里插入图片描述

com.mysql.cj.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)

    @Overridepublic java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {synchronized (getConnectionMutex()) {checkClosed();//// FIXME: Create warnings if can't create results of the given type or concurrency//ClientPreparedStatement pStmt = null;boolean canServerPrepare = true;String nativeSql = this.processEscapeCodesForPrepStmts.getValue() ? nativeSQL(sql) : sql;// useServerPrepStmts 属性if (this.useServerPrepStmts.getValue() && this.emulateUnsupportedPstmts.getValue()) {canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);}if (this.useServerPrepStmts.getValue() && canServerPrepare) {// cachePrepStmts 缓存预编译 Statementif (this.cachePrepStmts.getValue()) {synchronized (this.serverSideStatementCache) {// 从缓存中获取pStmt = this.serverSideStatementCache.remove(new CompoundCacheKey(this.database, sql));if (pStmt != null) {// 强转为ServerPreparedStatement，清理参数，直接返回((com.mysql.cj.jdbc.ServerPreparedStatement) pStmt).setClosed(false);pStmt.clearParameters();}if (pStmt == null) {try {pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType,resultSetConcurrency);if (sql.length() < this.prepStmtCacheSqlLimit.getValue()) {((com.mysql.cj.jdbc.ServerPreparedStatement) pStmt).isCacheable = true;}pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);} catch (SQLException sqlEx) {// Punt, if necessaryif (this.emulateUnsupportedPstmts.getValue()) {pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);if (sql.length() < this.prepStmtCacheSqlLimit.getValue()) {this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);}} else {throw sqlEx;}}}}} else {try {pStmt = ServerPreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);} catch (SQLException sqlEx) {// Punt, if necessaryif (this.emulateUnsupportedPstmts.getValue()) {pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);} else {throw sqlEx;}}}} else {pStmt = (ClientPreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);}return pStmt;}}

close 的时候会重新放回去缓存

// com.mysql.cj.jdbc.ServerPreparedStatement#close@Override
public void close() throws SQLException {JdbcConnection locallyScopedConn = this.connection;if (locallyScopedConn == null) {return; // already closed}synchronized (locallyScopedConn.getConnectionMutex()) {if (this.isCached && isPoolable() && !this.isClosed) {clearParameters();this.isClosed = true;// 重新缓存起来this.connection.recachePreparedStatement(this);return;}this.isClosed = false;realClose(true, true);}
}// com.mysql.cj.jdbc.ConnectionImpl#recachePreparedStatement@Overridepublic void recachePreparedStatement(JdbcPreparedStatement pstmt) throws SQLException {synchronized (getConnectionMutex()) {if (this.cachePrepStmts.getValue() && pstmt.isPoolable()) {synchronized (this.serverSideStatementCache) {Object oldServerPrepStmt = this.serverSideStatementCache.put(new CompoundCacheKey(pstmt.getCurrentCatalog(), ((PreparedQuery<?>) pstmt.getQuery()).getOriginalSql()),(ServerPreparedStatement) pstmt);if (oldServerPrepStmt != null && oldServerPrepStmt != pstmt) {((ServerPreparedStatement) oldServerPrepStmt).isCached = false;((ServerPreparedStatement) oldServerPrepStmt).setClosed(false);((ServerPreparedStatement) oldServerPrepStmt).realClose(true, true);}}}}}

关于 PSCache

这个是关于 druid 的

争议较大的是 mysql 的时候是否开启 PSCache、下面 github 的 issue 是处于 open 状态

// com.alibaba.druid.pool.DruidPooledConnection#closePoolableStatement
public void closePoolableStatement(DruidPooledPreparedStatement stmt) throws SQLException {PreparedStatement rawStatement = stmt.getRawPreparedStatement();if (holder == null) {return;}if (stmt.isPooled()) {try {rawStatement.clearParameters();} catch (SQLException ex) {this.handleException(ex, null);if (rawStatement.getConnection().isClosed()) {return;}LOG.error("clear parameter error", ex);}}PreparedStatementHolder stmtHolder = stmt.getPreparedStatementHolder();stmtHolder.decrementInUseCount();// holder.isPoolPreparedStatements 对应上面配置的开关if (stmt.isPooled() && holder.isPoolPreparedStatements() && stmt.exceptionCount == 0) {// 放入缓存池子中holder.getStatementPool().put(stmtHolder);stmt.clearResultSet();holder.removeTrace(stmt);stmtHolder.setFetchRowPeak(stmt.getFetchRowPeak());stmt.setClosed(true); // soft set close} else if (stmt.isPooled() && holder.isPoolPreparedStatements()) {// the PreparedStatement threw an exceptionstmt.clearResultSet();holder.removeTrace(stmt);// 开启了PSCache但是这个stmt抛出过异常，直接从缓存中移除holder.getStatementPool().remove(stmtHolder);} else {try {//Connection behind the statement may be in invalid state, which will throw a SQLException.//In this case, the exception is desired to be properly handled to remove the unusable connection from the pool.stmt.closeInternal();} catch (SQLException ex) {this.handleException(ex, null);throw ex;} finally {holder.getDataSource().incrementClosedPreparedStatementCount();}}
}

https://qsli.github.io/2020/05/05/cache-prep-stmts/

https://github.com/alibaba/druid/issues/2273

http://www.lryc.cn/news/435012.html

相关文章：

  • 漫谈设计模式 [9]：外观模式
  • 多进程编程
  • 7-Zip压缩包如何添加密码，加密后如何取消
  • HarmonyOS---应用测试概述
  • 密码学---真题演练
  • 时间日期工具类
  • linux中vim常用命令大全
  • 计算机的错误计算（八十九）
  • 深入理解java并发编程之aqs框架
  • ubuntu配置tftp、nfs
  • Sklearn的datasets模块与自带数据集介绍
  • css 个人喜欢的样式 速查笔记
  • C/C++ let __DATE__ format to “YYYY-MM-DD“
  • git如何灵活切换本地账号对应远程github的两个账号
  • Python中实现函数的递归调用
  • Multisim使用手册
  • 线程的六种状态
  • 全球热门剪辑软件大搜罗
  • swagger-bootstrap-ui页面空白，也没报错
  • 15.2 JDBC数据库编程2
  • Spark数据介绍
  • 【0基础】制作HTML网页小游戏——贪吃蛇（附详细解析）
  • Vscode python无法转到函数定义
  • Python中的上下文管理器（with语句）及其作用
  • CTK框架(八):服务追踪
  • [针对于个人用户] 显卡与计算卡性能对比表
  • 2024年智能录屏解决方案全攻略，从桌面到云端
  • CentOS7.9下snmp v3 inform搭建监控端
  • 水库大坝安全监测方案，双重守护，安全无忧
  • yolov8实现图片验证码识别