479      阿裏雲  技術社區[雲棲]

MySQL JDBC PrepareStatement基本的兩種模式&客戶端空間占用的源碼分析

關於預編譯（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()”，如下圖：

好吧，不關注它的屌絲做法了，來繼續關注正題。

來看看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);
}

這裏將邏輯分解為兩個大板塊：一個為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
}

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$

if (sql.length() < getPreparedStatementCacheSqlLimit()) {
     ((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
}

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$

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感興趣，就去看看它的代碼，同樣這個代碼很長，我也簡單簡化了下邏輯如下圖所示：

這個邏輯基本與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);
}

for (int i = 0; i < batchedParameterStrings.length; i++) {
            if ((batchedParameterStrings[i] == null)
                    && (batchedParameterStreams[i] == null)) {
                throw SQLError.createSQLException(Messages
                        .getString("PreparedStatement.40") //$NON-NLS-1$
                        + (i + 1), SQLError.SQL_STATE_WRONG_NO_OF_PARAMETERS);
            }
            sendPacket.writeBytesNoNull(this.staticSqlStrings[i]);
            if (batchedIsStream[i]) {
                streamToBytes(sendPacket, batchedParameterStreams[i], true,
                        batchedStreamLengths[i], useStreamLengths);
            } else {
                sendPacket.writeBytesNoNull(batchedParameterStrings[i]);
            }
        }

這個循環看到每次都會將staticSqlStrings拚接一次，然後再拚接一個參數，這個就是一個byte[][]格式，而它的賦值就是來源於ParseInfo，在方法：PrepareStatement中的initializeFromParseInfo()中有相應的說明。

也就是說他用的就是ParseInfo中的內容，而那個內容分析過，與占位符相關，其實就是將SQL從占位符的位置拆分開，然後實際運行時，再通過實際的參數拚接起來，這個就是文本協議，雖然它是預編譯，但是它也是拚接SQL出來的。

此時我們很好奇的問題，既然都是拚接SQL，它如何防止SQL注入呢？那麼自然是看看setString方法到底幹了啥，一下是它的源碼：

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 < stringLength; ++i) {
                    char c = x.charAt(i);                                                         
                    switch (c) {
                    case 0: /* Must be escaped for 'mysql' */
                        buf.append('\\');
                        buf.append('0');
                        break;
                    case '\n': /* Must be escaped for logs */
                        buf.append('\\');
                        buf.append('n');
                        break;
                    case '\r':
                        buf.append('\\');
                        buf.append('r');
                        break;
                    case '\\':
                        buf.append('\\');
                        buf.append('\\');
                        break;
                    case '\'':
                        buf.append('\\');
                        buf.append('\'');
                        break;
                    case '"': /* Better safe than sorry */
                        if (this.usingAnsiMode) {
                            buf.append('\\');
                        }
                        buf.append('"');
                        break;
                    case '\032': /* This gives problems on Win32 */
                        buf.append('\\');
                        buf.append('Z');
                        break;
                    default:
                        buf.append(c);
                    }
                }
                buf.append('\'');
                parameterAsString = buf.toString();
            }
            byte[] parameterAsBytes = null;
            if (!this.isLoadDataQuery) {
                if (needsQuoted) {
                    parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString,
                        '\'', '\'', this.charConverter, this.charEncoding, this.connection
                                .getServerCharacterEncoding(), this.connection
                                .parserKnowsUnicode());
                } else {
                    parameterAsBytes = StringUtils.getBytes(parameterAsString,
                            this.charConverter, this.charEncoding, this.connection
                                    .getServerCharacterEncoding(), this.connection
                                    .parserKnowsUnicode());
                }
            } else {
                // Send with platform character encoding
                parameterAsBytes = parameterAsString.getBytes();
            }
            setInternal(parameterIndex, parameterAsBytes);
            this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
        }
    }

可以發現，它將傳入的參數，進行了特殊字符的轉義處理，另外就是在字符串的兩邊加上了單引號，也就是這與MySQL將SQL轉義後傳送給服務器端的東西，也就是最終傳送的不是分解SQL與參數，而是拚接SQL，隻是通過轉義防止SQL注入。

在MySQL JDBC中，其實還有許多類似的偽轉換，例如批處理，它使用循環來完成的，不過它也算滿足了JDBC驅動的基本規範。  

另外，在MySQL分布式數據庫上，分表是非常多的，每個物理分表都會有至少好幾個SQL，即使每個庫下麵也會有許多，那麼配置幾十個cache，它的命中率到底有多少呢？而即便是一個庫下麵的多個Connection，他們的cache都是彼此獨立的，意味著庫越多、同一個庫下麵的表越多、業務邏輯越複雜，這樣一個Connection需要多少cache才能達到想要的效果呢？而cache後的結果是占用更多的JVM空間，而且是許多的JVM空間，即使內存可以放得下，在現在的JVM中，隻要做發生FULL  GC也會去掃描它們、移動它們。但是反過來，解析這個SQL語句隻是解析出占位符，純CPU密集型，而且次數相對CPU來講就是小兒科，一個普通SQL可能就是1us的時間，我們沒有必要跟JVM過不去，做費力不討好的事情，因為本身就很土鱉了，再土點不就完蛋了嗎。

最後更新：2017-04-03 16:49:04

  上一篇： Android框架淺析之鎖屏(Keyguard)機製原理

  下一篇： Oracle中job的使用詳解