Mysql一个超慢查询的问题

ZRBRZB 2019-01-11 12:04:50
数据库环境：windows server 2008 Mysql 5.5

问题描述：一个遗留项目，其中有个超慢查询，大概需要查3-4个小时左右，其中涉及到多表的union以及sub等聚合函数操作。问题在于：如果在数据库服务器上执行这个sql是没有问题的，能够正常执行，就是慢而已。但是如有在另一个服务器上执行这个sql过段时间（大概2个小时左右），就会出现以下异常：


com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure



The last packet successfully received from the server was 7,875,323 milliseconds ago.  The last packet sent successfully to the server was 7,875,002 milliseconds ago.

	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)

	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3030)

	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2916)

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3459)

	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)

	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)

	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)

	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)

	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1365)

	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3005)

	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)

	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3003)

	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)

	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3003)

	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)

	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3003)

	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:136)

	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:606)

	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)

	at com.sun.proxy.$Proxy95.execute(Unknown Source)

	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)

	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)

	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)

	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)

	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)

	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)

	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)

	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:606)

	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)

	at com.sun.proxy.$Proxy16.selectList(Unknown Source)

	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)

	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)

	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)

	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

	at com.sun.proxy.$Proxy91.getSBUDay(Unknown Source)

	at com.jy.quartz.SBUJob.work(SBUJob.java:38)

	at com.jy.quartz.SBUJob.start(SBUJob.java:29)

	at com.jy.quartz.Clarify.start(Clarify.java:78)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:606)

	at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)

	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:311)

	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113)

	at org.quartz.core.JobRunShell.run(JobRunShell.java:207)

	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:560)

Caused by: java.net.SocketException: 连接超时

	at java.net.SocketInputStream.socketRead0(Native Method)

	at java.net.SocketInputStream.read(SocketInputStream.java:152)

	at java.net.SocketInputStream.read(SocketInputStream.java:122)

	at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)

	at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)

	at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)

	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2472)

	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2927)


经过以下尝试，仍未解决：
1、查看了mysql的wait_timeout和interactive_timeout参数都是2147000，不是这2个参数导致的。
2、net_read_timeout/net_write_timeout都设置成28800，测试后还是这个错误。
3、后来怀疑是服务安全狗的问题，停掉测试还是这个错误
4、之后怀疑是数据库服务器-Windows系统KeepAliveTime这个参数配置的太小，调成28800000（8小时），仍旧报此错误。
5、之后把客户机服务器的KeepAliveTime参数也调成调成28800000（8小时），发现客户端由原来的2小时报错变成8小时报错了。错误信息相同。
6、执行show full processlist 发现，慢查询那个连接，大概执行8000多秒之后就自动消失了。怀疑是Mysql有个限制，把超时的慢查询强制杀死。但是翻了官方参数配置，没发现有这种参数。

注：由于是遗留项目，要进行优化耗费巨大。所有想不优化sql的情况下解决这个问题。

各位Mysql专家，希望能给一些建议。感谢!
...全文
399 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
梦情与你 2019-01-16
  • 打赏
  • 举报
 回复
一千万条就要进行分块处理，和多开几个线程。
梦情与你 2019-01-16
  • 打赏
  • 举报
 回复
我的经验是每天找个空闲时间定时处理，把处理结果可以写到一个表中。第二天查这个表就可以了。在看下数据库索引和搜索条件优化。
ZRBRZB 2019-01-14
  • 打赏
  • 举报
 回复
引用 5 楼 吉普赛的歌 的回复:
在客户端上如果能正常执行， 返回的数据量有多少？
很少，3000条左右，但是原始表数据量很大，差不多1千万左右。这个sql主要是对某一天的数据做统计分析
吉普赛的歌 2019-01-14
  • 打赏
  • 举报
 回复
引用 6 楼 ZRBRZB 的回复:
[quote=引用 5 楼 吉普赛的歌 的回复:] 在客户端上如果能正常执行， 返回的数据量有多少？
很少，3000条左右，但是原始表数据量很大，差不多1千万左右。这个sql主要是对某一天的数据做统计分析[/quote] 你这个运算量不小了， 1千万的数据经过统计分析到3000条， 这其实是报表， 而不是一般的查询。 建议你把这一块做成报表， 每天凌晨用事件生成报表结果表， 用户只查询报表结果表即可， 没有必要强求 mysql 即时处理。
ZRBRZB 2019-01-11
  • 打赏
  • 举报
 回复
引用
6、执行show full processlist 发现，慢查询那个连接，大概执行8000多秒之后就自动消失了。怀疑是Mysql有个限制，把超时的慢查询强制杀死。但是翻了官方参数配置，没发现有这种参数。
第6条补充一下，在数据库服务器上的localhost连接不会出现自动消失的情况
ZRBRZB 2019-01-11
  • 打赏
  • 举报
 回复
自顶一下。。。
吉普赛的歌 2019-01-11
  • 打赏
  • 举报
 回复
在客户端上如果能正常执行， 返回的数据量有多少？
ZRBRZB 2019-01-11
  • 打赏
  • 举报
 回复
引用 3 楼 qq_15972537 的回复:
max_execution_time参数是否设置？ jdbc的连接池设置，在使用连接时，检查连接的可用性
Mysql 5.5 没有这个参数
qq_15972537 2019-01-11
  • 打赏
  • 举报
 回复
max_execution_time参数是否设置？ jdbc的连接池设置，在使用连接时，检查连接的可用性
mysql查询视图数据慢_MySQL - 视图 - 超慢查询
这是一个奇怪的。我试图在MySQL中使用视图(我对MySQL有相当的新意，并且有更多的Sybase和SQL Server的经验...MySQL - 视图 - 超慢查询意见非常简单，只需选择语句(这些表确实有几百万行)。例如说这个查询：SELECT CA...
三分钟了解MySQL慢查询
一、什么是慢查询什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。具体耗费多久算慢查询呢？这其实因人而异，有些公司慢查询的阈值...我们访问一个东西，或者保存一个东西，都得等好久，那不得分分钟弃坑？
MySQL慢查询及解决方案
MySQL慢查询与索引的关系，以及慢查询问题解决的方案
MySQL的count查询超级慢
MySQL的count查询超级慢 看到平均执行时间13.773s，意识到自己的认知漏洞了，一直认为有个记录总数的地方，直接返回这个数就好了 原因 主键是聚集索引，所以不选择主键，会选择你的索引里面长度最短的那个，如果你...
面试题 MySQL的慢查询、如何监控、如何排查？
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10s。默认情况下，...
MySQL

56,678

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章