你真的懂PreparedStatement吗？
发布于:2024-7-15
最后更新: 2024-7-15
次查看
MySQL
URL
date
AI summary
slug
status
tags
summary
type

PreparedStatement是什么

PreparedStatement是java.sql包下面的一个接口，看看JavaDoc对于它是如何描述的：
An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
大致意思就是：PreparedStatement代表一个”预编译好的”SQL Statement。这个Statement可以被多次执行，用以提升效率
一般地，我们通过Java来执行SQL时不可避免的会用到Statement或者PreparedStatement：
    // 忽略一些资源释放问题
    private static void usePreparedStatemnt(String url, String username, String password) throws Exception {
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = connection
                .prepareStatement("select * from `test` where id = ?;");
        preparedStatement.setLong(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    private static void useStatemnt(String url, String username, String password) throws Exception {
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from `test` where id = 1;");

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

PreparedStatement和Statement对比

PreparedStatement有点类似模板的概念，把一条SQL的静态部分和动态部分拆分了开来，这样带来的好处：
  1. 支持参数动态化
  1. 提高了可读性

两个误区

PreparedStatement能提升性能

根据JavaDoc的描述，PreparedStatement貌似是可以提升性能的。但是PreparedStatement只是个接口，底层的实现逻辑都在具体的数据库驱动中。是不是真的能提升性能还得看connection.prepareStatement()方法怎么实现呢

PreparedStatement能有效防止SQL注入

和上面的误区存在同样的问题，这两个误区产生的原因都是没有基于一定的前提来看待PreparedStatement，下面我们以MySQL的官方驱动为例来看看这两个结论是不是都是对的

MySQL下的PreparedStatement

PreparedStatement在MySQL驱动中的实现分为client模式和server模式。主要是通过数据源url上的useServerPrepStmts参数指定的。默认为false，使用client模式。

client模式下的PreparedStatement

在client模式下，prepareStatement方法并不会与MySQL服务端交互，会在本地把SQL解析为ParseInfo，解析过程这里就不展开了，ParseInfo 里最核心的成员变量是一个二维的字节数组byte[][] staticSql 。staticSql[][]里保存的就是解析后的sql信息，第一维就是按照?的位置做分割，第二维则是表示对应的字符。
💡
为什么第二维不直接用String来表示，而是用byte[]？ 主要是空间上的考虑。一个String最少占用的空间是很大的，1个字符的String在64 bit的JVM开启压缩指针的前提下会占用32Byte的空间，未开启状态会到40Byte。JDK 1.6以前的版本会在这个基础上分别再加8Byte存放offset、count值，改为byte[]数组后会小一些，单个字符的byte[]在64bit下可以降低到24字节（其实也挺大的，数组除了对象头部还有一个位置保存数组的长度，加上1个字符的1~4字节按照8字节对齐到24字节）
而在execute 时，则通过staticSql[][]配合上对应的参数，替换成最终的SQL，最后把替换之后的SQL发送给MySQL服务端。
    // com.mysql.cj.AbstractPreparedQuery#fillSendPacket(com.mysql.cj.QueryBindings<?>)
    public <M extends Message> M fillSendPacket(QueryBindings<?> bindings) {
        
	        // 省略部分代码
          byte[][] staticSqlStrings = this.parseInfo.getStaticSql();
          for (int i = 0; i < bindValues.length; i++) {
              bindings.checkParameterSet(i);

              sendPacket.writeBytes(StringLengthDataType.STRING_FIXED, staticSqlStrings[i]);

              if (bindValues[i].isStream()) {
                  streamToBytes(sendPacket, bindValues[i].getStreamValue(), true, bindValues[i].getStreamLength(), useStreamLengths);
              } else {
                  sendPacket.writeBytes(StringLengthDataType.STRING_FIXED, bindValues[i].getByteValue());
              }
          }

          sendPacket.writeBytes(StringLengthDataType.STRING_FIXED, staticSqlStrings[bindValues.length]);

          return (M) sendPacket;
    }
我们测试一下在client模式下，上述两个结论是否成立，测试代码如下：
public class ClientPrepTest {

    public final static String url = "jdbc:mysql://127.0.0.1:3306/test?useConfigs=beautiful&useServerPrepStmts=true&cachePrepStmts=false";
    public final static String username = "root";
    public final static String password = "123456";

    public static void main(String[] args) throws Exception {
	    Connection connection = DriverManager.getConnection(url, username, password);
			PreparedStatement preparedStatement = connection
			       .prepareStatement("select * from `test` where id = ?");
			preparedStatement.setString(1, "1' AND '1' = '1");
			preparedStatement.executeQuery();
			connection.close();
		}
	}
  1. 理论上性能提升是不存在的，或者说没有明显性能提升的点。相比Statement，PreparedStatement还多了一步本地的参数解析替换（不成立）
      2. 后面我们会通过实验来测试，测试代码详见附录
  1. SQL注入这个通过1个测试用例，简单抓包，发现在驱动这一层做了转义（成立）
      2. notion image
相关源码如下，是在设置参数（setXxx）的时候做的转义：
   // com.mysql.cj.ClientPreparedQueryBindings#setString
   public void setString(int parameterIndex, String x) {
	   // 省略部分代码
	   StringBuilder quotedString = new StringBuilder(x.length() + 2);
        quotedString.append('\'');
        quotedString.append(x);
        quotedString.append('\'');

        byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(quotedString.toString())
                : StringUtils.getBytes(quotedString.toString(), this.charEncoding);
        setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR);
    }

server模式下的PreparedStatement

我们通过下面的测试代码来感受一下：
public class ServerPrepTest {

    public final static String url = "jdbc:mysql://127.0.0.1:3306/test?useConfigs=beautiful&useServerPrepStmts=true&cachePrepStmts=false";
    public final static String username = "root";
    public final static String password = "123456";

    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection(url, username, password);
        test2(connection);
        connection.close();
    }

    private static void test2(Connection connection) throws Exception {
        PreparedStatement preparedStatement = connection
                .prepareStatement("select * from `test` where id = ?;");
        preparedStatement.setLong(1, 1);
        preparedStatement.executeQuery();
        preparedStatement.close();

        preparedStatement = connection.prepareStatement("select * from `test` where id = ?;");
        preparedStatement.setLong(1, 2);
        preparedStatement.executeQuery();

        preparedStatement.setLong(1, 3);
        preparedStatement.executeQuery();

        preparedStatement.close();
    }
}
运行上面的测试程序，抓包来看看：
notion image
server模式下的PrepareStatement ，调用它的prepareStatement方法都会和MySQL服务端交互。整个查询过程从原来的Query Statement变成了Prepare Statement + Execute Statement + Close Statement。我们再看看具体的包数据：

Prepare Statement

请求包：
notion image
响应包：
notion image
Prepare主要是发送一条带?的SQL给MySQL服务端，MySQL执行预编译(或者叫硬解析)动作，硬解析得到语法树(stmt->Lex)，缓存在线程所在的prepareStatement cache中。此cache是一个HashMap，Key为StatementId，最后返回这个StatementId，指代这条预编译结果。
这里介绍一下，MySQL执行一条SQL的过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是基本相同的。

Execute Statement

notion image
Execute主要是客户端发送StatementId和参数等信息。注意这里不需要再发sql过去。服务器会根据StatementId在prepareStatement cache中查找得到硬解析后的stmt，并设置参数，就可以继续后面的优化和执行了。

Close Statement

notion image
PreparedStatement.close()操作也会触发与MySQL服务端交互，关闭刚才硬解析好的那条PreparedStatement

MySQL General Log

再来结合MySQL服务端的日志看一下：
115 Prepare   select * from `test` where id = ?
115 Execute   select * from `test` where id = 1
115 Close stmt
115 Prepare   select * from `test` where id = ?
115 Execute   select * from `test` where id = 2
115 Execute   select * from `test` where id = 3
115 Close stmt
115 Quit
这里可以看出来，与client模式相比：对于一个简单的SQL执行，需要与数据库多两次交互。整体上对于性能来说，在大多数简单查询的场景下，肯定是弊大于利的。
除非是Prepare之后，对同一个PreparedStatement执行很多次Execute，才有可能提升性能。存在这种可能性吗？试着打开参数cachePrepStmts=true，再试试
notion image
是不是惊喜的发现，只有一次Prepare Statement了？没有Close Statement了？但是怎么多了一种Reset Statement，这个是干嘛的？

MySQL Bug

我们注意到官网上有这么一段话：
notion image
看官网这个描述，这种情况不是只会出现在有大字段存在的场景吗，我这个测试表就一个ID字段，怎么会出现这种情况？
只能从源码里再去找找答案了(下面是8.0.16的connector源码)：
    /** ServerPreparedStatement **/
    public void clearParameters() {
        synchronized (checkClosed().getConnectionMutex()) {
            ((ServerPreparedQuery) this.query).clearParameters(true);
        }
    }

    /** ServerPreparedQuery **/
    public void clearParameters(boolean clearServerParameters) {
        boolean hadLongData = false;

        if (this.queryBindings != null) {
            hadLongData = this.queryBindings.clearBindValues();

            // 步骤A
            this.queryBindings.setLongParameterSwitchDetected(clearServerParameters && hadLongData ? false : true);
        }

        // 步骤B
        if (clearServerParameters && hadLongData) {
            // 执行Reset Statement
            serverResetStatement();
        }
    }
clearParameters这一步是在ServerPreparedStatement.close()阶段被触发的，还有个前提是打开了PreparedStatement缓存。很容易理解，因为PreparedStatement需要重用，所以每次用完需要把之前的参数信息都清空掉。上面的代码显示了在清空参数的过程中，会判定有没有大字段(blob, clob)，如果有的话hadLongData变量会被赋值成true。
上面的代码我们分成步骤A和步骤B来看，对于步骤B来说，逻辑上是存在大字段且需要 clearServerParameters时，才发送Reset Statement，这个毫无疑问是false。但是对于步骤A来说，由于不存在大字段，所以会将LongParameterSwitchDetected这个参数的值设置成true。这个时候会触发另外一个逻辑：
    /** ServerPreparedQuery **/
    public NativePacketPayload prepareExecutePacket() {

        ServerPreparedQueryBindValue[] parameterBindings = this.queryBindings.getBindValues();

        if (this.queryBindings.isLongParameterSwitchDetected()) {
            // Check when values were bound
            boolean firstFound = false;
            long boundTimeToCheck = 0;

            for (int i = 0; i < this.parameterCount - 1; i++) {
                if (parameterBindings[i].isStream()) {
                    if (firstFound && boundTimeToCheck != parameterBindings[i].boundBeforeExecutionNum) {
                        throw ExceptionFactory.createException(
                                Messages.getString("ServerPreparedStatement.11") + Messages.getString("ServerPreparedStatement.12"),
                                MysqlErrorNumbers.SQL_STATE_DRIVER_NOT_CAPABLE, 0, true, null, this.session.getExceptionInterceptor());
                    }
                    firstFound = true;
                    boundTimeToCheck = parameterBindings[i].boundBeforeExecutionNum;
                }
            }

            // Okay, we've got all "newly"-bound streams, so reset server-side state to clear out previous bindings

            serverResetStatement();
        }
        // 省略部分代码
    }
这个逻辑是在ServerPrepareStatement.prepare()时触发的，只要LongParameterSwitchDetected这个参数为true，那么就是触发调用Reset命令。我感觉这个参数是用来解决官网说到的最后一点问题的：一个字段从大字段变成了正常字段的场景。然而驱动里的逻辑是：只要没有大字段，就会Reset，缺少了从有->没有的这个过程转变信息。感觉是驱动的BUG
于是翻看了下5.1.47版本的驱动代码，发现老的逻辑是没有问题的，且正如我所猜想的：
   // getBinding在setParam的时候触发调用
   protected BindValue getBinding(int parameterIndex, boolean forLongData) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {

            if (this.parameterBindings.length == 0) {
                throw SQLError.createSQLException(Messages.getString("ServerPreparedStatement.8"), SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
                        getExceptionInterceptor());
            }

            parameterIndex--;

            if ((parameterIndex < 0) || (parameterIndex >= this.parameterBindings.length)) {
                throw SQLError.createSQLException(Messages.getString("ServerPreparedStatement.9") + (parameterIndex + 1)
                        + Messages.getString("ServerPreparedStatement.10") + this.parameterBindings.length, SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
                        getExceptionInterceptor());
            }

            if (this.parameterBindings[parameterIndex] == null) {
                this.parameterBindings[parameterIndex] = new BindValue();
            } else {
                if (this.parameterBindings[parameterIndex].isLongData && !forLongData) {
                    this.detectedLongParameterSwitch = true;
                }
            }

            return this.parameterBindings[parameterIndex];
        }
    }
    private void clearParametersInternal(boolean clearServerParameters) throws SQLException {
        boolean hadLongData = false;

        if (this.parameterBindings != null) {
            for (int i = 0; i < this.parameterCount; i++) {
                if ((this.parameterBindings[i] != null) && this.parameterBindings[i].isLongData) {
                    hadLongData = true;
                }

                this.parameterBindings[i].reset();
            }
        }

        if (clearServerParameters && hadLongData) {
            serverResetStatement();

            this.detectedLongParameterSwitch = false;
        }
    }
