在回答這個問題之前，首先我們看看 MySQL 中有哪些常用的 JDBC 連接池： c3p0 DBCP Druid Tomcat JDBC Pool HikariCP 這些連接池中，c3p0 是一個老牌的連接池，很多流行框架，在其老版本中，都將 c3p0 作為預設的連接池。 DBCP 和 Tomcat ...
在回答這個問題之前，首先我們看看 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 自定義檢測語句。
