时间戳

首页 > 解决方案 > 为什么 Index-seek 是成本最高的算子，而不是这个计划中的 Filter？

sql-server - 为什么 Index-seek 是成本最高的算子，而不是这个计划中的 Filter？

问题描述

我有这个计划需要大约 18 秒才能运行（由 SentryOne 计划资源管理器生成）： 在此处输入图像描述

使用Index-seek了一个非聚集索引，带有一些搜索谓词。而Filter运算符有一些其他谓词，包括LIKE条件。

这是查询（种类）：

SELECT  TOP(20) *
FROM    Table
WHERE   Table.Col1 = ''
        AND Table.Col2 LIKE '%' + @searchString + '%'
ORDER BY Table.Col3

第一个（不相关的）问题：

如何阅读执行计划？

当我读到它时，“SQL”从最左边的 operator 开始Top，然后它会在右边调用它的子运算符（节点）以获得 1 个单行并继续前进，直到它达到它的目标（20 行）。如果没有Top，那么它将继续运行，直到它的子节点说没有更多了。并且子节点会调用子节点的子节点。因此在上述计划Filter中调用了Index-seek290 万次以获取 290 万行。我希望我是对的？

然后我对查询做了一个小改动：

SELECT  TOP(20) *
FROM    Table
WHERE   Col1 = ''
        AND Col2 LIKE @searchString + '%'  -- search for string prefix only
ORDER BY Table.Col3

这次查询的运行时间 < 2 秒。实际执行与上面完全相同。我比较了 SSMS 中的 2 个。Index-seek仍然是成本最高的运算符，但它返回的行数相同（290 万）。

第二个问题：

我对LIKE条件所做的更改必须减少 16 秒的执行时间。那么为什么会Index-seek被标记为最昂贵的呢？不应该是Filter因为它是处理LIKE搜索的人吗？如果我不做这个实验，我怎么知道LIKE这里的条件是邪恶的？

第三个问题：

我如何知道 SQL 处理一项任务所花费的时间是否恰到好处？

我问这个问题是因为我是优化查询的新手。我第一次看到这个计划时，我不知道Index-seek花费 18 秒的 300 万行是正确的还是有问题的。

标签： sql-serveroptimizationsql-execution-plan

解决方案

阅读执行计划

从右到左。SQL Server 创建一个执行计划，从右侧开始。（数据所在的一侧）

因此，在您的示例中，发生索引查找，将 N 行数据流式传输到左侧的下一个运算符，然后过滤器处理内存（页面缓冲区）中的所有行，依此类推。

要查看实际情况，您可以在 SSMS中打开实时查询统计信息。这样您就可以看到流经每个运算符的数据。很高兴看到查询引擎在运行。

成本

成本是相对的（基于估计的子树成本）。从磁盘（SSD 或 HDD）获取数据是 I/O。I/O 仍然是一项昂贵的操作。您会看到，在index seek从磁盘检索数据之前，它已经在处理过滤器了。否则，您将进行全表扫描或索引扫描。把它想象成电话簿：如果你按姓氏查找一个人，你可以跳过大部分页面，因为你知道人名的第一个字母，电话簿是按姓名按字母顺序排列的。

您示例中的过滤器成本不高，因为所有行都在内存中，并且索引搜索可能已经过滤掉了 N 行。

适当的时间

时间是相对的。依赖于

  • 硬件：SQL 服务器上的查询可能需要 16 秒，但在具有 128 GB 内存的机器上运行它，而所有数据都已经在缓冲池中，它可能需要 < 100 毫秒。
  • 缓冲区缓存：所有数据，或部分数据已经可以缓存在内存中
  • 执行计划缓存：是第一次执行（或OPTION(RECOMPILE)），那么 SQL Server 需要快速猜测出最便宜的执行计划。此查询的第二次执行可以重用该计划，但对于新传递的参数，它可能不是一个好的执行计划。
  • 并行性：查询可以并行以减少整体执行时间，但会增加 CPU 时间。D取决于您的内核和配置以获得最大并行度和并行度成本阈值
  • 索引碎片：当发生大量更新/删除时，索引可能会被碎片化。
  • 统计信息：统计信息用于猜测将返回多少行，这种估计在很多情况下可能是错误的。（统计数据不是最新的，索引中的数据种类繁多，201 桶问题，...）
  • 锁定和隔离级别：当您尝试读取数据时，不同的事务可能正在更改数据。锁定查询数据，增加执行时间。

如果您想要一个正确的比较数字，您可以使用SET STATISTICS IO ON打开Statistics IO。如果您在运行查询后在 SSMS 中打开消息选项卡，您将看到类似的内容。

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

逻辑读取是关键指标。它指示读取多少AK1 页以创建您想要的结果。

了解一个表上可以发生多少逻辑读取（在聚集索引上）。您可以在启用统计信息 IO 的情况下执行简单SELECT count(*) FROM table操作，并将逻辑读取与您的查询进行比较。

当查询是 nonSARGABLE 时（在您的示例中LIKE '%' + @Param + '%'），您可能有更多的逻辑读取，然后您的表中有页面。
如果您查看电话簿示例并且想要检索所有姓氏中包含“Do”的人，则需要扫描整本电话簿 = nonSARGABLE。如果将其更改为开头，则可以进行索引查找，因为您知道 Do 的第一次出现在哪里，而 Dp 的第一次出现在哪里。

推荐阅读