从 net core 2.1 升级到 net 6 时 EF 查询性能下降

Question

我已将 AWS Lambda 从 .NET Core 2.1 升级到 .NET 6，并且遇到了实体框架查询速度比 .NET Core 2.1 生成的查询速度慢的问题。我放大了一个特定的查询，该查询花费的时间太长（2秒以上），其中使用.NET Core 2.1执行相同的代码不到100-200毫秒

我对.NET 6使用以下依赖项

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
  </ItemGroup>

我生成一个dbContext模型使用：

Scaffold-DbContext "server=server.com;port=3306;user=auser;password=pass;database=adatabase" Pomelo.EntityFrameworkCore.MySql -OutputDir DBModels -f

现在执行速度慢得多的代码：

vActiveSessions = _context.BiddingSessions
            .AsNoTracking()
            .Include("Bids.Character")
            .Include("IdItemNavigation")
            .Where(x => x.ClientId == "99IK2BLeIEWdV6bF9jFmcQ==" && x.State == 0).ToList();

问题似乎出在 .Include("Bids.Character") 上，因为当我调试并删除包含查询时快速执行。我使用 LINQPad 7 进行验证，并获取它生成的 SQL 并在 mysql 工作台中运行它，并确认查询运行和执行速度也很慢。生成的 SQL 看起来肯定比我预期的更复杂：

SELECT `b`.`id`, `b`.`clientId`, `b`.`AllowDeletes`, `b`.`Auctioneer`, `b`.`AutoAdjustBids`, `b`.`BidType`, `b`.`CreatedTimestamp`, `b`.`Duration`, `b`.`EndTimestamp`, `b`.`id_item`, `b`.`ItemQuantity`, `b`.`MaximumBid`, `b`.`MinimumBid`, `b`.`notes`, `b`.`State`, `b`.`UpdatedTimestamp`, `i`.`id_item`, `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`Rank`, `t`.`Timestamp`, `t`.`UpdatedTimestamp`, `t`.`User`, `t`.`Value`, `t`.`id_character`, `t`.`clientId`, `t`.`active`, `t`.`class`, `t`.`deleted`, `t`.`gender`, `t`.`guild`, `t`.`id_associated`, `t`.`level`, `t`.`main_change`, `t`.`name`, `t`.`race`, `t`.`rank0`, `i`.`game_itemid`, `i`.`id_game`, `i`.`name`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
LEFT JOIN (
    SELECT `b0`.`id`, `b0`.`session_id`, `b0`.`character_id`, `b0`.`Rank`, `b0`.`Timestamp`, `b0`.`UpdatedTimestamp`, `b0`.`User`, `b0`.`Value`, `c`.`id_character`, `c`.`clientId`, `c`.`active`, `c`.`class`, `c`.`deleted`, `c`.`gender`, `c`.`guild`, `c`.`id_associated`, `c`.`level`, `c`.`main_change`, `c`.`name`, `c`.`race`, `c`.`rank` AS `rank0`
    FROM `Bids` AS `b0`
    INNER JOIN `characters` AS `c` ON `b0`.`character_id` = `c`.`id_character`
) AS `t` ON `b`.`id` = `t`.`session_id`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`, `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`id_character`

本质上，表设置是这样的：

BidSession -->出价 -->字符，其中 Bids 是 Bids 的集合，每个 Bid 仅具有 1 个与其关联的字符。

我不确定我的 fkey 或主键是否导致了问题。最大的困惑是，这在 .NET Core 2.1 中运行得非常好，而我现在不知所措。

如果这也有帮助的话，我可以发布 BidSession/Bids/Character 表模式。

编辑： 当使用 AsSplitQuery 时，它会生成以下 SQL：

SELECT `b`.`id`, `b`.`clientId`, `b`.`AllowDeletes`, `b`.`Auctioneer`, `b`.`AutoAdjustBids`, `b`.`BidType`, `b`.`CreatedTimestamp`, `b`.`Duration`, `b`.`EndTimestamp`, `b`.`id_item`, `b`.`ItemQuantity`, `b`.`MaximumBid`, `b`.`MinimumBid`, `b`.`notes`, `b`.`State`, `b`.`UpdatedTimestamp`, `i`.`id_item`, `i`.`game_itemid`, `i`.`id_game`, `i`.`name`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`
GO

