文章目录
Join使用的是Nested-Loop Join算法,Nested-Loop Join(NLJ)有三种
select * from t1 join t2 on t1.a = t2.a;
-- a 100条数据, b 1000条数据
Simple Nested-Loop Join
会遍历t1全表,t1作为驱动表,t1中的每一条数据都会到t2中做一次全表查询,该过程会比较100*1000次。
每次在t2中做全表查询时,全表扫描可就不保证在内存里了,Buffer Pool会淘汰,有可能在磁盘。
Block Nested-Loop Join(MYSQL驱动链接没有使用索引)
会遍历t1全表,将t1数据加载到join_buffer中,再遍历t2全表,让t2的每条数据去匹配join_buffer中t1缓存的数据。
当不使用Index Nested-Loop Join的时候,默认使用Block Nested-Loop Join。
t1全表扫描 = 100次
t2全表扫描 = 1000次
查询次数 = 1100次
join_buffer中比较 = 100 * 1000次
比较的次数和Simple Nested-Loop Join是一样的,但是比较的过程会比Simple Nested-Loop Join快很多,性能更好。
join_buffer是有大小的,如果t1查出来的数据是大于join_buffer大小的,则会先加载部分t1中的数据,比较完t2以后,清空join_buffer,再加载t1中剩余数据,加载不完全,再重复该操作。
t1全表扫描次数和join_buffer中比较1次数不变,但是t2的扫描次数会根据分段次数做一个乘法。
假设,驱动表的数据行数是 N,需要分 K 段才能完成算法流程,被驱动表的数据行数是 M。
K = λ * N
扫描被驱动表次数 = M * λ * N
λ是和join_buffer的大小有关的,join_buffer大小足够的情况下,大表驱动和小表驱动的时间是一样的。
需要分段的情况下,分段次数越少,被驱动表扫描的次数也会越少,所以应该采用小表驱动。
简单理解Block Nested-Loop Join如下:
- 缓存块嵌套循环连接通过一次性缓存多条数据,把参与查询的列缓存到Join Buffer 里,然后拿join buffer里的数据批量与内层表的数据进行匹配,从而减少了内层循环的次数(遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据)。
- 当不使用Index Nested-Loop Join的时候,默认使用Block Nested-Loop Join。
MySQL使用Join Buffer有以下要点:
- join_buffer_size变量决定buffer大小。
- 只有在join类型为all, index, range的时候才可以使用join buffer。
- 能够被buffer的每一个join都会分配一个buffer,也就是说一个query最终可能会使用多个join buffer。
- 第一个nonconst table不会分配join buffer,即便其扫描类型是all或者index。
- 在join之前就会分配join buffer, 在query执行完毕即释放。 join buffer中只会保存参与join的列, 并非整个数据行。
Index Nested-Loop Join(MYSQL驱动链接使用索引)
还是以上面的sql为例,如果a字段是有索引的。
t1表会扫描全表,t1表中每条数据会去t2表中做索引查询,查到id后再进行回表查询(如果连接字段是t2表的主键,回表操作将省略)。
t1扫描全表 = 100次
t2索引查询 = log1000次
t2回表查询 = log1000次
假设,驱动表的数据行数是 N,被驱动表的数据行数是 M。
总查询次数 = N + N * 2logM
由上可见,驱动表数据越大,查询的次数会越多,所以应该使用小表作为驱动表。