MySQL标记问题: how to select an item that has been tagged as X, Y和Z？

Question

我正在处理一个数据库，其中的项目被“标记”了一定次数。

项目(10 万行)

• 编号
• 姓名
• 其他内容

标签(10k 行)

• 编号
• 姓名

item2tag(1,000,000 行)

• item_id
• tag_id
• 计数

我正在寻找最快的解决方案:

选择已标记为 X、Y 和 Z 的项目(其中 X、Y 和 Z 对应于(可能)标记名称)？

这是我目前所拥有的......我只是想确保我以最好的方式做到这一点:

首先从名字中获取tag_ids:

SELECT tag.id WHERE name IN ("X","Y","Z");

然后我按这些 tag_id 分组并使用 Having 来过滤结果:

SELECT item2tag.*, count(tag_id)
  FROM item2tag
  WHERE tag_id=1 or tag_id=2 or tag_id=3
GROUP BY item_id
HAVING count(tag_id)=3;

然后我就可以从具有这些 ID 的项目中进行选择。

SELECT * FROM item WHERE id IN ([results from prior query])

我在 item2tag 中有数百万行，索引在 (item_id, tag_id) 上。这将是最快的解决方案吗？

Answer 1

您建议的方法可能是执行查询的最常用方法，但可能不是最快的方法。使用联接可以更快:

SELECT T1.item_id
FROM item2tag T1
JOIN item2tag T2 ON T1.item_id = T2.item_id
JOIN item2tag T3 ON T2.item_id = T3.item_id
WHERE T1.tag_id = 1 AND T2.tag_id = 2 AND T3.tag_id = 3

您应该确保拥有以下索引:

• (item_id, tag_id) 上的主键
• (tag_id) 的索引。

我在几个不同的场景中针对原始查询对该查询进行了性能测试。

• 对于表中几乎每个项目都至少标记了一个正在搜索的标签的情况，原始查询大约需要 5 秒，而 JOIN 版本大约需要 10 秒 - 稍微慢一些。
• 对于其中两个标记非常频繁出现而其中一个标记很少出现的情况，原始查询大约需要 0.9 秒，而 JOIN 查询仅需要 0.003 秒 - 一个相当大的性能改进。

我用来做性能测试的SQL贴在下面。您可以自己运行此测试或稍微修改它并测试其他查询或不同的场景。

警告:不要在生产数据库上运行此脚本，因为它会修改 item2tag 表的内容。运行脚本可能需要几分钟时间，因为它会创建大量数据。

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$
CALL prc_filler(1000000);

CREATE TABLE item2tag (
    item_id INT NOT NULL,
    tag_id INT NOT NULL,
    count INT NOT NULL
);

INSERT INTO item2tag (item_id, tag_id, count)
SELECT  id % 150001, id % 10, 1
FROM    filler;
ALTER TABLE item2tag ADD PRIMARY KEY (item_id, tag_id);
ALTER TABLE item2tag ADD KEY (tag_id);

-- Make tag 3 occur rarely.    
UPDATE item2tag SET tag_id = 10 WHERE tag_id = 3 AND item_id > 0;

SELECT T1.item_id
FROM item2tag T1
JOIN item2tag T2 ON T1.item_id = T2.item_id
JOIN item2tag T3 ON T2.item_id = T3.item_id
WHERE T1.tag_id = 1 AND T2.tag_id = 2 AND T3.tag_id = 3;

SELECT item_id
FROM item2tag
WHERE tag_id=1 or tag_id=2 or tag_id=3
GROUP BY item_id
HAVING count(tag_id)=3;