为什么我的SQL索引会被忽略？

我们遇到的问题是,我们的表上的索引被忽略,而SQL Server 2000正在执行表扫描.我们可以通过使用该WITH (INDEX=)子句来强制使用索引,但是不希望这样做.

作为开发人员,我在编写T-SQL时非常熟悉SQL Server,但是分析和性能调优不是我的强项.我正在寻找关于为什么会发生这种情况的任何建议和指导.

更新:

我应该说我们已经重建了所有索引并更新了索引统计信息.

其中一个罪魁祸首的表定义如下:

CREATE TABLE [tblinvoices]
(
    [CustomerID] [int] NOT NULL,
    [InvoiceNo] [int] NOT NULL,
    [InvoiceDate] [smalldatetime] NOT NULL,
    [InvoiceTotal] [numeric](18, 2) NOT NULL,
    [AmountPaid] [numeric](18, 2) NULL 
        CONSTRAINT [DF_tblinvoices_AmountPaid]  DEFAULT (0),
    [DateEntered] [smalldatetime] NULL 
        CONSTRAINT [DF_tblinvoices_DateEntered]  DEFAULT (getdate()),
    [PaymentRef] [varchar](110),
    [PaymentType] [varchar](10),
    [SyncStatus] [int] NULL,
    [PeriodStart] [smalldatetime] NULL,
    [DateIssued] [smalldatetime] NULL 
        CONSTRAINT [DF_tblinvoices_dateissued]  DEFAULT (getdate()),
    CONSTRAINT [PK_tblinvoices] PRIMARY KEY NONCLUSTERED 
    (
        [InvoiceNo] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

此表上还有另一个索引(我们希望SQL使用的索引):

CustomerID (Non-Unique, Non-Clustered)

以下查询执行表扫描而不是使用CustomerID索引:

SELECT 
    CustomerID, 
    Sum(InvoiceTotal) AS SumOfInvoiceTotal, 
    Sum(AmountPaid) AS SumOfAmountPaid 
FROM tblInvoices 
WHERE CustomerID = 2112 
GROUP BY customerID

更新:

在回答Autocracy的问题时,这两个查询都执行表扫描.

更新:

在回答Quassnoi的问题有关DBCC SHOW_STATISTICS,该数据是:

RANGE_HI_KEY    RANGE_ROWS    EQ_ROWS    DISTINCT_RANGE_ROWS    AVG_RANGE_ROWS
1667            246           454        8                      27.33333
2112            911           3427       16                     56.9375
2133            914           775        16                     57.125

Quassnoi.. 5

我们遇到的问题是我们的表上的索引被忽略并且SQL Server 2000正在执行表扫描.

尽管4,302自认为已经过去了几天Aug 29, 1997,SQL Server的优化还没有演变成SkyNet着呢,它仍然可以做一些不正确的决定.

索引提示只是你,一个人,帮助人工智能的方式.

如果您确定收集了统计信息并且优化器仍然存在错误,那么请继续使用提示.

它们是合法,正确,记录和支持的,Microsoft以强制执行您想要的查询计划.

在你的情况下:

SELECT CustomerID, 
       SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
       SUM(AmountPaid) AS SumOfAmountPaid 
FROM   tblInvoices 
WHERE  CustomerID = 2112 
GROUP BY
       CustomerID

,优化器有两个选择:

使用其在该指数意味着嵌套循环索引随着KEY LOOKUP获取的值InvoiceTotal和AmountPaid

不要使用索引并扫描所有表行,这些行更快rows fetched per second,但总行数更长.

第一种方法可以或可以不比第二种方法快.

优化器尝试通过查看统计信息来估计哪种方法更快,这使得索引选择性与其他值保持一致.

对于选择性索引,前一种方法更快; 对于非选择性的,后者是.

你可以运行这个查询:

SELECT  1 - CAST(COUNT(NULLIF(CustomerID, 2112)) AS FLOAT) / COUNT(*)
FROM    tlbInvoices

更新:

由于CustomerID = 2112只涵盖1,4%您的行,您应该从使用索引中受益.

现在,您可以运行以下查询:

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

,在第三个结果集中找到两个相邻的行,其数量RANGE_HI_KEY少于和多于2112,并在此处发布行？

更新2:

由于统计数据似乎是正确的,我们只能猜测优化器在这种情况下选择全表扫描的原因.

大概(可能),这是因为这个非常值(2112)的发生RANGE_HI_KEY和优化看到它的不同寻常的密集(3427值2112单独只针对911整个范围1668至2111)

你能再做两件事吗:

运行此查询:

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

并发布前两个结果集.

运行此查询:

SELECT TOP 1 CustomerID,COUNT(*)FROM tblinvoices客户ID在1668和2111之间

,使用CustomerID原始查询中上面查询的顶部:

SELECT CustomerID, 
       SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
       SUM(AmountPaid) AS SumOfAmountPaid 
FROM   tblInvoices 
WHERE  CustomerID = @Top_Customer
GROUP BY
       CustomerID

并看看它会产生什么样的计划.

它不是SkyNet,但即使在2000年它也非常好.我的赌注是缺少统计数据 (3认同)

@Quassnio:结果是 - 0.014857559059556 (2认同)

---

BradC.. 5

最好的办法是通过在CustomerID索引中包含InvoiceTotal和AmountPaid列来使索引成为覆盖索引.(在SQL 2005中,您可以将它们添加为"包含"列.在SQL 2000中,您必须将它们添加为其他键列.)如果您这样做,我将保证查询优化器将选择您的索引*.

说明:索引似乎总是有用,但是使用(非覆盖)索引会产生隐藏成本,这就是必须要执行的" 书签查找 "以检索可能需要的任何其他列.主表.此书签查找是一项昂贵的操作,并且(一种可能)原因是查询优化器可能不会选择使用您的索引.

通过在索引本身中包含所有需要的列,完全避免了这个书签查找,并且优化器不必玩这个小游戏来确定使用索引是否"值得".

(*)或者我将退还您的StackOverflow积分.只需发送一个带有地址的盖章信封即可...

编辑:是的,如果你的主键不是聚集索引,那么一定要做到这一点!但即使有了这种改变,使您的CustomerID索引成为覆盖索引也应该将性能提高一个数量级(10倍或更高)!

1> Quassnoi..：

---

我们遇到的问题是我们的表上的索引被忽略并且SQL Server 2000正在执行表扫描.

尽管4,302自认为已经过去了几天Aug 29, 1997,SQL Server的优化还没有演变成SkyNet着呢,它仍然可以做一些不正确的决定.

索引提示只是你,一个人,帮助人工智能的方式.

如果您确定收集了统计信息并且优化器仍然存在错误,那么请继续使用提示.

它们是合法,正确,记录和支持的,Microsoft以强制执行您想要的查询计划.

在你的情况下:

SELECT CustomerID, 
       SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
       SUM(AmountPaid) AS SumOfAmountPaid 
FROM   tblInvoices 
WHERE  CustomerID = 2112 
GROUP BY
       CustomerID

,优化器有两个选择:

使用其在该指数意味着嵌套循环索引随着KEY LOOKUP获取的值InvoiceTotal和AmountPaid

不要使用索引并扫描所有表行,这些行更快rows fetched per second,但总行数更长.

第一种方法可以或可以不比第二种方法快.

优化器尝试通过查看统计信息来估计哪种方法更快,这使得索引选择性与其他值保持一致.

对于选择性索引,前一种方法更快; 对于非选择性的,后者是.

你可以运行这个查询:

SELECT  1 - CAST(COUNT(NULLIF(CustomerID, 2112)) AS FLOAT) / COUNT(*)
FROM    tlbInvoices

更新:

由于CustomerID = 2112只涵盖1,4%您的行,您应该从使用索引中受益.

现在,您可以运行以下查询:

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

,在第三个结果集中找到两个相邻的行,其数量RANGE_HI_KEY少于和多于2112,并在此处发布行？

更新2:

由于统计数据似乎是正确的,我们只能猜测优化器在这种情况下选择全表扫描的原因.

大概(可能),这是因为这个非常值(2112)的发生RANGE_HI_KEY和优化看到它的不同寻常的密集(3427值2112单独只针对911整个范围1668至2111)

你能再做两件事吗:

运行此查询:

DBCC SHOW_STATISTICS ([tblinvoices], [CustomerID])

并发布前两个结果集.

运行此查询:

SELECT TOP 1 CustomerID,COUNT(*)FROM tblinvoices客户ID在1668和2111之间

,使用CustomerID原始查询中上面查询的顶部:

SELECT CustomerID, 
       SUM(InvoiceTotal) AS SumOfInvoiceTotal, 
       SUM(AmountPaid) AS SumOfAmountPaid 
FROM   tblInvoices 
WHERE  CustomerID = @Top_Customer
GROUP BY
       CustomerID

并看看它会产生什么样的计划.

---

它不是SkyNet,但即使在2000年它也非常好.我的赌注是缺少统计数据

---

@Quassnio:结果是 - 0.014857559059556

2> BradC..：

---

最好的办法是通过在CustomerID索引中包含InvoiceTotal和AmountPaid列来使索引成为覆盖索引.(在SQL 2005中,您可以将它们添加为"包含"列.在SQL 2000中,您必须将它们添加为其他键列.)如果您这样做,我将保证查询优化器将选择您的索引*.

说明:索引似乎总是有用,但是使用(非覆盖)索引会产生隐藏成本,这就是必须要执行的" 书签查找 "以检索可能需要的任何其他列.主表.此书签查找是一项昂贵的操作,并且(一种可能)原因是查询优化器可能不会选择使用您的索引.

通过在索引本身中包含所有需要的列,完全避免了这个书签查找,并且优化器不必玩这个小游戏来确定使用索引是否"值得".

(*)或者我将退还您的StackOverflow积分.只需发送一个带有地址的盖章信封即可...

编辑:是的,如果你的主键不是聚集索引,那么一定要做到这一点!但即使有了这种改变,使您的CustomerID索引成为覆盖索引也应该将性能提高一个数量级(10倍或更高)!