MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用-mysql チュートリアル-php.cn

关于预编译（PrepareStatement），对于所有的JDBC驱动程序来讲，有一个共同的功能，就是“ 防止SQL注入 ”，类似Oracle还有一种“ 软解析 ”的概念，它非常适合应用于OLTP类型的系统中。在JDBC常见的操作框架中，例如ibatis、jdbcTemplate这些框架对JDBC操

关于预编译（PrepareStatement），对于所有的JDBC驱动程序来讲，有一个共同的功能，就是“防止SQL注入”，类似Oracle还有一种“软解析”的概念，它非常适合应用于OLTP类型的系统中。

在JDBC常见的操作框架中，例如ibatis、jdbcTemplate这些框架对JDBC操作时，默认会走预编译（jdbcTemplate如果没有传递参数，则会走createStatement），这貌似没有什么问题。不过在一个应用中发现了大量的预编译对象导致频繁GC，于是进行了源码上的一些跟踪，写下这篇文章，这里分别从提到的几个参数，以及源码中如何应用这几个参数来说明。

看看有那些参数：

MySQL JDBC是通过其Driver的connenct方法获取到连接，然后可以将连接参数设置在JDBC URL或者Properties中，它会根据这些参数来创建一个Connection，简单说来就是将这些参数解析为K-V结构，交给Connection的对象来解析，Connection会将它们解析为自己所能识别的许多属性中，这个属性的类型为：ConnectionProperty，当然有许多子类来实现不同的类型，例如：BooleanConnectionProperty、IntegerConnectionProperty是处理不同参数类型的。

这些参数会保存在Connection对象中（在源码中，早期的版本，源码的类名就叫：com.mysql.jdbc.Connection，新版本的叫做：com.mysql.jdbc.ConnectionImpl，抽象了接口与实现类，这里统一称Connection的对象）；具体是保存在这个Connection的父类中，这里将几个与本题相关的几个参截取出来，如下所示：

private BooleanConnectionProperty cachePreparedStatements = new BooleanConnectionProperty(
            "cachePrepStmts", //$NON-NLS-1$
            false,
            Messages.getString("ConnectionProperties.cachePrepStmts"), //$NON-NLS-1$
            "3.0.10", PERFORMANCE_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$
private IntegerConnectionProperty preparedStatementCacheSize = new IntegerConnectionProperty(
            "prepStmtCacheSize", 25, 0, Integer.MAX_VALUE, //$NON-NLS-1$
            Messages.getString("ConnectionProperties.prepStmtCacheSize"), //$NON-NLS-1$
            "3.0.10", PERFORMANCE_CATEGORY, 10); //$NON-NLS-1$
private IntegerConnectionProperty preparedStatementCacheSqlLimit = new IntegerConnectionProperty(
            "prepStmtCacheSqlLimit", //$NON-NLS-1$
            256,
            1,
            Integer.MAX_VALUE,
            Messages.getString("ConnectionProperties.prepStmtCacheSqlLimit"), //$NON-NLS-1$
            "3.0.10", PERFORMANCE_CATEGORY, 11); //$NON-NLS-1$
private BooleanConnectionProperty detectServerPreparedStmts = new BooleanConnectionProperty(
            "useServerPrepStmts", //$NON-NLS-1$
            false,
            Messages.getString("ConnectionProperties.useServerPrepStmts"), //$NON-NLS-1$
            "3.1.0", MISC_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$

ログイン後にコピー

找到这个通常要看看获取它的方法名，显然实际执行的时候，一般用方法来获取，而且这里的类型是private，也就是子类不可见，直接访问如果不通过变通手段访问不到;也许我们搞Java的第一眼看到的就是就是属性名的get方法嘛，有些时候MySQL这个该死的就是不按照常规思路走，例如它对属性：“detectServerPreparedStmts”的获取方法是：“getUseServerPreparedStmts()”，如下图：

MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用

好吧，不关注它的屌丝做法了，来继续关注正题。

来看看PrepareStatement初始化与编译过程：

要预编译，自然是通过Connection去做的，默认调用的预编译参数是这样一个方法：

public java.sql.PreparedStatement prepareStatement(String sql)
        throws SQLException {
    return prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY,
            java.sql.ResultSet.CONCUR_READ_ONLY);
}

ログイン後にコピー

这个方法貌似还看不出什么东西，但是可以稍微留意下发现默认值是什么，继续往下走，走到一个重载方法中，这个重载方法body部分太长了，看起来费劲，说起来难，经过梳理，我将它简化一下，如下图所示：

MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用

这里将逻辑分解为两个大板块：一个为com.mysql.jdbc.ServerPreparedStatement，一个是默认的，反过来讲就是如果是服务器端的Statement，处理类的类名一眼就能看出来。

那么什么时候会走服务器端的PrepareStatement呢？服务器端的PrepareStatement与普通的到底有什么区别呢？先看第一个问题，以下几条代码是进入逻辑的关键：

boolean canServerPrepare = true;
String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
   canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
}
if (this.useServerPreparedStmts && canServerPrepare) {
   ....使用ServerPrepareStatement
}

