关于 PreparedStatement

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