SQL Server - 使用聚集索引时如何存储数据页

Question

ahmed elbarbary

Asked: 2022-02-25 13:43:54 +0800 CST2022-02-25 13:43:54 +0800 CST 2022-02-25 13:43:54 +0800 CST

对于少量更新的行，如何提高更新速度？

772

我正在使用 SQL Server 2019 并面临一个问题，即在进行更新时，仅 162 行需要 20 秒。

Update FT SET
    ft.ValueName=avo.name
FROM #package FT 
inner join parts.Nop_PackageAttribute PA with(nolock)
    on PA.PackageID=ft.PackageID
        and PA.[Key]=FT.ZfeatureId 
inner join Nop_AcceptedValuesOption AVO with(nolock)
    ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value 
where FT.AcceptedValueID is not null

脚本示例

 create table #package
 (
 id int PRIMARY KEY IDENTITY(1,1),
 ZfeatureId INT NULL,
 AcceptedValueID INT NULL,
 PackageID INT NULL,
 ValueName NVARCHAR(2000) default ''
 )

我在#package 表上的索引

 create nonclustered index IDX_PackageID on #package(PackageID) include (ZfeatureId,AcceptedValueID , ValueName)
 create index acceptedvaluesidpackage_idx on #package(AcceptedValueID)

包属性表

ALTER TABLE [Parts].[Nop_PackageAttribute] ADD  CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] 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 [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_PackageAttribute_8_578153155__K2_K1_K3_4] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] ASC,
    [PackageAttributeID] ASC,
    [Key] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]

CREATE NONCLUSTERED INDEX [IDX_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] 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) ON [Customer]


CREATE NONCLUSTERED INDEX [IDX_PakageID] ON [Parts].[Nop_PackageAttribute]
(
    [PackageID] 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) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [IX_Nop_PackageAttribute_Key] ON [Parts].[Nop_PackageAttribute]
(
    [Key] ASC
)
INCLUDE (   [PackageID],
    [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]


CREATE TABLE [Parts].[Nop_PackageAttribute](
    [PackageAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [PackageID] [int] NOT NULL,
    [Key] [int] NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
 CONSTRAINT [PK_Nop_PackageAttribute] PRIMARY KEY CLUSTERED 
(
    [PackageAttributeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer] TEXTIMAGE_ON [PRIMARY]

Nop_AcceptedValuesOption 表

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] 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 [Customer]
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [_dta_index_Nop_AcceptedValuesOption_5_1669580986__col__] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID],
    [AcceptedValuesID],
    [Name],
    [DisplayOrder],
    [Description],
    [CreatedDate],
    [CreatedBy],
    [ModifiedDate],
    [ModifiedBy],
    [DeletedDate],
    [DeletedBy],
    [Is_Split],
    [AcceptedValuesOption_Value],
    [AcceptedValuesOption_Unit]
)WITH (DROP_EXISTING = OFF) ON [Customer]

CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesOptionID] ASC
)
INCLUDE (   [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Nop_AcceptedValuesOption_8_1074154922__K2_K4_1_3] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] ASC,
    [DisplayOrder] ASC
)
INCLUDE (   [AcceptedValuesOptionID],
    [Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160824-070515] ON [dbo].[Nop_AcceptedValuesOption]
(
    [AcceptedValuesID] 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) ON [Customer]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160829-015901] ON [dbo].[Nop_AcceptedValuesOption]
(
    [Name] 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) ON [Customer]
GO

CREATE TABLE [dbo].[Nop_AcceptedValuesOption](
    [AcceptedValuesOptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AcceptedValuesID] [int] NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [varchar](250) NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedBy] [int] NULL,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [DeletedDate] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [Is_Split] [int] NULL,
    [AcceptedValuesOption_Value] [float] NULL,
    [AcceptedValuesOption_Unit] [nvarchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_Nop_AcceptedValuesOption] PRIMARY KEY CLUSTERED 
(
    [AcceptedValuesOptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
) ON [Customer]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] ADD  CONSTRAINT [DF_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Nop_AcceptedValuesOption] CHECK CONSTRAINT [FK_Nop_AcceptedValuesOption_Nop_AcceptedValues]
GO

那么，如何增强更新语句更快呢？

3 个回答

Voted

David Browne - Microsoft · Answer 1 · 2022-02-25T16:38:03+08:00

Best Answer

David Browne - Microsoft

2022-02-25T16:38:03+08:002022-02-25T16:38:03+08:00

添加到布伦丹的答案，交换这个转换

inner join  Nop_AcceptedValuesOption AVO with(nolock) 
ON convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value

至

inner join  Nop_AcceptedValuesOption AVO 
ON AVO.AcceptedValuesOptionID = try_cast(PA.Value as int)

并且您应该能够用 162 个索引查找替换非聚集列存储扫描。如果您没有获得嵌套循环计划，请尝试inner loop join.

10

Brendan McCaffrey · Answer 2 · 2022-02-25T14:51:34+08:00

您应该避免在WHERE子句中围绕列包装函数。通过环绕 a CONVERT，AcceptedValuesOptionIDSQL Server 必须在表中的每一行上运行该函数，然后才能知道值是否等于PA.Value。

话虽如此，这很可能是您WHERE条款中导致您的缓慢的部分。

convert(varchar(20),AVO.AcceptedValuesOptionID)=PA.Value

我看到发生了两件事。

正在使用非聚集索引扫描检索此表中的数据。
然后执行哈希匹配，然后将其与其他两个表的结果结合起来。

看看是否有另一种方法可以连接到该表，其中连接两侧的列是相同的数据类型。

另一种选择可能是更改上的数据类型AVO.AcceptedValuesOptionID。但是，更改现有表中的数据类型是有风险的。您需要确保在此过程中不会破坏其他查询。

此外，如果您知道始终PA.value可以包含 INT 数据的特定场景，您可以考虑创建一个临时表并将这些行子集插入到临时表中。您将使用该列作为 INT 数据类型创建此临时表，然后在原始联接中使用临时表，而不是基表。

AVee · Answer 3 · 2022-02-26T09:42:36+08:00

AVee

2022-02-26T09:42:36+08:002022-02-26T09:42:36+08:00

这看起来像我过去被咬过的一个问题，我仍然有伤疤。

尝试这个：

convert(nvarchar(20),AVO.AcceptedValuesOptionID)=PA.Value

事情是这样的，PA.Value 是n vachar，但是您将 AVO.AcceptedValuesOptionID 转换为 varchar。这会导致 SQL Server 在比较之前首先将每个 PA.Value 转换为 varchar。这是一个昂贵的转换，因为它需要考虑非 ASCII 字符的各种等效规则。

（其他答案中的建议可能仍会进一步提高性能，但我强烈怀疑这是罪魁祸首。）

-1

对于少量更新的行，如何提高更新速度？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

对于少量更新的行，如何提高更新速度？

3 个回答

相关问题