MySQL 支付系统的交易表与按月结算设计

结论

这种场景可以继续使用 MySQL。没有必要每月新建一张交易表，也不必因为数据持续增加就改用 NoSQL。

建议采用以下设计：

• 每次购买都保存为不可随意修改的交易记录；
• 在同一事务中扣除 credits 并创建购买记录，确保两项操作同时成功或同时失败；
• 单独建立结算批次和结算明细，保存每位卖家的应付金额；
• 通过状态、时间字段和必要的索引查询待结算交易；
• 数据量达到一定规模，并且确实出现性能问题后，再考虑按时间分区或归档历史数据。

交易表持续增长并不异常。支付系统通常需要长期保存流水，用于对账、退款、审计和争议处理。更需要防范的是缺少索引的查询、重复结算，以及把金额计算全部交给无法追踪的定时 SQL。

推荐的数据模型

不要只用一个 paid 布尔字段表示结算状态。购买状态、卖家结算状态和实际付款时间至少要分别记录。

下面是一个简化示例：

CREATE TABLE purchases (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    buyer_id BIGINT UNSIGNED NOT NULL,
    seller_id BIGINT UNSIGNED NOT NULL,
    blueprint_id BIGINT UNSIGNED NOT NULL,

    credit_amount BIGINT UNSIGNED NOT NULL,
    seller_amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,

    purchase_status ENUM(
        'completed',
        'refunded',
        'cancelled'
    ) NOT NULL,

    settlement_status ENUM(
        'pending',
        'processing',
        'settled',
        'failed'
    ) NOT NULL DEFAULT 'pending',

    settlement_batch_id BIGINT UNSIGNED NULL,
    created_at DATETIME(6) NOT NULL,
    settled_at DATETIME(6) NULL,

    PRIMARY KEY (id),
    KEY idx_settlement_scan (
        settlement_status,
        created_at,
        seller_id
    ),
    KEY idx_seller_created (
        seller_id,
        created_at
    ),
    KEY idx_buyer_created (
        buyer_id,
        created_at
    )
) ENGINE = InnoDB;

如果 credits 只能是整数，应使用 BIGINT 等整数类型；如果允许小数，则可以使用 DECIMAL。不要使用 FLOAT 或 DOUBLE 保存余额和货币金额，因为二进制浮点数可能产生舍入误差。

结算批次也需要单独保存：

CREATE TABLE settlement_batches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    period_start DATETIME(6) NOT NULL,
    period_end DATETIME(6) NOT NULL,
    status ENUM(
        'created',
        'processing',
        'completed',
        'failed'
    ) NOT NULL DEFAULT 'created',
    created_at DATETIME(6) NOT NULL,
    completed_at DATETIME(6) NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_settlement_period (
        period_start,
        period_end
    )
) ENGINE = InnoDB;

每位卖家的结算结果应分别记录：

CREATE TABLE settlement_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    batch_id BIGINT UNSIGNED NOT NULL,
    seller_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,
    status ENUM(
        'pending',
        'processing',
        'paid',
        'failed'
    ) NOT NULL DEFAULT 'pending',
    provider_transfer_id VARCHAR(191) NULL,
    paid_at DATETIME(6) NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_batch_seller_currency (
        batch_id,
        seller_id,
        currency
    ),
    UNIQUE KEY uk_provider_transfer (
        provider_transfer_id
    ),
    CONSTRAINT fk_settlement_item_batch
        FOREIGN KEY (batch_id)
        REFERENCES settlement_batches (id)
) ENGINE = InnoDB;

provider_transfer_id 用来保存支付服务商的转账编号。为它添加唯一约束，可以降低同一笔外部转账被重复登记的风险。

购买操作必须放在同一个事务中

扣除余额和插入购买记录属于同一个不可分割的业务操作。如果先扣余额，再单独插入记录，第二步失败时就可能出现余额已经扣除、订单却不存在的情况。

典型流程如下：

START TRANSACTION;

SELECT credit_balance
FROM user_credit_accounts
WHERE user_id = 1001
FOR UPDATE;

