MySQL JDBC连接池最高效的连接检测语句是什么？

对数据库连接池的监控，一种解决方案可能就是写个应用程序，定时尝试连接数据库测，如果出现异常，就进行告警。例如Oracle中，经常会使用select 1 from dual，但它可能会出现一些争用。

MySQL中同样具有相同的问题，陈老师写的这篇文章《什么是 MySQL JDBC 连接池中最高效的连接检测语句？》就给我们讲解了这个问题的几种解决方案。

MySQL 中有哪些常用的 JDBC 连接池，

• c3p0
• DBCP
• Druid
• Tomcat JDBC Pool
• HikariCP

这些连接池中，c3p0 是一个老牌的连接池，很多流行框架，在其老版本中，都将 c3p0 作为默认的连接池。

DBCP 和 Tomcat JDBC Pool（Tomcat 的默认连接池）是 Apache 开源的。

Druid 是阿里开源的，它不仅仅是个数据库连接池，还可以监控数据库的访问性能，支持数据库密码加密等。

HikariCP 是目前风头最劲的 JDBC 连接池，其号称性能最好。

从下图 HikariCP 官网给出的压测结果来看，也确实如此，性能上吊打 c3p0、DBCP2。

包括 SpringBoot 2.0 也将 HikariCP 作为默认的数据库连接池。

MySQL JDBC连接池中最高效的连接检测语句

实际上，对于这个问题，c3p0 的官方文档（https://www.mchange.com/projects/c3p0/）中给出了答案。

When configuring Connection testing, first try to minimize the cost of each test. If you are using a JDBC driver that you are certain supports the new(ish) jdbc4 API — and if you are using c3p0-0.9.5 or higher! — let your driver handle this for you. jdbc4 Connections include a method called isValid()
 that should be implemented as a fast, reliable Connection test. By default, c3p0 will use that method if it is present.

However, if your driver does not support this new-ish API, c3p0's default behavior is to test Connections by calling the getTables()
 method on a Connection's associated DatabaseMetaData
 object. This has the advantage of being very robust and working with any database, regardless of the database schema. However, a call to DatabaseMetaData.getTables()
 is often much slower than a simple database query, and using this test may significantly impair your pool's performance.

The simplest way to speed up Connection testing under a JDBC 3 driver (or a pre-0.9.5 version of c3p0) is to define a test query with the preferredTestQuery
 parameter. Be careful, however. Setting preferredTestQuery
 will lead to errors as Connection tests fail if the query target table does not exist in your database prior to initialization of your DataSource. Depending on your database and JDBC driver, a table-independent query like SELECT 1
 may (or may not) be sufficient to verify the Connection. If a table-independent query is not sufficient, instead of preferredTestQuery
, you can set the parameter automaticTestTable
. Using the name you provide, c3p0 will create an empty table, and make a simple query against it to test the database.

从上面的描述中可以看到，最高效的连接检测语句是 JDBC4 中引入的isValid
方法 。

其次是通过 preferredTestQuery 设置一个简单的查询操作（例如SELECT 1
），最后才是默认的getTables
方法。

包括 HikariCP 的文档中，也推荐使用 isValid 方法。只有当驱动比较老，不支持 isValid 方法时，才建议通过 connectionTestQuery 自定义检测语句。

connectionTestQuery

If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none

所以接下来，我们要弄懂以下几个问题，

1. 什么是 isValid() 。

2. 什么是 getTables()。

3. 不同连接检测语句之间的性能对比情况。

4. 为什么 isValid() 的性能最好，MySQL 服务端是如何处理的。

5. 怎么设置才能使用 isValid() 进行连接检测？

什么是 isValid()

isValid 方法是在 JDBC4 中引入的。JDBC 是 Java 用于与关系型数据库通信的标准API。

JDBC4 指的是 Java Database Connectivity (JDBC) 的第 4 版本，该版本是在 Java 6（也被称为Java 1.6）中引入的。

所以只要程序使用的是 Java 1.6 及以上的版本，都支持 isValid 方法。

我们看这个方法的具体实现细节。

