奇怪为什么此语句在查询分析器执行计划里面显示的是index scan,而不是index seek?
select * from objectbaseinfo where objecttype =102如果objecttype 等于其他数字,执行计划都是index seek,就是等于102不行,为什么啊?------附表结构和索引-------
CREATE TABLE [ObjectBaseInfo] (
[ObjectID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ObjectCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomID] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Custo__6AEFE058] DEFAULT (''),
[Name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ShortName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[EName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
[EShortName] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ObjectType] [int] NULL ,
[OwnerID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Owner__6BE40491] DEFAULT (''),
[HeadID] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[Enable] [int] NOT NULL CONSTRAINT [DF__ObjectBas__Enabl__6CD828CA] DEFAULT (1),
[Address] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Addre__6DCC4D03] DEFAULT (''),
[ZipNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[WWW] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
[Email] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[FaxNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkTele] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkMan] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[MobileNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[WorkArea] [int] NOT NULL CONSTRAINT [DF__ObjectBas__WorkA__6EC0713C] DEFAULT (1),
[WorkerTotal] [int] NOT NULL CONSTRAINT [DF__ObjectBas__Worke__6FB49575] DEFAULT (1),
[OpenDate] [datetime] NOT NULL CONSTRAINT [DF__ObjectBas__OpenD__70A8B9AE] DEFAULT (getdate()),
[Operator] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [char] (64) COLLATE Chinese_PRC_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[ObjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---------
create index i_objectbaseinfo on objectbaseinfo(objecttype)
---------
select * from objectbaseinfo where objecttype =102如果objecttype 等于其他数字,执行计划都是index seek,就是等于102不行,为什么啊?------附表结构和索引-------
CREATE TABLE [ObjectBaseInfo] (
[ObjectID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ObjectCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomID] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Custo__6AEFE058] DEFAULT (''),
[Name] [varchar] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ShortName] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[EName] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
[EShortName] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[ObjectType] [int] NULL ,
[OwnerID] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Owner__6BE40491] DEFAULT (''),
[HeadID] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[Enable] [int] NOT NULL CONSTRAINT [DF__ObjectBas__Enabl__6CD828CA] DEFAULT (1),
[Address] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__ObjectBas__Addre__6DCC4D03] DEFAULT (''),
[ZipNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[WWW] [varchar] (64) COLLATE Chinese_PRC_CI_AS NULL ,
[Email] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[FaxNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkTele] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL ,
[LinkMan] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[MobileNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[WorkArea] [int] NOT NULL CONSTRAINT [DF__ObjectBas__WorkA__6EC0713C] DEFAULT (1),
[WorkerTotal] [int] NOT NULL CONSTRAINT [DF__ObjectBas__Worke__6FB49575] DEFAULT (1),
[OpenDate] [datetime] NOT NULL CONSTRAINT [DF__ObjectBas__OpenD__70A8B9AE] DEFAULT (getdate()),
[Operator] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [char] (64) COLLATE Chinese_PRC_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[ObjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---------
create index i_objectbaseinfo on objectbaseinfo(objecttype)
---------
---计划选用了主键索引
-- (所影响的行数为 2190 行)
-- 表 'ObjectBaseInfo'。扫描计数 1,逻辑读 100 次,物理读 0 次,预读 0 次。select * from objectbaseinfo with (index (i_objectbaseinfo)) where objecttype =1
---计划选用了主键索引,吓人,4531次读取!!!为什么选择这个索引差距如此大?
-- (所影响的行数为 2190 行)
-- 表 'ObjectBaseInfo'。扫描计数 1,逻辑读 4531 次,物理读 0 次,预读 0 次。--参考
select objecttype,count(*) from objectbaseinfo group by objecttype
-- objecttype
-- ----------- -----------
-- 0 1
-- 1 29
-- 2 5
-- 21 41
-- 31 2076
-- 100 144
-- 101 16
-- 102 2190
-- 122 1
-- 130 156
-- (所影响的行数为 10 行)
2、select *的时候如果不走聚集索引必然会有book lookup操作的,这种操作在数据量大的时候也是很费IO的。就你那2190行的记录做book lookup的话至少2190+次逻辑IO,加上index seek的操作的话明显比你全表扫描的100次IO(逻辑读 100 次)高了n倍。要完全明白的话好好的google下我回答里面出现的那些你不明白的东东
返回大量数据时:聚集索引 -> 表扫描 -> 非聚集索引
返回少量数据时:取集索引 -> 非聚集索引 -> 表扫描
我无能为力,已经超出我的理解范围了
如果变成了index seek
说明之前优化器认为返回的结果集过多,使用索引的成本已经超过了不使用索引的成本(看2楼关键字:成本)