mysql - 在多对多相关表中进行高效搜索

Question

我有两个表通过第三个连接表进行多对多关联:产品和类别。每个产品都可以分为几个类别。这是一个典型的多对多关系:

products
-------------
id
product_name


categories
-------------
id
category_name


products_to_categories
-------------
product_id
caregory_id

我想让用户搜索产品，这些产品属于某些选定的类别，但同时不属于其他选定的类别。

示例:查找属于“计算机”和“软件”类别但不属于“游戏”、“编程”和“教育”类别的所有产品。

这是我为此设计的查询:

SELECT product_name
FROM products
WHERE
    EXISTS (SELECT product_id FROM products_to_categories WHERE category_id = 1 AND product_id = products.id) 
    AND EXISTS (SELECT product_id FROM products_to_categories WHERE category_id = 2 AND product_id = products.id) 
    AND NOT EXISTS (SELECT product_id FROM products_to_categories WHERE category_id = 3 AND product_id = products.id)
    AND NOT EXISTS (SELECT product_id FROM products_to_categories WHERE category_id = 4 AND product_id = products.id) 
    AND NOT EXISTS (SELECT product_id FROM products_to_categories WHERE category_id = 5 AND product_id = products.id)
ORDER BY id

它有效。但它太慢了，我不能在生产中使用它。所有索引都已到位，但此查询导致 5 个相关子查询并且表很大。

有没有办法在没有依赖子查询的情况下解决相同的任务或以其他方式优化此查询？

更新

索引是:

products: PRIMARY KEY (id)
categories: PRIMARY KEY (id)
products_to_categories: PRIMARY KEY (product_id, caregory_id)

所有表都是InnoDB

Answer 1

请发布表的定义(以便显示使用的引擎和定义的索引)。

您还可以发布查询的执行计划(使用 EXPLAIN 语句)。

您还可以尝试以各种方式重写查询。这是一个:

SELECT p.product_name
FROM products  AS p
  JOIN products_to_categories  AS pc1
    ON pc1.category_id = 1 
    AND pc1.product_id = p.id
  JOIN products_to_categories  AS pc2
    ON  pc2.category_id = 2 
    AND pc2.product_id = p.id
WHERE
    NOT EXISTS 
    ( SELECT * 
      FROM products_to_categories  AS pc 
      WHERE pc.category_id IN (3, 4, 5)
        AND pc.product_id = p.id
    )

---

更新:您没有 (category_id, product_id) 索引。尝试添加它。