Mysql 层面的语法也支持 prepare
这个确实第一次见
PREPAREprepares a statement for execution (see Section 13.5.1, “PREPARE Statement”).EXECUTEexecutes a prepared statement (see Section 13.5.2, “EXECUTE Statement”).DEALLOCATE PREPAREreleases 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 prepared
mysql> 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)
@Override
public 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 缓存预编译 Statement
if (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 necessary
if (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 necessary
if (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
@Override
public 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 exception
stmt.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();
}
}
}
436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