也有人在2022年提了这个bug（我发现的时候应该是是2018年），最终在8.3.0版本fix了
notion image

和PreparedStatement相关的参数

  • useServerPrepStmts
  • cachePrepStmts
  • prepStmtCacheSize
  • prepStmtCacheSqlLimit
useServerPrepStmts前面我们讲过，用来控制是使用ClientPreparedStatement还是ServerPreparedStatement。默认false
cachePrepStmts用来控制是否缓存对应的PreparedStatement，无论是ClientPreparedStatement还是ServerPreparedStatement。默认false
prepStmtCacheSize用来限制缓存的PreparedStatement的数量，connection维度。默认25
prepStmtCacheSqlLimit用来限制可缓存的PreparedStatement的原始sql的长度。默认256

附录

性能测试代码

为了测试ClientPreparedStatement的性能，最好可以避免网络的干扰。下面我们直接不发送具体的请求，在本地测试：
public class PerformanceTest {

    // 这里可以通过cachePrepStmts参数开启/关闭本地prepareStatement缓存，但是实际上开了之后，性能有提升，但还是比Statement要差，可能是因为缓存的是模板，参数替换这步还是要做的
    public final static String url = "jdbc:mysql://127.0.0.1:3306/test?useServerPrepStmts=false&cachePrepStmts=false";
    public final static String username = "root";
    public final static String password = "123456";
    public final static int iteration = 1000000;

