什麼是 MySQL JDBC 連線池中最高效的連線檢測語句？

帶你聊技術發表於2023-12-26
MySqlJDBC

來源：MySQL實戰

在回答這個問題之前，首先我們看看 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 連線池中最高效的連線檢測語句？

MySQL JDBC連線池中最高效的連線檢測語句

實際上，對於這個問題，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://10.0.0.198: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 連線。

測試指令碼地址：

測試指令碼中，使用的是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 < 2) puts("mysqld is alive");
  } else {
    if (mysql_errno(mysql) == CR_SERVER_GONE_ERROR) {
      mysql->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驅動地址：
  2. PyMySQL專案地址：

來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70024922/viewspace-3001516/，如需轉載，請註明出處，否則將追究法律責任。

相關文章