MySQL 高频计费系统应如何设计与优化

2026-10-24支付阿杰

结论

MySQL 能处理这类“到期任务领取”场景，但不应让多台 Billing Daemon 反复扫描订阅主表，再通过长事务和 SELECT ... FOR UPDATE 争抢同一批记录。

更合理的做法是：

分开保存订阅状态、应收账款、计费任务和支付流水。
为待计费任务建立独立的窄表，配置与查询条件一致的复合索引。
在 MySQL 8.0+ 中使用 FOR UPDATE SKIP LOCKED 小批量领取任务。
领取事务只完成“锁定、标记、提交”，支付请求必须等事务提交后再执行。
通过租约、幂等键和事务型 outbox，应对进程崩溃、消息丢失和重复扣款。
日任务量达到数千万后，按稳定的业务键分片，不要指望单台数据库无限扩容。

每天 5000 万次计费，平均约为：

50,000,000 / 86,400 ≈ 579 次/秒

这个平均吞吐量并不算高。设计时更需要关注峰值、支付失败重试、第三方支付延迟，以及任务集中在某个整点触发的情况。容量规划要以峰值为准，不能只看日平均数。

当前流程为什么容易锁死

下面这种领取方式有几个问题：

SELECT ...
FROM billing_table
WHERE ...
ORDER BY next_bill_date
LIMIT 500
FOR UPDATE;

多个实例同时执行时，如果不跳过已锁定的记录，它们会从同一个索引起点开始扫描，并等待第一批记录释放。线程越多，锁等待队列越长，并发能力不会随之线性增长。

下面这些情况还会进一步加重问题：

查询条件和 ORDER BY 没有由同一个复合索引覆盖，数据库需要额外扫描、排序或回表。
支付调用发生在事务提交前，行锁会持续数百毫秒，甚至数秒。
使用较大的 IN (...) 更新分散记录，会产生大量随机写和脏页。
next_bill_date 同时承担调度和频繁更新的职责，相关二级索引会不断出现页分裂和写放大。
失败记录反复回到热区，与正常续费记录争抢同一段索引。
在默认的 REPEATABLE READ 下，范围锁定查询可能产生 next-key lock，扩大锁的影响范围。
大量 daemon 同时轮询时，即使没有任务，也会持续消耗数据库 I/O。

EXPLAIN 显示“使用了索引”，不等于查询已经足够高效。还需要检查实际扫描行数、回表次数、排序、锁等待时间，以及每批任务产生的写入量。

将计费任务从订阅主表拆出来

订阅表适合保存当前服务状态，不适合充当高并发工作队列。建议单独建立计费任务表：

CREATE TABLE billing_job (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    subscription_id BIGINT UNSIGNED NOT NULL,
    service_id      BIGINT UNSIGNED NOT NULL,
    shard_id         SMALLINT UNSIGNED NOT NULL,
    next_attempt_at DATETIME(6) NOT NULL,
    status           TINYINT UNSIGNED NOT NULL,
    attempt_no       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    lease_owner      VARBINARY(64) NULL,
    lease_until      DATETIME(6) NULL,
    idempotency_key  VARBINARY(64) NOT NULL,
    created_at       DATETIME(6) NOT NULL,
    updated_at       DATETIME(6) NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_idempotency_key (idempotency_key),
    KEY idx_job_claim (
        shard_id,
        status,
        next_attempt_at,
        id
    ),
    KEY idx_subscription (subscription_id)
) ENGINE=InnoDB;

如果任务需要先按 service_id 隔离，可以将领取索引改为：

KEY idx_job_claim (
    service_id,
    shard_id,
    status,
    next_attempt_at,
    id
)

索引列顺序应根据实际查询确定：

等值过滤列放在前面，例如 service_id、shard_id、status。
范围和排序列放在后面，例如 next_attempt_at。
最后放唯一且稳定的 id，使排序结果确定，也方便分页和批量更新。

领取查询必须使用相同的索引前缀：

WHERE shard_id = ?
  AND status = ?
  AND next_attempt_at <= ?
ORDER BY next_attempt_at, id

不要建立大量“可能有用”的二级索引。每次修改 status 或 next_attempt_at，相关索引都要同步更新。索引越多，写放大越严重。

使用 `SKIP LOCKED` 领取任务

在支持 SKIP LOCKED 的 MySQL 8.0+ 环境中，竞争实例可以跳过其他事务已经锁定的任务：

START TRANSACTION;

