至少从一开始,这是一个抽象的问题。但我正在寻找关于为什么我会经历我正在经历的事情的理论。
我有一个包含许多列的表,其中一列是允许为 null 的 Int。让我们将其称为NullableIntCol
。
我们在表上有一些非聚集索引,其中一些包括此处的列。
然后,我们以各种方式查询表,有些地方我们这样做WHERE NullableIntCol is null
,当我们这样做时,我们发现查询可能需要 5-10 秒才能执行。公平地说,我们需要优化一些东西。
在我们的测试环境中,我们已经能够复制该问题。虽然我们这里谈论的是1-3秒。但图案一模一样。
从 Datawise 来看,该表大约有 600.000 行,大约 350MB 的数据。
在我们尝试了各种索引组合等之后,我发现如果我在表上运行以下命令:
BEGIN TRANSACTION;
UPDATE [dbo].[MyTable]
SET [NullableIntCol] = -1
WHERE [NullableIntCol] is null;
UPDATE [dbo].[MyTable]
SET [NullableIntCol] = null
WHERE [NullableIntCol] = -1;
COMMIT TRANSACTION;
突然之间,查询性能好多了。(在我们的测试环境中为 50ms-200ms)
这是执行上述操作之前的查询计划。
https://www.brentozar.com/pastetheplan/?id=H1VCvT9uh
估计的:
执行:
这是更改后的查询计划。
https://www.brentozar.com/pastetheplan/?id=HJXMu69_2
估计的:
执行:
这显然是一个变化。但我仍然缺乏“为什么”。
更远。
然后我可以将数据库恢复到之前的状态并再次复制问题。
最初我认为上面的内容可能几乎触发了索引的完全重建,但是重建索引似乎没有太大效果(如果有的话)。
所以现在,我不知道为什么我会看到这种行为。
更新2023-06-29
运行UPDATE STATISTICS [Scehma1].[Object1];
上面的查询后,它似乎又回到了错误的查询计划。执行UPDATE FLIP -1 <==> null
我看到性能改进的地方,似乎即更新该特定列的一些统计信息,运行后UPDATE STATISTICS
更新所有统计信息,这似乎使我们回到原始查询计划。
我已经能够通过弄乱 Index1 来重现这两个查询计划。并发现以下内容。
任何更改之前的计划:开发环境中约 2 秒。 https://www.brentozar.com/pastetheplan/?id=BJoclpcun Index1 = 下面的 Index1 Index2 = PK 指数
如果我禁用上面使用的索引(下面的 Index1),我会得到以下计划: https://www.brentozar.com/pastetheplan/?id=BkrTzp5d2 Index1 = Index2 below Index2 = PK Index
最后,如果我将最初使用的索引(下面的 Index1)更改为:
CREATE NONCLUSTERED INDEX [Index1] ON [Schema1].[Object1] ( [Column4] ASC, [Column9] ASC )
所以切换列的顺序。然后我得到:
https://www.brentozar.com/pastetheplan/?id =r16QNpcd3
既然我实际上正在对索引进行更改,那么我更改计划就更有意义了,但问题仍然存在,为什么它似乎在一开始就选择了这样一个次优计划,而不是仅仅进行表扫描显然更快。当我开始在顶部添加分页和排序时,我会明白
现在很明显,随着时间的推移,这些索引已经变得一团糟,我认为,我们很快就发现了这一点,但对我来说奇怪的是,更新语句的执行发生了如此大的变化。现在也许已经有了答案
表模式
尝试将其与 Sentry Plan Explorer 的输出对齐,但可能不是 100%。它与旧计划并不相符,因为我认为没有必要将所有内容都匿名化,但哨兵计划只是将其全部删除。
CREATE TABLE [Schema1].[Object1](
[Column1] [int] IDENTITY(1,1) NOT NULL,
[Column2] [datetime2](7) NOT NULL,
[Column3] [varchar](256) NOT NULL,
[Column4] [int] NULL,
[Column5] [varchar](256) NULL,
[Column6] [varchar](256) NULL,
[Column7] [varchar](max) NOT NULL,
[Column8] [datetime2](7) NOT NULL,
[Column9] [int] NOT NULL,
[Column10] [varchar](256) NOT NULL,
[Column11] [datetime2](7) NOT NULL,
[Column12] [nvarchar](max) NULL,
[Column13] [nvarchar](max) NULL,
[Column14] [bit] NOT NULL,
[Column15] [bit] NOT NULL,
CONSTRAINT [PK_Object1] PRIMARY KEY CLUSTERED ( [Column1] 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];
ALTER TABLE [Schema1].[Object1] ADD DEFAULT ((0)) FOR [Column14]
ALTER TABLE [Schema1].[Object1] ADD DEFAULT ((0)) FOR [Column15]
ALTER TABLE [Schema1].[Object1] WITH CHECK ADD CONSTRAINT [FK_Column9]
FOREIGN KEY([Column9])
REFERENCES [Schema1].[Object2] ([Column1])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Schema1].[Object1] CHECK CONSTRAINT [FK_Column9]
--
CREATE NONCLUSTERED INDEX [Index0] ON [Schema1].[Object1] ( [Submitted] 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]
-- This Index is the one that goes in and out of our plans, whenever it's used it seems performance is tanked.
CREATE NONCLUSTERED INDEX [Index1] ON [Schema1].[Object1] ( [Column9] ASC, [Column4] 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]
-- This has clearly not been updated as it should since all selects always includes all columns, so I guess that if it should make any sense to include what is currently there, 13,14 and 15 should be added as well
-- Not sure it has anything to do with the current problem though.
CREATE NONCLUSTERED INDEX [Index2] ON [Schema1].[Object1] ( [Column9] ASC )
INCLUDE([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7],[Column8],[Column10],[Column11],[Column12])
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]
CREATE NONCLUSTERED INDEX [Index3] ON [Schema1].[Object1] ( [Column8] 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]
两个计划之间的区别在于,在第一个(慢)计划中,Sql Server 预计只有 20 行满足 WHERE 子句,并且它决定使用索引查找,然后在主表中进行查找。事实证明,至少有 1000 行满足该子句,并且由于 1000 次查找,该计划效率非常低。
在第二个计划中,Sql Server 正确估计至少需要检索 1000 行,因此它决定进行全表扫描(在从表中读取 2328 行后,它发现了 1000 行符合条件的行)。
Sql Server 有一项功能,可以在修改高比例的行时自动更新表的统计信息。正如您所说,由于大约 90% 的行包含值 "NULL",所以所有这些行的更新都触发了该表统计信息的自动更新,从而允许 Sql 服务器选择更好的计划。
您可以通过发出 UPDATE STATISTICS 命令而不是 UPDATE 块来测试这一点:
编辑:也有可能在第一个查询中没有可用的统计信息,并且 Sql 服务器在更新后创建了它们。如果 AUTO_CREATE_STATISTICS 选项打开,则查询优化器会在谓词中使用的各个列上创建统计信息(如果这些统计信息尚不可用)。因此,更新可能会触发 [NullableIntCol] 上的统计信息的创建,并且以下查询将找到并使用它们。
在这种情况下,您可以通过以下方式获得相同的结果:
发布执行计划后进行更新。
请注意,您发布的原始查询有
TOP 1000
限制,而查询计划中不存在该限制。对要检索的总行数的限制对优化器将选择的查询计划有很大影响,因此您正在将苹果与橘子进行比较。此外,仅当您指定 an 时, a
TOP 1000
才有意义ORDER BY
,这对优化器也有很大影响。尽管如此,计划清楚地表明,总共 569158 行中,有 251959 行满足 WHERE 条件。这是总行的 44%。如果您必须检索表的很大一部分,则任何索引查找都没有任何好处,因此最好的计划始终是表扫描。您可以受益于覆盖索引(其中包括您选择的所有列),但在您的示例中,您选择表的所有列,这样的索引将是整个表的重复。
在这种情况下,SQL Server 永远不应该选择索引查找,除非它错误地估计满足 WHERE 条件的行的百分比要小得多。拥有适当且最新的统计数据
Column4
应该Column9
避免这种情况。但是,如果您应用
ORDER BY
和TOP 1000
限制,则 上的索引Column4
以及Column9
中指定的任何其他列都ORDER BY
可以通过允许仅读取前 1000 行然后停止来加快速度。如果索引包含选择中所需的所有列,这可能会更快。另请注意,如果您甚至需要索引中未包含的单个列,则 sql server 将必须对表中的每一行进行查找。因此,如果您选择 15 列,则包含 12 列是没有用的,它只会使索引更大且速度更慢。要么包含所有需要的内容,要么不包含任何内容。
看来这主要是因为这两个计划本质上都是糟糕的计划。一个进行全面扫描,另一个进行多次查找。你要求服务器在进退两难之间做出选择。
更改索引之一以正确支持查询
两个键列的顺序可以任意。
将聚集索引更改为这两列可能有意义,具体取决于您还有哪些其他查询。主键不必是聚簇键。
Index1
看起来几乎没用,除非您只查询这两列,或者您正在对 中的第一个列进行范围查找Index2
,因为Index2
现在涵盖相同的键列。我建议你放弃它。