MySQL中的驱动表和被驱动表
程序猿集锦2020-11-11 18:48
关注我，不迷路，以便看更多分享。
什么是straight_join
join的类别
说到straight_join，就不得不提起我们平时在写SQL语句的关联的时候，经常使用到的几种关联方式。具体有如下几中join的方式：
inner join：内连接。最后返回的数据行数是在inner join前后两张表中同时存在的数据行数。任何一条只存在于某一张表中的数据，都不会返回，left join：左连接，又称为left outer join，我们平时都把outer省略。简写为left joinleft左边的表为主表，left右边的表为从表。返回结果行数以left左边的表的行数为最后的数据行，对于左表中有些数据行在右表中找不到它所匹配的数据行记录时候，返回结果的时候这些行后面通常会以null来填充。right join：右连接，又称为right outer join，我们平时都把outer省略。简写为right joinright右边的表为主表，right坐标的表为从表。返回结果行数以right右边的表的行数为左后的数据行，对于主表中有些数据行在从表中找不到它所匹配的数据行记录时候，返回结果的时候这些行后面通常会以null来填充。full join：全连接。最后返回的数据行数是full join前后两张表的数行数的笛卡尔积。但是MySQL中没有这种写法，它直接使用select * from A,B;这样的写法就可以实现全连接。Oracle中有full join这种写法。理解了上面的几个join的区别后，接下来我们来创建两个表student和score来实际操一下。下面是我们实际操作的时候，需要使用到的SQL语句，其中包括的表的DDL语句和DML语句。
CREATE TABLE `student` (`id` int PRIMARY KEY auto_increment NOT NULL,`code` varchar(20) DEFAULT NULL,`name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE `score` (`id` int PRIMARY KEY auto_increment NOT NULL,`code` varchar(20) DEFAULT NULL,`chinese` double(4,0) DEFAULT NULL,`math` double(4,0) DEFAULT NULL,`engilsh` double(4,0) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into student values(1,'0001','张三');insert into student values(2,'0002','李四');insert into student values(3,'0003','王五');insert into score values(1,'0001',98,92,94);insert into score values(2,'0002',90,93,97);insert into score values(3,'0004',78,77,80);insert into score values(4,'0005',66,87,99);insert into score values(5,'0006',87,90,96);insert into score values(6,'0007',78,88,99);select * from student;select * from score;select * from student as a left join score as b on a.code = b.code;select * from student as a right join score as b on a.code = b.code;select * from student as a inner join score as b on a.code = b.code;select * from student as a cross join score as b on a.code = b.code;select * from student as a, score as b;
student表中的数据如下：
score表中的数据如下：
student inner jion score的结果如下：
student left join score的结果如下：
student right join score的结果如下：
student full join score的结果如下：
straight_join
而我们要讨论的straight_join的含义和上面的inner join有些类似，它也是对两个表进行inner join的操作，但是它属于一种特殊的inner join。
在关联查询的时候，inner join会根据两个关联的表的大小自动的选择哪一个表作为驱动表，哪一个表作为被驱动表。这个由MySQL内部的优化器自己去选择的。但是MySQL的优化器也不是100%靠谱，有时候由于我们对表频繁的CURD操作，表的统计信息也可能不够准确，所以它也会选择错误的优化方案。比如选择了错误的表作为驱动表，这样将导致我们的查询效率变得很低。
对于上述的这种情况，我们可以手动的去指定让MySQL去选择哪个作为驱动表哪个作为被驱动表吗？答案是肯定的，此时的straight_join就上场了。
它的功能是可以指定在straight_join前面的表作为驱动表，在straight_joion后面的表作为被驱动表。这样我们就可以指定让MySQL选择哪个表作为驱动表哪个表作为被驱动表了。
MySQL官网给straight_join的解释是下面这样的：
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.
straight_join使用示例
select * from A as a straight_join B as b on a.id = b.id;
上面的SQL语句在执行的过程中，总是会先读取straight_join前面的A表到join_buffer中，然后再去读取B表中的数据，以此去和join buffer中的A表去匹配。匹配上的则是表示放入最后的结果集中，匹配不上的则丢弃。
所以在straight_join语句中，左边的表总是驱动表，右边的表总是被驱动表。从下图可以看做结论：
驱动表和被驱动表
概念解释
什么是驱动表? 什么是被驱动表？
驱动表在SQL语句执行的过程中，总是先读取。而被驱动表在SQL语句执行的过程中，总是后读取。
在驱动表数据读取后，放入到join_buffer后，再去读取被驱动表中的数据，来和驱动表中的数据进行匹配。如果匹配上则作为结果集返回，否则丢弃。
如何区分驱动表和被驱动表
我们对于一个已有的SQL语句，我们应该怎么判断这个SQL语句中哪个表示驱动表？哪个表示被驱动表呢？
可以使用explain命令查看一下SQL语句的执行计划。在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。
join中如何写驱动表和被驱动表
了解了驱动表和被驱动表的概念之后，那么我们在平时写SQL语句的时候，该怎么判断我们所写的SQL语句中哪个是驱动表，哪个是被驱动表？
left join
下面的SQL中，A是驱动表，B是被驱动表。left join的左表示驱动表，右表示被驱动表。
select * from A as a left join B as b on a.id = b.id;
上述A left join B连接查询的执行计划如下，从中可以看出A是驱动表，B是被驱动表。
right join
下面的SQL中，A是被驱动表，B是驱动表。right join的右表示驱动表，左表示被驱动表。
select * from A as a right join B as b on a.id = b.id;
上述A right join B连接查询的执行计划如下，从中可以看出B是驱动表，A是被驱动表。
inner join
对于inner join而言，MySQL会选择小表作为驱动表，大表作为被驱动表。下面我做一些实验来说明这个大表和小表的定义是什么。
A、B的表结构和数据行数如下，A表中有3行数据，B表中有6行数据。
select * from A as a inner join B as b on a.id = b.id;
上述A inner jion B连接查询的执行计划如下，可以看出A是驱动表，B是被驱动表，这里mysql认为A是小表，B是大表。
A、B的表结构和数据行数如下，和前面的例子相比，A表中的数据行数增加到了27行数据，而不是原先的3行记录。
select * from A as a inner join B as b on a.id = b.id;
上述A inner jion B连接查询的执行计划如下，可以看出B是驱动表，A是被驱动表，这里mysql认为B是小表，A是大表。
基于上面的例子，我们把我们的SQL语句修改一下，A和B表的数据行数仍然为27和6。但是我的SQL语句中增加一个where条件，如下所示：select * from A as a inner join B as b on a.code = b.code where a.id <= 10;select * from A as a inner join B as b on a.code = b.code where a.id <= 11;
最后的执行计划如下图所示，从图中可以看出
当where条件是<=10的时候，A表示驱动表，B表示被驱动表。当where条件是<=11的时候，B表示驱动表，A表示被驱动表。
为什么会有这个奇怪的现象？
这是因为两个表在进行关联查询的时候，是根据真正参与关联查询的数据行和列所占用的空间大小来确认谁作为驱动表谁作为被驱动表的。上述的例子中，当a.id<=10的时候，A表参与关联的数据行和列是A中所有的列，然后是10行数据。这些行和列组成的数据占用的空间大小刚好比B表的所有列加上它的所有行所占用的空间小，所以此时选择A表作为驱动表。而当a.id<=11的时候，A表参与关联的数据行和列是A中所有的列，然后是11行数据。这些行和列组成的数据占用的空间大小刚好比B表的所有列加上它的所有行所占用的空间大，所以此时选择B表作为驱动表。
所以不能单独看表的数据总数据行数来决定谁是驱动表谁是被驱动表。要看实际参与关联的数据行是多少，还要看实际参与关联查询的字段有哪些，因为有些表虽然有很多的字段，但是我们关联查询的时候，实际上在select后面只用了1个字段，此时在计算参与关联查询的表锁占用的空间大小的时候，把这个表的所有字段都计算进来显示是不合理的。
而有些观点说只要是在where条件中出现的表，就是驱动表。显然通过我们上面的实验可以看出这个观点是不正确的。两条SQL语句，在where条件中都使用了a.id作为过滤条件，但是驱动表却因为过滤行数的不同而出现截然相反的结果。有些在where条件中没有出现的表，也可能是驱动表。
所谓的小表是什么表
前面我们提到在inner join中，MySQL会选择小表作为驱动表，那么我们怎么理解这个小表的概念呢？什么样的表算是小表?
这里对于大小的判断，是指真正参与关联查询的数据量所占用的join_buffer的大小来区分的，而不是根据表中所有的数据行数来判断的。
Demo1：两个表所有的字段类型和大小都相同的前提下，如果一个表有1000万行、10列的数据，但是经过where条件过滤后，只有10行数据参与join操作，而另外一张表有100行、10列数据。那么此时过滤后的有10行数据参与join操作的表是驱动表，有100行数据参与join操作的表示被驱动表。所以，我们不能盲目的认为表的数据行数少的表就一定是驱动表、数据行数大的表就一定是被驱动表。
另外，select关键字后面查询的字段列表也会影响到底谁是驱动表和被驱动表。
Demo2：一个表A有20个字段、50行数据；另外一个表B有2个字段、50行数据。两个表中所有的字段的类型全部为char(10)。我们的查询语句如下：
select a.*, b.code from A as a inner join B as b on a.id = b.id;
此时，表A是被驱动表，表B是驱动表。因为：查询的字段结果中，A表占用的空间10000比B表占用的空间500要大20倍。
对于A和B在查询的过程中，它们所占用空间的大小，我们可以采用如下简单的计算过程：
A表的所有字段都参与查询，所以：查询的字段数 * 参与join的行数 * 每个字段的空间大小 = 占用的空间大小，即为：20 * 50 * 10 = 10000B表只有code字段参与查询，同样的计算方式：查询的字段数 * 参与join的行数 * 每个字段的空间大小 = 占用的空间大小，即为：1 * 50 * 10 = 500注意：上面我们在对比A和B两个表在join关联时占用的空间大小的时候，忽略了a.i和b.id
所占用的空间，因为对于A和B来说，他们的行数一样都是50，字段类型也一样都是char(10)，两个ID占用的空间也是一样的。所以，在A和B两者都忽略id字段的情况下，最后判断的结果也是没有问题的。
综上两个例子，当我们使用inner join的时候，对于哪个表示小表，那个表是大表，需要仔细衡量一下，不能根据表中的数据量直接拍脑袋就直接下结论。
join buffer的概念
前面我们提到的join查询的时候，会把驱动表中的数据全部查询出来放入到内存中，而这个内存就是我们现在要说的：join buffer，它的大小是有参数join_buffer_size大小来决定的，默认值为262144字节，即为：256KB。而它所能设置的最小值为128字节，最大值为：4GB1字节。
对于在多表连接查询的时候，由于纯索引扫描、范围索引扫描和不使用索引因而执行全表扫描的关联查询，join_buffer_size的值的大小定义了一个buffer pool 最小的大小。
这个值不建议设计的特别大，因为MySQL会为每一个join查询语句都分配一个当前配置的join_buffer_size大小的
join buffer，所以如果这个值配置过大，那么当查询并发量大的时候，可能导致内存被吃掉的很多。
比较好的建议是保持全局的设置为一个较小的值，然后当我们执行一个比较大的join查询的时候，设置session级别的join_buffer_size比较大，只对当前会话级别的查询生效。
两个表join执行的过程是怎么样的
对于两个表A和B进行inner join这个join是怎么一个执行过程呢？他们的SQL语句如下：
select * from A as a inner join B as b on a.code = b.code;
我们假设A表是小表也就是驱动表，B表是大表也就是被驱动表。那么上面这个关联查询是怎么一个关联过程呢？
Index Nested-Loop Join
Index Nested-Loop Join：索引嵌套循环连接。
我们采用如下SQL来解释Index Nested-Loop Join这种关联查询的执行过程。
explain select * from A as a inner join B as b on a.id = b.id;
A、B的表结构和查询计划如下图所示：
基于上面的查询计划，我们可以看出B表是驱动表，A表是被驱动表。接下来我们详细说明一下这个关联链接的具体执行过程是怎样的。
获取B表中的第一行数据，然后从这行中，获取出该行的id的值。拿着id的值去A表中去查找满足该id值的行，此时使用了A表中的主键索引。找到后，把A表中的这个行和B表中的行拼接在一起，作为最后的结果集，返回给客户端。这样就完成了B表中第一行数据和A表的inner join过程。重复1、2、3步骤，直道把B表的所有行都遍历完成，就完成了此次的join过程。以上的步骤就是Index Nested-Loop Join执行的过程。注意：它在去被驱动表A中获取数据的时候，使用到了A表中的索引，并不是把A表中的所有数据都扫描一遍，再去和B表中的数据去匹配。
Simple Nested-Loop Join
Simple Nested-Loop Join：简单嵌套循环连接。
我们这次把上面的使用的SQL简单修改一下，改为如下的这种写法。让其在关联的时候，不使用a.id = b.id的关联方式，而是使用a.code = b.code的关联方式。注意：此时在两个表上面的code字段都没有索引。
explain select * from A as a inner join B as b on a.code = b.code;
执行计划如下，从中可以看出B表示驱动表，A表示被驱动表。
此时在去A表被驱动表中查找数据的时候，就不能通过A表中的索引来获取数据了。那么就需要把A表中所有的数据都扫描一遍，然后再和B表中的id值进行匹配。这样每处理B表中的一行，A表中的数据都要全部扫描一次。这样的效率比前面我们提到的Index Nested-Loop Join慢了很多。
但是，MySQL在这种情况下并没有采用这种慢的方式，而是采用了下面我们将要说的Block Nexted-Loop Join的关联方式。这就是为什么我们在上面的查询计划中没有看到Simple Nested-Loop Join的原因。
Block Nexted-Loop Join
Block Nexted-Loop Join：基于块的嵌套循环连接。
因为在被驱动表A找那个没有索引，所以MySQL此时才去的关联方式是基于块的嵌套循环连接。执行计划如下所示：
此时的关联过程是这样的：
首选会把驱动表B的数据全部加载到join buffer中，因为我们的select语句包含了B表的所有列，所以把B表全部列和行都加载到join buffer中。扫描被驱动包A，把表中的行依次取出来和join buffer中的驱动表B中的数据进行匹配查询。匹配上则保留为左后的结果，否则丢弃。在上面的过程中，需要把驱动表中的数据全部加载到join buffer中。但如果驱动表B中的数据特别多，而我们的join buffer特比较小，join buffer的大小不能全部装下驱动表B中的数据时，会怎么样呢？
MySQL会把驱动表B中的数据，分批次的放入到join buffer中。每次在放置新的数据进入join buffer之前，会清空上一次的数据。并且，每次在放入新的数据后，都会再次把被驱动表A中的数据扫描一遍和join buffer中 数据进行关联匹配。
此时，如果适当的增加join buffer的大小，则可以减少分批次放入的次数，也就可以减少对被驱动表扫描的次数。所以在某些情况下，适当的增加join_buffer_size的值，可以提供join查询的效率。
综上所述：在mysql的join连接中，需要遵循如下两点：
以小表驱动大表。在left join和right join的时候，要注意小表要放在SQL语句的正确位置上。给被驱动表建立索引。
举报/反馈
0
0
收藏
分享
