利用Redis实现延时任务

今天碰到一个需求是：将社区首页推荐中用户2小时前浏览或互动过的内容删除，需要用Redis实现延时任务来解决。
参考： 如何用 Redis 实现延时任务？

数据库：

====为用户的推荐数据V5（kid_recommend_data_for_user_v5）
id - ID
user_id - 用户ID
object_type - 对象类型（0笔记/1日志/3回答）
object_id - 对象ID
create_time - 创建时间

====浏览或互动相关逻辑SQL：在ConsoleCalcRecommendDataForUserV5的“计算为用户的推荐数据V5”方法中
    (
      p.object_type=0
      AND NOT EXISTS(SELECT * FROM kid_view_log WHERE user_id={0} AND object_type=16 AND object_id=p.object_id)
      AND NOT EXISTS(SELECT * FROM rec_vote WHERE user_id={0} AND object_type=23 AND object_id=p.object_id AND vote=1)
      AND NOT EXISTS(SELECT * FROM rec_booklist_favorite WHERE type=10 AND booklist_id=p.object_id AND user_id={0} AND favorite_flag=1)
      AND NOT EXISTS(SELECT * FROM rec_comment FORCE INDEX(idx2_user_id) WHERE user_id={0} AND object_type=23 AND object_id=p.object_id)
    ) OR
    (
      p.object_type=1
      AND NOT EXISTS(SELECT * FROM kid_view_log WHERE user_id={0} AND object_type=2 AND object_id=p.object_id)
      AND NOT EXISTS(SELECT * FROM rec_experience_vote WHERE experiences_id=p.object_id AND user_id={0})
      AND NOT EXISTS(SELECT * FROM rec_experience_favorites WHERE experiences_id=p.object_id AND user_id={0})
      AND NOT EXISTS(SELECT * FROM rec_experiences_reply WHERE experiences_id=p.object_id AND user_id={0})
    ) OR
    (
      p.object_type=3
      AND NOT EXISTS(SELECT * FROM kid_view_log WHERE user_id={0} AND object_type=1 AND object_id=p.object_id)
      AND NOT EXISTS(SELECT * FROM rec_vote WHERE user_id={0} AND object_type=20 AND object_id=p.object_id AND vote=1)
      AND NOT EXISTS(SELECT * FROM rec_booklist_favorite WHERE type=5 AND booklist_id=p.object_id AND user_id={0} AND favorite_flag=1)
      AND NOT EXISTS(SELECT * FROM rec_comment FORCE INDEX(idx2_user_id) WHERE user_id={0} AND object_type=20 AND object_id=p.object_id)
    )

步骤：
1、编写存储过程delete_recommend_content； 参数：(user_id integer,object_type integer,object_id integer)

