PAYATHON 2026

SQL Server 中按客户存储每日日期资格是否合理？

AI 小顾

结论

可以继续按“客户 + 日期”逐行存储资格。约 2800 万行对 SQL Server 2014 来说不算异常规模。只要键足够窄，索引符合查询方式，并按月份增量结算，就不必因为行数较多而马上改用日期区间。

日期区间虽然能节省空间，却会增加区间合并、拆分、重叠检测、历史更正和按月计算的复杂度。业务一旦涉及付款和审计，简单明确、能直接回答“某客户在某天是否符合资格”的每日事实表通常更可靠。

现阶段可以优先做以下优化：

  1. 用整数 AccountId 代替重复存储的 NVARCHAR(20)；
  2. 保留 (AccountId, DiscountDate) 作为唯一聚集键；
  3. 每次只计算和汇总目标日期或目标月份；
  4. 建立月度结算或汇总表，避免月底反复扫描全部历史数据；
  5. 根据实际查询和执行计划，决定是否增加日期优先的辅助索引。

为什么数千万行本身不是问题

关系数据库本来就适合处理大量窄行。这个表中的每一行都表达一个明确事实：

某个账户在某个日期符合 discount 资格。

DATE 只占 3 字节。账户键改成 INT 后，业务数据部分只有 7 字节。实际空间还包括行头、页结构、索引，以及可能存在的非聚集索引，但这仍然是一张很窄的表。

真正影响扩展能力的并不是行数本身，而是以下问题：

  • 聚集索引是否符合主要查询方式；
  • 每个月是否都要扫描从 2014 年开始的全部数据；
  • 索引是否过多或过宽；
  • 日常写入是否逐行执行；
  • 历史数据是否经常修改；
  • 统计信息和索引是否得到正常维护；
  • 数据文件、日志文件和存储设备能否承受持续增长。

如果目前查询速度和写入情况都很稳定，未来几年的数据增长也可以预测，就没必要仅仅因为“理论上行数会很多”而引入更复杂的数据模型。

先缩窄账户键

AccountNumber 是业务标识，可能比较长，也可能随外部系统规则变化。更合适的做法是将它保存在账户表中，事实表只保存整数键。

CREATE TABLE dbo.Account
(
    AccountId     INT IDENTITY(1, 1) NOT NULL,
    AccountNumber NVARCHAR(20) NOT NULL,

    CONSTRAINT PK_Account
        PRIMARY KEY CLUSTERED (AccountId),

    CONSTRAINT UQ_Account_AccountNumber
        UNIQUE (AccountNumber)
);

每日资格表可以改为：

CREATE TABLE dbo.DiscountDay
(
    AccountId   INT  NOT NULL,
    DiscountDate DATE NOT NULL,

    CONSTRAINT PK_DiscountDay
        PRIMARY KEY CLUSTERED (AccountId, DiscountDate),

    CONSTRAINT FK_DiscountDay_Account
        FOREIGN KEY (AccountId)
        REFERENCES dbo.Account (AccountId)
);

这样做有几个直接好处：

  • 聚集键从上限 40 字节的 NVARCHAR(20) 缩短为 4 字节的 INT；
  • 账户编号只需在账户表中保存一次；
  • 如果存在非聚集索引，其叶级记录携带的聚集键也会更窄；
  • 账户编号变化时，不必更新数百万条资格记录。

不建议仅为事实表增加一个没有业务含义的 IDENTITY 主键，然后再维护 (AccountId, DiscountDate) 唯一索引。这通常只会多出一棵索引，而该表的自然唯一标识就是账户和日期。

如果账户数量以后可能超过 INT 的范围，可以改用 BIGINT。以当前规模来看，没有必要提前增加键宽度。

采用集合方式写入，并保证可重复执行

每日任务应该一次插入当天所有符合资格的账户，不要为每个客户单独执行一次插入。

下面的代码只展示写入方式，资格来源需要替换为实际业务查询：

DECLARE @DiscountDate DATE = '2026-09-01';

INSERT dbo.DiscountDay
(
    AccountId,
    DiscountDate
)
SELECT
    e.AccountId,
    @DiscountDate
FROM dbo.EligibleAccount AS e
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.DiscountDay AS d
    WHERE d.AccountId = e.AccountId
      AND d.DiscountDate = @DiscountDate
);

主键是防止重复数据的最后保障，NOT EXISTS 则便于控制批处理重跑。

如果任务可能并发执行，应通过调度系统保证同一业务日期只有一个实例运行，或者在事务和错误处理层面明确处理唯一键冲突。在所有并发场景下，“先判断不存在，再插入”都不能代替唯一约束。

月底不要累计扫描全部历史

如果每月付款只关心当月新增的资格天数，查询应限定日期范围：

DECLARE @MonthStart     DATE = '2026-08-01';
DECLARE @NextMonthStart DATE = '2026-09-01';

SELECT
    AccountId,
    COUNT_BIG(*) AS EligibleDays
FROM dbo.DiscountDay
WHERE DiscountDate >= @MonthStart
  AND DiscountDate <  @NextMonthStart
GROUP BY AccountId;

使用半开区间 [MonthStart, NextMonthStart)，可以避开各月天数不同和时间边界带来的问题。即使该列以后从 DATE 改成 DATETIME2，这种写法也不容易出错。

如果系统经常查询“累计已获得多少 discount”，就不必每次重新统计全部每日记录。可以建立月度汇总或结算表：

