MySQL中多标签高效搜索

Question

我有一个简单的数据库模式和示例，如下所示：

CREATE TABLE Media (
id INT AUTO_INCREMENT PRIMARY KEY,
file VARCHAR(255)
);
CREATE TABLE Tag (
id INT AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(255)
);
CREATE TABLE Media_Tag (
media_id INT,
tag_id INT,
PRIMARY KEY(media_id, tag_id)
);
INSERT INTO Media VALUES
(1, "firetruck.jpg"),
(2, "duck.jpg"),
(3, "apple.jpg"),
(4, "banana.jpg");
INSERT INTO Tag VALUES
(1, "red"),
(2, "yellow"),
(3, "mobile"),
(4, "immobile");
INSERT INTO Media_Tag VALUES
(1, 1),
(1, 3),
(2, 2),
(2, 3),
(3, 1),
(3, 4),
(4, 2),
(4, 4);

如果我想搜索单个标签，那很容易：

SELECT
m.*
FROM
Media m
LEFT JOIN Media_Tag mt ON mt.media_id = m.id
LEFT JOIN Tag t ON mt.tag_id = t.id
WHERE
t.label = ?

然而，我有兴趣通过两个标签进行搜索。例如，如果用户搜索"red">和"mobile"，我希望它只返回返回"firetruck.jpg"，而不是"apple.jpg"(仅红色(或"duck.jpg"(只移动(

---

到目前为止，我一直在研究以下解决方案：

SELECT
m.*
FROM
Media m
LEFT JOIN Media_Tag mt1 ON mt1.media_id = m.id
LEFT JOIN Media_Tag mt2 ON mt1.media_id = mt2.media_id AND mt1.tag_id <> mt2.tag_id
LEFT JOIN Tag t1 ON t1.id = mt1.tag_id
LEFT JOIN Tag t2 ON t2.id = mt2.tag_id
WHERE
t1.label = ? AND
t2.label = ?

除了我必须为添加到搜索参数中的每个标记添加两个额外的JOIN子句之外，这是有效的(而且非常快(。如果我不知道会传递多少搜索参数，我需要通过预联接X个表来创建一个具有"最大"允许搜索参数数的查询。

有更好的解决方案吗？

我一直在考虑这样一个想法：

SELECT
m.*
FROM
Media m
LEFT JOIN Media_Tag mt ON mt.media_id = m.id
LEFT JOIN Tag t ON mt.tag_id = t.id
WHERE
t.label IN ("red", "mobile")
GROUP BY
<all fields on m>
HAVING
COUNT(*) = <count-of-parameters>

但是，当我在MySQL Workbench中对500000行的样本数据集使用它时，我遇到了两个问题：

1. 具有多个JOIN的溶液运行0.002秒，而具有GROUP BY和HAVING的溶液运行整整3秒
2. GROUP BY解的结果似乎是按随机顺序排列的，而多重JOIN解的结果则按Media表的主键顺序返回

我不完全确定为什么解决方案如此缓慢。也许HAVING子句在内部是如何工作的，有些地方我不理解。但不管怎样，以看似随机的顺序返回的结果使这成为一个不可用的解决方案，因为我担心它会破坏分页。

---

更新1:

我了解到，在我的500k数据集上，多个JOIN在0.002秒内运行的解决方案有点侥幸。我用来添加数据的脚本添加了一个媒体项目，然后添加了它的标签。这意味着前100个媒体项目的所有标签都可以在标签表的顶部找到。当我执行搜索时，我有一个LIMIT 0,25子句来模拟分页。这提前结束了我的查询，发现了25个匹配的媒体项目，所有这些都可以在标签表的顶部找到。

另一方面，HAVING解决方案扫描整个标签表。这就解释了3秒——这就是扫描一个包含100万行的表所需的时间。

如果我将搜索修改为返回的媒体项目少于25个，那么它突然必须扫描整个表，无法提前退出，JOIN解决方案也需要3秒。

更新2:

我认为我在最初的帖子中不清楚，所以我想对此进行扩展。我在这里的首要任务是效率，而不是数据完整性、代码简单性或规范化。我当前的数据库模式是规范化的，但如果可以进行更高效的搜索，我愿意对其进行去规范化。

作为一个实验，我用一个新字段修改了Media表：

UPDATE TABLE Media ADD COLUMN all_tags varchar(255);
UPDATE
Media m
INNER JOIN (
SELECT
m.id,
GROUP_CONCAT(t.label ORDER BY t.label ASC) as all_tags
FROM
Media b
LEFT JOIN Media_Tag mt ON mt.media_id = m.id
LEFT JOIN Tag t ON mt.tag_id = t.id
GROUP BY
m.id
ORDER BY
m.id
) j ON j.id = m.id
SET m.all_tags = j.all_tags;

我的新桌子看起来是这样的：

+----+---------------+-----------------+
| id |      file     |     all_tags    |
+----+---------------+-----------------+
|  1 | firetruck.jpg |   mobile,red    |
|  2 |    duck.jpg   |  mobile,yellow  |
|  3 |   apple.jpg   |   immobile,red  |
|  4 |   banana.jpg  | immobile,yellow |
+----+---------------+-----------------+

然后我可以搜索标签，比如：

SELECT * FROM Media WHERE all_tags LIKE "%tag1%tag2%...%";

只要tag1、tag2等按字母顺序排列(就像all_tags按字母顺序一样(，这就可以了。

这能够在大约350毫秒内对我的500k个媒体项目的数据集执行全表搜索(返回的搜索少于分页限制(。好多了，但仍然不是我想要的。我的目标是尽可能将响应时间控制在100毫秒以下。

为了好玩，我在all_tags列上添加了一个索引，并进行了完全匹配的搜索：

SELECT * FROM Media WHERE all_tags = "mobile,red";

这在0.2毫秒内完成。不幸的是，我不能指望准确的匹配。搜索"mobile"one_answers"red"这两个标签的人还应该找到一个带有三个标签"cat"、"mobile"one_answers"red"的Media项目——由于"cat"按字母顺序位于"mobile(之前，确保它出现在结果集中的唯一方法是在我的LIKE子句中使用一个起始通配符，这可以防止使用索引。

我一直在想更聪明的解决方案，比如为"all_tags_starting_with_A"、"all_ttags_starting_with_B"等设置26列，但我似乎无法确定最佳选项。

Answer 1

GROUP BY的解决方案当然更容易维护，因此值得尝试，但仅适用于Media_Tag和Tag的连接，并将结果连接到Media:

SELECT m.*
FROM Media m
INNER JOIN (
SELECT mt.media_id
FROM Media_Tag mt INNER JOIN Tag t 
ON mt.tag_id = t.id
WHERE t.label IN ('red', 'mobile')
GROUP BY mt.media_id
HAVING COUNT(*) = 2
) t ON t.media_id = m.id;

我将所有联接更改为INNER，因为我看不到LEFT联接的意义
或者使用运算符IN而不是加入Media:

SELECT m.*
FROM Media m
WHERE m.id IN (
SELECT mt.media_id
FROM Media_Tag mt INNER JOIN Tag t 
ON mt.tag_id = t.id
WHERE t.label IN ('red', 'mobile')
GROUP BY mt.media_id
HAVING COUNT(*) = 2
);