mysql NOT IN QUERY 优化

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    ...

table_user_ownned_auction

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

我需要选择 atatus = APAST，而不是在表 2 中。
这意味着，在上面的结构中，table1 有 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 秒

执行耗时 115.1039 秒。

解释计划

我如何优化它或以其他方式选择我想要的内容？

Answer 1

尝试在 table_user_ownned_auction 表上添加索引：

ALTER TABLE table_user_ownned_auction ADD KEY(specific_product_id)

另外，尝试使用 non-exists 连接：

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

Answer 2

使用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

说明

最佳查询可以通过两个表之间比较的列是否可为 NULL 来确定（即：如果 <任一表中的 code>specific_product_id 都可以为 NULL）。

• 如果 nullable、NOT IN 或 NOT EXISTS 是 MySQL 中的最佳选择
• 如果 NOT nullable，`LEFT JOIN/IS NULL是MySQL中的最佳选择

附录

一旦确定了最佳查询，请查看创建索引（可能覆盖索引） 至少：

• special_product_id
• TABLE_PRODUCT.status