Quartz数据库表分析

前言
上一篇文章Spring整合Quartz分布式调度介绍了Quartz通过数据库的方式来实现分布式调度，通过使用数据库来存储trigger，job等信息，可以在停服重启的时候重新加载上次trigger的状态，保证了完整性；另一方面通过数据库来实现锁机制来实现分布式调度；Quartz默认提供了11张表，本文将对这几张表做简要的分析。

表信息

1. 1.qrtz_blob_triggers

2. 2.qrtz_cron_triggers

3. 3.qrtz_simple_triggers

4. 4.qrtz_simprop_triggers

5. 5.qrtz_fired_triggers

6. 6.qrtz_triggers

7. 7.qrtz_job_details

8. 8.qrtz_calendars

9. 9.qrtz_paused_trigger_grps

10. 10.qrtz_scheduler_state

11. 11.qrtz_locks

共11张表，前6张都是关于各种triggers的信息，后面包括job，悲观锁，调度状态等信息；相关表操作在类StdJDBCDelegate中，相关sql语句在StdJDBCConstants中；

1.qrtz_blob_triggers
自定义的triggers使用blog类型进行存储，非自定义的triggers不会存放在此表中，Quartz提供的triggers包括：CronTrigger，CalendarIntervalTrigger，
DailyTimeIntervalTrigger以及SimpleTrigger，这几个trigger信息会保存在后面的几张表中；

2.qrtz_cron_triggers
存储CronTrigger，这也是我们使用最多的触发器，在配置文件中做如下配置，即可在qrtz_cron_triggers生成记录：

1. <bean id="firstCronTrigger"

2. class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">

3. <property name="jobDetail" ref="firstTask" />

4. <property name="cronExpression" value="0/6 * * ? * *" />

5. <property name="group" value="firstCronGroup"></property>

6. </bean>

7. <bean id="firstTask"

8. class="org.springframework.scheduling.quartz.JobDetailFactoryBean">

9. <property name="jobClass" value="zh.maven.SQuartz.task.FirstTask" />

10. <property name="jobDataMap">

11. <map>

12. <entry key="firstService" value-ref="firstService" />

13. </map>

14. </property>

15. </bean>

16. <bean id="firstService" class="zh.maven.SQuartz.service.FirstService"></bean>

表达式指定了每隔6秒执行一次，然后指定了要执行的task，task指定了要执行的业务，运行之后可以查看数据表：

1. mysql> select * from qrtz_cron_triggers;

2. +-------------+------------------+----------------+-----------------+---------------+

3. | SCHED_NAME | TRIGGER_NAME | TRIGGER_GROUP | CRON_EXPRESSION | TIME_ZONE_ID |

4. +-------------+------------------+----------------+-----------------+---------------+

5. | myScheduler | firstCronTrigger | firstCronGroup | 0/6 * * ? * * | Asia/Shanghai |

6. +-------------+------------------+----------------+-----------------+---------------+

7.  

myScheduler是在定义SchedulerFactoryBean时指定的名称，其他字段都可以在上面的配置中找到；

3.qrtz_simple_triggers
存储SimpleTrigger，在配置文件中做如下配置，即可在qrtz_simple_triggers生成记录：

1. <bean id="firstSimpleTrigger"

2. class="org.springframework.scheduling.quartz.SimpleTriggerFactoryBean">

3. <property name="jobDetail" ref="firstSimpleTask" />

4. <property name="startDelay" value="1000" />

5. <property name="repeatInterval" value="2000" />

6. <property name="repeatCount" value="5"></property>

7. <property name="group" value="firstSimpleGroup"></property>

8. </bean>

9. <bean id="firstSimpleTask"

10. class="org.springframework.scheduling.quartz.JobDetailFactoryBean">

11. <property name="jobClass" value="zh.maven.SQuartz.task.SimpleFirstTask" />

12. <property name="jobDataMap">

13. <map>

14. <entry key="firstService" value-ref="simpleFirstService" />

