一次数据库查询超时优化问题的实战记录

目录
  • 问题发现
  • 查找原因
  • 解决问题
  • 额外话:Transaction Timeout、Statement Timeout、Socket timeout 的区别
  • 它们三者的关系是在怎样的呢
  • 总结
  • 参数文章

问题发现

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

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
### The error may exist in class path resource [mapper/XXXXXXXXX-Mapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select t3.cino, t2.sn as orderSn,             t2.provider_id as providerId,             t4.logistics_no as logisticsSn,             t2.`name`,
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
; Unsupported command; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
org.springframework.dao.DataAccessResourceFailureException:
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
### The error may exist in class path resource [mapper/XXXXXXXXXOMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select t3.cino, t2.sn as orderSn,             t2.provider_id as providerId,             t4.logistics_no as logisticsSn,
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command

查找原因

1 和 DBA 排查 mycat(公司使用 mycat ) 和 mysql 的错误日志。发现是应用服务这边会给 MyCat 发送一个 KILL QUERY 命令,而 myCat 不支持该 KILL QUERY 命令。才给应用服务返回了 Unsupported command 异常错误

2021-07-02 10:46:33.567  WARN [$_NIOREACTOR-37-RW] (io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:96)) - Unsupported command:KILL QUERY 2956587

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

3 查看了下应用服务的配置,在 jdbc:url 统一配置的 mysql socket 执行超时时间是 15s。而在 mybatis 单独的 sql 执行语句配置的设置更长,是 20s(注意这里的配置是 xml 加 注解的方式)

3.1 mybatis xml里的 sql 语句我单独在客户端执行,测试的 sql 执行时间大概在 6,7 秒左右,是不会超过 15s 这个限制的,但是从定时任务调度日志看,每次任务总体上就执行了 8 秒左右,说明 mycat 确实是在15秒内收到超时 KILL 命令,从而导致sql 执行失败

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

-

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

5 因此决定本地调试一波。发现 @Options 还真的没生效,jdbc的 queryTimeout 取值的是 mybatis在yml的 全局配置

解决问题

上面提到, sql 的 statment 混合使用了 mybatis 的注解和xml混合使用。猜测应该是 mybatis 的 注解和xml 使用方式是不兼容的。因此试着在 xml 的 statment 修改了 timeout配置。设置 timeout = 60。发现 配置生效了,如下图

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

额外话:Transaction Timeout、Statement Timeout、Socket timeout 的区别

上面 mybatis 配置的 timeout 其实就是Statement Timeout。还有就是在jdbc:url 配置的socketTimeout;其实还有是spring定义的一个事务超时: Transaction Timeout

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

Statement Timeout

  • statement timeout 是用来限制一条语句 statement 的执行时长,可通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) 进行设置,不过一般是通过ORM框架来进行设置
  • 在 myBatis中,statement timeout 的默认值是通过 defaultStatementTimeout 属性进行设置。同时还可以在 xml 中 select,insert,update标签设置timeout属性,从而对不同 sql 语句配置超时时间

Transaction Timeout

  • Spring 提供的 transaction timeout 配置也非常简单,它会记录每个事务的开始时间和消耗时间,当超出timeout值时将抛出异常。
  • 假设某个事务中包含 3 个statement,每个statement的执行时间是 100ms,其他业务逻辑的执行时间是 50ms,那么transaction timeout至少应该设置为350ms(100 * 3 + 50)

Socket timeout

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

优先级顺序

  • Socket timeout 级别优于 Transaction Timeout,而Transaction Timeout 级别优于 Statement Timeout。也就是说如果 Statement Timeout 大于 Transaction Timeout 或者 Socket timeout,则无法生效
  • 不推荐使用socket timeout来限制statement的执行时长,因此socket timeout的值必须要高于statement timeout,否则,socket timeout将会先生效,这样statement timeout就变得毫无意义

总结

到此这篇关于数据库查询超时优化问题的文章就介绍到这了,更多相关数据库查询超时优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

参数文章

  • 深入分析JDBC超时机制
  • 如何配置MySQL数据库超时设置
时间: 2021-10-13

mysql数据库查询优化 mysql效率第1/3页

提高MySQL 查询效率的三个技巧小结MySQL由于它本身的小巧和操作的高效, 在数据库应用中越来越多的被采用.我在开发一个P2P应用的时候曾经使用MySQL来保存P2P节点,由于P2P的应用中,结点数动辄上万个,而且节点变化频繁,因此一定要保持查询和插入的高效.以下是我在使用过程中做的提高效率的三个有效的尝试. l        使用statement进行绑定查询 使用statement可以提前构建查询语法树,在查询时不再需要构建语法树就直接查询.因此可以很好的提高查询的效率. 这个方法适合于

数据库查询优化(主从表的设计)

