mysql查询超时，跑批报错分析原因

1. 跑批报错 日志如下:

最开始以为是那天的数据有什么问题，然后把sql和对应的值拿出来在测试环境执行也不行 报一样的错，就是查询超时

在查询生产表的数据量发现19号开始，二维码的量到了60W，我认为是量大后查询变慢，设置的300s超时，现在300s查不出来报错

但是所有的表都做了分区，按理说一天60W的量也没有问题，只会查找那一天的

最终确认原因是 没有写执行日志表的时间 m.execute_time between #{startTime} and #{endTime} ，会全表查

结论就是以后再做两个表关联，两个表的时间限制都要写上，否则一个表查的分区的那一天，另一张表是全部的量

    where 1=1
    and o.original_data_id = m.original_data_id
    and m.business_mode = '0'
    and m.execute_time between #{startTime} and #{endTime}

2019-01-20 01:05:11.918 ERROR 117783 --- [pool-4-thread-1] c.s.s.b.s.AgentStatisticsScheduler       : 任务02二维码执行异常
java.lang.reflect.InvocationTargetException: null
        at sun.reflect.Native
        MethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.suixingpay.smartss.batch.service.AgentStatisticsScheduler.statisticsByDay(AgentStatisticsScheduler.java:80)
        at com.suixingpay.smartss.batch.scheduler.AgentStatisticsJob.execute(AgentStatisticsJob.java:17)
        at com.suixingpay.smartss.batch.scheduler.SmartssJob.run(SmartssJob.java:7)
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.QueryTimeoutException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
### The error may involve com.suixingpay.smartss.dao.query.AgentStatisticsDayMapper.saveDoubleFreeOrCloudPayOrQrcode-Inline
### The error occurred while setting parameters
### SQL: insert into t_smt_agent_statistics_day(statistics_id, statistics_date, statistics_type, statistics_desc   , mechant_no, top_agent_no, trans_amount_day, trans_count_day, trans_fee_day, trans_profit_day, remark)      select     UUID(),    left(?, 10),    ?,    ?,    o.trigger_user_id,     ifnull(o.top_agent_no, ''),    sum(if(trigger_type='1', o.trans_amount, 0))    - sum(if(trigger_type='0', o.trigger_amount, 0))    as free_trans_amount,    sum(if(o.trigger_type='1', 1, 0))    - sum(if(o.trigger_type='0', 1, 0))     as free_trans_count,    sum(if(trigger_type='1',o.trans_fee_amount, 0))     - sum(if(trigger_type='0',o.return_fee_amount, 0))      as free_trans_fee_amount,        sum(    (select if((o.trigger_type='1'),l.separate_amount,0) - if((o.trigger_type='0'),l.separate_amount,0)    from t_smt_execute_log l    where l.original_data_id = o.original_data_id    and l.execute_time between ? and ?    and l.business_mode = '0')    )as top_agent_separate_amount,        ?   from t_smt_original_data o,t_smt_execute_log m   where 1=1       and o.original_data_id = m.original_data_id       and m.business_mode = '0'    and o.trigger_time between ? and ?    and o.source_system = ?    and o.trans_patterns = ?       and (o.key3 = '' or o.key3 is null)and o.key1 = ?         and left(o.trigger_user_id, 3) = '800'        group by o.trigger_user_id    order by null
### Cause: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
; SQL []; Statement cancelled due to timeout or client request; nested exception is com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:118)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy85.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy90.saveDoubleFreeOrCloudPayOrQrcode(Unknown Source)
        ... 16 common frames omitted
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2827)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
        at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
        at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
        at com.sun.proxy.$Proxy127.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
        at com.suixingpay.smartss.rwsplit.mybatis.DynamicDataSourceInterceptor.intercept(DynamicDataSourceInterceptor.java:75)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
        at com.sun.proxy.$Proxy125.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
        at sun.reflect.GeneratedMethodAccessor58.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
        ... 21 common frames omitted
2019-01-20 01:05:11.919  INFO 117783 --- [pool-4-thread-1] c.s.s.b.s.AgentStatisticsScheduler       : 任务02-二维码 执行完成 共计耗时：300388