代码之家 › 专栏 › 技术社区 › Merlin Nestler

为什么索引中的列顺序在这种情况下会产生如此大的影响?

indexing performance sql-server

Merlin Nestler · 技术社区 · 3 周前

我最近更改了表上的索引,发现一个查询的性能损失很大。我想知道为什么会发生这种情况。

这就是查询。外键就像 From 和 To 正在发生变化,但其余的会保持不变,并经常重复。

SELECT COUNT(*) 
FROM Table_With_Values 
WHERE FK_ObjectTheValuesBelongTo_Id = 460 
AND [From]>=CONVERT([datetime2](3),'07.10.2024 00:00:00',(104)) 
AND [To]<=CONVERT([datetime2](3),'08.10.2024 00:00:00',(104))

首先是表的索引 TableWithValues 看起来像这样:

CREATE NONCLUSTERED INDEX [Idx_TableWithValues_Fk_ObjectTheValuesBelongTo_Id_From_To] ON [dbo].[TableWithValues]
(
    [Fk_ObjectTheValuesBelongTo_Id] ASC,
    [From] ASC,
    [To] 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 documentation 并看到了这一点:

如果索引包含多个列,请考虑列的顺序柱。在WHERE子句中使用的列等于 (=)、大于(>)、小于(<)或在搜索条件之间,或参加加盟,应该放在第一位。 其他栏目应根据其清晰度进行排序,即从从最明显到最不明显 .

这个 从 和 致 列有大约140000个不同的值,而外键列只有1600个。因此,我决定改变它们的顺序,使索引看起来像这样:

CREATE NONCLUSTERED INDEX [Idx_TableWithValues_From_To_Fk_ObjectTheValuesBelongTo_Id] ON [dbo].[TableWithValues]
(
    [From] ASC,
    [To] ASC,
    [Fk_ObjectTheValuesBelongTo_Id] 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]

然而,这导致了此查询的性能下降。执行需要1000倍的时间。我知道这一点,因为我在数据库的副本上激活了查询存储。然后,我运行了发送查询的IT系统,并检查了中的值 avg_duration 桌子的 query_store_runtime_stats .

表格本身看起来像这样:

CREATE TABLE [dbo].[TableWithValues](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Fk_ObjectTheValuesBelongTo_Id] [int] NOT NULL,
    [Value] [decimal](9, 3) NOT NULL,
    [From] [smalldatetime] NOT NULL,
    [To] [smalldatetime] NOT NULL,
 CONSTRAINT [Pk_TableWithValues_Id] PRIMARY KEY NONCLUSTERED 
(
    [Id] 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],
 CONSTRAINT [Uq_TableWithValues_ObjectTheValuesBelongTo_Id_From] UNIQUE NONCLUSTERED 
(
    [Fk_ObjectTheValuesBelongTo_Id] ASC,
    [From] 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]
GO

ALTER TABLE [dbo].[TableWithValues]  WITH CHECK ADD  CONSTRAINT [Fk_TableWithValues_ObjectTheValuesBelongTo_Id] FOREIGN KEY([Fk_ObjectTheValuesBelongTo_Id])
REFERENCES [dbo].[ObjectTheValuesBelongTo] ([Id])
GO

具有这两个索引的运行的查询计划看起来几乎相同。有两个不同之处。他们使用不同的索引(隐藏在屏幕截图中),在一种情况下,百分比为13%和87%(Fk在索引中排名第一),在另一种情况中,百分比为20%和80%(Fk排在索引的最后)。

知道吗,为什么我的数据库在阅读文档时表现得不像人们预期的那样?

1 回复 | 直到 3 周前

Charlieface 3 周前

你错过了那一段的其余部分,它间接地解释了 真实的 最好的索引方式。

我添加了编号,这样你就可以看到它想说什么:

如果索引包含多列,请考虑列的顺序。中使用的列 WHERE 第条:

等于(=)

大于(>)、小于(<)或介于搜索条件之间

或参与加入

应该放在第一位。

其他列应根据其清晰度进行排序,即从最明显到最不明显。

所以在你的情况下, FK_ObjectTheValuesBelongTo_Id 平等是第一位的。 无论它有多明显。 即使它是一个 bit 列,如果它是相等的,它会排在第一位。

在我看来,关于其他列(不等式、连接和其他列)的决定主要取决于是否有分组和排序的查询要求,以及不等式谓词后结果的基数是多少。如果表很大,谓词过滤了很多,那么以后再次排序就不重要了,而如果只删除一小部分,那么以后的排序会很慢,需要索引进行优化。

任何其他未被搜索、仅被选择的列, 应该在 INCLUDE 不是钥匙。顺序并不重要。

如果你有两个或多个不等式或连接列,那么你就不能对这两个列都进行索引。您需要决定哪一个将过滤得最好,然后将其余列放置为 包括 s

所以最好的指数是

(
    [Fk_ObjectTheValuesBelongTo_Id] ASC,
    [From] ASC
) INCLUDE (
    [To]
)

或

(
    [Fk_ObjectTheValuesBelongTo_Id] ASC,
    [To] DESC
) INCLUDE (
    [From]
)

此外,由于您正在传递一个 datetime2 而不是 smalldatetime 。您需要使用正确的类型。