mysql exists,in, not exists ,not in效率疑问

[meiyaxiong]

我在网上看到的都是一个观点
因为in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。在查询的两个表大小相当的情况下，EXISTS <= IN <= JOIN,NOT EXISTS <= NOT IN <= LEFT JOIN，如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in
但是我自己测试的时候却发现不是这样

已知
invoice_list表和invoice_package表数据均为为136万条，后表比前表多几千

select count(*) from invoice_package p where l.invoice=p.invoice and date< '2017-01-25' and date > '2017-01-18';
执行结果为1532条

SELECT l.invoice,l.uid,l.email,b.BFName FROM invoice_list l
LEFT JOIN invoice_package p ON p.invoice = l.invoice
LEFT JOIN invoice_bill_ship b ON b.invoice = l.invoice
WHERE p.date < '2017-01-25'
AND p.date > '2017-01-18'
AND l.channel IN ('','FR','CJ','WAP')
AND l.status NOT IN ('Cancelled','CustomerCancel','Emailed Product','');
执行结果1068 rows in set (2.12 sec)

SELECT l.invoice,l.uid,l.email,b.BFName FROM invoice_list l
LEFT JOIN invoice_bill_ship b ON b.invoice = l.invoice
WHERE exists (select invoice from invoice_package p where l.invoice=p.invoice and date< '2017-01-25' and date > '2017-01-18' )
AND l.channel IN ('','FR','CJ','WAP')
AND l.status NOT IN ('Cancelled','CustomerCancel','Emailed Product','');
执行结果1027 rows in set (30.28 sec)


SELECT l.invoice,l.uid,l.email,b.BFName FROM invoice_list l
LEFT JOIN invoice_bill_ship b ON b.invoice = l.invoice
WHERE l.invoice in (select invoice from invoice_package where date< '2017-01-25' and date > '2017-01-18' )
AND l.channel IN ('','FR','CJ','WAP')
AND l.status NOT IN ('Cancelled','CustomerCancel','Emailed Product','');
执行结果1027 rows in set (2.84 sec)

上面是left join，exists，in的比较


SELECT l.invoice,l.uid,l.email,b.BFName FROM invoice_list l
LEFT JOIN invoice_package p ON p.invoice = l.invoice
LEFT JOIN invoice_bill_ship b ON b.invoice = l.invoice
WHERE p.date < '2017-01-25'
AND p.date > '2017-01-18'
AND l.invoice NOT IN (SELECT invoice FROM blacklist)
AND l.channel IN ('','FR','CJ','WAP')
AND l.status NOT IN ('Cancelled','CustomerCancel','Emailed Product','');
执行结果1068 rows in set (1.85 sec)

SELECT l.invoice,l.uid,l.email,b.BFName FROM invoice_list l
LEFT JOIN invoice_package p ON p.invoice = l.invoice
LEFT JOIN invoice_bill_ship b ON b.invoice = l.invoice
WHERE p.date < '2017-01-25'
AND p.date > '2017-01-18'
AND not exists (SELECT invoice FROM blacklist b where l.invoice = b.invoice)
AND l.channel IN ('','FR','CJ','WAP')
AND l.status NOT IN ('Cancelled','CustomerCancel','Emailed Product','');
执行结果1068 rows in set (27.37 sec）

这个的left join不知道怎么写没测试
请问这是什么原因导致的

[meiyaxiong001]

没人回答，关闭了