mysql join语句的执行流程是怎么样的

mysql join语句的执行流程是怎么样的

join语句是使用十分频繁的sql语句,同样结果的join语句,写法不同会有非常大的性能差距。

select * from t1 straight_join t2 on (t1.a=t2.a);a字段都有索引

  1. TRAIGHT_JOIN语法能指定使用左边的表作为join语句的驱动表,join是让执行器自动选择。以上语句会选择t1作为驱动表。

  2. join语句,mysql内部执行时候会采用2中算法。一个是NLJ(Index Nested-Loop Join)。一个是BNL(Block Nested-Loop Join)

  3. NLJ:在join语句执行过程中,如果可以使用到被驱动表的索引,我们称之为“Index Nested-Loop Join”,简称 NLJ。

  4. 驱动表是走全表扫描,而被驱动表是走树搜索,所以驱动表行数越小越好。扫描行数多,性能影响更大,因此应该让小表来做驱动表。

  5. 如果驱动表有索引,被驱动表没有索引,这种情况下,驱动表全表扫描后,去被驱动表中匹配where语句的条件,在被驱动表找一条数据又是全表扫描。这样整个join扫描行数会内指数级别扩大。这种叫“Simple Nested-Loop Join”算法。

  6. 基于第五点,这种情况太笨重。所以msql没有采用”Simple Nested-Loop Join”算法,而是叫“Block Nested-Loop Join”的算法,简称 BNL。被驱动表没有索引情况下,他的逻辑流程是这样的:

    1. 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存;

    2. 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。

    1. explain语句查询出来会有use join buffer (block nested loop)关键字

    2. join语句采用BNL算法,虽然对表 t1(100行) 和 t2(1000行) 都做了一次全表扫描,因此总的扫描行数是1100。由于 join_buffer 是以无序数组的方式组织的,因此对表 t2 中的每一行,都要做 100 次判断,总共需要在内存中做的判断次数是:100*1000=10 万次。对比simple Nested-Loop Join算法他是在内存中做对比计算。能大大提供性能。

    3. join_buffer 的大小是由参数join_buffer_size设定的,默认值是 256k。如果放不下表 t1 的所有数据话,策略很简单,就是分段放。就是放多少先处理多少先作为结果集返回,然后清空join_buffer,继续读取后面的数据。

    4. 所以考虑到join_buffer大小有限,让小表作为驱动表,分段情况下,分段次数少。也应该让小表作为驱动表。

    5. 在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。

    join语句优化:

    1. mysql在join语句时,内部做了一些优化,即:Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。原理是:mysql的索引数据目录中,都是有序的,我们读入数据后,按主键排下序。这样就极大可能在磁盘是顺序读盘。这引入了read_rnd_buffer ,它的大小是由 read_rnd_buffer_size 参数控制的。

    2. 如果你想要稳定地使用 MRR 优化的话,需要设置set optimizer_switch="mrr_cost_based=off"。(官方文档的说法,是现在的优化器策略,判断消耗的时候,会更倾向于不使用 MRR,把 mrr_cost_based 设置为 off,就是固定使用 MRR 了。)explain语句也会有use MRR关键字

    3. 在使用BNL算法时候,引擎是一行一行读取数据。这样就用不上MRR算法优化,所以采取了BKA (Batched Key Access)算法。他可以一次性从驱动表多读一些数据,这些数据临时放在join_buff中。(之前BNL算法用不上join_buff,就利用了这个空间)。

    4. NBL算法优化后的BKA算法后,执行流程如下:

    5. 如果 join buffer 放不下 P1~P100 的所有数据,就会把这 100 行数据分成多段执行上图的流程。如果要使用 BKA 优化算法的话,你需要在执行 SQL 语句之前,先设置set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';前两个参数的作用是要启用 MRR。这么做的原因是,BKA 算法的优化要依赖于 MRR。

    6. BNL算法数据太大,稍不主机就会极大影响mysql服务性能,导致Buffer Pool命中率变低。大表 join 操作虽然对 IO 有影响,但是在语句执行结束后,对 IO 的影响也就结束了。但是,对 Buffer Pool 的影响就是持续性的,需要依靠后续的查询请求慢慢恢复内存命中率。

    7. BNL 算法对系统的影响主要包括三个方面:

      1. 可能会多次扫描被驱动表,占用磁盘 IO 资源;
      2. 判断 join 条件需要执行 M*N 次对比(M、N 分别是两张表的行数),如果是大表就会占用非常多的 CPU 资源;
      3. 可能会导致 Buffer Pool 的热数据被淘汰,影响内存命中率。
    8. BNL算法优化:

      1. BNL 转 BKA算法,在驱动表和被驱动表建索引,如果不方便建索引(数据大,join语句不频繁),可以人工主动使用临时表中转,拆分多个语句转化成BKA算法。
      2. hash join。条件匹配是n x m级别计算,如果 join_buffer 里面维护的不是一个无序数组,而是一个哈希表的话,那么就不是 10 亿次判断,而是 100 万次 hash 查找。mysql不支持哈希 join。并且,MySQL 官方的 roadmap,也是迟迟没有把这个优化排上议程。备注:mysql8.0已经支持

执行流程:

  1. 从表 t1 中读入一行数据 R;
  2. 从数据行 R 中,取出 a 字段到表 t2 里去查找;
  3. 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
  4. 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束。

posted @ 2021-10-15 11:16  penngke  阅读(67)  评论(0编辑  收藏  举报