聚集索引遇到的性能问题(查询分析器试验)

[softlife12]

背景:做的是userId+sdate的聚集索引，userId在前
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'

后由于发现性能很差，便在查询分析器中做实验

当实现
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-2')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-3')=0 and userId='01'

也即变化sdate，保持userId不变时，性能很好，

而当
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='02'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='03'
性能却慢了一倍多的时间

如果在where中把sdate和userId的位置对调也是这样

由于我在实际中的例子是按照
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='02'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='03'

来的，所以只能上来问下大家，谢谢

[wangdehao]

计算列无法使用索引,可以改成这样:

select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'

<===> select * from test where sdate>'2006-1-1' and sdate<'2006-1-2' and userid='01'
or: select * from test where sdate between '2006-1-1' and '2006-1-2' and userid='01'

[zjcxc]

datediff(day,sdate,'2006-1-1')=0 不能用索引扫描
而 userId='01' 可以使用索引

所以你和复合索引不太有效.


你的语句其实可以改为下面的, 这样索引就充分利用上了:
select * from test
where userId='01'
and sdate>='2006-1-1'
and sdate<'2006-1-2'

[zjcxc]

另, sql server 不会在意思你的查询条件中, 各个条件的顺序, 它始终会自动优化的