MySQL索引优化（索引三表优化案例）

【子查询优化】

用in 还是 exists

1、实验

有索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

有索引小驱动大表 性能优于 大表驱动小表

无索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

无索引大表驱动小表
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);
select sql_no_cache sum(sal) from emp inner  join dept on  emp.deptno=dept.deptno;

 

MySQL索引优化（索引三表优化案例）

标签：mamicode   次数   src   distinct   ima   mysql索引   rom   如何   全表扫描