MSQL系列(十一) Mysql实战-Inner Join算法底层原理及驱动表选择

Mysql实战-Inner Join算法驱动表选择

前面我们讲解了B+Tree的索引结构，及Mysql的存储引擎MyISAM和InnoDB,也详细讲解下 left Join的底层驱动表选择, 并且初步了解 Inner join是Mysql 主动选择优化的驱动表，知道索引要建立在被驱动表上

那么对于Inner join 来说, 到底什么是小表?

文章目录

- - Mysql实战-Inner Join算法驱动表选择
  - - 1.建表及测试数据
    - 2. inner join where条件不一致,判断大小表
    - 3. inner join小表 select字段不一致,判断大小表

1.建表及测试数据

我们先创建几乎一样的表结构用来测试 testA和testB,

testA 4条数据, 索引只有主键id
testB 6条数据, 索引只有主键id


drop TABLE IF EXISTS testA;
CREATE TABLE `testA` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表A';

drop TABLE IF EXISTS testB;
CREATE TABLE `testB` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hero_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '英雄名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表B';

#插入测试数据 testA 4条数据
INSERT INTO `testA` (user_name) VALUES ("张三");
INSERT INTO `testA` (user_name) VALUES ("李四");
INSERT INTO `testA` (user_name) VALUES ("王五");
INSERT INTO `testA` (user_name) VALUES ("吕布");

#插入测试数据 testB 10条数据
INSERT INTO `testB` (hero_name) VALUES ("亚瑟");
INSERT INTO `testB` (hero_name) VALUES ("鲁班");
INSERT INTO `testB` (hero_name) VALUES ("妲己");
INSERT INTO `testB` (hero_name) VALUES ("大乔");
INSERT INTO `testB` (hero_name) VALUES ("小乔");
INSERT INTO `testB` (hero_name) VALUES ("黄忠");
INSERT INTO `testB` (hero_name) VALUES ("元芳");
INSERT INTO `testB` (hero_name) VALUES ("后羿");
INSERT INTO `testB` (hero_name) VALUES ("马克");
INSERT INTO `testB` (hero_name) VALUES ("吕布");

查看插入结果, 符合预期
在这里插入图片描述

2. inner join where条件不一致,判断大小表

我们知道 join 是where自己选择的驱动表, 选择小表作为驱动表, 如何判断小表?

那么到底什么是小表呢?
是否是数据量小的表一定是小表?
跟索引是否有关系?

下面我们来解决这些问题

Mysql这里对于大小的判断，是指真正参与关联查询的数据量所占用的join_buffer的大小来区分的, 不是根据表中所有的数据行数来判断的

所以说不是数据量小的表就是小表

我们用实例来验证下

#inner join where条件不一致 导致的 驱动表不一致
explain select * from testB as b inner join testA as a on a.id = b.id where b.id > 1;
explain select * from testB as b inner join testA as a on a.id = b.id where b.id > 9;

执行结果
在这里插入图片描述
结果分析

同样的SQL语句, 只有条件不一样, 就会导致不同的驱动表
第一条 inner join SQL 驱动表是 A
第二条 inner join SQL 驱动表是 B
表A和表B的结构是一样的, 所以加载到 join_buffer的东西大小也是一样的
当 id > 1的时候, B表经过where条件过滤后，有10行数据参与join操作, 所以B表数据 10条, 这时候 A全部表也就4条数据, 所以A就是小表, 驱动表就是A
当 id > 9的时候, B表经过where条件过滤后，有2行数据参与join操作, , 而A表全部数据是4条, 这时候B 就是小表, 驱动表就是B
所以不是数据总行数决定驱动表,而是经过过滤后, 参与到join操作的数据来决定大小表

3. inner join小表 select字段不一致,判断大小表

上面我们看到了都是select * 表结构一致, 查询where条件不一致导致的驱动表不一致的情况, 那么还有其他情况影响驱动表么?

当然有, select 后面查询字段也会影响到大小表驱动表的判断,因为 join buffer 判断的就是查询的字段加载进内存
我们再建一个表用来测试,该表字段较多

#创建testC表
drop TABLE IF EXISTS testC;
CREATE TABLE `testC` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `emp_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表C';

#创建testD表
drop TABLE IF EXISTS testD;
CREATE TABLE `testD` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `emp_name1` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名1',
  `emp_name2` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名2',
  `emp_name3` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名3',
  `emp_name4` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名4',
  `emp_name5` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人名5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表C';

#testC 插入2条数据
INSERT INTO `testC` (emp_name) VALUES ("亚瑟1");
INSERT INTO `testC` (emp_name) VALUES ("鲁班1");

#testC 插入2条 很多列的数据
INSERT INTO testD (emp_name1, emp_name2, emp_name3, emp_name4,emp_name5) VALUES ("1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111","1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111","1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111","1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111","1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111");
INSERT INTO testD (emp_name1, emp_name2, emp_name3, emp_name4,emp_name5) VALUES ("2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222","2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222","2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222","2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222","2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222");

查看结果,符合预期, C,D表都是2条数据
在这里插入图片描述

现在我们使用testB和testC, testD 我们来验证下 select 的列对大表小表的影响

testC 表只有2列, 列属性都是char(32)
testD 表有5列, 列属性都是varchar(500)
按照 join_buffer 加载逻辑, 相同条件下( 行数一样) 只要是查了 testD的列, 他的列比较大, 占空间比较多, 就是大表
计算占用join_buffer 空间大小 = 查询的字段数 * 参与join的行数 * 每个字段的空间大小
占用的空间大小
所以对于testC和testD 只要查了D的列, D列多,字段多,空间大, 就是大表
这种情况下驱动表是小表, 就应该是 testC C表

看下我们分析的结果

#inner join select字段不一致 导致的 驱动表不一致, 字段多的空间大, 就是大表
explain select d.* from testC as c inner join testD as d on c.id = d.id ;

执行结果, 的确查询了testD的列, 字段多, 占用 join_buffer 空间大, 就是大表, 所以驱动表就选择小表 testC, 符合预期
在这里插入图片描述

那如果我们查询testC的列呢? 应该如何选择驱动表?

testC 表只有2列, 列属性都是char(32)
testD 表有5列, 列属性都是varchar(500)
查询 testC的列, 那么加载进 join_buffer的就是 c的全部列
对于 testD来说,加载进 join_buffer的就是 d的主键id 列, 因为 on条件关联的是 c.id = d.id
所以 d只有id列, c是全部列, c就是大表, d就是小表, d就是驱动表

#inner join select字段不一致 导致驱动表不一致, 查询 字段少的
#加载进join_buffer的 testC全字段,testD只有id字段, 那就是testD 占空间少, 驱动表就是d 
explain select c.* from testC as c inner join testD as d on c.id = d.id;

explain 执行结果 ,看到 d就是驱动表, 符合预期
在这里插入图片描述