sql-server - 正确索引 UNPIVOT SQL 查询

Question

如果我有一个表格，例如:

CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY,
    StudentName VARCHAR (50),
    Math INT,
    English INT,
    History INT,
    Science INT
)
GO

和一个 unpivot 查询，例如:

SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
    Score
    FOR Course in (Math, English, History, Science)
) AS SchoolUnpivot

最佳索引是什么样的？

Answer 1

我将 1.000.000 个测试数据填充到 Students 表中，然后我开始测试以下查询；

注意 不要在生产环境中使用DBCC DROPCLEANBUFFERS 语句。

测试环境:

Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64) Aug 16 2019 14:20:53 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 17763: )

测试-1:

以下查询需要 34 秒。

DBCC DROPCLEANBUFFERS
GO
 SELECT StudentName, Course, Score
FROM Students
 CROSS APPLY (
    VALUES 
        ('Math', Math),
        ('English', English),
        ('History', History),
        ('Science', Science)
    ) x(Course, Score)
WHERE Score IS NOT NULL

OPTION (MAXDOP 1)

测试 2:
以下查询需要 40 秒。

DBCC DROPCLEANBUFFERS
GO
 SELECT StudentName, Course, Score
FROM Students
 CROSS APPLY (
    VALUES 
        ('Math', Math),
        ('English', English),
        ('History', History),
        ('Science', Science)
    ) x(Course, Score)
WHERE Score IS NOT NULL

OPTION (MAXDOP 1)

测试 3:

以下查询在创建索引后需要 32 秒，执行计划也在执行计划中使用创建的索引。

   CREATE NONCLUSTERED INDEX [PerformanceIndex] ON [dbo].[Students]
(
    [Id] ASC,
    [Math] ASC,
    [English] ASC,
    [History] ASC,
    [Science] ASC
)
INCLUDE([StudentName])
GO
 DBCC DROPCLEANBUFFERS
 GO
SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
    Score
    FOR Course in (Math, English, History, Science)
) AS SchoolUnpivo

OPTION (MAXDOP 1)

因此，在非聚集索引中使用逆透视列有助于我们提高查询性能，尤其是在这种情况下。