在进行索引优化时,我们总会遇到一些难以处理的数据类型,而其中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′) |