主页 / dba / 问题 / 244062
Accepted
Chessbrain
Asked: 2019-07-30 09:13:46 +0800 CST

获取索引扫描而不是可能的索引搜索？

  • 772

目前正在学习一些关于查询优化的东西，我一直在尝试不同的查询并偶然发现了这个“问题”。

我正在使用 AdventureWorks2014 数据库，我运行了这个简单的查询：

表结构（取自https://www.sqldatadictionary.com/AdventureWorks2014.pdf）：

在此处输入图像描述

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

返回 19,720 行

Sales.Customer 中的总行数 = 19,820

在检查以确保 CustomerID 实际上不仅是表的 PK，而且上面还有一个聚集索引（但它使用非聚集索引），确实是这样：

EXEC SP_HELPINDEX 'Sales.Customer'

在此处输入图像描述

这是执行计划↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

我读过，当面对大量数据和/或当它返回超过 50% 的数据集时，查询优化器将支持索引扫描。但是整个表几乎没有 20,000 行（准确地说是 19,820 行），无论如何它都不是一张大表。

当我运行此查询时：

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

返回 118 行

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

我得到了一个索引搜索，所以我认为这是由于“超过 50% 的情况”，但是，我也运行了这个查询：

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

返回 10,118 行

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

它还使用了索引查找，即使它返回了超过 50% 的数据集。

那么这里发生了什么？

编辑：

打开 IO Statistics 后，>100 查询返回：

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

当> 20,000返回时：

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

因此WITH(FORCESCAN)，在 >20,000 的选项中添加了一个选项，看看会发生什么：

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

因此，即使查询优化器选择为这个特定查询运行 Index Seek，它最终也会通过索引扫描（更少的逻辑读取）运行得更好。

sql-server index

3 个回答

  1. Best Answer

    您使用非等式谓词，因此您的“查找”操作实际上是从某个值（而不是“第一个”）开始的扫描，然后到达聚集索引叶级别的末尾。

    另一方面，您只返回一个作为聚集索引键的列，因此使用任何索引都不会获得任何键查找操作。优化器必须估计什么会更便宜：扫描非聚集索引（叶级别的两个 int 列）或部分扫描聚集索引（叶级别的所有列）。

    它根据当前统计数据（多少行）和元数据（一行大小）来估计它。我们看到优化器在>20,000谓词上犯了一个错误。

    当面对大量数据和/或当它返回超过 50% 的数据集时，查询优化器将倾向于索引扫描。

    当优化器必须选择执行聚集索引或表扫描与非聚集索引查找 + 键查找时，这是一个事实。

    在您的情况下，如果您的索引CustomerID是非集群的，您将始终看到对该索引的查找操作，但如果您随后在输出中添加另一列，您将在短结果集上看到索引查找 + RID 查找和对大结果集进行表扫描。

    • 5

  2. 在成本基础优化中，优化器在给定的时间找到最佳的执行，这是具有成本效益的。

    当我们检查这个表中每个索引的索引大小时，

    SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('Sales.Customer'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;



IX_Customer_TerritoryID || 288
PK_Customer_CustomerID || 976

    所以很明显索引大小IX_Customer_TerritoryID是远远小于的PK_Customer_CustomerID。

    比较两个查询的成本，

    SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

SELECT C.CustomerID
FROM Sales.Customer AS C WITH(INDEX(PK_Customer_CustomerID))
WHERE C.CustomerID > 100

    I/O cost有索引的查询IX_Customer_TerritoryID小于PK_Customer_CustomerID。

    • 0

  3. 优化器使用它认为最快的任何东西；很多时候我期望它做的，它没有。它不仅基于行 %；它基于许多因素，例如它拥有的统计信息、索引、表列和查询本身。使用它来创建成本和阈值估计，尽管行数确实起作用。

    我猜它扫描第一个查询是因为上述因素，即统计数据。出于同样的原因，它在第二次进行了索引搜索。第三次查找可能只是因为计划已经在内存中编译。如果您像@scsimon 建议的那样尝试重新编译，我很好奇它是否会扫描它。

    • -1

相关问题