如何在SQL及ORM中按二进制数据前缀查询数据行？

问题背景 #

你需要查询二进制数据列中所有以\x00\xff\xaa开头的数据行，当前用EF编写的代码：

IEnumerable<KeyValue> keyValues = Db.KeyValue
    .Where(kv => kv.Key.KeyBytes.Take(key.Length).SequenceEqual(key));

这段代码会将全表数据拉取到内存后再过滤，数据量大时效率极低，完全无法满足性能需求。

关系型数据库原生实现思路 #

不同数据库提供了原生的二进制前缀匹配语法，直接使用这些语法可利用索引，大幅提升查询性能：

1. SQL Server #

推荐用LIKE运算符（可命中列索引），或用LEFT函数截取对比：

-- 方法1：LIKE（优先选择，支持索引范围扫描）
SELECT * FROM KeyValue WHERE KeyBytes LIKE 0x00FFAA + '%'
-- 方法2：LEFT截取对比
SELECT * FROM KeyValue WHERE LEFT(KeyBytes, 3) = 0x00FFAA

注意：需确保KeyBytes列存在非聚集索引，避免全表扫描。

2. MySQL #

支持LIKE或LEFT函数，二进制类型匹配时可配合BINARY关键字：

-- 方法1：LIKE匹配前缀
SELECT * FROM KeyValue WHERE KeyBytes LIKE BINARY '\x00\xff\xaa%'
-- 方法2：LEFT截取对比
SELECT * FROM KeyValue WHERE LEFT(KeyBytes, 3) = BINARY '\x00\xff\xaa'

若列类型为VARBINARY，直接使用LIKE即可完成前缀匹配，无需额外BINARY关键字。

3. PostgreSQL #

可用LEFT函数截取对比，或用正则运算符~匹配前缀：

-- 方法1：LEFT截取对比
SELECT * FROM KeyValue WHERE left(KeyBytes, 3) = '\x00\xff\xaa'::bytea
-- 方法2：正则前缀匹配
SELECT * FROM KeyValue WHERE KeyBytes ~ '^\x00\xff\xaa'::bytea

EF/EF Core中的高效实现 #

要让EF生成原生数据库前缀匹配SQL，而非在内存过滤，可采用以下几种方式：

1. 直接执行原生SQL（最直接高效） #

通过FromSqlRaw执行手写的原生查询，完全控制SQL逻辑：

var prefix = new byte[] { 0x00, 0xFF, 0xAA };
// SQL Server示例
var keyValues = Db.KeyValue
    .FromSqlRaw("SELECT * FROM KeyValue WHERE KeyBytes LIKE @prefix + '%'", 
        new SqlParameter("@prefix", prefix))
    .ToList();

2. 用EF.Functions封装数据库函数（符合EF风格） #

EF Core提供EF.Functions调用数据库原生函数，不同数据库适配如下：

• SQL Server：使用EF.Functions.Like

var prefix = new byte[] { 0x00, 0xFF, 0xAA };
// 构造带通配符的二进制前缀（0x25是'%'的ASCII码）
var searchPattern = prefix.Concat(new byte[] { 0x25 }).ToArray();
var keyValues = Db.KeyValue
    .Where(kv => EF.Functions.Like(kv.KeyBytes, searchPattern))
    .ToList();

• 通用方案：LEFT函数对比
  若需跨数据库兼容，可使用EF.Functions.Left（EF Core 3.0+支持）：

var prefix = new byte[] { 0x00, 0xFF, 0xAA };
var keyValues = Db.KeyValue
    .Where(kv => EF.Functions.Left(kv.Key.KeyBytes, prefix.Length) == prefix)
    .ToList();

该写法会被EF翻译成对应数据库的LEFT函数SQL，只要列有索引即可高效执行。

3. 自定义EF查询转换（进阶方案） #

若需频繁使用二进制前缀搜索，可自定义扩展方法并注册查询转换：

// 定义扩展方法
public static class EfBinaryExtensions
{
    public static bool StartsWithBinary(this byte[] column, byte[] prefix)
    {
        // 仅用于EF查询转换，运行时不会执行
        throw new NotImplementedException("该方法仅用于EF查询翻译");
    }
}

// 在DbContext的OnModelCreating中注册转换逻辑
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(() => EfBinaryExtensions.StartsWithBinary(default, default))
        .HasTranslation(args =>
        {
            var column = args[0];
            var prefix = args[1];
            // 生成LEFT(column, LEN(prefix)) = prefix的SQL逻辑
            var length = EF.Functions.Length(prefix);
            var leftColumn = EF.Functions.Left(column, length);
            return Expression.MakeBinary(ExpressionType.Equal, leftColumn, prefix);
        });
}

// 使用时直接调用扩展方法
var keyValues = Db.KeyValue
    .Where(kv => kv.Key.KeyBytes.StartsWithBinary(prefix))
    .ToList();

关键优化要点 #

• 必须给二进制列KeyBytes创建非聚集索引，否则无论用哪种方法都会触发全表扫描，性能无提升。
• 绝对避免内存过滤逻辑（如原代码的Take+SequenceEqual），这种方式会将全表数据加载到应用服务器内存，完全不适合大数据量场景。
• 优先使用数据库原生的LIKE '前缀%'模式，多数数据库会将其优化为索引范围扫描，性能略优于LEFT函数。

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