15. </map>

16. </property>

17. </bean>

18. <bean id="simpleFirstService" class="zh.maven.SQuartz.service.SimpleFirstService"></bean>

指定了开始延迟时间，重复间隔时间已经重复的次数限制，查看表如下：

1. mysql> select * from qrtz_simple_triggers;

2. +-------------+--------------------+------------------+--------------+-----------------+-----------------+

3. | SCHED_NAME | TRIGGER_NAME | TRIGGER_GROUP | REPEAT_COUNT | REPEAT_INTERVAL | TIMES_TRIGGERED |

4. +-------------+--------------------+------------------+--------------+-----------------+-----------------+

5. | myScheduler | firstSimpleTrigger | firstSimpleGroup | 5 | 2000 | 1 |

6. +-------------+--------------------+------------------+--------------+-----------------+-----------------+

7.  

TIMES_TRIGGERED用来记录执行了多少次了，此值被定义在SimpleTriggerImpl中，每次执行+1，这里定义的REPEAT_COUNT=5，实际情况会执行6次，具体可以查看SimpleTriggerImpl源码：

1. public Date getFireTimeAfter(Date afterTime) {

2. if (complete) {

3. return null;

4. }

5.  

6. if ((timesTriggered > repeatCount)

7. && (repeatCount != REPEAT_INDEFINITELY)) {

8. return null;

9. }

10. ......

11. }

timesTriggered默认值为0，当timesTriggered > repeatCount停止trigger，所以会执行6次，当执行完毕之后此记录会被删除；

4.qrtz_simprop_triggers
存储CalendarIntervalTrigger和DailyTimeIntervalTrigger两种类型的触发器，使用CalendarIntervalTrigger做如下配置：

1. <bean id="firstCalendarTrigger" class="org.quartz.impl.triggers.CalendarIntervalTriggerImpl">

2. <property name="jobDataMap">

3. <map>

4. <entry key="jobDetail" value-ref="firstCalendarTask"></entry>

5. </map>

6. </property>

7. <property name="key" ref="calendarTriggerKey"></property>

8. <property name="repeatInterval" value="1" />

9. <property name="group" value="firstCalendarGroup"></property>

10. </bean>

11. <bean id="firstCalendarTask"

12. class="org.springframework.scheduling.quartz.JobDetailFactoryBean">

13. <property name="jobClass" value="zh.maven.SQuartz.task.CalendarFirstTask" />

14. <property name="jobDataMap">

15. <map>

16. <entry key="firstService" value-ref="calendarFirstService" />

17. </map>

18. </property>

19. </bean>

20. <bean id="calendarFirstService" class="zh.maven.SQuartz.service.CalendarFirstService"></bean>

CalendarIntervalTrigger没有对应的FactoryBean，直接设置实现类CalendarIntervalTriggerImpl；指定的重复周期是1，默认单位是天，也就是每天执行一次，查看表如下：

1. mysql> select * from qrtz_simprop_triggers;

2. +-------------+--------------------+--------------------+------------+---------------+------------+------------+------------+-------------+-------------+------------+------------+-------------+-------------+

3. | SCHED_NAME | TRIGGER_NAME | TRIGGER_GROUP | STR_PROP_1 | STR_PROP_2 | STR_PROP_3 | INT_PROP_1 | INT_PROP_2 | LONG_PROP_1 | LONG_PROP_2 | DEC_PROP_1 | DEC_PROP_2 | BOOL_PROP_1 | BOOL_PROP_2 |

4. +-------------+--------------------+--------------------+------------+---------------+------------+------------+------------+-------------+-------------+------------+------------+-------------+-------------+

5. | myScheduler | calendarTriggerKey | firstCalendarGroup | DAY | Asia/Shanghai | NULL | 1 | 1 | 0 | 0 | NULL | NULL | 0 | 0 |

6. +-------------+--------------------+--------------------+------------+---------------+------------+------------+------------+-------------+-------------+------------+------------+-------------+-------------+

