MySQL中IN和EXISTS效率对比

验证：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

 

环境：mysql5.7以上版本

两个表分别是user（数据量小）和order（数据量大）

user：400条数据

order：409356条数据

 

数据库下载链接链接：https://pan.baidu.com/s/12Pk9cr_wNjj13TjoDdCJ7Q 密码：r93e

 

实验一：user表数据小于order表数据

SQL语句	SELECT SQL_NO_CACHE * FROM `user` WHERE id IN (SELECT user_id FROM `order`)	SELECT SQL_NO_CACHE * FROM `user` WHERE EXISTS ( SELECT * FROM `order` WHERE `user`.id = `order`.user_id )
第一次执行时长	0.173s	0.019s
第二次执行时长	0.236s	0.021s
第三次执行时长	0.178s	0.021s
第四次执行时长	0.172s	0.019s
第五次执行时长	0.209s	0.020s

实验结果：当子查询大于外层查询时，EXISTS比IN效率高

 

实验二：order表数据大于user表数据

SQL语句	SELECT SQL_NO_CACHE * FROM `order` WHERE user_id IN (SELECT id FROM `user`)	SELECT SQL_NO_CACHE * FROM `order` WHERE EXISTS ( SELECT * FROM `user` WHERE `user`.id = `order`.user_id )
第一次执行时长	0.527s	1.679s
第二次执行时长	0.556s	1.650s
第三次执行时长	0.558s	1.743s
第四次执行时长	0.590s	1.810s
第五次执行时长	0.548s	1.739s

实验结果：当子查询小于外层查询时，EXISTS比IN效率低

 

实验三：order表数据与user表数据差不多时（这边就不介绍了，爱用哪种用哪种）

 

注意：为了结果的准确性，在每次执行完，执行RESET QUERY CACHE;清楚缓存，或者直接在sql语句的SELECT 后面加上SQL_NO_CACHE

 

结论：外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。

 

IN()分析：

SELECT SQL_NO_CACHE

*

FROM

`user`

WHERE

id IN (SELECT user_id FROM `order`)

IN()中的sql只会查询一次，结果集会存储在临时文件中，查询结束后再查询外层的sql最好再匹配。

IN()查询过程类似于以下过程

$result = [];
$users = "SELECT * FROM `user`";
$orders = "SELECT user_id FROM `order`";
for($i = 0;$i < $users.length;$i++){
    for($j = 0;$j < $orders.length;$j++){
        if($users[$i].id == $orders[$j].user_id){
            $result[] = $users[$i];
            break;
        }
    }
}

 

如：user表有10000条记录,order表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差

 

EXISTS()分析:

SELECT SQL_NO_CACHE

*

FROM

`user`

WHERE

EXISTS (

SELECT

*

FROM

`order`

WHERE

`user`.id = `order`.user_id

)

在这里，exists语句会执行user.length次，它并不会去缓存exists的结果集，因为这个结果集并不重要，exists只返回true或false，false时不不查询。

EXISTS()查询过程类似于以下过程

$result = [];
$users = "SELECT * FROM `user`";
for ($i = 0; $i < $users . length; $i++) {
    if (exists($users[$i] . id )) {// 执行SELECT * FROM `order` WHERE user.id = order.user_id
        $result[] = $users[$i];
    }
}

遍历少了，over。

 

以上黑色部分代码仅仅只是帮你理解