如何根据一行的字段在索引上创建多个条目？

Question

Shaul Behr

Asked: 2014-06-02 02:10:35 +0800 CST2014-06-02 02:10:35 +0800 CST 2014-06-02 02:10:35 +0800 CST

如何摆脱聚集索引扫描？

772

我有一个由 C#/Linq-To-Entities 代码生成的查询。与这种查询一样，它看起来非常丑陋，我怀疑将它包含在此处会有所帮助。但是我通过查询分析器运行它，发现 50.5% 的处理时间发生在“嵌套循环（内部连接）”步骤中，该步骤正在加入聚集索引搜索a2_AssessmentQuestion（0.3%）和聚集索引扫描同一张表（13.1%）。

我承认我并不真正理解这个“嵌套循环连接”在做什么，但是为什么查询会在执行索引查找后立即对表进行扫描，而它正在查找的字段可以直接通过另一个索引获得究竟是必填字段？是的，我已经运行update statistics a2_AssessmentQuestion了，但没有效果。

不是了解查询计划的专家，如果您能促使我回答相关问题，我将不胜感激；我可能只是不够了解，无法为您提供足够有用的信息。

谢谢！

在此处输入图像描述

编辑： FWIW，这是查询：

SELECT 
[Project6].[ID] AS [ID], 
[Project6].[ID2] AS [ID1], 
[Project6].[ID1] AS [ID2], 
[Project6].[ID3] AS [ID3], 
[Project6].[ID5] AS [ID4], 
[Project6].[ID7] AS [ID5], 
[Project6].[ID6] AS [ID6], 
[Project6].[ID8] AS [ID7], 
[Project6].[ID9] AS [ID8], 
[Project6].[ID11] AS [ID9], 
[Project6].[ID10] AS [ID10], 
[Project6].[ID12] AS [ID11], 
[Project6].[ID13] AS [ID12], 
[Project6].[Number] AS [Number], 
[Project6].[ScholarAssessmentID] AS [ScholarAssessmentID], 
[Project6].[C1] AS [C1], 
[Project6].[GradingStyle] AS [GradingStyle], 
[Project6].[PartID] AS [PartID], 
[Project6].[C2] AS [C2], 
[Project6].[ID4] AS [ID13], 
[Project6].[Description] AS [Description], 
[Project6].[Version] AS [Version], 
[Project6].[DifficultyLevel] AS [DifficultyLevel], 
[Project6].[AssessmentQuestionID] AS [AssessmentQuestionID], 
[Project6].[GradingDueDate] AS [GradingDueDate], 
[Project6].[C3] AS [C3], 
[Project6].[AssessmentFormat] AS [AssessmentFormat], 
[Project6].[C4] AS [C4], 
[Project6].[Structure] AS [Structure], 
[Project6].[Title] AS [Title], 
[Project6].[C5] AS [C5], 
[Project6].[AssessmentFormat1] AS [AssessmentFormat1], 
[Project6].[C6] AS [C6], 
[Project6].[Label] AS [Label]
FROM ( SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[ScholarAssessmentID] AS [ScholarAssessmentID], 
    [Extent1].[AssessmentQuestionID] AS [AssessmentQuestionID], 
    [Join1].[ID1] AS [ID1], 
    [Join1].[ID2] AS [ID2], 
    [Extent4].[ID] AS [ID3], 
    [Extent4].[PartID] AS [PartID], 
    [Extent4].[Number] AS [Number], 
    [Extent4].[DifficultyLevel] AS [DifficultyLevel], 
    [Extent4].[Structure] AS [Structure], 
    [Extent4].[GradingStyle] AS [GradingStyle], 
    [Extent5].[ID] AS [ID4], 
    [Extent5].[Description] AS [Description], 
    [Extent5].[Version] AS [Version], 
    [Extent6].[ID] AS [ID5], 
    [Extent6].[GradingDueDate] AS [GradingDueDate], 
    [Extent6].[Title] AS [Title], 
    [Join6].[ID3] AS [ID6], 
    [Join6].[ID4] AS [ID7], 
    [Extent9].[ID] AS [ID8], 
    [Extent10].[ID] AS [ID9], 
    [Extent10].[AssessmentFormat] AS [AssessmentFormat], 
    [Join10].[ID5] AS [ID10], 
    [Join10].[ID6] AS [ID11], 
    [Extent13].[ID] AS [ID12], 
    [Extent14].[ID] AS [ID13], 
    [Extent14].[AssessmentFormat] AS [AssessmentFormat1], 
     CAST( [Extent4].[Points] AS bigint) AS [C1], 
     CAST( [Extent1].[PointsAwarded] AS decimal(19,0)) AS [C2], 
     CAST( [Extent6].[DueDate] AS datetime2) AS [C3], 
     CAST( [Extent4].[Number] AS bigint) AS [C4], 
    [Join14].[Label] AS [Label], 
    CASE WHEN ([Extent4].[RubricWeight] IS NOT NULL) THEN  CAST( [Extent4].[RubricWeight] AS int) ELSE 0 END AS [C5], 
    CASE WHEN ([Join14].[ScholarAssessmentAnswerID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C6]
    FROM            [dbo].[a2_ScholarAssessmentAnswer] AS [Extent1]
    INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[AssessmentID] AS [AssessmentID], [Extent3].[ID] AS [ID2]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent2]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent3] ON [Extent2].[ID] = [Extent3].[ID] ) AS [Join1] ON [Extent1].[ScholarAssessmentID] = [Join1].[ID1]
    INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent4] ON [Extent1].[AssessmentQuestionID] = [Extent4].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentQuestionType] AS [Extent5] ON [Extent4].[QuestionTypeID] = [Extent5].[ID]
    INNER JOIN [dbo].[a2_AssessmentPart] AS [Extent6] ON [Extent4].[PartID] = [Extent6].[ID]
    INNER JOIN  (SELECT [Extent7].[ID] AS [ID3], [Extent7].[AssessmentID] AS [AssessmentID], [Extent8].[ID] AS [ID4]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent7]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent8] ON [Extent7].[ID] = [Extent8].[ID] ) AS [Join6] ON [Extent1].[ScholarAssessmentID] = [Join6].[ID3]
    INNER JOIN [dbo].[a2_Assessment] AS [Extent9] ON [Join6].[AssessmentID] = [Extent9].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent10] ON [Extent9].[AssessmentTypeID] = [Extent10].[ID]
    INNER JOIN  (SELECT [Extent11].[ID] AS [ID5], [Extent11].[AssessmentID] AS [AssessmentID], [Extent12].[ID] AS [ID6]
        FROM  [dbo].[a2_ScholarAssessment] AS [Extent11]
        LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent12] ON [Extent11].[ID] = [Extent12].[ID] ) AS [Join10] ON [Extent1].[ScholarAssessmentID] = [Join10].[ID5]
    INNER JOIN [dbo].[a2_Assessment] AS [Extent13] ON [Join10].[AssessmentID] = [Extent13].[ID]
    LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent14] ON [Extent13].[AssessmentTypeID] = [Extent14].[ID]
    LEFT OUTER JOIN  (SELECT [Extent15].[ScholarAssessmentAnswerID] AS [ScholarAssessmentAnswerID], [Extent16].[Label] AS [Label]
        FROM  [dbo].[a2_ScholarAssessmentAnswerSelection] AS [Extent15]
        INNER JOIN [dbo].[a2_AssessmentAnswers] AS [Extent16] ON [Extent15].[AssessmentAnswerID] = [Extent16].[ID] ) AS [Join14] ON [Extent1].[ID] = [Join14].[ScholarAssessmentAnswerID]
    WHERE (1538 = [Join1].[AssessmentID]) AND ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent18].[ID] AS [ID]
            FROM  [dbo].[a2_AssessmentQuestion] AS [Extent17]
            INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18] ON [Extent17].[PartID] = [Extent18].[PartID]
            WHERE [Extent1].[AssessmentQuestionID] = [Extent17].[ID]
        )  AS [Project1]
        WHERE ( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent19]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent19].[ScholarAssessmentID]) AND ([Extent19].[AssessmentQuestionID] = [Project1].[ID])
        )) OR (CASE WHEN ( EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent20]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent20].[ScholarAssessmentID]) AND ([Extent20].[AssessmentQuestionID] = [Project1].[ID])
        )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent21]
            WHERE ([Extent1].[ScholarAssessmentID] = [Extent21].[ScholarAssessmentID]) AND ([Extent21].[AssessmentQuestionID] = [Project1].[ID])
        )) THEN cast(0 as bit) END IS NULL)
    ))
)  AS [Project6]
ORDER BY [Project6].[ID] ASC, [Project6].[ID2] ASC, [Project6].[ID1] ASC, [Project6].[ID3] ASC, [Project6].[ID5] ASC, [Project6].[ID7] ASC, [Project6].[ID6] ASC, [Project6].[ID8] ASC, [Project6].[ID9] ASC, [Project6].[ID11] ASC, [Project6].[ID10] ASC, [Project6].[ID12] ASC, [Project6].[ID13] ASC, [Project6].[ID4] ASC, [Project6].[C6] ASC

