主页 / dba / 问题 / 228876
Accepted
WadeH
Asked: 2019-02-05 10:53:48 +0800 CST

对相当大的表 (2m) 上的错误查询的索引帮助

  • 772

我正在寻找一些帮助。我有一个针对相当大的表（200 万条记录）运行的查询。

我一直在努力让索引有效地工作。还有一些针对此表的其他查询，但这是迄今为止最常见的查询。我非常努力让它在 1 秒内执行，并且经常看到它在 3 到 5 秒内使用分析器运行。

它可能会尽可能快，但我希望能提供一些输入来确认/拒绝。

请注意：Dev 根本不会更改查询或模式。只能在数据库中进行优化，而不能更改模式。

桌子：

CREATE TABLE [dbo].[Notifications](
[ntID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NotificationID] [int] NOT NULL,
[NotificationType] [nvarchar](50) NOT NULL,
[UserName] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL,
[CreatedOn] [datetime] NULL,
[Status] [nvarchar](50) NOT NULL,
[Result] [nvarchar](50) NULL,
[Extension] [nvarchar](50) NULL,
[ShiftRate] [nvarchar](255) NULL,
[ResponseMinutes] [int] NULL,
[ResponseWindow] [datetime] NULL,
[caNotificationID] [int] NULL,
[AwardedBy] [nvarchar](50) NULL,
[AwardedOn] [datetime] NULL,
[CancelledBy] [nvarchar](50) NULL,
[CancelledOn] [datetime] NULL,
[CancelledReasonID] [int] NULL,
[CancelledReasonText] [nvarchar](255) NULL,
[AwardingDate] [datetime] NULL,
[ScheduledLaunchDate] [datetime] NULL,
[CustomMessage] [nvarchar](160) NULL,
[SystemName] [nvarchar](4000) NULL,
[AutoClose] [bit] NOT NULL,
 CONSTRAINT [PK_ESP_Notifications_ntID] PRIMARY KEY CLUSTERED 
(
    [ntID] 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].[ESP_Notifications] ADD  DEFAULT ((0)) FOR [AutoClose]
GO

表数据快照：

示例数据的屏幕截图

查询：

Update Notifications 
  set Status = 'Awarding' OUTPUT deleted.* 
where ntID = (
    select top(1) ntID 
    from Notifications 
    where NotificationType = 'Shift' 
      and (Status = 'Done') 
      and ResponseWindow < '2019-02-04 10:40:03' 
    order by ntID)

尝试索引：

CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID] 
ON [dbo].[Notifications](
   [Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC
)

CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID] 
ON [dbo].[Notifications](
    [ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC
)

CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID] 
ON [dbo].[Notifications](
   [NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC
);

NotificationType 包含 3 种不同的类型，其中 70% 是 'Shift' 类型 Status 有 10 种类型，但 'In Flight' 记录只有大约 100 到 200 条，分为 4 个 Status 的

谢谢您的帮助。

sql-server index

1 个回答

  1. Best Answer

    如果该更新中的子查询始终使用这两个谓词值，则过滤索引应该会有很大帮助。像这样的东西（Erik Darling亲切地提供了评论）：

    CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow) 
INCLUDE (NotificationType, Status) 
WHERE (Status = 'Done' AND NotificationType = 'Shift');

    这将使子查询找到与Statusand匹配的相关 ID 组NotificationType（因为它们是索引中唯一的行）并避免对数据进行排序（因为它已经由 ntID 作为索引中的前导列排序）。

    它仍然必须检查每一行的值ResponseWindow。在最坏的情况下，如果没有满足查询日期要求的行（或者如果第一个匹配项具有非常高的 ntID），则将读取大部分或全部索引。这使得这种方法的有效性受到数据分布的限制。

    • 15

相关问题