7.  

提供了3个string类型的参数，2个int类型的参数，2个long类型的参数，2个decimal类型的参数以及2个boolean类型的参数；具体每个参数是什么含义，根据不同的trigger类型存放各自的参数；

5.qrtz_fired_triggers
存储已经触发的trigger相关信息，trigger随着时间的推移状态发生变化，直到最后trigger执行完成，从表中被删除；已SimpleTrigger为例重复3次执行，查询表：

1. mysql> select * from qrtz_fired_triggers;

2. +-------------+----------------------------------------+--------------------+------------------+---------------------------+---------------+---------------+----------+-----------+-----------------+-----------+------------------+-------------------+

3. | SCHED_NAME | ENTRY_ID | TRIGGER_NAME | TRIGGER_GROUP | INSTANCE_NAME | FIRED_TIME | SCHED_TIME | PRIORITY | STATE | JOB_NAME | JOB_GROUP | IS_NONCONCURRENT | REQUESTS_RECOVERY |

4. +-------------+----------------------------------------+--------------------+------------------+---------------------------+---------------+---------------+----------+-----------+-----------------+-----------+------------------+-------------------+

5. | myScheduler | NJD9YZGJ2-PC15241041777351524104177723 | firstSimpleTrigger | firstSimpleGroup | NJD9YZGJ2-PC1524104177735 | 1524104178499 | 1524104178472 | 0 | EXECUTING | firstSimpleTask | DEFAULT | 0 | 0 |

6. | myScheduler | NJD9YZGJ2-PC15241041777351524104177724 | firstSimpleTrigger | firstSimpleGroup | NJD9YZGJ2-PC1524104177735 | 1524104180477 | 1524104180472 | 0 | EXECUTING | firstSimpleTask | DEFAULT | 0 | 0 |

7. | myScheduler | NJD9YZGJ2-PC15241041777351524104177725 | firstSimpleTrigger | firstSimpleGroup | NJD9YZGJ2-PC1524104177735 | 1524104180563 | 1524104182472 | 0 | ACQUIRED | NULL | NULL | 0 | 0 |

8. +-------------+----------------------------------------+--------------------+------------------+---------------------------+---------------+---------------+----------+-----------+-----------------+-----------+------------------+-------------------+

9.  

相同的trigger和task，每触发一次都会创建一个实例；从刚被创建的ACQUIRED状态，到EXECUTING状态，最后执行完从数据库中删除；

6.qrtz_triggers
存储定义的trigger，以上定义的三个triggers为例，分别是：firstSimpleTrigger，firstCalendarTrigger和firstCronTrigger，运行之后查看数据库：

1. mysql> select * from qrtz_triggers;

2. +-------------+--------------------+--------------------+-------------------+-----------+-------------+----------------+----------------+----------+---------------+--------------+---------------+----------+---------------+---------------+----------+

3. | SCHED_NAME | TRIGGER_NAME | TRIGGER_GROUP | JOB_NAME | JOB_GROUP | DESCRIPTION | NEXT_FIRE_TIME | PREV_FIRE_TIME | PRIORITY | TRIGGER_STATE | TRIGGER_TYPE | START_TIME | END_TIME | CALENDAR_NAME | MISFIRE_INSTR | JOB_DATA |

4. +-------------+--------------------+--------------------+-------------------+-----------+-------------+----------------+----------------+----------+---------------+--------------+---------------+----------+---------------+---------------+----------+

5. | myScheduler | calendarTriggerKey | firstCalendarGroup | firstCalendarTask | DEFAULT | NULL | 1524203884719 | 1524117484719 | 5 | WAITING | CAL_INT | 1524117484719 | 0 | NULL | 0 | |

6. | myScheduler | firstCronTrigger | firstCronGroup | firstTask | DEFAULT | NULL | 1524117492000 | 1524117486000 | 0 | ACQUIRED | CRON | 1524117483000 | 0 | firstCalendar | 0 | |

