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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!