MySQL:删除带有子查询和"not in“的行很慢

Question

我有两张桌子：

产品(id {主键}、dcs_no {索引}和其他)- 1000行

product_price_import (dcs_no {主键}等)- 100 000行

我试着这样做：

DELETE 
FROM product_price_import 
WHERE dcs_no NOT IN (SELECT dcs_no FROM products WHERE dcs_no <> '')

但是，正如预期的那样，我遇到了一个解决方案，如果它是"IN“而不是”IN“，那么这个解决方案就会更快。

DELETE product_price_import 
FROM products 
JOIN product_price_import ON product_price_import.dcs_no = products.dcs_no;

有人能告诉我如何优化查询以从product_price_import表中删除在products表中没有dcs_no值的行吗？

提前谢谢。

Answer 1

您可以使用LEFT JOIN而不是JOIN，并在products表中检查NULL id值：

DELETE ppi
FROM product_price_import ppi
LEFT JOIN products p ON p.dcs_no = ppi.dcs_no
WHERE p.dcs_no IS NULL

Answer 2

我希望这能给我表演

Delete ppi.* from product_price_import ppi where not exists (select * from products p where p.dcs_no = ppi.dcs_no)


For EX
mysql> select * from empty;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.01 sec)

mysql> select * from empty1;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec)


mysql> delete e1.* from empty1 e1 where not exists (select * from empty e where e.id =e1.id);
Query OK, 2 rows affected (0.13 sec)

mysql> select * from empty1;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

同时在两个表中索引dcs_no