数据库连接池“污染”问题

诡异问题

最近小伙伴反映了一个很诡异的问题，就是当应用执行完一些SQL后，后续查询的查询SQL有的可以准确返回，而有的返回记录却数只有1个，出现丢数问题。经过排查日志，发现有个SQL在执行前先执行了SET SQL_SELECT_LIMIT=1。

SQL_SELECT_LIMIT 变量用来指定select返回的最大记录数，具体可参见https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_select_limit

应用组反馈并没有直接调用这个SQL，下意识的觉得应该是MySQL驱动自动发送的。查看其源码，很快就定位到是应用方调用的Statement.setMaxRow方法，对应MySQL驱动中com.mysql.cj.jdbc.StatementImpl类。

private boolean executeInternal(String sql, boolean returnGeneratedKeys) throws SQLException {
JdbcConnection locallyScopedConn = this.checkClosed();

...
locallyScopedConn.setSessionMaxRows(maybeSelect ? this.maxRows : -1);
...}

进一步看locallyScopedConn.setSessionMaxRows方法

com.mysql.cj.jdbc.ConnectionImpl

public void setSessionMaxRows(int max) throws SQLException {
    try {
        synchronized(this.getConnectionMutex()) {
            if (this.session.getSessionMaxRows() != max) {
                this.session.setSessionMaxRows(max);
                this.session.execSQL((Query)null, "SET SQL_SELECT_LIMIT=" + (this.session.getSessionMaxRows() == -1 ? "DEFAULT" : this.session.getSessionMaxRows()), -1, (NativePacketPayload)null, false, this.nullStatementResultSetFactory, (ColumnDefinition)null, false);
            }
        }
    } catch (CJException var6) {
        throw SQLExceptionsMapping.translateException(var6, this.getExceptionInterceptor());
    }
}

可以看到正是该方法发送了SET SQL_SELECT_LIMIT，正是这个SET语句使得后续该连接执行的SQL查询出来的记录数都只有1条，由于Proxy端的连接是一个连接池，因此下一个用到该连接的SQL也只能查到1条记录，这也是文章开头提到问题的原因所在。
写个测试代码很容易重现：

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | h2     |   10 |
|  2 | mysql  |   19 |
|  3 | oracle |   20 |
+----+--------+------+
3 rows in set (0.00 sec)

       HikariConfig config = new HikariConfig();
        config.setPoolName("test");
        config.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true&useAffectedRows=true");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(1);
        config.setMinimumIdle(1);
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");

        HikariDataSource hikariDataSource = new HikariDataSource(config);
        Connection connection1 = hikariDataSource.getConnection();
        Statement statement1 = connection1.createStatement();

        statement1.execute("SET SQL_SELECT_LIMIT=1");
       // statement.setMaxRows(1);
        ResultSet resultSet1 = statement1.executeQuery("select * from user");
        System.out.println("After SET SQL_SELECT_LIMIT=1,first query");
        while (resultSet1.next()) {
            System.out.println(resultSet1.getString("name"));
        }
        resultSet1.close();
        statement1.close();
        connection1.close();//release connection

        Connection connection2 = hikariDataSource.getConnection();
        Statement statement2 = connection2.createStatement();
        ResultSet resultSet2 = statement2.executeQuery("select * from user");
        System.out.println("After SET SQL_SELECT_LIMIT=1,second query");
        while (resultSet2.next()) {
            System.out.println(resultSet2.getString("name"));
        }
        resultSet2.close();
        statement2.close();
        connection2.close();

执行结果：

After SET SQL_SELECT_LIMIT=1,first query
h2
After SET SQL_SELECT_LIMIT=1,second query
h2

不过问题并没有结束，因为正常情况下，我们往往不直接调用SET SQL_SELECT_LIMIT，而是调用Statement.setMaRows，那么这种方式下会出现类似问题吗？
写个测试代码，发现竟然不会。为什么呢？

       ...
       HikariDataSource hikariDataSource = new HikariDataSource(config);
        Connection connection1 = hikariDataSource.getConnection();
        Statement statement1 = connection1.createStatement();

        //statement1.execute("SET SQL_SELECT_LIMIT=1");
        statement1.setMaxRows(1);
        ResultSet resultSet1 = statement1.executeQuery("select * from user");
        System.out.println("After setMaxRows,first query");
        while (resultSet1.next()) {
            System.out.println(resultSet1.getString("name"));
        }
        resultSet1.close();
        statement1.close();
        connection1.close();//release connection

        Connection connection2 = hikariDataSource.getConnection();
        Statement statement2 = connection2.createStatement();
        ResultSet resultSet2 = statement2.executeQuery("select * from user");
        System.out.println("After setMaxRows,second query");
        while (resultSet2.next()) {
            System.out.println(resultSet2.getString("name"));
        }
        resultSet2.close();
        statement2.close();
        connection2.close();

