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  阅读(592)  评论(0)  编辑  收藏  举报