举一个例子,我现在有一些新闻信息,它包括这些字段:新闻ID,新闻Name,新闻ShortIntro,新闻Detail,新闻PublishTime.我现在要把它存放在数据库中,然后从数据库中将其取出来放在GridView中分页显示. 我现在就以一种我所见过的常见的思维方式来一步一步模拟这个实现过程. 第一步:建立新闻数据表. 在这一步,很多人都会直接建一张News表,里面包括了上面说的那些字段. 第二步:查询数据. 写一个方法,把News表中满足查询条件的数据取出来放在DataSet(DataTa

海量数据库的查询优化及分页算法方案集合2/2

FROM publish  WHERE (id NOT IN  (SELECT TOP n-1 id   FROM publish))  id 为publish 表的关键字  我当时看到这篇文章的时候,真的是精神为之一振,觉得思路非常得好.等到后来,我在作办公自动化系统(ASP.NET+ C#+SQL SERVER)的时候,忽然想起了这篇文章,我想如果把这个语句改造一下,这就可能是一个非常好的分页存储过程.于是我就满网上找这篇文章,没想到,文章还没找到,却找到了一篇根据此语句写的一个分页存储过程

海量数据库的查询优化及分页算法方案

海量数据库的查询优化及分页算法方案  原出处不详 摘自:www.21php.com 随着"金盾工程"建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应用在各警种.各部门.与此同时,应用系统体系的核心.系统数据的存放地――数据库也随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了1000万条,可谓海量.那么,如何实现快速地从这些超大容量的数据库中提取数据(查询).分析.统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题. 在以下

海量数据库的查询优化及分页算法方案集合1/2第1/2页

随着"金盾工程"建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应用在各警种.各部门.与此同时,应用系统体系的核心.系统数据的存放地――数据库也随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了1000万条,可谓海量.那么,如何实现快速地从这些超大容量的数据库中提取数据(查询).分析.统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题. 在以下的文章中,我将以"办公自动化"系统为例,探讨如何在有着1000万

优化MySQL数据库中的查询语句详解

很多时候基于php+MySQL建立的网站所出现的系统性能瓶颈往往是出在MySQL上,而MySQL中用的最多的语句就是查询语句,因此,针对MySQL数据库查询语句的优化就显得至关重要!本文就此问题做出详细分析如下: 1.判断是否向MySQL数据库请求了不需要的数据,如下列情况: (1).查询不需要的数据,例如你需要10条数据,但是你选出了100条数据加了limit做限制. (2).多表关联时返回全部列 (3).总是取出全部列select*......取出全部列,会让优化器无法完成索引覆盖扫描这类优

数据库查询优化之子查询优化

1. 案例 取所有不为掌门人的员工,按年龄分组! select age as '年龄', count(*) as '人数' from t_emp where id not in (select ceo from t_dept where ceo is not null) group by age; 如何优化? ①解决dept表的全表扫描,建立ceo字段的索引: 此时,再次查询: ②进一步优化,替换not in. 上述SQL可以替换为: select age as '年龄',count(*) as

Oracle数据库中基本的查询优化与子查询优化讲解

1. 查询条件合理排序 Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表 之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率. 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

MySQL之select in 子查询优化的实现

下面的演示基于MySQL5.7.27版本 一.关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询,优化器会选择不同的策略. 1. 对于 IN.=ANY 子查询,优化器有如下策略选择: semijoin Materialization exists 2. 对于 NOT IN.<>ALL 子查询,优化器有如下策略选择: Materialization exists 3. 对于 derived 派生表,优化器有如下策略选择: derived_merge,将派生表合并到外部查询

MySql数据库中的子查询与高级应用浅析

MySql数据库中的子查询: 子查询:在一条select查询语句中嵌套另一条select语句,其主要作用是充当查询条件或确定数据源. 代码案例如下: 例1. 查询大于平均年龄的学生: select * from students where age > (select avg(age) from students); 例2. 查询学生在班的所有班级名字: select name from classes where id in (select cls_id from students where

浅谈MySQL中的子查询优化技巧

mysql的子查询的优化一直不是很友好,一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一,你可以点击这里 ,这里来获得一些信息,mysql在处理子查询的时候,会将子查询改写,通常情况下,我们希望由内到外,也就是先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询,但是恰恰相反,子查询不会先被执行:今天希望通过介绍一些实际的案例来加深对mysql子查询的理解: 案例:用户反馈数据库响应较慢,许多业务动更新被卡住:登录到数据库中观察,发现长时间执行的sql: | 10437

MySQL查询优化:用子查询代替非主键连接查询实例介绍

一对多的两张表,一般是一张表的外键关联到另一个表的主键.但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联. 例如: 复制代码 代码如下: create table t_team ( tid int primary key, tname varchar(100) ); create table t_people ( pid int primary key, pname varchar(100), team_name varchar(100) ); team表和people表是一对多的关

MySQL百万级数据分页查询优化方案

当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询.对于数据库分页查询,也有很多种方法和优化的点.下面简单说一下我知道的一些方法. 准备工作 为了对下面列举的一些优化进行测试,下面针对已有的一张表进行说明. 表名:order_history 描述:某个业务的订单历史表 主要字段:unsigned int id,tinyint(4) int type 字段情况:该表一共37个字段,不包含text等大型数组,最大为varcha

30个mysql千万级大数据SQL查询优化技巧详解

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用

MySQL SQL语句分析与查询优化详解

如何获取有性能问题的SQL 1.通过用户反馈获取存在性能问题的SQL 2.通过慢查询日志获取性能问题的SQL 3.实时获取存在性能问题的SQL 使用慢查询日志获取有性能问题的SQL 首先介绍下慢查询相关的参数 1.slow_query_log 启动定制记录慢查询日志 设置的方法,可以通过MySQL命令行设置set global slow_query_log=on 或者修改/etc/my.cnf文件,添加slow_query_log=on 2.slow_query_log_file 指定慢查询日志

如何提高MYSQL数据库的查询统计速度 select 索引应用

数据库系统是管理信息系统的核心,基于数据库的联机事务处理(OLTP)以及联机分析处理(OLAP)是银行.企业.政府等部门最为重要的计算机应用之一.从大多数系统的应用实例来看,查询操作在各种数据库操作中所占据的比重最大,而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句.举例来说,如果数据的量积累到一定的程度,比如一个银行的账户数据库表信息积累到上百万甚至上千万条记录,全表扫描一次往往需要数十分钟,甚至数小时.如果采用比全表扫描更好的查询策略,往往可以使查询时间降为几分钟,由此可见