// src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java
@Override
public boolean isValid(int timeout) throws SQLException {
    synchronized (getConnectionMutex()) { // 获取与连接相关的锁
        if (isClosed()) {
            return false; // 如果连接已关闭，返回 false，表示连接无效
        }
        try {
            try {
                // 调用 pingInternal 方法，检查连接是否有效，timeout 参数以毫秒为单位
                pingInternal(false, timeout * 1000); 
            } catch (Throwable t) {
                try {
                    abortInternal(); // 如果 pingInternal 抛出异常，调用 abortInternal 方法中止连接
                } catch (Throwable ignoreThrown) { 
                    // we're dead now anyway // 忽略异常，因为连接已经无效
                }
                return false; // 返回 false，表示连接无效
            }
        } catch (Throwable t) {
            return false; // 如果在 try 块中的任何地方发生异常，返回 false，表示连接无效
        }
        return true; // 如果没有异常发生，表示连接有效，返回 true
    }
}


isValid 方法的核心是 pingInternal 方法。我们继续看看 pingInternal 方法的实现。

@Override
public void pingInternal(boolean checkForClosedConnection, int timeoutMillis) throws SQLException {
    this.session.ping(checkForClosedConnection, timeoutMillis);
}


方法中的 session 是个 NativeSession 对象。

以下是 NativeSession 类中 ping 方法的实现。

// src/main/core-impl/java/com/mysql/cj/NativeSession.java
public void ping(boolean checkForClosedConnection, int timeoutMillis) {
    if (checkForClosedConnection) { // 如果需要检查连接是否已关闭，调用 checkClosed 方法
        checkClosed();
    }
    ...
    // this.protocol.sendCommand 是发送命令，this.commandBuilder.buildComPing(null)是构造命令。
    this.protocol.sendCommand(this.commandBuilder.buildComPing(null), false, timeoutMillis); // it isn't safe to use a shared packet here
}


实现中的重点是 this.protocol.sendCommand 和 this.commandBuilder.buildComPing 这两个方法。前者用来发送命令，后者用来构造命令。

后者中的 commandBuilder 是个 NativeMessageBuilder 对象。

以下是 NativeMessageBuilder 类中 buildComPing 方法的实现。

// src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeMessageBuilder.java
public NativePacketPayload buildComPing(NativePacketPayload sharedPacket) {
    NativePacketPayload packet = sharedPacket != null ? sharedPacket : new NativePacketPayload(1);
    packet.writeInteger(IntegerDataType.INT1, NativeConstants.COM_PING);
    return packet;
}


NativePacketPayload 是与 MySQL 服务器通信的数据包，NativeConstants.COM_PING 即 MySQL 中的 COM_PING 命令包。

所以，实际上，isValid 方法封装的就是COM_PING命令包。

什么是 getTables()

getTables() 是 MySQL JDBC 驱动中 DatabaseMetaData 类中的一个方法，用来查询给定的库中是否有指定的表。

c3p0 使用这个方法检测时，只指定了表名 PROBABLYNOT。

库名因为设置的是 null，所以默认会使用 JDBC URL 中指定的数据库。如jdbc:mysql://110.0.0.1:3306/information_schema
中的 information_schema。

{
    rs = c.getMetaData().getTables( null,
                                    null,
                                    "PROBABLYNOT",
                                    new String[] {"TABLE"} );
    return CONNECTION_IS_OKAY;
}


如果使用的驱动是 8.0 之前的版本，对应的检测语句是，

SHOW FULL TABLES FROM `information_schema` LIKE 'PROBABLYNOT'


如果使用的驱动是 8.0 之后的版本，对应的检测语句是，

SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PROBABLYNOT' HAVING TABLE_TYPE IN ('TABLE',null,null,null,null) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME


因为 c3p0 是一个老牌的连接池，它流行的时候 MySQL 8.0 还没发布。所以，对于 c3p0，见到更多的是前面这一个检测语句。

四个连接检测语句之间的性能对比

下面我们对比下PING
、SELECT 1
、SHOW FULL TABLES FROM information_schema LIKE 'PROBABLYNOT'
 和 INFORMATION_SCHEMA.TABLES
这四个连接检测语句的执行耗时情况。

下面是具体的测试结果，每个语句循环执行了 100000 次。

PING time for 100000 iterations: 3.04852 seconds
SELECT 1 time for 100000 iterations: 12.61825 seconds
SHOW FULL TABLES time for 100000 iterations: 66.21564 seconds
INFORMATION_SCHEMA.TABLES time for 100000 iterations: 69.32230 seconds


