为什么参数化查询会导致索引扫描，而硬编码值会导致索引查找？

Question

与使用参数的查询相比，在where子句中硬编码相同值的查询性能有很大的不同。

它可以归结为执行计划中的两个子任务。硬编码查询可以执行索引查找，但是参数化查询执行索引扫描。由于表包含一百万行，所以差异很大。

这个查询很难看..。

DECLARE @MethodOfDelivery nvarchar(3) = 'AAA'
DECLARE @OutwardPostcode nvarchar(4) = 'BBB'
DECLARE @InwardPostcode nvarchar(3) = 'CCC'

SELECT TOP 1
    slr.*
FROM
    [dbo].[SortLevelRecord](NOLOCK) slr
    INNER JOIN dbo.RoutingDataVersion(NOLOCK) v ON v.Version = slr.Version
    INNER JOIN dbo.Routing(NOLOCK) psr ON
        (
            slr.SortLevelKey = psr.Day1SortLevelKey OR
            slr.SortLevelKey = psr.Day2SortLevelkey OR
            slr.SortLevelKey = psr.Day3SortLevelkey OR
            slr.SortLevelKey = psr.Day4SortLevelkey OR
            slr.SortLevelKey = psr.Day5SortLevelkey OR
            slr.SortLevelKey = psr.Day6SortLevelkey OR
            slr.SortLevelKey = psr.Day7SortLevelkey OR
            slr.SortLevelKey = psr.Day8SortLevelkey OR
            slr.SortLevelKey = psr.Day9SortLevelkey OR
            slr.SortLevelKey = psr.Day10SortLevelkey OR
            slr.SortLevelKey = psr.Day11SortLevelkey OR
            slr.SortLevelKey = psr.Day12SortLevelkey OR
            slr.SortLevelKey = psr.Day13SortLevelkey OR
            slr.SortLevelKey = psr.Day14SortLevelkey
        ) AND
        psr.Version = v.Version AND
        psr.MethodOfDelivery = slr.MethodOfDelivery AND
        psr.OutwardPostcode = @OutwardPostcode AND
        (
            psr.InwardPostcode = @InwardPostcode OR
            psr.InwardPostcode = SUBSTRING(@InwardPostcode, 1, 1) OR
            psr.InwardPostcode = ''
        )
WHERE
    slr.MethodOfDelivery = @MethodOfDelivery  
ORDER BY
    LEN(psr.InwardPostcode) DESC

索引扫描/查找位于Routing表上。

还有一点似乎有点奇怪的是，参数化查询使用并行性来收集流，但是没有并行操作，只有1。

这是在Azure SQL server上。我不知道那是什么版本的。

Answer 1

这就是所谓的参数嗅探。当您执行查询时，将存储和重用基础计划，但是该特定计划可能不适合您的变量。这是Server中的正常行为和预期行为。由于编译查询花费很大，而且需要时间，所以SQL server将计划存储在缓存中，并且作为DBA，我们希望SQL Server尽可能地重用它们，以便节省时间和资源。

这里有一个来自Server中小企业的非常好的视频 Brent Ozar先生，它可以为您提供有关参数嗅探、可能的工作以及应该做什么来避免相同情况的详细信息。

在这个问题中，格雷格·拉森先生解释了几乎相同的问题，你会得到关于变量的详细信息，并在这个链接中对值进行硬编码。

您还可以在此链接上阅读参数嗅探的详细信息。

很少有关于同一主题的更有趣的文章：

1. https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
2. https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
3. https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc

我希望上面能帮上忙。

Answer 2

免责声明:我不知道SQL-server，所以这是一个普遍的想法，为什么会发生这种情况。

当第一次将查询发送到DBMS时，将对其进行编译，然后将其存储在缓存中。当再次发送相同的查询时，DBMS可以重用已编译的语句。在这方面，像select x from y where z=3这样的查询不同于select x from y where z=4，即必须再次编译查询，并且它将占用缓存中的内存。如果缓存已满，则必须牺牲并丢弃一个或多个已编译查询。这是在此查询中使用参数标记的一个主要原因：select x from y where z=?

不利的一面是，如果有一个倾斜的分布：

---
|z|
---
|3|
|4|
|4|
|4|
--- 

无法告诉以下几行将返回多少行：

`select x from y where z=?`

因此，必须使用一些计算值。一种合理的方法是获取表上的行数，除以z中的不同值数，即4/2=2。

另一方面，如果我们手头有分布统计信息，并使用类似于select x from y where z=4的查询，我们就可以使用分布统计信息更好地预测将从谓词返回的行数。假设表中有1000行和4个不同值的列x和分布统计信息，如下所示：

x | cnt
-------
1 | 500
2 | 400 

如果我们询问像WHERE x=1这样的查询，我们就会立即知道有500行与此谓词匹配。那WHERE x=3呢？我们知道，x有两个未知值，有1000-500-400=100行。平均来说，我们可以假设结果是100/2=50行。另一方面，如果我们询问像WHERE x=?这样的查询，则必须假设返回了1000/4=250行。

因此，在参数标记上使用文字可能会改变执行计划，而代价是更多的编译和缓存中使用的更多内存。通常，对于低基数(不同值)列和倾斜分布，可以使用文字，对于高基数列和均匀分布，可以使用参数标记。