主页 / coding / 问题 / 79352885
Accepted
Merlin Nestler
Asked: 2025-01-14 00:40:36 +0800 CST

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

  • 772

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

这是查询。外键就像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 文档并看到了以下内容：

如果索引包含多个列，请考虑列的顺序。在 WHERE 子句中用于等于 (=)、大于 (>)、小于 (<) 或 BETWEEN 搜索条件的列，或参与连接的列应放在最前面。其他列应根据其独特性级别进行排序，即从最独特到最不独特。

From和列To有大约 140,000 个不同的值，而外键列只有 1,600 个。因此，我决定更改它们的顺序，并使索引看起来像这样：

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]

然而，这导致此查询的性能下降。执行时间增加了 1,000 倍。我知道这一点，因为我在数据库副本上激活了查询存储。然后我运行了发送查询的 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 first in Index），在另一种情况下百分比为 20% 和 80%（Fk last in index）。 在此处输入图片描述

有什么想法吗，为什么我的数据库的行为不像阅读文档时所预期的那样？

sql-server

1 个回答

  1. Best Answer

    您错过了该段落的其余部分，该段落间接解释了索引的真正最佳方式。

    我添加了编号，以便您可以看到它想要表达的意思：

    如果索引包含多个列，请考虑列的顺序。子句中使用的列WHERE：

    1. 等于 (=)
    2. 大于 (>)、小于 (<) 或 BETWEEN 搜索条件
    3. 或参与加入

    应放在第一位。

    1. 应根据附加列的独特性级别对其进行排序，即从最独特到最不独特。

    因此，在您的例子中，FK_ObjectTheValuesBelongTo_Id是相等的，所以排在前面。这与它有多不同无关。即使它是bit一列，如果它是相等的，它也会排在前面。

    我认为，关于其他列（不等式、连接等）的决定主要取决于查询是否需要分组和排序，以及不等式谓词之后的结果基数是多少。如果表很大，并且谓词过滤掉了很多，那么稍后再次排序就无关紧要了，而如果只删除一小部分，那么稍后的排序会很慢，需要索引进行优化。

    任何其他未被搜索、仅被选中的列都应位于 中INCLUDE，而不是键中。顺序无关紧要。

    如果您有两个或多个不等式或连接列，则无法同时为两者建立索引。您需要确定哪个过滤效果最好，然后将其余列作为INCLUDEs。

    因此，最好的指数要么是

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

    或者

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

    datetime2此外，由于您传递的是而不是，因此无法正确查找索引smalldatetime。您需要使用正确的类型。

    • 2

相关问题