SELECT id
FROM billing_job
WHERE shard_id = ?
  AND status = 0
  AND next_attempt_at <= NOW(6)
ORDER BY next_attempt_at, id
LIMIT 500
FOR UPDATE SKIP LOCKED;

UPDATE billing_job
SET status = 1,
    lease_owner = ?,
    lease_until = DATE_ADD(NOW(6), INTERVAL 5 MINUTE),
    attempt_no = attempt_no + 1,
    updated_at = NOW(6)
WHERE id IN (?, ?, ?, ...);

COMMIT;

状态可以约定为：

0 = ready
1 = processing
2 = succeeded
3 = permanently_failed

事务提交后，再将任务交给本地 worker 或消息队列。领取事务中不能调用支付网关，也不应执行复杂的业务逻辑。

批次大小没有统一答案。可以先从 100～500 条开始压测，再根据以下指标调整：

单次事务耗时；
lock wait time；
redo log 和 binlog 写入速率；
buffer pool 命中率；
磁盘队列长度；
worker 的实际处理速度。

批次并非越大越快。批次过大会延长锁持有时间、增加回滚成本，还可能造成任务分配不均。

如果 MySQL 版本不支持 SKIP LOCKED，可以按 shard_id 将任务确定性地分配给不同 daemon，使各实例原则上不会扫描相同范围。不要单纯依靠多个实例无序竞争来提高吞吐量。

使用租约处理进程崩溃

daemon 可能在任务标记为 processing 后崩溃，因此领取动作需要设置租约，防止任务永久丢失：

status = 1
lease_owner = 'billing-node-17'
lease_until = '2026-08-31 12:05:00'

由独立的恢复任务定期将租约超时的记录放回待处理状态：

UPDATE billing_job
SET status = 0,
    lease_owner = NULL,
    lease_until = NULL,
    next_attempt_at = NOW(6),
    updated_at = NOW(6)
WHERE status = 1
  AND lease_until < NOW(6)
ORDER BY lease_until
LIMIT 1000;

这条恢复查询需要单独的索引：

KEY idx_expired_lease (status, lease_until, id)

不要在主领取查询中加入下面的条件：

status = 0 OR lease_until < NOW()

这种 OR 条件通常会破坏清晰的索引访问路径。将正常领取和超时恢复拆成两个流程，更容易控制执行行为。

消息队列不能代替数据库一致性

假设流程是：

数据库提交，将任务改为 processing；
再向消息队列发送消息；

进程可能在这两步之间崩溃。租约可以让任务最终恢复，但会带来额外延迟。如果需要更强的一致性，可以使用 transactional outbox：

CREATE TABLE billing_outbox (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_id     BIGINT UNSIGNED NOT NULL,
    payload    JSON NOT NULL,
    created_at DATETIME(6) NOT NULL,
    sent_at    DATETIME(6) NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_job (job_id),
    KEY idx_unsent (sent_at, id)
) ENGINE=InnoDB;

领取任务时，在同一个数据库事务中修改任务状态并写入 outbox：

START TRANSACTION;

-- SELECT ... FOR UPDATE SKIP LOCKED
-- UPDATE billing_job ...

INSERT INTO billing_outbox (job_id, payload, created_at)
VALUES (?, ?, NOW(6));

COMMIT;

随后由专门的 publisher 将 outbox 消息发送到队列。消息即使被重复发送，消费者也必须支持幂等处理。

扣款必须具备幂等性

租约超时、网络重试和消息重复投递，都可能使同一次计费执行多次。系统不能假设“队列通常只投递一次”。

每个计费周期应生成一个稳定的幂等键，例如：

SHA256(subscription_id + billing_period_start + charge_type)

本地数据库需要为该键建立唯一约束：

CREATE TABLE payment_attempt (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    billing_job_id    BIGINT UNSIGNED NOT NULL,
    idempotency_key   VARBINARY(64) NOT NULL,
    provider_ref      VARCHAR(128) NULL,
    amount            DECIMAL(18, 4) NOT NULL,
    currency          CHAR(3) NOT NULL,
    result_status     TINYINT UNSIGNED NOT NULL,
    created_at        DATETIME(6) NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_payment_idempotency (idempotency_key),
    KEY idx_job (billing_job_id)
) ENGINE=InnoDB;

如果支付服务商支持 idempotency key，还要将同一个键传给服务商。只靠本地唯一索引，无法避免一种情况：支付已经成功，但本地记录写入前连接中断，随后重试又造成重复扣款。此时还需要调用服务商的查询接口进行对账。