执行结果：

After setMaxRow,first query
h2
After setMaxRow,second query
h2
mysql
oracle

这是因为当调用Connection.createStatement创建一个新的Statement的时候，因为MySQL驱动会自动比较当前maxRows值与Session的设置是否一样，新建的Statement的maxRows值为-1，跟当前数据库连接的sessionMaxrows不一样，MySQL驱动自动发送SET SQL_SELECT_LIMIT=DEFAULT SQL来重置该变量值。
也可通过测试代码进行验证：

      ....
        HikariDataSource hikariDataSource = new HikariDataSource(config);
        Connection connection1 = hikariDataSource.getConnection();
        Statement statement1 = connection1.createStatement();
        statement1.execute("SET SQL_SELECT_LIMIT=1");
       // statement.setMaxRows(1);
        ResultSet resultSet1 = statement1.executeQuery("select * from user");
        System.out.println("After SET SQL_SELECT_LIMIT=1,first query");
        while (resultSet1.next()) {
            System.out.println(resultSet1.getString("name"));
        }
        resultSet1.close();
        statement1.close();
        connection1.close();//release connection

        Connection connection2 = hikariDataSource.getConnection();
        Statement statement2 = connection2.createStatement();
        statement2.execute("SET SQL_SELECT_LIMIT=DEFAULT");
        ResultSet resultSet2 = statement2.executeQuery("select * from user");
        System.out.println("After SQL_SELECT_LIMIT=DEFAULT,second query");
        while (resultSet2.next()) {
            System.out.println(resultSet2.getString("name"));
        }
        resultSet2.close();
        statement2.close();
        connection2.close();

执行结果：

After SET SQL_SELECT_LIMIT=1,first query
h2
After SQL_SELECT_LIMIT=DEFAULT,second query
h2
mysql
oracle

这个问题也让我们再次正视一个问题，数据库连接是有状态的，由于连接池中的连接是复用的，不当的设置会导致连接被“污染”，从而带来很多不容易排查的诡异问题，因为这类SESSION级的设置会在连接的生存期会一直生效。除了SQL_SELECT_LIMIT，常见的还有autocommit、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ、SET CHARACTER SET等等。

连接池带来的数据库连接“污染”，如何解决？

通过连接池直连MySQL

通过JDBC方式调用MySQL驱动方法，不要直接使用MYSQL SET类 SQL，这样MySQL驱动可以帮助尽可能降低这种连接污染带来的困惑。尽可能使用top、limit替代SET SQL SELECT_LIMIT。

通过DB Proxy中间件连接MySQL

由于Proxy端收到已经是SET 类SQL，没有了MySQL JDBC驱动进行自动重置，后端的连接管理要更复杂，需要在性能与状态正确性、连接的隔离和复用之间寻求平衡。

对于数据库连接，如果采用和数据库完全一致的模型，那么很容易有以下推导：
有状态->独占的->连接不复用共享（不使用连接池）-> 应用与Proxy的连接：Proxy与数据库连接是1: 1->数据库连接数限制->DB Proxy性能上不去.

连接独占模型

作为DB的统一接入端和网关，而连接性能是其核心关键的一个设计指标，而提升Proxy性能->后端连接必须进行复用->应用与Proxy的连接：Proxy与数据库连接是n :1。

连接复用模型

连接管理模型

在实际创景中并不是所有使用连接是都会执行SET SQL，而且也不是所有SET都会影响SQL执行的正确性（例如SET CHARACTER SET），因此可以通过SQL解析识别可能传染导致不正确的SET，然后再释放回连接之前进行重置（重置失败的，则进行隔离与关闭），这样一方面避免了SET SQL导致的连接污染，另一方面也保证了大多数场景连接的复用，增加Proxy前端连接能力，从而提升Proxy的整体性能。