表结构设计实战案例-定期清理

1. 版权

本文为原创, 遵循 CC 4.0 BY-SA 版权协议, 转载需注明出处: https://blog.youkuaiyun.com/big_cheng/article/details/116642907.
文中代码属于 public domain (无版权).

2. 原方案

业务系统里用户会上传文件. 既然上传就有清理的需求, 例如用户删除了一些文件、或用户上传中途直接’粗暴地’关闭了浏览器.

将待清理的文件归总到一个专门的表里然后后台定期清理是一个不错的方式: 一来分离关注点简化业务逻辑编写, 例如不用在保存订单时还要考虑如何删除一个文件. 二来独立出来后可增强功能/提高复用性, 例如清理时也更新相关统计.

(ps: 为何不直接上传到一个临时目录定期清理该目录就可以了? 因为在保存期间要一个个把文件从临时目录拷贝到正式目录, 如果中途拷失败了怎么办?)

设计待清理表如下(mysql):

CREATE TABLE IF NOT EXISTS file_gc (
  id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
  path            VARCHAR(36)     NOT NULL COMMENT '文件路径',
  gc_time         DATETIME        NOT NULL,
  try_cnt         BIGINT          NOT NULL DEFAULT 0,
  err_msg         VARCHAR(256)    NOT NULL DEFAULT ''
) ENGINE=InnoDB;

新增时设置gc_time=当前时间, try_cnt=0. 如果执行一次清理失败, 更新try_cnt++ 和err_msg.

一个文件删除失败应该定期重试但频率应放缓. 例如: 前3次重试间隔2min, 再3次重试间隔4min, 最后3次重试间隔6min, 仍不成功的话等待手工处理. 定义一个数据库函数由gc_time和try_cnt 来计算到下次重试还需多少秒:

-- 返回0=当前可gc, -1=禁止gc; 正数=距离可gc的秒数.
DROP FUNCTION IF EXISTS SF_gc_waitSec;
DELIMITER //
CREATE FUNCTION SF_gc_waitSec(p_gcTime DATETIME, p_tryCnt BIGINT) RETURNS BIGINT
  NOT DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE v_sec BIGINT;

  IF p_tryCnt >= 10 THEN
    RETURN -1;
  ELSEIF p_tryCnt <= 0 THEN
    RETURN 0;
  END IF;

  SET v_sec = CASE
    WHEN p_tryCnt <= 3 THEN TIMESTAMPDIFF(SECOND, NOW(), p_gcTime + INTERVAL 2 MINUTE)
    WHEN p_tryCnt <= 6 THEN TIMESTAMPDIFF(SECOND, NOW(), p_gcTime + INTERVAL 4 MINUTE)
    ELSE TIMESTAMPDIFF(SECOND, NOW(), p_gcTime + INTERVAL 6 MINUTE)
  END;

  RETURN CASE WHEN v_sec <= 0 THEN 0 ELSE v_sec END;
END
//
DELIMITER ;

try_cnt初值0, 失败后1, 重试第1次后2, 重试第9次后10. gc_time + 间隔 - 当前时间 如大于0 是还需等待的时间.

查询一批:

select
    id
    ,path
    ,SF_gc_waitSec(gc_time, try_cnt) 'waitSec' -- 0 可gc >0 将可gc
from file_gc
where
    try_cnt < 10
    and SF_gc_waitSec(gc_time, try_cnt) >= 0
order by
    waitSec
limit 100

按需等待的时间升序排列. 每次最多查100笔:

• 从头至尾: =0则执行gc - 如失败try_cnt++ 下次查询会排在后面
• 遇到>0时就可以中止处理, 等待该秒数

如果100笔都可以处理, 则可能还有=0的数据, 需马上继续. 否则不满100笔的话, 可以等久一点再继续 (等待期间产生新数据时应提前唤醒清理进程 - 不在本帖讨论).

3. 问题

仔细查看上方案可发现一个问题: 就是sql查询需要对所有记录计算SF_gc_waitSec() 然后才过滤前100笔返回. 如果由于维护/升级等特殊操作造成表里有大量数据, 则每次轮询都需要浪费较多计算, 不适合一个需长期运行的后台进程.

这里关键在每次查询都要计算每条记录到下次清理还需等待的时间, 但是’灵机一动’ 想到为何不在清理失败时直接算出下次的时间更新上去, 那么每次查询时只需要直接比较时间值, 同时也可以用上数据库索引了.

4. 方案2

表结构:

CREATE TABLE IF NOT EXISTS file_gc (
  id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
  path            VARCHAR(36)     NOT NULL COMMENT '文件路径',
  next_gc_time    DATETIME        NOT NULL,
  try_cnt         BIGINT          NOT NULL DEFAULT 0,
  err_msg         VARCHAR(256)    NOT NULL DEFAULT '',
  INDEX nextGcTime (next_gc_time)
) ENGINE=InnoDB;

查询一批:

select
    id
    ,path
    ,TIMESTAMPDIFF(SECOND, NOW(), next_gc_time) 'waitSec' -- <=0 可gc
from file_gc
where
    next_gc_time < NOW() + INTERVAL 1 HOUR
order by
    next_gc_time
limit 100

当前时间过了next_gc_time时就可执行, 即waitSec <= 0. 结果按next_gc_time 升序, 则waitSec 也是升序, 到第一笔 >0 时就可中止处理.

由于next_gc_time有索引, 所以mysql 只需按索引顺序取前100笔 (要满足where 1小时内条件) 返回即可 - 每次查询快速且浪费少, 适合于后台进程.

清理一笔失败时更新:

update upload_gc
set
    next_gc_time = CASE
        WHEN try_cnt <= 2 THEN next_gc_time + INTERVAL 2 MINUTE
        WHEN try_cnt <= 5 THEN next_gc_time + INTERVAL 4 MINUTE
        WHEN try_cnt <= 8 THEN next_gc_time + INTERVAL 6 MINUTE
        ELSE next_gc_time + INTERVAL 1 YEAR
    END
    ,try_cnt = CASE WHEN try_cnt <= 8 THEN try_cnt + 1 ELSE 0 END
    ,err_msg = SUBSTR(?, 1, 256)
where
    id = ?

第9次重试失败时 +一年&try_cnt清零, 这样万一没有人工处理, 系统在第二年仍然work.

4.2 总结

在开发中, 做出来和做得好可以有巨大的工作量差异. 好的设计应该简洁、高效 - mysql本身就是良好的例子.