SELECT `t`.`id`, `t`.`session_id`, `t`.`character_id`, `t`.`Rank`, `t`.`Timestamp`, `t`.`UpdatedTimestamp`, `t`.`User`, `t`.`Value`, `t`.`id_character`, `t`.`clientId`, `t`.`active`, `t`.`class`, `t`.`deleted`, `t`.`gender`, `t`.`guild`, `t`.`id_associated`, `t`.`level`, `t`.`main_change`, `t`.`name`, `t`.`race`, `t`.`rank0`, `b`.`id`, `b`.`clientId`, `i`.`id_item`
FROM `BiddingSessions` AS `b`
LEFT JOIN `items` AS `i` ON `b`.`id_item` = `i`.`id_item`
INNER JOIN (
    SELECT `b0`.`id`, `b0`.`session_id`, `b0`.`character_id`, `b0`.`Rank`, `b0`.`Timestamp`, `b0`.`UpdatedTimestamp`, `b0`.`User`, `b0`.`Value`, `c`.`id_character`, `c`.`clientId`, `c`.`active`, `c`.`class`, `c`.`deleted`, `c`.`gender`, `c`.`guild`, `c`.`id_associated`, `c`.`level`, `c`.`main_change`, `c`.`name`, `c`.`race`, `c`.`rank` AS `rank0`
    FROM `Bids` AS `b0`
    INNER JOIN `characters` AS `c` ON `b0`.`character_id` = `c`.`id_character`
) AS `t` ON `b`.`id` = `t`.`session_id`
WHERE (`b`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`b`.`State` = 0)
ORDER BY `b`.`id`, `b`.`clientId`, `i`.`id_item`

它仍然看起来第二个查询效率太低，但我不是专家。

生成的 .NET Core 2.1 EF SQL：

SELECT `x`.`id`, `x`.`clientId`, `x`.`AllowDeletes`, `x`.`Auctioneer`, `x`.`AutoAdjustBids`, `x`.`BidType`, `x`.`CreatedTimestamp`, `x`.`Duration`, `x`.`EndTimestamp`, `x`.`id_item`, `x`.`ItemQuantity`, `x`.`MaximumBid`, `x`.`MinimumBid`, `x`.`notes`, `x`.`State`, `x`.`UpdatedTimestamp`, `x.IdItemNavigation`.`id_item`, `x.IdItemNavigation`.`game_itemid`, `x.IdItemNavigation`.`id_game`, `x.IdItemNavigation`.`name`
FROM `BiddingSessions` AS `x`
LEFT JOIN `items` AS `x.IdItemNavigation` ON `x`.`id_item` = `x.IdItemNavigation`.`id_item`
WHERE (`x`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`x`.`State` = 0)
ORDER BY `x`.`id`
GO

SELECT `x.Bids`.`id`, `x.Bids`.`character_id`, `x.Bids`.`Rank`, `x.Bids`.`session_id`, `x.Bids`.`Timestamp`, `x.Bids`.`UpdatedTimestamp`, `x.Bids`.`User`, `x.Bids`.`Value`, `b.Character`.`id_character`, `b.Character`.`clientId`, `b.Character`.`active`, `b.Character`.`class`, `b.Character`.`deleted`, `b.Character`.`gender`, `b.Character`.`guild`, `b.Character`.`id_associated`, `b.Character`.`level`, `b.Character`.`main_change`, `b.Character`.`name`, `b.Character`.`race`, `b.Character`.`rank`
FROM `Bids` AS `x.Bids`
INNER JOIN `characters` AS `b.Character` ON `x.Bids`.`character_id` = `b.Character`.`id_character`
INNER JOIN (
    SELECT DISTINCT `x0`.`id`
    FROM `BiddingSessions` AS `x0`
    LEFT JOIN `items` AS `x.IdItemNavigation0` ON `x0`.`id_item` = `x.IdItemNavigation0`.`id_item`
    WHERE (`x0`.`clientId` = '99IK2BLeIEWdV6bF9jFmcQ==') AND (`x0`.`State` = 0)
) AS `t` ON `x.Bids`.`session_id` = `t`.`id`
ORDER BY `t`.`id`

看起来 .NET 2.1 不仅将查询分为两个，而且对第二个查询执行完全不同的查询。而且它比 .NET 6 生成的速度快得多（2 秒 vs 500 毫秒）

Answer 1

对于某些查询，EF Core 2 将回退到客户端评估。

看起来您指的是 2 个子表。强制 EF Core 构建单个查询将导致子记录的每个排列的笛卡尔积。

您可能想告诉 EF Core 拆分查询；

_context.BiddingSessions
            .AsNoTracking()
            .AsSplitQuery()
            .Include("Bids.Character")
            .Include("IdItemNavigation")

EF Core 将针对可疑查询引发诊断消息。为了快速捕获这些错误，您可以强制所有诊断程序抛出错误，仅将那些您希望忽略的消息列入白名单；

services.AddDbContextPool<...>(o => o
    .UseSqlServer(connectionString, options =>
    {
        // ...
    })
    .ConfigureWarnings(w => w
#if DEBUG
        .Default(WarningBehavior.Throw)
#endif
        .Ignore(CoreEventId.SensitiveDataLoggingEnabledWarning)
        .Ignore(CoreEventId.PossibleIncorrectRequiredNavigationWithQueryFilterInteractionWarning)
        .Ignore(SqlServerEventId.SavepointsDisabledBecauseOfMARS)
    )
);