MySQL JDBC PreparedStatement

傷城~ 2023-05-29 11:44 19阅读 0赞

JDBC作为JAVA访问数据库的一套规范与标准，统一了数据库操作的API，大大简化了程序开发工作。不过由于历史原因，MySQL对JDBC默认的实现与规范定义或者说其它数据库如Oracle并不一致，为了更完整记录这些差异，计划抽空写个系列，整理下这些可能会误解的常用功能。第一篇首先聊聊PreparedStatement。

## MySQL JDBC PreparedStatement ##

##### Prepare SQL的产生原因与实现原理 #####

数据库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。
    
    ![image][]

> Prepare的另一个作用是**防止SQL注入**，这个是纯客户端JDBC通过转义实现的。这也是一般更推荐使用PreparedStatement而不是Statement的主要理由。防SQL注入的具体实现可以参见MySQL驱动中com.mysql.jdbc.PreparedStatement.setString代码。

##### MySQL驱动中PrepareStament的实现逻辑 #####

看完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），选择是否从缓存重获取解析对象，该缓存是针对连接的，这对于应用端使用连接池的场景是比较适用的。

##### 不同参数对应的Prepare区别 #####

分别设置不同参数，查看服务器端操作日志。

1.  使用客户端PreparedStatement，不开启缓存

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

1.  使用客户端PreparedStatement，开启缓存

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

1.  使用服务器端PreparedStatement，不开启缓存

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

1.  使用服务器端PreparedStatement，开启缓存

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

结论：

1.  使用客户端PreparedStatement，无论是否开启缓存，服务器端都不使用prepare，即硬解析时间不会减少，而且客户端缓存执行时间差别并不大。
2.  使用服务器端PreparedStatement，如果开启缓存，则会使用prepare，硬解析仅为一次；如果不开启缓存，每次PreparedStatement进行close后，都需要重新进行prepare。
3.  从测试效果来看，**如果SQL本身比较简单，服务器端prepare并没有太大优势，使用客户端prepare即可**，如果SQL较为复杂，则可尝试开启服务器端prepare，网上也有文章做过测试称可提高7%性能，不过笔者本地测试提升不到1%，测试数据实际跟SQL的复杂程度有关。

另外还有两个参数

*  prepStmtCacheSize参数，控制缓存的条数，MySQL驱动默认是25，实际使用时一般会根据需要调整大些；
 *  prepStmtCacheSqlLimit参数，控制长度多大的SQL可以被缓存，MySQL驱动默认是256，实际使用时如果SQL较大，可调整大些。

关于PrepareStament，MySQL还有一个参数max\_prepared\_stmt\_count，默认值为16382。

如果创建的PS数量超过这个数值，则会报以下错误：

> ERROR 1461 (42000): Can’t create more than max\_prepared\_stmt\_count statements

可根据需要需要调大该值，不过调整前应先检查应用是否正确关闭PreparedStament对象。

由上可见，出于历史版本的迭代，MySQL在实现JDBC规范接口时，有很多功能最开始并不算“真正”的实现，而是客户端类facade的设计，因此很多默认功能与常识并不一致，这些需要我们在实际使用中特别注意。

[image]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8zNzUxMjM4LWU2NTEwOTNjMGVmZjcxYWEucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy83MDAvZm9ybWF0L3dlYnA?x-oss-process=image/format,png