我的桌子是这样的:
CREATE TABLE [dbo].[ClosedTaskCustomFields](
[ClosedTaskId] [uniqueidentifier] NOT NULL,
[CustomFieldId] [uniqueidentifier] NOT NULL,
[Value] [nvarchar](450) NULL
...
CONSTRAINT [PK_ClosedTaskCustomFields] PRIMARY KEY CLUSTERED
(
[ClosedTaskId] ASC,
[CustomFieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
我也有这样的索引用于连接 ClosedTask 表:
CREATE NONCLUSTERED INDEX [IX_ClosedTaskCustomFields_ClosedTaskId] ON [dbo].[ClosedTaskCustomFields]
(
[ClosedTaskId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
但我不明白为什么这样的查询会读取所有 ClosedTaskCustomFields 表以加入 ClosedTask(如果我有索引)。
select count(1) from ClosedTaskCustomFields ctcf
join ClosedTask c on c.id = ctcf.ClosedTaskId
where c.State = 'Rejected'
-- Result count is 50k.