mysql join操作内核分析，总结join操作的优化手段

在我们的日常编写sql语句中，查询语句中，比较耗时的就属join操作了，当然，我们对于join的操作在mysql查询算法内核的操作并不了解，因此我们需要了解join操作在mysql内核是怎么操作的，达到我们sql语句的优化，实现最大限度的优化我们的查询操作等。

 join操作：

首先，我们日常的join可以分为 left_join等，说白了，其实就是joins的操作是一次笛卡儿积操作。

全表扫描

​ 假设，现在对一个200G的innodb的表，做全表扫描，把扫描结果保存在客户端。

mysql ‑h$host ‑P$port ‑u$user ‑p$pwd ‑e "select * from db1.t" > $target_file

​ 这个语句的结果集存在哪里呢？

  • 实际上，服务端并不需要保存一个完整的结果集。取数据和发数据流程如下：
    1. 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是
      16k。
    2. 重复获取行，直到net_buffer写满，调用网络接口发出去。
    3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
    4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send
      buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
  • 一个查询在发送过程中，占用mysql内部的内存最大的就是net_buffer_length，并不会达到200G
  • socket send buffer 也不可能达到200G
  • 也就是说，MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会
    导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。

全表扫描对innodb的影响

  • innodb内存的一个作用是保存更新的结果，再配合redo log，避免了随机斜盘。
  • 内存的数据页在Buffer Pool中，在wal里，buffer pool即起到了加速更新的作用，也有个作用是加速查询。其依赖一个重要指标：内存命中率。
    • 执行 show engine innodb status 可以看到命中率
    • 如果所有查询需要的数据页都能够直接从内存得到，那是最好的，对应的命中率就是100%。
      但，这在实际生产上是很难做到的。
    • InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。
    • InnoDB内存管理用的是最近最少使用 (Least Recently Used, LRU)算法，这个算法的核心就是淘汰最久未使用的数据。
  • 全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

mysql如何使用join

CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;

可以看到，这两个表都有一个主键索引id和一个索引a，字段b上无索引。假设表t2有1000行数据，表t1是100行数据。

  • select * from t1 straight_join t2 on (t1.a=t2.a);

    straight_join可以让mysql使用固定的连接方式执行join，这个语句里，ti是驱动表，t2是被驱动表。执行流程如下：

    • 从表t1读入一行数据R
    • 从R中，取出a到t2里查找
    • 取出t2中满足的行和R一行，作为结果集的一部分
    • 重复以上步骤，直到t1的循环结束。

    这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记
    录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所
    以我们称之为“Index Nested-Loop Join”，简称NLJ。

    流程图如下：

    t1做了全表扫描，100行，t2是走了树搜索过程，也是100行。整个执行流程，总扫描行数是200。

  • 假设不适用join，用单表查询。

    1. 执行select * from t1，查出表t1的所有数据，这里有100行；
    2. 循环遍历这100行数据：
      • 从每一行R取出字段a的值$R.a；
      • 执行select * from t2 where a=$R.a；
      • 把返回的结果和R构成结果集的一行。

    可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了
    100次交互。除此之外，客户端还要自己拼接SQL语句和结果。

如何选择驱动表

  • 之前的join语句执行过程中，驱动表走的是全表扫描，被驱动表走树搜索。
  • 假设被驱动表的行数是M，每次在被驱动表插一行数据，先搜索索引a，再搜索主键索引，每次搜索一次近似复杂度是logM，在被驱动表上查一行的时间复杂度是2*logM。
  • 假设驱动表行数是N，搜索过程就要扫描驱动表N行，对于每一行，到被驱动表匹配一次，因此整个过程复杂度近似N+N*2*logM，显然，N对扫描行数的影响更大，应该让小表做驱动表。
  • 小结：
    • 使用join语句，性能比强行拆成多个单表执行sql语句性能要好
    • 如果使用join语句，需要让小表做驱动表。
    • 注意：前提是可以使用被驱动表的索引。

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b没有索引，因此再用上图的流程去匹配的时候，就要做一次全表扫描。