    public static void main(String[] args)
            throws SQLException, NoSuchFieldException, IllegalAccessException {
        Connection connection = DriverManager.getConnection(url, username, password);
        Field sessionField = connection.getClass().getDeclaredField("session");
        sessionField.setAccessible(true);
        NativeSession nativeSession = (NativeSession) sessionField.get(connection);
        NativeSession session = new NativeSession(null, new DefaultPropertySet()) {
            @Override
            public <T extends Resultset> T execSQL(Query callingQuery, String query, int maxRows,
                    NativePacketPayload packet, boolean streamResults,
                    ProtocolEntityFactory<T, NativePacketPayload> resultSetFactory, String catalog,
                    ColumnDefinition cachedMetadata, boolean isBatch) {
                return (T) new ResultSetImpl(new OkPacket(), null, null);
            }

            @Override
            public boolean isClosed() {
                return nativeSession.isClosed();
            }

            @Override
            public ServerSession getServerSession() {
                return nativeSession.getServerSession();
            }

            @Override
            public NativePacketPayload getSharedSendPacket() {
                return nativeSession.getSharedSendPacket();
            }

            @Override
            public NativeProtocol getProtocol() {
                return nativeSession.getProtocol();
            }
        };
        System.out.println(
                Arrays.stream(NativeSession.class.getDeclaredFields()).map(field -> field.getName())
                        .collect(Collectors.toList()));
        Field isClosedField = NativeSession.class.getDeclaredField("isClosed");
        isClosedField.setAccessible(true);
        isClosedField.set(session, false);
        sessionField.set(connection, session);
        testPreparedStatement(connection, true);
        testStatement(connection, true);
        for (int i = 0; i < 1; i++) {
            System.out.println("------------round:" + ++i + "------------");
            testPreparedStatement(connection, false);
            testStatement(connection, false);
        }

    }

