mysql - 优化 MySQL NOT IN Query

Question

我有一个基于以下 sql 查询生成的下拉列表:

 
SELECT * FROM product WHERE 
    product.id NOT IN (SELECT customer_1.product_id FROM customer_1 WHERE (customer_1.product_id != '$x'))
AND product.id NOT IN (SELECT customer_2.product_id FROM customer_2 WHERE (customer_2.product_id != '$x'))
AND product.id NOT IN (SELECT customer_3.product_id FROM customer_3 WHERE (customer_3.product_id != '$x'));
 

这里出现的问题是执行时间。这个查询本身大约需要 5.3 秒。我在同一页面上还有其他几个类似的查询。

我的问题是:是否有更好更快的方法来达到相同的结果？

提前谢谢你。

Answer 1

您可能会从 LEFT JOIN 获得更好的性能，在连接的右侧查找 NULL(customer_* 表)。如果我理解您的目标，这应该可以完成工作:

SELECT
  products.*
FROM
  products
  LEFT JOIN customer_1 ON products.id = customer_1.product_id
  LEFT JOIN customer_2 ON products.id = customer_2.product_id
  LEFT JOIN customer_3 ON products.id = customer_3.product_id
WHERE
  products.id != '$x'
  AND customer_1.product_id IS NULL
  AND customer_2.product_id IS NULL
  AND customer_3.product_id IS NULL