深究 PrepareStatement

 关注公众号【1024个为什么】，及时接收最新推送文章！

本篇文章基于 MySQL Connector/J 5.1.40。

上篇文章《写个任务，翻车了 -- 记一次内存溢出排查》说到， JDBC42PreparedStatement 对象导致了内存溢出，我们今天就带着上篇里的疑问，好好研究一下 Statement，以免日后再犯低级的错误。

先来回顾一下事故现场

| 每个对象 23K，对象里到底存了什么，这么大？

先看一下 JDBC42PreparedStatement 的类图

其属性信息都在它的父类 StatementImpl、PreparedStatement 中，主要有 sql 语句、参数信息、返回数据等等，而最占空间的一般是返回数据，就是这个属性 com.mysql.jdbc.StatementImpl#openResults 。

我的场景是查 3 个字段，最大字段占 3 个字符，每次查 1000 条，按最大字段算 (3 * 3 * 3 * 1000 )/1024 = 26K。

所以主要是查询结果撑爆了内存。

| 为什么需要 close ？

先看一下 Connection、Statement、ResultSet 对象的持有关系

真正存数据的是 ResultSetRow，每个对象都有 close 方法，关闭不同的资源。

我们再看一下 close 里都做了什么？

com.mysql.jdbc.ConnectionImpl#realClose，会关闭所有的 statement

public void realClose(boolean calledExplicitly, boolean issueRollback, boolean skipLocalTeardown, Throwable reason) throws SQLException {
        此处省略...
              try {
                  closeAllOpenStatements();
              } catch (SQLException ex) {
                  sqlEx = ex;
              }

com.mysql.jdbc.StatementImpl#realClose，会关闭所有的 resultSet

protected void realClose(boolean calledExplicitly, boolean closeOpenResults) throws SQLException {
        此处省略...
            if (this.results != null) {
                try {
                    this.results.close();
                } catch (Exception ex) {

com.mysql.jdbc.ResultSetImpl#realClose, 会关闭所有的 rowData

public void realClose(boolean calledExplicitly) throws SQLException {
       此处省略...
                if (this.rowData != null) {
                    try {
                        this.rowData.close();
                    } catch (SQLException sqlEx) {
                        exceptionDuringClose = sqlEx;

大家注意这里 rowData 有三个实现类，如图所示

我们常用的是 RowDataStatic，它的 close 方法没有做任何事情，而是靠把ResultSet 中的 rowData 置为 null，

this.rowData = null;

释放了 rowData，也就释放了 List<ResultSetRow> rows。

这里简单说一下 RowDataDynamic 和 RowDataCursor。

RowDataDynamic 是通过流式接收 MySQL 服务端的结果，它的关闭比较复杂，还需要给服务端发指令。

if (conn != null) {
                if (!conn.getClobberStreamingResults() && conn.getNetTimeoutForStreamingResults() > 0) {
                    String oldValue = conn.getServerVariable("net_write_timeout");
                    if (oldValue == null || oldValue.length() == 0) {
                        oldValue = "60"; // the current default
                    }
                    this.io.clearInputStream();
                    java.sql.Statement stmt = null;
                    try {
                        stmt = conn.createStatement();
                        ((com.mysql.jdbc.StatementImpl) stmt).executeSimpleNonQuery(conn, "SET net_write_timeout=" + oldValue);
                    } finally {
                        if (stmt != null) {
                            stmt.close();
                        }
                    }
                }

RowDataCursor 是通过游标的方式接收 MySQL 服务端的结果，通过把持有的 private ResultSetImpl owner; 置为 null 实现对象的释放。

public void close() throws SQLException {
        this.metadata = null;
        this.owner = null;
    }

不过这两种场景我在工作中没用到过。

所以依靠任何一个上游对象关闭，其内部的所有资源都会被释放。

上篇文章中提到刷 200W 数据没有问题，是因为还没等着内存撑爆，connection 就已经关闭了。而数据量再大一些，问题就暴露出来了。

| 创建这么多的 PrepareStatement，服务端没有限制吗？

提出这个问题的前提是，假设 PrepareStatement 会和服务端建立连接，由服务端执行预处理的逻辑。

其实默认使用的是 JDBC42PreparedStatement ，不会和服务端建立连接的，服务端限制一说也就不成立了。

如果使用 ServerPreparedStatement, 服务端是有限制的，但远大于我这次的 2873 (见首图)，况且这个限制不是针对我们默认常用的场景。

这个限制生效的前提，是开启了服务端的 prepareStatement，如果开启，创建出来的 prepareStatement 对象就不是本次事故中撑爆内存的 JDBC42PreparedStatement ，而是 ServerPreparedStatement 。

服务端的 prepareStatement 默认是关闭的，开启方法是建立连接时追加属性

useServerPreparedStmts=true

jdbcUrl?useServerPreparedStmts=true

| 服务端的预处理还要和服务端通信，效率岂不是很低？

默认情况下效率肯定没有本地预处理高，要不 MySQL 默认也不会关闭 useServerPreparedStmts 了。

MySQL 也想到这一点了，并且给我们提供了一个解决方案，那就是缓存。细想一下我们使用预处理的场景， SQL 语句基本不变，变的只是参数，所以服务端可以把经过预处理的 SQL 缓存起来，想要使用预处理缓存，还得用户配置，默认也是关闭的。

开启方法是建立连接时追加属性 cachePrepStmts=true

jdbcUrl?cachePrepStmts=true

| 到底哪种方式效率高？

有网友对此做了测试，有兴趣的可以看一下这篇文章。

http://www.javashuo.com/article/p-mvxbhdya-k.html

从他的测试结果看，采用 服务端预处理 + 缓存 的方式效率最高，但比本地预处理并没有高特别多，大多数业务场景使用本地预处理就可以。

最后通过 2 张图看一下各种 Statement 具体的处理流程，有兴趣的可以对照源码查看：

(点击查看大图)

(点击查看大图)

扯两句

循环体内创建对象要谨慎

缓存几乎是提效的首选方案

原创不易，如有收获，一键三连，感谢支持！

参考文献

https://dev.mysql.com/doc/refman/5.7/en/statement-caching.html

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_prepared_stmt_count

http://www.javashuo.com/article/p-mvxbhdya-k.html

https://blog.csdn.net/gladmustang/article/details/41408373