那么这一次扫描的行数就是100*1000 = 10万行。这个就叫Simple Nested-Loop Join，

很笨重，下面是mysql使用的另一种算法Block Nested-Loop Join

Block Nested-Loop Join

  1. 把表t1的数据读入线程内存join_buffer中，由于是select * ,因此是把整个表t1放入内存

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

    所以，在整个过程中，只对t1和t2都做了一次全表扫描，总行数是1100，由于join_buffer是无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共次数是10万次，从时间复杂度来看，两者是一样的，但BNL的算法是内存上的操作，速度会更快，性能页更好。

    • 如何选择驱动表

      假设小标N行，大表M行，扫描的总行数是M+N，内存判断次数是M*N，所以选择大表还是小标，耗时是一样的。

      如果t1是大表，join_buffer会不会放不下呢？

      join_buffer可以设置大小，默认是256k，如果放不下t1的所有数据，就分段放。流程如下：

      1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
      2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
      3. 清空join_buffer；
      4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

      所以这个算法体现了BLOCK，表示分块去join，判读的总次数是不变的。再看一下驱动表的选择问题。比如100*1000 = （88+12）*1000，分块越多，被驱动表扫描次数也就越多。

      驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

      这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

      1. 扫描行数是 N+λ*N*M；
      2. 内存判断 N*M次。

      内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。所以结论是，应该让小表当驱动表。

    • 小表的定义

      我们前面的例子是没有加条件的。如果我在语句的where条件加上 t2.id<=50这个限定条件，再来看下这两条语句：

      select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

      如果是用第二个语句的话，join_buffer只需要放入t2的前50行，显然是更好的。所以这里，“t2的前50行”是那个相对小的表，也就是“小表”。

      select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

      这个例子里，表t1 和 t2都是只有100行参加join。但是，这两条语句每次查询放入join_buffer中的数据是不一样的：

      • 表t1只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；
      • 表t2需要查所有的字段，因此如果把表t2放到join_buffer中的话，就需要放入三个字段id、a和b。我们应该选择表t1作为驱动表。也就是说在这个例子里，“只需要一列参与join的表t1”是那个相对小的表。
      • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完
        成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join表优化

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;

假设在表t1里，插入了1000行数据，每一行a=1001 - id，表t1中字段a是逆序的，同时t2插入了100万行数据。

Multi-Range Read优化

这个优化的目的是尽量使用顺序读盘。

select * from t1 where a>=1 and a<=100;

主键索引是b+树，在这颗树上，每次只能根据一个主键id查到一行数据，因此回表是一行行搜索主键索引的。

如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

  1. 根据索引a，定位到满足条件的记录，将id放入到read_rnd_buffer中
  2. 将buffer中的id按递增顺序排列，
  3. 排序后的id数组，依次到主键id索引中查记录，返回结果。
  • 如果buffer反满了，就会执行完2，3，清空buffer，之后继续招索引a的下个记录，并继续循环。
  • MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询，也就是一个多值查询，可以得到足够多的id，这样通过排序以后，再去主键索引查数据，才能体现顺序优势。

Batched Key Access

回顾以下NLJ算法的执行逻辑，从驱动表t1，一行行的取出a，再到t2中去join，对于t2来说，每次都是匹配一个值，这样不能多传值给t2，MRR的优势就用不上了。

可以先取出表t1的数据取出一部分，放到join_buffer中，下图就是NLJ算法优化后的BKA算法流程。

BNL算法的性能问题

  1. 可能会多次扫描被驱动表，占用IO资源。
  2. 判断join条件需要执行M*N次对比，如果是大表就会占用非常多的CPU资源，
  3. 可能会导致Buffer Pool的热数据淘汰，影响内存命中率。

在执行语句前，先应该分析和查看explain结果的方式，确认是否要使用BNL算法，如果优化器会选择BNL做法，应该给被驱动表加上索引，把BNL算法转化成BKA算法

 
 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/dhName/p/14270469.html