什么是 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 自定义检测语句。