7. | myScheduler | firstSimpleTrigger | firstSimpleGroup | firstSimpleTask | DEFAULT | NULL | -1 | 1524117488436 | 0 | COMPLETE | SIMPLE | 1524117484436 | 0 | NULL | 0 | |

8. +-------------+--------------------+--------------------+-------------------+-----------+-------------+----------------+----------------+----------+---------------+--------------+---------------+----------+---------------+---------------+----------+

9.  

和qrtz_fired_triggers存放的不一样，不管trigger触发了多少次都只有一条记录，TRIGGER_STATE用来标识当前trigger的状态；firstCalendarTask每天执行一次，执行完之后一直是WAITING状态；firstCronTrigger每6秒执行一次状态是ACQUIRED状态；firstSimpleTrigger重复执行6次后状态为COMPLETE，并且会被删除；

7.qrtz_job_details
存储jobDetails信息，相关信息在定义的时候指定，如上面定义的JobDetailFactoryBean，查询数据库：

1. mysql> select * from qrtz_job_details;

2. +-------------+-------------------+-----------+-------------+-----------------------------------------+------------+------------------+----------------+-------------------+----------+

3. | SCHED_NAME | JOB_NAME | JOB_GROUP | DESCRIPTION | JOB_CLASS_NAME | IS_DURABLE | IS_NONCONCURRENT | IS_UPDATE_DATA | REQUESTS_RECOVERY | JOB_DATA |

4. +-------------+-------------------+-----------+-------------+-----------------------------------------+------------+------------------+----------------+-------------------+----------+

5. | myScheduler | firstCalendarTask | DEFAULT | NULL | zh.maven.SQuartz.task.CalendarFirstTask | 0 | 0 | 0 | 0 | |

6. | myScheduler | firstSimpleTask | DEFAULT | NULL | zh.maven.SQuartz.task.SimpleFirstTask | 0 | 0 | 0 | 0 | |

7. | myScheduler | firstTask | DEFAULT | NULL | zh.maven.SQuartz.task.FirstTask | 0 | 0 | 0 | 0 | |

8. +-------------+-------------------+-----------+-------------+-----------------------------------------+------------+------------------+----------------+-------------------+----------+

9.  

JOB_DATA存放的就是定义task时指定的jobDataMap属性，所以此属性需要实现Serializable接口，方便持久化到数据库；

8.qrtz_calendars
Quartz为我们提供了日历的功能，可以自己定义一个时间段，可以控制触发器在这个时间段内触发或者不触发；现在提供6种类型：AnnualCalendar，CronCalendar，DailyCalendar，HolidayCalendar，MonthlyCalendar，WeeklyCalendar；以下使用CronCalendar为例：

1. <bean id="firstCalendar" class="org.quartz.impl.calendar.CronCalendar">

2. <constructor-arg value="0/5 * * ? * *"></constructor-arg>

3. </bean>

4. <bean id="firstCronTrigger"

5. class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">

6. <property name="jobDetail" ref="firstTask" />

7. <property name="cronExpression" value="0/6 * * ? * *" />

8. <property name="group" value="firstCronGroup"></property>

9. <property name="calendarName" value="firstCalendar"></property>

10. </bean>

11. <bean id="scheduler"

12. class="org.springframework.scheduling.quartz.SchedulerFactoryBean">

13. <property name="schedulerName" value="myScheduler"></property>

14. <property name="dataSource" ref="dataSource" />

15. <property name="configLocation" value="classpath:quartz.properties" />

16. <property name="triggers">

17. <list>

18. <ref bean="firstCronTrigger" />

19. </list>

20. </property>

21. <property name="calendars">

22. <map>

23. <entry key="firstCalendar" value-ref="firstCalendar"></entry>

24. </map>

25. </property>

26. </bean>

