mysql - 尽管使用 EXPLAIN 所示的索引，但跨数据库查询的运行时间异常长

Question

Table_A
id int(11)
sku varchar(24)
Total number of records 250000, sku indexed

Table_B
id int(11)
sku varchar(16)

Total number of records - 180000, sku indexed

以下查询已运行超过 30 分钟

Select count(*) 
from Table_A 
where not exists(select 1 from Table_B where Table_B.sku = Table_A.sku)

同时Select count(*) from Table_B where exists(select 1 from Table_A where Table_A.sku = Table_B.sku) 在不到一秒内执行。

有人可以解释一下会发生什么吗？

忘了说 Table_A 和 Table_B 在不同的数据库中

mysql> describe db1.table_a;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| prodcut_id | int(11)     | NO   | PRI | NULL    |       || sku        | varchar(24) | YES  | MUL | NULL    |       |+------------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> describe db2.table_b;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| sno   | int(11)     | NO   | PRI | NULL    |       || sku   | varchar(24) | YES  | MUL | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> explain select count(*) from db2.table_b where not exists(select 1 from db1.table_a where table_a.sku = table_b.sku);+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+| id | select_type        | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+|  1 | PRIMARY            | table_b | index | NULL          | sku  | 27      | NULL | 181286 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | table_a | ref   | sku           | sku  | 75      | func |      1 | Using where; Using index |+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+2 rows in set (0.00 sec)mysql> explain select count(*) from db1.table_a where not exists(select 1 from db2.table_b where table_b.sku = table_a.sku);+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+| id | select_type        | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+|  1 | PRIMARY            | table_a | index | NULL          | sku  | 75      | NULL | 277030 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | table_b | index | NULL          | sku  | 27      | NULL | 181286 | Using where; Using index |+----+--------------------+---------+-------+---------------+------+---------+------+--------+--------------------------+2 rows in set (0.00 sec)

Answer 1

在 MySQL 中 LEFT JOIN 应该比 NOT EXISTS 有更好的性能:

SELECT COUNT(a.*) 
FROM Table_A a LEFT JOIN Table_B b ON a.sku = b.sku
WHERE b.sku IS NULL