我试图包括执行计划，但这让我超过了 30,000 个字符的限制。

疯狂的是，我提供了 AssessmentID = 1538，这是查询中最有用的限制信息，但执行计划几乎忽略了这个事实，并且扫描了几乎所有其他连接表，仅按 AssessmentID 过滤作为事后的想法...

1 个回答

Voted

Blaž Dakskobler · Answer 1 · 2014-06-02T04:43:37+08:00

Best Answer

Blaž Dakskobler

2014-06-02T04:43:37+08:002014-06-02T04:43:37+08:00

嵌套循环发生在这部分，您将在其中加入a2_AssessmentQuestion自身：

SELECT
   [Extent18].[ID] AS [ID]
FROM
   [dbo].[a2_AssessmentQuestion] AS [Extent17]
   INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18]
      ON [Extent17].[PartID] = [Extent18].[PartID]
WHERE
   [Extent1].[AssessmentQuestionID] = [Extent17].[ID]

从您发布的整个查询中可以看出，此部分不应用 where 条件。

查看嵌套循环和聚集索引扫描的谓词和输出，我建议您尝试在a2_AssessmentQuestion.PartID.

执行计划中的嵌套循环详细信息执行计划中的聚集索引扫描详细信息

3

如何摆脱聚集索引扫描？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？