EF核心和MySql查询太慢

Question

我在mysql中有两个表，名为市场和交易所。

交换表中大约有150万条记录。

我想从交易所的记录，与最高的更新日期，每个市场。作为查询的结果，我希望返回一个列表，其中包含每个市场的最新更新。

我用linq创建了一个查询，如下所示。但他反应很慢。

大约7-8分钟。我该如何解决这个问题？

谢谢你的回答。

var query = (from exchange in _context.Exchanges
             join market in _context.Markets on exchange.MarketId equals market.Id
             where market.TypeId == 1
             group exchange by exchange.MarketId into grp

             let maxdate = grp.Max(x => x.LastUpdatedDateTime)

             from exchanges in grp
             where exchanges.LastUpdatedDateTime == maxdate
             select new DtoGetAllMarketsWithLastExchanges
             {
                 Id = exchanges.MarketId,
                 Code = exchanges.Markets.Code,
                 Name = exchanges.Markets.Name,
                 LastBuyPrice = exchanges.LastBuyPrice,
                 LastSellPrice = exchanges.LastSellPrice,
                 SeoUrl = exchanges.Markets.SeoUrl,
                 Icon = exchanges.Markets.Icon,
                 LastUpdateDate = exchanges.LastUpdatedDateTime,
                 Rate = exchanges.Rate
             }).ToList();

市场实体级；

[Table("Markets")]
public partial class Markets : BaseEntity
{
    public int TypeId { get; set; }

    public string Code { get; set; }

    public string Name { get; set; }

    public byte Unit { get; set; }

    public int OrderNumber { get; set; }

    public string Icon { get; set; }

    public string SeoUrl { get; set; }

    public virtual List<Exchanges> Exchanges { get; set; }
}

交换实体类；

[Table("Exchanges")]
public partial class Exchanges : BaseEntity
{
    public int MarketId { get; set; }

    public double LastBuyPrice { get; set; }

    public double LastSellPrice { get; set; }

    public double Rate { get; set; }

    public DateTime CreatedDateTime { get; set; }

    public DateTime LastUpdatedDateTime { get; set; }

    [ForeignKey("MarketId")]
    public virtual Markets Markets { get; set; } 

}

Answer 1

查询很慢，因为它使用了当前不支持的结构，这会导致客户评价。大数据下的客户端评估效率低下，这也是其将在EF Core 3.0中删除的原因之一。

因此，诀窍是找到支持的LINQ构造(转换到SQL并执行服务器端)。由于每个EF Core版本都支持不同的构造，所以我不能确切地说在2.0中支持哪种结构，但是在最新的2.2版本中，通常情况下，尝试遵循下一个简单的规则：

• 在可能的情况下，通过手动联接使用导航属性
• 尽可能避免GroupBy。

并始终检查客户端评估警告。在3.0+中，它们无论如何都是运行时异常，因此您必须找到一个可翻译的构造。

下面是在2.2.6中工作的一些功能等价的LINQ查询：

(1)具有Max准则

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
where exchange.LastUpdatedDateTime == market.Exchanges.Max(e => (DateTime?)e.LastUpdatedDateTime)
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}

(2)与(1)相同，但使用!Any (SQL NOT EXISTS)标准：

where !market.Exchanges.Any(e => e.LastUpdatedDateTime < exchange.LastUpdatedDateTime)

(3)与(1)相同，但使用join到Max子查询：

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
join lastExchange in _context.Exchanges
    .GroupBy(e => e.MarketId)
    .Select(g => new { MarketId = g.Key, Date = g.Max(e => e.LastUpdatedDateTime) })
on new { exchange.MarketId, Date = exchange.LastUpdatedDateTime }
equals new { lastExchange.MarketId, lastExchange.Date }
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}