诡异问题
最近小伙伴反映了一个很诡异的问题，就是当应用执行完一些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的整体性能。
网友评论