PAYATHON 2026

SQL Server Datetime 与 Int key 性能比较

支付小周

结论

如果“财政期间”是稳定、明确的业务维度，而且大量查询需要按财政期间汇总或筛选，建议采用以下设计：

  • 保留 PaymentDate，记录交易发生时间；
  • 建立财政期间表；
  • 在支付表中保存 FiscalPeriodID 外键；
  • 根据常用查询建立合适的复合索引。

这种设计通常比每次根据日期范围推导财政期间更清楚，也可能更快。不过，性能差异主要取决于索引、数据分布、查询覆盖率和执行计划。不能因为 int 比 datetime 更容易比较，就认定性能一定会提升一个数量级。

如果财政期间只是自然月、自然季度等可以直接根据日期确定的简单分类，而且按期间查询并不频繁，那么只保存日期，再使用可索引的范围条件，通常就够了。

为什么不能只比较 datetime 和 int

就单次比较而言，整数比较通常更简单，索引键也更窄。但面对数百万行数据，真正影响性能的通常是：

  • 查询能否使用索引查找；
  • 需要读取多少行和多少数据页；
  • 索引是否覆盖查询所需的列；
  • 条件的选择性如何；
  • 是否需要关联其他表；
  • 数据是否按日期或期间分区；
  • 统计信息是否准确。

下面两个条件都可以是 SARGable 条件，也就是能够利用普通 B-tree 索引：

WHERE FiscalPeriodID = @FiscalPeriodID
WHERE PaymentDate >= @StartDate
  AND PaymentDate <  @EndDate

只要 PaymentDate 上有合适的索引，日期范围查询本身并不低效。同样，如果 FiscalPeriodID 没有索引，把日期条件改成整数条件也不会自动变快。

推荐的数据模型

财政年度经常不同于自然年度，还可能采用 4-4-5 周规则，或者包含调整期、第 13 期间等特殊安排。把这些规则放在独立的期间表中，比在每条查询里重复计算更可靠。

CREATE TABLE dbo.FiscalPeriod
(
    FiscalPeriodID int NOT NULL
        CONSTRAINT PK_FiscalPeriod PRIMARY KEY,

    FiscalYear smallint NOT NULL,
    PeriodNumber tinyint NOT NULL,
    StartDate date NOT NULL,
    EndDateExclusive date NOT NULL,

    CONSTRAINT UQ_FiscalPeriod_Year_Period
        UNIQUE (FiscalYear, PeriodNumber),

    CONSTRAINT CK_FiscalPeriod_DateRange
        CHECK (StartDate < EndDateExclusive)
);

支付表同时保存日期和期间标识：

CREATE TABLE dbo.Payment
(
    PaymentID bigint NOT NULL
        CONSTRAINT PK_Payment PRIMARY KEY,

    PaymentDate datetime2(0) NOT NULL,
    FiscalPeriodID int NOT NULL,
    Amount decimal(19, 4) NOT NULL,

    CONSTRAINT FK_Payment_FiscalPeriod
        FOREIGN KEY (FiscalPeriodID)
        REFERENCES dbo.FiscalPeriod (FiscalPeriodID)
);

EndDateExclusive 表示结束边界不属于当前期间。例如，2026 年 1 月可以定义为：

StartDate        = 2026-01-01
EndDateExclusive = 2026-02-01

这样可以避开“当天最后一毫秒”的问题，也不受不同时间精度的影响。

索引怎么建

如果查询主要按财政期间汇总金额，可以建立一个以 FiscalPeriodID 开头的索引：

CREATE INDEX IX_Payment_FiscalPeriodID
ON dbo.Payment (FiscalPeriodID)
INCLUDE (Amount, PaymentDate);

典型查询如下：

SELECT
    FiscalPeriodID,
    SUM(Amount) AS TotalAmount
FROM dbo.Payment
WHERE FiscalPeriodID = @FiscalPeriodID
GROUP BY FiscalPeriodID;

如果查询主要使用日期范围，则建立日期索引：

CREATE INDEX IX_Payment_PaymentDate
ON dbo.Payment (PaymentDate)
INCLUDE (Amount, FiscalPeriodID);

查询时使用半开区间：

SELECT
    SUM(Amount) AS TotalAmount
FROM dbo.Payment
WHERE PaymentDate >= @StartDate
  AND PaymentDate <  @EndDateExclusive;

如果经常需要先限定财政期间，再按日期筛选，可以使用复合索引：

CREATE INDEX IX_Payment_FiscalPeriodID_PaymentDate
ON dbo.Payment (FiscalPeriodID, PaymentDate)
INCLUDE (Amount);

索引列的顺序要根据实际查询决定。(FiscalPeriodID, PaymentDate) 适用于先限定期间、再限定日期的查询。如果查询通常只有日期条件，这个索引不能完全替代以 PaymentDate 开头的索引。

不要这样写日期条件

不要对索引列执行函数：

WHERE YEAR(PaymentDate) = 2026

这种写法通常无法直接对 PaymentDate 执行有效的索引范围查找。可以改成：

WHERE PaymentDate >= '20260101'
  AND PaymentDate <  '20270101'

也不建议使用 BETWEEN 表示包含时间的整日范围：