CREATE TABLE dbo.DiscountMonth
(
    AccountId       INT           NOT NULL,
    MonthStart      DATE          NOT NULL,
    EligibleDays    SMALLINT      NOT NULL,
    DiscountAmount  DECIMAL(19, 4) NOT NULL,
    CalculatedAt    DATETIME2(0)  NOT NULL,
    PaidAmount      DECIMAL(19, 4) NOT NULL,

    CONSTRAINT PK_DiscountMonth
        PRIMARY KEY CLUSTERED (AccountId, MonthStart),

    CONSTRAINT FK_DiscountMonth_Account
        FOREIGN KEY (AccountId)
        REFERENCES dbo.Account (AccountId),

    CONSTRAINT CK_DiscountMonth_EligibleDays
        CHECK (EligibleDays >= 0)
);

每日表保留可审计的明细，月度表保存结算结果。这样既能保留日级证据，也不需要为了显示账户余额而反复扫描多年的历史数据。

涉及实际付款时，还应保存计算时使用的费率、规则版本、计算时间和调整记录。否则规则发生变化后，历史付款金额可能难以解释。

索引顺序应由查询方式决定

(AccountId, DiscountDate) 适合下面这类查询：

SELECT COUNT_BIG(*)
FROM dbo.DiscountDay
WHERE AccountId = @AccountId
  AND DiscountDate >= @StartDate
  AND DiscountDate <  @EndDate;

它也适合快速判断某个客户在某天是否符合资格。

如果系统经常查询“某一天所有符合资格的客户”，可能还需要日期优先的索引：

CREATE INDEX IX_DiscountDay_DiscountDate_AccountId
ON dbo.DiscountDay (DiscountDate, AccountId);

不过，不要只凭猜测创建索引。额外索引会增加磁盘占用、日志量和每日写入成本。应先检查最重要的查询、实际执行计划和逻辑读取量。

也可以将聚集键改为 (DiscountDate, AccountId)。这种顺序有利于按日、按月写入和读取，但会降低单个账户跨日期查询的数据局部性。没有一种固定顺序能在所有场景下都达到最优。

日期区间模型何时更合适

如果资格通常连续数年，状态很少变化，而且主要数据本来就是带生效日期的供应关系，那么日期区间可能更符合业务事实：

CREATE TABLE dbo.DiscountEligibilityPeriod
(
    AccountId       INT  NOT NULL,
    StartDate       DATE NOT NULL,
    EndDateExclusive DATE NOT NULL,

    CONSTRAINT PK_DiscountEligibilityPeriod
        PRIMARY KEY CLUSTERED (AccountId, StartDate),

    CONSTRAINT FK_DiscountEligibilityPeriod_Account
        FOREIGN KEY (AccountId)
        REFERENCES dbo.Account (AccountId),

    CONSTRAINT CK_DiscountEligibilityPeriod_DateRange
        CHECK (StartDate < EndDateExclusive)
);

建议将结束日期定义为不包含的上界。例如，资格覆盖 2026-08-10 至 2026-08-13，可以保存为：

StartDate        = 2026-08-10
EndDateExclusive = 2026-08-14

某个月份与区间重叠的天数可以按以下方式计算：

DATEDIFF
(
    DAY,
    CASE
        WHEN StartDate > @MonthStart
            THEN StartDate
        ELSE @MonthStart
    END,
    CASE
        WHEN EndDateExclusive < @NextMonthStart
            THEN EndDateExclusive
        ELSE @NextMonthStart
    END
)

查询还需要先排除没有交集的区间：

WHERE StartDate < @NextMonthStart
  AND EndDateExclusive > @MonthStart

问题是，SQL Server 2014 没有简单的声明式约束，可以直接保证同一账户的所有区间互不重叠。业务处理还必须正确应对以下情况：

  • 新区间与旧区间相连时需要合并；
  • 在已有区间中间取消一天时，需要拆成两个区间；
  • 补录历史资格时，可能需要合并多个区间；
  • 更正开始日期或结束日期；
  • 并发修改时需要防止区间重叠；
  • 月份只覆盖部分区间时，需要计算交集；
  • 重新计算已经完成的月份，并生成调整款。

这些处理会直接影响财务结果。为了节省磁盘空间而承担这些维护成本，未必划算。

更稳妥的折中方案

一种比较实用的设计是保留三层数据：

  1. 供应关系及其生效时间，作为原始业务事实；
  2. DiscountDay，作为根据规则生成的日级资格事实；
  3. DiscountMonth 和付款流水，作为结算事实。

如果原始供应数据足够完整，理论上可以重新生成 DiscountDay。但在财务系统中，通常仍有必要保留已经用于结算的日级结果，尤其是在规则可能变化或源数据可能被追溯修改的情况下。

也可以根据数据生命周期，归档已经结算且很少访问的年份。分区有助于按月或按年归档、维护和切换数据，但不会自动提高查询速度。SQL Server 2014 可以使用哪些相关功能，还取决于具体版本和许可。等到系统确实出现维护窗口、归档或超大索引管理问题时，再考虑引入分区。

需要持续观察的指标

不要只根据行数判断是否需要重构。更值得定期检查的是：

  • 表和索引的实际占用空间；
  • 每月新增行数及未来增长速度；
  • 月度结算的运行时间；
  • 查询的逻辑读取量和执行计划；
  • 数据文件与事务日志的增长情况；
  • 索引碎片和统计信息状态；
  • 备份、恢复及维护窗口；
  • 历史更正是否越来越频繁。

只要这些指标仍处于可接受范围，按日存储就是合理的设计。如果将来的瓶颈是重复统计，可以增加月度汇总；如果瓶颈是账户编号过宽，可以改用整数外键；如果问题出在历史维护和归档，再考虑分区。只有当业务事实本来就适合用少量连续区间表达，并且团队能够可靠处理区间变更时，才值得将日期区间作为主要存储模型。

备注：内容仅供参考。