下面几个方法有严重的慢查询，建议去掉这个功能或者用其他方式实现

[solosky]

https://github.com/xuxueli/xxl-job/blob/cf1768d20e697681634069a6b89b49f8a4a04fbf/xxl-job-admin/src/main/java/com/xxl/job/admin/service/impl/XxlJobServiceImpl.java#L297

    @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。

[tk1990]

我们是自己给表加索引 然后log表定期删除或者备份

原始邮件 发件人:soloskynotifications@github.com 收件人:xuxueli/xxl-jobxxl-job@noreply.github.com 抄送:Subscribedsubscribed@noreply.github.com 发送时间:2018年6月4日(周一) 21:05 主题:[xuxueli/xxl-job] 下面几个方法有严重的慢查询，建议去掉这个功能或者用其他方式实现 (#402)

https://github.com/xuxueli/xxl-job/blob/cf1768d20e697681634069a6b89b49f8a4a04fbf/xxl-job-admin/src/main/java/com/xxl/job/admin/service/impl/XxlJobServiceImpl.java#L297 @Override public MapString, Object dashboardInfo() { int jobInfoCount = xxlJobInfoDao.findAllCount(); int jobLogCount = xxlJobLogDao.triggerCountByHandleCode(-1); int jobLogSuccessCount = xxlJobLogDao.triggerCountByHandleCode(ReturnT.SUCCESS_CODE); // executor count SetString executerAddressSet = new HashSetString(); ListXxlJobGroup groupList = xxlJobGroupDao.findAll(); if (CollectionUtils.isNotEmpty(groupList)) { for (XxlJobGroup group: groupList) { if (CollectionUtils.isNotEmpty(group.getRegistryList())) { executerAddressSet.addAll(group.getRegistryList()); } } } 还有这里。 @Override public ReturnTMapString, Object triggerChartDate() { Date from = DateUtils.addDays(new Date(), -30); Date to = new Date(); ListString triggerDayList = new ArrayListString(); ListInteger triggerDayCountSucList = new ArrayListInteger(); ListInteger triggerDayCountFailList = new ArrayListInteger(); int triggerCountSucTotal = 0; int triggerCountFailTotal = 0; ListMapString, Object triggerCountMapAll = xxlJobLogDao.triggerCountByDay(from, to, -1); ListMapString, 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。 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[xuxueli]

你好，这个问题将会在下个版本修复，相关代码已经推送master，可以参考调整。 修复逻辑：表 “XXL_JOB_QRTZ_TRIGGER_LOG” 上新增索引项 “KEY I_trigger_time (trigger_time)”；

[solosky]

感谢回复啊，看到你的提交了，但是dashboard方法前三个统计 依然有慢查询。

[solosky]

这个issue 怎么不能重开了

[xuxueli]

@solosky 这个问题在master分支已经修复，可以pull最新代码体验下啊。

我们Mock了100W+条Log数据，报表也响应时间低于80ms。