我的印象是,在LIKE
对未知场景进行所有优化时,旧版和新版 CE 都使用 9% 的估计值(假设相关统计数据可用并且查询优化器不必求助于选择性猜测)。
在对信用数据库执行以下查询时,我在不同的 CE 下得到不同的估计。在新的 CE 下,我收到了预期的 900 行的估计值,在旧版 CE 下,我收到了 241.416 的估计值,但我无法弄清楚这个估计值是如何得出的。有没有人能够阐明任何观点?
-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;
-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);
在我的场景中,我已经将信用数据库设置为兼容级别 120,因此为什么在第二个查询中我使用跟踪标志来强制使用旧版 CE,并提供有关查询优化器使用/考虑哪些统计信息的信息。我可以看到正在使用“姓氏”的列统计信息,但我仍然无法计算出 241.416 的估计值是如何得出的。
除了这篇 Itzik Ben-Gan 文章外,我在网上找不到任何东西,该文章指出“在所有优化未知场景中使用 LIKE 谓词时,旧版和新版 CE 都使用 9% 的估计值。”。该帖子中的信息似乎不正确。
LIKE
在你的情况下的猜测是基于:G
: 标准的 9% 猜测 (sqllang!x_Selectivity_Like
)M
:系数 6(幻数)D
:以字节为单位的平均数据长度(来自统计数据),向下舍入为整数具体来说,
sqllang!CCardUtilSQL7::ProbLikeGuess
使用:笔记:
LOG(D)
则省略该术语。D
D
小于 1(包括缺失或NULL
统计):D = FLOOR(0.5 * maximum column byte length)
这种古怪和复杂性是原始 CE 的典型特征。
在问题示例中,平均长度为 5(
DBCC SHOW_STATISTICS
四舍五入后为 5.6154):其他示例值:
试验台
我使用旧版 CE 在 SQL Server 2014 上进行了测试,也没有得到 9% 作为基数估计。我在网上找不到任何准确的东西,所以我做了一些测试,找到了一个适合我尝试过的所有测试用例的模型,但我不能确定它是否完整。
在我找到的模型中,估计值来自表中的行数、过滤列的统计信息的平均键长度,有时还有过滤列的数据类型长度。有两种不同的公式用于估计。
如果 FLOOR(average key length) = 0,则估计公式会忽略列统计信息并根据数据类型长度创建估计值。我只用 VARCHAR(N) 进行了测试,因此 NVARCHAR(N) 可能有不同的公式。这是 VARCHAR(N) 的公式:
这非常适合,但并不完全准确:
x 轴是数据类型的长度,y 轴是具有 100 万行的表的估计行数。
如果您没有关于该列的统计信息,或者如果该列有足够的 NULL 值以将平均键长度驱动到 1 以下,则查询优化器将使用此公式。
例如,假设您有一个包含 150k 行的表,并且对 VARCHAR(50) 进行了过滤,并且没有列统计信息。行估计预测为:
SQL来测试它:
SQL Server 给出的估计行数为 7242.47,这有点接近。
如果 FLOOR(平均密钥长度)>= 1,则使用基于 FLOOR(平均密钥长度)值的不同公式。这是我尝试过的一些值的表格:
如果 FLOOR(average key length) < 6 则使用上表。否则使用以下等式:
这个比另一个更适合,但仍然不是完全准确。
x 轴是平均键长度,y 轴是具有 100 万行的表的估计行数。
再举一个例子,假设您有一个包含 10k 行的表,平均键长度为 5.5,用于过滤列的统计信息。行估计为:
SQL来测试它:
行估计为 241.416,与您在问题中的情况相匹配。如果我使用表中没有的值,将会出现一些错误。
这里的模型并不完美,但我认为它们很好地说明了一般行为。