UPDATE user_credit_accounts
SET credit_balance = credit_balance - 250
WHERE user_id = 1001
  AND credit_balance >= 250;

-- 应用程序必须检查受影响行数。
-- 如果为 0，说明余额不足或账户不存在，此时执行 ROLLBACK。

INSERT INTO purchases (
    buyer_id,
    seller_id,
    blueprint_id,
    credit_amount,
    seller_amount,
    currency,
    purchase_status,
    settlement_status,
    created_at
) VALUES (
    1001,
    2001,
    3001,
    250,
    2.50,
    'USD',
    'completed',
    'pending',
    CURRENT_TIMESTAMP(6)
);

COMMIT;

如果还要记录平台手续费、税费或汇率，应在购买发生时保存计算结果，不要等到月底再根据当时的价格重新计算。例如，可以增加以下字段：

gross_amount DECIMAL(18, 2) NOT NULL,
platform_fee DECIMAL(18, 2) NOT NULL,
seller_amount DECIMAL(18, 2) NOT NULL

这样即使费率以后发生变化，也能准确复算历史交易。

要求更严格的支付账务系统，通常还会增加一张只追加、不更新的 ledger 表，分别记录买家扣款、卖家应收和平台收入。余额可以作为缓存，但账本才是可审计的数据来源。

每月结算的处理方式

结算前应先确定一个固定的时间区间，例如：

[2026-07-01 00:00:00, 2026-08-01 00:00:00)

建议使用左闭右开的区间，不要把结束时间写成“月底 23:59

”。如果时间精度达到微秒，这种写法容易遗漏记录。

创建批次后，可以按卖家汇总：

SELECT
    seller_id,
    currency,
    SUM(seller_amount) AS total_amount
FROM purchases
WHERE purchase_status = 'completed'
  AND settlement_status = 'pending'
  AND created_at >= '2026-07-01 00:00:00'
  AND created_at <  '2026-08-01 00:00:00'
GROUP BY seller_id, currency;

“汇总、调用转账接口、更新状态”不能简单地放进一个数据库事务，因为 MySQL 事务无法控制外部支付服务。更可靠的流程是：

1. 创建唯一的结算批次；
2. 锁定或认领符合条件的购买记录；
3. 生成 settlement_items；
4. 为每个结算项生成稳定的幂等键；
5. 调用支付服务商的转账接口；
6. 保存支付服务商返回的转账编号；
7. 确认转账成功后，把相关购买记录标记为 settled；
8. 转账失败时保留明确的状态，并允许安全重试。

可以先在事务中认领交易：

START TRANSACTION;

UPDATE purchases
SET settlement_status = 'processing',
    settlement_batch_id = 501
WHERE purchase_status = 'completed'
  AND settlement_status = 'pending'
  AND created_at >= '2026-07-01 00:00:00'
  AND created_at <  '2026-08-01 00:00:00';

INSERT INTO settlement_items (
    batch_id,
    seller_id,
    amount,
    currency,
    status
)
SELECT
    501,
    seller_id,
    SUM(seller_amount),
    currency,
    'pending'
FROM purchases
WHERE settlement_batch_id = 501
  AND settlement_status = 'processing'
GROUP BY seller_id, currency;

COMMIT;

外部转账成功后，再通过一个短事务更新状态：

START TRANSACTION;

UPDATE settlement_items
SET status = 'paid',
    provider_transfer_id = 'transfer_abc123',
    paid_at = CURRENT_TIMESTAMP(6)
WHERE id = 9001
  AND status IN ('pending', 'processing');

UPDATE purchases
SET settlement_status = 'settled',
    settled_at = CURRENT_TIMESTAMP(6)
WHERE settlement_batch_id = 501
  AND seller_id = 2001
  AND currency = 'USD'
  AND settlement_status = 'processing';

COMMIT;

实际系统还要检查更新行数，并处理“外部转账已经成功，但本地数据库更新失败”的情况。通常需要借助支付服务商提供的幂等键和转账状态查询接口恢复状态。在结果尚未明确时，不能直接再次付款。

