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
with useServerPrepStmts=true
useServerPrepStmts
Use server-side prepared statements if the server supports them?
Default: false
Since version: 3.1.0
jdbc的连接参数加上useServerPrepStmts=true:
首选需要编译statement,请求如下:
后面执行的时候,只传了对应的statement id 1和占位符对应的值 1,大大减少了网络的传输:
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和useServerPrepStmts同时打开
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?
// 上面的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(); } }
}
wireshark抓包:
mysql的general log:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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
连接关闭之后,重新执行同样的sql,发现又触发了编译。
缓存大小的限制
下面两个参数,分别限制了能够缓存多少个和最大sql的长度
prepStmtCacheSize
prepStmtCacheSize
If prepared statement caching is enabled, how many prepared statements should be cached?
Default: 25
Since version: 3.0.10
prepStmtCacheSqlLimit
If prepared statement caching is enabled, what’s the largest SQL the driver will cache the parsing for?
2020-05-04T15:21:34.187732Z 21 Connect root@localhost on test using TCP/IP 2020-05-04T15:21:34.202166Z 21 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:21:34.240492Z 21 Query SET character_set_results = NULL 2020-05-04T15:21:34.316483Z 21 Prepare delete from words where id = ? 2020-05-04T15:21:34.333444Z 21 Execute delete from words where id = 1 2020-05-04T15:21:34.344075Z 21 Execute delete from words where id = 10 2020-05-04T15:21:34.351728Z 21 Reset stmt 2020-05-04T15:21:34.352101Z 21 Execute delete from words where id = 100 2020-05-04T15:21:34.356117Z 21 Reset stmt 2020-05-04T15:21:34.356407Z 21 Execute delete from words where id = 66
stmtHolder.setFetchRowPeak(stmt.getFetchRowPeak()); stmt.setClosed(true); // soft set close } elseif (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(); } } }