MySQL中NOT IN的替代方法

Question

我有一个问题。

SELECT DISTINCT phoneNum 
FROM `Transaction_Register` 
WHERE phoneNum NOT IN (SELECT phoneNum FROM `Subscription`) 
LIMIT 0 , 1000000

由于 Transaction_Register 表中有数百万条记录，执行时间太长了。是否有以上查询的替代方法？如果有，我将不胜感激。

- developerpk

---

你想要更快的东西吗？你有什么更好的替代方案吗？这些表的模式是什么，数据如何分布（统计数据），你有执行计划EXPLAIN吗？你有什么索引？ - Jodrell

请使用MariaDb替换您的MySQL二进制文件。对于这种类型的查询，它比MySQL要好得多。原因是它具有更好的查询规划器。 - Aron

@Aron SQL已经存在一段时间了，有许多引擎可供选择，具有不同的功能和成本。如果你把屎放在铲子上或金盘上，它仍然闻起来很糟糕。 - Jodrell

尝试为您的表添加索引。 - Gimmy

@Jodrell 你评论的是什么？每个DB引擎的每个SQL语句的成本都不同。MySQL因不能有效地使用子查询而闻名。 - Aron

Answer 1

另一种方法是使用LEFT JOIN：

select distinct t.phoneNum
from Transaction_Register t
left join Subscription s
  on t.phoneNum = s.phoneNum
where s.phoneNum is null
LIMIT 0 , 1000000;

请查看带有演示的SQL Fiddle：点击此处。

- Taryn

---

严肃的问题。这个在MySQL上会提供更好的性能吗？ - Jodrell

@Jodrell 经过研究，这确实表现更好 +1。 - Jodrell

Answer 2

我怀疑 LEFT JOIN 是否真的比 NOT IN 更好。我刚刚用以下表结构进行了一些测试（如果我错了，请纠正我）：

account (id, ....)   [42,884 rows, index by id]
play (account_id, playdate, ...)   [61,737 rows, index by account_id]

(1) 使用 LEFT JOIN 进行查询

SELECT * FROM
account LEFT JOIN play ON account.id = play.account_id
WHERE play.account_id IS NULL

(2) 使用 NOT IN 进行查询

SELECT * FROM
account WHERE
account.id NOT IN (SELECT play.account_id FROM play)

使用LIMIT 0，...进行速度测试。

LIMIT 0,->   100      150      200      250
-------------------------------------------------------------------------
LEFT         3.213s   4.477s   5.881s   7.472s
NOT EXIST    2.200s   3.261s   4.320s   5.647s
--------------------------------------------------------------------------
Difference   1.013s   1.216s   1.560s   1.825s

我增加限制时，差异越来越大。

---

使用 EXPLAIN

(1) 使用 LEFT JOIN 的查询

SELECT_TYPE   TABLE      TYPE   ROWS    EXTRA
-------------------------------------------------
SIMPLE         account   ALL    42,884
SIMPLE         play      ALL    61,737  Using where; not exists

(2) 使用 NOT IN 进行查询

SELECT_TYPE          TABLE      TYPE   ROWS   EXTRA
-------------------------------------------------
SIMPLE               account   ALL    42,884  Using where
DEPENDENT SUBQUERY   play      INDEX  61,737  Using where; Using index

似乎左连接没有使用索引。

逻辑：

(1) 使用 LEFT JOIN 查询

在 account 和 play 之间进行 LEFT JOIN，将产生 42,884 * 61,737 = 2,647,529,508 行。然后检查这些行中的 play.account_id 是否为 NULL。

(2) 使用 NOT IN 查询

二分查找需要 log2(N) 步骤来确定项目是否存在。这意味着需要 42,884 * log2(61,737) = 686,144 步骤。

- invisal