ログイン後にコピー

也就是判定逻辑是基于“useServerPreparedStmts”、“canServerPrepare”这两个参数决定的，而“useServerPreparedStmts”我们可以将对应的参数设置为true即可，参数对应到那里呢？在第一个参数列表图中，就对应到：“detectServerPreparedStmts”，而在JDBC URL上需要设置的是：“useServerPrepStmts”，定义如

private BooleanConnectionProperty detectServerPreparedStmts = new BooleanConnectionProperty(
            "useServerPrepStmts", //$NON-NLS-1$
            false,
            Messages.getString("ConnectionProperties.useServerPrepStmts"), //$NON-NLS-1$
            "3.1.0", MISC_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$

ログイン後にコピー

而另一个参数canServerPrepare并非默认，它虽然被初始化设置了true，但是getEmulateUnsupportedPstmts()这个方法跟踪进去也会发现默认是true（当然可以通过设置参数将其设置为false），对应到代码中，参数canServerPrepare的值将由方法：canHandleAsServerPreparedStatement(String)来决定，跟踪进去会发现，首先只考虑“SELECT、UPDATE、DELETE、INSERT、REPLACE”几种语法规则，也就是如果不是这几种就直接返回false了。另外会对参数Limit后面7位做一个判定是否有逗号、?这些符号，如果有这些就返回false了，对于这7位一直很纳闷，因为LIMIT后面7位最多包含一个占位符，而分页最少2个。

这里说明这些就只想说明，“并不一定将useServerPrepStmts设置为true，就一定会采用服务器端的PrepareStatement”；这假设已经采用了服务器端的，它做了什么呢？

pStmt = ServerPreparedStatement.getInstance(this, nativeSql,
                                    this.database, resultSetType, resultSetConcurrency);

ログイン後にコピー

这个是代码中的关键，跟踪进去，你会发现它这个动作，会向服务器端发送SQL，很明显的，这里还没有执行SQL，只是预编译，就已经将SQL交给服务器端，那么后面只需要拿到相应的状态标识给服务器端参数即可。

另外，这个里面还有一层是：getCachePreparedStatements()，这个参数就是对应到上图中设置的“cachePrepStmts”，它的定义如下所示：

private BooleanConnectionProperty cachePreparedStatements = new BooleanConnectionProperty(
            "cachePrepStmts", //$NON-NLS-1$
            false,
            Messages.getString("ConnectionProperties.cachePrepStmts"), //$NON-NLS-1$
            "3.0.10", PERFORMANCE_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$

ログイン後にコピー

它将首先预判定是否将SQL cache到一个内存区域中，然后再内部创建ServerPrepareStatement，如果创建失败则也调用client的，并且在失败的时候put到serverSideStatementCheckCache这个里面（这里可以看到出来是基于SQL的K-V结构，K肯定是SQL了，Value等下来看），成功的值发现做了一个：

if (sql.length() <br><span>这个判定语句很明显是判定SQL长度的，也就是SQL长度低于某个值就设置这个参数，这个</span><span>getPreparedStatementCacheSqlLimit()</span><span>就是来自第一个图中的：preparedStatementCacheSqlLimit参数，JDBC URL参数是：</span><span>prepStmtCacheSqlLimit</span><span>，它的默认值是256，如下所示：</span><br><p><span><span><span><span></span></span></span></span></p><pre class="brush:php;toolbar:false">private IntegerConnectionProperty preparedStatementCacheSqlLimit = new IntegerConnectionProperty(
            "prepStmtCacheSqlLimit", //$NON-NLS-1$
            256,
            1,
            Integer.MAX_VALUE,
            Messages.getString("ConnectionProperties.prepStmtCacheSqlLimit"), //$NON-NLS-1$
            "3.0.10", PERFORMANCE_CATEGORY, 11); //$NON-NLS-1$

ログイン後にコピー

但是这个isCache仅仅是设置一个boolean值，那里做了cache呢？没有简单做任何cache，仅仅看到是失败的会cache，它到底在哪里有用呢，跟踪到内部会在Statement发生close的时候有用：

public synchronized void close() throws SQLException {
    if (this.isCached && !this.isClosed) {
        clearParameters();
        this.isClosed = true;
        this.connection.recachePreparedStatement(this);
        return;
    }              
    realClose(true, true);
}

ログイン後にコピー

这个：recachePreparedStatement()方法最终也会调用：serverSideStatementCache来讲编译信息设置进去，也就是这个cache始终在客户端，而服务器端PrepareStatement只是代表了谁来编译这个SQL语句的问题。

也许对clientPrepareStatement感兴趣，就去看看它的代码，同样这个代码很长，我也简单简化了下逻辑如下图所示：

MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用

这个逻辑基本与ServerPrepareStatement内部的逻辑差不多，唯一的区别就是这个是显式做了LRU算法，而这个LRU是一是一种最简单的最近最久未使用方式，将最后一个删掉，将现在这个写进去，它同样也有getCachePreparedStatements()、getPreparedStatementCacheSqlLimit()来控制是否做cache操作，也同样用了一个K-V结构来做cache，这个K-V结构，通过Connection的初始化方法：initializeDriverProperties(Properties)间接调用：createPreparedStatementCaches()完成初始化，可以看到他会被初始化为一个HashMap结构，较早的版本会创建多个类似大小的对象出来。

好了，现在来看问题，一个HashMap不足以造成多少问题，因为有LRU队列来控制长度，但是看代码中你会发现它没控制并行处理，HashMap是非线程安全的，那么为啥MySQL JDBC没出问题呢？因为你会发现这个HashMap完全绑定到Connection对象上，成为Connection对象的一个属性，连接池分配的时候没见过会将一个Connection同时分配给两个请求的，因此它将并发的问题交给了连接池来解决，自己认为线程都是安全的，反过来，如果你自己去并行同一个Connection可能会有问题。

继续回到问题上来，每个Connection都可能cache几十个上百个Statement对象，那么一个按照线上数据源的配置，也就配置5~10个是算比较大的了，也就最多上千个对象，JVM配置都是多少G的空间，几千个对象能造成什么问题？

于是我们来看他cache了什么，主要是普通的PrepareStatement，里面的代码发现编译完后返回了一个ParseInfo类型对象，然后将它作为Value写入到HashMap中，它是一个PrepareStatement的内部类，它的定义如下所示：

class ParseInfo {
     char firstStmtChar = 0;
    boolean foundLimitClause = false;
    boolean foundLoadData = false;
    long lastUsed = 0;
    int statementLength = 0;
    int statementStartPos = 0;
    byte[][] staticSql = null;
}

ログイン後にコピー

我们可以搬着手指头算下，对象头部、属性、padding大致占用的空间（当然是在64bit），发现也不大，而最关键的是这个二维数组，byte[][]staticSql，它占用多大，经过代码跟踪我们发现它与占位符的个数相关，也就是参数中的“?”个数，这个个数将决定第一维的大小，而SQL中的每个字节将填写到数组的第二维。

Java中没有绝对的二维数组，都是通过一维数组虚拟出来的，而第一维本身也是一个引用数组，占用的空间自然很大，参数个数自然和业务表相关，至少会有“增、删、改、查”，查和删其实占位符较少，而相应的业务系统写操作是十分多的，因此参数个数用15~20个来估算不算过分，而SQL长度用200来估算也不过分，通过简单估算，这个空间将会是原来SQL的2~3倍甚至于更多，但是也不至于有问题呀？

再回头看看，一个HashMap里面的Key、Value、next、hash几个会形成一个新的对象，而Key是SQL，自然会占用SQL的空间大小，Vaue是好几倍的SQL空间，其余的再抛开HashMap本身数组的利用率极低外，这里可能SQL的宽度会上K的占用，不过算起来还是不对，因为就算是1000K，也只有1m,再放大几倍也只有几M的空间。

想不通了，后来一个小情况得到了提醒，那就是数据库是分布式的，分布式数据库的连接池配置底层会针对每一个访问过的数据库建立初始化大小的连接数，那么自然的，这个数据应当乘以数据库的个数，该应用存在上百个数据库，那么自然的1M到几M的空间，就上升到一百到几百M，不过也不至于有这么大的问题，因为基本内存都用G来衡量的，再细探，数据库还存在读写分流，也就是部分流量会分配到备库上，而一个数据库会有多个备库，自然的读流量只要访问过也会在备库上建立同样的Connection，即使你用得不多，那么自然的空间还要乘以一套库的个数，例如乘以4，那么这个空间就完全有可能占用得非常大，理论上这些数据就是这样来的了。

回头再来看看ParseInfo到底在什么时候用，普通的prepareStatement（即客户端的），到底是怎么与服务器端通信的，我们用一个常见的executeQuery查询语句来看代码，它内部通过一个叫：Buffer sendPacket = fillSendPacket();这个方法获取到要与MySQL服务器端通信的package的Buffer，它的代码是这样的：

protected Buffer fillSendPacket() throws SQLException {
    return fillSendPacket(this.parameterValues, this.parameterStreams,
                this.isStream, this.streamLengths);
}

ログイン後にコピー

发现又调用了一个该死的重载方法，但是知道了传入的是参数列表parameterValues，而重载方法中，这个方法入口参数的名字变成了：batchedParameterStrings，说明重载方法是兼容批处理的，只是单个语句传入的参数可能在里面只循环一次而，跟踪进去，发现一段很重要的循环的地方是这样的：

for (int i = 0; i <br><p>这个循环看到每次都会将staticSqlStrings拼接一次，然后再拼接一个参数，这个就是一个<strong>byte[][]</strong>格式，而它的赋值就是来源于<strong>ParseInfo</strong>，在方法：PrepareStatement中的initializeFromParseInfo()中有相应的说明。</p><p><br></p><p>也就是说他用的就是ParseInfo中的内容，而那个内容分析过，与占位符相关，其实就是将SQL从占位符的位置拆分开，然后实际运行时，再通过实际的参数拼接起来，这个就是文本协议，虽然它是预编译，但是它也是拼接SQL出来的。</p><p><br></p><p>此时我们很好奇的问题，既然都是拼接SQL，它如何防止SQL注入呢？那么自然是看看setString方法到底干了啥，一下是它的源码：</p><pre class="brush:php;toolbar:false">public void setString(int parameterIndex, String x) throws SQLException {
        // if the passed string is null, then set this column to null
        if (x == null) {
            setNull(parameterIndex, Types.CHAR);
        } else {
            checkClosed();
            int stringLength = x.length();
            if (this.connection.isNoBackslashEscapesSet()) {
                // Scan for any nasty chars
                boolean needsHexEscape = isEscapeNeededForString(x,
                        stringLength);
                if (!needsHexEscape) {
                    byte[] parameterAsBytes = null;
                    StringBuffer quotedString = new StringBuffer(x.length() + 2);
                    quotedString.append('\'');
                    quotedString.append(x);
                    quotedString.append('\'');
                    if (!this.isLoadDataQuery) {
                        parameterAsBytes = StringUtils.getBytes(quotedString.toString(),
                                this.charConverter, this.charEncoding,
                                this.connection.getServerCharacterEncoding(),
                                this.connection.parserKnowsUnicode());
                    } else {
                        // Send with platform character encoding
                        parameterAsBytes = quotedString.toString().getBytes();
                    }
                    setInternal(parameterIndex, parameterAsBytes);
                } else {
                    byte[] parameterAsBytes = null;
                    if (!this.isLoadDataQuery) {
                        parameterAsBytes = StringUtils.getBytes(x,
                                this.charConverter, this.charEncoding,
                                this.connection.getServerCharacterEncoding(),
                                this.connection.parserKnowsUnicode());
                    } else {
                        // Send with platform character encoding
                        parameterAsBytes = x.getBytes();
                    }
                    setBytes(parameterIndex, parameterAsBytes);
                }
                return;
            }
            String parameterAsString = x;
            boolean needsQuoted = true;
            if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                needsQuoted = false; // saves an allocation later
                StringBuffer buf = new StringBuffer((int) (x.length() * 1.1));
                buf.append('\'');
                //
                // Note: buf.append(char) is _faster_ than
                // appending in blocks, because the block
                // append requires a System.arraycopy()....
                // go figure...
                //
                for (int i = 0; i <br><p>可以发现，它将传入的参数，进行了特殊字符的转义处理，另外就是在字符串的两边加上了<strong>单引号</strong>，也就是这与MySQL将SQL转义后传送给服务器端的东西，也就是最终传送的不是分解SQL与参数，而是拼接SQL，只是通过转义防止SQL注入。</p><p><br></p><p>在MySQL JDBC中，其实还有许多类似的伪转换，例如批处理，它使用循环来完成的，不过它也算满足了JDBC驱动的基本规范。  </p><p><br></p><p>另外，在MySQL分布式数据库上，分表是非常多的，每个物理分表都会有至少好几个SQL，即使每个库下面也会有许多，那么配置几十个cache，它的命中率到底有多少呢？而即便是一个库下面的多个Connection，他们的cache都是彼此独立的，意味着库越多、同一个库下面的表越多、业务逻辑越复杂，这样一个Connection需要多少cache才能达到想要的效果呢？而cache后的结果是占用更多的JVM空间，而且是许多的JVM空间，即使内存可以放得下，在现在的JVM中，只要做发生FULL  GC也会去扫描它们、移动它们。但是反过来，解析这个SQL语句只是解析出占位符，纯CPU密集型，而且次数相对CPU来讲就是小儿科，一个普通SQL可能就是1us的时间，我们没有必要跟JVM过不去，做费力不讨好的事情，因为本身就很土鳖了，再土点不就完蛋了吗。</p><br><p><span><span><span><span><span><span><br><br></span></span></span></span></span></span></p><p><span><br></span></p><p><span><br></span></p><p><span><br></span></p>

ログイン後にコピー

MySQL JDBC PrepareStatement基本的两种模式&amp;客户端空间占用

MySQL JDBC PrepareStatement基本的两种模式&客户端空间占用