这个有”Seek谓词字样“:
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
这样就没有”Seek谓词字样“:
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
如果没有 Seek 谓词 字样,索引查找用上了吗?
15 个解决方案
#1
另外问一下,由于必须用到 AND 和 OR 的组合,导致索引查询失效,我可以用 UNION ALL 分开写成2次查询,问题是:
用 UNION AL 分开2次查询速度快,还是舍弃部分索引速度快(我用的复合索引,至少前3个是可以用上的)?
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
#7
确实,这次有谓词了,不过为什么FromHour索引没有用上?
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
CREATE TABLE [dbo].[t](
[id] [int] IDENTITY(1,1) NOT NULL,
[a] [int] NOT NULL,
[b] [bit] NOT NULL,
[c] [tinyint] NOT NULL,
[d] [int] NOT NULL,
[h] [int] NOT NULL,
[m] [int] NOT NULL,
[LimitRange] [bit] NOT NULL,
[FromHour] [int] NOT NULL,
[ToHour] [int] NOT NULL,
[LastTime] [datetime] NULL,
CONSTRAINT [PK_t] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleEnabled] DEFAULT ((0)) FOR [b]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleMode] DEFAULT ((0)) FOR [c]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleDays] DEFAULT ((0)) FOR [d]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleHours] DEFAULT ((0)) FOR [h]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleMinutes] DEFAULT ((0)) FOR [m]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleLimitTimeRange] DEFAULT ((0)) FOR [LimitRange]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleFromHour] DEFAULT ((0)) FOR [FromHour]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleToHour] DEFAULT ((23)) FOR [ToHour]
GO
declare @count int = 1;
while @count < 1000
begin
insert into t (a, b, c, LimitRange)
values (1, 1, 1, 1)
set @count = @count + 1
end
#9
确实,这次有谓词了,不过为什么FromHour索引没有用上?
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
#7
确实,这次有谓词了,不过为什么FromHour索引没有用上?
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour
CREATE TABLE [dbo].[t](
[id] [int] IDENTITY(1,1) NOT NULL,
[a] [int] NOT NULL,
[b] [bit] NOT NULL,
[c] [tinyint] NOT NULL,
[d] [int] NOT NULL,
[h] [int] NOT NULL,
[m] [int] NOT NULL,
[LimitRange] [bit] NOT NULL,
[FromHour] [int] NOT NULL,
[ToHour] [int] NOT NULL,
[LastTime] [datetime] NULL,
CONSTRAINT [PK_t] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleEnabled] DEFAULT ((0)) FOR [b]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleMode] DEFAULT ((0)) FOR [c]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleDays] DEFAULT ((0)) FOR [d]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleHours] DEFAULT ((0)) FOR [h]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleMinutes] DEFAULT ((0)) FOR [m]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleLimitTimeRange] DEFAULT ((0)) FOR [LimitRange]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleFromHour] DEFAULT ((0)) FOR [FromHour]
GO
ALTER TABLE [dbo].[t] ADD CONSTRAINT [DF_t_ScheduleToHour] DEFAULT ((23)) FOR [ToHour]
GO
declare @count int = 1;
while @count < 1000
begin
insert into t (a, b, c, LimitRange)
values (1, 1, 1, 1)
set @count = @count + 1
end
#9
确实,这次有谓词了,不过为什么FromHour索引没有用上?
--这个有“Seek谓词”
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 0
--现在都有“Seek谓词”,但为什么FromHour索引没有用上?
SELECT * FROM t WHERE a = 1 AND b = 1
AND c = 0
AND d = DAY(GETDATE()) AND h = DATEPART(HH, GETDATE()) AND m = DATEPART(MI, GETDATE())
AND LimitRange = 1
AND FromHour <= DATEPART(HH, GETDATE()) AND DATEPART(HH, GETDATE()) <= ToHour