索引优化时如何处理Date数据类型

在进行索引优化时，我们总会遇到一些难以处理的数据类型，而其中datetime可以说是这些中最难的。使用到datetime数据类型的查询过滤只是想对日期部分进行匹配。大多数人是通过一个转换函数来进行此操作。然而这将造成索引无效的问题，因为转换函数会使索引被扫描和不是被查找。

　　要进一步了解应用情况，你可以创建一个表，里面添加date字段，然后向表中添加一些记录。

CREATE TABLE sample_table  　　(id INT IDENTITY(1,1) PRIMARY KEY,  　　dt DATETIME)  　　go  　　CREATE INDEX ix_sample_table ON dbo.sample_table  　　(dt)  　　go  　　INSERT INTO sample_table  　　(dt)  　　SELECT ‘1/1/2000 15:00:00′  　　UNION  　　SELECT ‘1/2/2000 12:00:00′  　　UNION  　　SELECT ‘1/3/2000 08:00:00′  　　go

　　你现在可以对dt列进行查询并使用转换函数了。当查看执行计划时，你可以看到一个索引扫描正在使用。

SELECT id, dt  　　FROM sample_table  　　WHERE CONVERT(varchar(10), dt, 101) = ‘01/01/2000′

　　但是如果你使用BETWEEN运算来代替=运算时，可以看到引擎使用了索引查找代替了索引扫描。

SELECT id, dt  　　FROM sample_table  　　WHERE dt BETWEEN ‘01/01/2000′ AND ‘01/02/2000′

　　在表中对datetime列使用其它函数的情况也是一样。比如你有一个系统，人们想要根据时区进行搜索，数据以UTC 时间形式存储，但是用户想要根据自己的时区进行搜索。

　　实现在这一搜索最简单的方法是在dt字段上使用dateadd函数。然而即使是使用BETWEEN运算，你还是要使用索引扫描，而不是索引查找。

SELECT id, dt  　　FROM sample_table  　　WHERE dateadd(mi, -420, dt) BETWEEN ‘01/01/2000′ AND ‘01/02/2000′

　　现在，如果你将-420切换至420，然后在BETWEEN运算中对date使用DATEADD函数，索引查找将被保持。查询也许看上去有些陌生，但这样也OK。这就是我们想要的最终结果。

SELECT id, dt  　　FROM sample_table  　　WHERE dt BETWEEN dateadd(mi, 420, ‘01/01/2000′) AND dateadd(mi, 420, ‘01/02/2000′)

　　现在如果你想要使用UtcOffset，是存在另外的表中，比如Account表。我们将重新创建样表。

CREATE TABLE sample_table  　　(id INT IDENTITY(1,1) PRIMARY KEY,  　　dt DATETIME,  　　AccountId int)  　　CREATE TABLE Account  　　(AccountId INT PRIMARY KEY,  　　UtcOffset INT)  　　go  　　CREATE INDEX ix_sample_table ON dbo.sample_table  　　(dt)  　　INCLUDE (AccountId)  　　go  　　INSERT INTO sample_table  　　(dt, AccountId)  　　SELECT ‘1/1/2000 15:00:00′, 1  　　UNION  　　SELECT ‘1/2/2000 12:00:00′, 2  　　UNION  　　SELECT ‘1/3/2000 08:00:00′, 1  　　INSERT INTO Account  　　SELECT 1, -420  　　UNION  　　SELECT 2, -300  　　SELECT id, dt  　　FROM sample_table  　　JOIN Account ON sample_table.AccountId = Account.AccountId  　　WHERE dt BETWEEN dateadd(mi, Account.UtcOffset*-1, ‘01/01/2000′) AND dateadd(mi, Account.UtcOffset*-1, ‘01/02/2000′)