一.分析
mysql的join实现原理是,以驱动表的数据为基础,“嵌套循环”去被驱动表匹配记录,
这里的小表驱动大表速度快的前提是:两个表上根据主/外键建立了索引,这样在根据某一条数据查找B+树时,速度就会大大提高,若没有建立索引,则两个表无论谁当作主表,查找数据的次数都是一样的
select * from a join b on a.bid =b.id
假设 a表10000数据,b表20数据
这里有2个过程,b 表数据最少,查询引擎优化选择b为驱动表,
- 循环b表的20条数据,
- 去a表的10000数据去匹配,这个匹配的过程是B+树的查找过程,比循环取数要快的多。
- 小表驱动的方式
for 20条数据
匹配10000数据(根据on a.bid=b.id的连接条件,进行B+树查找)
查找次数 20+ log10000
2.如果使用大表驱动,则查找过程是这样的
for 10000条数据
匹配20条数据(根据on a.bid=b.id的连接条件,进行B+树查找)
查找次数 10000+ log20
二.结论
可以看出来
小表驱动大表:20+ log10000
大表驱动小表:10000+ log20
显然小表驱动大表查询效率要高很多。
C.小表驱动大表的案例:
三.in和exists的对比:
下面关于大表和小表的讨论均为A是小表
a.小表放到in之后的效率优于放到in之前
例:select name from B where id in(select id in A);
分析:
1>IN的语法理解:MySql 的执行顺序会先执行子查询,再执行主查询,然后获得我们要查询的数据。
2>上面的查询语句可以拆成 首先 select id in A; 其次 select name from B where B.id=A.id;
b.exists
例:select name from A where exists (Select * from B where B.id=A.id);
分析:
1>将EXISTS 语法理解为:将主查询的数据放在子查询中做条件验证,根据结果TRUE 和 FALSE 来决定主查询中的数据是否需要保留,EXISTS 子查询只返回TRUE 或 FALSE .Exists就是将主查询出的每一条语句放到exists后的子查询中存在即保留。exists/no exists具体用法可看:https://blog.csdn.net/qq_39552268/article/details/111934416
2>先遍历小表A,取得A中每个值和B中的值进行比对,如果比对成功了返回true
总之,什么时候用In,什么时候用exists呢?
1>根据两个表的数据量判断:
我们使用IN 优于 EXISTS。当A表中数据多于 B表中的数据时(我们这里只是假设),这时我们使用EXISTS 优于 IN。因此是使用IN 还是使用EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用IN 还是使用 EXISTS 差别不大。
2>根据限制条件来判断:
虽然exists和in都有过滤功能,他俩最大的差别就是in引导的子句只能对一个字段进行限制,比如
但是如果我们想对多个字段进行限制,使用in就不合适了。具体可参考:https://blog.csdn.net/qq_39552268/article/details/111934416