记一次数据库查询超时优化问题

发布网友发布时间：2024-10-02 11:49

共1个回答

热心网友时间：2024-12-08 16:00

问题发现

起初在七月份时，经常发现有几个定时任务报错，查看了下异常原因，大概定位是数据库执行异常

###Errorqueryingdatabase.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand###Theerrormayexistinclasspathresource[mapper/XXXXXXXXX-Mapper.xml]###TheerrormayinvolvedefaultParameterMap###Theerroroccurredwhilesettingparameters###SQL:selectt3.cino,t2.snasorderSn,t2.provider_idasproviderId,t4.logistics_noaslogisticsSn,t2.`name`,###Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand;Unsupportedcommand;nestedexceptioniscom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommandorg.springframework..DataAccessResourceFailureException:###Errorqueryingdatabase.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand###Theerrormayexistinclasspathresource[mapper/XXXXXXXXXOMapper.xml]###TheerrormayinvolvedefaultParameterMap###Theerroroccurredwhilesettingparameters###SQL:selectt3.cino,t2.snasorderSn,t2.provider_idasproviderId,t4.logistics_noaslogisticsSn,###Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand查找原因

1.和DBA排查mycat（公司使用mycat）和mysql的错误日志。发现是应用服务这边会给MyCat发送一个KILLQUERY命令，而myCat不支持该KILLQUERY命令。才给应用服务返回了Unsupportedcommand异常错误

2021-07-0210:46:33.567WARN[$_NIOREACTOR-37-RW](io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:96))-Unsupportedcommand:KILLQUERY2956587

2.上网搜索了下KILLQUERY发生的场景，在一边文章里深入分析JDBC超时机制找到问题所在，sql执行超时，jdbc会向mysql服务发送一个kill命令，从而停止sql执行，不过公司的mycat服务没有处理该命令，而是直接报错

3.查看了下，服务的配置这里统一配置的mysqlsocket执行超时时间是15。而在单独的sql执行语句配置的设置更长是20s（注意这里的配置是xml加注解的方式）

3.1xml里的sql语句我单独在客户端执行，测试的sql执行时间在6，7秒左右，是不会超过15这个*的，但是从定时任务来，任务总体上就执行了8秒左右。说明sql确实是在15内秒被超时KILL掉的

4.想着是不是在其他配置超时。细看了下mybatis的配置，还真有有个一个统一sql超时配置，default-statement-timeout=5的设置-

从文档上看，单独设置的mybatis@Options属性是会覆盖掉在yml配置的default-statement-timeout属性的。难道是@Options没生效？

5.因此决定调试一波。发现@Options还真的没生效，jdbc的queryTimeout视同mybatis在yml的全局配置

解决问题

上面提到服务中用到mybatis的注解和xml混合使用。猜测应该是mybatis的注解和xml使用方式是相斥的，不兼容的，因此在xml的sql修改timeout配置timeout=60。发现timeout=60配置生效了

后面将sql放置到mybatis的@Select注解,去掉xml的声明。@Options的配置也是生效的。真的坑，mybatis的@Options和xml是无法同时生效的，可能mybatis其他的注解一样和xml配置不能同时生效，希望读者以后能避开这个坑

额外话：TransactionTimeout、StatementTimeout、Sockettimeout的区别

上面mybatis配置的timeout其实就是StatementTimeout。还有就是在jdbc:url配置的socketTimeout；其实还有一个事务超时TransactionTimeout,是spring定义

它们三者的关系是在怎样的呢StatementTimeout

statementtimeout是用来*statement的执行时长，可通过调用JDBC的java.sql.Statement.setQueryTimeout(inttimeout)进行设置，不过一般是通过ORM框架来进行设置

在myBatis中，statementtimeout的默认值是通过defaultStatementTimeout属性进行设置。同时还可以在xml中select，insert，update标签设置timeout属性，从而对不同sql语句配置超时时间

TransactionTimeout

Spring提供的transactiontimeout配置非常简单，它会记录每个事务的开始时间和消耗时间，当超出timeout值时将抛出异常。

假设某个事务中包含3个statement，每个statement的执行时间是100ms，其他业务逻辑的执行时间是50ms，那么transactiontimeout至少应该设置为350ms(100*3+50)

Sockettimeout

JDBC的sockettimeout在数据库被突然停掉或是发生网络错误时十分重要。由于TCP/IP的结构原因，socket没有办法探测到网络错误，因此应用也无法主动发现数据库连接断开。如果没有设置sockettimeout的话，应用在数据库返回结果前会无期限地等下去，这种连接被称为deadconnection

生效顺序

Sockettimeout级别优于TransactionTimeout，TransactionTimeout级别优于StatementTimeout。也就是说如果StatementTimeout大于TransactionTimeout或者Sockettimeout，则无法生效

不推荐使用sockettimeout来*statement的执行时长，因此sockettimeout的值必须要高于statementtimeout，否则，sockettimeout将会先生效，这样statementtimeout就变得毫无意义

参数文章

深入分析JDBC超时机制

如何配置MySQL数据库超时设置