同一列的多个过滤索引
问题描述:
根据不同条件为同一列创建多个过滤索引是否合理?例如,假设以下内容:同一列的多个过滤索引
CREATE TABLE [dbo].[Skills](
[UserId] INT NOT NULL PRIMARY KEY,
[SkillTitle] NVARCHAR(50) NOT NULL,
[SkillLevel] INT NOT NULL CHECK ([SkillLevel] BETWEEN 0 AND 10)
)
GO
CREATE INDEX [IX_Skill_Low] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 3 AND 0
GO
CREATE INDEX [IX_Skill_Moderate] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 5 AND 3
GO
CREATE INDEX [IX_Skill_Good] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel BETWEEN 7 AND 5
GO
CREATE INDEX [IX_Skill_High] ON [dbo].[Skill] (SkillTitle,SkillLevel) WHERE SkillLevel > 7
或者创建单个非过滤索引会更好吗?
如果进行以下查询,会发生什么SELECT * FROM Skills WHERE SkillLevel BETWEEN 7 AND 1
?
答
除非您有数百万记录用于每种技能等级的变化。
即,
在5至7
在5和3
我不会建议,我建议建立像下面
skilllevel between 1 and 10
是怎样的一个单一过滤指数如果进行以下查询,则会发生SELECT * FROM技能WHERE SkillLevel BETWEEN 7 AND 1?
如果如果你select *
包含许多列以外,其在指数没有specfied,SQL可能
- 选择使用该索引和做基表查找的列的其余
- 做一个全表扫描
我会创造一个下方覆盖指数,但是这取决于你的数据
create index nci_test on table(skilllevel)
include(SkillTitle)
我做了一些测试,以了解更多,你可以找到下面的测试脚本
CREATE TABLE [dbo].[Skills](
[UserId] INT NOT NULL identity(1,1) PRIMARY KEY,
[SkillTitle] NVARCHAR(50) NOT NULL,
[SkillLevel] INT NOT NULL CHECK ([SkillLevel] BETWEEN 0 AND 10)
)
GO
insert into dbo.Skills
(SkillTitle,SkillLevel)
select cast(NEWID() as varchar(50)),
case when rand()*10<0 then 1 else rand()*10 end
go 100000
现在让我们运行一些查询
create index nci_1and5 on dbo.skills(skilllevel)
include(skilltitle)
where skilllevel >= 1 and skilllevel <=5
create index nci_5and10 on dbo.skills(skilllevel)
include(skilltitle)
where skilllevel >5 and skilllevel <=10
select * from skills where skilllevel
between 1 and 4--uses 1 to 5 index
select * from skills where skilllevel
between 6 and 10--uses 6 to 10 index
select * from skills where skilllevel
between 1 and 7--uses scan
但是我不推荐这些指标,即使你有百万行之间的技能levels.I将建议分区
“如果您的选择*包含很多列以外的索引中没有被指定”我不明白这部分,你能解释一下吗?简单吗?它是'除了哪个没有指定'或'哪个是'??无法理解那部分。例如 – Arrrr
(对你的表不适用),如果你的表中有列a,b,c,d,并且你的索引就像'on dbo.table(a,b)',那么你的'select *'查询可能无法利用这个指数 – TheGameiswar
我不知道这一点,谢谢提及。如果您示例中的“c”列是主键或唯一索引,该怎么办? – Arrrr