sql-server - 在子表上使用 IN () 删除 2 行创建的匹配行数为 1.97 亿行

Question

为什么IN()在子表上使用删除 2 行会创建 1.97 亿行的哈希匹配，此查询在应用程序中超时，但使用=or删除 1 行IN()工作正常并立即完成？

执行计划和查询：DELETE FROM dbo.ClientDepartmentContact WHERE ClientDepartmentContactId IN (12126,12127) https://www.brentozar.com/pastetheplan/?id=rkKueM6HS

从 dbo.ClientDepartmentContact 中删除 ClientDepartmentContactId = 12126 https://www.brentozar.com/pastetheplan/?id=Sku2lGaBr

主/外键关系未设置为级联。

DDL：

 CREATE TABLE [dbo].[ClientDepartmentContact](
    [ClientDepartmentContactId] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [int] NOT NULL,
    [DepartmentId] [smallint] NOT NULL,
    [DispatchContactId] [int] NOT NULL,
    [DispatchType] [char](1) NOT NULL,
    [DispatchContactType] [char](1) NOT NULL,
 CONSTRAINT [PK_ClientDepartmentContact] PRIMARY KEY CLUSTERED 
(
    [ClientDepartmentContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_ClientDepartmentContact] UNIQUE NONCLUSTERED 
(
    [ClientId] ASC,
    [DepartmentId] ASC,
    [DispatchContactId] ASC,
    [DispatchType] ASC,
    [DispatchContactType] 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

--Index
ALTER TABLE [dbo].[ClientDepartmentContact] ADD  CONSTRAINT [PK_ClientDepartmentContact] PRIMARY KEY CLUSTERED 
(
    [ClientDepartmentContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientDepartmentContact] ADD  CONSTRAINT [UK_ClientDepartmentContact] UNIQUE NONCLUSTERED 
(
    [ClientId] ASC,
    [DepartmentId] ASC,
    [DispatchContactId] ASC,
    [DispatchType] ASC,
    [DispatchContactType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



CREATE TABLE [dbo].[ClientDepartmentContact](
    [ClientDepartmentContactId] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [int] NOT NULL,
    [DepartmentId] [smallint] NOT NULL,
    [DispatchContactId] [int] NOT NULL,
    [DispatchType] [char](1) NOT NULL,
    [DispatchContactType] [char](1) NOT NULL,
 CONSTRAINT [PK_ClientDepartmentContact] PRIMARY KEY CLUSTERED 
(
    [ClientDepartmentContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_ClientDepartmentContact] UNIQUE NONCLUSTERED 
(
    [ClientId] ASC,
    [DepartmentId] ASC,
    [DispatchContactId] ASC,
    [DispatchType] ASC,
    [DispatchContactType] 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

--Index
ALTER TABLE [dbo].[ClientDepartmentContact] ADD  CONSTRAINT [PK_ClientDepartmentContact] PRIMARY KEY CLUSTERED 
(
    [ClientDepartmentContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientDepartmentContact] ADD  CONSTRAINT [UK_ClientDepartmentContact] UNIQUE NONCLUSTERED 
(
    [ClientId] ASC,
    [DepartmentId] ASC,
    [DispatchContactId] ASC,
    [DispatchType] ASC,
    [DispatchContactType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DispatchContact](
    [DispatchContactId] [int] IDENTITY(1,1) NOT NULL,
    [Email] [varchar](254) NOT NULL,
 CONSTRAINT [PK_DispatchContact] PRIMARY KEY CLUSTERED 
(
    [DispatchContactId] 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

CREATE UNIQUE NONCLUSTERED INDEX [IX_ContactAddress_Email] ON [dbo].[DispatchContact]
(
    [Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DispatchContact] ADD  CONSTRAINT [PK_DispatchContact] PRIMARY KEY CLUSTERED 
(
    [DispatchContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SQL 版本 2012 标准，所有统计信息都是最新的。

在寻求帮助之前，我已将数据库恢复到 2014 年和 2017 年（均为标准）开发实例，并且无法重新创建执行计划，查询会立即执行。2017 年的计划是： https ://www.brentozar.com/pastetheplan/?id=BJdpX76rH

统计信息每晚通过维护工作更新，但我在调查问题时确实更新了所有统计信息。我曾尝试使用 SentryOnes Plan explorer 索引分析工具添加索引，但无济于事。

ps 谢谢你的帮助

标签： sql-server