LinqToSQL中AsNoTracking为何增大大数据集查询耗时而非优化？

问题背景 #

涉及百万级记录表的左连接查询，当前耗时6-7秒，未来数据量将翻倍。尝试AsNoTracking()后耗时反而增加，查询代码如下：

var res = (from rd in db.RequiredData
    join rdm in db.RequiredDataMapper
        on new { t1 = rd.Id } equals new { t1 = rdm.Id } 
    into rd_rdm
    from rdm in rd_rdm.DefaultIfEmpty()
    where (rdm.Name == null || rdm.Name == nameof(Name))
        && (model.TypeId == 0 || rdm.Id == model.STypeId)
        && (ApplicationType.ToLower() != "QR".ToLower() ?
            rdm.ATypes.TypeName.ToLower() != "QR".ToLower() : 1 == 1)
                select rd)
                .OrderByDescending(s => s.date)
                .AsNoTracking();

注：RequiredDataMapper是维护RequiredData与其他表多对多关系的桥接表

优化方案 #

1. 补全关键索引（优先级最高）

数据库索引是性能提升的核心，针对你的查询场景，必须检查以下索引是否存在：

• RequiredData表：给date字段创建倒序索引，避免内存排序开销：

  CREATE INDEX IX_RequiredData_Date ON RequiredData(date DESC);
  

• RequiredDataMapper表：创建复合索引覆盖连接和过滤条件，包含Id、Name、TypeId：

  CREATE INDEX IX_RequiredDataMapper_Id_Name_TypeId ON RequiredDataMapper(Id, Name, TypeId);
  

• ATypes表：给TypeName字段创建普通索引（大小写不敏感场景），或函数索引（大小写敏感场景）：

  -- 大小写不敏感场景
  CREATE INDEX IX_ATypes_TypeName ON ATypes(TypeName);
  -- 大小写敏感场景
  CREATE INDEX IX_ATypes_LowerTypeName ON ATypes(LOWER(TypeName));
  

2. 重构查询逻辑，用Exists替换左连接

你最终只需要RequiredData的数据，左连接后过滤会产生冗余行，用Exists子查询能让数据库提前过滤，减少数据传输：

var res = db.RequiredData
    .Where(rd => 
        -- 左连接无匹配的情况（对应原逻辑rdm.Name == null）
        !db.RequiredDataMapper.Any(rdm => rdm.Id == rd.Id)
        || 
        -- 匹配到符合条件的rdm
        db.RequiredDataMapper.Any(rdm => 
            rdm.Id == rd.Id 
            && rdm.Name == nameof(Name)
            && (model.TypeId == 0 || rdm.Id == model.STypeId)
            && (
                ApplicationType.Equals("QR", StringComparison.OrdinalIgnoreCase) 
                || !rdm.ATypes.TypeName.Equals("QR", StringComparison.OrdinalIgnoreCase)
            )
        )
    )
    .OrderByDescending(s => s.date)
    .AsNoTracking();

用StringComparison.OrdinalIgnoreCase代替ToLower()，避免不必要的字符串转换

3. 移除冗余的字符串转换

多次ToLower()会导致数据库无法使用索引（除非有函数索引）。如果数据库是大小写不敏感排序规则，直接用==判断即可：

// 替换前
ApplicationType.ToLower() != "QR".ToLower()
// 替换后
ApplicationType != "QR"

若必须区分大小写，用StringComparison.Ordinal判断，避免ToLower()的性能损耗。

4. 强制分页（非全量查询场景）

百万级数据返回全量结果本身就是性能瓶颈，加上分页逻辑：

var pageIndex = model.PageIndex;
var pageSize = model.PageSize;
var res = ... // 前面的查询逻辑
    .Skip(pageIndex * pageSize)
    .Take(pageSize);

5. 对比EF生成的SQL执行计划

AsNoTracking()变慢可能是因为EF生成的SQL执行计划差异，用EF日志查看生成的SQL：

db.Database.Log = sql => Console.WriteLine(sql);
var res = ... // 你的查询

对比加不加AsNoTracking()的SQL，在数据库中查看执行计划，针对性调整索引或查询逻辑。

6. 限定数据范围（业务允许的话）

如果业务上可以限定date的时间范围（比如只查最近30天），直接减少扫描的数据量：

var startDate = DateTime.Now.AddDays(-30);
var res = db.RequiredData
    .Where(rd => rd.date >= startDate)
    // 后续查询逻辑

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