时间戳

首页 > 解决方案 > 为什么这个查询太慢了？获取 A 的列表，其中每个项目都有一个关联的外键 B

sql - 为什么这个查询太慢了？获取 A 的列表，其中每个项目都有一个关联的外键 B

问题描述

我有Website(Id)表，每条记录可能有多个CheckLog(FK WebsiteId)相关联的条目。CheckLog也有一个复合指数[WebsiteId, CreatedTime]。Website只有大约 20 条记录，但加班时间CheckLog会增加，在我遇到这个问题时有 300 万条记录。（请参阅问题末尾的使用 EF Core 的架构）。

我有一个常见的查询是查询所有Websites 的列表，以及零/一个最新CheckLog记录：

return await this.ctx.Websites.AsNoTracking()
    .Select(q => new  WebsiteListItem()
    {
        Website = q,
        LatestCheckLog = q.CheckLogs
            .OrderByDescending(q => q.CreatedTime)
            .FirstOrDefault(),
    })
    .ToListAsync();

我相信该[WebsiteId, CreatedTime]指数应该会有所帮助。但是，查询需要大约 11 秒才能执行。这是翻译后的查询，以及EXPLAIN QUERY PLAN：

      SELECT "w"."Id", "t0"."Id", "t0"."CreatedTime", "t0"."WebsiteId"
      FROM "Websites" AS "w"
      LEFT JOIN (
          SELECT "t"."Id", "t"."CreatedTime", "t"."WebsiteId"
          FROM (
              SELECT "c"."Id", "c"."CreatedTime", "c"."WebsiteId", ROW_NUMBER() OVER(PARTITION BY "c"."WebsiteId" ORDER BY "c"."CreatedTime" DESC) AS "row"
              FROM "CheckLogs" AS "c"
          ) AS "t"
          WHERE "t"."row" <= 1
      ) AS "t0" ON "w"."Id" = "t0"."WebsiteId"

在此处输入图像描述

MATERIALIZE 1
CO-ROUTINE 4
SCAN TABLE CheckLogs AS c USING INDEX IX_CheckLogs_WebsiteId_CreatedTime
USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
SCAN SUBQUERY 4
SCAN TABLE Websites AS w
SEARCH SUBQUERY 1 AS t USING AUTOMATIC COVERING INDEX (WebsiteId=?)

这可以用索引修复吗？如果没有，是否有一种有效的方法来查询它而不创建 N+1 查询？我试图想办法用 2 个查询来做到这一点，但想不出任何更好的方法来像 EF Core 那样翻译它）。

此外，我相信这是一个非常常见的问题，但我不知道应该使用什么关键字来找到此类问题的解决方案。我对此类问题的一般解决方案没有意见（即获取最新Product列表Categories）。谢谢你。

我将 EF Core 用于 DB Schema：

    public class Website
    {

        public int Id { get; set; }

        // Other properties

        public ICollection<CheckLog> CheckLogs { get; set; }

    }

    [Index(nameof(CreatedTime))]
    [Index(nameof(WebsiteId), nameof(CreatedTime))]
    public class CheckLog
    {
        public int Id { get; set; }
        public DateTime CreatedTime { get; set; }

        public int WebsiteId { get; set; }
        public Website Website { get; set; }

        // Other properties

    }

标签： sql.netsqliteindexingentity-framework-core

解决方案

如果您想要的是获得CreatedTime每个最新的行，WebsiteId那么就不需要任何连接。

只需聚合并设置条件：

HAVING MAX(CreatedTime)

这不是标准 SQL，而是利用 SQLite 的裸列：

SELECT *
FROM CheckLogs 
GROUP BY WebsiteId
HAVING MAX(CreatedTime);

如果你想加入它Websites：

SELECT  w.Id, t.Id, t.CreatedTime, t.WebsiteId 
FROM Websites AS w 
LEFT JOIN (
  SELECT *
  FROM CheckLogs 
  GROUP BY WebsiteId
  HAVING MAX(CreatedTime)
) AS t ON w.Id = t.WebsiteId;

推荐阅读