一.mysql中表关联有两种常见算法
1.1. N L J 嵌套循环算法 (Nested Loop Join)
1.2.B N L 块嵌套循环 (Block Nested Loop Join)
二. 简介
2.1.N L J 嵌套循环算法 (Nested Loop Join)
内连接inner join, 数据库会自主选择驱动表,(以小表驱动大表)
Left Join, 以左边的表为驱动表
Right Join,以右边的表为驱动表
执行的流程为:
循环驱动表,取出一行数据,然后赋值给对应的关联字段中的值
拿到上述行的对应字段的值,作为条件去被驱动表中搜索
2.1. B N L 块嵌套循环 (Block Nested Loop Join)
拿到所有驱动表的数据丢入一个Join Buffer中,
然后遍历 被驱动表里的每一条数据去进行匹配
三. 适用场景
3.1. 当被驱动表 的关联字段是 索引字段时 走NLJ 算法
3.2. 当被驱动表 的关联字段是 非索引(普通)字段时, 走BNL算法
为啥呢?
假设这里有大表t1 一万行数据, 小表t2 一百行数据
id ,主键字段
a , 普通索引字段
b , 普通字段
select t1.*, t2.* from t1 inner join t2 on t1.a = t2.a
(a是索引字段,走NLJ算法)
Select t1.*, t2.* from t1 inner join on t1.b = t2.b
(b是非索引的普通字段,走BNL算法)
假设:用b字段作为关联的这种场景,不走BNL,而是走NLJ算法会发生啥问题?
小表驱动大表的思想,t2去循环每一条数据,然后 通过 t1.b = ‘某个常量’去搜索t1表
这时,t1表就发生了全表扫描。 然后t2表循环到下一条数据,同样,t1表再次发生全表扫描
这是不可接受的!!!