首先要明确的是内连接、外连接(分左外连接和右外连接)的区别:
- select * from t1,t2:这种写法等价于
select * from t1 join t2
select * from t1 inner join t2
select * from t1 cross join t2
查询出来的结果集是笛卡尔积,条数为t1表的条数乘以t2表的条数。
- 内连接:前面的连接中没有进行条件过滤,实际情况中,我们需要匹配符合条件的记录,这个时候可以使用on或者where。在内连接中where和on的作用是一样的,过滤不符合条件的记录,多表严格按照on或where后的条件匹配,不符合条件的记录是不会出现在结果集中的。
- 外连接:分为left outer join和right outer join,多表连接,靠前的表称为驱动表,后面的表称为被驱动表。此处,on和where的作用是不一样的,如果在左外连接中使用了on关键字,那么即使驱动表中的记录不符合条件也会出现在结果集中。而如果使用了where不符合条件的记录是绝对不会出现在结果集中的。
多表连接原理:
- 嵌套循环连接:驱动表只会读一次,根据成本最低的方式确定驱动表单表访问方法,过滤后的结果集循环和被驱动表的每条记录匹配,数据量多的情况下,被驱动表会加载多次。
- 基于块的嵌套循环连接:一般情况下,驱动表过滤后的结果集有多少条记录,被驱动表就需要加载多少次(被驱动表匹配后就会被从内存中清除掉)。但是这种情况会造成频繁的IO,为了降低IO的次数也就是被驱动表的加载次数。mysql引入了一个join buffer,join buffer是一块内存区域,用于存放驱动表的记录,这样让尽可能多的驱动表过滤后的记录和被驱动表匹配。当然理想情况下是把所有驱动表过滤后的记录都放在join buffer中,这样被驱动表只需要访问一次就可以了。
连接优化提示:
- 从上面连接原理的过程中,我们可以看出对单表的访问是基础,因此提高单表的访问速度是一个手段,比如引入索引,当然引入索引不一定会使用到,还要看查询优化器选择哪种方式去执行查询。
- 不要使用*来查询所有字段。因为在join buffer中,存的是驱动表记录,所查询的列越少,存的记录就越多,IO次数就少。
- 增大join buffer的值,join buffer越大,存的记录越多,加载被驱动表次数就越少。
完。