社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
JDBC作为JAVA访问数据库的一套规范与标准,统一了数据库操作的API,大大简化了程序开发工作。不过由于历史原因,MySQL对JDBC默认的实现与规范定义或者说其它数据库如Oracle并不一致,为了更完整记录这些差异,计划抽空写个系列,整理下这些可能会误解的常用功能。第一篇首先聊聊PreparedStatement。
数据库SQL执行过程包括以下阶段: 词法分析->语法分析->语义分析->执行计划优化->执行。【词法分析->语法分析】这两个阶段称之为硬解析。词法分析识别SQL中每个词,语法分析解析SQL语句是否符合(SQL92、99、方言等)语法,并得到一棵语法树。
其实基于SQL的架构设计,基本都有这样一个处理过程,TDDL、ShardingJDBC、MyCAT都如此,当然这些产品都相比于数据库,支持的关键词、语法都只是其子集。另外SQL解析器可基于Yacc、Lex、Antlr、Javacc等构建,当然如果对解析性能要更好要求,则需要进行一个纯手工编写的解析器,例如阿里的Druid中的SQL解析器,应用可基于Vistor模式进行使用。
Prepare SQL也叫预编译SQL、Prepared Statements或者Parameterized Statements,就是将这类SQL中的值用占位符?替代,可以视为将SQL语句模板化或者说参数化。预编译语句的优势在于归纳为:一次编译、多次运行,省去了解析优化等过程。
Prepare的出现就是为了优化硬解析的问题,Prepare在服务器端的执行过程如下:
【Prepare】 接收客户端带?的SQL, 硬解析得到语法树(stmt->Lex), 缓存在线程所在的PS cache中。此cache是一个HASH MAP. Key为stmt->id. 然后返回客户端stmt->id等信息。
【Execute】接收客户端stmt->id和参数等信息(客户端不需要再发SQL过来)。服务器根据stmt->id在PS cache中查找得到硬解析后的stmt, 并设置参数,就可以继续后面的优化和执行。
Prepare在execute阶段可以节省硬解析的时间。因此prepare适用于频繁执行的SQL。
Prepare的另一个作用是防止SQL注入,这个是纯客户端JDBC通过转义实现的。这也是一般更推荐使用PreparedStatement而不是Statement的主要理由。防SQL注入的具体实现可以参见MySQL驱动中com.mysql.jdbc.PreparedStatement.setString代码。
看完Prepare的功能原理后,我们看下JDBC操作MySQL时的PreparaStatement,
在com.mysql.jdbc.ConnectionImpl类中
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
//
PreparedStatement pStmt = null;
boolean canServerPrepare = true;
String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
}
if (this.useServerPreparedStmts && canServerPrepare) {
if (this.getCachePreparedStatements()) {
synchronized (this.serverSideStatementCache) {
pStmt = (com.mysql.jdbc.ServerPreparedStatement)this.serverSideStatementCache.remove(sql);
if (pStmt != null) {
((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false);
pStmt.clearParameters();
}
if (pStmt == null) {
try {
pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
this.database, resultSetType, resultSetConcurrency);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
}
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
if (sql.length() < getPreparedStatementCacheSqlLimit()) {
this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
}
} else {
throw sqlEx;
}
}
}
}
} else {
try {
pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
this.database, resultSetType, resultSetConcurrency);
pStmt.setResultSetType(resultSetType);
pStmt.setResultSetConcurrency(resultSetConcurrency);
} catch (SQLException sqlEx) {
// Punt, if necessary
if (getEmulateUnsupportedPstmts()) {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
} else {
throw sqlEx;
}
}
}
} else {
pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
}
return pStmt;
}
}
从以上源代码中,我们看到其实MySQL的Prepare竟然有两种,分为是客户端(JDBC4PreparedStatement)与服务器端(ServerPrepareStatement),根据应用连接参数设置(useServerPrepStmts),选择不同的PreparedStatement。另外还会根据缓存参数设置(cachePrepStmts),选择是否从缓存重获取解析对象,该缓存是针对连接的,这对于应用端使用连接池的场景是比较适用的。
分别设置不同参数,查看服务器端操作日志。
public static void selectWithClientPs(int count) throws SQLException{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
long begin = System.currentTimeMillis();
for(int i=0;i<count;i++){
PreparedStatement statement = connection.prepareStatement("select * from test where id= ?");
statement.setInt(1, i);
ResultSet resultSet = statement.executeQuery();
resultSet.close();
statement.close();
}
System.out.println("selectWithClientPs span time="+(System.currentTimeMillis()-begin) + "ms");
connection.close();
}
MySQL服务器执行日志:
Time Id Command Argument
181225 13:23:43 1 Connect root@localhost on test
1 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
1 Query SET NAMES latin1
1 Query SET character_set_results = NULL
1 Query SET autocommit=1
1 Query select * from test where id= 0
1 Query select * from test where id= 1
1 Query select * from test where id= 2
1 Quit
public static void selectWithClientPsAndCache(int count) throws SQLException{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?cachePrepStmts=true", "root", "123456");
long begin = System.currentTimeMillis();
for(int i=0;i<count;i++){
PreparedStatement statement = connection.prepareStatement("select * from test where id= ?");
statement.setInt(1, i);
ResultSet resultSet = statement.executeQuery();
resultSet.close();
statement.close();
}
System.out.println("selectWithClientPsAndCache span time="+(System.currentTimeMillis()-begin) + "ms");
connection.close();
}
MySQL服务器执行日志:
2 Connect root@localhost on test
2 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2 Query SET NAMES latin1
2 Query SET character_set_results = NULL
2 Query SET autocommit=1
2 Query select * from test where id= 0
2 Query select * from test where id= 1
2 Query select * from test where id= 2
2 Quit
public static void selectWithServerPs(int count) throws SQLException{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true", "root", "123456");
long begin = System.currentTimeMillis();
for(int i=0;i<count;i++){
PreparedStatement statement = connection.prepareStatement("select * from test where id= ?");
statement.setInt(1, i);
ResultSet resultSet = statement.executeQuery();
resultSet.close();
statement.close();
}
System.out.println("selectWithServerPs span time="+(System.currentTimeMillis()-begin) + "ms");
connection.close();
}
MySQL服务器执行日志:
3 Connect root@localhost on test
3 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
3 Query SET NAMES latin1
3 Query SET character_set_results = NULL
3 Query SET autocommit=1
3 Prepare select * from test where id= ?
3 Execute select * from test where id= 0
3 Close stmt
3 Prepare select * from test where id= ?
3 Execute select * from test where id= 1
3 Close stmt
3 Prepare select * from test where id= ?
3 Execute select * from test where id= 2
3 Close stmt
3 Quit
public static void selectWithServerPsAndCache(int count) throws SQLException{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true&cachePrepStmts=true", "root", "123456");
long begin = System.currentTimeMillis();
for(int i=0;i<count;i++){
PreparedStatement statement = connection.prepareStatement("select * from test where id= ?");
statement.setInt(1, i);
ResultSet resultSet = statement.executeQuery();
resultSet.close();
statement.close();
}
System.out.println("selectWithServerPsAndCache span time="+(System.currentTimeMillis()-begin) + "ms");
connection.close();
}
MySQL服务器执行日志:
4 Connect root@localhost on test
4 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */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, @@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_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
4 Query SET NAMES latin1
4 Query SET character_set_results = NULL
4 Query SET autocommit=1
4 Prepare select * from test where id= ?
4 Execute select * from test where id= 0
4 Execute select * from test where id= 1
4 Execute select * from test where id= 2
4 Quit
另外对这四种情况分别进行5000次查询,执行时间对比
selectWithClientPs span time=232601ms
selectWithClientPsAndCache span time=231493ms
selectWithServerPs span time=233999ms
selectWithServerPsAndCache span time=231262ms
结论:
另外还有两个参数
关于PrepareStament,MySQL还有一个参数max_prepared_stmt_count,默认值为16382。
mysql> show variables like "max_prepare%";
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_prepared_stmt_count | 16382 |
如果创建的PS数量超过这个数值,则会报以下错误:
ERROR 1461 (42000): Can’t create more than max_prepared_stmt_count statements
可根据需要需要调大该值,不过调整前应先检查应用是否正确关闭PreparedStament对象。
由上可见,出于历史版本的迭代,MySQL在实现JDBC规范接口时,有很多功能最开始并不算“真正”的实现,而是客户端类facade的设计,因此很多默认功能与常识并不一致,这些需要我们在实际使用中特别注意。
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!