什么是 MySQL JDBC 连接池中最高效的连接检测语句？，DBCP和Tomca
在回答这个问题之前，首先我们看看 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 associatedDatabaseMetaDataobject. This has the advantage of being very robust and working with any database, regardless of the database schema. However, a call toDatabaseMetaData.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
preferredTestQueryparameter. Be careful, however. SettingpreferredTestQuerywill 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 likeSELECT 1may (or may not) be sufficient to verify the Connection. If a table-independent query is not sufficient, instead ofpreferredTestQuery, you can set the parameterautomaticTestTable. 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 自定义检测语句。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处. 同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。