在索引范围扫描的情况下，整数列索引比字符串列索引快吗？

Question

我在SQL Server上有一个数据库实现任务，其中有一个包含列- yearMonth的表A。我现在不需要日期操作，例如计算两个日期之间的天数或月数等。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，可以输入的无意义的可能性是无穷的。

既然您已经对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;

Answer 2

Int应该比Varchar(6)更快，因为它占用的空间更少。

​

SQL SELECT speed int vs varchar

​

Answer 3

但从索引的角度看呢，尤指。万一索引范围扫描？

范围扫描速度不受索引类型的限制，但其有效性受到分段.Your范围扫描查询速度的限制，如果分段较少，则碎片越少，就意味着所有页面都是相邻的，而不是分散的。