sql-server - 为什么 SQL Server 会选择聚集索引扫描而不是非聚集索引扫描？

Question

在我查询的其中一个表中，聚集索引是在一个非主键的键上创建的。 (我不知道为什么。)

但是，此表的主键有一个非聚集索引。

在执行计划中，SQL 选择聚集索引，而不是我在查询中使用的主键的非聚集索引。

SQL 这样做有什么原因吗？如何强制 SQL 选择非聚集索引？

---

附加更多细节:

表有很多字段，查询包含很多连接。让我抽象一下。

表定义如下所示:

SlowTable
[SlowTable_id] [int] IDENTITY(200000000,1) NOT NULL,
[fk1Field] [int] NULL,
[fk2Field] [int] NULL,
[other1Field] [varchar] NULL,
etc. etc...

然后这个表的索引是:

fk1Field (Clustered)
SlowTable_id (Non-Unique, Non-Clustered)
fk2Field (Non-Unique, Non-Clustered)
... and 14 other Non-Unique, Non-Clustered indices on other fields

大概有更多针对 fk1Field 的查询，这就是为什么他们选择它作为聚簇索引的基础。

我的查询使用了一个 View :

SELECT
  [field list]
FROM 
     SourceTable1 S1
     INNER JOIN SourceTable2 S2
       ON S2.S2_id = S1.S2_id
     INNER JOIN SourceTable3 S3
       ON S3.S3_id = S2.S3_id
     INNER JOIN SlowTable ST
       ON ST.SlowTable_id = S1.SlowTable_id
     INNER JOIN [many other tables, around 7 more...]

执行计划比较大，有相关节点说

Hash Match 
(Inner Join)
Cost: 9%

粗箭头指向

Clustered Index Scan (Clustered)
SlowTable.fk1Field 
Cost: 77%

我希望这提供了有关该问题的足够详细信息。

谢谢!

---

附录 2:更正我之前的帖子。该 View 没有 where 子句。它只是一系列内部连接。执行计划取自在如下所示的复杂查询中使用 View (列为 SLOW_VIEW)的 Insert 语句:

(此存储过程的作用是根据权重对某些记录的总量进行按比例拆分，计算为占总数的百分比。这模拟了将一个值从一个账户分配到其他账户。 )

INSERT INTO dbo.WDTD(
    FieldA,
    FieldB,
    GWB_id,
    C_id,
    FieldC,
    PG_id,
    FieldD,
    FieldE,
    O_id,
    FieldF,
    FieldG,
    FieldH,
    FieldI,
    GWBIH_id,
    T_id,
    JO_id,
    PC_id,
    PP_id,
    FieldJ,
    FieldK,
    FieldL,
    FieldM,
    FieldN,
    FieldO,
    FieldP,
    FieldQ,
    FieldS)
SELECT DISTINCT
    @FieldA FieldA,
    GETDATE() FieldB,
    @Parameter1 GWB_id,
    GWBIH.C_id C_id,
    P.FieldT FieldC,
    P.PG_id PG_id,
    PAM.FieldD FieldD,
    PP.FieldU FieldE,
    GWBIH.O_id O_id,
    CO.FieldF FieldF,
    CO.FieldG FieldG,
    PSAM.FieldH FieldH,
    PSAM.FieldI FieldI,
    SOURCE.GWBIH_id GWBIH_id,
    ' ' T_id,
    GWBIH.JO_id JO_id,
    SOURCE.PC_id PC_id,
    GWB.PP_id,
    SOURCE.FieldJ FieldJ,
    1 FieldK,
    ROUND((SUM(GWBIH.Total) / AGG.Total) * SOURCE.Total, 2) FieldL,
    ROUND((SUM(GWBIH.Total) / AGG.Total) * SOURCE.Total, 2) FieldM,
    0 FieldN,
    ' ' FieldO,
    ESGM.FieldP_flag FieldP,
    SOURCE.FieldQ FieldQ,
     '[UNPROCESSED]'
