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

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

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

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

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就是驱动表, 符合预期

---

至此, 我们彻底的了解了 inner join算法驱动表的选择, 也了解了 mysql如何选择驱动表, 如何选择小表, 这对于我们后期SQL分析, 索引优化很重要, 因为我们要在 被驱动表上 添加索引,优化提升我们的查询效率