MySQL标记问题：如何选择已标记为X、Y和Z的项目？

Question

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

item (100k rows)

• id
• name
• other stuff

tag (10k rows)

• id
• name

item2tag (1,000,000 rows)

• item_id
• tag_id
• count

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

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

这是我到目前为止所拥有的...我只是想确保我以尽可能最好的方式做到这一点：

首先从名称中获取 tag_ids：

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

然后我按这些 tag_ids 进行分组并使用 Have 来过滤结果：

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;

Answer 2

最好有一个以 tag_id 作为第一列的索引 - 否则查找所有 tag_id 为 1 的项目将需要全表扫描（当然，对于任何 tag_id 都相同）。

Answer 3

根据使用单个标签标记的项目数量，您可以通过获取使用一个标签标记的项目列表，然后过滤它以查找其他标签的出现来完成此操作，如下所示：

select item_id from item2tag
where item_id in (
    select item_id from item2tag
    where item_id in (
        select item_id from item2tag where tag_id = TID1
    ) and tag_id = TID2
) and tag_id = TID3