Mysql优化原则_小表驱动大表IN和EXISTS的合理利用详解数据库

//假设一个for循环 
for($i = 0; $i < 10000; $i++)  
{ 
     for ($j = 0; $i < 50; $j++) 
     { 
 
     } 
} 
 
for($i = 0; $i < 50; $i++)  
{ 
    for ($j = 0; $i < 10000; $j++) 
    { 
 
    } 
}

看以上两个for循环，总共循环的次数是一样的。但是对于mysql数据库而言，并不是这样了，我们尽量选择第②个for循环，也就是小表驱动大表。
数据库最伤神的就是跟程序链接释放，第一个建立了10000次链接，第二个建立了50次。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。
这时候就诞生了in 和exists的对比。

小表驱动大表：即小的数据集驱动大的数据集。

这里假设A表代表员工表，B表代表部门表。
假设部门只有三个，销售、技术部、行政部，言下之意是在这三个部门里的所有员工都查出。

select * from A where id in (select id from B);

这样写就等价于：
for select id from B。比如华为有100个部门，但是华为的员工少说有15W-20W，员工总比部门多，这时候就相当于得到了小表(部门表)；for select * from A where A.id = B.id，相当于A.id等B表里面的，相当于从部门表获得对应的id。

当B表的数据集必须小于A表的数据集时，用in优于exists。
反之

select * from A where exists (select 1 from B where B.id = A.id); //这里的select 1并不绝对，可以写为select 'X'或者'A','B','C'都可以，只要是常量就可以。

这样写就等价于：
for select * from A,先从A表做循环
for select * from B where B.id = A.id,再从B表做循环。
这样exists就会变成看看A表是否存在于(select 1 from B where B.id = A.id)里面，这个查询返回的是TRUE或者FALSE的BOOL值，简单来说就是要当A表的数据集小于B表的数据集时，用exists优于in。要注意的是：A表与B表的ID字段应该建立索引。

语法：EXISTS
SELECT …FROM table WHERE EXISTS(subquery)。
理解：将主查询的数据放到子查询中做条件验证，根据验证结果（TRUE或者FALSE）来决定朱查询的数据结果是否得意保留。
相当于从表A和B中取出交集，然后再从A表中取出所在交集的部分数据，当然后面加WHERE条件还可以进一步筛选。
补充：
1：EXISTS(subquery)只返回TRUE或者FALSE，因此子查询中的SELECT * 也可以是SELECT 1或者SELECT ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
2：EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际校验。
3：EXISTS子查询旺旺可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

如果查询的两个表大小相当，那么用in和exists差别不大。

延伸举例巩固：

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）

select * from A where cc in (select cc from B) ;//  效率低，用到了A表上cc列的索引； 
select * from A where exists(select cc from B where cc=A.cc) ;// 效率高，用到了B表上cc列的索引。 

相反的

select * from B where cc in (select cc from A) ; //效率高，用到了B表上cc列的索引； 
select * from B where exists(select cc from A where cc=B.cc) ;//效率低，用到了A表上cc列的索引。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。