.NET Core 2.1 Web API房产网站搜索性能优化求助

先给你梳理下当前代码里的核心性能瓶颈，再一步步给出可落地的优化方案：

1. 修复字符串匹配导致的索引失效问题 #

你现在用的ToLower().Contains()是典型的非SARGable查询（无法利用索引的查询），SQL Server遇到列上的函数操作（比如ToLower()）会直接放弃使用索引，转而走全表扫描——这也是你建的非聚集索引没生效的核心原因之一。

优化方案： #

• 先把数据库列的排序规则设置为不区分大小写（比如SQL_Latin1_General_CP1_CI_AS，CI就是Case Insensitive），这样可以直接去掉ToLower()；
• 改用EF Core的EF.Functions.Like或者全文索引来做模糊匹配：

  // 替换原有的关键词匹配逻辑
  if (!string.IsNullOrEmpty(model.Keyword))
  {
      var keyword = model.Keyword; // 无需转小写
      query = query.Where(a => 
          EF.Functions.Like(a.BuildTitle, $"%{keyword}%")
          || EF.Functions.Like(a.Description, $"%{keyword}%")
          || EF.Functions.Like(a.ZipCode, $"%{keyword}%")
          || (a.Region != null && a.Region.Localizations.Any(b => EF.Functions.Like(b.Name, $"%{keyword}%")))
          || (a.District != null && a.District.Localizations.Any(b => EF.Functions.Like(b.Name, $"%{keyword}%")))
          || (a.Zone != null && a.Zone.Localizations.Any(b => EF.Functions.Like(b.Name, $"%{keyword}%")))
      );
  }
  

• 如果模糊查询需求频繁，强烈建议给BuildTitle、Description、Localizations.Name这些字段建全文索引，比Like %xxx%效率高10倍以上：

  -- 创建全文目录
  CREATE FULLTEXT CATALOG ftRealEstateCatalog AS DEFAULT;
  -- 给Buildings表的标题、描述建全文索引
  CREATE FULLTEXT INDEX ON Buildings (BuildTitle, Description) KEY INDEX PK_Buildings;
  -- 给Localizations表的Name建全文索引
  CREATE FULLTEXT INDEX ON Localizations (Name) KEY INDEX PK_Localizations;
  

  然后EF里用EF.Functions.Contains来查询：

  query = query.Where(a => 
      EF.Functions.Contains(a.BuildTitle, keyword)
      || EF.Functions.Contains(a.Description, keyword)
      || ...
  );
  

2. 彻底避免重复查询数据库 #

当前代码里犯了一个低级但影响极大的错误：同一个IQueryable被枚举了三次（Count()、Skip/Take()、Locations的Select()），这意味着数据库要执行三次完全相同的过滤逻辑，性能直接打三折。

优化方案： #

先把分页后的结果一次性拉到内存，再基于内存数据生成Buildings和Locations：

// 1. 先获取总条数（仅一次数据库查询）
var totalCount = query.Count();

// 2. 获取分页后的实体列表（第二次数据库查询）
var paginatedBuildings = query
    .Skip(model.PageSize * (model.CurrentPage - 1))
    .Take(model.PageSize)
    .ToList(); // 执行SQL，把数据拉到内存

// 3. 基于内存数据生成结果，不再访问数据库
var result = paginatedBuildings.Select(a =>
{
    var building = a.MapTo<BuildingShortDetailsViewModel>();
    building.LoadEntity(a);
    return building;
});

var locations = paginatedBuildings.Select(a => new LocationModel
{
    BuildingId = a.Id,
    Latitude = a.Latitude?.ToString(),
    Longitude = a.Longitude?.ToString(),
    OwnerPrice= a.OwnerPrice,
    Size= a.Size,
    SizeName= a.SizeName.Id,
    BuildingPhotos= a.BuildingPhotos,
    BuildAction=a.BuildAction.Id,
});

var searchResult = new SearchResult
{
    Filter = model,
    Locations = locations,
    Buildings = result,
    TotalCount = totalCount
};

3. 优化多表关联与过滤逻辑 #

针对BuildingRooms的循环查询 #

你当前的循环Where会生成多个EXISTS条件，要确保BuildingRooms表有合适的索引：

CREATE NONCLUSTERED INDEX IX_BuildingRooms_RoomType_RoomCount 
ON BuildingRooms (RoomType, RoomCount) 
INCLUDE (BuildingId); -- 包含关联字段，避免回表

简化Size过滤逻辑 #

原代码重复判断了a.SizeNameId == model.Size.SizeNameId，可以简化：

if (model.Size != null && model.Size.SizeNameId > 0)
{
    query = query.Where(a => a.SizeNameId == model.Size.SizeNameId);
    if (model.Size.SizeFrom.HasValue)
    {
        // 去掉重复的SizeNameId判断
        query = query.Where(a => a.Size >= model.Size.SizeFrom && a.Size <= model.Size.SizeTo);
    }
}

4. 索引优化策略 #

基于你的过滤和排序需求，建议创建以下非聚集索引：

-- 针对区域过滤+排序的覆盖索引
CREATE NONCLUSTERED INDEX IX_Buildings_RegionId_Sort 
ON Buildings (RegionId) 
INCLUDE (OwnerPrice, CreatedDate, Latitude, Longitude);

-- 针对价格范围+排序的覆盖索引
CREATE NONCLUSTERED INDEX IX_Buildings_OwnerPrice 
ON Buildings (OwnerPrice) 
INCLUDE (CreatedDate, Latitude, Longitude);

-- 针对地理位置过滤的索引
CREATE NONCLUSTERED INDEX IX_Buildings_LatLong 
ON Buildings (Latitude, Longitude) 
INCLUDE (OwnerPrice);

-- 针对创建时间排序的索引
CREATE NONCLUSTERED INDEX IX_Buildings_CreatedDate 
ON Buildings (CreatedDate) 
INCLUDE (OwnerPrice);

创建后可以用SQL Server的「实际执行计划」检查索引是否被调用，如果还是没用到，可能是索引选择性太差（比如某列重复值太多），可以调整索引列的顺序。

5. 其他细节优化 #

• 移除生产环境的StackTrace暴露：当前异常返回里包含了StackTrace，这会泄露敏感代码信息，建议只返回错误消息，把StackTrace记录到日志里；
• 提前过滤空值：比如model.Points的判断，可以先检查Latitude和Longitude是否有值，避免无效的范围查询；
• 避免不必要的关联加载：如果LoadEntity方法里有额外的关联查询，建议用Include提前预加载，避免N+1查询问题。

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