datediff是不是会造成全表扫描？

Question

一直在小数据量下进行操作，没什么感觉，最近数据量上到几十万的级别以后，感觉以前用datediff写的一些语句的执行时间都以秒来计了，不能忍受，举个例子：select sum(num) from table1 where datediff(day,create_time,getdate())<=5  and 
datediff(day,create_time,getdate())>=2这样的写法是不是会对table1这个表的所有行的create_time执行datediff后，再取其中<=5的?这样的话的确太慢了，正确的做法应该是怎么样的？我看到有一些替代作法
1）在create_time上建立聚集索引
2) 不使用以上的datediff写法，而用create_time<=dateadd(day,-2,getdate())  and create_time>=dateadd(day,-5,getdate())  
3) 如果只使用1)，然后坚持使用datediff的话，会使聚集索引失去意义，因为执行函数的列将会忽略索引上面的三点说法是正确的么？

Answer 1

1,在这个字段上建聚集索引的话,对这条查询来说,确实有提高,不过适不适合对这个字段做聚集索引,要看你的具体情况, 个人看法:不建议.
2,是的,这种作法是一个技巧, 当create_time上有索引时,使用 datediff对create_time进行计算会造成表扫描,使索引失效, 这种改写的做法是不错的做法.
3,3是指在create_time上建了索引,但是写语句时又对它进行计算从而导致索引失效, 跟我对第2条的描述一样, 第三条是正确的. 个人看法,仅供参考.

Answer 2

有一个说法是说 适合建立聚集索引的列是：“既不能绝大多数都相同，又不能只有极少数相同”，根据这一条，似乎我的表里create_time这一列比较适合，因为在表中会有一群一群一样的create_time出现

Answer 3

我在21W行的数据集上用datediff和>=dateadd这两种写法得出的时间相差无几啊，没有想像中那么大的差距，这是为什么呢？因为数据集还不够大么？

Answer 4

聚集索引是与记录行的物理位置对应的.针对聚集索引来扫描的效率是最高的.我所说的,看你的情况要不要在它上面建:
你在2楼说的是一方面,还要看你的查询语句中过滤时使用到的频率. 另外,在唯一列,及标识列上建聚集索引,那么效率是最高的.
如果你的表中有标识列(假设列名为id), 而且走势与 create_time 相同(即id大的create_time也大), 那么将聚集索引建在id上, 以它来划分区间优先过滤,再在较少的记录里以create_time(普通索引),按第2种写法技巧,那将是不错的选择.

Answer 5

我在21W行的数据集上用datediff和> =dateadd这两种写法得出的时间相差无几啊，没有想像中那么大的差距，这是为什么呢？因为数据集还不够大么？
----------------------------
有建索引吗? 

Answer 6

在createtime列建了聚集索引，查询复杂化之后居然是datediff更高效一些，看来应该是我写的其他地方有问题

Answer 7

比较用的sql语句极简单，就是
select top 10000 * from table1 where create_time>=dateadd(day,-30,getdate())
select top 10000 * from table1 where datediff(day,create_time,getdate())<=30在两个sql语句执行之间会重启sql server以防止有sql缓存影响时间table1中没有唯一标识列，create_time列会有适量重复的的值存在，所以应该比较适合建聚集索引，
建的过程是"企业管理器"中在create_time列在点击右键，新建索引，“升序”，选中“创建为clustered”，不选中“创建unique”,填充因子为"0%"，看起来应该没有什么问题啊

Answer 8

比较用的sql语句极简单，就是
select top 10000 * from table1 where create_time>=dateadd(day,-30,getdate())
select top 10000 * from table1 where datediff(day,create_time,getdate())<=30在两个sql语句执行之间会重启sql server以防止有sql缓存影响时间table1中没有唯一标识列，create_time列会有适量重复的的值存在，所以应该比较适合建聚集索引，
建的过程是"企业管理器"中在create_time列在点击右键，新建索引，“升序”，选中“创建为clustered”，不选中“创建unique”,填充因子为"0%"，看起来应该没有什么问题啊

Answer 9

有聚集索引后，两条语句的差距还是很明显的,看看下面的结果：
200万记录，没有聚集索引时，第一条语句时间：1780MS，第二条语句：683MS
有聚集索引后，第一条语句时间：1610MS（应该是索引失效），第二条语句：20MS（索引的效果出来了）
注：建聚集索引花了我近20分钟，1MS＝1毫秒＝0.001秒
declare @d1 datetime,@d2 datetime,@d3 datetimeselect '记录总数:'+rtrim(count(*))+'  rq 字段没建建聚集索引 ' from t_lager
set @d1 = getdate()
select min(greed),max(greed),count(*) from t_lager where datediff(day,rq,getdate()) <=7000 and  
datediff(day,rq,getdate())> =6640
set @d2 = getdate()
select min(greed),max(greed),count(*) from t_lager where
rq <=dateadd(day,-6640,getdate())  and rq> =dateadd(day,-7000,getdate())   
set @d3 = getdate()
select datediff(ms,@d1,@d2),datediff(ms,@d2,@d3),datediff(ms,@d1,@d3)
/*
--------------------------------------------- 
记录总数:2000001  rq 字段没建聚集索引（所影响的行数为 1 行）----------- ----------- ----------- 
1           89          8901（所影响的行数为 1 行）                                    
----------- ----------- ----------- 
1           89          8901（所影响的行数为 1 行）                                    
----------- ----------- ----------- 
1780        683         2463（所影响的行数为 1 行）*/
select '记录总数:'+rtrim(count(*))+'  rq 字段建了聚集索引后 ' from t_lager
set @d1 = getdate()
select min(greed),max(greed),count(*) from t_lager where datediff(day,rq,getdate()) <=7000 and  
datediff(day,rq,getdate())> =6640
set @d2 = getdate()
select min(greed),max(greed),count(*) from t_lager where
rq <=dateadd(day,-6640,getdate())  and rq> =dateadd(day,-7000,getdate())   
set @d3 = getdate()
select datediff(ms,@d1,@d2),datediff(ms,@d2,@d3),datediff(ms,@d1,@d3)
/*
--------------------------------------------- 
记录总数:2000001  rq 字段建了聚集索引后 （所影响的行数为 1 行）                                    
----------- ----------- ----------- 
1           89          8901（所影响的行数为 1 行）                                    
----------- ----------- ----------- 
1           89          8901（所影响的行数为 1 行）                                    
----------- ----------- ----------- 
1610        20          1630（所影响的行数为 1 行）
*/