mysql行标记_MySQL标记问题：如何选择已标记为X,Y和Z的项目？

我正在处理一个数据库,在该数据库中项目被“标记”了一定次数.

项目(10万行)

> ID

>名称

>其他东西

标签(1万行)

> ID

>名称

item2tag(1,000,000行)

> item_id

> tag_id

>计数

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

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

到目前为止,这是我想要的…我想确保自己以最佳方式进行操作：

首先从名称中获取tag_id：

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

然后,我将这些tag_ids分组,并使用Haven过滤结果：

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).这将是最快的解决方案吗？

最佳答案

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

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;

【免责声明】本站内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。