Sql2005数据库主键A 聚族索引字段B、C建立索引我在查询时 select * from 表 where B='值' and C='值',通过执行计划发现,他走的事聚族索引,没有走B C字段建立的索引于是我强制索引 select * from 表 with(index(索引名)) where B='值' and C='值',通过执行计划发现,聚族索引开销为96,B、C字段索引开销为2%不知道为啥呢??
调试欢乐多
select * from 表 with(index(索引名)) where B='值' and C='值'
CREATE TABLE A (a int,b int, c int)
create clustered index i_a on A(a)
create nonclustered index i_bc on A(b,c)set statistics profile onselect * from A where b=1 and c=5/*SELECT * FROM [A] WHERE [b]=@1 AND [c]=@2
|--Index Seek(OBJECT:([master].[dbo].[A].[i_bc]), SEEK:([master].[dbo].[A].[b]=CONVERT_IMPLICIT(int,[@1],0) AND [master].[dbo].[A].[c]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
*/
select a,b,c from A where b=1 and c=5
/*
SELECT [a],[b],[c] FROM [A] WHERE [b]=@1 AND [c]=@2
|--Index Seek(OBJECT:([master].[dbo].[A].[i_bc]), SEEK:([master].[dbo].[A].[b]=CONVERT_IMPLICIT(int,[@1],0) AND [master].[dbo].[A].[c]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
*/
select b,C from A where b=1 and c=5
/*
SELECT [b],[C] FROM [A] WHERE [b]=@1 AND [c]=@2
|--Index Seek(OBJECT:([master].[dbo].[A].[i_bc]), SEEK:([master].[dbo].[A].[b]=CONVERT_IMPLICIT(int,[@1],0) AND [master].[dbo].[A].[c]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)
*/
select * from A with (index(i_a)) where b=1 and c=5
/*
select * from A with (index(i_a)) where b=1 and c=5
|--Clustered Index Scan(OBJECT:([master].[dbo].[A].[i_a]), WHERE:([master].[dbo].[A].[b]=(1) AND [master].[dbo].[A].[c]=(5)))
*/
select * from A with(index(i_bc)) where b=1 and c=5
/*
select * from A with(index(i_bc)) where b=1 and c=5
|--Index Seek(OBJECT:([master].[dbo].[A].[i_bc]), SEEK:([master].[dbo].[A].[b]=(1) AND [master].[dbo].[A].[c]=(5)) ORDERED FORWARD)
*/