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

日期： 2009-09-17 作者：Denny Cherry翻译：孙瑞 来源：TechTarget中国 英文

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

　　要进一步了解应用情况，你可以创建一个表，里面添加date字段，然后向表中添加一些记录。 　　CREATE TABLE sample_table 　　(id INT IDENTITY(1,1) PRIMARY KEY, 　　dt DATETIME) 　　……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

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

作者

Denny Cherry
Denny Cherry

翻译

孙瑞
孙瑞

相关推荐