MySQL/Eloquent 查询优化

Question

我有一个包含多个表的数据库，我想优化的查询中涉及的只有 4 个。albums , songs , genres , genre_song一首歌可以有很多流派，一个流派可以有很多歌曲。一张专辑可以有很多首歌。专辑通过歌曲与流派相关。
目标是能够推荐与专辑类型相关的专辑。
所以这让我有了这个查询。

SELECT *
FROM `albums`
WHERE EXISTS
    (SELECT *
     FROM `songs`
     WHERE `albums`.`id` = `songs`.`album_id`
       AND EXISTS
         (SELECT *
          FROM `genres`
          INNER JOIN `genre_song` ON `genres`.`id` = `genre_song`.`genre_id`
          WHERE `songs`.`id` = `genre_song`.`song_id`
            AND `genres`.`id` IN (6)))
  AND `id` <> 37635
  AND `published` = 1
ORDER BY `release_date` DESC
LIMIT 6

这个查询需要我在 1.4s 和 1.6s 之间。 我想尽可能减少它 .理想的目标是 小于 10ms 😁
我已经在几个表中使用索引，我已经设法将其他查询的时间从最多 4 秒减少到只有 15-20 毫秒。我愿意使用任何东西来将性能降低到最低限度。
我正在使用 Laravel，所以这将是 Eloquent 的查询。

$relatedAlbums = Album::whereHas('songs.genres', function ($query) use ($album) {
        $query->whereIn('genres.id', $album->genres->pluck('id'));
    })->where('id', '<>', $album->id)
    ->orderByDesc('release_date')
    ->take(6)
    ->get();

注意:以前，加载了流派。
如果您想重新创建数据库中的表和一些假数据， here is the structure

Answer 1

在没有看到真实数据的情况下很难进行猜测......但无论如何:
我认为问题在于，即使您将所需的行数限制为 6，您也必须阅读所有专辑表，因为:

您正在通过非索引列过滤它们

您正在按非索引列对它们进行排序

您不知道哪些专辑会入选(将有所需流派的歌曲)。因此，您计算所有这些，然后按 release_date 排序，并保留前 6 个

如果您访问的专辑处于已排序的发布状态和发布日期，一旦您获得前 6 个入选的专辑，mysql 就可以停止处理查询。当然，你可能有“运气不好”，也许有流派6歌曲的专辑是最古老的专辑，因此无论如何你都必须阅读和处理许多专辑。无论如何，这种优化不应该受到伤害，因此值得尝试，并且应该期望数据在某种程度上是偶然分布的。
此外，如其他答案所述，您实际上并不需要访问 geres 表(尽管这可能不是查询中最糟糕的问题)。您可以只访问genre_song，并且可以为您需要的两列创建一个新索引。

create index genre_song_id_id on genre_song(genre_id, song_id);

请注意，只有在更改查询时，先前的索引才有意义(如答案末尾的建议)
对于专辑表，您可以创建这两个索引中的任何一个:

create index release_date_desc_v1 on albums (published, release_date desc);

create index release_date_desc_v2 on albums (release_date desc, published);

选择最适合您的数据的索引:

如果已发布专辑的百分比“低”，您可能想要使用 _v1

否则，_v2 索引会更好

请同时测试它们，但不要让两个索引同时共存。如果测试 _v1，请确保删除 _v2，反之亦然。
另外，将您的查询更改为不使用 genre table :

SELECT *
FROM `albums`
WHERE EXISTS
    (SELECT *
     FROM `songs`
     WHERE `albums`.`id` = `songs`.`album_id`
       AND EXISTS
         (SELECT *
          FROM `genre_song`
          WHERE `songs`.`id` = `genre_song`.`song_id`
            AND `genre_song`.`genre_id` IN (6)))
  AND `id` <> 37635
  AND `published` = 1
ORDER BY `release_date` DESC
LIMIT 6;