MySQL中IN和EXISTS效率对比实验
逍遥斩舞 2018-08-06
验证：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。
以上黑色部分代码仅仅只是帮你理解
少侠请赐教
