北京做网站哪家强,寻找石家庄网站建设,wordpress页面如何调用文章分类,wordpress excel插件Mysql实战-Inner Join算法驱动表选择
前面我们讲解了BTree的索引结构#xff0c;及Mysql的存储引擎MyISAM和InnoDB,也详细讲解下 left Join的底层驱动表 选择, 并且初步了解 Inner join是Mysql 主动选择优化的驱动表#xff0c;知道索引要建立在被驱动表上
那么对于Inner j…Mysql实战-Inner Join算法驱动表选择
前面我们讲解了BTree的索引结构及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条数据, 索引只有主键idtestB 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)
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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)
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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)
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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)
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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分析, 索引优化很重要, 因为我们要在 被驱动表上 添加索引,优化提升我们的查询效率
