如何对Varbinary类型数据按前缀+数值范围高效查询？

背景 #

游戏服务器中，属性数据以varbinary(50)类型存储，数据按3字节为一组划分：

• 每组首字节为属性类型前缀
• 后两字节为对应属性的数值
• 分组无固定位置，且不存在全零间隙

需求 #

筛选出包含指定前缀，且对应属性数值超过设定阈值的记录。例如：前缀为26（十六进制0x1A）时，需找出数值超过9600的记录（对应十六进制组合为0x1A 0x25 0x80，9600转十六进制为0x2580）。

已尝试方案 #

1. 直接比较Varbinary值 #

DECLARE @t TABLE (
    val VARBINARY(MAX)
)
INSERT INTO @t SELECT 0x00000100000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00001000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00010000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00100000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00000f00000000000000000000000000000000000000000000000000

declare @pattern varbinary(max)
declare @pattern2 varbinary(max)
set @pattern = 0x0001
set @pattern2 = @pattern+0xFF

select @pattern,@pattern2

SELECT
    *
FROM @t
WHERE val<@pattern
OR val>@pattern2

局限性：仅能匹配固定位置的短模式，无法定位任意位置的3字节分组。

2. 转换为Varchar后模糊匹配 #

select * 
from table
where CONVERT(varchar(max),val,2) like '%data%'

局限性：可精准查找指定前缀的分组，但无法对后续数值进行范围筛选。

3. 手动枚举符合范围的模式 #

可行但代码冗余，查询效率极低，不适用于生产环境。

高效解决方案 #

核心思路是逐段扫描Varbinary数据，提取所有3字节分组，检查前缀匹配性并验证数值是否超过阈值，以下是两种实现方式：

方式1：递归CTE扫描所有3字节分组 #

-- 定义参数：目标前缀（十六进制）、数值阈值（十进制）
DECLARE @TargetPrefix VARBINARY(1) = 0x1A; -- 对应十进制26
DECLARE @Threshold INT = 9600;

-- 转换阈值为两字节十六进制（此处假设数值为大端存储，小端需反转字节）
DECLARE @ThresholdBinary VARBINARY(2) = CAST(@Threshold AS BINARY(2));

WITH VarbinarySegments AS (
    -- 初始行：从第1字节开始的3字节分组
    SELECT 
        val,
        1 AS StartPos,
        SUBSTRING(val, 1, 3) AS Segment
    FROM YourTableName
    WHERE DATALENGTH(val) >= 3

    UNION ALL

    -- 递归扫描后续所有3字节分组
    SELECT 
        val,
        StartPos + 3 AS StartPos,
        SUBSTRING(val, StartPos + 3, 3) AS Segment
    FROM VarbinarySegments
    WHERE StartPos + 3 <= DATALENGTH(val)
)
-- 筛选符合条件的记录（去重避免同一记录重复返回）
SELECT DISTINCT val
FROM VarbinarySegments
WHERE 
    SUBSTRING(Segment, 1, 1) = @TargetPrefix
    AND SUBSTRING(Segment, 2, 2) > @ThresholdBinary;

说明：若数值为小端存储，需将SUBSTRING(Segment, 2, 2)和@ThresholdBinary都用REVERSE()处理后再比较。

方式2：CLR函数（SQL Server专属） #

若递归CTE性能无法满足大数据量需求，可编写CLR函数实现高效字节扫描：

1. 创建C#类库实现扫描逻辑
2. 将CLR函数部署到SQL Server
3. 直接调用筛选

示例CLR核心逻辑（C#）：

public static bool HasMatchingSegment(byte[] data, byte targetPrefix, int threshold)
{
    byte[] thresholdBytes = BitConverter.GetBytes((short)threshold);
    // 大端存储需反转字节（根据实际存储调整）
    if (BitConverter.IsLittleEndian)
        Array.Reverse(thresholdBytes);

    for (int i = 0; i <= data.Length - 3; i += 3)
    {
        if (data[i] == targetPrefix)
        {
            byte[] valueBytes = new byte[2];
            Array.Copy(data, i+1, valueBytes, 0, 2);
            if (BitConverter.IsLittleEndian)
                Array.Reverse(valueBytes);
            short value = BitConverter.ToInt16(valueBytes, 0);
            if (value > threshold)
                return true;
        }
    }
    return false;
}

部署后SQL调用示例：

SELECT *
FROM YourTableName
WHERE dbo.HasMatchingSegment(val, 0x1A, 9600) = 1;

优势：CLR函数字节扫描效率远高于T-SQL递归，适合处理海量数据。

注意事项 #

• 确认数值的字节序（大端/小端），直接影响数值比较的正确性
• 若数据量极大，建议考虑拆分存储结构，将属性分组拆分为独立行存储，便于建立索引优化查询

内容的提问来源于stack exchange，提问作者smoka smokov