mysql-表连接join

连接表的本质就是把个个表的数据都取出来 依次匹配组合 并返回给用户

以一条连接查询语句为例:

t1表

m1	n1
1	a
2	b
3	c

 

 

 

 

 

t2表

m2	n2
2	b
3	c
4	d

 

 

 

 

 

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
1. 确认一个驱动表 从驱动表中查出符合条件的记录 以t1为驱动表 查出来的记录时 m1>1的记录 也就是m1值为2 和3 的记录

 

2. 从第一步取得的结果集 到t2表中匹配记录 因为第一步查出两条记录 所以会进行两次匹配t2

t1.m1 = t2.m2 AND t2.n2 < 'd'

转化为 

 t2.m2 = 2 AND t2.n2 < 'd'

 t2.m2 = 3 AND t2.n2 < 'd'

得出一个结论是 两表联查的时候 会查询一次驱动表 查询多次被驱动表 所以尽量驱动表要小

 

内连接/外连接

内连接:驱动表中的记录在被驱动表中匹配不到记录 就不会放到结果集中

外连接:驱动表中的记录在被驱动表中匹配不到记录 会放到结果集中

　　left join  左外连接 以左边的表为驱动表

　　right join 右外连接 以右边的表为驱动表

 

where

不管是内连接和外连接 不符合where条件的都不会放入结果集

on  在外连接中驱动表中的记录在被驱动表中匹配不到记录 会放到结果集中以null填充 

 

在内连接中 on和where等价

 

以驱动表的结果记录去被驱动表 依次匹配的过程叫做嵌套循环连接(nested-loop join)

 基于块的嵌套循环连接(block nested-loop join) 为了减少被驱动表数据的加载次数,创建了一个固定大小的内存区域,叫join buffer,里面存储着部分 驱动表的记录,依次与被驱动表匹配,减少从磁盘加载被驱动表记录的次数

默认join buffer size为256kb ,注意join buffer中只存储着 查询列表中的列和过滤条件中的列 所以为了能够存储更多的驱动表数据 不要用*来查询