sql - 在索引范围扫描的情况下，整数列索引是否比字符串列索引更快？

Question

我在 SQL Server 上有一个数据库实现任务，那里有一张 table A包含一列 - yearMonth .我不需要日期操作，例如计算两个日期之间的天数或月数等。 yearMonth 可以定义为 Date , Int , 或 varchar(6) .从节省数据空间的角度来看，4字节Int显然是最好的选择，因为只需要 6 位整数，例如201701 .而varchar(6)占用6个字节，Date占用 2x4 字节。 (适用于大多数数据库)

但是从索引的角度来看呢，尤其是。在索引范围扫描的情况下？

如列yearMonth定义为 varchar(6) , 使用查询 select .. from A where yearMonth IN (...) 时可能会发生索引范围扫描

如列yearMonth定义为 Int或 Date ，然后索引范围扫描可以使用像 <= 这样的运算符进行, <=等

在上述情况下，当发生索引范围扫描时，哪种类型的列定义更有效？

Answer 1

无论如何，大多数(如果不是全部)DBMS 本质上都将日期存储为整数，而对于 DateTime，它是两个整数，一个用于日期，一个用于时间，因此两者之间几乎没有区别。我认为您最大的考虑是您打算如何使用该列，如果您想对该列进行任何类型的日期操作，则将其存储为日期(默认为该月的第一天)。例如，如果您想知道 201604 之间有多少个月和 201701如果您想将您的值格式化为类似 April 2017 的格式，使用日期会更容易。如果将其存储为日期会容易得多。

另一个考虑因素是验证，如果您有 varchar(6) 或 int，则需要额外的检查约束以确保输入的任何值实际上都是有效日期，任何人都可以轻松输入 999999 ，虽然年份有效，但月份无效，对于 varchar，可以输入的无意义的可能性是无穷无尽的。

现在您已经标记了 SQL Server，我可以更明确地回答 - 两个 DATE和 INT占用 4 个字节的存储空间，因此没有节省空间，并且从测试来看，两者的执行情况几乎完全相同(日期执行情况略有不同，但没有明显更好，而且读取次数通常更少)，因此使用 int 没有任何好处(除非您不想仅限于有效日期)

我使用以下模式进行了一些快速测试:

CREATE TABLE dbo.TDate (ID INT IDENTITY(1, 1) PRIMARY KEY, DT DATE NOT NULL);
INSERT dbo.TDate (DT)
SELECT TOP 100000 DATEADD(MONTH, RAND(CHECKSUM(NEWID())) * 300, '20000101')
FROM sys.all_objects a, sys.all_objects b;

CREATE NONCLUSTERED INDEX IX_TDate_DT ON dbo.TDate (DT);

CREATE TABLE dbo.TInt(ID INT IDENTITY(1, 1) PRIMARY KEY, DT INT NOT NULL);
INSERT dbo.TInt (DT)
SELECT (DATEPART(YEAR, DT) * 100) + DATEPART(MONTH, DT)
FROM dbo.TDate;

CREATE NONCLUSTERED INDEX IX_TInt_DT ON dbo.TInt (DT);

然后运行它来比较性能

DECLARE @D1 DATE = (SELECT TOP 1 DT FROM dbo.TDate ORDER BY NEWID());
DECLARE @D2 DATE = (SELECT TOP 1 DT FROM dbo.TDate WHERE DT > @D1 ORDER BY NEWID());
DECLARE @I1 INT = (DATEPART(YEAR, @D1) * 100) + DATEPART(MONTH, @D1),
        @I2 INT = (DATEPART(YEAR, @D2) * 100) + DATEPART(MONTH, @D2);


SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT  COUNT(*)
FROM    dbo.TDate
WHERE   DT >= @D1
AND     DT < @D2;

SELECT  COUNT(*)
FROM    dbo.TInt
WHERE   DT >= @I1
AND     DT < @I2;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;