使用INNER JOIN或EXISTS在m2m关系中查找属于几个的更好吗？

Question

给定m2m关系： 项目类别 我有三个表：

• 项 ，
• 类别 和
• items_categories 包含对两者的引用

我想找到属于 所有给定 类别集的项目：

Find Item 
belonging to a category in [1,3,6] 
and belonging to a category in [7,8,4] 
and belonging to a category in [12,66,42]
and ...

我可以考虑通过两种方法在mySQL中完成此操作。

选项A：内联：

SELECT id from items 
INNER JOIN category c1 ON (item.id = c1.item_id)
INNER JOIN category c2 ON (item.id = c2.item_id)
INNER JOIN category c3 ON (item.id = c3.item_id)
...
WHERE
c1.category_id IN [1,3,6] AND
c2.category_id IN [7,8,4] AND
c3.category_id IN [12,66,42] AND
...;

选项B：存在：

SELECT id from items
WHERE
EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [1,3,6] AND
EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [7,8,4] AND
EXISTS(SELECT category_id FROM category WHERE category.item_id = id AND category_id in [12,66,42] AND
...;

这两个选项都可以。问题是： 对于大型项目表，最快/最合适的 是 哪一个？ 还是缺少OPTION CI？

Answer 1

选项A

JOIN与相比具有一个优势EXIST，因为它将更有效地使用索引，尤其是在大型表的情况下