主页 / coding / 问题 / 79324597
Accepted
Nelson O
Asked: 2025-01-03 02:05:40 +0800 CST

使用 CLR 标量函数解决 SQL Server 中的不良执行计划

  • 772

本质上，我的问题归结为观察这两个查询计划（来自 SQL Server 2019）：

  1. 使用 SQL 定义的标量函数进行计划。

SQL 标量函数查询计划

  1. 使用 CLR 定义的标量函数进行规划。

CLR 标量函数查询计划

我定义了一个标量函数，用于将 IP 地址的字符串表示解析为binary(16)IPv6 地址的值（将 IPv4 地址映射到 IPv6）。我首先在 SQL 中实现它，然后我也在 C# 中使用内置类IPAddress来解析该值。我试图使用此函数将包含 IP 地址字符串的表连接到包含解析出的 CIDR 块列表的表（聚集索引[Start]和[End] binary(16)值）。

我编写 SQL 查询的方法有两种：

  1. 直接在条件中使用标量函数JOIN。
SELECT
    *
FROM
    [dbo].[values]
    val
LEFT JOIN
    [dbo].[cidr]
    cidr
        ON [dbo].[fn_ParseIP](val.[IpAddress]) BETWEEN cidr.[Start] AND cidr.[End]
;
  1. APPLY在引用子句中的标量函数之前，先计算它JOIN。
SELECT
    val.*, cidr.*
FROM
    [dbo].[values]
    val
CROSS APPLY
    (
        SELECT [ParsedIpAddress] = [dbo].[fn_ParseIP](val.[IpAddress])
    )
    calc
LEFT JOIN
    [dbo].[cidr]
    cidr
        ON calc.[ParsedIpAddress] BETWEEN cidr.[Start] AND cidr.[End]
;

在我的测试中，[dbo].[values]包含 17 行（并且是使用实际表定义的VALUES而不是实际表）并且[dbo].[cidr]包含 986,320 行。

当使用 SQL 中定义的标量函数时，查询 1 大约需要 7.5 分钟才能运行，而查询 2 则需要不到 1 秒。

使用 CLR 标量函数时，两个查询大约需要 2.5 分钟才能运行，但查询 2 在查询计划中有一个额外的节点用于在连接后计算标量函数。

最终的区别在于，当引用 SQL 中定义的标量函数时，我可以让它生成第一个计划，该计划首先计算标量函数的结果，然后[dbo].[cidr]在执行连接时将这些结果用作查找谓词进入聚集索引。但是当使用 CLR 函数时，它总是将计算作为聚集索引查找（和过滤）的一部分来执行，因此它会更频繁地运行该函数来获取结果。

我的假设是，这种行为可能是由于查询规划器认为该函数是不确定的，但我已经使用以下属性实现了 CLR 函数：

    [SqlFunction(
        DataAccess = DataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true
    )]
    [return: SqlFacet(IsFixedLength = true, IsNullable = true, MaxSize = 16)]
    public static SqlBinary fn_CLR_ParseIP([SqlFacet(MaxSize = 50)] SqlString ipAddress)
    { }

我希望可以依靠 .NET 标准库在 SQL 中帮我处理 IP 地址解析。目前，我们有一些流程仅适用于 IPv4 地址，我需要更新它们以使其也适用于 IPv6。在我们的一些大型数据库中，这种处理非常缓慢，因此我希望 .NET 中的解析逻辑更高效。看起来 CLR 函数本身比我的 SQL 实现更快，但对查询计划的影响明显更差。

我可以重写查询以首先使用临时表解析 IP 地址，这应该可以解决这个问题。定义一个等效的 CLR 表值函数（该函数仅返回一行）时，我也可以获得不错的结果。

但是，我想知道我是否遗漏了某些东西，以便更轻松地将 CLR 标量函数用作过滤谓词的一部分。这是否是个坏主意，我应该继续使用其中一些替代方案，或者我可以做些什么来更轻松地使用 CLR 函数作为 SQL 函数的替代品？

对于任何感兴趣的人，这里是使用 T N 的答案中给出的概念表现良好的最终查询。

SELECT
    *
FROM
    [dbo].[values]
    val
CROSS APPLY
    (
        SELECT [IpAddress] = [dbo].[fn_CLR_ParseIP](val.[IpAddress])
    )
    parsed
OUTER APPLY
    (
        SELECT TOP (1)
            *
        FROM
            [dbo].[cidr]
            _cidr
        WHERE
                _cidr.[range_start] <= parsed.[IpAddress]
            AND _cidr.[range_end]   >= parsed.[IpAddress]
        ORDER BY
            _cidr.[range_start] DESC
    )
    cidr
;
sql-server

1 个回答

  1. Best Answer

    如果您的cidr表中有行没有重叠Start/End范围，那么对于任何给定的 IP 地址最多只有一个匹配的行，您可以：

    1. 使用SELECT TOP 1 ... ORDER BY ...逻辑根据Start列有效地识别单个候选行。
    2. 通过检查列值来确认匹配End。
    3. 将这一切包装在一个中OUTER APPLY，并将其TOP 1进一步包装在一个嵌套的子选择中。
    4. 必须在操​​作之后执行WHERE [End] ...测试，以防止在（第一个）候选行不匹配的情况下扫描额外的行。TOP 1
    OUTER APPLY (
    SELECT candidate.*
    FROM (
        SELECT TOP 1
        FROM cidr
        WHERE cidr.Start <= calc.ParsedIpAddress
        ORDER BY cidr.Start DESC
    ) candidate
    WHERE candidate.[End] >= calc.ParsedIpAddress
) match

    为了有效操作，需要cidr(Start)、cidr(Start, [End])或上的索引。cidr(Start) INCLUDE([End])

    如果您的源cidr表可以具有任意重叠的范围，以至于给定的 IP 可能有多个匹配项，那么我不知道有任何简单而有效的查找方法可以避免范围扫描。索引cidr(Start, [End])至少会在有限宽度（更紧凑）的索引上执行范围扫描，而不是在可能更宽的表（聚集索引）上执行范围扫描。这可能会提供一些性能改进。

    更复杂的解决方案可能涉及cidr根据固定八位字节数（如原始/过时的 A、B、C、D 类子网）或固定位数（/16、/24 等）对表进行隔离/分区 - 对于 IPV6 最多为 16 个八位字节或 128 位。然后，您将优化对每个分区的查找并对结果进行 UNION。这几乎模拟了 SQL Server 实现空间索引的方式。

    • 4

相关问题