为什么聚集索引扫描？

Question

我刚刚开始学习优化我的查询和分析他们的查询计划。我认为这个查询会生成非聚集索引查找+键查找。

SELECT ct.*
FROM Person.ContactType AS ct
WHERE ct.Name LIKE 'Own%';

相反，它使用聚集索引扫描。我也不知道原因。

我正在SQLServer2012Express上使用AdventureWorks2012数据库。ContactTypeId列上有聚集索引，Name列上有非聚集索引。第三列(ModifiedDate)不是任何索引的一部分。此表仅包含20行。

我怀疑查询优化器决定进行聚集索引扫描，因为表只有20行，也许扫描索引然后进行键查找更快。

Answer 1

这张桌子很小！

它有20行，其中2行符合搜索条件。表定义包含三个列和两个索引(它们都支持唯一性约束)。

CREATE TABLE Person.ContactType(
    ContactTypeID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NOT NULL,
    ModifiedDate datetime NOT NULL,
    CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
    CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
) 

正在运行

SELECT index_type_desc,
       index_depth,
       page_count,
       avg_page_space_used_in_percent,
       avg_record_size_in_bytes
FROM   sys.dm_db_index_physical_stats(db_id(), 
                                      object_id('Person.ContactType'), 
                                      NULL, 
                                      NULL, 
                                      'DETAILED') 

显示这两个索引仅由没有较高级别页的单个叶页组成。

+--------------------+-------------+------------+--------------------------------+--------------------------+
|  index_type_desc   | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX    |           1 |          1 | 15.9130219915987               | 62.5                     |
| NONCLUSTERED INDEX |           1 |          1 | 13.1949592290586               | 51.5                     |
+--------------------+-------------+------------+--------------------------------+--------------------------+

每个索引页上的行不一定按索引键顺序排列，但每个页都有一个槽数组，其中包含页上每一行的偏移量。这是按索引顺序进行的。

非聚集索引涵盖三列中的两列(名称作为键列，ContactTypeID作为行定位器返回基表)，但缺少ModifiedDate。

您可以使用索引提示强制NCI查找如下所示

SELECT ct.*
FROM   Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE  ct.Name LIKE 'Own%'; 

但是您可以看到，在Server的成本模型下，这个计划的估计成本比竞争的CI扫描(大约是两倍)要高。

单页聚集索引扫描只需读取页面上的所有20行，根据它们计算谓词并返回它们。

单页非聚集索引范围查找可能潜在地能够在插槽数组上执行一个二进制搜索，以减少计算的行数，但是索引不包括查询，因此它还需要一个潜在的IO来检索CI页面，然后它仍然需要定位在那里缺少列值的行(对于NCI查找返回的每一行)。

在我的机器上，运行100万次非聚集索引计划的迭代要花费15.245秒，而对于聚集索引计划则需要11.113秒。虽然这远远不是计划的两倍，但没有暗示的计划要快得多。

然而，即使该表的大小为数量级，您也很可能无法通过查找得到您预期的计划。

Server的成本计算模型更倾向于顺序扫描而不是随机IO查找，而选择覆盖索引的扫描或非覆盖索引的查找和查找之间的“临界点”通常比在这里讨论过金伯利·特里普的博客文章低得令人吃惊。

当然，为10%的选择性谓词选择这样的方案也不是不可能的，但要做到这一点，聚集索引可能需要比NCI宽得多。