为了避免网络的影响，测试使用的是 socket 连接。

测试脚本地址：https://github.com/slowtech/dba-toolkit/blob/master/mysql/connection_test_benchemark.py

测试脚本中，使用的是connection.ping(reconnect=False)
命令。

这个 ping 命令跟 COM_PING 命令包有什么关系呢？

实际上，在 pymysql 中，ping 命令封装的就是 COM_PING 命令包。

def ping(self, reconnect=True):
    if self._sock is None:
        if reconnect:
            self.connect()
            reconnect = False
        else:
            raise err.Error("Already closed")
    try:
        self._execute_command(COMMAND.COM_PING, "")
        self._read_ok_packet()
    except Exception:
        if reconnect:
            self.connect()
            self.ping(False)
        else:
            raise


MySQL 服务端对于 COM_PING 的处理逻辑

以下是 MySQL 服务端处理 ping 命令的堆栈信息。

[mysqld] my_ok(THD *, unsigned long long, unsigned long long, const char *) sql_class.cc:3247
[mysqld] dispatch_command(THD *, const COM_DATA *, enum_server_command) sql_parse.cc:2268
[mysqld] do_command(THD *) sql_parse.cc:1362
[mysqld] handle_connection(void *) connection_handler_per_thread.cc:302
[mysqld] pfs_spawn_thread(void *) pfs.cc:2942
[libsystem_pthread.dylib] _pthread_start 0x0000000197d3bfa8


整个链路比较短，MySQL 服务端在接受到客户端请求后，首先会初始化一个线程。

线程初始化完毕后，会验证客户端用户的账号密码是否正确。

如果正确，则线程会循环从客户端连接中读取命令并执行。

不同的命令，会有不同的处理逻辑。

具体如何处理是在dispatch_command
中定义的。