WHERE PaymentDate BETWEEN '20260101' AND '20260131'

在 SQL Server 中，日期字符串 '20260131' 表示当天零点，因此这个条件不会包含 1 月 31 日白天的大部分记录。即使手动写成 23:59:59.997，结果仍会受具体数据类型的时间精度影响。

更安全的写法是：

WHERE PaymentDate >= '20260101'
  AND PaymentDate <  '20260201'

参数也应使用正确的日期类型，不要先把列转换成字符串再比较。

如何保证期间标识和日期一致

同时保存 PaymentDate 和 FiscalPeriodID 是有意保留的数据冗余，风险在于两者可能不一致。例如，日期属于第 3 期间，记录却写成了第 4 期间。

写入时可以先根据日期查找对应期间：

DECLARE @PaymentDate datetime2(0) = @InputPaymentDate;
DECLARE @FiscalPeriodID int;

SELECT @FiscalPeriodID = fp.FiscalPeriodID
FROM dbo.FiscalPeriod AS fp
WHERE @PaymentDate >= fp.StartDate
  AND @PaymentDate <  fp.EndDateExclusive;

IF @FiscalPeriodID IS NULL
    THROW 50001, 'No fiscal period is defined for the payment date.', 1;

INSERT dbo.Payment
(
    PaymentID,
    PaymentDate,
    FiscalPeriodID,
    Amount
)
VALUES
(
    @PaymentID,
    @PaymentDate,
    @FiscalPeriodID,
    @Amount
);

还要确保各财政期间没有重叠，并且覆盖所有允许录入的日期。普通 CHECK 约束无法跨多行检查日期范围是否重叠，通常要通过受控存储过程、触发器或应用服务来维护。

是否允许日后调整财政期间，也需要事先确定：

  • 如果期间归属必须随日历规则变化，应在规则调整后重新计算历史记录。
  • 如果已过账交易必须保留当时的会计归属，就不能只根据当前日期规则重新推导。
  • 如果存在关闭期间后的调整分录，财政期间可能是独立的会计属性，而不只是日期的派生值。

在后两种情况下，保存 FiscalPeriodID 不只是为了提高性能，也是为了保存业务事实。

只保存日期的适用场景

只保存 PaymentDate，查询时再关联期间表，同样可以保持模型规范化：

SELECT
    fp.FiscalPeriodID,
    SUM(p.Amount) AS TotalAmount
FROM dbo.Payment AS p
JOIN dbo.FiscalPeriod AS fp
  ON p.PaymentDate >= fp.StartDate
 AND p.PaymentDate <  fp.EndDateExclusive
WHERE fp.FiscalPeriodID = @FiscalPeriodID
GROUP BY fp.FiscalPeriodID;

不过，这里使用的是范围关联，而不是简单的等值外键关联，因此执行计划和维护方式通常不如直接保存 FiscalPeriodID 简单。这种方案更适合以下情况：

  • 财政期间可以完全根据日期推导；
  • 期间定义可能经常调整；
  • 按期间查询并不频繁；
  • 希望避免重复存储及其一致性问题。

还可以先从期间表中取得日期边界，再直接查询支付表：

DECLARE @StartDate date;
DECLARE @EndDateExclusive date;

SELECT
    @StartDate = StartDate,
    @EndDateExclusive = EndDateExclusive
FROM dbo.FiscalPeriod
WHERE FiscalPeriodID = @FiscalPeriodID;

SELECT SUM(Amount)
FROM dbo.Payment
WHERE PaymentDate >= @StartDate
  AND PaymentDate <  @EndDateExclusive;

只要 PaymentDate 有索引，这种方案在大型数据集上也可能表现很好。

数百万行数据时的选择

对于拥有数百万行数据，并且经常生成财政期间报表的财务系统，一个实用方案是同时保存：

PaymentDate + FiscalPeriodID

这样既能保留准确的交易时间，也能明确记录稳定的会计期间归属。实施时应注意：

  1. 使用外键维护期间引用。
  2. 在写入路径中统一计算或验证期间。
  3. 根据实际查询建立索引，不要为所有可能的组合都创建索引。
  4. 使用 >= 和 < 表示日期范围。
  5. 使用实际数据、参数和代表性查询检查执行计划与逻辑读取量。

可以用下面的方式比较候选查询，但结果只适用于当前的数据分布、索引和硬件环境：

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 日期范围方案
SELECT SUM(Amount)
FROM dbo.Payment
WHERE PaymentDate >= @StartDate
  AND PaymentDate <  @EndDateExclusive;

-- 财政期间方案
SELECT SUM(Amount)
FROM dbo.Payment
WHERE FiscalPeriodID = @FiscalPeriodID;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

比较时应关注执行计划、逻辑读取次数和返回行数，而不只是某一次运行的耗时。生产环境中还要考虑缓存状态、参数嗅探、并发写入和索引维护成本。

如果财政期间是稳定而重要的业务属性，保存 FiscalPeriodID 通常更合适。如果它只是日期的一种简单展示方式，那么索引良好的日期范围查询已经足够。设计时应先保证会计语义正确，再根据实际执行计划判断是否值得增加冗余字段和索引。

备注：内容仅供参考。