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

Posted on by hackdl

在回答这个问题之前，首先我们看看 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 calledisValid()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 thegetTables()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 thepreferredTestQueryparameter. 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 自定义检测语句。

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
publicbooleanisValid(inttimeout)throwsSQLException{
synchronized(getConnectionMutex()){//获取与连接相关的锁
if(isClosed()){
returnfalse;//如果连接已关闭，返回false，表示连接无效
}
try{
try{
//调用pingInternal方法，检查连接是否有效，timeout参数以毫秒为单位
pingInternal(false,timeout*1000);
}catch(Throwablet){
try{
abortInternal();//如果pingInternal抛出异常，调用abortInternal方法中止连接
}catch(ThrowableignoreThrown){
//we'redeadnowanyway//忽略异常，因为连接已经无效
}
returnfalse;//返回false，表示连接无效
}
}catch(Throwablet){
returnfalse;//如果在try块中的任何地方发生异常，返回false，表示连接无效
}
returntrue;//如果没有异常发生，表示连接有效，返回true
}
}

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

@Override
publicvoidpingInternal(booleancheckForClosedConnection,inttimeoutMillis)throwsSQLException{
this.session.ping(checkForClosedConnection,timeoutMillis);
}

方法中的 session 是个 NativeSession 对象。

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

//src/main/core-impl/java/com/mysql/cj/NativeSession.java
publicvoidping(booleancheckForClosedConnection,inttimeoutMillis){
if(checkForClosedConnection){//如果需要检查连接是否已关闭，调用checkClosed方法
checkClosed();
}
...
// this.protocol.sendCommand 是发送命令，this.commandBuilder.buildComPing(null)是构造命令。
this.protocol.sendCommand(this.commandBuilder.buildComPing(null),false,timeoutMillis);//itisn'tsafetouseasharedpackethere
}

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

后者中的 commandBuilder 是个 NativeMessageBuilder 对象。

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

//src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeMessageBuilder.java
publicNativePacketPayloadbuildComPing(NativePacketPayloadsharedPacket){
NativePacketPayloadpacket=sharedPacket!=null?sharedPacket:newNativePacketPayload(1);
packet.writeInteger(IntegerDataType.INT1,NativeConstants.COM_PING);
returnpacket;
}

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",
newString[]{"TABLE"});
returnCONNECTION_IS_OKAY;
}

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

SHOWFULLTABLESFROM`information_schema`LIKE'PROBABLYNOT'

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

SELECTTABLE_SCHEMAASTABLE_CAT,NULLASTABLE_SCHEM,TABLE_NAME,CASEWHENTABLE_TYPE='BASETABLE'THENCASEWHENTABLE_SCHEMA='mysql'ORTABLE_SCHEMA='performance_schema'THEN'SYSTEMTABLE'ELSE'TABLE'ENDWHENTABLE_TYPE='TEMPORARY'THEN'LOCAL_TEMPORARY'ELSETABLE_TYPEENDASTABLE_TYPE,TABLE_COMMENTASREMARKS,NULLASTYPE_CAT,NULLASTYPE_SCHEM,NULLASTYPE_NAME,NULLASSELF_REFERENCING_COL_NAME,NULLASREF_GENERATIONFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME='PROBABLYNOT'HAVINGTABLE_TYPEIN('TABLE',null,null,null,null)ORDERBYTABLE_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 次。

PINGtimefor100000iterations:3.04852seconds
SELECT1timefor100000iterations:12.61825seconds
SHOWFULLTABLEStimefor100000iterations:66.21564seconds
INFORMATION_SCHEMA.TABLEStimefor100000iterations:69.32230seconds

为了避免网络的影响，测试使用的是 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 命令包。

defping(self,reconnect=True):
ifself._sockisNone:
ifreconnect:
self.connect()
reconnect=False
else:
raiseerr.Error("Alreadyclosed")
try:
self._execute_command(COMMAND.COM_PING,"")
self._read_ok_packet()
exceptException:
ifreconnect:
self.connect()
self.ping(False)
else:
raise

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

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

[mysqld]my_ok(THD*,unsignedlonglong,unsignedlonglong,constchar*)sql_class.cc:3247
[mysqld]dispatch_command(THD*,constCOM_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_start0x0000000197d3bfa8

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

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

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

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

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

booldispatch_command(THD*thd,constCOM_DATA*com_data,
enumenum_server_commandcommand){
...
switch(command){
caseCOM_INIT_DB:{
LEX_STRINGtmp;
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_CSTRINGtmp_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;
}
caseCOM_REGISTER_SLAVE:{
//TODO:accessofprotocol_classicshouldberemoved
if(!register_replica(thd,thd->get_protocol_classic()->get_raw_packet(),
thd->get_protocol_classic()->get_packet_length()))
my_ok(thd);
break;
}
caseCOM_RESET_CONNECTION:{
thd->status_var.com_other++;
thd->cleanup_connection();
my_ok(thd);
break;
}
...
caseCOM_PING:
thd->status_var.com_other++;
my_ok(thd);//Tellclientwearealive
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*,constCOM_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_start0x0000000181d53fa8

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

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

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

try{
finalvarvalidationSeconds=(int)Math.max(1000L,validationTimeout)/1000;

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

try(varstatement=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|grepping
pingCheckifmysqldisalive

#mysqladmin-h127.0.0.1-P3306-uroot-p'123456'ping
mysqldisalive

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

//mysqladmin.cc
caseADMIN_PING:
mysql->reconnect=false;/*Wewanttoknowofreconnects*/
if(!mysql_ping(mysql)){
if(option_silent}else{
if(mysql_errno(mysql)==CR_SERVER_GONE_ERROR){
mysql->reconnect=true;
if(!mysql_ping(mysql))
puts("connectionwasdown,butmysqldisnowalive");
}else{
my_printf_error(0,"mysqlddoesn'tanswertoping,error:'%s'",
error_flags,mysql_error(mysql));
return-1;
}
}
mysql->reconnect=true;/*Automaticreconnectisdefault*/
break;

//libmysql/libmysql.cc
intSTDCALLmysql_ping(MYSQL*mysql){
intres;
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);
returnres;
}

总结

  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

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: