Mysql-06 表关联查询原理

-- 示例表 
记录数10000+
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

记录数100+
create table t2 like t1;

表关联算法

1）Nested-Loop Join（NLJ算法）

从驱动表一次取出一行，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表结果的合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

sql的大致流程 

1.t2表如果有查询条件，根据查询条件过滤出结果，没有查询条件读取所有数据（100行）

2.遍历t2结果行中的a字段的值，根据a的值扫描t1表中的对应行（扫描100次t1的索引）

3.取出t1中满足结果的行（回表）和t2的结果合并得到最终结果（整个过程扫描了200行）

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join算法。

2）Block Nested-Loop Join （BNL算法)

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

sql的大致流程 

1.把 t2 的所有数据放入到 join_buffer 中

2.把表 t1中每一行取出来，根据关联字段b的值跟 join_buffer 中的数据做对比

3.返回满足 join 条件的数据

整个过程对t1和t2都做了一次全表扫描，扫描总行数t1+t2=100100，join_buffer里的数据是无序的，t1 中的每一行都要做 100 次判断，所以内存中的判断次数是 10000 * 100 = 100 万次，join_buffer 由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据，就会分段放

如果 t2 表有1000行记录， join_buffer 一次只能放800行数据，执行过程如下：

1.先往 join_buffer 里放800行记录

2.从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果

3.清空 join_buffer ，再放入 t2 表剩余200行记录

4.再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

总结：

1.t2是驱动表，t1是被驱动表 （先执行的表是驱动表，explain执行计划id一样时按顺序从上到下执行）

2.优化器一般会选择记录数小的表做驱动表，where条件过滤后再与被驱动表做关联

3.left join左表是驱动表，right join右表是驱动表，join时数据量较小的表是驱动表

4.join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

5.被驱动表的关联字段没索引使用 BNL ，因为如果使用NLJ，上述第二条sql的扫描行数100*10000=100万次并且是磁盘扫描

6.有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高