记一次EF查询单号优化

前言

最近发现在日志中发现有一个接口查询超时,发现查询使用的是模糊,造成查询索引没有生效,便调整了这一块的查询.这里也学习一些Abp.下面示例代码,就是使用Abp的.

示例代码

最初的代码:
/// <summary>
/// 为测试代码,真实环境条件更多,一般都会有一个时间戳列(索引),先限制查询范围
/// </summary>
/// <param name="sn">单号</param>
/// <returns></returns>
/// <exception cref="UserFriendlyException"></exception>
public async Task<PeopleDto> GetPeopleAsync(string sn)
{
    if (sn.IsNullOrWhiteSpace())
    {
        throw new UserFriendlyException("查询单号不能为空");
    }
    IQueryable<People> query = await _peopleRepository.GetQueryableAsync();
    query = query.WhereIf(!sn.IsNullOrWhiteSpace(), p => p.SN.Contains(sn));  //这里只是单个条件,真实环境有多个条件
    var item = await ObjectMapper.GetMapper().ProjectTo<PeopleDto>(query).FirstOrDefaultAsync();
    if (item == null)
    {
        throw new UserFriendlyException($"没有查询到单号:{sn}数据");
    }
    return item;
}

使用SqlServer Profile监控生成SQL代码为:

SELECT TOP(1) [p].[SN]
FROM [Peoples] AS [p]
WHERE [p].[SN] LIKE N'%IP77496973775311%'

查看Sql执行计划:

模糊查询索引会失效

优化查询条件

这里优化的方式,先用正则表达式匹配一下单号,是否符合单号的规则,如果符合规则,就使用完整匹配,不在使用模糊查询,如果不符合单号规则,退化为模糊查询.
/// <summary>
/// 为测试代码
/// </summary>
/// <param name="sn">单号</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"></exception>
public async Task<PeopleDto> GetAsync(string sn)
{
    if (sn.IsNullOrWhiteSpace())
    {
        throw new UserFriendlyException("查询单号不能为空");
    }

    IQueryable<People> query = await _peopleRepository.GetQueryableAsync();

    //增加FilterBySN扩展方法
    var item = await ObjectMapper.GetMapper().ProjectTo<PeopleDto>(query.FilterBySN(sn)).FirstOrDefaultAsync();
    if (item == null)
    {
        throw new UserFriendlyException($"没有查询到单号:{sn}数据");
    }
    return item;
}

看一下扩展方式的代码实现:

public static partial class PeopleExtension
{
#if NET7_0_OR_GREATER
    //在.Net 7加入 正则表达式代码生成,简单不单独说明怎么使用
    //1. 使用RegexGenerator特性
    [GeneratedRegex(@"^[A-Z]{2,3}\d{13,14}", RegexOptions.IgnoreCase)]

    //2. 使用static和partial进行声明
    private static partial Regex SN();
    private static bool IsSNMatch(string sn) => SN().IsMatch(sn);
#else
    //兼容老版本
    private static Regex SNRegex = new Regex(@"^[A-Z]{2,3}\d{13,14}", RegexOptions.IgnoreCase);

    private static bool IsSNMatch(string sn) => SNRegex.IsMatch(sn);
#endif

    public static IQueryable<People> FilterBySN(this IQueryable<People> query, string sn)
    {
        if (IsSNMatch(sn))  
        {
            //1. 符合单号规则,精准查询
            return query.Where(x => x.SN == sn);
        }
        else
        {
            //2. 退化为模糊查询,还可以用StartsWith/EndsWith进行查询
            return query.Where(x => x.SN.Contains(sn));
        }
    }
}

测试一下符合规则的单号:

测试符合规则单号的查询

生成的Sql代码为:

SELECT TOP(1) [p].[SN]
FROM [Peoples] AS [p]
WHERE [p].[SN] = N'IP77496973775311'

查看Sql执行计划:

看生成的sql执行计划,索引是否生效

符合规则的单号,查询起来就很快,本次优化严格上不能说一次优化,因为本质上没有彻底解决查询慢的问题,看看后面还有其他的解决方式没.

秋风 2024-04-04
文章分类
algorithm windows linux c cpp csharp javascript java dependon mono javascript browser asp.net core go asp.net database python asm rust english TypeScript EntityFramework Core
友情链接