记一次数据库查询超时优化问题
<p><img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/3a71fda03c533b9b895f725605c3b4ad.jpg"></p>
<h3>
问题发现</h3>
<ul>
<li>
在七月份时，经常发现有几个定时任务报错，查看了下异常原因，大概定位是数据库执行异常。</li>
</ul>
<ol class="dp-sql">
<li class="alt">
<span><span>###Errorquerying</span><span class="keyword">database</span><span>.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand</span></span>
</li>
<li>
<span>###Theerrormayexist<span class="op">in</span><span>classpathresource</span></span>
</li>
<li class="alt">
<span>###TheerrormayinvolvedefaultParameterMap</span>
</li>
<li>
<span>###Theerroroccurredwhilesettingparameters</span>
</li>
<li class="alt">
<span>###SQL:<span class="keyword">select</span><span>t3.cino,t2.sn</span><span class="keyword">as</span><span>orderSn,t2.provider_id</span><span class="keyword">as</span><span>providerId,t4.logistics_no</span><span class="keyword">as</span><span>logisticsSn,t2.`</span><span class="keyword">name</span><span>`,</span></span>
</li>
<li>
<span>###Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand</span>
</li>
<li class="alt">
<span>;Unsupportedcommand;nestedexception<span class="keyword">is</span><span>com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand</span></span>
</li>
<li>
<span>org.springframework.dao.DataAccessResourceFailureException:</span>
</li>
<li class="alt">
<span>###Errorquerying<span class="keyword">database</span><span>.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand</span></span>
</li>
<li>
<span>###Theerrormayexist<span class="op">in</span><span>classpathresource</span></span>
</li>
<li class="alt">
<span>###TheerrormayinvolvedefaultParameterMap</span>
</li>
<li>
<span>###Theerroroccurredwhilesettingparameters</span>
</li>
<li class="alt">
<span>###SQL:<span class="keyword">select</span><span>t3.cino,t2.sn</span><span class="keyword">as</span><span>orderSn,t2.provider_id</span><span class="keyword">as</span><span>providerId,t4.logistics_no</span><span class="keyword">as</span><span>logisticsSn,</span></span>
</li>
<li>
<span>###Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:Unsupportedcommand</span>
</li>
</ol>
<h3>
排查原因</h3>
<ul>
<li>
1 和 DBA 排查 mycat(公司使用 mycat ) 和 mysql 的错误日志。发现是应用服务这边会给 MyCat 发送一个 KILL QUERY 命令，而 myCat 不支持该 KILL QUERY 命令。才给应用服务返回了 Unsupported command 异常错误</li>
</ul>
<ol class="dp-sql">
<li class="alt">
<span><span>2021-07-0210:46:33.567WARN[$_NIOREACTOR-37-RW](io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:96))-Unsupportedcommand:KILLQUERY2956587</span></span>
</li>
</ol>
<ul>
<li>
2 上网搜索了下 KILL QUERY 发生的场景，在一篇文章 ?深入分析JDBC超时机制 找到问题所在，sql 执行超时，jdbc 会向 mysql 服务发送一个kill 命令，从而让 mysql 停止 sql 执行。不过传给了 mycat 服务，mycat 没有处理该命令，而是直接报错返回</li>
<li>
3 查看了下应用服务的配置，在 jdbc:url 统一配置的 mysql socket 执行超时时间是 15s。而在 mybatis 单独的 sql 执行语句配置的设置更长，是 20s(注意这里的配置是 xml 加 注解的方式)</li>
<li>
3.1 mybatis xml里的 sql 语句我单独在客户端执行，测试的 sql 执行时间大概在 6，7 秒左右，是不会超过 15s 这个限制的，但是从定时任务调度日志看，每次任务总体上就执行了 8 秒左右，说明 mycat 确实是在15秒内收到超时 KILL 命令，从而导致sql 执行失败</li>
</ul>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/7d358ef2d84f73ae50628b3bc9122dad.jpg"></p>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/3df14dfbfda116081def05611788ac86.jpg"></p>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/9ef915fc1735eaaa30cbb1e2104fb46b.jpg"></p>
<ul>
<li>
4 想着是不是在其他地方配置超时。细看了下 mybatis 的配置，还真有一个统一 sql 超时配置，default-statement-timeout = 5</li>
<li>
从mybatis 源码文档上看，单独设置的 mybatis @Options timeout属性是会覆盖掉在 yml 配置的 default-statement-timeout属性的。难道是 @Options 没生效 ?</li>
</ul>
<p>
5 因此决定本地调试一波。发现 @Options 还真的没生效，jdbc的 queryTimeout 取值的是 mybatis在yml的 全局配置</p>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/99a09f90e70b1efd743d2147109bb7fa.jpg"></p>
<h3>
解决问题</h3>
<ul>
<li>
上面提到, sql 的 statment 混合使用了 mybatis 的注解和xml混合使用。猜测应该是 mybatis 的 注解和xml 使用方式是不兼容的。因此试着在 xml 的 statment 修改了 timeout配置。设置 timeout = 60。发现 配置生效了，如下图图片：</li>
</ul>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/b155cb1603b280d5521d0dc3de82ad3a.jpg"></p>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/266c0999c5609ccc614b6bcc93535700.jpg"></p>
<p>
后面也试着将 sql 放置到 mybatis 的 @Select 注解,去掉 xml 的 statement。@Options 的配置也是可以生效的。mybatis 的@Options 和 xml 是无法同时生效的，可能 mybatis 其他的注解一样和 xml 配置不能同时生效，希望读者以后能避开这个坑。</p>
<h3>
Transaction Timeout、Statement Timeout、Socket timeout 的区别</h3>
<p>
<img title="记一次数据库查询超时优化问题" alt="记一次数据库查询超时优化问题" border="0" src="https://zhuji.jb51.net/uploads/img/202305/543acb12819917971b5a921cb789d3a4.jpg"></p>
<ul>
<li>
上面 mybatis 配置的 timeout 其实就是Statement Timeout。还有就是在jdbc:url 配置的socketTimeout;其实还有是spring定义的一个事务超时：Transaction Timeout。</li>
</ul>
<h3>
它们三者的关系是在怎样的呢</h3>
<p>
Statement Timeout</p>
<ul>
<li>
statement timeout 是用来限制一条语句 statement 的执行时长，可通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) 进行设置，不过一般是通过ORM框架来进行设置</li>
<li>
在 myBatis中，statement timeout 的默认值是通过 defaultStatementTimeout 属性进行设置。同时还可以在 xml 中 select，insert，update标签设置timeout属性，从而对不同 sql 语句配置超时时间</li>
</ul>
<p>
<strong>Transaction Timeout</strong></p>
<ul>
<li>
Spring 提供的 transaction timeout 配置也非常简单，它会记录每个事务的开始时间和消耗时间，当超出timeout值时将抛出异常。</li>
<li>
假设某个事务中包含 3 个statement，每个statement的执行时间是 100ms，其他业务逻辑的执行时间是 50ms，那么transaction timeout至少应该设置为350ms(100 * 3 + 50)</li>
</ul>
<p>
<strong>Socket timeout</strong></p>
<ul>
<li>
JDBC的 socket timeout 在数据库被突然停掉或是发生网络错误时十分重要。由于TCP/IP的结构原因，socket没有办法探测到网络错误，因此应用也无法主动发现数据库连接断开。如果没设置 socket timeout 的话，应用在数据库返回结果前会无期限地等下去，这种连接被称为 dead connection</li>
</ul>
<p>
<strong>优先级顺序</strong></p>
<ul>
<li>
Socket timeout 级别优于 Transaction Timeout，而Transaction Timeout 级别优于 Statement Timeout。也就是说如果 Statement Timeout 大于 Transaction Timeout 或者 Socket timeout，则无法生效</li>
<li>
不推荐使用socket timeout来限制statement的执行时长，因此socket timeout的值必须要高于statement timeout，否则，socket timeout将会先生效，这样statement timeout就变得毫无意义</li>
</ul>
<p>
<strong>参考文章</strong></p>
<p>
深入分析JDBC超时机制</p>
<p>
如何配置MySQL数据库超时设置</p>
<p>
原文链接：https://mp.weixin.qq.com/s/XvBK2PDbq2lyRzfL7YP2uQ</p>
頁:
[1]