不要直接覆盖财务历史

money-people-owe-me 表中每个订阅只有一行，并不断覆盖余额和尝试次数。这种实现虽然简单，却不利于审计、对账和故障恢复。

更稳妥的模型通常包含：

subscription：保存当前订阅和服务状态。
invoice：保存每个账期产生的应收记录。
payment_attempt：保存每次支付尝试。
ledger_entry：保存费用、付款、退款、核销等不可变流水。
billing_job：只负责调度，不承担财务账本职责。

当前余额可以缓存，但必须能够根据不可变流水重新计算。金额应使用 DECIMAL 或最小货币单位对应的整数，不能使用 FLOAT、DOUBLE。

控制失败重试形成的压力

支付失败的用户如果被高频重试，会长期占据索引头部。可以使用指数退避，并加入少量随机抖动：

next_delay = min(max_delay, base_delay × 2^attempt_no) + jitter

例如可以采用下面的间隔，但具体参数要根据业务可容忍的时间确定，不能直接照搬：

5 分钟 → 30 分钟 → 2 小时 → 12 小时 → 1 天

同时还要设置明确的终止条件：

最大尝试次数；
最大宽限期；
卡号无效等不可重试错误；
需要用户操作的状态；
停止服务和通知用户的时间点。

正常续费和欠费重试最好使用不同的队列、索引范围或任务表，避免大量失败重试拖慢正常账期的任务。

削平整点峰值

如果所有订阅都在午夜或账期边界到期，即使日平均吞吐量不高，也会产生很大的瞬时压力。

在业务允许的计费窗口内，可以根据订阅 ID 进行稳定散列：

offset = hash(subscription_id) % billing_window_seconds

再将任务均匀分散到这个窗口中。使用稳定散列后，同一订阅的调度位置不会在每次计算时随机变化。

还可以增加 shard_id：

shard_id = hash(subscription_id) % 256

每个 daemon 只处理明确分配给自己的 shard。这样可以减少锁竞争，也方便将来把不同 shard 迁移到不同数据库实例。

数据量继续增长时如何分片

分片应优先选择不会频繁变化的键，例如：

hash(subscription_id) % N

不建议直接按 next_bill_date 做物理分片。每次重试都会改变时间，如果以此分片，记录就需要频繁跨分片迁移。

可以按下面的方式逐步扩展：

调度器
  ├─ shard 0～63   → MySQL A
  ├─ shard 64～127 → MySQL B
  ├─ shard 128～191 → MySQL C
  └─ shard 192～255 → MySQL D

逻辑 shard 的数量应提前设置得足够细，每个物理数据库负责一组逻辑 shard。扩容时只需要迁移部分 shard，不必修改所有订阅的哈希规则。

MySQL 分区表可以改善部分数据管理工作，但无法自动解决热点锁、错误索引和长事务，也不能代替真正的跨实例分片。

进一步降低 I/O 压力

还可以从这些方面检查和优化：

领取查询不要使用 OFFSET，应始终从索引范围或上次处理的键继续。
只读取领取任务所需的列。通常先取 id 即可，不要读取宽字段和 JSON。
将支付响应、日志和审计信息放入独立表中，尽量缩小任务表的行宽。
不要把不参与查询却频繁修改的列加入索引。
限制同时轮询数据库的线程数，没有任务时采用有上限的退避等待。
分别配置 daemon 数量、领取批次和 worker 数量，不要让每个支付 worker 都直接轮询数据库。
检查 InnoDB buffer pool 能否容纳热索引，但不要在不了解内存和工作集数据的情况下猜测参数。
检查 redo log、binlog、checkpoint 和刷盘是否已经成为瓶颈。
使用慢查询日志、Performance Schema 和 EXPLAIN ANALYZE 查看实际执行情况。
如果语义允许并且经过测试，可以考虑让短领取事务使用 READ COMMITTED，以减少部分范围锁的影响。它不能替代正确的索引和 SKIP LOCKED。

对于典型的领取查询，应重点检查：

EXPLAIN ANALYZE
SELECT id
FROM billing_job
WHERE shard_id = 17
  AND status = 0
  AND next_attempt_at <= NOW(6)
ORDER BY next_attempt_at, id
LIMIT 500
FOR UPDATE SKIP LOCKED;

理想情况下，数据库应直接从复合索引中找到少量到期记录，而不是先扫描大量未来任务再过滤。