mysql - 如何有效地选择具有多对多关系的关联项目的不同标签？

Question

我正在 MySQL 中构建一个包含项目和标签的系统，具有多对多关系(通过中间表)。当我扩大规模时，一个查询变得慢得令人无法接受，但我正在努力提高它的效率。

相关查询相当于“选择所有与 x 类型的项目关联的标签”。这是一个稍微简化的版本:

SELECT DISTINCT(t.id)
FROM tags t 
INNER JOIN items_tags it ON it.tag_id = t.id
INNER JOIN items i ON it.item_id = i.id
WHERE i.type = 10

我在 t.id、item.id 和“it.tag_id, it.item_id”上有唯一的主索引。我遇到的问题是 items_tags 表的大小(~1,400,000 行)使得查询花费的时间太长(这里让我困惑的一件事是 phpMyAdmin 似乎认为查询很快 - 它将它的时间缩短为几倍) ms，但实际上似乎需要 6 或 7 秒)。

我觉得好像可能有一种方法可以将 items_tags 表连接到自身以减少结果集的大小(并且可能消除对 DISTINCT 子句的需要)，但我不知道如何实现。 .. 或者，我想到可能有更好的方法来索引事物。任何帮助或建议将不胜感激!

Answer 1

好吧，郑重声明，这就是对我有用的方法(尽管如果有人有任何其他建议，我仍然会感兴趣)。

有人指出(在上面的评论中 - 感谢@Turophile!)，由于标签 id 在 items_tags 表中可用，我可以将 tags 表排除在外。我实际上确实需要标签表中的其他字段(例如名称)(我稍微简化了问题的查询)，但我发现从上面的查询中删除标签表并将标签表连接到其结果中要快得多(EXPLAIN 显示它允许扫描更少的行)。这使得查询看起来更像这样:

SELECT 
    tags.id,
    tags.name
FROM tags 
INNER JOIN (
    SELECT DISTINCT(it.tag_id) AS tag_id
    FROM items_tags it
    JOIN items i ON it.item_id = i.id
    WHERE i.type = 10 
) it ON tags.id = it.tag_id

这比之前版本的查询快了约 10 倍(平均时间从约 27 秒减少到约 2.5 秒)。

最重要的是，向 items.type 添加索引进一步改善了情况(将平均时间从约 2.5 秒减少到约 1.2 秒)。