定义了一个排除每隔5秒的CronCalendar，然后在firstCronTrigger中指定了calendarName，并且需要在SchedulerFactoryBean中定义calendars；因为firstCronTrigger每6秒执行一次，而CronCalendar排除每隔5秒，所以会出现firstCronTrigger在第5次触发的时候需要等待12秒，结果如下：

1. 20180419 15:09:06---start FirstService

2. 20180419 15:09:08---end FirstService

3. 20180419 15:09:12---start FirstService

4. 20180419 15:09:14---end FirstService

5. 20180419 15:09:18---start FirstService

6. 20180419 15:09:20---end FirstService

7. 20180419 15:09:24---start FirstService

8. 20180419 15:09:26---end FirstService

9. 20180419 15:09:36---start FirstService

10. 20180419 15:09:38---end FirstService

查询保存在数据中的CronCalendar：

1. mysql> select * from qrtz_calendars;

2. +-------------+---------------+----------+

3. | SCHED_NAME | CALENDAR_NAME | CALENDAR |

4. +-------------+---------------+----------+

5. | myScheduler | firstCalendar | |

6. +-------------+---------------+----------+

CALENDAR存放的是CronCalendar序列化之后的数据；

9.qrtz_paused_trigger_grps
存放暂停掉的触发器，测试手动暂停firstCronTrigger，代码如下：

1. public class App {

2. public static void main(String[] args) {

3. final AbstractApplicationContext context = new ClassPathXmlApplicationContext("quartz.xml");

4. final StdScheduler scheduler = (StdScheduler) context.getBean("scheduler");

5. try {

6. Thread.sleep(4000);

7. scheduler.pauseTriggers(GroupMatcher.triggerGroupEquals("firstCronGroup"));

8. } catch (Exception e) {

9. e.printStackTrace();

10. }

11. }

12. }

启动之后延迟4秒后暂停firstCronTrigger，这里传递的参数group，然后查看数据库：

1. mysql> select * from qrtz_paused_trigger_grps;

2. +-------------+----------------+

3. | SCHED_NAME | TRIGGER_GROUP |

4. +-------------+----------------+

5. | myScheduler | firstCronGroup |

6. +-------------+----------------+

因为已经入库，所以重启之后firstCronGroup还是处于暂停状态，firstCronTrigger不会运行；

10.qrtz_scheduler_state
存储所有节点的scheduler，会定期检查scheduler是否失效，启动多个scheduler，查询数据库：

1. mysql> select * from qrtz_scheduler_state;

2. +-------------+---------------------------+-------------------+------------------+

3. | SCHED_NAME | INSTANCE_NAME | LAST_CHECKIN_TIME | CHECKIN_INTERVAL |

4. +-------------+---------------------------+-------------------+------------------+

5. | myScheduler | NJD9YZGJ2-PC1524209095408 | 1524209113973 | 1000 |

6. | myScheduler | NJD9YZGJ2-PC1524209097649 | 1524209113918 | 1000 |

7. +-------------+---------------------------+-------------------+------------------+

记录了最后最新的检查时间，在quartz.properties中设置了CHECKIN_INTERVAL为1000，也就是每秒检查一次；

11.qrtz_locks
Quartz提供的锁表，为多个节点调度提供分布式锁，实现分布式调度，默认有2个锁：

1. mysql> select * from qrtz_locks;

2. +-------------+----------------+

3. | SCHED_NAME | LOCK_NAME |

4. +-------------+----------------+

5. | myScheduler | STATE_ACCESS |

6. | myScheduler | TRIGGER_ACCESS |

7. +-------------+----------------+

STATE_ACCESS主要用在scheduler定期检查是否失效的时候，保证只有一个节点去处理已经失效的scheduler；
TRIGGER_ACCESS主要用在TRIGGER被调度的时候，保证只有一个节点去执行调度；

总结
本文对这11张表做了简要的分析，介绍了每张表具体是用来存储什么的，并且给了简单的实例；其实如果要实现一个trigger的管理系统，其实也就是对这几张表的维护。

 

原文地址：https://blog.csdn.net/qq_41866572/article/details/80051520