主页 / dba / 问题 / 222987
Accepted
McNets
Asked: 2018-11-21 07:42:34 +0800 CST

为什么优化器会选择Clustered Index + Sort而不是Non-Clustered Index？

  • 772

给出下一个例子：

IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM 
    sys.all_objects AS s1 
CROSS JOIN 
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

如果我获取按[nki]（非聚集索引）排序的所有记录：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

优化器选择聚簇索引，然后应用排序算法。

在此处输入图像描述

Execution plan

但是如果我强制它使用非聚集索引：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

然后它使用带有键查找的非聚集索引：

在此处输入图像描述

Execution plan

显然如果将非聚集索引转化为覆盖索引：

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

然后它只使用这个索引：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

在此处输入图像描述

Execution plan

问题

  • 为什么 SQL Server 使用聚集索引加排序算法而不是使用非聚集索引，即使在后一种情况下执行时间快 38%？
sql-server sql-server-2012

3 个回答

  1. 如果您将 100,000 次查找所需的读取次数与进行排序所涉及的内容进行比较，您可能很快就会明白为什么查询优化器认为 CIX+Sort 是最佳选择。

    Lookup 执行最终会更快，因为正在读取的页面在内存中（即使您清除缓存，每页有很多行，所以您一遍又一遍地读取相同的页面，但碎片数量不同或来自其他活动的不同内存压力，情况可能并非如此）。让 CIX+Sort 运行得更快确实不需要那么多，但您所看到的是因为读取成本没有考虑重复访问相同页面的相对便宜。

    • 9
  2. Best Answer

    为什么 SQL Server 使用聚集索引加排序算法而不是使用非聚集索引，即使在后一种情况下执行时间快 38%？

    因为 SQL Server 使用基于统计信息而非运行时信息的基于成本的优化器。

    在此查询的成本估算过程中，它确实评估了查找计划，但估计会花费更多的精力。（将鼠标悬停在执行计划中的 SELECT 上时，请注意“估计的子树成本”）。这也不一定是一个糟糕的假设——在我的测试机器上，查找计划占用排序/扫描 6 倍的 CPU。

    查看 Rob Farley 的回答，了解为什么 SQL Server 可能会使查找计划成本更高。

    • 9

  3. 我决定深入研究这个问题，我发现了一些有趣的文档，讨论如何以及何时使用或可能更好，而不是（强制）使用非聚集索引。

    根据John Eisbrener的评论建议，Kimberly L. Tripp 的这篇有趣的文章是引用最多的文章之一，甚至在其他博客中也是如此：

    但它不是唯一的，如果你有兴趣可以看看这个页面：

    如您所见，所有这些都围绕着引爆点的概念展开。

    引自 KL Tripp 文章

    临界点是什么？

    这是返回的行数“不再具有足够选择性”的点。SQL Server 选择不使用非聚集索引来查找相应的数据行，而是执行表扫描。

    当 SQL Server 在堆上使用非聚集索引时，基本上它会获得指向基表页面的指针列表。然后它使用这些指针通过一系列称为行 ID 查找 (RID) 的操作来检索行。这意味着至少，它将使用与返回的行数一样多的页面读取，甚至可能更多。这个过程有点类似于以聚簇索引作为基表，但结果相同：读取更多。

    但是，这个临界点何时出现？

    当然，作为这一生中的大多数事情，这取决于...

    不严重，它出现在表中页数的 25% 到 33% 之间，具体取决于每页的行数。但还有更多因素需要考虑：

    引自 ITPRoToday 文章

    影响临界点的其他因素 尽管 RID 查找的成本是影响临界点的最重要因素，但还有许多其他因素：

    • 扫描聚集索引时，物理 I/O 的效率要高得多。聚簇索引数据按索引顺序依次放置在磁盘上。因此，磁盘上的横向磁头移动非常小，从而提高了 I/O 性能。
    • 当数据库引擎扫描聚集索引时，它知道磁盘轨道上接下来的几页很可能仍包含它需要的数据。因此，它开始以 64KB 的块而不是正常的 8KB 页面进行预读。这也会导致更快的 I/O。

    现在，如果我使用统计 IO 再次执行我的查询：

    SET STATISTICS IO ON;
SELECT id, foo, bar, nki FROM my_table WHERE nki < 20000 ORDER BY nki ;
SET STATISTICS IO OFF;

Logical reads: 312

SET STATISTICS IO ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS IO OFF;

Logical reads: 41293

    第二个查询比第一个查询需要更多的逻辑读取。

    我应该避免非聚集索引吗？

    不，聚簇索引可能很有用，但值得花时间并付出额外的努力来分析您试图通过它实现的目标。

    引自 KL Tripp 文章

    那你该怎么办？这取决于。如果您非常了解您的数据并且进行了一些广泛的测试，您可能会考虑使用提示（您可以在 sps 中以编程方式执行一些聪明的事情，我将尝试尽快为此专门写一篇文章）。然而，一个更好的选择（如果可能的话）是考虑覆盖（这真的是我的主要观点:)。在我的查询中，覆盖是不现实的，因为我的查询需要所有列（邪恶的 SELECT *）但是，如果您的查询范围更窄并且它们是高优先级的，那么您最好使用覆盖索引（在许多情况下）而不是提示，因为涵盖查询的索引，从不提示。

    这就是目前难题的答案，但肯定还有很多东西需要深入研究。引爆点可能是一件非常好的事情——而且通常效果很好。但是，如果您发现可以强制索引并获得更好的性能，您可能需要进行一些调查，看看是否是这样。然后考虑提示提供帮助的可能性有多大，现在您知道可以将重点放在何处。

    • 4

相关问题