JOIN 语句原理
join方式连接多个表,本质就是各个表之间数据的循环匹配。MySQL5.5版本之前,MySQL只支持一种表间关联方式,就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大,则 join 关联的执行时间会非常长。在 MySQL5.5以后的版本中,MySQL 通过引入BNLJ算法来优化嵌套执行。
驱动表(主表)和被驱动表(从表)
驱动表就是主表,被驱动表就是从表、非驱动表。
- 对于内连接来说:
# 内连接的两种写法
select * from A join B on A.f1=B.f1;
select * from A inner join B on A.f1=B.f1;
A一定是驱动表吗?不一定,优化器会根据你查询语句做优化,决定先查哪张表。先查询的那张表是驱动表,反之就是被驱动表。通过 explain 关键字可以查看。 (使用 explain 分析语句查询后,表出现在上面的是主表,下面的是从表)
- 对于外连接来说:
select * from A left join B on A.f1=B.f1;
# 或
select * from B right join A on A.f1=B.f1;
通常大家会认为 A 就是驱动表,B 就是被驱动表。但也未必。测试如下:
create table a(f1 int,f2 int ,INDEX(f1));
create table b(f1 int,f2 int);
insert into a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
# 测试1 查询结果:b 为主表,a 为从表
explain select * from a left join b on a.f1=b.f1 where a.f2=b.f2;
# 测试2 查询结果:a 为主表,b 为从表
explain select * from a left join b on a.f1=b.f1 and a.f2=b.f2;
# 测试3 查询结果:b 为主表,a 为从表
explain select * from a inner join b on a.f1=b.f1 where a.f2=b.f2;
explain select * from a join b on a.f1=b.f1 where a.f2=b.f2;
总结
使用上面的外连接语句测试时,大家通常会认为 A 为主表,B为从表。结果并非如此。实际情况是,查询优化器帮我们把外连接改造成了内连接。那么谁作为主表、从表,查询优化器说的算。那么对于外连接来说,前面那张表不一定是主表(驱动表)。
JOIN语句原理-——Simple Nested-Loop Join (简单嵌套循环)
算法相当简单,从表 A 中取出一条数据 1,遍历表 B,将匹配到的数据放到result。以次类推,驱动表 A 中的每一条记录与被驱动表 B 的记录进行判断。
当使用JOIN语句查询时,从主表表 A 中取出一条数据,然后把从表表 B 的所有数据加载到内存中。表 A 中的这条数据与内存中表 B 的所有数据匹配一遍,匹配完后清空内存中表B的数据。以次类推,取出表A中第二条数据,把表B的所有数据加载到内存中,表A的第二条数据与内存中表B的所有数据匹配一遍,匹配完了清空内存中表 B 的数据。以此类推.....
JOIN语句原理-——Block Nested-Loop Join(块嵌套循环连接)
SNLJ 简单嵌套循环,大大增加了IO的次数,为了减少从表(被驱动表)的IO次数,就引入了Block Nested-Loop Join(块嵌套循环连接) 。
不再是逐条获取主表的数据,而是一块一块的获取,引入了 join buffer 缓存区,将主表 join 相关的部分数据(列大小受 join buffer 的限制)缓存到 join buffer中,然后全表扫描从表,从表的每一条记录一次性和 join buffer 中的所有主表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合成一次,降低了从表的访问频率。
join_buffer_size:
主表能不能一次性加载完,要看 join buffer 能不能存储所有数据,默认情况下 join_buffer=256k。
加载不完的话,会分开加载,再开一个新的块保存剩下的主表数据。
JOIN 小结
- 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是 表行数 * 每行大小)
select t1.b,t2.* from t1 straight_join t2 on t1.b=t2.b where t2.id<=100;#推荐
select t1.b,t2.* from t2 straight_join t1 on t1.b=t2.b where t2.id<=100;#不推荐
straight_join :就是按照你写的 表的位置来作为主表、从表。不根据查询优化器来判断主从表。
那么第一条SQL,主表是 t1,从表是 t2
为什么两条SQL语句差不多,只有表的位置不一样,却推荐使用第一条SQL?
select t1.b,t2.* from... 中,表 t1 只查询了字段 b,而 t2 查询了所有字段。那么根据“小结果集驱动大结果集”原则,选表 t1 作为主表查询效率最好。
假如表 t1 有100条数据,t2 有1000条数据,主从表选择?
那么选择表 t1 作为主表合适
假如表 t1 有100条数据,t2 有1000条,加了条件 where t2.id<=50 过滤只有50条数据符合条件。主从表选择?
这种情况,虽然 t1表的数据100条,表 t2 数据1000,两表关联后 t2 的数据量经过条件过滤后,只有50条符合条件数据。这个时候 选择 表 t2 为主表,效率高。
- 为从表匹配的条件增加索引(减少内层表的循环匹配次数)
- 增大 join buffer size 的大小 (一次缓存的数据越多,那么内层包的扫描次数就越少)
- 减少主表不必要的字段查询(字段越少,join buffer 所缓存的数据就越多)