FROM
    dbo.Table1 GWBIH
    INNER JOIN dbo.Table2 GWBPH
        ON GWBPH.GWBP_id = GWBIH.GWBP_id
    INNER JOIN dbo.Table3 GWB
        ON GWB.GWB_id = GWBPH.GWB_id
    INNER JOIN dbo.Table4 P
        ON P.P_id = GWBPH.P_id
    INNER JOIN dbo.Table5 ESGM
        ON ESGM.ET_id = P.ET_id
    INNER JOIN dbo.Table6 PAM
        ON PAM.PG_id = P.PG_id
    INNER JOIN dbo.Table7 O
        ON O.dboffcode = GWBIH.O_id
    INNER JOIN dbo.Table8 CO
        ON
            CO.Country_id = O.Country_id
            AND CO.Brand_id = O.Brand_id
    INNER JOIN dbo.Table9 PSAM
        ON PSAM.Office_id = GWBIH.O_id
    INNER JOIN dbo.Table10 PCM
        ON PCM.PC_id = GWBIH.PC_id
    INNER JOIN dbo.Table11 PC
        ON PC.PC_id = GWBIH.PC_id
    INNER JOIN dbo.Table12 PP
        ON PP.PP_id = GWB.PP_id
            -- THIS IS THE VIEW THAT CONTAINS THE CLUSTERED INDEX SCAN
    INNER JOIN dbo.SLOW_VIEW GL
        ON GL.JO_id = GWBIH.JO_id
    INNER JOIN (
        SELECT
            GWBIH.C_id C_id,
            GWBPH.GWB_id,
            SUM(GWBIH.Total) Total
        FROM
            dbo.Table1 GWBIH
            INNER JOIN dbo.Table2 GWBPH
                ON GWBPH.GWBP_id = GWBIH.GWBP_id
            INNER JOIN dbo.Table10 PCM
                ON PCM.PC_id = GWBIH.PC_id
        WHERE
            PCM.Split_flag = 0
            AND GWBIH.JO_id IS NOT NULL
        GROUP BY
            GWBIH.C_id,
            GWBPH.GWB_id
            ) AGG
        ON AGG.C_id = GWBIH.C_id
            AND AGG.GWB_id = GWBPH.GWB_id
    INNER JOIN (
        SELECT
            GWBIH.GWBIH_id GWBIH_id,
            GWBIH.C_id C_id,
            GWBIH.FieldQ FieldQ,
            GWBP.GWB_id GWB_id,
            PCM.PC_id PC_id,
            CASE
            WHEN WT.FieldS IS NOT NULL
                THEN WT.FieldS
            WHEN WT.FieldS IS NULL
                THEN PCMS.FieldT
            END FieldJ,
            SUM(GWBIH.Total) Total
        FROM
            dbo.Table1 GWBIH
            INNER JOIN dbo.Table2 GWBP
                ON GWBP.GWBP_id = GWBIH.GWBP_id
            INNER JOIN dbo.Table4 P
                ON P.P_id = GWBP.P_id
            INNER JOIN dbo.Table10 PCM
                ON PCM.PC_id = GWBIH.PC_id
            INNER JOIN dbo.Table11 PCMS
                ON PCMS.PC_id = PCM.PC_id
            LEFT JOIN dbo.WT WT
                ON WT.ET_id = P.ET_id
                AND WT.PC_id = GWBIH.PC_id
        WHERE
            PCM.Split_flag = 1
        GROUP BY
            GWBIH.GWBI_id,
            GWBIH.C_id,
            GWBIH.FieldQ,
            GWBP.GWB_id,
            WT.FieldS,
            PCM.PC_id,
            PCMS.ImportCode
            ) SOURCE
        ON SOURCE.C_id = GWBIH.C_id
            AND SOURCE.GWB_id = GWBPH.GWB_id
WHERE
    PCM.Split_flag = 0
    AND AGG.Total > 0
    AND GWBPH.GWB_id = @Parameter1
    AND NOT EXISTS (
        SELECT *
        FROM dbo.WDTD
        WHERE
            TD.C_id = GWBIH.C_id
            AND TD.FieldA = GWBPH.GWB_id
            AND TD.JO_id = GWBIH.JO_id
            AND TD.PC_id = SOURCE.PC_id
            AND TD.GWBIH_id = ' ')
GROUP BY
    GWBIH.C_id,
    P.FieldT,
    GWBIH.JO_id,
    GWBIH.O_id,
    GWBPH.GWB_id,
    P.PG_id,
    PAM.FieldD,
    PP.FieldU,
    GWBIH.O_id,
    CO.FieldF,
    CO.FieldG,
    PSAM.FieldH,
    PSAM.FieldI,
    GWBIH.JO_id,
    SOURCE.PC_id,
    GWB.PP_id,
    SOURCE.FieldJ,
    ESGM.FieldP_flag,
    SOURCE.GWBIH_id,
    SOURCE.FieldQ,
    AGG.Total,
    SOURCE.Total

---

附录 3:在对 View 的 select 语句执行执行计划时，我看到了:

Hash Match     <====   Bitmap           <------ etc...
(Inner Join)           (Bitmap Create)
Cost: 0%               Cost: 0%
    ^
    |
    |
Parallelism                           Clustered Index Scan (Clustered)
(Repartition Streams)    <====        Slow_Table.fk1Field
Cost: 1%                              Cost: 98%

---

附录 4:我想我找到了问题所在。聚簇索引扫描不是指我引用主键的子句，而是另一个需要在某种程度上与上面的 fk1Field 相关的字段的子句。

Answer 1

很可能是以下之一:

• 太多的行使索引无效
• 索引不符合 ON/WHERE 条件
• 索引没有覆盖并且 SQL Server 避免了键查找

编辑，更新后:

你的索引没用，因为都是单列索引，所以它做了聚簇索引扫描。

您需要一个索引来匹配您的 ON、WHERE、GROUP BY 条件以及 SELECT 列表的 INCLUDES。