BEGIN
    DECLARE v_json VARCHAR(4000);
    DECLARE v_redis_ret INT;
    DECLARE v_timestamp BIGINT;

    SET v_json=CONCAT(user_id, ',', object_type, ',', object_id);
    SET v_timestamp=UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 2 HOUR));
    SET v_redis_ret=redis_command_v2('eval', CONCAT('redis.call(', '''ZADD''', ',', '''DeleteRecommendContent''', ',', v_timestamp, ',', '''', v_json, '''', ')'), '0');
END

2、触发器

kid_view_log
错误：一直以为是条件错误，结果是传的值不正确，还是没有正确理解需求以及表结构。
BEGIN
  IF new.object_type=16 OR new.object_type=2 OR new.object_type=1 THEN
    call delete_recommend_content(new.user_id, new.object_type, new.object_id);
  END IF;
END

正确：
BEGIN    
    IF (new.object_type=16) THEN
    call delete_recommend_content(new.user_id, 0, new.object_id);
  END IF;
    
    IF (new.object_type=2) THEN
    call delete_recommend_content(new.user_id, 1, new.object_id);
  END IF;

  IF (new.object_type=1) THEN
    call delete_recommend_content(new.user_id, 3, new.object_id);
  END IF;
END

rec_vote
BEGIN
  DECLARE v_ret INT;

  IF new.object_type=10 THEN
    SET v_ret = redis_command_v2('DEL', CONCAT('ReadRec:VoteUser:', new.object_id));
  END IF;

  IF (new.object_type=23 AND new.vote=1) THEN
    call delete_recommend_content(new.user_id, 0, new.object_id);
  END IF;

  IF (new.object_type=20 AND new.vote=1) THEN
    call delete_recommend_content(new.user_id, 3, new.object_id);
  END IF;
END

rec_experience_vote 
BEGIN
  DECLARE v_ret INT;

  SET v_ret = redis_command_v2('HDEL', 'ArticleVoteUser', CONCAT(new.experiences_id));

  CALL delete_recommend_content(new.user_id, 1, new.experiences_id);
END

rec_booklist_favorite 
BEGIN
  DECLARE v_ret INT;

  SET v_ret = redis_command_v2('LPUSH', 'mq:CalcUserFavoriteCount', CONCAT('{"pk":',new.user_id,'}'));

  IF new.type=5 THEN
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',10,'contentType',3,'contentId',new.booklist_id)));
  ELSEIF new.type=10 THEN
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',11,'contentType',0,'contentId',new.booklist_id)));
  ELSEIF new.type=2 THEN
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',13,'contentType',6,'contentId',new.booklist_id)));
  ELSEIF new.type=8 THEN
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',14,'contentType',7,'contentId',new.booklist_id)));
  ELSEIF new.type=1 THEN
    SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',15,'contentType',15,'contentId',new.booklist_id)));
  END IF;
  
  IF (new.type=10 AND new.favorite_flag=1) THEN
    CALL delete_recommend_content(new.user_id, 0, new.booklist_id);
  END IF;

  IF (new.type=5 AND new.favorite_flag=1) THEN
    CALL delete_recommend_content(new.user_id, 3, new.booklist_id);
  END IF;
END

rec_experience_favorites 
BEGIN
  DECLARE v_ret INT;

  SET v_ret = redis_command_v2('LPUSH', 'mq:CalcUserFavoriteCount', CONCAT('{"pk":',new.user_id,'}'));
  SET v_ret = redis_command_v2('LPUSH', 'mq:CalcFollowFeed', CONCAT(json_object('oper',new.user_id,'type',8,'contentType',1,'contentId',new.experiences_id)));

  CALL delete_recommend_content(new.user_id, 1, new.experiences_id);
END

rec_comment 
BEGIN
  DECLARE v_ret INT;

  IF new.object_type=10 THEN
    SET v_ret = redis_command_v2('DEL', CONCAT('ReadRec:Comment:', new.object_id));
  END IF;

  SET v_ret = redis_command_v2('HDEL', 'LatestComment', CONCAT(new.object_type,'_',new.object_id));

  IF (new.object_type=23) THEN
    call delete_recommend_content(new.user_id, 0, new.object_id);
  END IF;

  IF (new.object_type=20) THEN
    call delete_recommend_content(new.user_id, 3, new.object_id);
  END IF;
END

rec_experiences_reply 
BEGIN
  DECLARE v_ret INT;

  SET v_ret = redis_command_v2('HDEL', 'ArticleReplyTopic', CONCAT(new.experiences_id));

  CALL delete_recommend_content(new.user_id, 1, new.experiences_id);
END

3、程序

package xhs.appApi.service;

import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

import redis.clients.jedis.Jedis;
import xhs.appApi.common.TextUtil;
import xhs.appApi.dao.CommonMapper;

public class DeleteRecommendContent {
    private static Logger logger = Logger.getLogger(DeleteRecommendContent.class);
    
    @Autowired
    private RedisService redisService;
    @Autowired
    private CommonMapper commonMapper;
    
    public void doJob() {
        if (!TextUtil.equals(System.getProperty("xhs.doJob"), "true")) return;
        
        Jedis jedis = redisService.getRedis();
        int userId, objectType, objectId;
        try {
            long timeMillis = System.currentTimeMillis()/1000;
            Set<String> set = jedis.zrangeByScore("DeleteRecommendContent", 0, timeMillis);
            for (String item : set) { // item="1,2,3"
                List<Integer> list = TextUtil.splitAsIntList(item); 
                userId = list.get(0);
                objectType = list.get(1);
                objectId = list.get(2);
                
                commonMapper.executeDelete(String.format("DELETE FROM kid_recommend_data_for_user_v5 WHERE user_id=%d AND object_type=%d AND object_id=%d", userId, objectType, objectId));
            }
            jedis.zremrangeByScore("DeleteRecommendContent", 0, timeMillis);
            
        } catch(Exception ex) {
            logger.error("doJob", ex);
        } finally {
            RedisService.closeRedis();
        }
    }
}

4、定时任务quarz配置

<bean id="job12" class="xhs.appApi.service.DeleteRecommendContent"/>
    <bean id="jobDetail_12" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean"
        p:targetMethod="doJob"
        p:concurrent="false">
        <property name="targetObject"><ref bean="job12" /></property>  
    </bean>
    <bean id="cronTrigger_12" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean"
        p:cronExpression="0 0/2 * * * ?">
        <property name="jobDetail"><ref bean="jobDetail_12" /></property>  
    </bean>
    
    <bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">  
        <property name="triggers">  
            <list>  
                <ref bean="cronTrigger_1" />
                <ref bean="cronTrigger_2" />
                <ref bean="cronTrigger_3" />
                <ref bean="cronTrigger_4" />
                <ref bean="cronTrigger_5" />
                <ref bean="cronTrigger_6" />
                <ref bean="cronTrigger_7" />
                <ref bean="cronTrigger_8" />
                <ref bean="cronTrigger_9" />
                <ref bean="cronTrigger_10" />
                <ref bean="cronTrigger_11" />
                <ref bean="cronTrigger_12" />
            </list>  
        </property>  
    </bean>