慢SQL案例之一

现象：定时任务引入springbatch框架，当数据量积累到6000+以后，出现超过30s的慢查询报警。

慢查询sql：

SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from BATCH_JOB_EXECUTION E where JOB_INSTANCE_ID = 1 and JOB_EXECUTION_ID in (SELECT max(JOB_EXECUTION_ID) from BATCH_JOB_EXECUTION E2 where E2.JOB_INSTANCE_ID = 1);
 

   

分析过程:

1. 线上数据库查看执行计划：

explain SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from BATCH_JOB_EXECUTION E where JOB_INSTANCE_ID = 1 and JOB_EXECUTION_ID in (SELECT max(JOB_EXECUTION_ID) from BATCH_JOB_EXECUTION E2 where E2.JOB_INSTANCE_ID = 1);

执行计划结果：

   

原因分析：

• select_type为DEPENDENT SUBQUERY，代表子查询依赖于外部查询结果，即外部查询出数据然后再关联子查询的数据，外部查询数据过多时会变慢。
• 如图，e2的rows为3712，子查询扫描行数过多一样会导致查询慢。

　　2. 在测试环境运行执行计划，结果如图：

执行计划跟线上不一样：子查询rows为0，Extra为"Select tables optimized away"，表示不需要执行select就可以返回数据。

   

验证测试环境跟测试环境的数据库版本：

• 查询mysql版本：select version();

   

• 线上mysql版本：

可以看到线上mysql版本跟测试环境不一样，5.6版本对子查询做了优化，最终导致sql的查询效果不一样。

 

解决方案：

升级线上数据库版本到5.6，验证最终效果。