我有一个问题
SELECT DISTINCT phoneNum
FROM `Transaction_Register`
WHERE phoneNum NOT IN (SELECT phoneNum FROM `Subscription`)
LIMIT 0 , 1000000执行b/c耗费了太多的时间,Transaction_Register表有数百万条记录,上面的查询有没有替代方案,如果有的话,我将不胜感激。
发布于 2013-06-04 00:51:53
另一种方法是使用左连接:
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;发布于 2013-06-04 02:00:38
我怀疑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)速度测试限制为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左连接后的
将产生42,884 * 61,737 = 2,647,529,508行。然后检查这些行的play.account_id是否为NULL。
(2)使用NOT IN进行查询
二进制搜索以log2(N)表示项的存在。这意味着42,884 * log2(61,737) = 686,144步
https://stackoverflow.com/questions/16901755
复制相似问题