exits 代替in_mysql IN 可否使用 EXISTS 替代，求提示

一开始的语句是这样的

SELECT

a.*, b. NAME AS store_name

FROM

order a

LEFT JOIN store b ON a.store_id = b.id

WHERE

a.wid IN (

100020109, 100020114, 100020112, 100020133, 100020131, 100020143, 100020145, 100020145, 100020147, 100020149, 100020155, 100020170, ...... ,100020407

)

AND a.channel = 1

AND a.created_at >= '2019-03-01'

AND a.created_at < '2019-03-02'

AND a.is_pay = 1

AND a.type IN (2, 3)

ORDER BY

a.id DESC

解释后是这样的

SELECT

a.*, b. NAME AS store_name

FROM

order a

LEFT JOIN store b ON a.store_id = b.id

WHERE

EXISTS (

SELECT

c.wid

FROM

`tree` c

WHERE

c.deleted_at IS NULL

AND c.first_agent_id = 207

AND a.wid = c.wid

)

AND a.channel = 1

AND a.created_at >= '2019-03-01'

AND a.created_at < '2019-03-02'

AND a.is_pay = 1

AND a.type IN (2, 3)

ORDER BY

a.id DESC

LIMIT 0,

20

并没有一点效果！！！

以下来自网络：

一直以来认为exists比in效率高的说法是不准确的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A(小表)，表B(大表)

1：

select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。

相反的

2：

select * from B where cc in (select cc from A)

效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc)

效率低，用到了A表上cc列的索引。

https://blog.csdn.net/shooke/article/details/52605232