主页 / dba / 问题 / 155881
Accepted
BanksySan
Asked: 2016-11-22 05:41:06 +0800 CST

意外的索引性能（一切都一样）

  • 772

我想看看在计算列上设置索引的效果，所以我创建了一个表，如下所示：

CREATE TABLE [Domain\UserName].[CompColIndexing](
    [a] [int] NOT NULL,
    [nonIndexedNonPersisted]  AS ([a]+(1)),
    [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
    [IndexedNonPersisted]  AS ([a]+(1)),
    [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

我已经800,000为此添加了行，其值为a循环0到9.

添加了以下索引：

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing] 
(
    [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

然后我运行了一些ORDER BY子句来查看我会得到什么性能差异，然后计划以查看不断变化的值a将如何影响事物。

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

出乎意料的是，我发现我对每个查询都得到了完全相同的结果：

分析器输出

我至少期望SORT第一个查询的操作会变慢，因为它没有索引。

这里发生了什么事？

基数是故意低的，实际上，我实际上需要对三个不同的值进行排序。

我在用着Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

实际执行计划可在： https ://www.brentozar.com/pastetheplan/?id=S10MTuxGg

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    您拥有的表定义导致了一些非常奇怪的优化器行为。我怀疑您遇到了此SE 帖子中记录的问题。为避免该问题，我将创建仅包含 [a] 和 [IndexedPersisted] 列的表。

    查询提示可用于找出优化器未选择您期望的计划的原因。在这里您希望使用索引，但 SQL Server 没有使用它。让我们并排查看两个查询计划：

    SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

    查询计划

    查询优化器认为表扫描后的排序比 800000 次 RID 查找便宜。也许这是错误的，所以让我们运行查询并比较它们的性能指标。

    cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

    在检查了“执行后丢弃结果”的单独会话中运行查询后，我通过查看 sys.dm_exec_sessions 获得了这些数字，这样我就不必等待行返回给客户端。

    这些数字对我来说似乎是合理的。仅仅因为可以使用索引并不意味着应该使用它，尤其是当 SQL Server 需要使用索引读取整个表时。这是我能想到的最糟糕的索引用例。从表中选择一小部分行或覆盖索引时，索引非常有用。

    如果我只选择 [IndexedPersisted] 列，则该索引是一个覆盖索引。在那种情况下，SQL Server 认为使用索引比进行表扫描更便宜。比较两种方法的代码：

    -- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

    查询计划 2

    以下是性能数据：

    cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

    现在索引是一个覆盖索引，它是比表扫描更好的表访问路径。

    • 4

相关问题