我有以下表格:users、tags、tags_data。
tags_data 包含 tag_id 和 user_id 列以将 users 与 1 中的 tags 链接起来用户与许多标签的关系。

列出所有具有 tag_id 1001 AND 1003，OR tag_id 的用户的最佳方式是什么1004？
编辑:我的意思是，也可能有其他相关标签，也可能没有，只要肯定有 1004 或(1001 和 1003)即可。

目前我有两种方法可以做到这一点，都在派生表中使用 UNION，在 FROM 子句或 中INNER JOIN 子句...

SELECT subsel.user_id, users.name 
FROM   ( SELECT user_id 
         FROM   tags_data
         WHERE  tag_id IN (1001, 1003) 
         GROUP  BY user_id 
         HAVING COUNT(tag_id)=2
        UNION 
         SELECT user_id 
         FROM   tags_data 
         WHERE  tag_id=1004
       ) AS subsel 
LEFT JOIN users ON subsel.user_id=users.user_id

或者

SELECT users.user_id, users.name
FROM   users
INNER JOIN ( SELECT user_id
             FROM   tags_data
             WHERE  tag_id  IN (1001, 1003) 
             GROUP  BY user_id
             HAVING COUNT(tag_id)=2
            UNION 
             SELECT user_id
             FROM   tags_data
             WHERE  tag_id=1004
           ) AS subsel ON users.user_id=subsel.user_id

我将LEFT JOIN加入其他表。 users 表中的 50k+ 行和 tags_data 表中的 150k+ 行。

这是一个将数据导出到另一个系统的批处理作业，而不是最终用户运行的实时查询，因此性能并不是非常关键。但是，我想尝试获得最好的结果。对派生表的查询实际上应该非常快，并且在我向返回给客户端的结果添加更多连接、函数和计算字段之前缩小结果集的范围是有意义的。稍后我将在更大的数据集上运行它们以查看是否存在任何性能差异，但运行 EXPLAIN 显示几乎相同的执行计划。

一般来说，除非绝对必要，否则我尽量避免使用 UNION。但我认为在这种情况下，我几乎必须根据定义在某处设置一个 UNION，因为这两个实际上不相关的标准。

我可以在这里使用另一种方法吗？
是否有针对此类问题的某种特定数据库术语？

完整示例模式:

CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) NOT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1006 ;

INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES
(1001, 'tag1001'),
(1002, 'tag1002'),
(1003, 'tag1003'),
(1004, 'tag1004'),
(1005, 'tag1005');

CREATE TABLE IF NOT EXISTS `tags_data` (
  `tags_data_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`tags_data_id`),
  KEY `user_id` (`user_id`,`tag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `tags_data` (`tags_data_id`, `user_id`, `tag_id`) VALUES
(1, 1, 1001),
(2, 1, 1002),
(3, 1, 1003),
(4, 5, 1001),
(5, 5, 1003),
(6, 5, 1005),
(7, 8, 1004),
(8, 9, 1001),
(9, 9, 1002),
(10, 9, 1004);

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `users` (`user_id`, `name`) VALUES
(1, 'user1'),
(2, 'user2'),
(3, 'user3'),
(4, 'user4'),
(5, 'user5'),
(6, 'user6'),
(7, 'user7'),
(8, 'user8'),
(9, 'user9'),
(10, 'user10');

最佳答案

如果您正在寻找 MySQL 的性能，您绝对应该避免使用嵌套查询和联合——它们中的大多数会导致创建临时表并在没有索引的情况下进行扫描。很少有派生临时表仍然使用索引并且仅适用于某些特定情况和 MySQL 发行版的示例。

我的建议是仅将查询重写为内部/外部连接，如下所示:

select distinct u.* from users as u 
  left outer join tags_data as t on 
    t.user_id=u.user_id and t.tag_id=1003 
  inner join tags_data as t2 on 
    t2.user_id=u.user_id 
    and (t2.tag_id=1004 or (t2.tag_id=1001 and t.tag_id=1003));

如果您可以确定没有用户可以同时拥有 1004 和(1001 和 1003)标签，您也可以从此查询中删除“distinct”，这将避免创建临时表。

你也绝对应该使用索引，像这样:

create index tags_data__user_id__idx on tags_data(user_id);
create index tags_data__tag_id__idx on tags_data(tag_id);

这将使 150k+ 的结果集非常容易查询。

关于派生表中的 MySQL 联合 (related_id=a AND related_id=b) OR (related_id=z)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22158976/