bool dispatch_command(THD *thd, const COM_DATA *com_data,
                      enum enum_server_command command) {
  ...
  switch (command) {
    case COM_INIT_DB: {
      LEX_STRING tmp;
      thd->status_var.com_stat[SQLCOM_CHANGE_DB]++;
      thd->convert_string(&tmp, system_charset_info,
                          com_data->com_init_db.db_name,
                          com_data->com_init_db.length, thd->charset());

      LEX_CSTRING tmp_cstr = {tmp.str, tmp.length};
      if (!mysql_change_db(thd, tmp_cstr, false)) {
        query_logger.general_log_write(thd, command, thd->db().str,
                                       thd->db().length);
        my_ok(thd);
      }
      break;
    }
    case COM_REGISTER_SLAVE: {
      // TODO: access of protocol_classic should be removed
      if (!register_replica(thd, thd->get_protocol_classic()->get_raw_packet(),
                            thd->get_protocol_classic()->get_packet_length()))
        my_ok(thd);
      break;
    }
    case COM_RESET_CONNECTION: {
      thd->status_var.com_other++;
      thd->cleanup_connection();
      my_ok(thd);
      break;
    }
    ...
    case COM_PING:
      thd->status_var.com_other++;
      my_ok(thd);  // Tell client we are alive
      break;
    ...
}


可以看到，对于 COM_PING 命令包，MySQL 服务端的处理比较简单，只是将 com_other（com_other 对应状态变量中的 Com_admin_commands）的值加 1，然后返回一个 OK 包。

反观SELECT 1
命令，虽然看上去也足够简单，但毕竟是一个查询，是查询就要经过词法分析、语法分析、优化器、执行器等阶段。

以下是SELECT 1
在执行阶段的堆栈信息，可以看到，它比 COM_PING 的堆栈信息要复杂不少。

[mysqld] FakeSingleRowIterator::Read() basic_row_iterators.h:284
[mysqld] Query_expression::ExecuteIteratorQuery(THD *) sql_union.cc:1290
[mysqld] Query_expression::execute(THD *) sql_union.cc:1343
[mysqld] Sql_cmd_dml::execute_inner(THD *) sql_select.cc:786
[mysqld] Sql_cmd_dml::execute(THD *) sql_select.cc:586
[mysqld] mysql_execute_command(THD *, bool) sql_parse.cc:4604
[mysqld] dispatch_sql_command(THD *, Parser_state *) sql_parse.cc:5239
[mysqld] dispatch_command(THD *, const COM_DATA *, enum_server_command) sql_parse.cc:1959
[mysqld] do_command(THD *) sql_parse.cc:1362
[mysqld] handle_connection(void *) connection_handler_per_thread.cc:302
[mysqld] pfs_spawn_thread(void *) pfs.cc:2942
[libsystem_pthread.dylib] _pthread_start 0x0000000181d53fa8


怎么设置才能使用 isValid() 进行连接检测

对于 HikariCP 连接池来说，不设置 connectionTestQuery
 即可。

这一点，可从连接检测任务（KeepaliveTask）的代码中看出来。

try {
   final var validationSeconds = (int) Math.max(1000L, validationTimeout) / 1000;

   if (isUseJdbc4Validation) { 
      return !connection.isValid(validationSeconds);
   }

   try (var statement = connection.createStatement()) {
      if (isNetworkTimeoutSupported != TRUE) {
         setQueryTimeout(statement, validationSeconds);
      }

      statement.execute(config.getConnectionTestQuery());
   }
}


可以看到，是否使用 connection.isValid 是由 isUseJdbc4Validation 决定的。

而 isUseJdbc4Validation 是否为 true，是由配置中是否设置了connectionTestQuery
决定的，该参数不设置则默认为 none。

this.isUseJdbc4Validation = config.getConnectionTestQuery() == null;


对于 c3p0 连接池来说，需使用 v0.9.5 及之后的版本，同时不要设置 preferredTestQuery
 或者 automaticTestTable
。

注意，如果要定期对空闲连接进行检测，在 HikariCP 中，需要设置 keepaliveTime
。而在 c3p0 中，则需设置 idleConnectionTestPeriod
。这两个参数的默认值为 0，即不会对空闲连接进行定期检测。

mysqladmin ping 命令的实现逻辑

mysqladmin 中有个 ping 命令可以检查 MySQL 服务端的存活情况。

# mysqladmin --help | grep ping
  ping   Check if mysqld is alive
  
# mysqladmin -h127.0.0.1 -P3306 -uroot -p'123456' ping
mysqld is alive


这个 ping 命令实际上封装的就是 COM_PING 命令包。

// mysqladmin.cc
case ADMIN_PING:
  mysql->reconnect = false; /* We want to know of reconnects */
  if (!mysql_ping(mysql)) {
    if (option_silent reconnect = true;
      if (!mysql_ping(mysql))
        puts("connection was down, but mysqld is now alive");
    } else {
      my_printf_error(0, "mysqld doesn't answer to ping, error: '%s'",
                      error_flags, mysql_error(mysql));
      return -1;
    }
  }
  mysql->reconnect = true; /* Automatic reconnect is default */
  break;

// libmysql/libmysql.cc
int STDCALL mysql_ping(MYSQL *mysql) {
  int res;
  DBUG_TRACE;
  res = simple_command(mysql, COM_PING, nullptr, 0, 0);
  if (res == CR_SERVER_LOST && mysql->reconnect)
    res = simple_command(mysql, COM_PING, nullptr, 0, 0);
  return res;
}  


因此

1. 连接检测语句，首选是 JDBC 驱动中的 isValid 方法，其次才是自定义查询语句。

2. 虽然 isValid 方法是 JDBC 4 中才支持的，但 JDBC 4 早在 Java 6 中就引入了，所以，只要程序使用的是 Java 1.6 及以上的版本，都支持 isValid 方法。

3. 从连接池性能的角度出发，不建议使用 c3p0。但有些框架，在比较老的版本中，还是将 c3p0 作为默认的连接池。

   如果使用的是 c3p0 v0.9.5 之前的版本，建议配置 preferredTestQuery。如果使用的是 v0.9.5 及之后的版本，推荐使用 isValid。

4. SHOW FULL TABLES FROM xx LIKE 'PROBABLYNOT' 这个查询，在并发量较高的情况下，会对 MySQL 的性能产生较大的负面影响，线上慎用。

参考

1. MySQL JDBC驱动地址：https://github.com/mysql/mysql-connector-j
2. PyMySQL项目地址：https://github.com/PyMySQL/PyMySQL

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：《errorstack的level最高是多少？》《echarts的字体设置》《Oracle数据库揭露问题本质的利器 - errorstack》《推荐几款文本编辑器》《"'abrt-cli status' timed out"错误解决》
近期的热文：《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引：《公众号1300篇文章分类和索引》