MySql 优化 in 和 exists
新人一枚,求大神分析下exists 和in。本人自己测试,建了两张表:employee 1000万条数据; employee_department 14条数据。
-- 外表大的情况
explain select t1.* from employee t1
where t1.id in (select employee_id from employee_department);
查询(1)耗时:0.002s
explain select t1.* from employee t1 where exists (
select 1 from employee_department where employee_id = t1.id
);
查询(2)耗时:23s
这个查看执行计划明白。但是内表大的情况,就纠结了:
-- 内表大的情况
select t1.* from employee_department t1 where t1.employee_id in (
select id from employee
)
查询(3):0.001s
select t1.* from employee_department t1 where exists (
select 1 from employee where id = t1.employee_id
)
查询(4):0.001s
和网上主流说法,内表大的情况,使用exists表示看不明白。环境MySql 5.7