我对一些索引测试了一下,发现我开始建一复合索引,查询时要花60毫秒的时间,当我把复合索引删除,然后改为建两个单独的索引,发现使用两个单独的索引来查询,所用的时间居然是 3 毫秒而已,请问是正常吗?--这是后来要删除的复合索引 drop index ix_age_second_name on dbo.users create index ix_age_second_name on dbo.users(age,second_name) --下面是两个单独的索引 drop index ix_age_second on dbo.users create index ix_age_second on dbo.users(second_name)drop index ix_age_age on dbo.users create index ix_age_age on dbo.users(age) declare @startdate datetime,@enddate datetime; set @startdate=getdate(); select * from users where second_name='427' and age>22 set @enddate=getdate(); print('SQL语句执行的时间(ms):'+convert(varchar(200),datediff(ms,@startdate,@enddate)))
我也想,不用复合索引了,单个单个的用算了,而且有些查询条件会用到 or 的 还有一下inner join 麻烦啊
drop index ix_age_second_name on dbo.users
create index ix_age_second_name on dbo.users(age,second_name)
--下面是两个单独的索引
drop index ix_age_second on dbo.users
create index ix_age_second on dbo.users(second_name)drop index ix_age_age on dbo.users
create index ix_age_age on dbo.users(age)
declare @startdate datetime,@enddate datetime;
set @startdate=getdate();
select * from users where second_name='427' and age>22
set @enddate=getdate();
print('SQL语句执行的时间(ms):'+convert(varchar(200),datediff(ms,@startdate,@enddate)))