mysql左连接表执⾏逻辑_Mysql多表连接查询的执⾏细节
（⼀）
先构建本篇博客的案列演⽰表：
create table a(a1 int primary key, a2 int ,index(a2)); --双字段都有索引
create table c(c1 int primary key, c2 int ,index(c2), c3 int); --双字段都有索引
create table b(b1 int primary key, b2 int);--有主键索引
create table d(d1 int, d2 int); --没有索引
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
insert into b values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
mysql语句多表查询insert into c values(1,1,1),(2,4,4),(3,6,6),(4,5,5),(5,3,3),(6,3,3),(7,2,2),(8,8,8),(9,5,5),(10,3,3);
insert into d values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
驱动表如何选择？
驱动表的概念是指多表关联查询时，第⼀个被处理的表，使⽤此表的记录去关联其他表。驱动表的确定很关键，会直接影响多表连接的关联顺序，也决定了后续关联时的查询性能。
驱动表的选择遵循⼀个原则：在对最终结果集没影响的前提下，优先选择结果集最⼩的那张表作为驱动表。改变驱动表就意味着改变连接顺序，只有在不会改变最终输出结果的前提下才可以对驱动表做优化选择。在外连接情况下，很多时候改变驱动表会对输出结果有影响，⽐如left join的左边表和right join的右边表，驱动表选择join的左边或者右边最终输出结果很有可能会不同。
⽤结果集来选择驱动表，那结果集是什么？如何计算结果集？mysql在选择前会根据where⾥的每个表的筛选条件，相应的对每个可作为驱动表的表做个结果记录预估，预估出每个表的返回记录⾏数，同时再根据select⾥查询的字段的字节⼤⼩总和做乘积：
每⾏查询字节数 * 预估的⾏数 = 预估结果集
通过where预估结果⾏数，遵循以下规则：
如果where⾥没有相应表的筛选条件，⽆论on⾥是否有相关条件，默认为全表
如果where⾥有筛选条件，但是不能使⽤索引来筛选，那么默认为全表
如果where⾥有筛选条件，⽽且可以使⽤索引，那么会根据索引来预估返回的记录⾏数
我们以上述创建的表为基础，⽤如下sql作为案列来演⽰：
select a.*,c.c2 from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;
通过explain查看其执⾏计划：
explain显⽰结果⾥排在第⼀⾏的就是驱动表，此时表c为驱动表。
如果将sql修改⼀下，将select ⾥的条件c.c2 修改为 c.* ：peap是什么意思
select a.*,c.* from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;
通过explain查看其执⾏计划：
此时驱动表还是c，按理来说 c.* 的数据量肯定是⽐ a.*⼤的，似乎结果集⼤⼩的规则在这⾥没有起作⽤。
此情形下如果⽤a作为驱动表，通过索引c2关联到c表，那么还需要再回表查询⼀次，因为仅仅通过c2获取不到c.*的数据，还需要通过c2上的主键c1再查询⼀次。⽽上⼀个sql查询的是c2，不需要额外查询。同时因为a表只有两个字段，通过a2索引能够直接获得a.*，不需要额外查询。
综上所述，虽然使⽤c表来驱动，结果集⼤⼀些，但是能够减少⼀次额外的回表查询，所以mysql认为使⽤c表作为驱动来效率更⾼。
结果集是作为选择驱动表的⼀个主要因素，但不是唯⼀因素。
2 . 两表关联查询的内在逻辑是怎样的？
mysql表与表之间的关联查询使⽤Nested-Loop join算法，顾名思义就是嵌套循环连接，但是根据场景不同可能有不同的变种：⽐如Index Nested-Loop join，Simple Nested-Loop join,Block Nested-Loop join, Betched Key Access join等。
在使⽤索引关联的情况下，有Index Nested-Loop join和Batched Key Access join两种算法；
在未使⽤索引关联的情况下，有Simple Nested-Loop join和Block Nested-Loop join两种算法；
我们先来看有索引的情形，使⽤的是博客刚开始时建⽴的表，sql如下：
select a.*,c.* from a join c on a.a2=c.c2 where a.a1>4;
通过explain查看其执⾏计划：
⾸先根据第⼀步的逻辑来确定驱动表a，然后通过a.a1>4，a.来查询⼀条记录a1=5，将此记录的c2关联到c表，取得c2索引上的主键c1，然后⽤c1的值再去聚集索引上查询c.*，组成⼀条完整的结果，放⼊net buffer，然后再根据条件a.a1>4，a. 取下⼀条记录，循环此过程。过程图如下：
通过索引关联被驱动表，使⽤的是Index Nested-Loop join算法，不会使⽤msyql的join buffer。根据驱动表的筛选条件逐条地和被驱动表的索引做关联，每关联到⼀条符合的记录，放⼊net-buffer中，然后继续关联。此缓存区由net_buffer_length参数控制，最⼩4k，最⼤16M，默认是1M。 如果net-buffer满了，将其发送给client，清空net-buffer，继续上⼀过程。
通过上述流程知道，驱动表的每条记录在关联被驱动表时，如果需要⽤到索引不包含的数据时，就需要回表⼀次，去聚集索引上查询记录，这是⼀个随机查询的过程。每条记录就是⼀次随机查询，性能不是⾮常⾼。mysql对这种情况有选择的做了优化，将这种随机查询转换为顺序查询，执⾏过程如下图：
此时会使⽤Batched Key Access join 算法，顾名思义，就是批量的key访问连接。
逐条的根据where条件查询驱动表，将符合记录的数据⾏放⼊join buffer，然后根据关联的索引获取被驱动表的索引记录，存⼊
read_rnd_buffer。join buffer和read_rnd_buffer都有⼤⼩限制，⽆论哪个到达上限都会停⽌此批次的数据处理，等处理完清空数据再执⾏下⼀批次。也就是驱动表符合条件的数据可能不能够⼀次处理完，⽽要分批次处理。
当达到批次上限后，对read_rnd_buffer⾥的被驱动表的索引按主键做递增排序，这样在回表查询时就能够做到近似顺序查询：
如上图，左边是未排序前的随机查询⽰意图，右边是排序后使⽤MRR(Multi-Range Read)的顺序查询⽰意图。
因为mysql的InnoDB引擎的数据是按聚集索引来排列的，当对⾮聚集索引按照主键来排序后，再⽤主键去查询就使得随机查询变为顺序查询，⽽计算机的顺序查询有预读机制，在读取⼀页数据时，会向后额外多读取最多1M数据。此时顺序读取就能排上⽤场。
redis的数据需要持久化吗BKA算法在需要对被驱动表回表的情况下能够优化执⾏逻辑，如果不需要会表，那么⾃然不需要BKA算法。
如果要使⽤ BKA 优化算法的话，你需要在执⾏ SQL 语句之前先设置：
headers already sent是什么意思
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
前两个参数的作⽤是要启⽤ MRR(Multi-Range Read)。这么做的原因是，BKA 算法的优化需要依赖于MRR，官⽅⽂档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使⽤ MRR，把 mrr_cost_based 设置为 off，就是固定使⽤ MRR 了。)
最后再⽤explain查看开启参数后的执⾏计划：sql优化培训班
上述都是有索引关联被驱动表的情况，接下来我们看看没有索引关联被驱动表的情况。
没有使⽤索引关联，那么最简单的Simple Nested-Loop join，就是根据where条件，从驱动表取⼀条数据，然后全表扫⾯被驱动表，将符合条件的记录放⼊最终结果集中。这样驱动表的每条记录都伴随着被驱动表的⼀次全表扫描，这就是Simple Nested-Loop join。
特斯拉model3保费多少
当然mysql没有直接使⽤Simple Nested-Loop join，⽽是对其做了⼀个优化，不是逐条的获取驱动表的
数据，⽽是多条的获取，也就是⼀块⼀块的获取，取名叫Block Nested-Loop join。每次取⼀批数据，上限是达到join buffer的⼤⼩，然后全表扫⾯被驱动表，每条数据和join buffer⾥的所有⾏做匹配，匹配上放⼊最终结果集中。这样就极⼤的减少了扫描被驱动表的次数。
BNL(Block Nested-Loop join) 和 BKA(Batched Key Access join)的流程有点类似， 但是没有read_rnd_buffer这个步骤。
⽰例sql如下：
select a.*, d.* from a join d on a.a2=d.d2 where a.a1>7;
⽤explain查看其执⾏计划：
3 . 多表连接如何执⾏？是先两表连接的结果集然后关联第三张表，还是⼀条记录贯穿全局？
其实看连接算法的名称：Nested-Loop join，嵌套循环连接，就知道是多表嵌套的循环连接，⽽不是先两表关联得出结果，然后再依次关联的形式，其形式类似于下⾯这样：
for row1 in table1 filtered by where{
for row2 in table2 associated by table1.index1 filtered by where{
for row3 in table3 associated by table2.index2 filtered by where{
put into net-buffer then send to client;
}
}
}
对于不同的join⽅式，有下列情况：
Index Nested-Loop join：
sql如下：
select a.*,b.*,c.* from a join c on a.a2=c.c2 join b on c.c2=b.b2 where b.b1>4;
通过explain查看其执⾏计划：
其内部执⾏流程如下：
执⾏前mysql执⾏器会确定好各个表的关联顺序。⾸先通过where条件，筛选驱动表b的第⼀条记录b5，然后将⽤此记录的关联字段b2与第⼆张表a的索引a2做关联，通过Btree定位索引位置，匹配的索引可能不⽌⼀条。当匹配上⼀条，查看where⾥是否有a2的过滤条件且条件是否需要索引之外的数据，如果要则回表，⽤a2索引上的主键去查询数据，然后做判断。通过则⽤join后的信息再⽤同样的⽅式来关联第三章表c。
Block Nested-Loop join 和 Batched Key Access join : 这两个关联算法和Index Nested-Loop join算法类似，不过因为他们能使⽤join buffer，所以他们可以每次从驱动表筛选⼀批数据，⽽不是⼀条。同时每个join关键字就对应着⼀个join buffer，也就是驱动表和第⼆张表⽤⼀个join buffer，得到的块结果集与第三章表⽤⼀个join buffer。
本篇博客主要就是讲述上述三个问题，如何确定驱动表，两表关联的执⾏细节，多表关联的执⾏流程。
有疑问欢迎留⾔，共同进步。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。