@Override
public Map<String, Object> dashboardInfo() {
int jobInfoCount = xxlJobInfoDao.findAllCount();
int jobLogCount = xxlJobLogDao.triggerCountByHandleCode(-1);
int jobLogSuccessCount = xxlJobLogDao.triggerCountByHandleCode(ReturnT.SUCCESS_CODE);
// executor count
Set<String> executerAddressSet = new HashSet<String>();
List<XxlJobGroup> groupList = xxlJobGroupDao.findAll();
if (CollectionUtils.isNotEmpty(groupList)) {
for (XxlJobGroup group: groupList) {
if (CollectionUtils.isNotEmpty(group.getRegistryList())) {
executerAddressSet.addAll(group.getRegistryList());
}
}
}
还有这里。
@Override
public ReturnT<Map<String, Object>> triggerChartDate() {
Date from = DateUtils.addDays(new Date(), -30);
Date to = new Date();
List<String> triggerDayList = new ArrayList<String>();
List<Integer> triggerDayCountSucList = new ArrayList<Integer>();
List<Integer> triggerDayCountFailList = new ArrayList<Integer>();
int triggerCountSucTotal = 0;
int triggerCountFailTotal = 0;
List<Map<String, Object>> triggerCountMapAll = xxlJobLogDao.triggerCountByDay(from, to, -1);
List<Map<String, Object>> triggerCountMapSuc = xxlJobLogDao.triggerCountByDay(from, to, ReturnT.SUCCESS_CODE);
贴几个Explain的结果,都是全表扫。
mysql> explain SELECT count(1)
-> FROM XXL_JOB_QRTZ_TRIGGER_LOG AS t
-> WHERE t.handle_code = 200;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4781243 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)
mysql> explain SELECT DATE_FORMAT(trigger_time,'%Y-%m-%d') triggerDay, COUNT(id) triggerCount
-> FROM XXL_JOB_QRTZ_TRIGGER_LOG
-> WHERE trigger_time BETWEEN '2018-05-05 20:54:58.247' and '2018-06-04 20:54:58.247'
->
-> GROUP BY triggerDay;
+----+-------------+--------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | XXL_JOB_QRTZ_TRIGGER_LOG | ALL | NULL | NULL | NULL | NULL | 4781253 | Using where; Using temporary; Using filesort |
+----+-------------+--------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)
建议把以上功能去掉,或者换一种方法实现。 每次登陆都产生很多慢查询,对生产环境数据库影响很大,非常有风险! 另外建议开发一个定时清除TRIGGER_LOG表记录功能,这个表会越来越大的。。 实现的时候要小步快跑,也就是执行频繁,但每次只删一点数据,避免删除语句占用大量数据库IO。