正确索引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个测试数据，然后开始测试以下查询；

Note不使用生产环境中的DBCC DROPCLEANBUFFERS状态。

测试环境:

Microsoft 2019 (RC1) - 15.0.1900.25 (X64) 2019年8月16日14:20:53版权(C) 2019微软公司开发版(64位)在Windows10Pro10.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)

作为一个结果，使用非聚集索引中的un透视列可以帮助我们提高查询性能，特别是在这种情况下。

Answer 2

我不确定可以使用哪些索引来优化查询。您需要查看查询的执行计划，才能提出有意义的建议。

但是在进行索引之前，您可以尝试优化查询。提高性能的一个选择是使用CROSS APPLY VALUES而不是UNPIVOT。以下博客文章非常详细地介绍了它可以提供的性能改进。

因此，请考虑以下查询：

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

博客作者报告说，我很想知道你是否获得了同样的性能收益。