SQL query 匹配完整双周周期并避免全表扫描

2026-10-24支付老李

结论

不要在查询条件中对 first_billing_date 计算 DATEDIFF(...) % 14，因为普通 B-tree 索引通常无法直接支持这类条件。

更合适的做法是保存一个可索引的 next_billing_date，查询时直接筛选已经到期的记录。first_billing_date 仍然可以保留，用于审计或重新计算账期。日期加 14 天的操作可以放在应用层处理，从而避开 MySQL 和 SQL Server 日期函数的差异。

推荐的数据设计

CREATE TABLE recurring_payments (
    id BIGINT NOT NULL PRIMARY KEY,
    first_billing_date DATE NOT NULL,
    next_billing_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL
);

CREATE INDEX ix_recurring_payments_due
    ON recurring_payments (next_billing_date, status);

如果 next_billing_date 是 DATE 类型，可以直接查询当天到期的记录：

SELECT id, first_billing_date, next_billing_date
FROM recurring_payments
WHERE next_billing_date = :today
  AND status = 'active';

这里使用的是索引友好的等值条件。也不必使用 SELECT *，账单任务通常只需读取实际参与处理的字段。

如果该字段是 DATETIME 或 TIMESTAMP，应使用半开区间，不要对列做日期转换：

SELECT id, first_billing_date, next_billing_date
FROM recurring_payments
WHERE next_billing_date >= :day_start
  AND next_billing_date < :next_day_start
  AND status = 'active';

避免写成：

WHERE DATE(next_billing_date) = :today

在索引列外套一层 DATE()，通常会影响索引的使用。

付款成功后推进账期

付款成功后，将计划日期向后推进 14 天：

UPDATE recurring_payments
SET next_billing_date = :following_billing_date
WHERE id = :id
  AND next_billing_date = :processed_billing_date;

建议由应用程序根据 :processed_billing_date 加 14 天，得到 :following_billing_date。

计算时应以原计划日期为基准，不要直接使用“当前时间加 14 天”。如果任务晚运行了一天，按当前时间计算会使后续账期全部偏移：

following_billing_date = processed_billing_date + 14 days

条件 WHERE next_billing_date = :processed_billing_date 也能提供基本的并发保护。如果其他工作进程已经推进了账期，本次更新就不会再次成功。

处理任务漏跑

如果调度任务可能中断，只查询“等于今天”的记录并不安全。昨天遗漏的账单之后将再也不会被选中。更稳妥的方式是查询所有已经到期的记录：

SELECT id, first_billing_date, next_billing_date
FROM recurring_payments
WHERE next_billing_date <= :today
  AND status = 'active'
ORDER BY next_billing_date;

这个范围条件同样可以使用索引。

对于已经逾期多个周期的记录，需要事先确定处理规则：

只收取一次，再把下次日期推进到未来；
分别收取每个遗漏周期的费用；
暂停自动收费，改为人工处理。

这属于业务规则，不能等生产环境真正出现逾期记录后再决定。

如果只能保留首次付款日期

从数学上看，需要判断：

today >= first_billing_date
AND (today - first_billing_date) mod 14 = 0

MySQL 可以这样写：

SELECT id, first_billing_date
FROM recurring_payments
WHERE first_billing_date <= CURRENT_DATE
  AND MOD(DATEDIFF(CURRENT_DATE, first_billing_date), 14) = 0;

SQL Server 的写法是：

SELECT id, first_billing_date
FROM recurring_payments
WHERE first_billing_date <= CAST(GETDATE() AS date)
  AND DATEDIFF(day, first_billing_date, CAST(GETDATE() AS date)) % 14 = 0;

这些表达式需要逐行计算日期差并取模。普通的 first_billing_date 索引最多只能帮助排除未来日期，通常无法直接定位所有与当天相差 14 天整数倍的记录。历史数据越多，需要扫描和计算的记录也越多。

如果首次付款日期有明确的最早边界，也可以让应用程序生成候选日期：

SELECT id, first_billing_date
FROM recurring_payments
WHERE first_billing_date IN (
    :today,
    :today_minus_14,
    :today_minus_28,
    :today_minus_42
);

这种查询可以使用 first_billing_date 索引，但参数数量会随着历史跨度增加，因此只适合数据生命周期较短或候选范围固定的场景。

账单处理还需要幂等保护

正确查出到期记录并不足以防止重复扣款。调度器重试、进程崩溃以及多个 worker 并发运行，都可能导致同一期账单被处理两次。

可以为每次计划扣款创建单独的记录，并添加唯一约束：

CREATE TABLE billing_attempts (
    id BIGINT NOT NULL PRIMARY KEY,
    recurring_payment_id BIGINT NOT NULL,
    scheduled_for DATE NOT NULL,
    payment_status VARCHAR(20) NOT NULL,
    UNIQUE (recurring_payment_id, scheduled_for)
);

如果支付服务支持幂等键，可以用 recurring_payment_id 和 scheduled_for 组成稳定的幂等标识。即使任务重复执行，同一账期也不会被视为新的扣款请求。

更通用、易维护的方案不是设计更复杂的取模查询，而是持久化“下一次应付款日期”并为其建立索引，再通过唯一的账期记录保证扣款幂等。