是否应该使用 MySQL Event Scheduler

Event Scheduler 可以定时创建结算任务，但不适合独立承担完整的付款流程，尤其是在流程中需要调用外部转账 API 时。

通常会由应用程序的后台任务或作业队列执行结算，因为这种方式更便于处理：

• 失败重试；
• 幂等控制；
• 超时；
• 日志和告警；
• 人工审核；
• 外部支付接口调用；
• 程序崩溃后的任务恢复。

如果仍要使用 Event Scheduler，建议只让它创建一个“待处理结算批次”，实际的汇总和付款工作交给后台 worker。

不要每月创建新表

每月创建 purchases_2026_07、purchases_2026_08 之类的表，往往会带来更多麻烦：

• 查询跨月数据时，需要动态拼接多个表；
• 外键、索引和结构变更必须同步到所有表；
• 退款可能发生在购买后的任意月份；
• 对账和审计需要跨表处理；
• ORM、报表和运维工具更难使用；
• 不同月份的表容易出现结构不一致。

单表拥有数百万甚至更多记录，并不意味着 MySQL 无法处理。实际容量取决于行宽、索引数量、查询方式、硬件和业务吞吐量，不能只根据记录数作出判断。应先查看执行计划和真实监控数据，确认瓶颈在哪里。

常用的检查方式如下：

EXPLAIN ANALYZE
SELECT seller_id, currency, SUM(seller_amount)
FROM purchases
WHERE settlement_status = 'pending'
  AND created_at >= '2026-07-01 00:00:00'
  AND created_at <  '2026-08-01 00:00:00'
GROUP BY seller_id, currency;

什么时候考虑分区或归档

当交易量很大，而且查询、备份或清理历史数据确实遇到压力时，可以考虑按照 created_at 做月度或季度分区。分区在逻辑上仍是一张表，对应用程序来说，通常比“每月一张表”更容易维护。

MySQL 分区会影响唯一键、外键和表结构设计，相关限制也可能随版本变化。采用分区前，应根据当前使用的 MySQL 版本核对官方文档，并通过实际查询确认是否发生了分区裁剪。

也可以选择更简单的归档方式，例如把超过若干年、不会再参与日常结算的记录迁移到历史表或归档存储。支付记录能否删除以及需要保存多久，取决于所在地法规、税务要求、支付服务商规则和争议处理周期，不能只从数据库性能出发作决定。

NoSQL 是否更合适

通常不合适。

这个业务有明显的关系型特征：

• 余额扣减需要事务；
• 用户、蓝图、购买和结算之间存在关联；
• 系统需要唯一约束和一致性检查；
• 需要按卖家和时间范围聚合；
• 退款和对账必须可靠处理。

这些工作适合关系型数据库。NoSQL 不会自动解决数据增长问题，反而可能让跨记录事务、聚合、约束和对账变得更复杂。

如果以后需要进行大规模分析，可以把交易数据同步到数据仓库或分析系统，MySQL 仍然可以作为核心交易数据库。

还需要注意的事项

• 所有时间建议统一按 UTC 存储，生成结算周期时再按业务时区换算。
• 不要把“已结算”直接等同于“外部转账成功”。系统应保存完整状态和支付服务商编号。
• 退款、拒付和撤销应通过反向记录或独立调整记录处理，不建议覆盖原始交易金额。
• 为同一购买请求添加业务幂等键，防止用户重复点击或网络重试造成重复扣款。
• 结算批次应有唯一约束，避免同一时间区间被重复创建。
• 不同币种必须分别汇总，不能直接相加。
• 定期核对账本、余额、购买记录和支付服务商流水，不能只依赖状态字段。
• 索引可以加快读取，但也会增加写入和存储成本，应根据实际查询保留必要的索引。

基本原则是保留完整的交易历史，通过事务保证购买操作的一致性，再用可重试、可审计且具备幂等性的批次流程完成结算。表持续增长属于容量规划问题，不应通过每月创建新表或直接改用 NoSQL 来回避。

备注：内容仅供参考。