记一次SQL优化（in vs exists）

优化记录

以MySQL官方库的表结构为例，

当时的业务场景抽象出来后类似：
1、dept_manager表中记录较少，employees表中记录较多。
2、需要查询dept_manager表中失效雇员的记录，即employees表中没有对应dept_manager表中emp_no的记录。
一开始的写法是：

select * from dept_manager a where a.emp_no in (select emp_no from employees);   

效果很不理想。后续想办法优化了之后：

select * from dept_manager a where exists  (select 1 from employees b where b.emp_no = a.emp_no);

效果有明显提升。

后经过在测试库上测试：

select * from employees a where a.emp_no in (select emp_no from dept_manager);   
select * from employees a where exists  (select 1 from dept_manager b where b.emp_no = a.emp_no);

2个sql效果相同，但第1句的写法优于第2句。

select * from dept_manager a where a.emp_no in (select emp_no from employees);   
select * from dept_manager a where exists  (select 1 from employees b where b.emp_no = a.emp_no);

2个sql效果相同，但第2句的写法优于第1句。

网上查了下，说是如果大表在前，in好于exists。小表在前，exists好于in。
假设A表有m条记录，B表有n条记录
in的原理是：

select * from A where id in (select id from B)
等价于
for select id from B
    for select * from A where A.id = B.id
 即
 for(int i=0;i<n;i++){
    for(int j=0;j<m;j++){
    }
 }

exists的原理是：

select * from A where exists (select 1 from B where B.id = A.id)
括号里的可以是select 1也可以是select null，总之返回的是布尔值。
等价于
for select * from A 
    for select * from B where B.id = A.id
即
for(int i=0;i<m;i++){
    for(int j=0;j<n;j++){
    }
 }

数据库里连接操作比较耗时，所以外层循环是连接，里层循环是操作。连接的循环次数越少越好。