优化 MySQL NOT IN 查询

Question

我有两个表，分别命名为：

1. table_product
2. table_user_ownned_auction

table_product

specific_product_id      astatus  ... 
(primary_key,autoinc)
--------------------------------------
1                        APAST    ...
2                        ALIVE    ...
3                        ALIVE    ...
4                        APAST    ... 
5                        APAST    ...

用户拥有的拍卖

own_id     specific_product_id   details   
----------------------------------------
1                  1               XXXX
2                  5               XXXX

我需要选择 atatus = APAST，并且不在表2中。
也就是说，在上述结构中，表1有3个APAST状态（1、4、5）。但是在表2中，只存储了specific_product_id（1、5），因此我需要选择specific_product_id = 4。

我使用了这个查询

  SELECT * 
    FROM table_product 
   WHERE astatus = 'APAST' 
     AND specific_product_id NOT IN (SELECT specific_product_id 
                                       FROM table_user_ownned_auction )

...需要这么长时间：

查询耗时115.1039秒

...才能执行。

解释计划

我该如何优化它或选择其他方法来获取我想要的内容？

- Gowri

Answer 1

使用NOT EXISTS

SELECT p.* 
  FROM TABLE_PRODUCT p
 WHERE p.astatus = 'APAST' 
   AND NOT EXISTS (SELECT NULL
                     FROM TABLE_USER_OWNED_AUCTION uoa
                    WHERE uoa.specific_product_id = p.specific_product_id)

使用LEFT JOIN/IS NULL

   SELECT p.* 
     FROM TABLE_PRODUCT p
LEFT JOIN TABLE_USER_OWNED_AUCTION uoa ON uoa.specific_product_id = p.specific_product_id
    WHERE p.astatus = 'APAST' 
      AND uoa.own_id IS NULL

解释

如果两个表之间比较的列可以为空（例如，specific_product_id 的值在任一表中都可以为 NULL），则最优查询可以确定。

• 如果是可空的，在MySQL中，NOT IN 或 NOT EXISTS 是最好的选择
• 如果不可空，则使用 `LEFT JOIN / IS NULL` 是 MySQL 中的最佳选择

附加说明

一旦确定了最优查询，至少需要查看创建索引（可能是覆盖索引）：

• specific_product_id
• TABLE_PRODUCT.astatus

- OMG Ponies

---

我喜欢你的解决方案。我正在学习mysql。当uoa.specific_product_id != p.specific_product_id时，您能否请解释一下在NOT EXISTS(..)中子查询将选择什么？ - Sandeep

@Sandeep：NOT执行反转，将uoa.specific_product_id = p.specific_product_id更改为uoa.specific_product_id != p.specific_product_id。 - OMG Ponies

附录一中有一个错别字 -> 应该是Once。 - broadband

我阅读了你提供的文章，似乎在LEFT JOIN/IS NULL和NOT IN之间没有性能差异。因此，可以得出结论，您可以始终使用NOT IN并且总是安全的。引用文章中比较两者的话：“实际上，算法是相同的，查询完成时间也相同。” - Adam

Answer 2

尝试在 table_user_ownned_auction 表上添加索引:

ALTER TABLE table_user_ownned_auction ADD KEY(specific_product_id)

此外，尝试使用一个不存在的连接：

SELECT p.*
FROM table_product p
    LEFT JOIN table_user_ownned_auction l
      ON p.specific_product_id = l.specific_product_id
WHERE p.astatus = 'APAST' 
    AND l.specific_product_id IS NULL

- The Scrum Meister

---

@gowri：这也可能是因为查询计划已被缓存。 - OMG Ponies

请使用确切的字段名称而不是 SELECT *。 - Sreekumar P