    public static void testPreparedStatement(Connection connection, boolean warmup)
            throws SQLException {
        if (warmup) {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("select * from `test` where id = ?");
            preparedStatement.setInt(1, 1);
            preparedStatement.executeQuery();
            preparedStatement.close();
            return;
        }
        long start = System.currentTimeMillis();
        for (int i = 0; i < iteration; i++) {
            PreparedStatement preparedStatement = connection
                    .prepareStatement("select * from `test` where id = ?");
            preparedStatement.setInt(1, 1);
            preparedStatement.executeQuery();
            preparedStatement.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("testPreparedStatement cost:" + (end - start) + " ms");
    }

    public static void testStatement(Connection connection, boolean warmup) throws SQLException {
        if (warmup) {
            Statement statement = connection.createStatement();
            statement.executeQuery("select * from `test` where id = 1");
            statement.close();
            return;
        }
        long start = System.currentTimeMillis();
        for (int i = 0; i < iteration; i++) {
            Statement statement = connection.createStatement();
            statement.executeQuery("select * from `test` where id = 1");
            statement.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("testStatement cost:" + (end - start) + " ms");
    }
}

测试结果

100w次循环(cachePrepStmts=false)测试结果： testPreparedStatement cost:6829 ms testStatement cost:2047 ms 100w次循环(cachePrepStmts=true)测试结果： testPreparedStatement cost:4375 ms testStatement cost:2104 ms

测试结论

这里可以看出ClientPreparedStatement相对Statement性能反而更差，开启cachePrepStmts能提升一定的性能，当然这里本来就是空间换时间，并且这里的场景单一，只有一个SQL，命中率100%，并不具备参考性。也可以看出其实简单sql的解析成本也很低。

结论

个人不建议开启useServerPrepStmts和cachePrepStmts。因为：
  1. cache是connection维度的，同一条SQL在不同的connection中会被放大N倍，如果考虑分库分表的场景，那问题会更加严重。
  1. 实际使用过程中，我们可能碰到不少in语句，而对于in语句来说，in子句里的元素数量不同，那么SQL也是不同的，同一个场景就有可能产生上百种SQL。此时要么花大量内存来存储，要么就是牺牲命中率。
  1. 开启cache可能会造成内存占用较多，容易引发系统不稳定，对于MySQL Server也是。
  1. 从我们自己的实践来看，客户端和服务端去解析SQL的成本都还好，并且我们不允许使用复杂SQL，SQL的复杂度对于解析时间应该也有一定的影响。

参考

  1. 预编译语句(Prepared Statements)介绍，以MySQL为例
  1. MySQL如何对SQL做prepare预处理（解决IN查询SQL预处理仅能查询出一条记录的问题）
  1. MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用的源码分析
  1. MySQL JDBC为什么都不开启PreparedStatement Cache
 
相关文章
2024-11-07
MySQL异步复制和半同步复制
2024-10-31
MySQL 并行复制原理及演进
2024-10-17
MySQL OnlineDDL发展历程及各算法介绍
2024-07-29
MySQL 连接阶段
2024-07-24
记一次mysql连接问题
2024-07-18
一文说透批量SQL
一文说透批量SQL惊！一条SQL竟有如此威力